Oracle DBMS_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 DBMS_ADVISOR is part of the server manageability suite of advisors, a set of expert systems that identifies and helps resolve performance problems relating to database server components.
Some advisors have their own packages. For these advisors, Oracle recommends that you use the advisor-specific package rather than DBMS_ADVISOR. Each of the following advisors has its own package, tailored to its specific functionality:
  • Automatic Database Diagnostic Monitor (DBMS_ADDM)
  • SQL Performance Analyzer (DBMS_SQLPA)
  • SQL Repair Advisor (DBMS_SQLDIAG)
  • SQL Tuning Advisor (DBMS_SQLTUNE)
  • Compression Advisor (DBMS_COMPRESSION.GET_COMPRESSION_RATIO)
SQL Access Advisor and Segment Advisor are the only advisors with common use cases for DBMS_ADVISOR. Undo Advisor and Compression Advisor do not support DBMS_ADVISOR subprograms.
AUTHID CURRENT_USER
Constants
Name Data Type Value
Advisor Names
ADV_NAME_ADDM VARCHAR2(30) 'ADDM'
ADV_NAME_COMPRESS VARCHAR2(30) 'Compression Advisor'
ADV_NAME_DEFAULT VARCHAR2(30) 'Default Advisor'
ADV_NAME_SEGMENT VARCHAR2(30) 'Segment Advisor'
ADV_NAME_SPM_EVOLVE VARCHAR2(30) 'SPM Evolve Advisor'
ADV_NAME_SQLACCESS VARCHAR2(30) 'SQL Access Advisor'
ADV_NAME_SQLPA VARCHAR2(30) 'SQL Performance Analyzer'
ADV_NAME_SQLREPAIR VARCHAR2(30) 'SQL Repair Advisor'
ADV_NAME_SQLTUNE VARCHAR2(30) 'SQL Tuning Advisor'
ADV_NAME_SQLWM VARCHAR2(30) 'SQL Workload Manager'
ADV_NAME_STATISTICS VARCHAR2(30) 'Statistics Advisor'
ADV_NAME_TUNEMV VARCHAR2(30) 'Tune MView'
ADV_NAME_UNDO VARCHAR2(30) 'Undo Advisor'
ADV_NAME_ZONEMAP VARCHAR2(30) 'Zonemap Advisor'
Advisor Numbers
ADV_ID_DEFAULT NUMBER 0
ADV_ID_ADDM NUMBER 1
ADV_ID_SQLACCESS NUMBER 2
ADV_ID_UNDO NUMBER 3
ADV_ID_SQLTUNE NUMBER 4
ADV_ID_SEGMENT NUMBER 5
ADV_ID_SQLWM NUMBER 6
ADV_ID_TUNEMV NUMBER 7
ADV_ID_SQLPA NUMBER 8
ADV_ID_SQLREPAIR NUMBER 9
ADV_ID_COMPRESS NUMBER 10
ADV_ID_SPM_EVOLVE NUMBER 11
ADV_ID_STATISTICS NUMBER 12
ADV_ID_ZONEMAP NUMBER 13
Common
ADVISOR_ALL NUMBER -995
ADVISOR_CURRENT NUMBER -996
ADVISOR_DEFAULT NUMBER -997
ADVISOR_UNLIMITED NUMBER -998
ADVISOR_UNUSED NUMBER -999
SQL Access Advisor
SQLACCESS_ADVISOR VARCHAR2(30) ADV_NAME_SQLACCESS
SQLACCESS_GENERAL VARCHAR2(20) 'SQLACCESS_GENERAL'
SQLACCESS_OLTP VARCHAR2(20) 'SQLACCESS_OLTP'
SQLACCESS_WAREHOUSE VARCHAR2(20) 'SQLACCESS_WAREHOUSE'
SQLWORKLOAD_MANAGER VARCHAR2(30) ADV_NAME_SQLWM
TUNE_MVIEW_ADVISOR VARCHAR2(30) ADV_NAME_TUNEMV
ZONEMAP_MANAGER VARCHAR2(30) ADV_NAME_ZONEMAP
Data Types -- Used to pass a list of task parameters to the execute_task function. Only used for advisor that support multi-execution.
TYPE argList IS TABLE OF sys.wri$_adv_parameters.value%TYPE;

-- Identical to DBMS_SQL.VARCHAR2S and is redefined here due to bootstrapping problems
TYPE varchar2adv IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
Definitions
dba_advisor_findings Findings and symptoms and recommendations from the diagnostic monitor
dba_advisor_log Current task information: status, progress, error messages, execution times
dba_advisor_recommendations Results from completed diagnostic tasks with recommendations for the problems identified in each run. The recommendations should be looked at in the order of the RANK column, as this relays the magnitude of the problem for the recommendation. The BENEFIT column gives the benefit to the system you can expect after the recommendation is carried out.
dba_advisor_tasks Basic information about existing tasks, such as the task Id, task name, and when created
Dependencies
DBA_ADDM_FDG_BREAKDOWN DBMS_AUTO_ZONEMAP_INTERNAL USER_ADDM_TASKS
DBA_ADDM_FINDINGS DBMS_COMPRESSION USER_ADVISOR_ACTIONS
DBA_ADDM_TASKS DBMS_IREFSTATS USER_ADVISOR_EXECUTIONS
DBA_ADVISOR_ACTIONS DBMS_LOB USER_ADVISOR_EXEC_PARAMETERS
DBA_ADVISOR_DEF_PARAMETERS DBMS_MANAGEMENT_PACKS USER_ADVISOR_FDG_BREAKDOWN
DBA_ADVISOR_EXECUTIONS DBMS_SMB_INTERNAL USER_ADVISOR_FINDINGS
DBA_ADVISOR_EXECUTION_TYPES DBMS_SPACE USER_ADVISOR_JOURNAL
DBA_ADVISOR_EXEC_PARAMETERS DBMS_SPM USER_ADVISOR_LOG
DBA_ADVISOR_FDG_BREAKDOWN DBMS_SPM_INTERNAL USER_ADVISOR_PARAMETERS
DBA_ADVISOR_FINDINGS DBMS_SQLDIAG USER_ADVISOR_RATIONALE
DBA_ADVISOR_FINDING_NAMES DBMS_SQLPA USER_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_JOURNAL DBMS_SQLTUNE USER_ADVISOR_SQLW_JOURNAL
DBA_ADVISOR_LOG DBMS_SQLTUNE_INTERNAL USER_ADVISOR_SQLW_PARAMETERS
DBA_ADVISOR_PARAMETERS DBMS_SQLTUNE_UTIL1 USER_ADVISOR_TASKS
DBA_ADVISOR_PARAMETERS_PROJ DBMS_SQLTUNE_UTIL2 UTL_FILE
DBA_ADVISOR_RATIONALE DBMS_STANDARD WRI$_ADV_ABSTRACT_T
DBA_ADVISOR_RECOMMENDATIONS DBMS_STATS_ADVISOR WRI$_ADV_COMPRESSION_T
DBA_ADVISOR_SQLW_JOURNAL DBMS_SWAT_ARM_INTERNAL WRI$_ADV_DEF_PARAMETERS
DBA_ADVISOR_SQLW_PARAMETERS DBMS_SYS_ERROR WRI$_ADV_HDM_T
DBA_ADVISOR_TASKS DBMS_UNDO_ADV WRI$_ADV_OBJSPACE_TREND_T
DBA_AUTO_INDEX_EXECUTIONS DBMS_WORKLOAD_CAPTURE_I WRI$_ADV_PARAMETERS
DBA_AUTO_INDEX_STATISTICS DBMS_WORKLOAD_REPLAY_I WRI$_ADV_SPM_EVOLVE
DBA_AUTO_INDEX_VERIFICATIONS DBMS_WRR_INTERNAL WRI$_ADV_SQLACCESS_ADV
DBA_AUTO_MV_ANALYSIS_ACTIONS DBMS_XPLAN WRI$_ADV_SQLPI
DBA_AUTO_MV_ANALYSIS_EXECUTIONS DBMS_XPLAN_INTERNAL WRI$_ADV_SQLTUNE
DBA_AUTO_MV_ANALYSIS_
RECOMMENDATIONS		 PRVTEMX_SQL WRI$_ADV_STATS
DBA_AUTO_MV_ANALYSIS_REPORT PRVT_ACCESS_ADVISOR WRI$_ADV_TASKS
DBA_AUTO_MV_ANALYSIS_TASK PRVT_ADVISOR WRI$_ADV_TUNEMVIEW_ADV
DBA_AUTO_MV_VERIFICATION_REPORT PRVT_COMPRESSION WRI$_ADV_UNDO_ADV
DBA_AUTO_MV_VERIFICATION_STATUS PRVT_HDM WRI$_ADV_WORKLOAD
DBA_WORKLOAD_DIV_SUMMARY PRVT_SMGUTIL WRI$_ADV_ZONEMAP
DBA_WORKLOAD_REPLAY_DIVERGENCE PRVT_SQLADV_INFRA WRI$_REPT_ADDM
DBMS_ADDM PRVT_TUNE_MVIEW WRI$_REPT_PLAN_DIFF
DBMS_ADVISOR PRVT_WORKLOAD WRI$_REPT_SPMEVOLVE
DBMS_ASSERT PRVT_WORKLOAD_NOPRIV WRI$_REPT_SQLPI
DBMS_AUTO_INDEX_INTERNAL USER_ADDM_FDG_BREAKDOWN WRI$_REPT_SQLT
DBMS_AUTO_SQLTUNE USER_ADDM_FINDINGS X$KEHRP
Documented Yes
Exceptions
Error Code Reason
ORA-13699 Advisor feature is not currently implemented
First Available 10.1
Generic Advisor Parameters For the full list of values run the SQL statement below
set linesize 121
col value format a40

