oracle what to do when there is a bad execution plan

Hi,

I will explain What is the Execution Plan and How to Generate Execution Program in this commodity.

What Is an Execution Programme ?

Each Database direction system similar Oracle generates an execution program for SQL Statements to nowadays to the execution engine for implementation which is the output of the optimizer.

If you want to learn more details about Optimizer, You lot should read the post-obit article before this.

What is the Oracle Optimizer and How Cost-Based Optimizer Works ? | Oracle Database Performance Tuning Tutorial -iii

Oracle Execution Plan

Execution plan consists of the instructions for the execution engine about the operations information technology must perform for retrieving the data nearly efficiently.

For Example; You want to become to B indicate ( London) from A signal ( Istanbul ). At that place are several ways for this travel as follows.

• Airplane
• Machine
• Train
• Send
• Bicycle
• Walking

You lot have several means for going to London, simply each of these vehicles' cost is different. You should decide which one to use.

Database management arrangement is running like this example.

Execution program steps are performed in a parent-child human relationship between steps, they are not performed in the gild in which they are numbered. Yous tin can see the following information in the Execution programme steps.

• An ordering of the tables referenced by the SQL statement
• An admission method for each table
• A join method for tables
• Data operations, such as filter, sort, or aggregation
  

Execution plan is a vital point for Performance tuning of Oracle database, then Where to access or Find Execution Plans?

• PLAN_TABLE (SQL Developer or SQL*Plus)
• 5$SQL_PLAN (Library Cache)
• V$SQL_PLAN_MONITOR
• DBA_HIST_SQL_PLAN (AWR)
• SQL tuning set
• STATS$SQL_PLAN (Statspack)
• SQL management base (SQL plan baselines)
• Upshot 10053 trace file
• Trace files generated by DBMS_MONITOR
• Process land dump trace file

View Execution Plan in Oracle

There are lots of ways to view Execution plan in Oracle as follows.

• The Explain Plan command
  a.SELECT from PLAN_TABLE
  b.DBMS_XPLAN.Display()
•  SQL*Plus Autotrace: Ready AUTOTRACE ON
  • DBMS_XPLAN.DISPLAY_CURSOR()
  • DBMS_XPLAN.DISPLAY_AWR()
  • DBMS_XPLAN.DISPLAY_SQLSET()
  • DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE()

Oracle Explain Plan

Explicate PLAN command generates the execution program and stores the plan in PLAN_TABLE ( tabular array ). Explain Plan is just generates the program, it doesn'texecute the SQL statement itself.

PLAN_TABLE is created automatically as a global temporary table to go along the output of an Explicate PLAN statement for all users.

Yous can create your own PLAN_TABLE executing the $ORACLE_HOME/rdbms/admin/utlxplan.sql script, if you lot want to continue the execution programme information for a long term.  You lot can query the PLAN_TABLE via DBMS_XPLAN.Brandish().

You can use EXPLAIN Plan , DBMS_XPLAN.Display() equally follows to see execution plan of whatsoever specific sql.

SQL>  SQL>                      Explicate PLAN SET STATEMENT_ID                    = 'Deveci'                      FOR                    SELECT * FROM MEHMET.DEVECI;  Explained.  SQL>  SQL> SET LINESIZE 130 SQL> Set up PAGESIZE 0                      SQL> select * from table(DBMS_XPLAN.DISPLAY());                      Program hash value: 1925475714  ----------------------------------------------------------------------------------------------- | Id  | Functioning                            | Proper name   | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------- |   0 | SELECT Statement                     |        |    14 |   420 |     3   (0)| 00:00:01 | |   1 |  TABLE ACCESS STORAGE Total Kickoff ROWS| DEVECI |    14 |   420 |     3   (0)| 00:00:01 | -----------------------------------------------------------------------------------------------  Annotation -----    - dynamic statistics used: dynamic sampling (level=2)  12 rows selected.  SQL>

EXPLAIN PLAN command instance as in the above is Typical option. You tin can use ALL option which is used with the DISPLAY function allows y'all to output the maximum user level data (IncludesTYPICAL level, with additional information such as PROJECTION, Alias, and information about REMOTE SQL)

You can apply PLAN_TABLE : ALL selection as follows and come across detailed information about SQL.

SQL>  SQL> select * from table(DBMS_XPLAN.DISPLAY(zilch,null,'ALL')); Plan hash value: 1925475714  ----------------------------------------------------------------------------------------------- | Id  | Operation                            | Name   | Rows  | Bytes | Price (%CPU)| Time     | ----------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                     |        |    fourteen |   420 |     3   (0)| 00:00:01 | |   one |  Table ACCESS STORAGE FULL FIRST ROWS| DEVECI |    14 |   420 |     3   (0)| 00:00:01 | -----------------------------------------------------------------------------------------------  Query Cake Name / Object Alias (identified past operation id): -------------------------------------------------------------     one - SEL$1 / [email protected]$1  Column Project Information (identified by operation id): -----------------------------------------------------------     1 - "DEVECI"."ID"[NUMBER,22], "DEVECI"."Proper name"[VARCHAR2,30]  Notation -----    - dynamic statistics used: dynamic sampling (level=two)  22 rows selected.  SQL>        

You lot can use Enterprise director, TOAD and SQL Developer to view Explicate plan and Execution plan by clicking button marked xanthous ( in TOAD )  as follows.

