 |
|
dbms_advisor tips
Oracle Tips by Burleson |
Oracle 10g popular dbms_advisor procedures
Let's take a closer look at some of the most
popular dbms_advisor procedures. For a full list of available
procedures, we suggest consulting the Oracle Manual ‘PL/SQL Packages
and Types Reference’.
Stage 1 uses the following procedures to
manage the SQLAccess Task and workload object.
-
create_task - Creates a new Advisor
task in the repository.
-
delete_task - Deletes the specified
SQLAccess task from the repository.
-
update_task_attributes - Makes
changes to the existing tasks
-
create_sqlwkld - Creates a new
workload object
-
delete_sqlwkld - Deletes an entire
workload object
-
quick_tune - The operation creates a
task using the specified task name. The task will be created
using either a specified SQLAccess task template or the built-in
default template of sqlaccess_general. This procedure performs
all of the necessary operations to analyze a single SQL
statement. The operation creates a task for which all parameters
are defaulted.
Stage 2 uses the following procedures to
make changes to the workload object.
-
import_sqlwkld_sqlcache - Imports
data into a workload from the SQL cache
-
add_sqlwkld_statement - Adds a
single statement to a workload.
-
delete_sqlwkld_statement - Deletes
one or more statements from a workload.
-
update_sqlwkld_statement - Updates
one or more SQL statements in a workload.
Stage 3 uses the following procedures to
set and reset parameters related to tasks and workload objects.
-
set_task_parameter - Modifies a user
parameter within a SQLAccess task or a template.
-
set_sqlwkld_parameter - Sets the
value of a workload parameter
-
reset_task - Resets a task to its
initial state. All intermediate and recommendation data will be
deleted.
-
reset_sqlwkld - Resets a workload to
its initial state. All journal and log messages are cleared.
Workload data will remain untouched.
Stage 4 uses the following procedures to
handle the SQL Workload and the execution of analysis.
-
add_sqlwkld_ref - Establishes a link
between the current SQLAccess task and a SQLWkld data object.
The link allows an Advisor task to access relevant data for the
purpose of doing an analysis.
-
delete_sqlwkld_ref - Removes a link
between the current SQLAccess task and a SQLWkld data object.
-
execute_task - Performs the
SQLAccess Advisor analysis or evaluation for the specified task.
Task execution is a synchronous operation.
-
Oracle 10g popular dbms_advisor procedures
-
interrupt_task - Stops a currently
executing task. The task will end its operations as it would at a
normal exit.
-
cancel_task - Causes a currently
executing operation to terminate. This call does a soft interrupt.
-
mark_recommendation - Marks a
recommendation for import or implementation.
-
update_rec_attributes - Updates the
owner and name of a new object as recommended by Advisor analysis.
-
create_task_script - Creates a
SQL*Plus-compatible SQL script and sends the output to file. The
script will contain all of the accepted recommendations from the
specified task.
The popular procedures and basic workflow
stages are shown in Figure 16.2
Fig
16.2 Procedures and their relation with tasks and workload
objects
|