desc wri$_adv_def_parameters

SELECT name, datatype, value, description
FROM wri$_adv_def_parameters
ORDER BY 1;

-- ADDM specific parameters; for example
desc dba_advisor_def_parameters

SELECT DISTINCT advisor_name
FROM dba_advisor_def_parameters;

col parameter_value format a30

SELECT parameter_value, is_default
FROM dba_advisor_def_parameters
WHERE advisor_name = 'ADDM';
Generic Advisor Parameter Descriptions
ACTION_LIST STRINGLIST Deprecated
COMMENTED_FILTER_LIST NUMBER Deprecated
DAYS_TO_EXPIRE NUMBER Specifies the expiration time in days for the current SQL Workload object. The value is relative to the last modification date. Once the data expires, it will become a candidate for removal by an automatic purge operation. Possible values are:
  • an integer in the range of 0 to 2147483647 (def. 30)
  • ADVISOR_UNLIMITED
  • ADVISOR_UNUSED
END_TIME STRING Specifies an end time for selecting SQL statements. If the statement did not execute on or before the specified time, it will not be processed. Each date must be in the standard Oracle form of MM-DD-YYY HH24:MI:SS, where:
  • DD is the numeric date
  • MM is the numeric month
  • YYYY is the numeric year
  • HH is the hour in 24 hour format
  • MI is the minute
  • SS is the second
INVALID_ACTION_LIST STRINGLIST Contains a fully qualified list of actions that are not eligible for saving in a workload. An action string is not scanned for correctness. During workload collection, if a SQL statement's action matches a name in the action list, it will not be processed by the operation. An action name is case sensitive. Possible values are:
  • single action
  • comma-delimited action list
  • ADVISOR_UNUSED
INVALID_MODULE_LIST STRINGLIST Contains a fully qualified list of application modules that are not eligible when populating a SQL workload object. The list elements are comma-delimited, and quoted names are supported. A module string is not scanned for correctness.
During workload collection, if a SQL statement's module matches a name in the list, it will not be processed by the operation. Module names are case sensitive.

Possible values are:
  • single application
  • comma-delimited module list
  • ADVISOR_UNUSED (default)
INVALID_SQL STRINGLIST Contains a fully qualified comma-delimited list of text strings that are not eligible when populating a SQL workload object.
A SQL string is not scanned for correctness. During workload collection, if a SQL statement contains a string in the SQL string list, it will not be processed by the operation. Possible values are:
  • single string
  • comma-delimited string list
  • ADVISOR_UNUSED (default)
INVALID_TABLE_LIST TABLELIST  
INVALID_USERNAME_LIST STRINGLIST  
JOURNALING NUMBER  
MODULE_LIST STRINGLIST  
ORDER_LIST STRING  
REPORT_DATE_FORMAT ? Deprecated
SQL_LIMIT NUMBER  
START_TIME STRING  
USERNAME_LIST STRINGLIST  
VALID_ACTION_LIST STRINGLIST  
VALID_MODULE_LIST STRINGLIST  
VALID_SQLSTRING_LIST STRINGLIST  
VALID_TABLE_LIST TABLELIST  
VALID_USERNAME_LIST STRINGLIST Contains a fully qualified list of usernames that are eligible when populating a SQL workload object. The list of elements is comma-delimited: quoted names are supported. During workload collection, if a SQL statement's username does not match a name in the username list, it will not be processed. A username is not case sensitive unless it is quoted. Possible values are:
  • single username
  • comma-delimited username list
  • ADVISOR_UNUSED (default)
Licensing and Usage SELECT advisor_name, num_execs
FROM dba_advisor_usage;

ADVISOR_NAME               NUM_EXECS
------------------------- ----------
ADDM                               1
Compression Advisor                0
SPM Evolve Advisor                 0
SQL Access Advisor                 0
SQL Performance Analyzer           0
SQL Repair Advisor                 0
SQL Tuning Advisor                 0
SQL Workload Manager               0
Segment Advisor                    6
Statistics Advisor               844
Tune MView                         0
Undo Advisor                       1
Recommendations
  • PARTITION BASE TABLE
  • CREATE | DROP  | RETAIN MATERIALIZED VIEW
  • CREATE | ALTER | RETAIN MATERIALIZED VIEW LOG
  • CREATE | DROP  | RETAIN INDEX
  • GATHER STATS
Task Templates
  • DBMS_ADVISOR.SQLACCESS_OLTP
    Preset parameters for an OLTP application.
  • DBMS_ADVISOR.SQLACCESS_WAREHOUSE
    Preset parameters for a data warehouse application.
  • DBMS_ADVISOR.SQLACCESS_GENERAL
    Preset parameters for a hybrid application This is the default.
Security Model Owned by SYS with EXECUTE granted to PUBLIC (a grant that is a clear violation of the Principle of Least Privilege).