SQL*Plus Autotrace

You tin automatically get a written report forexecution plan and the statement execution statistics by using Autotrace, if you use SQL*Plus or SQL Developer. You must have a PLAN_TABLE available in your schema for using this feature.

You can enable AUTOTRACE in various ways using the syntax every bit follows.

• OFF : Disables autotracing SQL
• ON : Enables autotracing SQL
• TRACE or TRACE[ONLY] : Enables autotracing SQL and suppresses statement output
• EXPLAIN : Displays execution plans, only does not display statistics
• STATISTICS : Displays statistics, simply does not display execution plans

You can employ AUTOTRACE options as follows to meet execution programme and its details.

To start tracing SQL using AUTOTRACE, execute the following script.

          SQL> set autotrace on        

                      SQL> prepare autotrace on                      SQL> SELECT * FROM MEHMET.DEVECI where id=two;          ID Proper noun ---------- ------------------------------          ii Salih          2 Salih   Execution Plan ---------------------------------------------------------- Plan hash value: 1925475714  -------------------------------------------------------------------------------- ---------------  | Id  | Operation                            | Proper noun   | Rows  | Bytes | Cost (%C PU)| Time     |  -------------------------------------------------------------------------------- ---------------  |   0 | SELECT STATEMENT                     |        |     2 |    60 |     3 (0)| 00:00:01 |  |*  ane |  Tabular array Access STORAGE Full FIRST ROWS| DEVECI |     2 |    60 |     3 (0)| 00:00:01 |  -------------------------------------------------------------------------------- ---------------   Predicate Information (identified past operation id): ---------------------------------------------------     i - storage("ID"=two)        filter("ID"=2)  Note -----    - dynamic statistics used: dynamic sampling (level=2)   Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets           8  consistent gets           0  physical reads           0  redo size         668  bytes sent via SQL*Net to client         552  bytes received via SQL*Cyberspace from client           2  SQL*Cyberspace roundtrips to/from client           0  sorts (retentivity)           0  sorts (disk)           2  rows candy  SQL>

Display the execution plan only without execution, execute the following script.

          SQL> set autotrace traceonly explicate                  

                      SQL> set autotrace traceonly explain                    SQL> SELECT * FROM MEHMET.DEVECI where id=ii;  Execution Program ---------------------------------------------------------- Plan hash value: 1925475714  -------------------------------------------------------------------------------- ---------------  | Id  | Performance                            | Name   | Rows  | Bytes | Cost (%C PU)| Time     |  -------------------------------------------------------------------------------- ---------------  |   0 | SELECT STATEMENT                     |        |     2 |    60 |     3 (0)| 00:00:01 |  |*  one |  Table ACCESS STORAGE FULL FIRST ROWS| DEVECI |     2 |    60 |     3 (0)| 00:00:01 |  -------------------------------------------------------------------------------- ---------------   Predicate Information (identified past operation id): ---------------------------------------------------     1 - storage("ID"=2)        filter("ID"=2)  Note -----    - dynamic statistics used: dynamic sampling (level=two)  SQL>

Brandish rows and statistics, execute the post-obit script.

          SQL> set autotrace on statistics                  

                      SQL> set autotrace on statistics          SQL> SELECT * FROM MEHMET.DEVECI where id=2;  ID Proper name ---------- ------------------------------ 2 Salih 2 Salih   Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 668 bytes sent via SQL*Cyberspace to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows candy  SQL>

Get the plan and the statistics but (suppress rows), execute the following script.

          SQL> fix autotrace traceonly        

                      SQL> set autotrace traceonly                                SQL> SELECT * FROM MEHMET.DEVECI where id=ii;   Execution Plan ---------------------------------------------------------- Plan hash value: 1925475714  -------------------------------------------------------------------------------- ---------------  | Id  | Functioning                            | Name   | Rows  | Bytes | Toll (%C PU)| Time     |  -------------------------------------------------------------------------------- ---------------  |   0 | SELECT STATEMENT                     |        |     2 |    60 |     3 (0)| 00:00:01 |  |*  one |  Table ACCESS STORAGE Full FIRST ROWS| DEVECI |     2 |    60 |     3 (0)| 00:00:01 |  -------------------------------------------------------------------------------- ---------------   Predicate Information (identified by operation id): ---------------------------------------------------     1 - storage("ID"=2)        filter("ID"=2)  Note -----    - dynamic statistics used: dynamic sampling (level=2)   Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets           8  consistent gets           0  concrete reads           0  redo size         668  bytes sent via SQL*Net to client         552  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           2  rows processed  SQL>  SQL>  SQL>  SQL>

I will continue to explain how to Interpret Execution Plans in the next article.

Next post link near Operation Tuning Tutorial is as follows.

How to Read or Interpret an Execution Programme in Oracle | Oracle Database Performance Tuning Tutorial -five

Practice you desire to learn Top 30 SQL Tuning Tips and Tricks, so read the following articles.

SQL Tuning Tips and Tricks Tutorial in Oracle -3

two,942 views last calendar month,  1 views today

lyleshawat.blogspot.com

Source: https://ittutorial.org/what-is-the-execution-plan-and-how-to-view-execution-plan-oracle-database-performance-tuning-tutorial-4/

Share this post