Search the Reference Library pages:  

Oracle DBMS_ADVISOR
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsadv.sql
First Available 10.1

Constants
Name Data Type Value
Advisor Names
ADV_NAME_ADDM VARCHAR2(30) 'ADDM'
ADV_NAME_DEFAULT

VARCHAR2(30)

'Default Advisor'

ADV_NAME_SEGMENT VARCHAR2(30) 'Segment 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_TUNEMV VARCHAR2(30) 'Tune MView'
ADV_NAME_UNDO VARCHAR2(30) 'Undo Advisor'
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
Common Constants
ADVISOR_ALL

NUMBER

-995

ADVISOR_CURRENT NUMBER -996
ADVISOR_DEFAULT NUMBER -997
ADVISOR_UNLIMITED NUMBER -998
ADVISOR_UNUSED NUMBER -999
SQL Access Advisor Constants
SQLACCESS_ADVISOR VARCHAR2(30) 'SQL Access Advisor'
SQLACCESS_GENERAL VARCHAR2(20) 'SQLACCESS_GENERAL'
SQLACCESS_OLTP VARCHAR2(20) 'SQLACCESS_OLTP'
SQLACCESS_WAREHOUSE VARCHAR2(20) 'SQLACCESS_WAREHOUSE'
SQLWORKLOAD_MANAGER VARCHAR2(30) 'SQL Workload Manager'
TUNE_MVIEW_ADVISOR VARCHAR2(30) 'Tune MView'
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;

Dependencies
SELECT name
FROM dba_dependencies
WHERE referenced_name = 'DBMS_ADVISOR'
UNION
SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_ADVISOR';

dba_advisor_findings dba_hist_snapshot
dba_advisor_journal dbms_sys_error
dba_advisor_objects dba_tune_mview
dba_advisor_parameters prvt_access_advisor
dba_advisor_rationale prvt_advisor
dba_advisor_recommendations prvt_tune_mview
dba_advisor_sqlw_journal prvt_workload
dba_advisor_tasks session_privs
dba_advisor_usage user_tune_mview
 
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
Exceptions ORA-13699: Advisor feature is not currently implemented

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)
init Parameters _addm_auto_enable ... use TRUE to enable auto run
Licensing and Usage SELECT advisor_name, num_execs
FROM dba_advisor_usage;

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 Execute is granted to PUBLIC
 
ADD_SQLWKLD_REF (new 11g parameter)
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);
See Demo
 
ADD_SQLWKLD_STATEMENT

Adds a single statement to the specified workload
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
 
ADD_STS_REF (new in 11g)

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,
sts_owner     IN VARCHAR2,
workload_name IN VARCHAR2);

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
 
CHECK_PRIVS
Checks for required advisor privileges dbms_advisor.check_privs;
exec dbms_advisor.check_privs;
 
CHECK_READ_PRIVS
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.
dbms_advisor.check_read_privs(owner_name IN VARCHAR2);
exec dbms_advisor.check_read_privs('UWCLASS');

exec dbms_advisor.check_read_privs('UWCLASZ');

exec dbms_advisor.check_read_privs('Invalid Value');

Note: This procedure is broken in 10g and reportedly was going to be fixed in 11g ... it wasn't.
 
COPY_SQLWKLD_TO_STS (new in 11g)

Copies workload object data into a user-specified STS. No filters are supported.
dbms_advisor.copy_sqlwkld_to_sts(
workload_name IN VARCHAR2,
sts_name      IN VARCHAR2,
import_mode   IN VARCHAR2 := 'NEW');
TBD
 
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
 
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

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 / as sysdba

grant advisor to sh;

conn sh/sh

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 / 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');
See Demo
 
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 DELETE_SQLWKLD_STATEMENT Demo

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
dbms_advisor.delete_directive(
directive_id  IN NUMBER,
instance_name IN VARCHAR2,
task_name     IN VARCHAR2 := NULL);
TBD
 
DELETE_SQLWKLD
Deletes an existing SQL Workload object from the repository dbms_advisor.delete_sqlwkld(workload_name IN VARCHAR2);
See DELETE_SQLWKLD_REF Demo
 
DELETE_SQLWKLD_REF (new 11g parameter)

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 := 2);
conn sh/sh

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_sqlwkld('UW Wkld');
  dbms_advisor.delete_task('UW Task');