Use of this package requires the ADVISOR system privilege.
Source {ORACLE_HOME}/rdbms/admin/dbmsadv.sql
Startup Parameters _addm_auto_enable ... use TRUE to enable auto run
Subprograms
ADD_SQLWKLD_REF EVALUATE_DIRECTIVE QUICK_TUNE
ADD_SQLWKLD_STATEMENT EXECUTE_TASK RESET_SQLWKLD
ADD_STS_REF FORMAT_MESSAGE RESET_TASK
CANCEL_TASK FORMAT_MESSAGE_GROUP RESUME_TASK
CHECK_PRIVS GET_ACCESS_ADVISOR_DEFAULTS SETUP_REPOSITORY
CHECK_READ_PRIVS GET_REC_ATTRIBUTES SETUP_USER_ENVIRONMENT
COPY_SQLWKLD_TO_STS GET_TASK_REPORT SET_DEFAULT_SQLWKLD_PARAMETER
CREATE_FILE GET_TASK_SCRIPT SET_DEFAULT_TASK_PARAMETER
CREATE_OBJECT IMPLEMENT_TASK SET_SQLWKLD_PARAMETER
CREATE_SQLWKLD IMPORT_SQLWKLD_SCHEMA SET_TASK_PARAMETER
CREATE_TASK IMPORT_SQLWKLD_SQLCACHE TUNE_MVIEW
DELETE_DIRECTIVE IMPORT_SQLWKLD_STS UPDATE_DIRECTIVE
DELETE_SQLWKLD IMPORT_SQLWKLD_SUMADV UPDATE_OBJECT
DELETE_SQLWKLD_REF IMPORT_SQLWKLD_USER UPDATE_REC_ATTRIBUTES
DELETE_SQLWKLD_STATEMENT INSERT_DIRECTIVE UPDATE_SQLWKLD_ATTRIBUTES
DELETE_STS_REF INTERRUPT_TASK UPDATE_SQLWKLD_STATEMENT
DELETE_TASK MARK_RECOMMENDATION UPDATE_TASK_ATTRIBUTES
 
ADD_SQLWKLD_REF
Adds a workload reference to an advisor task dbms_advisor.add_sqlwkld_ref(
task_name     IN VARCHAR2,
workload_name IN VARCHAR2
is_sts        IN NUMBER := 0);
Deprecated in Database 11g: Use ADD_STS_REF. 0 = SQL Workload Object, 1 = SQL Tuning Set
 
ADD_SQLWKLD_STATEMENT
Adds a single statement to the specified workload

Deprecated starting in 11g		 dbms_advisor.add_sqlwkld_statement(
workload_name       IN VARCHAR2,
module              IN VARCHAR2 := '',
action              IN VARCHAR2 := '',
cpu_time            IN NUMBER   := 0,
elapsed_time        IN NUMBER   := 0,
disk_reads          IN NUMBER   := 0,
buffer_gets         IN NUMBER   := 0,
rows_processed      IN NUMBER   := 0,
optimizer_cost      IN NUMBER   := 0,
executions          IN NUMBER   := 1,
priority            IN NUMBER   := 2,
last_execution_date IN DATE     := 'SYSDATE',
stat_period         IN NUMBER   := 0,
username            IN VARCHAR2,
sql_text            IN CLOB);
See Demo Below
 
ADD_STS_REF
Adds an STS reference to an advisor task. An STS object must have an owner. The owner can be NULL, in which case the owner is assumed to be the SESSION_USER. dbms_advisor.add_sts_ref(
task_name     IN VARCHAR2 NOT NULL,
sts_owner     IN VARCHAR2,
workload_name IN VARCHAR2 NOT NULL);

-- the following two calls are equivalent:
add_sqlwkld_ref(task_name, workload_name, 1);
add_sts_ref(task_name, NULL, workload_name);
TBD
 
CANCEL_TASK
Cancels a Currently Executing Task. All intermediate and result data are removed from the task. dbms_advisor.cancel_task(task_name IN VARCHAR2);
See Demo Below
 
CHECK_PRIVS
Checks for required advisor privileges dbms_advisor.check_privs;
exec dbms_advisor.check_privs;
 
CHECK_READ_PRIVS
Oracle's comments: "Checks whether the current user has read privileges for another user's tasks. This is typically used only by DBAs to access other users's data, hence we query the dba_* views for now. General support can be added later on once we define all_* views."

LOL		 dbms_advisor.check_read_privs(owner_name IN VARCHAR2);
exec dbms_advisor.check_read_privs('C##UWCLASS');

PL/SQL procedure successfully completed.

exec dbms_advisor.check_read_privs('UWCLASZ');

PL/SQL procedure successfully completed.

exec dbms_advisor.check_read_privs('A Clearly Invalid Input');

PL/SQL procedure successfully completed.

Note: This procedure is broken in 10g, was reportedly going to be fixed in 11g, it has been broken in 12cR1 and 12cR2, 18c, 19c, and as you can see is still worthless in 21.1.
 
COPY_SQLWKLD_TO_STS
Copies workload object data into a user-specified SQL Tuning Set (STS). No filters are supported. dbms_advisor.copy_sqlwkld_to_sts(
workload_name IN VARCHAR2,
sts_name      IN VARCHAR2,
import_mode   IN VARCHAR2 := 'NEW'); -- options are APPEND, NEW, and REPLACE
exec dbms_advisor.copy_sqlwkld_to_sts('UW_WRKLD', 'UW_STS', REPLACE);
 
CREATE_FILE
Creates an external file from a PL/SQL CLOB variable and writes to the file dbms_advisor.create_file(
buffer   IN CLOB,
location IN VARCHAR2,  -- Oracle Directory Object Name
filename IN VARCHAR2); -- Name of file to write in directory
See Demo Below
 
CREATE_OBJECT
Create a new task object

Overload 1		 dbms_advisor.create_object(
task_name   IN  VARCHAR2,
object_type IN  VARCHAR2,
attr1       IN  VARCHAR2 := NULL,
attr2       IN  VARCHAR2 := NULL,
attr3       IN  VARCHAR2 := NULL,
attr4       IN  CLOB     := NULL,
object_id   OUT NUMBER);
conn sh/sh@pdbdev

set serveroutput on

DECLARE
 task_id   NUMBER;
 task_name VARCHAR2(30) := 'UW Task';
 obj_id    NUMBER;
BEGIN
  dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor, task_id,
  task_name);

  dbms_advisor.create_object(task_name, 'SQL', NULL, NULL, NULL,
  'SELECT * FROM sh.sales', obj_id);

  dbms_output.put_line(obj_id);
END;
/

conn sys@pdbdev as sysdba

grant advisor to sh;

conn sh/sh@pdbdev

DECLARE
 task_id   NUMBER;
 task_name VARCHAR2(30) := 'UW Task';
 obj_id    NUMBER;
BEGIN
  dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor, task_id,
  task_name);

  dbms_advisor.create_object(task_name, 'SQL', NULL, NULL, NULL,
  'SELECT * FROM sh.sales', obj_id);

  dbms_output.put_line(obj_id);
END;
/

SELECT type, task_name
FROM user_advisor_objects;

exec dbms_advisor.execute_task('UW Task');

exec dbms_advisor.delete_task('UW Task');

conn sys@pdbdev as sysdba

revoke advisor from sh;
Overload 2 dbms_advisor.create_object(
task_name   IN  VARCHAR2,
object_type IN  VARCHAR2,
attr1       IN  VARCHAR2 := NULL,
attr2       IN  VARCHAR2 := NULL,
attr3       IN  VARCHAR2 := NULL,
attr4       IN  CLOB     := NULL,
attr5       IN  VARCHAR2 := NULL,
object_id   OUT NUMBER);
TBD
 
CREATE_SQLWKLD
Creates a new workload object dbms_advisor.create_sqlwkld(
workload_name IN VARCHAR2,
description   IN VARCHAR2 := NULL,
template      IN VARCHAR2 := NULL,
is_template   IN VARCHAR2 := 'FALSE');
Deprecated in Database 11g
 
