9.2 EXPLAIN PLAN

 < Day Day Up > 



We have seen extensive use of the EXPLAIN PLAN command in this book so far. Thus far it has not really been essential to describe the nitty-gritty of what precisely the EXPLAIN PLAN command does. From my perspective this type of information falls under the guise of reference material. I find that initially bombarding the reader with too much reference material can cause a lot of confusion and an even larger amount of boredom.

Once again so far in this book you have learned about data model tuning and SQL code tuning. SQL code tuning has been covered from the perspective of writing usable SQL code rather than an in-depth analysis of the tools used to find problems. Therefore, writing SQL code properly in the first place might help to avoid having to use in-depth and overbearing SQL code tuning tools. It is better by far to build properly tuned SQL code without having to immediately resort to masses of interpretive output such as that produced by trace files or STATSPACK.

9.2.1 What Does EXPLAIN PLAN Produce?

The Optimizer creates a query plan for every SQL statement, be it a DML command or a SELECT statement. DML commands are INSERT, UPDATE, DELETE, and MERGE commands. A query plan is selected by the Optimizer as a method of best performance for the execution of an SQL statement. The EXPLAIN PLAN command simply creates readable descriptions of the steps in the query plan and inserts those steps as entries into a table called the PLAN_TABLE.

An SQL statement query plan contains a number of general items. These items describe how an SQL statement should be executed.

  • The sequence in which tables are accessed.

  • Probably most importantly an estimated cost of the query plan and a cost for each step.

  • Less importantly details such as I/O estimates, temporary sort space usage, rows accessed and bytes read, amongst other details.

  • A method of access for each table.

  • Join methods for any joins.

  • Filtering and sorting operations.

  • Other specialized factors such as parallel processing or concatenation.

Note that these operations are not necessarily executed on a table itself but rather what is effectively a row set. A row set is either a filtered or an indexed subset of a table or a resulting set of rows produced by a join. A join is a join between two tables, a table and a row set or even two row sets.

Three things are important to note. Firstly, the Optimizer is not always right. Secondly, the query plan can be changed or at least influenced using hints. Thirdly, the EXPLAIN PLAN command makes a best guess of how the Optimizer should execute a query. This best guess is not the actual query execution plan. Actual query execution plans can be found in the V$SQL_PLAN performance view.

9.2.2 What to Look for in Query Plans

What should one be searching for in query plans with respect to creating high-performance SQL code statements? Bad things! Well what are "bad things"? This is colloquial but it is descriptive. We need to search for anything potentially problematic, slowing down the execution of an SQL statement. We have already seen a lot of potential problems in previous chapters. What are some common "red flags" to watch out for?

  • Full table scans and poor use of indexing, or lack of use of indexing.

    • Inappropriate use of indexing where full table scans can sometimes be faster.

    • Over-indexing on individual tables or too many indexes in a database in general.

  • Large selections from large tables sometimes caused by filtering too late.

  • Unnecessary sorting.

  • Joins.

    • Sort merge joins where nested loops or hash joins can be used.

    • Cartesian joins, outer joins, and anti-joins.

    • Highly complex mutable (multiple table) joins.

    • Noncorrelated subquery semi-joins.

  • Using views, especially multiple layers of views calling subviews.

  • Inherently poorly performing steps like UNION and concatenation operations.

9.2.3 Problems Producing Query Plans

Cost-based optimization is far more efficient than rule-based optimization. Statistics must exist for cost-based optimization to be used. If statistics are out of date then statistics may be inconsistent with data in the database. This can lead to incorrect assessment of the best-performing query plan for SQL code by the Optimizer.

Note 

 Oracle Database 10 Grid   Rule-based optimization is desupported.

Statistics can be copied between different databases using the export (exp) and import (imp) utilities or even using the DBMS_STATS package. There are a number of things of paramount importance to tuning of SQL code with respect to statistics.

  • The existence of statistics in all tables and indexes.

  • Realistic statistics perhaps matching production database environments. Development, testing, tuning, and production databases can often be vastly different physically. Therefore, statistics may vary wildly.

  • Different databases can be placed on different hardware platforms where those different hardware platforms have differing configurations. Those differing configurations can affect how the Oracle Optimizer creates query plans. Different configurations can be differences in numbers of CPUs, storage media such as RAID arrays or not, and perhaps most importantly different Oracle installation configuration parameters. For instance, differences between the SORT_AREA_SIZE and HASH_AREA_SIZE parameters on different database servers can affect Optimizer choices between nested loops and hash joins.

9.2.4 EXPLAIN PLAN Command Syntax

EXPLAIN PLAN       [ SET STATEMENT_ID = 'string' ]       [ INTO [schema.]table[@dblink] ]       FOR sql_statement;

This simplest version of the EXPLAIN PLAN command generates entries directly into the PLAN_TABLE.

EXPLAIN PLAN FOR SELECT * FROM generalledger;

Using this query we can examine the PLAN_TABLE entries.

COL Cost FORMAT 9990; COL Rows FORMAT 999990; COL Bytes FORMAT 99999990; COL Query FORMAT a40; SELECT operation||' '||options||' on '||object_name "Query"       ,cost "Cost"       ,cardinality "Rows"       ,bytes "Bytes" FROM plan_table ORDER BY id;

This is the result of the query.

Query                                Cost      Rows       Bytes --------------------------------   ------   -------   --------- SELECT STATEMENT on                   778    752741    17313043 TABLE ACCESS FULL on GENERALLEDGER    778    752741    17313043

The EXPLAIN PLAN command does not delete any previous entries from the PLAN_TABLE. Thus execution of the previous EXPLAIN PLAN command will add the same two rows again. Thus deletion of the previously added rows is essential to clarity. In this case, we can simply issue the DELETE or TRUNCATE commands to clear the PLAN_TABLE completely. In a multi-user development database this would be a problem. Multiple coders could be generating PLAN_TABLE entries. It would probably irritate other developers to continuously be removing all entries from the PLAN_TABLE. We can use specific identifying values to remove only the PLAN_TABLE rows generated. The SET_STATEMENT_ID = 'string' option can be used to generate session-specific PLAN_TABLE entries and isolate specific output, assuming no other coders are using the same string value.

EXPLAIN PLAN SET STATEMENT_ID = 'TEST'       FOR SELECT * FROM generalledger;

Change the PLAN_TABLE query accordingly.

COL Cost FORMAT 9990; COL Rows FORMAT 999990; COL Bytes FORMAT 99999990; COL Query FORMAT a40; SELECT operation||' '||options||' on '||object_name "Query"       ,cost "Cost"       ,cardinality "Rows"       ,bytes "Bytes" FROM plan_table WHERE statement_id = 'TEST' ORDER BY id;

The optional INTO table clause can be used to place PLAN_TABLE output into a table other than the PLAN_TABLE.

The FOR clause part of the EXPLAIN PLAN command is passed an SQL statement. That SQL statement can be any SQL statement type for which the Optimizer generates a query plan.

  • SELECT.

  • INSERT, UPDATE, DELETE, MERGE.

  • CREATE TABLE AS SELECT ¼

  • CREATE INDEX.

  • ALTER INDEX ¼ REBUILD ¼

Following are some examples. At this stage we will use a more sophisticated version of the PLAN_TABLE query already used in previous chapters (see Appendix B).

