Page 763
SQL trace/tkprof when you could have it immediately by using EXPLAIN PLAN? Hence, Oracle also supplies this immensely useful utility.
How do you use EXPLAIN PLAN? First, run the script to create the PLAN_TABLE that store s the EXPLAIN PLAN output. This is usually $ORACLE_HOME/rdbms/admin/utxlplan.sql, although the directories and filename may change from platform to platform. After you have run this script, it should have created the PLAN_TABLE under your schema (assuming you had the proper privileges). On a Windows NT platform, the file is located in the subdirectory DRIVE_LETTER:\ORANT\ADMIN\, where DRIVE_LETTER is the drive where Oracle is installed.
Second, run EXPLAIN PLAN for the statement on which you want to generate the execution plan information, such as the following query:
SQL> EXPLAIN PLAN 2> SET STATEMENT_ID='STMT_1 3> FOR 4> SELECT EMPLOYEE_ID 5> FROM EMPLOYEES 6> WHERE EMPLOYEE_ID=243218;
Use the following SELECT from the PLAN_TABLE to get the output you need:
SQL> SELECT OPERATION, OPTIONS, OBJECT_NAME, ID, PARENT_ID,POSITION 2> FROM PLAN_TABLE 3> WHERE STATEMENT_ID='STMT_1' 4> ORDER BY ID;
The output then looks like:
Operation | Options | OBJECT_NAME | ID | PARENT_ID | Position |
SELECT | |||||
STATEMENT | 0 | 2 | |||
TABLE ACCESS | BY ROWID | EMPLOYEES | 1 | 0 | 1 |
INDEX | RANGE SCAN | EMPL_IDX | 2 | 1 | 1 |
You can also generate a tree-walk, if you prefer. To get an indented, text graph representation, use the following SQL code:
SQL> SELECT LPAD(` `,2*LEVEL_1) | | OPERATION | | ` ` | | OPTIONS | | ` ` | |OBJECT_NAME | | ` ` | | DECODE(ID, 0, `COST= `| | POSITION) "QUERY PLAN" 2> FROM PLAN_TABLE 3> START WITH ID=0 AND STATEMENT_ID='STMT_1P>' 4> CONNECT BY PRIOR ID=PARENT_ID; Source: Oracle Corporation
The output should look like:
Query Plan ------------------------------------------------------- -SELECT STATEMENT COST=2 TABLE ACCESS BY ROWID EMPLOYEES INDEX RANGE SCAN EMPL_IDX
Page 764
This is frequently more useful for large queries that generate large execution plans. The indentations represent another level down in the execution tree, parent to child. The preceding example contains three levels, and the cost is equal to two. The most important thing, however, is the verification that you expected the optimizer to use the index and it did. If it did not, you would need to review the various possibilities as to why it didn't, such as the number of rows returned, the index not being highly selective, and so forth. Attempt to fix this situation and then rerun the EXPLAIN PLAN and see if you fixed the problem. This iterative use of EXPLAIN PLAN in verifying what you think should be going on with the optimizer is EXPLAIN PLAN's most notable use.
To summarize using EXPLAIN PLAN:
The root of most application tuning problems is improper index usage. In the first section, we'll take a look at some of the rules for proper index usage. In the second section, we'll consider typical application problems, those directly and indirectly related to indexing, as well as some unrelated to indexing.
Typically, most application tuning problems occur when deciding whether or not to use indexes. Over time, indexes have been both underused and overused. My experience has shown both extremes among DBAs of varying expertise. The best thing you can say about indexing is that you must use it correctly. The following is a list of tips for using indexes properly:
Page 765
In addition to the preceding tips, you can do several more things to make your database more efficient through the use of indexes.
For example, use hash indexes for columns with high selectivity (near 1) and that are nearly always accessed through point queriesalso known as exact match queries. These are queries with equality comparisons, such as WHERE x = y or WHERE s = `abc' Do use regular (B*Tree) indexes for almost all other columns, which tend to be accessed with a substantial percentage of range (search) queries, bound or unbound. These are queries with inequality comparisons, such as WHERE x > y or WHERE s LIKE"c%".
Don't index columns that must be updated frequently. Also, try to refrain from indexing an y columns on tables with high insert and delete activity. Either of these cases causes continual reorganization of the B*Tree structure and, eventually, poor performance due to index fragmentation. Primary and foreign keys are exceptions.
Index primary and foreign keys. Oracle creates a unique index on defined primary key s whether you create one or not. However, the Oracle default is to not create an index on a foreign key, unless you explicitly do it with a CREATE or ALTER command. Do so, because this enhances performance by not locking the parent table when the child tables are being accessed through DML.
Add additional columns to form composite indexes when sensible. This takes advantage of th e optimizer's preference for index coverage that was discussed earlier. Oracle, unlike Sybase for example, uses a more restricted form of index coverage. Oracle imposes the restriction so that columns can be retrieved from the index only if they form a leading column of that (composite) index. Suppose a table has a composite index on columns a+b+c. The optimizer would use the index for a WHERE clause by using a, a+b, or a+b+c, but not b, c, b+c, nor a+c. For Oracle, index coverage can only occur for left-hand, ordered subsets of the composite index.
Don't index columns that are nearly always used in WHERE clauses with functions or operators (except for MIN or MAX). The optimizer will not use the index in this case. Alternatively, create a derived column or rewrite the query, using hints or tricks to bypass this behavior. Use EXPLAIN PLAN to verify your results.
Don't index columns that are primarily used for negative or NULL comparisons. The Oracle optimizer will not use indexes based on the following comparison operations in WHERE clauses:
Alternatively, rewrite the query in the positive vein using the = or IN operators. If using NULLs, you may need to consider using defaults.
Be wary of using indexing with views and complex subqueries. It is difficult to predict what will occur given the various layers of these types of statements. You must use EXPLAIN PLAN over and over to be sure of what these queries are doing. If you cannot get your queries to behave as you need, consider rewriting them by collapsing the subqueries into one larger query or