Oracle | DBMS_AUTO_CLUSTERING

Oracle DBMS_AUTO_CLUSTERING_INTERNAL Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Internal support utilities with subprograms called by DBMS_AUTO_CLUSTERING for creation of automatic clustering.

Run this package to create clustering recommendations based on workload running in the system. Also run this package to verify and implement the clustering recommendations. This package contains subprograms that display a report of the task, configure the execution and behavior of task.

AUTHID DEFINER

Dependencies

AC$CONFIG_PARAMS	DBMS_SQLTUNE_INTERNAL
AC_AUTOTASK_STATUS$	DBMS_STANDARD
AC_CLEANUP$	DBMS_STATS
AC_JOURNAL$	DBMS_STATS_INTERNAL
ALL_SQLSET	DBMS_SWAT_ARM_INTERNAL
ALL_TABLES	DBMS_SWAT_INTERNAL
ALL_ZONEMAPS	DBMS_SYSTEM
AUTOCL_TASK_STATUS$	DBMS_SYS_ERROR
AUTOCL_VER_STATES$	DBMS_UTILITY
COL$	DUAL
DBA_CLUSTERING_TABLES	IND$
DBA_CONSTRAINTS	MON_MODS_ALL$
DBA_CONS_COLUMNS	OBJ$
DBA_OBJECTS	PLITBLM
DBA_PART_TABLES	PRVT_ADVISOR
DBA_SEGMENTS	SUM$
DBA_SQLSET	SUMAGG$
DBA_SQLSET_REFERENCES	SUMDETAIL$
DBA_TABLES	TAB$
DBA_TAB_PARTITIONS	UTL_CALL_STACK
DBA_TAB_SUBPARTITIONS	V$DATBASE
DBA_ZONEMAPS	WRI$_ADV_ACTIONS
DBMS_ADVISOR	WRI$_ADV_AUTOCL_COLINFO
DBMS_APPLICATION_INFO	WRI$_ADV_JOININFO
DBMS_ASSERT	WRI$_ADV_AUTOCL_RECOMMENDATIONS
DBMS_AUTO_CLUSTERING	WRI$_ADV_TASKS
DBMS_AUTO_CLUSTERING_LIB	WRI$_OPTSTAT_TAB_HISTORY
DBMS_OUTPUT	X$KSPPI
DBMS_RANDOM	X$KSPPSV
DBMS_SQLPA	XMLTYPE
DBMS_SQLTUNE

Documented No

First Available 23.1

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvsaci.plb
{ORACLE_HOME}/rdbms/admin/prvtaci.plb

Subprograms

AC_CLEAR	CONFIGURE	RECOMMEND_CLUSTERING_METHOD
AC_INIT	GET_RECOMMENDATION	REPORT_ACTIVITY
AC_TASK_PROC	GET_REPORT_LEVELS	VERIFY_RECOMMENDATION
APPLY_RECOMMENDATION	GET_REPORT_SECTIONS

AC_CLEAR (new 23c)

Undocumented dbms_auto_clustering_internal.ac_clear;

exec dbms_auto_clustering_internal.ac_clear;

      

      PL/SQL procedure successfully completed.

AC_INIT (new 23c)

Undocumented dbms_auto_clustering_internal.ac_init;

exec dbms_auto_clustering_internal.ac_init;

      

      PL/SQL procedure successfully completed.

AC_TASK_PROC (new 23c)

Undocumented dbms_auto_clustering_internal.ac_task_proc( stsowner IN VARCHAR2, stsname IN VARCHAR2, task_start_time IN TIMESTAMP, task_end_time IN TIMESTAMP);

TBD

APPLY_RECOMMENDATION (new 23c)

Undocumented dbms_auto_clustering_internal.apply_recommendation( recommendation_id IN RAW, table_owner IN VARCHAR2, table_name IN VARCHAR2, clustering_ddl IN VARCHAR2, apply_mode IN VARCHAR2);

TBD

CONFIGURE (new 23c)

Undocumented dbms_auto_clustering_internal.configure( parameter_name IN VARCHAR2, parameter_value IN VARCHAR2, allow IN BOOLEAN);

TBD

GET_RECOMMENDATION (new 23c)

Undocumented dbms_auto_clustering_internal.get_recommendation( table_owner IN VARCHAR2, table_name IN VARCHAR2) RETURN RAW;

SELECT dbms_auto_clustering_internal.get_recommendation('C##UWCLASS', 'ACTABLE')

      FROM dual;

      

      -- ORA-40216 Feature not supported in 23.1 Beta

GET_REPORT_LEVELS (new 23c)

Undocumented dbms_auto_clustering_internal.get_report_levels(report_level IN VARCHAR2) RETURN NUMBER;

SELECT dbms_auto_clustering_internal.get_report_levels('TYPICAL');

      

      DBMS_AUTO_CLUSTERING_INTERNAL.GET_REPORT_LEVELS('TYPICAL')

      ----------------------------------------------------------

                                                               1

      

      SELECT dbms_auto_clustering_internal.get_report_levels('BASIC');

      

      
      DBMS_AUTO_CLUSTERING_INTERNAL.GET_REPORT_LEVELS('BASIC')

      --------------------------------------------------------

                                                             2

      

      SELECT dbms_auto_clustering_internal.get_report_levels('ALL');

      

      
      DBMS_AUTO_CLUSTERING_INTERNAL.GET_REPORT_LEVELS('ALL')

      ------------------------------------------------------

                                                           4

GET_REPORT_SECTIONS (new 23c)

Undocumented dbms_auto_clustering_internal.get_report_sections(report_section IN VARCHAR2) RETURN NUMBER;

SELECT 
      dbms_auto_clustering_internal.get_report_sections('SUMMARY');

      

      
      DBMS_AUTO_CLUSTERING_INTERNAL.GET_REPORT_SECTIONS('SUMMARY')

      ------------------------------------------------------------

                                                                33

      

      SELECT 
      dbms_auto_clustering_internal.get_report_sections('ALL');

      

      
      DBMS_AUTO_CLUSTERING_INTERNAL.GET_REPORT_SECTIONS('ALL')

      --------------------------------------------------------

                                                            63

RECOMMEND_CLUSTERING_METHOD (new 23c)

Undocumented dbms_auto_clustering_internal.recommend_clustering_method( sqlset_owner IN VARCHAR2, sqlset_name IN VARCHAR2, table_owner IN VARCHAR2, table_name IN VARCHAR2, time_limit IN INTERVAL DAY TO SECOND, report_type IN VARCHAR2, report_section IN VARCHAR2, report_level IN VARCHAR2, verification IN BOOLEAN);

TBD

REPORT_ACTIVITY (new 23c)

Undocumented dbms_auto_clustering_internal.retport_activity( activity_start IN TIMESTAMP WITH TIME ZONE, activity_end IN TIMESTAMP WITH TIME ZONE, section_flags IN NUMBER, level_flags IN NUMBER, RETURN sys.xmlType;

TBD

VERIFY_RECOMMENDATION (new 23c)

Undocumented dbms_auto_clustering_internal.verify_recommendation( recommendation_id IN RAW, table_owner IN VARCHAR2, table_name IN VARCHAR2);

TBD