1. What is an execution plan)
Execution Plan: the execution process or access path of a query statement in ORACLE.
Ii. How to view the execution plan
1: Press F5 under PL/SQL to view the execution plan. Third-party tools such as toad.
Many people think that the PL/SQL Execution Plan can only see basic information such as the base number, optimizer, and consumption. In fact, this can be set in the PL/SQL tool. You can see a lot of other information, as shown below
2: execute the following steps under SQL * PLUS (PL/SQL command window and SQL window)
Copy codeThe Code is as follows:
SQL> EXPLAIN PLAN
SELECT * from scott. EMP; -- SQL script to be parsed
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY );
3: execute the following command in SQL * PLUS (some commands are invalid in PL/SQL:
Copy codeThe Code is as follows:
SQL> SET TIMING ON -- control display execution time statistics
SQL> SET AUTOTRACE ON EXPLAIN -- this setting includes execution plan, script data output, no statistics
SQL> execute the SQL statement that needs to view the execution plan
SQL> SET AUTOTRACE OFF -- no AUTOTRACE report is generated. This is the default mode.
SQL> SET AUTOTRACE ON -- this setting includes execution plan, statistics, and script data output.
SQL> execute the SQL statement that needs to view the execution plan
SQL> SET AUTOTRACE OFF
SQL> SET AUTOTRACE TRACEONLY -- in this way, the execution plan, statistics, and script data are not output.
SQL> execute the SQL statement that needs to view the execution plan
SQL> SET AUTOTRACE TRACEONLY STAT -- this setting only contains statistics.
SQL> execute the SQL statement that needs to view the execution plan
Set autot [RACE] {ON | OFF | TRACE [ONLY]} [EXP [LAIN] [STAT [ISTICS]
Reference: SQLPlus User's Guide and Reference Release 11.1
Note: PL/SQL Developer does not fully support all SQL * Plus commands, just like set autotrace on. If you execute this command in PL/SQL Developer, an error is returned.
SQL> SET AUTOTRACE ON;
Cannot SET AUTOTRACE
4: SQL _TRACE can be enabled globally as a parameter or in a specific SESSION using commands.
4.1 if it is enabled globally and SQL _TRACE = true is specified in the parameter file (pfile/spfile). When SQL _TRACE is enabled globally, all process activities, including background processes and user processes, will be tracked, it usually causes serious performance problems, so you should be cautious when using it in the production environment.
Tip: by enabling SQL _TRACE globally, we can track the activities of all background processes, abstract descriptions in many documents, and track real-time file changes, we can clearly see the close coordination between processes.
4.2 When setting the current SESSION level, you can trace the current process to find the background database recursive activity of the current operation (which is particularly effective when studying the new features of the database). When studying SQL Execution, you can find the background
Errors.
Copy codeThe Code is as follows:
SQL> alter session set SQL _TRACE = TRUE;
SQL> SELECT * FROM SCOTT. EMP;
SQL> alter session set SQL _TRACE = FALSE;
How can I view related information? No matter whether you execute the preceding script in SQL * PLUS or PL/SQL DEVELOPER, you can use the following script to query the trace log information.
Copy codeThe Code is 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
The help information of TKPROF is as follows:
Copy codeThe Code is as follows:
TKPROF Option
Option description
TRACEFILE: trace the name of the output file
Name of the file in the specified format of OUTPUTFILE
SORT = SORT order of option statements
PRINT = n PRINT the first n statements
EXPLAIN = user/password run the explain plan with the specified user name
INSERT = filename generate INSERT statement
SYS = NO ignore recursive SQL statements run as sys
AGGREGATE = [Y | N] if the specified AGGREGATE = no tkprof is not clustered
Multiple users of SQL text
RECORD = filename records the statements found in the trace file
TABLE = schema. tablename: place the execution plan into the specified TABLE instead of the default PLAN_TABLE.
You can type tkprof in the operating system to obtain a list of all available options and outputs.
Note sorting options include
Sorting options
Prscnt execnt fchcnt call analysis execution extraction times
CPU usage of prscpu execpu fchcpu analysis and Extraction
Time taken by prsela exela fchela analysis to perform Extraction
Number of disk reads during prsdsk exedsk fchdsk analysis and Extraction
Prsqry exeqry fchqry Number of buffer zones used for continuous read during extraction
Number of buffer zones used for current read during prscu execu fchcu analysis execution and Extraction
Prsmis exemis analyzes the number of times that the library cache did not hit during execution
Exerow fchrow: number of rows processed during analysis execution
Userid: User ID of the user who analyzes the cursor
TKPROF statistics
Count: Number of invocation calls
CPU: CPU usage seconds
Elapsed: Total used time
Disk: Number of physical reads
Query: Number of logical reads continuously read
Current: Number of logical reads in the Current Mode
Rows: number of processed Rows
TKPROF statistics
Statistical meaning
Count the number of times of analysis or execution of statements and the number of extracted calls for statements
The processing time of each CPU phase is measured in seconds. If this statement is found in the shared pool, the analysis phase is 0.
Elapsed takes up time in seconds, which is usually not very useful because other processes affect the time used.
The physical data block read by Disk from the database file. If the data is buffered, the statistics may be low.
Query is a logical buffer for continuous reading and retrieval. It is usually used in SELECT statements.
The logical buffer retrieved by Current in Current mode is usually used for DML statements.
The row processed by the Rows external statement is displayed for the SELECT statement in the extraction phase. It is displayed for the DML statement in the execution phase.
The sum of Query and Current is the total number of accessed logical buffers.
Run the following command: tkprof D: \ ORACLE \ PRODUCT \ 10.2.0 \ DB_1 \ RDBMS \ TRACE/wgods_ora_3940.trc h: \ out.txt outputfile explain = etl/etl
After running the preceding command, you can view the generated text file.
Copy codeThe Code is as follows:
TKPROF: Release 10.2.0.1.0-Production on Wednesday May 23 16:56:41 2012
Copyright (c) 1982,200 5, 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 was 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 current 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) OF '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 this 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 processes of other users. In many cases, we need to track processes of other users. instead of the current users, we can use the system packages provided by ORACLE.
DBMS_SYSTEM.SET_ SQL _TRACE_IN_SESSION.
For example:
Copy codeThe Code is 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 codeThe Code is 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 run a script to view the location of the Tracing 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 the wgods_ora_28279.trc file, but the corresponding trace file is not found in the corresponding directory, but the following trace file: wgods_ora_28279_10046.trc
6. Use 10053 events
A bit similar to 10046, skipped here,
7. System View
Through the following system views, you can see some scattered execution plan information. If you are interested, you can study it more.
Copy codeThe Code is 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
3. Understand the execution plan
1. execution sequence
The principle of execution sequence is: from top to bottom, from right to left
From top to bottom: Generally, an execution plan contains multiple nodes at the same level (or in parallel). The execution plan is based on the top and the bottom.
From right to left: multiple sub-nodes exist under a node. The execution starts from the rightmost sub-node.
Of course, you can also use the functions provided by PL/SQL to view the execution sequence. As shown in:
2. Field explanation in the execution plan
SQL>
Glossary:
Recursive cballs recursive call
The number of blocks read by db block gets from buffer cache the number of blocks currently requested. The current mode block indicates the number of blocks being extracted in the operation, instead of generating consistent reads, a query extracts a database at the point at which the query starts, the current block contains data blocks at this time, rather than the number of data blocks before or after this time point.
The number of blocks of undo data read by consistent gets from buffer cache the total number of data requests required for consistent read of data in the Buffer segment ,, the concept here is that when you process your operation, you need to process multiple blocks in the consistent read status. The main reason for these blocks is that during the Query Process, because other sessions operate on data blocks, the blocks to be queried are modified. However, because our queries are called before these modifications, you need to query the front image of the data block in the rollback segment to ensure data consistency. In this way, consistent read is generated.
Physical reads refers to the number of data blocks read from the disk. The main cause is:
1: these blocks do not exist in the database cache.
2: Full table Scan
3: Sort Disks
Redo size DML-generated redo size
Sorts (memory) sorting in memory
Sorts (disk) sorting volume on the disk
2091 bytes sent via SQL * Net to client sent 2091 bytes of data from SQL * Net to client
416 bytes encoded ed via SQL * Net from client sends 416 bytes of data to SQL * Net.
Reference: SQLPlus User's Guide and Reference Release 11.1
The relationships among db block gets, consistent gets, and physical reads can be summarized as follows: Logical read refers to the number of data blocks read from the memory of ORACLE, which is generally:
Consistent gets + db block gets. When the required data block cannot be found in the memory, it needs to be obtained from the disk, so physical read is generated.
3. View Details
1> Plan hash Value
This row is the hash value of this statement. We know that the execution plan generated by ORACLE for each ORACLE statement is placed in the share pool. For the first time, it is hard parsed to generate a hash value. The hash value is compared during next execution. If the hash value is the same, hard Parsing is not performed.
2> COST
COST has no unit and is a relative value. It is used by ORACLE to evaluate the COST of CBO when SQL parses the execution plan in CBO mode and select the execution plan. There is no clear 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: execution sequence, but not the execution sequence. The execution sequence is determined based on the Operation indent (the hierarchy is viewed based on the rightmost and rightmost execution principle. If an action has no sub-ID at the same level, it is executed first. Generally, it is determined by the indent length. If there are two lines of indentation, the first line is the same .)
Operation: the content of the current Operation.
Name: Operation object
Rows: Cardinality (base) earlier than 10 Gb. Oracle estimates the number of Rows returned for the current operation.
Bytes: the number of Bytes returned after this step is executed.
Cost (CPU): indicates the execution Cost of this step, which is used to describe the Cost of SQL Execution.
Time: Oracle estimates the current operation Time.
4. predicate description:
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("B". "MGR" IS NOT NULL)
4-access ("A". "EMPNO" = "B". "MGR ")
Access: indicates that the value of this predicate condition will affect the data Access strength (full table scan or index ).
Filter: indicates that the value of the predicate condition does not affect the data access strength and only filters data.
In the predicate, pay attention to access. Consider the condition of the predicate and check whether the access path used is correct.
5. Dynamic Analysis
If the following prompt appears in the execution plan:
Note
------------
-Dynamic sampling used for the statement
This indicates the technology currently used by CBO, which needs to be taken into account in the analysis plan. When this prompt appears, it indicates that the current table uses dynamic sampling. We can infer that this table may not have been analyzed.
There are two situations:
(1) If the table has not been analyzed, CBO can obtain the analysis data through dynamic sampling, or execute the plan correctly.
(2) If the table has been analyzed but the analysis information is too old, then CBO will not use dynamic sampling, but use the old analysis data, which may lead to incorrect execution plans.
Iv. Table access methods
1. Full Table Scan (FTS) Full Table Scan
2. Index Lookup Index Scanning
There are 5 methods of index lookup:
Index unique scan -- unique index scan
Searching for a value through a UNIQUE index often returns a single ROWID. If the UNIQUE or primary key constraint exists (it ensures that the statement only accesses a single row), ORACLE
Frequent uniqueness Scanning
Method for looking up 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 -- partial index scan
Index range scan is a method for accessing a range values of a particle column. at least the leading column of the index must be supplied to access data via the index. can be used for range operations (e.g.> <> = <= ).
One index is used to access multiple rows of data. A typical case of using index range scanning on a unique index is that range operators (such as>) are used in predicates (WHERE restrictions, <>, >=, <=, BWTEEN)
Index full scan -- index Global scan
Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan wocould be a good idea or not. we choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. for example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.
Index fast full scan -- index quick global scan, which is usually generated without order
Scans all the block in the index, Rows are 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 be used to access second column of concatenated indexes. this is because we are selecting all of the index.
Index skip scan-index skip scan, where condition columns are frequently generated when they are not indexed
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
