Oracle DBMS_STATS_ADVISOR
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
Creation an operation filter dbms_stats_advisor.create_opr_filter(
opr_id     IN     NUMBER,
opr_filter IN OUT dbms_stats.statsAdvOpr);
TBD
 
CREATE_TASK (new 12.2)
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 (new 12.2)
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 (new 12.2)
Undocumented dbms_stats_advisor.example_avoid_analyze(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_CREATE_MAINTENANCE_WIN (new 12.2)
Undocumented dbms_stats_advisor.example_create_maintenance_win(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_ENABLE_AUTO_TASK (new 12.2)
Undocumented dbms_stats_advisor.example_enable_auto_task(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_ENA_AUTO_OPTIM_STATS (new 12.2)
Undocumented dbms_stats_advisor.example_ena_auto_optim_stats(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_ENA_MAINTENANCE_WIN (new 12.2)
Undocumented dbms_stats_advisor.example_ena_maintenance_win(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_GATHER_SCHEMA_STATS (new 12.2)
Undocumented dbms_stats_advisor.example_gather_schema_stats(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_GATHER_STATS_ON_OBJ (new 12.2)
Undocumented dbms_stats_advisor.example_gather_stats_on_obj(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_INC_MAINTENANCE_WINDOW (new 12.2)
Undocumented dbms_stats_advisor.example_inc_maintenance_window(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_LOCK_VOLATILE_TABLE (new 12.2)
Undocumented dbms_stats_advisor.example_lock_volatile_table(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_NOP (new 12.2)
Example NO Operation dbms_stats_advisor.example_nop(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_NOT_USE_INCREMENTAL (new 12.2)
Undocumented dbms_stats_advisor.example_not_use_incremental(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_PURGE_STATS (new 12.2)
Undocumented dbms_stats_advisor.example_purge_stats(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_REDUCE_RETENTION (new 12.2)
Undocumented dbms_stats_advisor.example_reduce_retention(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_SET_AUTO_STATS_TARGET (new 12.2)
Undocumented dbms_stats_advisor.example_set_auto_stats_target(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_SET_CONCURRENT (new 12.2)
Undocumented dbms_stats_advisor.example_set_concurrent(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_SET_GLOBAL_PREF (new 12.2)
Undocumented dbms_stats_advisor.example_set_global_prefs(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_TURN_ON_SPD (new 12.2)
Undocumented dbms_stats_advisor.example_turn_on_spd(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_UNLOCK_NONVOLATILE_TAB (new 12.2)
Undocumented dbms_stats_advisor.example_unlock_nonvolatile_tab(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_USE_AUTODEGREE (new 12.2)
Undocumented dbms_stats_advisor.example_use_autodegree(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_USE_DEFAULT_PARAM (new 12.2)
Undocumented dbms_stats_advisor.example_use_default_param(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_USE_DEF_OBJ_PREF (new 12.2)
Undocumented dbms_stats_advisor.example_ues_def_obj_pref(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_USE_INCREMENTAL (new 12.2)
Undocumented dbms_stats_advisor. example_use_incremental(example_xml IN OUT XMLTYPE);
TBD
 
EXAMPLE_USE_TRUNCATE (new 12.2)
Undocumented dbms_stats_advisor.example_use_truncate(example_xml IN OUT XMLTYPE);
TBD
 
EXECUTE_TASK (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
Undocumented dbms_stats_advisor.i_trace(msg IN VARCHAR2);
exec dbms_stats_advisor.i_trace('What Does This Do?');
 
REPORT_AVOIDINEFFSTATSOPRSEQ (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
Resets the named task dbms_stats_advisor.reset_task(task_name IN VARCHAR2);
exec dbms_stats_advisor.reset_task('UW_ADV_TASK');
 
RESUME_TASK (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
Undocumented dbms_stats_advisor.tab_same_signature(
owner1    IN VARCHAR2,
tabnameu1 IN VARCHAR2,
owner2    IN VARCHAR2,
tabnameu2 IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
USE_AUTODEGREE (new 12.2)
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 (new 12.2)
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 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved