CHAPTER 16 • ORACLE8i SQL PERFORMANCE MONITORING AND TUNING 756 which results in the following execution plan and statistics from Autotrace: Elapsed: 00:00:00.41 Execution Plan 0 SELECT STATEMENT Optimizer=RULE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (BY INDEX ROWID) OF ‘EMPLOYEE’ 3 2 INDEX (RANGE SCAN) OF ‘ID_EMP’ (NON-UNIQUE) 4 1 INDEX (RANGE SCAN) OF ‘ID_DEPT’ (NON-UNIQUE) Statistics 0 recursive calls 0 db block gets 24 consistent gets 0 physical reads 14 rows processed Notice the index lookup on the EMPLOYEE and DEPT tables. Every employee in EMPLOYEE is a member of the research department. Notice also that EMPLOYEE is the driving table. We know that DEPT is going to be a better driving table, however, because only one row is needed from it (as compared to reading every row from EMPLOYEE). So we’re scanning the index on the EMPLOYEE table for every employee, and then we have to scan the DEPT index for every row in the EMPLOYEE table. This is costing us 24 consistent gets. What if we just did a full table scan of the EMPLOYEE table? We’re reading every row anyway, so wouldn’t it help? Let’s see. To force the optimizer to do a full table scan, append a +0 to the left side of the EMPNO < 7800 line in the WHERE clause: SELECT empno, dname FROM dept b, EMPLOYEE a WHERE dname=’RESEARCH’ and empno+0< 7950 and a.deptno=b.deptno; In the following output, you’ll see there’s not much improvement. In fact, we’ve managed to increase the number of logical I/Os by 12. Also, the runtime has about doubled. Elapsed: 00:00:00.51 Execution Plan 0 SELECT STATEMENT Optimizer=RULE C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 757 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF ‘EMPLOYEE’ 3 1 INDEX (RANGE SCAN) OF ‘ID_DEPT’ (NON-UNIQUE) Statistics 0 recursive calls 12 db block gets 24 consistent gets 0 physical reads 0 sorts (memory) 0 sorts (disk) 14 rows processed Tuning is an iterative thing, so you try and try again. Let’s try again. We know we want DEPT to be the driving table. Let’s disable the index on the DEPT table and see what happens: SELECT empno, dname FROM dept b, EMPLOYEE a WHERE dname=’RESEARCH’ and empno< 7950 and a.deptno||’ ’=b.deptno||’ ’; Now look at the runtime; it’s slightly better—and the logical I/Os are very much improved! Elapsed: 00:00:00.40 Execution Plan 0 SELECT STATEMENT Optimizer=RULE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF ‘DEPT’ 3 1 TABLE ACCESS (BY INDEX ROWID) OF ‘EMPLOYEE’ 4 3 INDEX (RANGE SCAN) OF ‘ID_EMP’ (NON-UNIQUE) Statistics 0 recursive calls 4 db block gets 11 consistent gets 0 physical reads 0 sorts (memory) 0 sorts (disk) 14 rows processed TUNING SQL STATEMENTS Beyond Simple Database Management PART III C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CHAPTER 16 • ORACLE8i SQL PERFORMANCE MONITORING AND TUNING 758 So the big gain here appears to have come from disabling the DEPT index, which caused the RBO to make the DEPT table the driving table. Instead of 24 logical I/Os, we got 15—a 62 percent improvement in performance. Not bad. As you can see, dis- abling indexes can be a good thing or it can be a bad thing. If you are using CBO, you can also disable the use of an index by using the NO_INDEX hint. You can use the hint without any parameters, or you can include parameters that cause certain indexes to be ignored by the optimizer. For example, if you didn’t want to use the IX_EMPLOYEE index, you’d use NO_INDEX(ix_employee) Tuning Rule-Based Statements One of the unfortunate facts of life about RBO is that the tuning options are some- what limited. In this section we will discuss some issues that can make an impact on the overall performance of your RBO-based SQL statements. Choosing the Driving Table Wisely As discussed, the driving table is the table that provides the row sources to be resolved in the second table in the join. Often the driving table will be accessed through a full table scan, if the column is not indexed. In a rule-based statement, the driving table is generally the table with the lowest access path, but not always. When the access paths are alike between two tables, you can change the join order in RBO by reversing the table order in the FROM clause. This is because the last table in the FROM clause will be the driving table, if the access paths for both tables are the same. Because the dri- ving table is the main row source, you always want it to be the table that will generate the smallest and thus most selective row source. Let’s examine an example in which you can see the impact of a well-chosen dri- ving table. Assume you have an EMP table and a DEPT table, both of which are not indexed. Here is the first query: SELECT a.empno, b.dname FROM emp a, dept b WHERE a.deptno=b.deptno; And here are the execution plan and performance stats: Elapsed: 00:00:39.87 Execution Plan 0 SELECT STATEMENT Optimizer=RULE 1 0 MERGE JOIN 2 1 SORT (JOIN) C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 3 2 TABLE ACCESS (FULL) OF ‘DEPT’ 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF ‘EMP’ Statistics 2072 recursive calls 325 db block gets 2227 consistent gets 3324 physical reads 25536 redo size 1391187 bytes sent via SQL*Net to client 212546 bytes received via SQL*Net from client 1913 SQL*Net roundtrips to/from client 7 sorts (memory) 1 sorts (disk) 28672 rows processed In this case, the DEPT table is the driving table. Notice that both tables are using full table scans, so they are ranked equally. To continue our example, let’s reverse the order of the tables in the FROM clause and make the employee table the driving table, as follows: SELECT a.empno, b.dname FROM dept b, emp a WHERE a.deptno=b.deptno; Take a look at the execution plan and performance stats now, and notice the reversal of the join order of the DEPT and EMP tables: Elapsed: 00:00:30.14 Execution Plan 0 SELECT STATEMENT Optimizer=RULE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF ‘EMP’ 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF ‘DEPT’ Statistics 2072 recursive calls 297 db block gets 2227 consistent gets 759 TUNING SQL STATEMENTS Beyond Simple Database Management PART III C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CHAPTER 16 • ORACLE8i SQL PERFORMANCE MONITORING AND TUNING 760 3068 physical reads 25736 redo size 1391187 bytes sent via SQL*Net to client 212546 bytes received via SQL*Net from client 1913 SQL*Net roundtrips to/from client 7 sorts (memory) 1 sorts (disk) 28672 rows processed Notice that this plan executes almost 10 seconds faster than the first. A 10-second difference may not seem like much at first glance, but consider the significance if this same statement were executed several thousand times, perhaps as part of a month- end or payroll report. Those seconds add up quickly. Accounting for Indexes Of course, indexes can make for even more interesting tuning. Assume you have an index on the DEPT table that you are querying. You create the index on the join columns for DEPT, DEPTNO, and DNAME. What do you suppose will happen? Let’s run the original query first. Then we’ll reverse the order of the tables in the query and change the driving table. Elapsed: 00:00:23.84 Execution Plan 0 SELECT STATEMENT Optimizer=RULE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF ‘EMP’ 3 1 INDEX (RANGE SCAN) OF ‘IX_DEPT’ (NON-UNIQUE) Statistics 444 recursive calls 6 db block gets 36176 consistent gets 1625 physical reads 60 redo size 908283 bytes sent via SQL*Net to client 212546 bytes received via SQL*Net from client 1913 SQL*Net roundtrips to/from client 8 sorts (memory) 0 sorts (disk) 28672 rows processed C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 761 As you might expect, Oracle has picked up the index and used it. This has improved the overall performance of the query over the original by some 45 percent (which is a lesson unto itself). Now, let’s see what happens when we reverse the FROM clause: Elapsed: 00:00:24.65 Execution Plan 0 SELECT STATEMENT Optimizer=RULE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF ‘EMP’ 3 1 INDEX (RANGE SCAN) OF ‘IX_DEPT’ (NON-UNIQUE) Statistics 444 recursive calls 6 db block gets 36175 consistent gets 1625 physical reads 0 redo size 908283 bytes sent via SQL*Net to client 212546 bytes received via SQL*Net from client 1913 SQL*Net roundtrips to/from client 8 sorts (memory) 0 sorts (disk) 28672 rows processed In this case, Oracle did not change the execution plan. Regardless of the order of the objects in the FROM clause, Oracle is still going to follow the rules. So with the introduction of the index, Oracle in RBO will always choose to use the index and will choose to do a nested loop. Tuning Cost-Based Statements CBO tuning is a whole different animal than RBO tuning. In cost-based optimization, you have many more options available to you in terms of access paths. CBO is smarter than RBO because CBO takes advantage of generated statistics. And CBO queries are easier to tune because you can utilize hints to tell the optimizer which access paths are optimal. Using Hints Hints are part of CBO magic and probably your most important SQL tuning tool— they only work with CBO (except for the RULE hint). Hints let you direct the CBO on TUNING SQL STATEMENTS Beyond Simple Database Management PART III C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CHAPTER 16 • ORACLE8i SQL PERFORMANCE MONITORING AND TUNING 762 what access paths to take when generating the execution plan for your SQL state- ment. The CBO will not always follow your hints, for one reason or another, and you might find that it takes a combination of hints to get the response you want from SQL statement. To use a hint in a SQL statement, you place it immediately after the SELECT state- ment in a comment (/* */) bracket, with a + immediately after the first /* symbol. For example: /*+ RULE */ The format of the hint designation is important. You must put the + immediately after the /*. If you leave a space between the /* and the +, you invalidate the hint. You can also use the alternate + syntax for hint inclusion (as in + RULE). A SQL statement can comprise multiple hints; each hint is separated by a space. Hints often have associated parameters, one of which is often the name of the table to which you want the hint applied. For example, when using the FULL hint to get full table scans, you give the hint a parameter specifying the name of the table you want to have the full table scan, as in /*+ FULL(EMPLOYEE) */ You must use the table alias if it is defined. So if an alias, EMP, is used for the EMPLOYEE table, then the hint would be + FULL(EMP) NOTE Oracle does not report an error when it encounters an invalid hint. It just ignores the hint and processes the SQL statement using whatever plan the optimizer would have generated apart from the hint. On the other hand, if you have a valid hint and an invalid hint in the same hint SQL statement, Oracle will accept the valid hint and throw out the invalid hint. So, when you do use hints, it’s important to check the execution plans to make sure your hints are being incorporated. When you use multiple hints, Oracle may ignore some of them depending on the access paths it decides to use. If a certain hint is very important but is not being implemented, you can try to force it by controlling the order of the join (using the ORDERED hint). Thus, if you force the table with the preferred hint to be the driving table of the join by using the ORDERED hint, Oracle is more likely to use the access path directed in the hint. Tables 16.9 through 16.14 list and classify all hints available in Oracle8i and how to use them. C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 763 TABLE 16.9: OPTIMIZATION APPROACH HINTS Hint Name Parameters Purpose/Example ALL_ROWS None Causes Oracle to optimize the SQL statement for throughput rather than response. This mode tends to prefer full table scans and merge joins over index lookups. Example: /*+ ALL_ROWS */ FIRST_ROWS None Causes Oracle to optimize the SQL statement for response rather than throughput. This mode tends to prefer index lookups and nested loop lookups. Example: /*+ FIRST_ROWS */ CHOOSE None Causes the statement to run in RULE mode if no statistics are available. If statistics are available, then the statement will run in cost-based mode. Example: /*+ CHOOSE */ RULE None Causes the statement to run in rule-based mode regardless of the current system setting OPTIMIZER_MODE. Example: /*+ RULE */ TABLE 16.10: ACCESS METHOD HINTS Hint Name Parameters Purpose/Example FULL Table name Forces a full table scan of the listed table. If table has an alias, then the alias rather than the table name must be used in the hint. Example: /*+ FULL(e) */ ROWID Table name Suggests that the optimizer do a table scan by ROWID for the listed table. Example: /*+ ROWID(e) */ CLUSTER Table name Suggests that the optimizer do a cluster scan to access a specific table. Example: /*+ CLUSTER(e) */ TUNING SQL STATEMENTS Beyond Simple Database Management PART III C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. CHAPTER 16 • ORACLE8i SQL PERFORMANCE MONITORING AND TUNING 764 TABLE 16.10: ACCESS METHOD HINTS (CONTINUED) Hint Name Parameters Purpose/Example HASH Table name Suggests that the optimizer do a hash scan to access the table specified. Applies only to tables stored in a cluster. Example: /*+ HASH(e) */ INDEX Table name and Specifies the index to be used to access index names the given table. Oracle will not consider a full table scan or a scan on another index on the table. Applies to both B*Tree and bitmap indexes. See also INDEX_COMBINE, which Oracle suggests when using bitmap indexes. When this hint specifies multiple indexes, Oracle selects the one that offers the best access. If no index name is specified, the optimizer chooses from all indexes available on the table. Full table scans are not considered. In multiple index listings or where no index is listed, Oracle may decide to scan multiple indexes and merge the results. Example: /*+ INDEX (e ix_emp_01 ix_emp_02) */ INDEX_ASC Table name and Designates an index scan for the table specified. index names Essentially the same as the INDEX hint. Example: /*+ INDEX_ASC(e ix_bit_emp_01) */ INDEX_COMBINE Table name bitmap Designates the specified bitmap index as index names the access path to the table specified. If no indexes are listed, the optimizer uses whatever combinations of bitmap indexes have the best- calculated cost. If multiple indexes are listed, Oracle will use the best combination of indexes that it finds. Example: /*+ INDEX_COMBINE (e ix_bit_emp_01)*/ INDEX_JOIN Table name and Designates an index join containing the index names specified indexes as the access path to the table specified. Example: /*+ INDEX_JOIN */ C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 765 TABLE 16.10: ACCESS METHOD HINTS (CONTINUED) Hint Name Parameters Purpose/Example INDEX_DESC Table name and Causes the optimizer to choose a plan index names that instigates an index scan for the given table. If during the execution the query an index range scan is used, the index will be scanned in descending order (the reverse of the default Oracle setting for index scans). Example: /*+ INDEX_DESC */ INDEX_FFS Table name and Designates a fast full scan on the index index name specified, instead of a full scan of the associated table. Example: /*+ INDEX_FFS(emp ix_emp_01) */ NO_INDEX Table name and Causes the specified indexes associated index names with the listed table to be ignored by the opti- mizer. If no indexes are listed in this hint, then no indexes will be considered for the table. Example: /*+ NO_INDEX(emp ix_emp_01) */ AND_EQUAL Table name and Causes the optimizer to choose an index names execution plan for access to the listed table that includes a merge scan of the indexes listed. At least two indexes must be specified in the hint, and no more than five. Example: /*+ AND_EQUAL (emp ix_emp_02 ix_emp_03 ix_emp_04) */ USE_CONCAT None Forces a SQL statement using OR conditions in the WHERE clause to be transformed into a com- pound query (i.e., using a UNION ALL keyword). Example: /*+ USE_CONCAT */ NO_EXPAND None This hint removes OR expansions from considera- tion when the query is being optimized. It is the opposite of the USE_CONCAT query. Example: /*+ NO_EXPAND */ REWRITE View name Causes Oracle to search for eligible materialized views that could be used to rewrite the SQL query associated with the hint. You may specify one or more materialized view names to be considered. Example: /*+ REWRITE */ TUNING SQL STATEMENTS Beyond Simple Database Management PART III C opyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.