Oracle DBMS_STATS_ADVISOR
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
Purpose Undocumented but serves as the basis for the new Optimizer Statistics Advisor.
AUTHID CURRENT_USER
Data Types TYPE targetObjTab IS ....;

TYPE defParamOprTable ....;

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.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC. We expect another 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;
SQL> SELECT dbms_stats_advisor.create_task('UW_ADV_TASK', 1)
  2  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;
SQL> DECLARE
  2   retTab dbms_stats_advisor.targetObjTab;
  3  BEGIN
  4    SELECT TABLE(dbms_stats_advisor.get_NoStats_obj('SYS', 0))
  5    INTO retTab
  6    FROM dual;
  7  END;
  8  /

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



SQL> 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

SQL> 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;
SQL> col objname format a30
SQL> col partname format a41

SQL> SELECT objname, partname, lock_flags, obj_num, reason, staleness
  2  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
SQL> DECLARE
  2   retVal BINARY_INTEGER;
  3  BEGIN
  4    retVal := dbms_stats_advisor.i_execute_task(1, FALSE);
  5    dbms_output.put_line(TO_CHAR(retVal));
  6  END;
  7  /
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;
SQL> SELECT dbms_stats_advisor.use_autodegree(81069)
  2  FROM dual;
 
USE_INCREMENTAL
Collect INCREMENTAL statistics for the identified target object dbms_stats_advisor.use_incremental(tobjn IN NUMBER)
RETURN VARCHAR2;
SQL> SELECT dbms_stats_advisor.use_incremental(81069)
  2  FROM dual;

DBMS_STATS_ADVISOR.USE_INCREMENTAL(81069)
-----------------------------------------
F

Related Topics
Built-in Functions
Built-in Packages
DBMS_STATS
DBMS_STATS_INTERNAL
What's New In 18cR3
What's New In 19cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved