Chapter 10: Tuning SQL with Oracle Enterprise Manager

---

Oracle Enterprise Manager is a large suite of software tools used to manage and tune Oracle databases across an enterprise. This chapter will briefly cover parts of Oracle Enterprise Manager useful to tuning of SQL code. More will be covered in Part III when discussing the Oracle Database Wait Event Interface.

10.1 The Tuning Pack

The Tuning Pack contains several applications for both preemptive and reactive tuning of an Oracle installation. With respect to SQL code tuning the parts we are interested in at present are as follows:

• Index Tuning Wizard.   Searches for inefficient indexes and makes suggestions for new indexes, removal of superfluous indexing and changes to existing indexes.

• SQL Analyze.   Allows SQL code analysis, potential optimization, SQL statement editing, and even some capability for automated tuning.

• Oracle Expert.   Allows recommendations and implementation of possible tuning approaches.

• Outlines.   Outline performance assessment and management is also included in the Tuning Pack. Outlines will eventually be deprecated from a future version of Oracle Database and are thus not covered in this book.

Let's start by taking a look at the Index Tuning Wizard.

10.1.1 Index Tuning Wizard

The Index Tuning Wizard attempts to find inefficient indexes from a cost-based optimization perspective. Inefficient indexing was covered in Chapter 7 on Common Sense Indexing. The Index Tuning Wizard makes recommendations. These recommendations include recommendations to add new indexes, rebuild current indexes, and index type changes such as changing from a Bitmap to a BTree.

Any changes with the database can warrant running the Index Tuning Wizard. If your database consistently changes over time this tool could be executed periodically.

On a more practical note, using a tool such as this to automatically regenerate indexes can be detrimental to performance in itself. Interrogation of database statistics should suffice to interrogate indexing capabilities. Also when I have used this tool in the past recommendations to create new and rebuild existing indexes have been somewhat "wild" to say the least. I would propose extreme caution when taking heed of recommendations reported by this tool. In general, OLTP systems are highly active and any index rebuilds should be executed with the ONLINE option to avoid application errors. The mere act of rebuilding an index can cause a severe performance hit. For an OLTP database any recommendation to change an index type from a BTree to any other index type should probably be completely ignored. Bitmap indexes are not really useful for anything but read-only environments. I have even heard complaints about bitmap index use in data warehouses. Additionally BTree indexing only becomes inefficient when skewed. Skewing is generally as a result of data content and not much can be done about that unless the data model can be altered. The only case where BTree indexes should occasionally be rebuilt is when a table undergoes a lot of deletion. Deleted leaf blocks are not reclaimed in BTree indexes.

Note 

 Oracle Database 10 Grid   General recommendation and acceptance of use of automated segment management may make occasional rebuilding of BTree indexing less necessary.

I executed the Index Tuning Wizard on my highly active database. It appears from the following example that the Index Tuning Wizard searches through past SQL code statements attempting to match WHERE, ORDER BY, and GROUP BY clauses against indexes. If an index does not exist it suggests creating one. The composite index suggested for creation in the following example is not strictly necessary. Let's examine this example. Figure 10.1 shows the initial screen for the Index Tuning Wizard.

Figure 10.1: The Index Tuning Wizard

The next screen shown in Figure 10.2 allows an option of selecting different database types. Index requirements between the two extremes of OLTP and data warehousing are totally different. OLTP databases require exact hits on single rows and data warehouses require retrieval of large numbers of rows at once.

Figure 10.2: Select the Application Type

Then either a single schema or multiple schemas can be selected for index analysis, as shown in Figure 10.3.

Figure 10.3: Select Schemas to Analyze

Figure 10.4 shows a recommendation to modify an index on the Transactions table. This index is suggested as being altered from a single-column to a multiple-column index. The suggestion is sensible because as shown in Index Tuning Wizard output, included later in this chapter, this index is accessed by an SQL statement using the two columns suggested for the composite index. However, this index is a foreign key index on the TransactionsLine table pointing to the primary key on the Transactions table. This index can absolutely not be altered in my Accounts schema. Note that Oracle Database does not create indexes on foreign key constraints automatically, thus Oracle Database is not aware that this index is actually a single-column only foreign key index. Or, more succinctly, the Index Tuning Wizard ignores foreign keys. Additionally this recommendation is poor for two further reasons. Firstly, the number of values in the suffix (second column) of the composite index is minimal. As a result, indexing the composite would have little effect on any SQL accessing both columns at once. The single-column index would suffice for good performance. Secondly, my application code simply does not require a composite index in this case; a composite index is totally inappropriate.

Figure 10.4: The Index Tuning Wizard Makes Suggestions

The following script shows the Index Tuning Wizard output suggesting why the index should be changed. Note the composite column access in the WHERE clause.

A change to an existing B*-tree index is recommended for the table ACCSTEST.TRANSACTIONSLINE.     Index before recommendations ----------------------------     TRANSACTION_ID     Recommended columns -------------------     TRANSACTION_ID       There was at least one reference by an equality          operator.     STOCKMOVEMENT_ID       There was at least one reference by an equality          operator.       There was at least one reference by SELECT, ORDER-BY          or GROUP-BY clauses.       Number of rows in the table: 1551030       Distinct values in the index: 1551030           ************** SQL Statement Information **************           The recommendation above is based on the following SQL:           SQL: SELECT stockmovement_seq.NEXTVAL, sm.stock_id,             sm.qty * -1 qty, sm.price, :b1          FROM stockmovement sm          WHERE EXISTS (SELECT stockmovement_id                   FROM transactionsline                   WHERE stockmovement_id =                       sm.stockmovement_id                   AND transaction_id = :b2) 

The Index Tuning Wizard also produces the code necessary to regenerate the index. Note one thing about this code. Performing these DDL statements in a highly available OLTP database is a very bad idea because the ONLINE option was excluded. If this table is large, which it is, and there is access to the table between dropping and completion of the CREATE INDEX command, and SQL code accesses the index, any rows not yet built in the index will simply not be found by the SQL statements.

--CREATE Index Recommendations DROP INDEX ACCSTEST.XFK_TL_TRANSACTION; CREATE INDEX ACCSTEST.XFK_TL_TRANSACTION       ON ACCSTEST.TRANSACTIONSLINE          (TRANSACTION_ID, STOCKMOVEMENT_ID)       LOGGING       INITRANS 2       MAXTRANS 255       TABLESPACE INDX       PCTFREE 10       STORAGE (          INITIAL 1024K          NEXT 1024K          PCTINCREASE 0          MINEXTENTS 1          MAXEXTENTS 2147483645) ONLINE; 

I have grave doubts about the usefulness of the Index Tuning Wizard in any highly active OLTP database. Most OLTP databases are required to be available permanently. Perhaps this tool would be more useful for data warehouses? For the CREATE INDEX command shown previously at least the ONLINE option should be used. To reiterate, the ONLINE option was not generated for the commands created by the Index Tuning Wizard; I added it. Additionally better availability would be gained by using the ALTER INDEX command with the REBUILD and ONLINE options. ALTER INDEX REBUILD ONLINE would never create a situation of the index not existing. In the preceding script the index does not exist between the DROP and CREATE commands.

Note 

 Oracle Database 10 Grid   Over the years the parts and pieces in Oracle Enterprise Manager have become much better as tuning tools. The problem with making index recommendations in this context is that indexing is abstracted from applications and data model to a database engine. The database engine is not an expert on what applications are attempting to achieve.

10.1.2 SQL Analyze

Tuning SQL code is one of the most effective methods of tuning an Oracle installation and its attached applications. However, tuning of SQL code can sometimes be difficult and very time consuming. SQL code embedded in applications can involve application code changes, which may not be too difficult but may involve resistance from developers, and quite understandably so. Developers are of course correct to resist any code changes. Code changes can cause a stable application to rapidly become very unstable. In a production environment the cliché "If it works don't fix it" holds true in many cases. Additionally tuning of SQL code can require that the personnel tuning that code have extensive knowledge of all aspects of the database and any applications. This is often a difficult if not impossible scenario.

SQL Analyze is a tool which can be used to assist in tuning SQL code by allowing SQL code analysis, potential optimization, SQL statement editing, and even some capability for automated tuning. Note that allowing SQL Analyze to automatically tune SQL code could be risky. In the past when using automated tuning features in Oracle Enterprise Manager I have found the more automated aspects to be somewhat dubious at best. Making automated changes in production environments is contrary to the approach of most database administrators. The unpredictability of automated changes is relatively unknown. Database administrators are generally exceedingly cautious and for a good reason. Production databases are often required to be running constantly and any potential changes could cause loss of service. For most OLTP database installations downtime is absolutely not acceptable. Customers can get very upset. When customers are upset they go to the competition.

