 |
|
Oracle Tips by Burleson |
Oracle 10g Define the Workload
The workload consists of one or more SQL
statements, plus various statistics and attributes that fully
describe each statement. This becomes the primary input and basis
for analysis for the SQLAccess Advisor. The workload is stored as a
separate object, which is created using the
dbms_advisor.create_sqlwkld procedure.
Because the workload is independent, it must be
linked to a task using the dbms_advisor.add_sqlwkld_ref procedure.
Before analysis, you need to set the parameters
or guidelines that permit you to control and influence tuning
decisions. The guidelines range from various resource limits to
choosing where new indexes and materialized views may be placed.
Parameters are set in the SQLAccess task and the workload.
Generate the Recommendations
After creating the task, linking it to the
workload, and setting the appropriate parameters, recommendations
can be generated using the dbms_advisor.execute_task procedure.
These recommendations are stored in the SQLAccess Advisor
Repository.
Implement the Recommendations
You can view the recommendations from the
SQLAccess Advisor by using the catalog views or by generating a
script using the dbms_advisor.get_task_script procedure. You can
also use the Enterprise Manager to display the recommendations, once
the SQLAccess Advisor process has completed. It is left to your
discretion whether to implement the suggestions.
The above stages can be depicted in a diagram
of workflows as shown in Figure 16.1.
Figure 16.1 Tuning Exercise with SQLAccess Advisor
The SQL Access Advisor uses the common workload repository used by
other advisors. It can take a workload derived from many sources as
shown below:
-
SQL Cache - where it takes the current
contents of v$sql
-
User Defined - You specify your workload in
an input table
-
Hypothetical - SQL Access Advisor generates
a likely workload from your dimensional model
-
STS (SQL Tuning Set) - From the workload
repository
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
|