EXPLAIN PLAN SET STATEMENT_ID = 'TEST' FOR       INSERT INTO coa(coa#, type, subtype, text) VALUES(          '60007', 'I', 'S', 'Consulting Fees');     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. INSERT STATEMENT on              2        55        1320     EXPLAIN PLAN SET STATEMENT_ID = 'TEST' FOR       UPDATE coa SET text = 'Consulting Fees' WHERE coa# =          '60007';     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. UPDATE STATEMENT on              1         1          19 2.  UPDATE on COA 3.   INDEX UNIQUE SCAN on XPK_COA             1          19     EXPLAIN PLAN SET STATEMENT_ID = 'TEST' FOR       DELETE FROM coa WHERE coa# = '60007';     Query                               Cost      Rows       Bytes -------------------------------   ------   -------   --------- 1. DELETE STATEMENT on                 1         1           7 2.  DELETE on COA 3.   TABLE ACCESS BY INDEX ROWID         on COA                          1         1           7 4.     INDEX UNIQUE SCAN on XPK_COA             55     CREATE TABLE tmp AS       SELECT coa#, type, subtype, text FROM coa WHERE          ROWNUM < 1; INSERT INTO tmp VALUES( '60008', 'I', 'S', 'Other Income'); INSERT INTO tmp VALUES( '60001', 'I', 'S', 'Primary    Sales'); EXPLAIN PLAN SET STATEMENT_ID = 'TEST' FOR       MERGE INTO coa USING tmp ON(coa.coa# = tmp.coa#)       WHEN MATCHED THEN UPDATE SET coa.text = tmp.text       WHEN NOT MATCHED THEN             INSERT VALUES(tmp.coa#, tmp.type, tmp.subtype,                tmp.text,                      NULL, NULL, NULL);     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. MERGE STATEMENT on            9189    369820    26996860 2.  MERGE on COA 3.   VIEW on 4.    HASH JOIN OUTER on            5        82        4920 5.     TABLE ACCESS FULL on TMP     2        82        2542 5.     TABLE ACCESS FULL on COA     2        55        1595     EXPLAIN PLAN SET STATEMENT_ID = 'TEST' FOR       CREATE TABLE tmp AS SELECT * FROM generalledger;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. CREATE TABLE STATEMENT on      778    752741    17313043 2.  LOAD AS SELECT on 3.   TABLE ACCESS FULL on         778    752741    17313043        GENERALLEDGER     EXPLAIN PLAN SET STATEMENT_ID = 'TEST' FOR       CREATE INDEX xak_gl_coa#_dte ON generalledger          (coa#, dte);     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. CREATE INDEX STATEMENT on      778    752741     9032892 2.  INDEX BUILD NON UNIQUE on       XAK_GL_COA#_DT 3.   SORT CREATE INDEX on                752741     9032892 4.    TABLE ACCESS FULL on          GENERALLEDGER             778    752741     9032892     EXPLAIN PLAN SET STATEMENT_ID = 'TEST' FOR       ALTER INDEX xak_gl_coa#_dte REBUILD ONLINE;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. ALTER INDEX STATEMENT on       101    752741     9032892 2.  INDEX BUILD NON UNIQUE on       XAK_GL_COA#_DT 3.   SORT CREATE INDEX on                752741     9032892 4.    TABLE ACCESS FULL on          GENERALLEDGER             101    752741     9032892

9.2.5 How to Create the PLAN_TABLE

Use the script called UTLXPLAN.SQL to create the PLAN_TABLE. This script is located on the database server in the $ORACLE_HOME/rdbms/admin directory on a Unix box and in the ORACLE_HOME\rdbms\admin directory on a Windows machine. The UTLXPLAN.SQL script contains a simple table creation command.

Also in that directory are two other scripts called UTLXPLS.SQL and UTLXPLP.SQL. The latter version involves a reference to a column called OTHER_TAG containing details of parallel execution. Thus use UTLXPLS.SQL for serial execution and UTLXPLP.SQL for parallel execution. These two scripts can be used to display PLAN_TABLE query plans using the DBMS_XPLAN package. I prefer to use a hierarchical query as seen in Appendix B.

Note 

 Oracle Database 10 Grid   Two new columns in the PLAN_TABLE are ACCESS_ PREDICATES and FILTER_PREDICATES.

9.2.6 What is Not Provided in Query Plans?

A query plan will not necessarily tell you everything about an SQL code statement. There are things not included in query plans potentially drastically affecting performance. It is always best to attempt to tune SQL code from the perspective of the SQL code itself and use query plans for verification and fine-tuning. In the extreme, tracing can be used. Timing tests can be used to assess the speed of execution of SQL statements; however, be careful of executing large SQL code statements and killing database performance, particularly on a production database. Small portions of tables can be used for testing and counts using the COUNT function as a wrapper function to minimize performance impact.

SQL> SELECT COUNT(*) FROM(SELECT * FROM generalledger);        COUNT(*) -----------      752741 Elapsed: 00:00:00.03 

Using the COUNT function as a wrapper is a simple and effective timing mechanism. It avoids having to include the display of data into a tool such as SQL*Plus. Displaying thousands or even millions of rows in SQL*Plus will take forever to display and could slow down your database and flood your network. Note that every SQL statement tested in this manner should probably be executed at least twice. This allows loading into buffer caches perhaps accounting for differences in retrieval from buffers and disk. Note that a method of timing such as this may be ineffective running against a highly active concurrent database, especially if concurrent activity fluctuates within the time taken to execute such timing SQL code statements.

So query plans do not tell you everything. Tracing and TKPROF is the next step from using EXPLAIN PLAN. SQL Trace and TKPROF can also be used to determine SQL statement performance on a deeper level by allowing analysis of resource consumption.



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net