CREATE_TASK
Creates a new advisor task in the repository

Overload 1		 dbms_advisor.create_task(
advisor_name     IN     VARCHAR2,
task_id             OUT NUMBER,
task_name        IN OUT VARCHAR2,
task_desc        IN     VARCHAR2 := NULL,
task_or_template IN     VARCHAR2 := NULL,
is_template      IN     VARCHAR2 := 'FALSE',
how_created      IN     VARCHAR2 := NULL);
See Demo Below
Overload 2 dbms_advisor.create_task(
advisor_name     IN VARCHAR2,
task_name        IN VARCHAR2,
task_desc        IN VARCHAR2 := NULL,
template         IN VARCHAR2 := NULL,
is_template      IN VARCHAR2 := 'FALSE',
how_created      IN VARCHAR2 := NULL);
TBD
Overload 3 dbms_advisor.create_task(
parent_task_name IN     VARCHAR2,
rec_id           IN     NUMBER,
task_id             OUT NUMBER,
task_name        IN OUT VARCHAR2,
task_desc        IN     VARCHAR2,
template         IN     VARCHAR2);
TBD
 
DELETE_DIRECTIVE
Deletes a directive from the specified task

This appears to be internal for use by the Grid Control and is undocumented		 dbms_advisor.delete_directive(
directive_id  IN NUMBER,
instance_name IN VARCHAR2,
task_name     IN VARCHAR2 := NULL);l
desc user_advisor_dir_task_inst

SELECT directive_id, instance_name, task_id, task_name, data
FROM user_advisor_dir_task_inst;
 
DELETE_SQLWKLD
Deletes an existing SQL Workload object from the repository dbms_advisor.delete_sqlwkld(workload_name IN VARCHAR2);
Deprecated in Database 11g
 
DELETE_SQLWKLD_REF
Removes a workload reference from the specified task dbms_advisor.delete_sqlwkld_ref(
task_name     IN VARCHAR2,
workload_name IN VARCHAR2
is_sts        IN NUMBER := 0);

0 = SQL Workload Object, 1 = SQL Tuning Set
Deprecated in Database 11g: Use DELETE_STS_REF instead.
 
DELETE_SQLWKLD_STATEMENT
Deletes one or more statements from a workload

Overload 1		 dbms_advisor.delete_sqlwkld_statement(
workload_name IN VARCHAR2,
sql_id        IN NUMBER);
Deprecated in Database 11g
Overoad 2 dbms_advisor.delete_sqlwkld_statement(
workload_name IN  VARCHAR2,
search        IN  VARCHAR2,
deleted       OUT NUMBER);
Deprecated in Database 11g
 
DELETE_STS_REF
Removes a workload reference from the specified task dbms_advisor.delete_sts_ref(
task_name     IN VARCHAR2 NOT NULL,
sts_owner     IN VARCHAR2,
workload_name IN VARCHAR2 NOT NULL);
conn sh/sh@pdbdev

set serveroutput on

DECLARE
 task_id   NUMBER;
 task_name VARCHAR2(30) := 'UW Task';
 wkld_name VARCHAR2(30) := 'UW Wkld';
BEGIN
  dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor, task_id, task_name);

  dbms_advisor.create_sqlwkld(wkld_name, 'UW Wkld');

  dbms_advisor.add_sqlwkld_ref(task_name, wkld_name);

  dbms_advisor.add_sqlwkld_statement(wkld_name, 'MONTHLY', 'ROLLUP', 100, 400, 5041, 103, 640445, 680000, 2, 1, SYSDATE, 1,'SH', 'SELECT avg(amount_sold) FROM sh.sales');

  dbms_advisor.delete_sqlwkld_ref(task_name, wkld_name);
END;
/

SELECT task_name, status
FROM user_advisor_tasks;

col workload_name format a16
col username format a10
col description format a15
col parameter_value format a40

SELECT workload_name, username, cpu_time, buffer_gets, disk_reads, elapsed_time, rows_processed, executions, optimizer_cost
FROM user_advisor_sqlw_stmts;

SELECT workload_name, description, num_select_stmt
FROM user_advisor_sqlw_sum;

SELECT parameter_name, parameter_value, parameter_type
FROM user_advisor_sqlw_parameters
WHERE workload_name = 'UW Wkld';

-- the task remains and the workload remains

exec dbms_advisor.execute_task('UW Task');

BEGIN
  dbms_advisor.delete_sts_ref('UW Task', USER, 'UW Wkld');
  dbms_advisor.delete_task('UW Task');
END;
/
 
DELETE_TASK
Deletes the specified task from the repository dbms_advisor.delete_task(task_name IN VARCHAR2);
See DELETE_SQLWKLD_REF Demo
 
EVALUATE_DIRECTIVE
Evaluates a directive instance and returns the results dbms_advisor.evaluate_directive(
directive_id  IN NUMBER,
instance_name IN VARCHAR2,
task_name     IN VARCHAR2 := NULL,
p1            IN CLOB     := NULL,
p2            IN CLOB     := NULL)
RETURN CLOB;
TBD
 
EXECUTE_TASK
Analyzes or evaluates the specified task
Overload 1		 dbms_advisor.execute_task(task_name IN VARCHAR2);
See Demo Below
Overload 2 dbms_advisor.execute_task(
task_name        IN VARCHAR2,
execution_type   IN VARCHAR2 := NULL,
execution_name   IN VARCHAR2 := NULL,
execution_desc   IN VARCHAR2 := NULL,
execution_params IN argList  := NULL,
database_link_to IN VARCHAR2 := NULL)
RETURN VARCHAR2;
TBD
 
FORMAT_MESSAGE
Retrieves test from an Oracle Message file dbms_advisor.format_message(msg_id IN VARCHAR2) RETURN VARCHAR2;
TBD
 
FORMAT_MESSAGE_GROUP
Retrieves and formats a set of messages from the advisor message table dbms_advisor.format_message_group(
group_id IN NUMBER,
msg_type IN NUMBER := 0)
RETURN VARCHAR2;
TBD
 
GET_ACCESS_ADVISOR_DEFAULTS
Returns default task and workload id numbers for the Access Advisor. This routine is typically only called by the GRID Control SQL Access Advisor Wizard dbms_advisor.get_access_advisor_defaults(
task_name     OUT VARCHAR2,
task_id_num   OUT NUMBER,
workload_name OUT VARCHAR2,
work_id_num   OUT NUMBER);
See Demo Below
 
GET_REC_ATTRIBUTES
Retrieves an existing recommendation attribute for the specified task dbms_advisor.get_rec_attributes(
task_name       IN  VARCHAR2,
rec_id          IN  NUMBER,
action_id       IN  NUMBER,
attribute_name  IN  VARCHAR2,
value           OUT VARCHAR2,
owner_name      IN  VARCHAR2 := NULL);
See Demo Below
 
GET_TASK_REPORT
Creates and returns a report for the specified task dbms_advisor.get_task_report (
task_name      IN VARCHAR2,
type           IN VARCHAR2 := 'TEXT',
level          IN VARCHAR2 := 'TYPICAL',
section        IN VARCHAR2 := 'ALL',
owner_name     IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL,
object_id      IN NUMBER   := NULL)
RETURN CLOB;
DECLARE
 buf CLOB;
BEGIN
  buf := dbms_advisor.get_task_report('UW Task', 'TEXT', 'ALL');
  dbms_output.put_line(buf);
END;
/
DECLARE
*
ERROR at line 1:
ORA-13699: Advisor feature is not currently implemented.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2738
ORA-06512: at "SYS.DBMS_ADVISOR", line 585
ORA-06512: at line 4
 
