Oracle | DBMS_STATS

Oracle DBMS_STATS_ADVISOR Version 21c

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 Undocumented but serves as the basis for the new Optimizer Statistics Advisor.

AUTHID CURRENT_USER

Data Types TYPE targetObjTab IS ....; TYPE defParamOprTable ....; TYPE defParamValTable ....; TYPE dbmsstatnumtab ....;

Dependencies

DBMSSTATNUMTAB	DBMS_STATS_INTERNAL	USER_ADVISOR_TASKS
DBMS_ADVISOR	DBMS_STATS_LIB	WRH$_OSSTAT
DBMS_ASSERT	DUAL	WRI$_ADV_STATS
DBMS_LOB	OBJ$	WRI$_OPTSTAT_OPR
DBMS_PRIV_CAPTURE	OPTSTAT_HIST_CONTROL$	WRI$_OPTSTAT_OPR_TASKS
DBMS_REPORT	PLITBLM	WRM$_SNAPSHOT
DBMS_SMB_INTERNAL	PRVT_ADVISOR	XMLSEQUENCE
DBMS_STANDARD	PRVT_REPORT_TAGS	XMLTYPE
DBMS_STATS	USER$

Documented No

First Available 12.2

Security Model Owned by SYS with EXECUTE granted to PUBLIC.

This appears to be another wholly unnecessary violation of the concept of least privilege.

Source {ORACLE_HOME}/rdbms/admin/prvtstatadv.plb

Subprograms

ACTION_GATHEROBJECTSTATS	EXAMPLE_ENA_AUTO_OPTIM_STATS	GET_PARAM_DEF_VAL
ACTION_LOCKVOLATILETABLE	EXAMPLE_ENA_MAINTENANCE_WIN	GET_SINGLE_PARAM_DEF_VAL
ACTION_NOP	EXAMPLE_GATHER_SCHEMA_STATS	GET_STALESTATS_OBJ
ACTION_NOTUSEINCREMENTAL	EXAMPLE_GATHER_STATS_ON_OBJ	IMPLEMENT_TASK
ACTION_UNLOCKNONVOLATILETABLE	EXAMPLE_INC_MAINTENANCE_WINDOW	INTERRUPT_TASK
ACTION_USEAUTODEGREE	EXAMPLE_LOCK_VOLATILE_TABLE	I_EXECUTE_TASK
ACTION_USECONCURRENT	EXAMPLE_NOP	I_RAISE_STATS_ADVISOR_ERROR
ACTION_USEDEFAULTOBJPREFERENCE	EXAMPLE_NOT_USE_INCREMENTAL	I_TRACE
ACTION_USEDEFAULTPARAMETER	EXAMPLE_PURGE_STATS	REPORT_AVOIDINEFFSTATSOPRSEQ
ACTION_USEDEFAULTPREFERENCE	EXAMPLE_REDUCE_RETENTION	REPORT_AVOIDOUTOFRANGE
ACTION_USEINCREMENTAL	EXAMPLE_SET_AUTO_STATS_TARGET	REPORT_CONSISTENTSTATS
CANCEL_TASK	EXAMPLE_SET_CONCURRENT	REPORT_NOP
CHECK_MMON_POLICY_VIOLATION	EXAMPLE_SET_GLOBAL_PREF	REPORT_OBJ_DEFAULT
COMPUTE_VOLATILE_FLAG	EXAMPLE_TURN_ON_SPD	REPORT_OPR_DEFAULT
CONFIGURE_FILTER	EXAMPLE_UNLOCK_NONVOLATILE_TAB	REPORT_TASK
CONFIGURE_OBJ_FILTER	EXAMPLE_USE_AUTODEGREE	RESET_TASK
CONFIGURE_OPR_FILTER	EXAMPLE_USE_DEFAULT_PARAM	RESUME_TASK
CONFIGURE_RULE_FILTER	EXAMPLE_USE_DEF_OBJ_PREF	SCRIPT_TASK
CREATE_OPR_FILTER	EXAMPLE_USE_INCREMENTAL	SET_TASK_PARAMETER
CREATE_TASK	EXAMPLE_USE_TRUNCATE	SKIP_OPERATION
DROP_TASK	EXECUTE_TASK	TAB_SAME_SIGNATURE
EXAMPLE_AVOID_ANALYZE	GET_INDIVIDUAL_RECS	USE_AUTODEGREE
EXAMPLE_CREATE_MAINTENANCE_WIN	GET_NONDEFPARAM_OPR	USE_INCREMENTAL
EXAMPLE_ENABLE_AUTO_TASK	GET_NOSTATS_OBJ

