Use the advisory framework for indexing advice
Thursday, November 02, 2006 09:42 AM
Oracle 10g Release 2 adds a QUICK_TUNE procedure to DBMS_ADVISOR that streamlines the process of getting advice on your SQL statements.
One of the best features of Oracle 10g is its Advisory Framework, which is a collection of tuning advisors that help you get the most out of your database. One of these advisors, the SQL Access Advisor, can recommend changes in your schema's indexing or materialized views to improve performance.
But because the Advisory Framework is generalized to support many different advisors, navigating all the stored procedures and functions in the DBMS_ADVISOR package can be daunting. A CREATE_TASK procedure initializes a new tuning task, a SET_TASK_PARAMETER procedure modifies default parameters, the CREATE_SQLWKLD procedure creates a workload object, and so on.
Oracle 10g Release 2 adds a QUICK_TUNE procedure to the package. This procedure defaults most of the parameters required to set up an advisor run, streamlining the interface. And instead of building a workload object (i.e., a list of SQL Statements to tune), you submit a single SQL statement directly to the procedure.
To use the QUICK_TUNE procedure--or any other tool in the DBMS_ADVISOR package--a user must have the ADVISOR privilege. In addition, the user running the analysis must have at least SELECT permissions on the tables referenced in the query. Then, it's simply a matter of calling the procedure, as in Listing A.
You can view the results in a series of DBA_ views. DBA_ADVISOR_LOG lists your task and the number of recommendations it generated. Then the recommendations can be seen in the views DBA_ADVISOR_RECOMMENDATIONS, and the individual commands in DBA_ADVISOR_ACTIONS.
Even better, you can have DBMS_ADVISOR generate the complete SQL script needed to implement the recommended actions. The function GET_TASK_SCRIPT returns a CLOB with the script in it.
Listing B shows the output from the run in Listing A. Because the AMOUNT_SOLD column used in the WHERE clause is not indexed, a full table scan is required to process the statement. The advisor recommends creation of an index on the column. Further, it recommends that it be a bitmap index, because the data in the column has many repeating values.
Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.
Play video
There are currently no comments for this post.