Oracle DBMS_STATS_ADVISOR
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 18c
What's New In 19c

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
  DBSecWorx