ACTION_GATHEROBJECTSTATS

Undocumented dbms_stats_advisor.action_gatherObjectStats( action_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN NUMBER, operation IN VARCHAR2, valid_rules IN dbmsstatnumtab);

TBD

ACTION_LOCKVOLATILETABLE

Undocumented dbms_stats_advisor.action_lockVolatileTable( action_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN NUMBER, operation IN VARCHAR2, valid_rules IN dbmsstatnumtab);

TBD

ACTION_NOP

Action NO Operation dbms_stats_advisor.action_nop( action_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN NUMBER, operation IN VARCHAR2, valid_rules IN dbmsstatnumtab);

TBD

ACTION_NOTUSEINCREMENTAL

Undocumented dbms_stats_advisor.action_notUseIncremental( action_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN NUMBER, operation IN VARCHAR2, valid_rules IN dbmsstatnumtab);

TBD

ACTION_UNLOCKNONVOLATILETABLE

Undocumented dbms_stats_advisor.action_unlockNonVolatileTable( action_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN NUMBER, operation IN VARCHAR2, valid_rules IN dbmsstatnumtab);

TBD

ACTION_USEAUTODEGREE

Undocumented dbms_stats_advisor.action_userAutoDegree( action_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN NUMBER, operation IN VARCHAR2, valid_rules IN dbmsstatnumtab);

TBD

ACTION_USECONCURRENT

Undocumented dbms_stats_advisor.action_useConcurrent( action_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN NUMBER, operation IN VARCHAR2, valid_rules IN dbmsstatnumtab);

TBD

ACTION_USEDEFAULTOBJPREFERENCE

Undocumented dbms_stats_advisor.action_useDefaultObjPreference( action_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN NUMBER, operation IN VARCHAR2, valid_rules IN dbmsstatnumtab);

TBD

ACTION_USEDEFAULTPARAMETER

Undocumented dbms_stats_advisor.action_useDefaultParameter( action_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN NUMBER, operation IN VARCHAR2, valid_rules IN dbmsstatnumtab);

TBD

ACTION_USEDEFAULTPREFERENCE

Undocumented dbms_stats_advisor.action_useDefaultPreference( action_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN NUMBER, operation IN VARCHAR2, valid_rules IN dbmsstatnumtab);

TBD

ACTION_USEINCREMENTAL

Undocumented dbms_stats_advisor.action_useIncremental( action_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN NUMBER, operation IN VARCHAR2, valid_rules IN dbmsStatNumTab);

TBD

CANCEL_TASK

Cancel the named advisor task dbms_stats_advisor.cancel_task(task_name IN VARCHAR2);

exec dbms_stats_advisor.cancel_task('UW_ADV_TASK');

CHECK_MMON_POLICY_VIOLATION

Returns the number of MMON process policy violations dbms_stats_advisor.check_mmon_policy_violation( arbnum IN NUMBER, elapsed_time IN NUMBER, cpu_time IN NUMBER) RETURN NUMBER;

SQL> SELECT dbms_stats_advisor.check_mmon_policy_violation(1, 1, 100)

        2  FROM dual;

      

      DBMS_STATS_ADVISOR.CHECK_MMON_POLICY_VIOLATION(1,1,100)

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

                                                            0

COMPUTE_VOLATILE_FLAG

Undocumented dbms_stats_advisor.compute_volatile_flag( objn IN NUMBER, flag IN NUMBER, new_flag IN NUMBER, inserts_new IN NUMBER, updates_new IN NUMBER, deletes_new IN NUMBER, inserts_old IN NUMBER, updates_old IN NUMBER, deletes_old IN NUMBER, rowcnt IN NUMBER, stale_pcnt IN NUMBER, gather IN VARCHAR2) RETURN NUMBER;

