Oracle Execution Plan

Oracle Database View Execution plan

Oracle-based application system many performance problems are caused by poor application SQL performance, so, SQL performance optimization is very important, analysis and optimization of SQL performance we generally look at the SQL implementation plan, this article on how to understand the implementation plan, And how to optimize SQL by analyzing the execution plan.

I. What is the implementation plan (explain plans)

Execution plan: A description of the execution process or access path of a query statement in Oracle.

Second, how to view the execution plan

1: Under PL/sql, press F5 to view the execution plan. Third-party tools toad, etc.

Many people think that PL/SQL's execution plan can only see basic information such as cardinality, optimizer, and consumption, which can be set in the PL/SQL tool. You can see a lot of other information, as shown below

2: Perform the following steps in the Sql*plus (both PL/SQL command windows and Windows)

Sql>explain PLAN for          SELECT * from SCOTT. EMP;  --The SQL script to parse Sql>select * from TABLE (Dbms_xplan. DISPLAY);

3: Under Sql*plus (some commands are not valid under PL/SQL), execute the following command:

Sql>set TIMING on-controls display execution time statistics Sql>set AUTOTRACE on EXPLAIN-This setting includes execution plan, script data output, no statistics sql> execution required View execution Plan SQL statement sql>set AUTOTRACE OFF-no AUTOTRACE report is generated, this is the default mode sql> set AUTOTRACE on-This setting contains the execution plan, statistics , and the script data output sql> execute the SQL statement that needs to view the execution plan Sql>set AUTOTRACE offsql> set AUTOTRACE traceonly-This setting will have execution plans, statistics, and no script data output s Ql> execute the SQL statement that needs to view the execution plan sql>set AUTOTRACE traceonly STAT     -This setting contains only statistics sql> Execute SQL statements that need to view the execution plan

