1 / 21

101 Ways to get SQL Execution Plan. What is the best option to use?

101 Ways to get SQL Execution Plan. What is the best option to use?. By: Paul Guerin (Originenergy) , Serge Stadnichenko (Originenergy) , Konrad Dear (Telstra) , Yury Velikanov (Pythian) , & All of you. Introductions. Typical responses to poor performance:

laurel-salas
Download Presentation

101 Ways to get SQL Execution Plan. What is the best option to use?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 101 Ways to get SQL Execution Plan. What is the best option to use? By: Paul Guerin (Originenergy), Serge Stadnichenko (Originenergy), Konrad Dear (Telstra), Yury Velikanov (Pythian), & All of you

  2. Introductions Typical responses to poor performance: "We need to buy faster hardware!!!" "Someone has been dropping indexes!!!" "We need to gather stats on the whole database every 5 minutes!!!" "Lets create more materialised views!!!“ "We develop the application for optimal performance!!!“ "Lets use partitioning option!!!" "Lets use parllel option!!!" Inefficient execution plans lead to poor performance and poor scalability.... Few words about - "What is an execution plan?“

  3. Execution plans > AUTOTRACE (SQL*Plus) > EXPLAN PLAN FOR & DBMS_XPLAN > SQL TRACE aka event 10046 > event10053 > Tanel's Dtrace method > V$SQL_PLAN & DBMS_XPLAN.DISPLAY_CURSOR > SQLT (SQLTXPLAIN) tool from Oracle Support

  4. Execution plans - SET AUTOTRACE – the simplest option 19:38:52 SYSTEM:MEGA> set autotrace helpmefindparameters Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] SET AUTOTRACE ON <statement>; SET AUTOTRACE OFF

  5. Execution plans - SET AUTOTRACE – the simplest option + It is most probably the simplest possibe option + Oracle executes an SQL (true execution plan for given session) + Oracle executes an SQL (statistics are reflected) - Oracle executes an SQL (results are reflected, what is thousands rows?) SET AUTOTRACE TRACEONLY - Oracle executes an SQL (results are fetched to client side) - Oracle executes an SQL (what if it takes hours before is starts fetching?) -It doens't provide advantacges that other options have SET AUTOTRACE ON EXPLAIN SET AUTOTRACE ON STATISTICS To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. @$ORACLE_HOME/rdbms/admin/utlxplan.sql @$ORACLE_HOME/sqlplus/admin/plustrce.sql

  6. Execution plans - EXPLAIN PLAN FOR – plan w/t execution -- No statement execution, displays predicted plan only EXPLAIN PLAN FOR <statement>; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

  7. Execution plans - EXPLAIN PLAN FOR – plan w/t execution + It retrives executiion plan witout an SQL execution + It shows predicates for each step (access/filer) + It reflects prallel execution and partitioning related infromation automagicly + It gives you some Hints about the execution (Note: cpu costing is off) - Oracle makes assumptions on bind variables data types (wrong execution plan)

  8. Execution plans - EXPLAIN PLAN FOR – plan w/t execution DBMS_XPLAN.DISPLAY( table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE', statement_id IN VARCHAR2 DEFAULT NULL, format IN VARCHAR2 DEFAULT 'TYPICAL', filter_preds IN VARCHAR2 DEFAULT NULL); format Controls the level of details for the plan. It accepts four values: * BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option. * TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes only PROJECTION, ALIAS and REMOTE SQL information (see below). * SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel. * ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

  9. Execution plans - SET AUTOTRACE – might lie ! REF: Jonathan Lewis http://jonathanlewis.wordpress.com/testing-autotrace/ set autotrace traceonly explain – doens’t execute an SQL

  10. Konrad > SQL TRACE aka event 10046 > event 10053 > Tanel's Dtrace method

  11. Execution plans - v$sql_plan Determine the session and cursor v$session, v$sql: hash_value, address, child_number (9i) sql_id, child_number (11g) Session activity gives clues to what is in the execution plan… v$session_event: db file sequential read, db file scattered read v$session_wait: v$session_longops: Table Scan

  12. Execution plans - v$sql_plan Actual execution plan (9i) truncate table plan_table; -- Following is equivalent to EXPLAIN PLAN FOR <statement> insert into plan_table (statement_id, timestamp, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OPTIMIZER, ID, PARENT_ID, POSITION, SEARCH_COLUMNS, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES) select 0, sysdate, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OPTIMIZER, ID, PARENT_ID, POSITION, SEARCH_COLUMNS, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, OTHER, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES from v$sql_plan where hash_value=&hsh and address='&add' and child_number=&chld; -- Displays the actual plan instead of the predicted plan select * from table(dbms_xplan.display);

  13. Execution plans - v$sql_plan Actual execution plan (10g/11g) -- execution plan for last cursor of session SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR); -- execution plan for a cached cursor (need sql_id + child_number from v$sql) SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>',<child#>)); -- execution plan for an AWR cursor (need sql_id from DBA_HIST_SQLTEXT) SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('<sql_id>')); -- Plan related dynamic views V$SQL_PLAN V$SQL_PLAN_MONITOR Plan level monitoring statistics. V$SQL_BIND_CAPTURE Bind variables used for a cursor. V$SQL_BIND_DATA As above, except for current session.

  14. Execution plans 11g features: DBMS_SQLTUNE.REPORT_SQL_MONITOR(); Create a report that can include explain plan and binds. SQL Plan Baselines Storage of past plans that are considered efficient.

  15. Execution plans - SQLT (SQLTXPLAIN) tool from Oracle Support DEMO SQLT (SQLTXPLAIN) – Tool that helps to diagnose SQL statements performing poorly [ID 215187.1] + It is most complete and comprehansive option + Support 9.2.0.1 to 11.2.0.1 RDBMS versions + Can use many different sources as input - Needs additional instaltion in the production DB + Install in totaly isolated schema (SQLTXPLAIN) - A report generating process is relativly complex (as application owner with additional privileges, multiple stages) - Sometimes generating process is resources intensive

More Related