A small amount of SQL execution. We can basically analyze the time to perform the time of the SQL statement through the implementation plan. Connection order and waste of database resources, tell the efficiency of the SQL statement execution, the following is simple to introduce the use of the implementation plan
2. EXPLAIN
Oracle RDBMS performs each SQL statement and must be evaluated by Oracle Optimizer. To understand how the optimizer is selected (search) path and how index is used, it is very helpful to optimize the SQL statement. Explain can quickly and easily identify how the search path is obtained for how to get query data in a given SQL statement (we are often referred to as Access Path). This makes us choose the optimal query to achieve the maximum optimization effect.
2.1. Installation
To use the execution plan, you first need to perform the corresponding script.
Using the Explain tool to create an Explain_PLAN table, this must first enter the account of the relevant application table, the view, and the owner of the index. Oracle's media contains SQL source programs that perform this work, for example:
ORA_RDBMS: XPLAINPL.SQL (VMS)
$ Oracle_Home / Rdbms / Admin / UTLXPLAN.SQL (UNIX)
After this script generates a table this program creates a table named Plan_Table, the table structure is as follows:
We briefly introduce the main field meaning:
Field Name Field Type Meaning STATEMENT_ID VARCHAR2 (30) The optimal Statement_ID parameter value specified in the Explain PLAN statement, if set statement_id is not used in the Explan PLAN statement, this value will be set to NULL. Remarks VARCHAR2 (80) The annotation of the annotation of each step of the explanation plan for up to 80 bytes Operation varcha2 (30) The name executed by the internal operation is in the first line generated by a statement. Possible values The DELETE STATEMENT STATEMENT STATEMENT UPDATEMENTOPTIONS VARCHAR2 (30) Variable Object_Node Varchar2 (128) for the Operation column is used to access the name of the database link Database Link's name for the local query using parallel The order Object_OWNER VARCHAR2 (30) capable of describing the operation in the operation is the order of the name Object_name varcha2 (30) table or index appears in the original Original statement in the original Original statement in the original Original statement for the name of the name Object_Name Varchar2 (30) table or index. The corresponding order number given to the original statement text and the processing order is from left to right, and a modifier for providing object descriptive information is used to provide object descriptive information, for example, Non-UniqueOptimizer VARCHAR2 ( 255) The currently optimized program ID Integer assigns the number of the next step of the program for the execution plan of the plan, the next execution step of the ID step, the next execution step, for the steps of the same Parent_ID, the corresponding processing order Cost Integer is based on the optimizer Operating opening values estimated by overhead methods For the use of rule-based methods, this column value does not have a specific measurement unit it is just a weight value for comparing the execution planning overhead size Cardinal INTEGER, based on overhead Method is interviewed by the operation The estimated value of the number of lines bytes Integer is estimated to be estimated to the byte of the operation based on the overhead method.
2.2.1. General use
Routine use syntax:
Explain Plan [set statement_id [=]
SQL> SELECT A.OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, ID, PARENT_ID 2 FROM PLAN_TABLE a 3 WHERE STATEMENT_ID = 'test1' 4 ORDER BY Id; OPERATION OPTIONS OBJECT_NAME OBJECT_TYPEID PARENT_ID ------------- --- --------------------------------------------- ----------- ---------- SELECT STATEMENT 0WINDOW SORT 1 0FILTER 2 1NESTED LOOPS OUTER 3 2NESTED LOOPS OUTER 4 3NESTED LOOPS 5 4TABLE ACCESS FULL TB_SOC_PACKDEALDATA 6 5TABLE ACCESS BY INDEX ROWID TB_SOC_LEVYDETAILDATA 7 5in DEX RANGE SCAN IND_DATAID_LEVSOC NON-UNIQUE 8 7TABLE ACCESS BY INDEX ROWID TB_LVY_TAXDATABILLMAP 9 4INDEX RANGE SCAN TBLVYTAXDATABIL_DATAFROMPOINTE NON-UNIQUE 10 9TABLE ACCESS BY INDEX ROWID TB_LVY_BILLDATA 11 3INDEX UNIQUE SCAN TBLVYBILLDATA_BILLDATAID UNIQUE2.2.2. Automatically displays
Automatically track display execution plan and related information in SQLPLUS SQL> SET TIMING ON - Display execution time SQL> SET AutoRACE ON? C Display execution plan SQL> SETORAACE ON? C Display execution plan SQL> SET Autotrace TRACEONLY? C The execution plan is not displayed in query
After setting, execute the SQL statement will display the execution plan information and the corresponding statistics (requires settings to display this option)
SQL> select nvl (sum (t.taxdue), 0) 2 from tb_lvy_sbzs100 t, tb_lvy_declaredoc a, tb_lvy_declaredoc b 3 where a.dossiercode = 'SB02041108' 4 and a.pages = 123 5 and a.remarkid = b.remarkid 6 And A.Remarkid IS Not Null 7 and B.Declaredocid = T. Declaredocid; NVL (SUM (T.TAXDUE), 0) -------------------- 0 Time: 00: 00: 04.07EXECUTION Plan ---------------------------------------- ------------------ 0 Select Statement Optimizer = Choose (COST = 6 Card = 1 Bytes = 110) 1 0 Sort (aggregate) 2 1 NESTED LOOPS (COST = 6 Card) = 1 bytes = 110) 3 2 Merge Join (COST = 4 card = 1 Bytes = 74) 4 3 Table access (full) of 'tb_lvy_sbzs100' (COST = 2 card = 1 bytes = 31) 5 3 Buffer Sort) (COST = 2 card = 1 Bytes = 43) 6 5 Table Access (Full) of 'TB_LVY_DECLAREDOC' (COST = 2 Card = 1 Bytes = 43) 7 2 Table Access (By Index RowID) of 'TB_L VY_DECLAREDOC '(cost = 2 card = 1 bytes = 36) 8 7 index (unique scan) of' tblvydeclaredoc_declaredocid '
(UNIQUE) statistics ------------------------------------------------------------------------------------------------------------ ------------ 0 Recursive Calls - Cycling Time 0 DB Block Gets - Request data Blocks 6675 CONSITENT GETS - Logic Io is used to read tables and calculate lines Number, data requests in rollback segment buffer 45 Physical Reads? C from disk to buffer cache data block number 0 redo size? C generated Redo log size 217 BYTES SENT VIA SQL * Net to Client 276 Bytes Received Via SQL * Net from Clom Client 2 SQL * NET ROUNDTRIPS TO / FROM Client 1 Sorts (Memory) 0 Sorts (Disk) 1 Rows ProcessedSQL> If the 6675 Consistent Gets - Logic Io is used to read the table and calculate the number of lines, the total number of data requests is rolling Buffer
45 Physical READS? C reads the value of the number of buffer cache data blocks from disk, which is relatively high in the statement of the statement.
2.2.3. PL / SQL and TOAD
If you want to query the statement in PL / SQL to display the execution plan, you only need to enter the SQL statement you want to query, then select the button F5 or in the menu Tools? D> EXPLAIN PLAN menu button can be executed The plan window checks the execution plan of the statement.
In the execution of the Explain Plantab page in the execution of the current SQL window, you can view the execution plan information to perform statements.
2.3. Restriction
Although any SQL statement can be interpreted with EXPLAIN, this tool is not too large for INSERT, UPDATE, DELETE operation without query. INSERT operations with no subquery do not create an execution plan, but there is no WHERE clause or subquery Update and DELETE operations created an execution plan, because these operations must first identify the required records.
Also, if you use other types such as SEQUENCE in the SQL statement, Explain can reveal its usage.
EXPLAIN really unique limit is that users cannot explain tables, views, indexes, or other types of other users, and users must be owners who have interpreted things. If they are not owners, only SELECT permissions, Explain returns an error.
