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');
|