GET_TASK_SCRIPT
Creates and returns executable script for the specified task dbms_advisor.get_task_script(
task_name      IN VARCHAR2,
type           IN VARCHAR2 := 'IMPLEMENTATION',
rec_id         IN NUMBER   := NULL,
act_id         IN NUMBER   := NULL,
owner_name     IN VARCHAR2 := NULL,
_name IN VARCHAR2 := NULL,
object_id      IN NUMBER   := NULL)
RETURN CLOB;
See Demo Below
 
IMPLEMENT_TASK
Implements the recommendations of the specified task dbms_advisor.implement_task(
task_name     IN VARCHAR2,
rec_id        IN NUMBER  := NULL,
exit_on_error IN BOOLEAN := NULL);
See Demo Below
 
IMPORT_SQLWKLD_SCHEMA
Constructs and loads a SQL workload based on schema evidence

Deprecated in Database 11g		 dbms_advisor.import_sqlwkld_schema(
workload_name IN  VARCHAR2,
import_mode   IN  VARCHAR2 := 'NEW',  -- APPEND or REPLACE
priority      IN  NUMBER   := 2,      -- 1=HIGH, 2=MEDIUM, 3=LOW
saved_rows    OUT NUMBER,
failed_rows   OUT NUMBER);
conn sh/sh@pdbdev

DECLARE
 wkld_name VARCHAR2(30) := 'UW Wkld';
 saved     NUMBER;
 failed    NUMBER;
BEGIN
  dbms_advisor.create_sqlwkld(wkld_name, 'Demo Description');

  dbms_advisor.set_sqlwkld_parameter(wkld_name, 'VALID_TABLE_LIST', 'SH.%');

  dbms_advisor.import_sqlwkld_schema(wkld_name, 'APPEND', 1, saved, failed);

  dbms_output.put_line(TO_CHAR(saved));
  dbms_output.put_line(TO_CHAR(failed));
END;
/

exec dbms_advisor.delete_sqlwkld('UW Wkld');
 
IMPORT_SQLWKLD_SQLCACHE
Imports data into a workload from the current SQL cache

Deprecated in Database 11g		 dbms_advisor.import_sqlwkld_sqlcache(
workload_name IN  VARCHAR2,
import_mode   IN  VARCHAR2 := 'NEW',
priority      IN  NUMBER   := 2,
saved_rows    OUT NUMBER,
failed_rows   OUT NUMBER);
conn sh/sh@pdbdev

DECLARE
 wkld_name VARCHAR2(30) := 'UW Wkld';
 saved     NUMBER;
 failed    NUMBER;
BEGIN
  dbms_advisor.create_sqlwkld(wkld_name, 'Demo Description');

  dbms_advisor.set_sqlwkld_parameter(wkld_name, 'VALID_TABLE_LIST', 'SH.%');

  dbms_advisor.import_sqlwkld_sqlcache(wkld_name, 'REPLACE', 1, saved, failed);

  dbms_output.put_line(saved);
  dbms_output.put_line(failed);
END;
/

exec dbms_advisor.delete_sqlwkld('UW Wkld');
 
IMPORT_SQLWKLD_STS
Imports data into a workload from a SQL Tuning Set

Overload 11g

Overload 1		 dbms_advisor.import_sqlwkld_sts (
workload_name  IN  VARCHAR2,
sts_owner      IN  VARCHAR2,
sts_name       IN  VARCHAR2,
import_mode    IN  VARCHAR2 := 'NEW',
priority       IN  NUMBER   := 2,
saved_rows     OUT NUMBER,
failed_rows    OUT NUMBER);
conn sys@pdbdev as sysdba

GRANT administer sql tuning set TO sh;
GRANT advisor TO sh;

conn sh/sh@pdbdev

set serveroutput on

-- view a list of available snapshots
set feedback off
set heading on
set linesize 1500
set termout on
set trim on
set trimspool on
set veri off
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

-- create and load a SQLSET
DECLARE
 l_cursor dbms_sqltune.sqlset_cursor;
BEGIN
  dbms_sqltune.create_sqlset('SH_TSet1', 'Test Tuning Set', 'SH');

  OPEN l_cursor FOR
  SELECT VALUE(p)
  FROM TABLE (dbms_sqltune.select_workload_repository (
  4006, -- begin_snap
  4014, -- end_snap
  NULL, -- basic_filter
  NULL, -- object_filter
  NULL, -- ranking_measure1
  NULL, -- ranking_measure2
  NULL, -- ranking_measure3
  NULL, -- result_percentage
  10) -- result_limit
  ) p;

  dbms_sqltune.load_sqlset(sqlset_name => 'SH_TSet1', populate_cursor => l_cursor);
END;
/

desc user_sqlset

col description format a25

SELECT name, description, created, statement_count
FROM user_sqlset;

DECLARE
 wkld_name VARCHAR2(30) := 'My Wkld01';
 saved     NUMBER;
 failed    NUMBER;
BEGIN
  dbms_advisor.create_sqlwkld(wkld_name, 'Demo Description');

  dbms_advisor.set_sqlwkld_parameter(wkld_name, 'VALID_TABLE_LIST', 'SH.%');

  dbms_advisor.import_sqlwkld_sts(wkld_name, 'SH_TSet1', 'REPLACE', 1, saved, failed);

  dbms_output.put_line(saved);
  dbms_output.put_line(failed);
END;
/

desc user_advisor_sqlw_sum

SELECT workload_name, create_date, num_select_stmt, num_update_stmt,
num_delete_stmt, num_insert_stmt,num_merge_stmt, source, how_created, data_source
FROM user_advisor_sqlw_sum;

desc user_advisor_sqlw_parameters

col workload_name format a15
col parameter_value format a30

SELECT workload_name, parameter_name, parameter_value, parameter_type
FROM user_advisor_sqlw_parameters;

desc user_advisor_sqlw_journal

col journal_entry format a50

SELECT workload_name, journal_entry_seq, journal_entry_type, journal_entry
FROM user_advisor_sqlw_journal;

exec dbms_advisor.delete_sqlwkld('My Wkld01');
exec dbms_sqltune.drop_sqlset('SH_TSet1', 'SH');
Overload 2 dbms_advisor.import_sqlwkld_sts(
workload_name IN  VARCHAR2,
sts_name      IN  VARCHAR2,
import_mode   IN  VARCHAR2 := 'NEW',
priority      IN  NUMBER   := 2,
saved_rows    OUT NUMBER,
failed_rows   OUT NUMBER);
TBD
 
IMPORT_SQLWKLD_SUMADV
Imports data into a workload from a 9i Summary Advisor workload

Deprecated in Database 11g		 dbms_advisor.import_sqlwkld_sumadv(
workload_name IN  VARCHAR2,
import_mode   IN  VARCHAR2 := 'NEW',
priority      IN  NUMBER := 2,
sumadv_id     IN  NUMBER,
saved_rows    OUT NUMBER,
failed_rows   OUT NUMBER);
conn sh/sh@pdbdev

DECLARE
 wkld_name VARCHAR2(30) := 'UW Wkld';
 saved     NUMBER;
 failed    NUMBER;
 sumadv_id NUMBER := 394;
BEGIN
  dbms_advisor.create_sqlwkld(wkld_name, 'Imported 9i Workload');

  dbms_advisor.set_sqlwkld_parameter(wkld_name, 'VALID_TABLE_LIST', 'SH.%');

  dbms_advisor.import_sqlwkld_sumadv(wkld_name, 'REPLACE', 1, sumadv_id, saved, failed);

  dbms_output.put_line(saved);
  dbms_output.put_line(failed);
