One, what is the implementation plan (explain plans)
Execution plan: A description of the execution process or access path of a query statement in Oracle.
Ii. How to view the execution plan
1: Under Pl/sql press F5 to view the execution plan. Third-party tools Toad and so on.
Many people think that Pl/sql's implementation plan can only see the base, optimizer, consumption and other basic information, in fact, this can be set in the Pl/sql tool. You can see a lot of other information, as shown below
2: In Sql*plus (pl/sql Command Window and SQL window are available) perform the following steps
Copy Code code as follows:
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:
Copy Code code as follows:
Sql>set TIMING on--control display execution time statistics
Sql>set autotrace on EXPLAIN--This setting contains execution plans, script data output, no statistics
Sql> Execute SQL statements that need to view execution plans
Sql>set Autotrace Off--the Autotrace report is not generated, which is the default mode
Sql> set Autotrace on--This setting contains execution plans, statistics, and script data output
Sql> Execute SQL statements that need to view execution plans
Sql>set Autotrace off
Sql> Set Autotrace traceonly-This setting will have execution plans, statistics, no script data output
Sql> Execute SQL statements that need to view execution plans
Sql>set autotrace traceonly STAT--This setting contains only statistical information
Sql> Execute SQL statements that need to view execution plans
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, as set autotrace on, and executing this command under the Pl/sql Developer tool will cause an error
Sql> SET autotrace on;
Cannot SET Autotrace
4:sql_trace can be enabled as parameters globally, or in a specific session by command form
4.1 Globally enabled, specifying Sql_trace =true in a parameter file (pfile/spfile) causes all process activities to be tracked, including background processes and user processes, when Sql_trace is enabled globally, often resulting in more serious performance problems. Therefore in the production environment to use carefully.
Tip: By enabling sql_trace globally, we can track the activities of all background processes, many abstract descriptions in the documentation, and we can clearly see the close coordination between the processes by tracking the real-time changes in the file.
4.2 at the current session level, the background database recursive activity of the current operation can be found by tracking the current process (this is especially effective when studying the new features of the database), and when studying SQL execution, it discovers that the background
errors, and so on.
Copy Code code as follows:
Sql> ALTER session SET Sql_trace=true;
Sql> SELECT * from SCOTT. EMP;
Sql> ALTER session SET Sql_trace =false;
So how do you view the relevant information at this time? No matter what you don't see in Sql*plus or Pl/sql developer tools, you can query trace log information from the following script
Copy Code code as follows:
SELECT T.value | | '/' || LOWER (RTRIM (i.instance, CHR (0)) | | ' _ora_ ' | |
P.spid | | '. TRC ' Trace_file_name
From
(SELECT p.spid
From V$mystat M, V$session S, v$process P
WHERE m.statistic# =1
and S.sid = M.sid
and p.addr = S.paddr
P
(SELECT t.instance
From V$thread T, V$parameter V
WHERE 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
Copy Code code as follows:
Tkprof Options
Options description
Tracefile the name of the trace output file
OutputFile the name of the file that has been formatted
Sort order for Sort=option statements
Print=n Print the first n statements
Explain=user/password run EXPLAIN plan with the specified user name
Insert=filename Generate INSERT Statement
Sys=no ignores recursive SQL statements that run as user sys
aggregate=[y| N] If the specified AGGREGATE = no tkprof does not aggregate the same
Multiple users of SQL text
Record=filename records the statements 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 a list of all available options and outputs
Note the sort options are
Sort Options Description
Number of prscnt execnt fchcnt calls profiling execution extraction
PRSCPU execpu fchcpu Profiling the CPU time it takes to perform the extraction
Prsela Exela Fchela Analysis of the time taken to perform the extraction
Prsdsk Exedsk Fchdsk Analysis The number of disk reads performed during extraction
Prsqry exeqry fchqry Analysis of 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 the number of times the library cache misses during the profiling execution
Exerow Fchrow The number of rows processed during profiling execution
User ID of the user who userid the profiling cursor
TKPROF Statistical data
Count: Number of calls performed
Number of seconds used for CPU:CPU
Elapsed: The total time used
Disk: Number of physical reads
Query: Number of logical reads for persistent reads
Current: Number of logical reads in the present mode
Rows: Number of lines processed
TKPROF Statistical information
Statistical implications
Count analyzes or executes the number of statements and the number of fetch calls made for the statement
CPU processing time per phase in seconds if the statement is found in the shared pool for the analysis phase of 0
Elapsed elapsed time in seconds is often not very useful because other processes affect elapsed time
Disk a physical block of data read from a database file if the data is buffered, the statistic may be low
The logical buffer that query retrieves for continuous reading is typically used for SELECT statements
The logical buffers retrieved by current in this mode are typically used for DML statements
Rows external statements are processed by a SELECT statement at the extraction stage to display it for a DML statement during the execution phase
The sum of Query and 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 you execute the above command, you can view the generated text file
Copy Code code as follows:
Tkprof:release 10.2.0.1.0-production on Wednesday May 23 16:56:41 2012
Copyright (c) 1982, +, Oracle. All rights reserved.
Trace FILE:D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/WGODS_ORA_3940.TRC
Sort Options:default
********************************************************************************
Count = number of times OCI procedure is executed
CPU = CPU time in seconds executing
elapsed = elapsed time in seconds executing
Disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
Current = number of buffers gotten in-mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
ALTER session SET Sql_trace = TRUE
Call count CPU Elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse:0
Misses in library cache during execute:1
Optimizer Mode:choose
Parsing user id:89 (ETL)
********************************************************************************
Begin:id: = sys.dbms_transaction.local_transaction_id; End
Call count CPU Elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Total 4 0.00 0.00 0 0 0 2
Misses in library cache during parse:0
Optimizer Mode:choose
Parsing user id:89 (ETL)
********************************************************************************
SELECT *
From
SCOTT. Emp
Call count CPU Elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Total 4 0.00 0.00 0 7 0 14
Misses in library cache during parse:1
Optimizer Mode:choose
Parsing user id:89 (ETL)
Rows Execution Plan
------- ---------------------------------------------------
SELECT STATEMENT Mode:choose
Table ACCESS mode:analyzed (full) ' EMP ' (table)
********************************************************************************
ALTER session SET Sql_trace = FALSE
Call count CPU Elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse:1
Optimizer Mode:choose
Parsing user id:89 (ETL)
********************************************************************************
Overall totals for all non-recursive statements
Call count CPU Elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 2
Fetch 1 0.00 0.00 0 7 0 14
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Total 11 0.00 0.00 0 7 0 16
Misses in library cache during parse:2
Misses in library cache during execute:1
Overall totals for all recursive statements
Call count CPU Elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse:0
User SQL statements in session.
Internal SQL statements in session.
SQL statements in session.
Statement explained in the session.
********************************************************************************
Trace FILE:D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/WGODS_ORA_3940.TRC
Trace file compatibility:10.01.00
Sort Options:default
Session in Tracefile.
User SQL statements in trace file.
Internal SQL statements in trace file.
SQL statements in trace file.
Unique SQL statements in trace file.
SQL statements explained using schema:
Etl.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
lines in trace file.
Elapsed seconds in trace file.
4.3 Tracking the process of other users, in many cases we need to track other user processes, not the current user, can be provided through the Oracle System Package
Dbms_system. Set_sql_trace_in_session to complete.
For example:
Copy Code code as follows:
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 Use 10046 Events
Copy Code code as follows:
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_name
From
(SELECT p.spid
From V$mystat M, V$session S, v$process P
WHERE m.statistic# =1
and S.sid = M.sid
and p.addr = S.paddr
P
(SELECT t.instance
From V$thread T, V$parameter V
WHERE 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
The query result is WGODS_ORA_28279.TRC file, but the corresponding directory does not find the corresponding trace file, but the following trace file: wgods_ora_28279_10046.trc
6 Use 10053 Events
A bit like 10046, here skipped,
7 System View
Some of the following system views, you can see some of the scattered implementation of the relevant information, you can be interested to study more.
Copy Code code as follows:
SELECT * from V$sql_plan
SELECT * from v$rsrc_plan_cpu_mth
SELECT * from V$sql_plan_statistics
SELECT * from V$sql_plan_statistics_all
SELECT * from V$sqlarea_plan_hash
SELECT * from V$rsrc_plan_history
Third, understand the implementation plan
1. Order of execution
The principle of execution order is: from 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 nodes, by the priority of execution, down the post-execution
Right-to-left: Multiple child nodes exist under a node, starting with the most right child node.
Of course, you can also view the order of execution in the Pl/sql tool through the functionality it provides. As shown in the following illustration:
2. Explanation of the fields in the execution plan
Sql>
Noun Explanation:
Recursive calls recursive call
The number of blocks that DB block gets reads from buffer cache the number of block currently requested, and the current pattern blocks mean the number of blocks that are just extracted in the operation, rather than in the case of consistent read, a query extracts a block that is at the beginning of the query query. The current block is the number of blocks of data that exist at this time, not before or after the point.
Consistent gets the number of data requests from the block of the undo data read from buffer cache in the rollback segment buffer data Consistency read the required data blocks, the concept here is when you're dealing with this operation you need to handle multiple block, the main reason these blocks are generated is because you are in the process of querying, because other sessions operate on blocks of data and modify the blocks to be queried, because our query was invoked before these modifications, you need to query the front image of the block in the rollback segment to ensure data consistency. This results in consistent reading.
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
Size of the redo size DML-generated redo
Sorts (memory) the amount of sorting performed in memory
Sorts (disk) the amount of sorting 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
The relationship between DB block gets, consistent gets, and physical reads can be summed up as follows: Logically read refers to the number of blocks of data that Oracle reads from memory, typically:
Consistent gets + db block gets. When the required block of data is not found in memory, it needs to be fetched from the disk, thus generating a physical read.
3. View specific content
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 for the first time by hard parsing to produce a hash value. The hash value is compared the next time it is executed, and if the same does not perform hard parsing.
2> Cost
Cost is not a unit, is a relative value, is the SQL in the CBO resolution of the execution plan, for Oracle to evaluate CBO costs, select execution plan. There is no clear meaning, but in contrast it is very useful.
Formula: cost= (single block I/O cost + multiblock I/o cost + CPU cost)/Sreadtim
3> an explanation of the above Execution Plan column fields:
Id: Executes the sequence, but not in the order in which it is executed. The execution is based on Operation indentation (using the principle of the most right and first execution to see the hierarchy, at the same level if an action does not have a child ID on the first execution.) Generally according to the indentation length to determine, indent the largest first execution, if there are 2 lines indented, then the first execution above. ）
Operation: The contents of the current operation.
Name: Action Object
Rows: That is, the previous cardinality (cardinality) of the 10g version, Oracle estimates the number of rows of the returned result set for the current operation.
Bytes: Represents the number of bytes returned after performing this step.
Cost (CPU): represents one of the execution costs of executing to this step to illustrate 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 Lu Jing (full table scan or index) of the data.
Filter: A value that represents a predicate condition does not affect the access path strength of the data, only acts as a filter.
The main focus in the predicate is access, to consider the condition of the predicate, and to use the correct access path.
5. Dynamic analysis
If you have the following prompts in the execution plan:
Note
------------
-dynamic sampling used for the statement
This prompts the user to use the technology that the CBO is currently using, requiring 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 things that happen here:
(1) If the table has not been analyzed, then the CBO can use the dynamic sampling method to obtain the analysis data, can also or correctly execute the plan.
(2) If the table is analyzed, but the analysis information is too old, then the CBO will not use dynamic sampling, but rather use these old analysis data, which may lead to the wrong execution plan.
Four, table access mode
1.Full table Scan (FTS) Full table scan
2.Index Lookup Index Scan
There are 5 methods of index lookup:
Index unique Scan--indexed unique scan
Finding a value through 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
Always implement a unique scan
method to looking up a single key value via a unique index. Always returns a single value, your must supply at least the leading column of the ' Index to access data via the index.
Index range Scan--indexed local scan
Index range Scan is a to 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, the typical case for using an index range scan on a unique index is to use a range action symbol (e.g., < <> >=, <=,bwteen) in the predicate (where constraints).
Index full Scan--indexed global scan
Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good Idea or not. We choose a index full Scan when we have statistics that indicate this it is going to being more efficient than a full table Scan and a sort. For example we/may does a full index scan as we do a unbounded scan the ' an index and want the ' data to is ordered in Dex order.
Index fast full Scan-index quick global scan, often occurs without order by
Scans all the blocks in the index, Rows are not returned in sorted order, introduced in 7.3 and requires =true and CBO, May is hinted using index_ffs hint, uses multiblock I/O, can is executed in parallel, can is used to access Second column of concatenated indexes. This is because we are selecting all of the index.
Index skip Scan-index jump scans, where condition columns are not indexed, often occur
Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the "the" the "during" search.
3.Rowid Physical ID Scan
This is the quickest access method available. Oracle retrieves the "specified block and extracts" it is interested in.--rowid scan is the fastest way to access data