TBD

CONFIGURE_FILTER

Undocumented dbms_stats_advisor.configure_filter( task_name IN VARCHAR2, operation_type IN VARCHAR2, configuration_type IN VARCHAR2, filter IN dbms_stats.statsAdvFilterTab, filter_xml IN OUT CLOB);

TBD

CONFIGURE_OBJ_FILTER

Configures an object filter dbms_stats_advisor.configure_obj_filter( task_name IN VARCHAR2, stats_adv_opr_type IN VARCHAR2, rule_name IN VARCHAR2, ownname IN VARCHAR2, tabname IN VARCHAR2, action IN VARCHAR2) RETURN CLOB;

TBD

CONFIGURE_OPR_FILTER

Configures an operation filter

Overload 1 dbms_stats_advisor.configure_opr_filter( task_name IN VARCHAR2, stats_adv_opr_type IN VARCHAR2, rule_name IN VARCHAR2, operation_name IN VARCHAR2, action IN VARCHAR2) RETURN CLOB;

TBD

Overload 2 dbms_stats_advisor.configure_opr_filter( task_name IN VARCHAR2, stats_adv_opr_type IN VARCHAR2, rule_name IN VARCHAR2, operation_name IN VARCHAR2, ownname IN VARCHAR2, tabname IN VARCHAR2, action IN VARCHAR2) RETURN CLOB;

TBD

Overload 3 dbms_stats_advisor.configure_opr_filter( task_name IN VARCHAR2, stats_adv_opr_type IN VARCHAR2, rule_name IN VARCHAR2, operation_id IN NUMBER, action IN VARCHAR2) RETURN CLOB;

TBD

CONFIGURE_RULE_FILTER

Configure a rule filter dbms_stats_advisor.configure_rule_filter( task_name IN VARCHAR2, stat_adv_opr_type IN VARCHAR2, rule_name IN VARCHAR2, action IN VARCHAR2) RETURN CLOB;

TBD

CREATE_OPR_FILTER

Creation an operation filter dbms_stats_advisor.create_opr_filter( opr_id IN NUMBER, opr_filter IN OUT dbms_stats.statsAdvOpr);

TBD

CREATE_TASK

Create a task

Interesting: The function is named CREATE_TASK but appears to want the task to already exist dbms_stats_advisor.create_task( task_name IN VARCHAR2, prop IN BINARY_INTEGER) RETURN VARCHAR2;

SELECT dbms_stats_advisor.create_task('UW_ADV_TASK', 1)

      FROM dual;

      SELECT dbms_stats_advisor.create_task('UW_ADV_TASK', 1)

      *

      ERROR at line 1:

      ORA-13605: The specified task or object UW_ADV_TASK does not exist for the current user.

      ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 3312

      ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 4598

      ORA-06512: at "SYS.PRVT_ADVISOR", line 4848

      ORA-06512: at "SYS.PRVT_ADVISOR", line 4809

      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

      ORA-06512: at "SYS.PRVT_ADVISOR", line 7072

      ORA-06512: at "SYS.PRVT_ADVISOR", line 4656

      ORA-06512: at "SYS.PRVT_ADVISOR", line 4827

      ORA-06512: at "SYS.DBMS_ADVISOR", line 409

      ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 4553

      ORA-06512: at "SYS.DBMS_STATS", line 53460

      ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 3303

      ORA-06512: at line 1

DROP_TASK