END;
/

exec dbms_advisor.delete_sqlwkld('UW Wkld');
 
IMPORT_SQLWKLD_USER
Collects a SQL workload from a specified user table

Deprecated in Database 11g		 dbms_advisor.import_sqlwkld_user(
workload_name IN  VARCHAR2,
import_mode   IN  VARCHAR2 := 'NEW',
owner_name    IN  VARCHAR2,
table_name    IN  VARCHAR2,
saved_rows    OUT NUMBER,
failed_rows   OUT NUMBER);
conn sh/sh@pdbdev

CREATE TABLE test (
username VARCHAR2(30),
sql_text VARCHAR2(1000));

INSERT INTO test
SELECT 'SH', sql_text
FROM gv$sql
WHERE rownum < 1001;

DECLARE
 wkld_name VARCHAR2(30) := 'UW Wkld';
 saved     NUMBER;
 failed    NUMBER;
BEGIN
  dbms_advisor.create_sqlwkld(wkld_name, 'Demo Description');

  dbms_advisor.set_sqlwkld_parameter(wkld_name, 'VALID_TABLE_LIST', 'SH.%');

  dbms_advisor.import_sqlwkld_user(wkld_name, 'REPLACE', 'SH', 'TEST', saved, failed);

  dbms_output.put_line(saved);
  dbms_output.put_line(failed);
END;
/

SELECT workload_name, journal_entry
FROM user_advisor_sqlw_journal;

exec dbms_advisor.delete_sqlwkld('UW Wkld');
 
INSERT_DIRECTIVE
Creates an instance of a known directive dbms_advisor.insert_directive(
directive_id  IN NUMBER,
instance_name IN VARCHAR2,
task_name     IN VARCHAR2,
document      IN CLOB);
TBD
 
INTERRUPT_TASK
Stops a currently executing task. The task will end its operations as it would at a normal exit. The user will be able to access any recommendations that exist to this point. dbms_advisor.interrupt_task(task_name IN VARCHAR2);
SELECT task_name
FROM dba_advisor_tasks
WHERE owner = 'UWCLASS';

exec dbms_advisor.interrupt_task('UW_TASK');
 
MARK_RECOMMENDATION
Sets the annotation_status for a specific recommendation dbms_advisor.mark_recommendation(
task_name IN VARCHAR2,
id        IN NUMBER,
action    IN VARCHAR2);
conn sh/sh@pdbdev

DECLARE
 task_id   NUMBER;
 task_name VARCHAR2(30) := 'UW Task';
 wkld_name VARCHAR2(30) := 'UW Wkld';
 attribute VARCHAR2(100);
 rec_id    NUMBER;
BEGIN
  dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor, task_id, task_name);
  dbms_advisor.create_sqlwkld(wkld_name, 'Demo Description');
  dbms_advisor.add_sqlwkld_ref(task_name, wkld_name);

  dbms_advisor.add_sqlwkld_statement(wkld_name, 'MONTHLY', 'ROLLUP', 100, 400, 5041, 103, 640445, 680000, 2, 1,
  SYSDATE, 1, 'SH', 'SELECT AVG(amount_sold) FROM sh.sales WHERE promo_id = 10');

  dbms_advisor.execute_task(task_name);
  rec_id := 1;
  dbms_advisor.mark_recommendation(task_name, rec_id, 'REJECT');
END;
/

-- need to show how the marking works in the DD

exec dbms_advisor.delete_task('UW Task');
exec dbms_advisor.delete_sqlwkld('UW Wkld');
 
QUICK_TUNE
Performs an analysis and generates recommendations for a single SQL statement based on 1 to 3 simple attributes dbms_advisor.quick_tune(
advisor_name IN VARCHAR2,
task_name    IN VARCHAR2,
attr1        IN CLOB     := NULL,
attr2        IN VARCHAR2 := NULL,
attr3        IN NUMBER   := NULL,
template     IN VARCHAR2 := NULL,
implement    IN BOOLEAN  := FALSE,
description  IN VARCHAR2 := NULL);
desc user_advisor_templates

set linesize 121
col task_name format a10
col description format a50

SELECT task_name, description
FROM user_advisor_templates;

desc user_advisor_journal

SELECT COUNT(*)
FROM user_advisor_journal;

DECLARE
 task_name VARCHAR2(30) := 'UW Task';
BEGIN
  dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor, task_name,
  'SELECT AVG(amount_sold) FROM sh.sales WHERE promo_id=350');
END;
/

SELECT COUNT(*)
FROM user_advisor_journal;

desc user_advisor_journal

col journal_entry format a65

SELECT task_name, journal_entry_type, journal_entry
FROM user_advisor_journal
WHERE task_name = 'UW Task';

desc user_advisor_recommendations

-- Grid Control recommends the actions be accepted
SELECT type, rank, benefit, annotation_status
FROM user_advisor_recommendations
WHERE task_name = 'UW Task';

col command format a30
col attr1 format a25
col attr3 format a25
col attr4 format a25
col attr5 format a25
col attr6 format a25
col error_message format a15

desc user_advisor_actions

-- view the recommended actions
SELECT command, attr1, attr3, attr4
FROM user_advisor_actions
WHERE task_name = 'UW Task';

set long 100000
col attr5 format a100

SELECT attr5
FROM user_advisor_actions;
WHERE task_name = 'UW Task';

desc user_advisor_log

SELECT execution_start, execution_end, status, error_message
FROM user_advisor_log
WHERE task_name = 'UW Task';

desc user_advisor_tasks

col description format a15
col adivsor_name format a15

SELECT description, advisor_name, created, status, recommendation_count,
source, how_created
FROM user_advisor_tasks
WHERE task_name = 'UW Task';

exec dbms_advisor.delete_task('UW Task');
set autotrace on

SELECT AVG(amount_sold) FROM sales WHERE promo_id=350;

SELECT STATEMENT     |       |   1 |    9 | 177 (20)
SORT AGGREGATE       |       |   1 |    9 |
PARTITION RANGE ALL  |       | 229K| 2018K| 177 (20)
TABLE ACCESS FULL    | SALES | 229K| 2018K| 177 (20)
----------------------------------------------------
Statistics
----------------------------------------------------
1 recursive calls
0 db block gets
1718 consistent gets
1647 physical reads
0 redo size
438 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

-- implement recommended actions
CREATE MATERIALIZED VIEW LOG ON sales WITH rowid, sequence
(promo_id, amount_sold) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW demomv
BUILD IMMEDIATE
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE AS
SELECT promo_id c1, SUM(amount_sold) m1, COUNT(amount_sold) m2,
COUNT(*) m3
FROM sales
GROUP BY promo_id;

-- gather table statistics
exec dbms_stats.gather_table_stats(USER, 'DEMOMV', NULL, dbms_stats.auto_sample_size, CASCADE=>TRUE);

SELECT AVG(amount_sold) FROM sales WHERE promo_id=350;

SELECT STATEMENT            |        | 1 | 16 | 2 (0)
SORT AGGREGATE              |        | 1 | 16 |
MAT_VIEW REWRITE ACCESS FULL| DEMOMV | 1 | 16 | 2 (0)
-----------------------------------------------------
Statistics
-----------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
438 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
 
RESET_SQLWKLD
Resets a workload to its initial state. All journal and log messages are cleared.
Workload statements will be revalidated.

Deprecated in Database 11g		 dbms_advisor.reset_sqlwkld(workload_name IN VARCHAR2);
conn sh/sh@pdbdev