END;
/
 
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);
See Demo
Overload 2 dbms_advisor.delete_sqlwkld_statement(
workload_name IN  VARCHAR2,
search        IN  VARCHAR2,
deleted       OUT NUMBER);
TBD
 
DELETE_STS_REF (new in 11g)

Removes a workload reference from the specified task
dbms_advisor.delete_sts_ref(
task_name     IN VARCHAR2,
sts_owner     IN VARCHAR2,
workload_name IN VARCHAR2);
TBD
 
DELETE_TASK
Deletes the specified task from the repository dbms_advisor.delete_task(task_name IN VARCHAR2);
See DELETE_SQLWKLD_REF Demo
 
EVALUATE_DIRECTIVE (new in 11g)

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 (overload 2 new in 11g)
Performs the Advisor analysis or evaluation for the specified task

Overload 1
dbms_advisor.execute_task(task_name IN VARCHAR2);
See Demo

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) 
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 (new 11g parameter)
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
 
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
 
GET_TASK_REPORT (new parameters in 11g)

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 (new parameters in 11g)

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,
execution_name IN VARCHAR2 := NULL,
object_id      IN NUMBER   := NULL)
RETURN CLOB;
See Demo
 
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
 
IMPORT_DIRECTIVES

Imports directives into the specified task. Directives are created from the recommendations of another executed task
dbms_advisor.import_directive(
task_name   IN  VARCHAR2,
from_name   IN  VARCHAR2,
import_mode IN  VARCHAR2,
accepted    OUT NUMBER,
rejected    OUT NUMBER);
TBD
 
IMPORT_SQLWKLD_SCHEMA

Constructs and loads a SQL workload based on schema evidence
dbms_advisor.import_sqlwkld_schema(
workload_name IN  VARCHAR2,
import_mode   IN  VARCHAR2 := 'NEW',  -- APPEND or REPLACE
priority      IN  NUMBER   := 2,
saved_rows    OUT NUMBER,
failed_rows   OUT NUMBER);
conn sh/sh

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

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
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 sh/sh

-- a sqlset must be created too

set serveroutput on

DECLARE
 wkld_name VARCHAR2(30) := 'My 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_sts(wkld_name, 'MY_SQLSET', 'REPLACE', 1,
  saved, failed);

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

exec dbms_advisor.delete_sqlwkld('My Wkld');
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
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

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

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 (new in 11g)

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

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 131
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 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 a20
col attr3 format a25
col attr4 format a20
col attr5 format a20
col attr6 format a20
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

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
dbms_advisor.reset_sqlwkld(workload_name IN VARCHAR2);
conn sh/sh

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

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 task dbms_advisor.resume_task(task_name IN VARCHAR2);
exec dbms_advisor.resume_task('UW Task');
 
SET_DEFAULT_SQLWKLD_PARAMETER
Sets the specified parameter value as default for all new SQL workload objects

Overload 1
dbms_advisor.set_default_sqlwkld_parameter(
parameter IN VARCHAR2,
value     IN VARCHAR2);
TBD
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

Overload 1
dbms_advisor.set_sqlwkld_parameter(
workload_name IN VARCHAR2,
parameter     IN VARCHAR2,
value         IN VARCHAR2);
See IMPORT_SQLWKLD_SCHEMA Demo
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

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);
 
SETUP_REPOSITORY
Sets up advisor framework repository for use dbms_advisor.setup_repository;
exec dbms_advisor.setup_repository;
 
SETUP_USER_ENVIRONMENT
Setups up user environment for the Grid Control. Typically, it is not necessary to call this routine as
user setup is automatically done when a user creates a task. However, EM needs the environment set up prior to creating a task.
dbms_advisor.setup_user_environment(advisor_name IN VARCHAR2);
exec dbms_advisor.setup_user_environment('SQLACCESS_GENERAL');
 
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

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 (parameter changes in 11g?)

Updates an existing directive for the specified task

This appears to be internal for use by OEM Grid 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
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

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);
See Demo

Overload 2
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 / as sysdba

SELECT *
FROM dba_advisor_usage
ORDER BY last_exec_time;

conn sh/sh

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 / as sysdba

grant advisor to sh;

conn sh/sh

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 / as sysdba

GRANT read, write ON DIRECTORY ctemp TO sh;

conn sh/sh
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
DBMS_SQLDIAG
DBMS_SQLTUNE
DBMS_WORKLOAD_REPOSITORY
Grid Control
Materialized Views
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----