SQL Analyze can be used to do a large number of things. SQL Analyze allows access to initialization parameters, current cache "TopSQL" (hardest-hitting SQL statements) plus stored historical SQL statements. In general, SQL Analyze can be divided into two main sections. The first as shown in Figure 10.5 shows the analysis stage.

Figure 10.5: SQL Analyze and the Analysis Stage

As can be seen in Figure 10.6 the analysis stage allows retrieval of the worst-performing SQL statements. Note all the different sort orders. The sort orders are consistent with SQL Trace and TKPROF output sort orders described in the previous chapter. Disk reads per execution is the default because it shows disk access for the execution of an SQL statement. In terms of data retrieval I prefer to simply add both disk and buffer gets and compare the sum to rows processed, giving an assessment of database access required for each row. Very often the actual executions of an SQL statement is the most significant factor. Sometimes a few SQL statements can be executed much more frequently than any other SQL code. Obviously the most frequently executed SQL code could potentially have the most profound effect on performance and should be the most highly tuned SQL statements in the database. Focus on the biggest problems!

Figure 10.6: SQL Analyze Single SQL Statement Assessment and Testing

Figure 10.6 shows a selection of TopSQL selected SQL statements in the top right box, sorted in order of disk reads per execution. Note access to configuration parameters in the bottom right box.

The second main section of SQL Analyze has rich capabilities and is very useful indeed, as seen in Figure 10.7 through to Figure 10.12. SQL Analyze allows for numerous actions and assessments on individual SQL code statements, providing a graphical user interface into both query plans and execution statistics.

Figure 10.7: SQL Analyze SQL Text Display

Figure 10.7 shows the SQL code text in the bottom right box for a highlighted SQL statement.

Figure 10.8 shows the EXPLAIN PLAN command query plan for an SQL statement.

Figure 10.8: SQL Analyze Query Plan

Figure 10.9 shows the ability in SQL Analyze for index recommendations.

Figure 10.9: SQL Analyze Index Recommendations

Figure 10.10 shows that a query plan can be assessed for all Optimizer modes: RULE, FIRST_ROWS, ALL_ROWS, and CHOOSE. This allows testing of different modes of optimization.

Figure 10.10: SQL Analyze Query Plan Assessment

Figure 10.11 shows menu access routes to the Virtual Index Wizard, the Hint Wizard, and the SQL Tuning Wizard. These tools are extremely useful for doing SQL code tuning, interactively and with ease of use.

Figure 10.11: SQL Analyze Virtual Indexing, Hints, and Tuning

Having used SQL Analyze quite extensively I am rather enthused with its very rich capabilities. I am still noticing a few bugs, even in Oracle9i Database, but nothing debilitating. SQL Analyze can be used to compare different versions of the same SQL statement as shown in Figure 10.12. This multiple SQL statement functionality is completely interactive and changes as the SQL programmer changes things.

Figure 10.12: Comparing Different Versions of an SQL Statement

Figures 10.13 and 10.14 show two query plans for a rather "busy" SQL join statement. Note the difference in cost between the ALL_ROWS and FIRST_ROWS Optimizer mode versions, respectively. The ALL_ROWS version is much lower in cost because the mutable join is retrieving all the rows in all the tables. This is more apparent when examining the query plans, showing that the FIRST_ROWS option in Figure 10.14 uses all nested loop joins with no hash joins. Hash joins are efficient for joining large row sets and nested loop joins are better for joining a very small row set with another small or a large row set. Forcing FIRST_ROWS optimization as in Figure 10.14 makes the Optimizer think it is not accessing many rows and thus it defaults to nested loop joins.

SQL Analyze is very rich in functionality. Spotlight and TOAD are non-Oracle tools of the same caliber with similar capabilities but SQL Analyze is a richer environment with respect to tuning SQL code. The only problem using any tool within Oracle Enterprise Manager is that you need to be a rocket scientist to set it up properly.

Keep trying. Practice makes perfect! Parts of Oracle Enterprise Manager are extremely useful and informative.

Tip 