SELECT DISTINCT workload_name
FROM user_advisor_sqlw_parameters;

DECLARE
 wkld_name VARCHAR2(30) := 'UW Wkld';
BEGIN
  dbms_advisor.create_sqlwkld(wkld_name, 'Demonstration Workload');
END;
/

SELECT DISTINCT workload_name
FROM user_advisor_sqlw_parameters;

set linesize 121
col workload_name format a20
col parameter_name format a25
col parameter_value format a35
col description format a65

SELECT parameter_name, parameter_value, parameter_type
FROM user_advisor_sqlw_parameters
WHERE workload_name = 'UW Wkld';

SELECT parameter_name, description
FROM user_advisor_sqlw_parameters
WHERE workload_name = 'UW Wkld';

desc user_advisor_sqlw_stmts

SELECT cpu_time, elapsed_time, disk_reads, buffer_gets
FROM user_advisor_sqlw_stmts;

DECLARE
  wkld_name VARCHAR2(30) := 'UW Wkld';
BEGIN
  dbms_advisor.add_sqlwkld_statement(wkld_name, 'MONTHLY', 'ROLLUP', 100, 400, 5041, 103, 640445, 680000, 2, 1, SYSDATE, 1, 'SH', 'SELECT avg(amount_sold) FROM sh.sales WHERE promo_id = 10');
END;
/

SELECT cpu_time, elapsed_time, disk_reads, buffer_gets, rows_processed, optimizer_cost, executions, priority
FROM user_advisor_sqlw_stmts;

SELECT command_type, sql_text
FROM user_advisor_sqlw_stmts;

BEGIN
  -- add wkld to task
  dbms_advisor.execute_task('UW Task');
END;
/

-- look at the recommendations

BEGIN
  dbms_advisor.reset_sqlwkld('UW Wkld');
END;
/

BEGIN
  dbms_advisor.delete_sqlwkld('UW Wkld');
  dbms_advisor.delete_task('UW Task');
END;
/
 
RESET_TASK
Resets a task to its initial state. All intermediate and recommendation data are deleted dbms_advisor.reset_task(task_name IN VARCHAR2);
conn sh/sh@pdbdev

DECLARE
 task_id   NUMBER;
 task_name VARCHAR2(30) := 'UW Task';
BEGIN
  dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor, task_id, task_name);

  dbms_advisor.execute_task(task_name);

  dbms_advisor.reset_task(task_name);
END;
/
 
RESUME_TASK
Resumes a previously interrupted advisor task dbms_advisor.resume_task(task_name IN VARCHAR2);
exec dbms_advisor.resume_task('UW Task');
 
SETUP_REPOSITORY
Sets up advisor framework repository for use dbms_advisor.setup_repository;
exec dbms_advisor.setup_repository;
 
SETUP_USER_ENVIRONMENT
Setups up the user environment for OEM Grid or Cloud Control dbms_advisor.setup_user_environment(advisor_name IN VARCHAR2);
exec dbms_advisor.setup_user_environment('SQLACCESS_GENERAL');
 
SET_DEFAULT_SQLWKLD_PARAMETER
Sets the specified parameter value as default for all new SQL workload objects
Deprecated in Database 11g
Overload 1		 dbms_advisor.set_default_sqlwkld_parameter(
parameter IN VARCHAR2,
value     IN VARCHAR2);
TBD
Deprecated in Database 11g
Overload 2		 dbms_advisor.set_default_sqlwkld_parameter(
parameter IN VARCHAR2,
value     IN NUMBER);
TBD
 
SET_DEFAULT_TASK_PARAMETER
Sets the specified task parameter value as default for all new tasks of a specific type

Overload 1		 dbms_advisor.set_default_task_parameter(
advisor_name IN VARCHAR2,
parameter    IN VARCHAR2,
value        IN VARCHAR2);
TBD
Overload 2 dbms_advisor.set_default_task_parameter(
advisor_name IN VARCHAR2,
parameter    IN VARCHAR2,
value        IN NUMBER);
TBD
 
SET_SQLWKLD_PARAMETER
Sets the value of a workload parameter

Deprecated in Database 11g

Overload 1		 dbms_advisor.set_sqlwkld_parameter(
workload_name IN VARCHAR2,
parameter     IN VARCHAR2,
value         IN VARCHAR2);
See IMPORT_SQLWKLD_SCHEMA Demo
Deprecated in Database 11g

Overload 2		 dbms_advisor.set_sqlwkld_parameter(
workload_name IN VARCHAR2,
parameter     IN VARCHAR2,
value         IN NUMBER);
TBD
 
SET_TASK_PARAMETER
Sets the specified task parameter value

Overload 1		 dbms_advisor.set_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value     IN VARCHAR2);
See Demo Below
Overload 2 dbms_advisor.set_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value     IN NUMBER);

Parameter Name Comment
DBID Optional: Defaults to current database
DBIO_EXPECTED Optional: Defaults to 10,000 microseconds
END_SNAPSHOT Mandatory
INSTANCE Optional: Defaults to current instance
START_SNAPSHOT Mandatory
-- set the average time it takes to read a single database block in microseconds for the ADDM

exec dbms_advisor.set_default_task_parameter('ADDM', 'DBIO_EXPECTED', 8000);
 
TUNE_MVIEW
Shows how to decompose a materialized view into two or more materialized views and to restate the materialized view in a way that is more advantageous for fast refresh and query rewrite. It also shows how to fix materialized view logs and to enable query rewrite. dbms_advisor.tune_mview(
task_name      IN OUT VARCHAR2,
mv_create_stmt IN     CLOB);
conn sh/sh@pdbdev

desc user_tune_mview

SELECT *
FROM user_tune_mview;

set serveroutput on

-- failure is a success
DECLARE
 task_name VARCHAR2(30) := '';
