 |
|
Oracle Tips by Burleson |
Oracle 10g An Illustration of SQLAccess Advisor
Procedures
In this section, we show some simple examples to
illustrate the usage of the procedures while creating and analyzing
SQL tuning sessions.
By using the create_task procedure, a task with
the description my_sample is created. task_id and task_n are
returned by the application.
DBMS_ADVISOR.CREATE_TASK
(DBMS_ADVISOR.SQLACCESS_ADVISOR, :task_id, :task_n, 'MY_Sample',
DBMS_ADVISOR.SQLACCESS_GENERAL);
Using the create_sqlwkld procedure, a workload
object is created.
DBMS_ADVISOR.CREATE_SQLWKLD(:workload_n, 'My current application
workload');
The import_sqlwkld_sqlcache procedure loads the
new sqlwkld object (workload_n) from the server’s SQL Cache.
DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE(:workload_n,'NEW','HIGH',:saved_rows,:failed_rows);
The add_sqlwkld_ref procedure establishes a
link between the current SQLAccess task (task_n) and the SQLWkld
data object (workload_n).
DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_n,:workload_n);
The set_sqlwkld_parameter procedure specifies
that the workload_scope should be PARTIAL.
DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(:workload_n, 'WORKLOAD_SCOPE',
'PARTIAL');
The procedure execute_task starts the task
execution. task_n is provided to identify the corresponding task.
DBMS_ADVISOR.EXECUTE_TASK(:task_n);
You can monitor the progress with the following
statement.
SELECT
pct_completion_time
FROM dba_advisor_log WHERE task_name = :task_n;
Get the complete Oracle10g story:
To get the code instantly, click here:
Need an Oracle Mentor?
BEI is now offering personal mentors for Oracle DBAs where you can have an
Oracle expert right at your fingertips, anytime day or night. We work with
hundreds of Oracle databases every year, so we know exactly how to quickly
assist you with any Oracle question.
Why risk an unplanned outage? You can now get telephone access to Don
Burleson or any of his Oracle Certified DBAs with more than 20 years of
full-time IT experience. Click here for details:
http://www.dba-oracle.com/service_oracle_backup.htm
|