Drilling down into the Oracle Database Wait Event Interface is covered extremely well by Oracle Enterprise Manager. The Oracle Database Wait Event Interface will be covered in Part III.

In the past Oracle Enterprise Manager using the Management Server has had serious security issues. In the past Oracle Enterprise Manager has been bug-ridden and known to induce a certain amount of frustration, sometimes most immense frustration. The word is that these problems have apparently largely been resolved.

What can SQL Analyze be used for in general?

• Search for and change poorly performing SQL code.

• Assess existing indexing.

• TopSQL can be used to show high-impact SQL statements from both current cache and historical SQL code. SQL code statements can be sorted on a large number of criteria much like SQL Trace files can be sorted with TKPROF.

• A high-impact SQL statement can be selected. That selected statement can be analyzed in its entirety including query plans. SQL statements can even be imported or typed in from scratch and tested.

• Query plans and execution statistics can be generated for all potential Optimizer modes.

• A Hint Wizard allows suggested and tested alterations using hints.

• Virtual indexing and index recommended alterations and additions can be utilized to produce varying query plans.

• There is some automated change capability in SQL Analyze. My experience with Oracle Enterprise Manager in general makes it necessary to recommend not executing any automation-type features which change anything in the database without a database administrator being made aware of potential changes before changes are made.

• Allow comparisons between different query plans for the same SQL statement both in textual and graphical form. This is a really "cute" feature but not necessarily all that useful.

• Allow a step-by-step walk-through of query plans giving a detailed, easy-to-understand explanation of each step.

10.1.3 Oracle Expert

Oracle Expert is a tool allowing recommendations and implementation of possible tuning approaches. This tool applies to SQL code tuning plus physical and configuration tuning. In this chapter we will concentrate on the SQL code tuning aspects of Oracle Expert. As far as SQL code tuning is concerned Oracle Expert can produce what is called an Optimal Index Analysis. An excerpt of one of these analyses executed on the Accounts schema is shown on the next page.

Figure 10.13: An ALL_ROWS Optimizer Query Plan

---------------------------------------------- -- DROP Index Recommendations ---------------------------------------------- DROP INDEX ACCSTEST.XFK_CBL_CHEQUE; DROP INDEX ACCSTEST.XFK_O_TYPE; DROP INDEX ACCSTEST.XAK_O_TYPE_CUSTOMER; DROP INDEX ACCSTEST.XFK_OL_ORDERS; DROP INDEX ACCSTEST.XFK_TL_TRANSACTION; 

Note that all the Oracle Expert has advised that I do is to drop foreign key indexes from the Accounts schema. Those foreign key indexes are not accessed by any SQL code. Shriek! That is definitely not an intelligent idea. Why? Firstly, foreign key indexes will not necessarily be used by SQL code. Secondly, removing indexes from foreign keys will create a lot of locking problems and probably result in some severe performance issues. Foreign key indexes are used by Oracle Database internal Referential Integrity checks. Those Referential Integrity checks are very likely ignored by Oracle Expert since no explicit SQL code is involved. This is not good. Be very careful using Oracle Expert in this particular respect.

Figure 10.14: A FIRST_ROWS Optimizer Query Plan

The Oracle Expert on-screen visual of the above script is shown in Figure 10.15.

Figure 10.15: Oracle Expert Recommendations for the Accounts Schema

10.1.4 SQL Access Advisor

 Oracle Database 10 Grid   SQL Access Advisor is an Oracle Enterprise Manager tool allowing identification and resolution of problematic SQL code statements.

That is about all we need to cover on the subject of tuning SQL code using Oracle Enterprise Manager. Further discussion of Oracle Database physical and configuration tuning using Oracle Enterprise Manager will be covered in Part III. Oracle Enterprise Manager is effectively an excellent teaching tool and one can learn a lot about the Oracle Database and tuning using Oracle Enterprise Manager. There is much in Oracle Enterprise Manager which is very useful.

This concludes Part II of this book and the discussion of SQL code tuning. The next chapter is the first chapter in Part III, beginning the subject of physical and configuration tuning in Oracle Database. We will start Part III with a chapter covering Oracle installation and database creation, allowing for the best database performance from first acquisition and installation of Oracle software. Subsequent chapters in Part III will proceed into physical and configuration tuning in much greater depth.

---