BEGIN
  dbms_advisor.tune_mview(task_name, 'CREATE MATERIALIZED VIEW demomv
REFRESH FAST AS SELECT promo_id c1, AVG(amount_sold) FROM sales WHERE promo_id = 350 GROUP BY promo_id');
  dbms_output.put_line(task_name);
END;
/

SELECT *
FROM user_tune_mview;

-- alternatively, save the output to an external file
exec dbms_advisor.create_file (dbms_advisor.get_task_script ('TASK_2380'), 'CTEMP','tune_mview_output.sql');

-- success is a failure
DECLARE
 task_name VARCHAR2(30) := '';
BEGIN
  dbms_advisor.tune_mview(task_name, 'CREATE MATERIALIZED VIEW demomv
BUILD IMMEDIATE REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT promo_id c1, SUM(amount_sold) m1, COUNT(amount_sold) m2, COUNT(*) m3
FROM sales GROUP BY promo_id');
  dbms_output.put_line(task_name);
END;
/
 
UPDATE_DIRECTIVE
Updates an existing directive for the specified task

This appears to be internal for use by OEM Grid and Cloud only		 dbms_advisor.update_directive(
directive_id  IN NUMBER,
instance_name IN VARCHAR2,
task_name     IN VARCHAR2,
document      IN CLOB);
TBD
 
UPDATE_OBJECT
Updates an existing task object. Parameters that are NULL will have no effect on the existing value of the column. dbms_advisor.update_object(
task_name IN VARCHAR2,
object_id IN NUMBER,
attr1     IN VARCHAR2 := NULL,
attr2     IN VARCHAR2 := NULL,
attr3     IN VARCHAR2 := NULL,
attr4     IN CLOB     := NULL,
attr5     IN VARCHAR2 := NULL);
TBD
 
UPDATE_REC_ATTRIBUTES
Updates an existing recommendation for the specified task dbms_advisor.update_rec_attributes(
task_name      IN VARCHAR2,
rec_id         IN NUMBER,
action_id      IN NUMBER,
attribute_name IN VARCHAR2,
value          IN VARCHAR2);
TBD
 
UPDATE_SQLWKLD_ATTRIBUTES
Updates a workload object

Deprecated in Database 11g		 dbms_advisor.update_sqlwkld_attributes(
workload_name IN VARCHAR2,
new_name      IN VARCHAR2 := NULL,
description   IN VARCHAR2 := NULL,
read_only     IN VARCHAR2 := NULL,
is_template   IN VARCHAR2 := NULL,
how_created   IN VARCHAR2 := NULL);
TBD
 
UPDATE_SQLWKLD_STATEMENT
Updates one or more SQL statements in a workload

Deprecated in Database 11g

Overload 1		 dbms_advisor.update_sqlwkld_statement(
workload_name IN VARCHAR2,
sql_id        IN NUMBER,
application   IN VARCHAR2 := NULL,
action        IN VARCHAR2 := NULL,
priority      IN NUMBER   := NULL,
username      IN VARCHAR2 := NULL);
TBD
Overload 2

Deprecated in Database 11g		 dbms_advisor.update_sqlwkld_statement(
workload_name IN  VARCHAR2,
search        IN  VARCHAR2,
updated       OUT NUMBER,
application   IN  VARCHAR2 := NULL,
action        IN  VARCHAR2 := NULL,
priority      IN  NUMBER   := NULL,
username      IN  VARCHAR2 := NULL);
TBD
 
UPDATE_TASK_ATTRIBUTES
Updates a task's attributes dbms_advisor.update_task_attributes(
task_name   IN VARCHAR2,
new_name    IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
read_only   IN VARCHAR2 := NULL,
is_template IN VARCHAR2 := NULL,
how_created IN VARCHAR2 := NULL);
TBD
 
Demo
Tuning Demo conn sys@pdbdev as sysdba

SELECT *
FROM dba_advisor_usage
ORDER BY last_exec_time;

conn sh/sh@pdbdev

SELECT task_name
FROM dba_advisor_tasks
WHERE owner = 'SH';

col startup_time format a30

SELECT snap_id, startup_time
FROM dba_hist_snapshot
ORDER BY 1,2;

set serveroutput on

-- this will fail: watch the error
DECLARE
 task_id   NUMBER;
 task_name VARCHAR2(30) := 'UW Task';
 wkld_name VARCHAR2(30) := 'UW Workload';
BEGIN
  dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor, task_id, task_name);

  dbms_output.put_line(task_id);

  dbms_advisor.set_task_parameter('UW Task', 'START_SNAPSHOT', 999);
  dbms_advisor.set_task_parameter('UW Task', 'END_SNAPSHOT', 1013);
  dbms_advisor.set_task_parameter('UW Task', 'INSTANCE', 1);
  dbms_advisor.create_sqlwkld(wkld_name, 'UW Workload');
  dbms_advisor.add_sqlwkld_ref(task_name, wkld_name);
  dbms_advisor.add_sqlwkld_statement(wkld_name, 'MONTHLY', 'ROLLUP',100, 400, 5041, 103, 640445, 680000, 2, 1, SYSDATE, 1, 'SH', 'SELECT avg(amount_sold) FROM sh.sales');
/****
  dbms_advisor.add_sqlwkld_statement('To Delete', 'WEEKLY', 'ROLLUP',100, 400, 5041, 103, 640445, 680000, 2, 1, SYSDATE,  1, 'SH', 'SELECT AVG(amount_sold) FROM sh.sales');
  dbms_advisor.update_sqlwkld_statement('To Delete', DBMS_ADVISOR.ADVISOR_ALL, ADVISOR_UNUSED, NULL, 3);
  dbms_advisor.delete_sqlwkld_statement('To Delete', DBMS_ADVISOR.ADVISOR_ALL);
****/
  dbms_advisor.execute_task('UW Task');
END;
/

-- grant the missing privilege
conn sys@pdbdev as sysdba

grant advisor to sh;

conn sh/sh@pdbdev

DECLARE
 task_id   NUMBER;
 task_name VARCHAR2(30) := 'UW Task';
 wkld_name VARCHAR2(30) := 'UW Workload';
BEGIN
  dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor, task_id, task_name);

  dbms_output.put_line(task_id);

  dbms_advisor.set_task_parameter('UW Task', 'START_SNAPSHOT', 999);
  dbms_advisor.set_task_parameter('UW Task', 'END_SNAPSHOT', 1013);
  dbms_advisor.set_task_parameter('UW Task', 'INSTANCE', 1);
  dbms_advisor.create_sqlwkld(wkld_name, 'UW Workload');
  dbms_advisor.add_sqlwkld_ref(task_name, wkld_name);
  dbms_advisor.add_sqlwkld_statement(wkld_name, 'MONTHLY', 'ROLLUP', 100, 400, 5041, 103, 640445, 680000, 2, 1, SYSDATE, 1, 'SH', 'SELECT AVG(amount_sold) FROM sh.sales');

  dbms_advisor.execute_task('UW Task');
END;
/

SELECT task_name
FROM dba_advisor_tasks
WHERE owner = 'SH';

-- retrieve the advisor defaults
DECLARE
 tname    VARCHAR2(30);
 tid      NUMBER;
 wkldname VARCHAR2(30);
 wkid     NUMBER;
BEGIN
  dbms_advisor.get_access_advisor_defaults(tname,tid,wkldname,wkid);
  dbms_output.put_line(tname ||);
  dbms_output.put_line(TO_CHAR(tid));
  dbms_output.put_line(wkldname);
  dbms_output.put_line(TO_CHAR(wkid));
END;
/

-- write the recommendations to a file
SELECT *
FROM all_directories;

conn sys@pdbdev as sysdba

GRANT read, write ON DIRECTORY ctemp TO sh;

conn sh/sh@pdbdev

DECLARE
 buf CLOB;
BEGIN
  buf := dbms_advisor.get_task_script('UW Task');
  dbms_output.put_line(buf);

  dbms_advisor.create_file(buf, 'CTEMP', 'advisor_create_file.txt');
END;
/

-- retrieve the "NAME" attribute from the task
DECLARE
 val VARCHAR2(100);
BEGIN
  dbms_advisor.get_rec_attributes('UW Task', 1, 1, 'NAME', val);
  dbms_output.put_line(val);
END;
/

SELECT object_name, object_type
FROM user_objects
WHERE object_type LIKE 'MAT%';

-- this is what makes it worth the wait
SELECT rec_id, type, rank, benefit, annotation_status
FROM user_advisor_recommendations;

exec dbms_advisor.implement_task('UW Task', 1, FALSE);

SELECT object_name, object_type
FROM user_objects
WHERE object_type LIKE 'MAT%';

desc dba_advisor_tasks

SELECT task_name
FROM dba_advisor_tasks
ORDER BY 1;

exec dbms_advisor.delete_task('UW Task');

Related Topics
Built-in Functions
Built-in Packages
DBMS_ADDM
DBMS_SQLDIAG
DBMS_SQLTUNE
DBMS_WORKLOAD_REPOSITORY
PRVT_ACCESS_ADVISOR
PRVT_ADVISOR
PRVT_SQLADV_INFRA
PRVT_TUNE_MVIEW
Materialized Views
What's New In 21c
What's New In 23c

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