Drops an Advisor Stats Advisor task dbms_stats_advisor.drop_task(task_name IN UW_ADV_TASK');

exec dbms_stats_advisor.drop_task('UW_ADV_TASK');

EXAMPLE_AVOID_ANALYZE

Undocumented dbms_stats_advisor.example_avoid_analyze(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_CREATE_MAINTENANCE_WIN

Undocumented dbms_stats_advisor.example_create_maintenance_win(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_ENABLE_AUTO_TASK

Undocumented dbms_stats_advisor.example_enable_auto_task(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_ENA_AUTO_OPTIM_STATS

Undocumented dbms_stats_advisor.example_ena_auto_optim_stats(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_ENA_MAINTENANCE_WIN

Undocumented dbms_stats_advisor.example_ena_maintenance_win(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_GATHER_SCHEMA_STATS

Undocumented dbms_stats_advisor.example_gather_schema_stats(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_GATHER_STATS_ON_OBJ

Undocumented dbms_stats_advisor.example_gather_stats_on_obj(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_INC_MAINTENANCE_WINDOW

Undocumented dbms_stats_advisor.example_inc_maintenance_window(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_LOCK_VOLATILE_TABLE

Undocumented dbms_stats_advisor.example_lock_volatile_table(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_NOP

Example NO Operation dbms_stats_advisor.example_nop(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_NOT_USE_INCREMENTAL

Undocumented dbms_stats_advisor.example_not_use_incremental(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_PURGE_STATS

Undocumented dbms_stats_advisor.example_purge_stats(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_REDUCE_RETENTION

Undocumented dbms_stats_advisor.example_reduce_retention(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_SET_AUTO_STATS_TARGET

Undocumented dbms_stats_advisor.example_set_auto_stats_target(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_SET_CONCURRENT

Undocumented dbms_stats_advisor.example_set_concurrent(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_SET_GLOBAL_PREF

Undocumented dbms_stats_advisor.example_set_global_prefs(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_TURN_ON_SPD

Undocumented dbms_stats_advisor.example_turn_on_spd(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_UNLOCK_NONVOLATILE_TAB

Undocumented dbms_stats_advisor.example_unlock_nonvolatile_tab(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_USE_AUTODEGREE

Undocumented dbms_stats_advisor.example_use_autodegree(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_USE_DEFAULT_PARAM

Undocumented dbms_stats_advisor.example_use_default_param(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_USE_DEF_OBJ_PREF

Undocumented dbms_stats_advisor.example_ues_def_obj_pref(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_USE_INCREMENTAL

Undocumented dbms_stats_advisor. example_use_incremental(example_xml IN OUT XMLTYPE);

TBD

EXAMPLE_USE_TRUNCATE

Undocumented dbms_stats_advisor.example_use_truncate(example_xml IN OUT XMLTYPE);

TBD

EXECUTE_TASK

Execute an advisor task dbms_stats_advisor.execute_task( task_name IN VARCHAR2, exec_name IN VARCHAR2) RETURN VARCHAR2;

BEGIN

        dbms_output.put_line(dbms_stats_advisor.execute_task('UW_ADV_TASK', 'UW_EXEC'));

      END;

      /

GET_INDIVIDUAL_RECS

Undocumented dbms_stats_advisor.get_individual_recs( ownname IN VARCHAR2, tabname IN VARCHAR2, rec IN VARCHAR2, type IN VARCHAR2) RETURN CLOB;

TBD

GET_NONDEFPARAM_OPR

Undocumented dbms_stats_advisor.get_nonDefParam_Opr(op_start_time IN DATE) RETURN PIPELINED dbms_stats_advisor.defParamOprTable;

SELECT * FROM TABLE(dbms_stats_advisor.get_nonDefParam_Opr(SYSDATE));

GET_NOSTATS_OBJ

Undocumented dbms_stats_advisor.get_NoStats_obj( ownname IN VARCHAR2, get_sys IN NUMBER) RETURN PIPELINED dbms_stats_advisor.targetObjTab;

DECLARE

       retTab dbms_stats_advisor.targetObjTab;

      BEGIN

        SELECT TABLE(dbms_stats_advisor.get_NoStats_obj('SYS', 0))

        INTO retTab

        FROM dual;

      END;

      /

INTO retTab * ERROR at line 5: ORA-06550: line 5, column 8: PLS-00642: local collection types not allowed in SQL statements ORA-06550: line 4, column 29: PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got SYS.SYS_PLSQL_5A015A37_395_1 ORA-06550: line 4, column 3: PL/SQL: SQL Statement ignored

desc SYS.SYS_PLSQL_5A015A37_395_1

      SYS.SYS_PLSQL_5A015A37_395_1 TABLE OF SYS_PLSQL_5A015A37_338_1

      Name            Null?    Type

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

      OBJTYPE                  VARCHAR2(6)

      OWNNAME                  VARCHAR2(130)

      OBJNAME                  VARCHAR2(130)

      PARTNAME                 VARCHAR2(130)

      SUBPARTNAME              VARCHAR2(130)

      LOCK_FLAGS               NUMBER

      OBJ_NUM                  NUMBER

      FXT_TYP                  VARCHAR2(30)

      REASON                   NUMBER

      STALENESS                NUMBER

      

      SELECT * FROM TABLE(dbms_stats_advisor.get_NoStats_obj('XDB', 3));

      

      no rows selected

GET_PARAM_DEF_VAL

Returns the default value for all parameters dbms_stats_advisor. get_param_def_val RETURN PIPELINED dbms_stats_advisor.defParamValTable;

TBD

GET_SINGLE_PARAM_DEF_VAL

Returns the default value of a single parameter dbms_stats_advisor.get_single_param_def_value(pnameu IN VARCHAR2) RETURN VARCHAR2;

TBD

GET_STALESTATS_OBJ

Get a list of objects with stale optimizer stats dbms_stats_advisor.get_staleStats_obj( ownname IN VARCHAR2, get_sys IN NUMBER) RETURN PIPELINED dbms_stats_advisor.targetObjTab;

col objname format a30

      col partname format a41

      

      SELECT objname, partname, lock_flags, obj_num, reason, staleness

      FROM TABLE(dbms_stats_advisor.get_staleStats_obj(USER, 1)) WHERE rownum < 11;

      

      
      OBJNAME                  PARTNAME                              LOCK_FLAGS OBJ_NUM REASON  STALENESS

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

      WRH$_OSSTAT              WRH$_OSSTAT_549318987_692                      0   81121       4 .363636364

      WRH$_PARAMETER           WRH$_PARAMETER_549318987_692                   0   81101       4 .363636364

      WRH$_ROWCACHE_SUMMARY    WRH$_ROWCACHE_SUMMARY_549318987_692            0   81089       4 .363636364

      WRH$_SEG_STAT            WRH$_SEG_STAT_549318987_692                    0   81105       4 .376898048

      WRH$_SEG_STAT_OBJ                                                       0   10833       4 .736423841

      WRH$_SERVICE_NAME                                                       0   10893       4          1

      WRH$_SERVICE_STAT        WRH$_SERVICE_STAT_549318987_692                0   81109       4 .363636364

      WRH$_SERVICE_WAIT_CLASS  WRH$_SERVICE_WAIT_CLASS_549318987_692          0   81129       4 .363636364

      WRH$_SGASTAT             WRH$_SGASTAT_549318987_692                     0   81093       4 .374117647

      WRH$_SQLSTAT             WRH$_SQLSTAT_549318987_692                     0   81063       4 .402930403

IMPLEMENT_TASK

Implements an Advisor task dbms_stats_advisor.implement_task( task_name IN VARCHAR2, exec_name IN VARCHAR2, implement_level IN VARCHAR2) RETURN CLOB;

TBD

INTERRUPT_TASK

Interrupts a running Advisor task dbms_stats_advisor.interupt_task(task_name IN VARCHAR2);

exec dbms_stats_advisor.interupt_task('UW_ADV_TASK');

I_EXECUTE_TASK

Runs or resume the identified Advisor task dbms_stats_advisor.i_execute_task( task_id IN NUMBER, resume IN BOOLEAN) RETURN BINARY_INTEGER

DECLARE

       retVal BINARY_INTEGER;

      BEGIN

        retVal := dbms_stats_advisor.i_execute_task(1, FALSE);

        dbms_output.put_line(TO_CHAR(retVal));

      END;

      /

      0

      

      PL/SQL procedure successfully completed.

I_RAISE_STATS_ADVISOR_ERROR

Raises an exception in the range -20000 through -20999 dbms_stats_advisor.i_raise_stats_advisor_error( err_num IN NUMBER, err_msg IN VARCHAR2);

exec dbms_stats_advisor.i_raise_stats_advisor_error(-20000, 'Test Error Msg');

      exec dbms_stats_advisor.i_raise_stats_advisor_error(-20999, 'Test Error Msg');

I_TRACE

Undocumented dbms_stats_advisor.i_trace(msg IN VARCHAR2);

exec dbms_stats_advisor.i_trace('What Does This Do?');

REPORT_AVOIDINEFFSTATSOPRSEQ

Undocumented dbms_stats_advisor.report_avoidIneffStatOprSeq( detail_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN VARCHAR2, rule_id IN NUMBER, finding_def_id IN NUMBER);

TBD

REPORT_AVOIDOUTOFRANGE

Undocumented dbms_stats_advisor.report_avoidOutOfRange( detail_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN VARCHAR2, rule_id IN NUMBER, finding_def_id IN NUMBER);

TBD

REPORT_CONSISTENTSTATS

Undocumented dbms_stats_advisor.report_consistentStats( detail_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN VARCHAR2, rule_id IN NUMBER, finding_def_id IN NUMBER);

TBD

REPORT_NOP

Report NO Operation dbms_stats_advisor.report_nop( detail_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN VARCHAR2, rule_id IN NUMBER, finding_def_id IN NUMBER);

TBD

REPORT_OBJ_DEFAULT

Undocumented dbms_stats_advisor.report_obj_default( detail_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN VARCHAR2, rule_id IN NUMBER, finding_def_id IN NUMBER);

TBD

REPORT_OPR_DEFAULT

Undocumented dbms_stats_advisor.report_opr_default( detail_xml IN OUT XMLTYPE, task_id IN NUMBER, exec_name IN VARCHAR2, level_flags IN VARCHAR2, rule_id IN NUMBER, finding_def_id IN NUMBER);

TBD

REPORT_TASK

Task Report dbms_stats_advisor.report_task( task_name IN VARCHAR2, exec_name IN VARCHAR2, type IN VARCHAR2, section IN VARCHAR2, level IN VARCHAR2) RETURN CLOB;

TBD

RESET_TASK

Resets the named task dbms_stats_advisor.reset_task(task_name IN VARCHAR2);

exec dbms_stats_advisor.reset_task('UW_ADV_TASK');

RESUME_TASK

Resume the named optimizer task dbms_stats_advisor.resume_task(task_name IN VARCHAR2);

exec dbms_stats_advisor.resume_task('UW_ADV_TASK');

SCRIPT_TASK

Returns a scripted task dbms_stats_advisor.script_task( task_name IN VARCHAR2, exec_name IN VARCHAR2, dir_name IN VARCHAR2, script_level IN VARCHAR2) RETURN CLOB;

TBD

SET_TASK_PARAMETER

Sets the parameters for a task dbms_stats_advisor.set_task_parameter( task_name IN VARCHAR2, parameter IN VARCHAR2, value IN VARCHAR2);

TBD

SKIP_OPERATION

Skips an operation dbms_stats_advisor.skip_operation( rule_id IN NUMBER, task_id IN NUMBER, opr_type IN VARCHAR2, opr_name IN VARCHAR2, target IN VARCHAR2, param_val IN VARCHAR2, username IN VARCHAR2, privileg IN NUMBER) RETURN VARCHAR2;

TBD

TAB_SAME_SIGNATURE

Undocumented dbms_stats_advisor.tab_same_signature( owner1 IN VARCHAR2, tabnameu1 IN VARCHAR2, owner2 IN VARCHAR2, tabnameu2 IN VARCHAR2) RETURN VARCHAR2;

TBD

USE_AUTODEGREE

Use the AUTO DEGREE optimizer default for the identified target object dbms_stats_advisor.use_autoDegree(tobjn IN NUMBER) RETURN VARCHAR2;

SELECT dbms_stats_advisor.use_autodegree(81069)

      FROM dual;

USE_INCREMENTAL

Collect INCREMENTAL statistics for the identified target object dbms_stats_advisor.use_incremental(tobjn IN NUMBER) RETURN VARCHAR2;

SELECT dbms_stats_advisor.use_incremental(81069)

      FROM dual;

      

      DBMS_STATS_ADVISOR.USE_INCREMENTAL(81069)

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

      F