SET Autot[race] {on | OFF | Trace[only]} [Exp[lain]] [stat[istics]

Reference Document: SQLPlus User's Guide and Reference Release 11.1

Note: the PL/SQL Developer tool does not fully support all Sql*plus commands, such as set AUTOTRACE on, which can be an error when executing this command under the PL/SQL Developer tool

Sql> SET AUTOTRACE on;

Cannot SET AUTOTRACE

4:sql_trace can be enabled globally as a parameter, or it can be enabled in a specific session via the command form

4.1 At global Enable, specify Sql_trace =true in the parameter file (pfile/spfile), which causes all process activity to be traced when Sql_trace is enabled globally, including background processes as well as user processes, often leading to more serious performance issues. So use caution in production environments.

Tip: By enabling sql_trace globally, we can track the activity of all background processes, many abstract descriptions in the documentation, and by tracking the real-time changes in the file, we can clearly see the close coordination between the processes.

4.2 at the current session level, by tracing the current process can discover the current operation of the background database recursive activity (this is particularly effective when studying the new features of the database), the study of SQL execution, the discovery background

Errors and so on.

sql> ALTER SESSION SET sql_trace=true; Sql> SELECT * from SCOTT. EMP; sql> ALTER SESSION SET sql_trace =false;

So how do I see the information at this point? Whether you're in sql*plus or PL/SQL developer tools, you can't see any information after you execute the above script, you can query the trace log information by using the following script

SELECT T.value | | '/' | | LOWER (RTRIM (i.instance, CHR (0))) | | ' _ora_ ' | | P.spid | | '. TRC ' Trace_file_namefrom (SELECT p.spidfrom v$mystat M, v$session S, v$process pwhere m.statistic# =1and S.SID = M.SIDAN  D p.addr = s.paddr) P, (SELECT t.instancefrom v$thread T, v$parameter vwhere v.name = ' THREAD ' and (v.value = 0 OR t.thread# = To_number (V.value))) I, (SELECT VALUE from V$parameter WHERE name= ' user_dump_dest ') T

Tkprof's help information is as follows

The TKPROF option option describes the name of the tracefile trace output file OUTPUTFILE                                 The name of the formatted file sort=option the sort order of the statement print=n                         Print the first n statements Explain=user/password run with the specified user name EXPLAIN PLAN insert=filename Generate INSERT Statement Sys=no ignores recursive SQL statements that run as user SYS aggregate=[y| N] If you specify AGGREGATE = no TKPROF multiple users who do not aggregate the same SQL text record=       FileName records the statement found in the trace file table=schema.tablename the execution plan into the specified table instead of the default plan_table             You can type tkprof in the operating system to get all available options and the output of the list note sort option has a sort option description prscnt execnt fchcnt  The number of times the parse execution fetch was called PRSCPU EXECPU fchcpu analyze the CPU time taken to perform the fetch Prsela Exela Fchela analyze the elapsed time taken to perform the fetch Prsdsk Exedsk FCHDSK Analysis of disk reads during extraction prsqry EXeqry Fchqry Analysis The number of buffers used for continuous reads during extraction Prscu EXECU FCHCU Analysis The number of buffers used for the current read during extraction Prsmis Exemis        Number of times library cache misses were parsed during execution Exerow Fchrow parsing the number of rows processed during execution userid parsing the user ID of the cursor TKPROF Statistics Count: Number of CPU:CPU to execute calls Elapsed: Total time spent Disk: physical read count Query: Number of logical reads Read continuously: Current mode Number of logical reads rows: Number of rows processed TKPROF statistics meaning count counts the number of times the parsing or execution of a statement and the number of fetch calls issued for a statement CPU processing time per stage in seconds if you are looking in a shared pool To this statement is typically not very useful for the analysis phase of 0 Elapsed take-up time in seconds because other processes affect the physical data block read by Disk from the database file if the data is buffered then the statistic may be very low the query is a logical buffer for continuous read retrieval Often used in the SELECT statement, current mode retrieves the logical buffers that are typically applied to DML statements that are processed by rows outside the row for the SELECT statement to show it in the fetch stage for DML statements during the execution phase of the query  Sum of current is the total number of logical buffers that are accessed

Execute the following command: Tkprof d:\oracle\product\10.2.0\db_1\rdbms\trace/wgods_ora_3940.trc h:\out.txtoutputfile Explain=etl/etl

After executing the above command, you can view the generated text file

View Code

4.3 track the processes of other users, and in many cases we need to track the processes of other users, not the current user, the system packages available through Oracle

Dbms_system. Set_sql_trace_in_session to finish.

For example:

SELECT SID, serial#, USERNAME from v$session WHERE USERNAME = ' ETL ' EXEC dbms_system. Set_sql_trace_in_session (61,76,true); EXEC Dbms_system. Set_sql_trace_in_session (61,76,false);

5 Using 10046 Events

ALTER SESSION SET tracefile_identifier = 10046; ALTER SESSION SET events= ' 10046 Trace name Context forever, Level 8 '; SELECT * from SCOTT. EMP; ALTER SESSION SET EVENTS = ' 10046 trace name context off '; then you can use the script to view the location of the trace file Select T.value | | '/' | | LOWER (RTRIM (i.instance, CHR (0))) | | ' _ora_ ' | | P.spid | | '. TRC ' Trace_file_namefrom (SELECT p.spidfrom v$mystat M, v$session S, v$process pwhere m.statistic# =1and S.SID = M.SIDAN  D p.addr = s.paddr) P, (SELECT t.instancefrom v$thread T, v$parameter vwhere v.name = ' THREAD ' and (v.value = 0 OR t.thread# = To_number (V.value))) I, (SELECT VALUE from V$parameter WHERE name= ' user_dump_dest ') t query result is WGODS_ORA_28279.TRC file, But to the corresponding directory but did not find the corresponding tracking file, but the following trace file: wgods_ora_28279_10046.trc

6 Using 10053 Events

A little bit like 10046, over here,

7 System View

With some of the following system views, you can see some information about the scattered execution plans, and if you are interested, you can do more research.

SELECT * from V$sql_planselect * from V$rsrc_plan_cpu_mthselect * to V$sql_plan_statisticsselect * from V$sql_plan_stati Stics_allselect * from V$sqlarea_plan_hashselect * from V$rsrc_plan_history

Third, read the implementation plan

1. Execution order

The principle of order of execution is: top to bottom, right to left

Top to bottom: In the execution plan generally contains multiple nodes, the same level (or side-by-side) of the node, upper priority execution, down the next execution

Right-to-left: multiple child nodes exist under a node, starting with the right child node.

Of course, you can also see the order of execution in the PL/SQL tool by the features it provides. As shown in the following:

2. Explanation of the fields in the execution plan

Sql>

Noun Explanation:

Recursive calls recursive invocation

DB Block gets the number of blocks that are read from the buffer cache, the current block count is the number of blocks that are exactly fetched in the operation, rather than the normal case in which a query is fetched when the query query begins. The current block is a data block at this time, not the number of blocks before or after this point in time.

Consistent gets the number of block of undo data that is read from the buffer cache data requests total data blocks required for data consistency reading in the rollback segment buffer, where the concept is that when you are dealing with this operation, you need to handle multiple on a consistent read state. Blocks, the main reason for these blocks is because you are in the process of querying, because other sessions on the block of data, and the blocks to be queried, but because our query is called before these changes, we need to rollback segments of the data block of the pre-image query to ensure data consistency. This creates a consistent read.

Physical reads physical reading is the number of data blocks read from disk. The main reasons for this are:

1: These blocks do not exist in the database cache.

2: Full table scan

3: Disk Sorting

Redo size of redo generated by DML

Sorts (memory) the sort amount that is executed in RAM

Sorts (disk) the sort amount that is performed on the disks

2091 Bytes sent via sql*net to client sends 2091 bytes of data from Sql*net to clients

The 416 bytes received via sql*net from client clients sent 416 bytes of data to sql*net.

Reference Document: SQLPlus User's Guide and Reference Release 11.1

DB block get, consistent gets, physical reads these three relationships can be summed up as follows: Logical reading refers to the number of blocks of data that Oracle reads from memory, generally:

Consistent gets + db block gets. When the required block of data is not found in memory, it needs to be fetched from the disk, resulting in a physical read.

3. Specific content View

1> Plan Hash Value

This line is the hash value of this statement, and we know that Oracle's execution plan for each Oracle statement is placed in the share pool, the first time to undergo a hard parse, resulting in a hash value. The hash value is compared the next time it is executed, and the hard parse is not performed if the same.

2> Cost

Cost no unit, is a relative value, is the SQL in the CBO to parse the execution plan, for Oracle to evaluate the CBO costs, choose the execution plan. There is no definite meaning, but it is very useful in comparison.

Formula: cost= (single Block I/O cost + multiblock I/o cost + CPU cost)/Sreadtim

3> explanation of the above Execution Plan column fields:

Id: Executes the sequence, but not the order of execution. The execution is based on the operation in the judgment (using the most right-most first execution principle to see the hierarchical relationship, at the same level if an action does not have a child ID is the first to execute. Generally according to the indentation length to determine, indent the largest first execution, if there are 2 lines indented the same, then first execute the above. ）

Operation: The contents of the current operation.

Name: Action Object

Rows: This is the previous cardinality (cardinality) of the 10g version, and Oracle estimates the number of returned result set rows for the current operation.

Bytes: Indicates the number of bytes returned after performing this step.

Cost (CPU): Represents the execution costs of executing to this step and is used to describe the cost of SQL execution.

Time:oracle estimates the time of the current operation.

4. Predicate Description:

predicate information (identified by Operation ID):

---------------------------------------------------

2-filter ("B". " MGR "is not NULL)

4-access ("A". ") EMPNO "=" B "." MGR ")

Access: The value that represents this predicate condition will affect the access brutishness (full table scan or index) of the data.

Filter: The value that represents the predicate condition does not affect the data access path, only the filtering effect.

The main note in predicates is access, which is to consider the conditions of the predicate, using the correct access path.

5. Dynamic Analysis

If you have the following hints in the execution plan:

Note

------------

-dynamic sampling used for the statement

This hints at the technology currently used by the user CBO and requires the user to take these factors into account when analyzing the plan. When this prompt appears, the current table uses dynamic sampling. We thus infer that the table may not have been analyzed.

There are two scenarios:

(1) If the table has not been analyzed, the CBO can obtain the analysis data by dynamic sampling, or execute the plan correctly.

(2) If the table is analyzed, but the analysis information is too old, then the CBO will not use dynamic sampling, but instead use these old analysis data, which may lead to the wrong execution plan.

Four, table access mode

1.Full table Scan (FTS) Full table scanning

2.Index Lookup Index Scan

There is 5 methods of index lookup:

Index unique Scan--indexes only scanning

Finding a value by a unique index often returns a single rowid if there is a unique or PRIMARY KEY constraint (which guarantees that the statement only accesses a single line), ORACLE

Unique scans are often implemented

Method for Looking-a single key value via a unique index. Always returns a single value, you must supply at LEAST the leading column of the index to access data via the index.

Index range Scan--indexed local scans

Index range Scan is a method for accessing a range values of a particular column. At LEAST the leading column of the index must is supplied to access data via the index. Can is used for range operations (e.g. > < <> >= <= between).

Using an index to access multiple rows of data, a typical case of using an indexed range scan on a unique index is the use of a range operation symbol in a predicate (where restriction) (e.g., < <>, >=, <=,bwteen)

Index full Scan--indexed global scan

Full index scans is only available in the CBO as otherwise we is unable to determine whether a full scan would be a good Idea or not. We choose an index full Scan if we have statistics this indicate that it was going to being more efficient than a full table Scan and a sort. For example we could do a full index scan if we do an unbounded scan of an index and want the data to is ordered in the Dex order.

Index fast full Scan--indexing quick global scan, often occurs without order by

Scans all the block in the index, Rows is not returned in sorted order, introduced in 7.3 and requires v733_plans_enabled =true and CBO, may be hinted using index_ffs hint, uses multiblock I/O, can be executed in parallel, can is used to access Second column of concatenated indexes. This is because we were selecting all of the index.

Index skip Scan--indexed hop scanning, where condition columns are non-indexed and often occur

Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column (s) during the search.

3.Rowid Physical ID Scan

This is the quickest access method available. Oracle retrieves the specified block and extracts the rows it is interested in.--rowid scan is the fastest way to access data

Category: Oracle

Oracle Execution Plan