10G SQL Access Advisor

If you had to answer the following question: "What two types of database objects have the greatest impact on performance?" How many of you would answer "indexes and materialized views?" You could add my name to that list.

Indexes
Generally, the fastest way to access Oracle data is with an index. Oracle's bitmap and B-Tree indexes are designed to provide complementary performance functionality. While standard B-tree indexes perform best with key columns containing a high number of different values (good selectivity), bitmap indexes work best with columns that have a limited number (poor selectivity) of possible values.

Both types of indexes contain an entry for each value that appears in the indexed column(s) of the table and a pointer that provides direct, fast access to rows in that table. The key to good B-Tree index performance is to build the index on columns having good selectivity. Oracle is able to quickly bypass rows that do not meet the search criteria when searching through indexes built on columns having a high degree of selectivity. Conversely, bitmap indexes perform better when the selectivity of an index is poor. A bitmap index is a set of multiple bitmap structures containing a series of bits. If the bit is on, the corresponding row has that value. If it is off, the row does not.

Materialized Views
In releases previous to Oracle8i, administrators used summary tables to increase SQL performance for data warehousing applications. A summary table stores the pre-computed results of data calculations, such as sums, counts, averages and data table joins. It is more efficient to access a pre-computed result instead of computing the desired result from detail data every time a query is executed. In Oracle8i and later releases, administrators are able to use materialized views to provide the same benefits as summary tables.

An example that Oracle often uses in its materialized view demos is the sales-per-region-per-month materialized views. When a SQL statement is executed that calculates all sales for the Eastern region for the first quarter, the Oracle database will not calculate the total sales from individual sales detail records. Oracle will use the query-rewrite feature to rewrite the query to retrieve the summarized data from the sales-per-region-per-month materialized view. Because the optimizer automatically rewrites the query to use the summarized data, the use of the materialized view is entirely transparent to the end-user. No SQL or application changes required.

Introducing the SQL Access Advisor
In the old days, when DBAs were DBAs (we actually had to take data offline to administer it - I know that's hard to believe), we had to create indexes and materialized views the old fashioned way. We interviewed the application developers to determine what columns they were using to access the data, what tables they were joining together and what types of summary calculations they were performing. We then went back to our desks and began building database objects to improve performance.

Well, you no longer have to do that… Just kidding! You still have to do that but the SQL Access advisor is another tool in your toolbox that will help you obtain "maximus database performis". The SQL Access Advisor is not a replacement for old-fashioned DBA grunt work. You must still interview the developers to determine how they access and summarize the data and tune SQL by creating indexes.

SQL Access Advisor Output
The SQL Access Advisor recommends a set of materialized views and indexes based on a supplied workload input. The tool can also help administrators optimize materialized views to take advantage of fast refresh and query rewrite capabilities. In addition, the SQL Access Advisor may recommend dropping indexes and materialized views that aren't being used. The SQL Advisor ranks and groups the recommendations based on the positive impact they will have if implemented. The recommendations can range from simple suggestions to the creation of complex objects such as materialized views and materialized view logs. Like most output generated by the advisors, the data is stored in the repository, which allows it to be viewed until it expires and is removed.

SQL Access Advisor Input
The input workload can consist of SQL statements currently in the SQL Cache, a user defined set of SQL statements contained in a workload table or an OEM generated SQL Tuning Set. The SQL Access Advisor is also able to generate a hypothetical workload for a specified schema. The utility can be invoked from the Advisor Central home page or from the DBMS_ADVISOR package. Since we are discussing 10G Enterprise Manager's tuning toolsets, we'll focus our discussion on invoking it from Oracle's "sissy GUI tool".

Please remember that the quality of the output will depend upon the quality of input. Make sure that the tables and indexes being accessed have up-to-date statistics. You do this by generating statistics using the Oracle supplied package DBMS_STATS.

Using the SQL Access Advisor
The SQL Access Advisor can be accessed from 10G Enterprise Manager's Advisor Central home page. The home page allows administrators to access all of the advisors that are available in 10G Enterprise Manager. The Advisor Tasks section of the page displays output from advisor executions.

I clicked on the SQL Access Advisor link to activate the wizard. 10G Enterprise Manager displayed the SQL Access Advisor: Workload Source page that allows users to specify the workload input. As stated previously, the administrator can choose from one of the following workload inputs: SQL currently residing in the SQL cache, SQL from a predefined SQL Tuning Set (more on this in a later blog), SQL contained in a user-defined table or a hypothetical workload based on a set of tables contained in a particular schema.

I decided to use the SQL currently residing in the SQL cache as the workload input. When I clicked next, 10G EM displayed the SQL Access Advisor: Recommendation Options page. This page allows users to choose the objects that will be recommended (index, materialized views, both). The user is also able to choose the depth of the analysis to be performed, specify if space should be used as a determining factor when generating the recommendations, how the results will be sorted and the schema and tablespaces that will be used when the tool generates the DDL recommendations.

When I click Next, 10G Enterprise Manager displays the SQL Access Advisor: Schedule page. This page allows users to specify when to run the advisor and if the job should be repeated on a regular basis. I chose to use the Standard option from the drop down menu titled "Schedule Type". The SQL Access Advisor also allows users to choose from predefined time-period windows.

The last page that is displayed is the SQL Access Advisor: Review page that allows users to perform a final review of the options they chose. Hitting the Submit button asks 10G Enterprise Manager's job scheduler to run the job based on the scheduling options chosen.

If you chose the comprehensive option, the SQL Access Advisor will take some time to generate the recommendations. During my test, I checked the amount of resources the utility consumed on the database server using 10G EM and the NMON operating system monitoring tool. Although the resources the utility used were noticeable, the impact was not large enough for me to become concerned . The affect it has on your system will depend upon your hardware platform's CPU and memory configuration and the workload used as input.

When the job completes, 10G Enterprise Manager displays the Summary Output page that sorts recommendations according to their benefit. Clicking on the number on the far left side of the tabular output drills down into the specific recommendation. Notice that this execution has generated over 5,000 recommendations. Needless to say, I will be concentrating on the top few recommendations.

I did notice that when I clicked on the report, 10G EM began to access the target database server. I assume this is to update some information required by the report. Once again, the resources used were noticeable but nothing that would cause me concern. The problem I created was when the page didn't load fast enough for me. I became impatient and started "clicking around" (don't tell me you have never done this yourself) which generated an additional workload on the target database. I think I hit the browser's BACK button and then clicked on the report's link one too many times. The workload became more and more noticeable until I came to the conclusions that I had better 1) stop clicking around and 2)kill some of my connections. Once I terminated my connections, I went back into the tool and clicked on the report ONCE. I let the report run and things were fine. I have accessed the report several times since then to test performance and have experienced no problems.

I continued reviewing the output by clicking on the recommendation ID link titled "7". 10G Enterprise Manager displayed the Recommendation Details page. This page displays links for the specific objects that will be created if the recommendation is implemented. The bottom of the page displays the SQL statements that will be improved by the recommendation. Notice that the output DDL uses the table schema as default if none is specified. The page also displays the space that will be consumed if the object is created. Finally, users can hit the OK button to begin creating the recommended objects or save the DDL for execution at a later time. When you click on the materialized view link, 10G Enterprise Manager displays the DDL for the recommended object.

Like all output generated by the 10G EM advisors, the results can be viewed on the Advisor Central home page until it reaches it reaches its expiration date and is deleted.

I hope you enjoyed our review of 10G EM's SQL Access Advisor. As I stated previously, although the tool helps administrators improve the performance of their databases, it should only be used in conjunction with proper DBA tuning practices.