oracle sql statement execution plan in the analysis method

2011-01-14  来源:本站原创  分类:Database  人气:132 

How to generate explain plan?

Answer: Run utlxplan.sql. The establishment plan table

Specific SQL statement, using the explain plan set statement_id = 'tst1' into plan_table for sql statement

Run explain plan look utlxplp.sql or utlxpls.sql

(Select * from table (dbms_xplan.display ());)

EXPLAIN PLAN is a good tool for analyzing the SQL statement, it can not even execute SQL statements in the case analysis by analysis, we can know is how to connect ORACLE table, in what way to use scanning table (or full index scan table scan) and the use of the name of the index.

You need to follow from the inside out, from top to bottom order of interpretation of results of the analysis. EXPLAIN PLAN results of the analysis is arranged with an indented format, the most internal operations will be the first to read, at the same level if the two operations in operation with a minimum number will be executed first.

NESTED LOOP is a small number of processing operations in accordance with the above rules, the correct execution path is to check the NESTED LOOP provide data on the operation, which operate with the smallest number will be processed first.

Translator's note:

Through practice, feel, or use the SET TRACE function in SQLPLUS more convenient.

For example:

SQL> list

1 SELECT *

2 FROM dept, emp

3 * WHERE emp.deptno = dept.deptno

SQL> set autotrace traceonly / * traceonly can not display the execution result * /

SQL> /

14 rows selected.

Execution Plan

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

0 SELECT STATEMENT Optimizer = CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (FULL) OF 'EMP'

3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

Statistics

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

0 recursive calls

2 db block gets

30 consistent gets

0 physical reads

0 redo size

2598 bytes sent via SQL * Net to client

503 bytes received via SQL * Net from client

2 SQL * Net roundtrips to / from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

Through the above analysis we can draw the actual steps are:

1. TABLE ACCESS (FULL) OF 'EMP'

2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4. NESTED LOOPS (JOINING 1 AND 3)

Note: Many third-party tools such as TOAD and ORACLE tools such as the OMS itself the SQL Analyze provide an extremely convenient EXPLAIN PLAN tool, perhaps like the graphical interface of friends can use them.
-------------------------------------------------- --------------------------

Implementation of a small amount of high and low for the sql our execution plan information can be analyzed basically view the SQL statement execution time. The order and connection information such as a waste of database resources to determine the efficiency of the SQL statement to execute, the following a brief look at the use of execution plan
2. Explain use
Oracle RDBMS implementation of each SQL statement, Oracle optimizer must undergo an assessment. Therefore, to understand how the optimizer option (search) path and the index is to be used, to optimize the SQL statement is very helpful. Explain can be used to quickly and easily identified for a given SQL statement in the query data is how to get the search path (we often referred to as Access Path). So we choose the best way to achieve maximum optimization of the query results.
2.1 installation to use the execution plan you first need to execute the corresponding script.
Explain tools used to create Explain_plan table, it must first enter the relevant application tables, views, and indexes the owner's account. Oracle's media contains the SQL source code to perform this work, for example:
ORA_RDBMS: XPLAINPL.SQL (VMS)
$ ORACLE_HOME / rdbms / admin / utlxplan.sql (UNIX)
The script will generate a table of this program will create a file called plan_table table, table structure is as follows:
We simply explain the meaning of the main fields:
Field name field, type the meaning of
STATEMENT_ID VARCHAR2 (30) explain PLAN statement optimal STATEMENT_ID specified parameter values, if EXPLAN PLAN statement does not use SET STATEMENT_ID, then this value will be set to NULL.
REMARKS VARCHAR2 (80) and was planning to explain the various steps associated with the Notes up to 80 bytes
OPERATION VARCHAR2 (30) the internal operations of the steps performed by a statement in the name of the first row generated by the possible values ​​in the column following DELETE STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENT
OPTIONS VARCHAR2 (30) on the operation described in the OPERATION column variant
OBJECT_NODE VARCHAR2 (128) used to access the object database link name of the database link for the local queries using parallel execution of the operation of the column to describe the order of output
OBJECT_OWNER VARCHAR2 (30) for containing the table or index structure gives its owner the name of the schema
OBJECT_NAME VARCHAR2 (30) the name of the table or index
OBJECT_INSTANCE INTEGER based object appears in the original order of the original statement in the order given by the corresponding number on the original text of the statement in terms of its handling of the order from left to right picture to expand view from the outside in
OBJECT_TYPE VARCHAR2 (30) used to provide descriptive information of the object modifiers such as index NON-UNIQUE
OPTIMIZER VARCHAR2 (255) The current optimizer mode
ID INTEGER assigned to the enforcement of planning the number of steps
PARENT_ID INTEGER output of the ID procedure to operate the next steps of the ID
POSITION INTEGER PARENT_ID steps for the same processing order of the corresponding
COST INTEGER optimization program based on cost-based method to estimate the operating cost of a rule-based method for the empty statement as the column value of the specific unit of measurement it is not just an implementation plan for comparing the size of the weights overhead
CARDINALITY INTEGER according to the method of cost-based access operations estimate of the number of rows
BYTES INTEGER method based on cost-based estimates of bytes accessed by operation

2.2 Use
2.2.1 routine use of conventional syntax:
explain PLAN [SET STATEMENT_ID [=] <string literal>]
[INTO <table>]
FOR <sql>
Of which:
STATEMENT_ID is a unique string, the current execution plan stored in the same PLAN table with the other implementation plans distinguished.
TABLE_NAME is the name of the table plan, its structure as shown previously, you can arbitrarily set the name.
SQL_STATEMENT is a real SQL statement.
Such as:
SQL> explain plan set statement_id = 'test1' for
2 SELECT a.soctermbegin,
3 a.soctermend,
4 a.dealserialno,
5 a.levydataid,
6 a.dealtotal,
7 e.categoryitemcode,
8 row_number () over (PARTITION BY a.levydataid ORDER BY 1) AS theRow
9 FROM tb_soc_packdealdata a,
10 tb_Lvy_TaxDataBillMap c,
11 Tb_lvy_BillData d,
12 tb_soc_levydetaildata e
13 WHERE a.levydataid = c.datafrompointer (+)
14 AND c.billdataid = d.billdataid (+)
15 AND a.levydataid = e.levydataid
16 AND a.packdealstatuscode = '10 '
17 AND (a.datastatus '9 'OR a.datastatus is NULL)
18 AND (d.billstatus IS NULL OR
19 (d.billstatus '2 'AND d.billstatus '8'))
20 AND a.Insurcode = '6010952 '
21;
Explained
Execute the following statement, you can view the execution plan of the statement is executed:
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 0
WINDOW SORT 1 0
FILTER 2 1
NESTED LOOPS OUTER 3 2
NESTED LOOPS OUTER 4 3
NESTED LOOPS 5 4
TABLE ACCESS FULL TB_SOC_PACKDEALDATA 6 5
TABLE ACCESS BY INDEX ROWID TB_SOC_LEVYDETAILDATA 7 5
INDEX RANGE SCAN IND_DATAID_LEVSOC NON-UNIQUE 8 7
TABLE ACCESS BY INDEX ROWID TB_LVY_TAXDATABILLMAP 9 4
INDEX RANGE SCAN TBLVYTAXDATABIL_DATAFROMPOINTE NON-UNIQUE 10 9
TABLE ACCESS BY INDEX ROWID TB_LVY_BILLDATA 11 3
INDEX UNIQUE SCAN TBLVYBILLDATA_BILLDATAID UNIQUE
2.2.2. Automatically using automatic tracking in SQLPLUS display the execution plan and related information
SQL> set timing on - show the execution time
SQL> set autorace on C shows the execution plan
SQL> set autorace on C shows the execution plan
SQL> set autotrace traceonly C only show the query execution plan that is not out of the data set after executing SQL statement execution plan information is displayed and the corresponding statistics (need to set the display options)
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
Elapsed time: 00: 00: 04.07
Execution 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 (CARTESIAN) (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_LVY_DECLAREDOC' (Cost = 2 Card = 1 Bytes = 36)
8 7 INDEX (UNIQUE SCAN) OF 'TBLVYDECLAREDOC_DECLAREDOCID' (UNIQUE)
Statistics
-------------------------------------------------- --------
0 recursive calls - the number of recursive loops
0 db block gets-requested data blocks in the buffer to meet the number of
6675 consistent gets - logical IO is used to read the table and calculate the number of rows, the total number of data requests in the Buffer rollback
45 physical reads C read from the disk block number of Buffer Cache
0 redo size C generated the redo log size
217 bytes sent via SQL * Net to client
276 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
If the 6675 consistent gets - logical IO is used to read the table and calculate the number of rows, the total number of data requests in the Buffer rollback
45 physical reads C Buffer Cache read from the disk block number of relatively small value of the statement on the performance of the database is relatively high.

2.2.3. PL / SQL and TOAD to use if the PL / SQL using select query execution plan display, which only need SQL WINDOWS window, enter the query SQL statement, then select the button or menu TOOLSD F5 D> Explain Plan button on the menu in the window to view the execution plan of the statement execution plan.
TOAD statement in the SQL in the execution of the current window, select the bottom of the page that you can view Explain PlanTAB to execute the statement execution plan information.
2.3 Limitations Although any SQL statement can be used to explain explain, but there is no query INSERT, UPDATE, DELETE operations, this tool is not very useful. No subquery of the INSERT operation will not create the execution plan, but there is no WHERE clause or a subquery, UPDATE and DELETE operations will create an execution plan, because these operations must first identify the desired records.
Also, if you use other types of SQL statements, such as sequence, etc., explain its use can reveal.
explain only real restriction is that users can not go to the interpretation of other users of the table, view, index or other type, the user must be the owner of all to explain things, if not owners but only the select privilege, explain returns an error.

相关文章
  • oracle sql statement execution plan in the analysis method 2011-01-14

    How to generate explain plan? Answer: Run utlxplan.sql. The establishment plan table Specific SQL statement, using the explain plan set statement_id = 'tst1' into plan_table for sql statement Run explain plan look utlxplp.sql or utlxpls.sql (Select *

  • Reposted elsewhere - by analyzing the SQL statement execution plan optimization of SQL (summary) 2010-06-30

    By analyzing the SQL statement execution plan optimization of SQL (summary) DBA did almost 7 years, and sentiment among many. In the DBA's daily work, to adjust individual performance to a less challenging when the SQL statement of work. The key lies

  • By analyzing the SQL statements SQL statement execution plan optimization 2010-09-08

    By analyzing the SQL statements SQL statement execution plan optimization Routine maintenance in the database, adjust the individual performance of the SQL statement is a less challenging job. The key is how to get SQL statement execution plans and h

  • By analyzing the SQL statement SQL statement execution plan optimization 2010-09-08

    By analyzing the SQL statement SQL statement execution plan optimization Routine maintenance on the database, adjust the individual performance of poorly performing SQL statements is a challenging task. The key is how to get SQL statement execution p

  • How to find and solve Oracle SQL statement execution efficiency (reprint) 2010-12-30

    First, identify the more resource intensive method of statement (4 methods) 1. The test group and end-user feedback and slow response-related issues. 2. Using V_ $ SQLAREA view provides the implementation details. (Executive, read the number of disk

  • Oracle SQl statement execution process 2011-03-28

    Speaking of soft parsing (soft prase) and hard parsing (hard prase), it can not be said about Oracle's sql processing. When you issue a sql statement delivered Oracle, and get the result in the implementation, Oracle sql this will be a few steps of t

  • oracle sql statement in the Plan of Implementation of the method 2011-01-14

    How to generate explain plan? Answer: Run utlxplan.sql. Create plan table For a particular SQL statement, using the explain plan set statement_id = 'tst1' into plan_table for sql statement Run explain plan utlxplp.sql or utlxpls.sql View (Select * fr

  • SQL statement execution order and the basic query optimization oracle 2011-07-24

    1.select Column from the table list name / View a list of names where the conditions . 2.select Column from the table list name / View a list of names where the conditions group by ( Column list ) having Condition 3.select Column from the table list

  • SQL statement execution sequence analysis 2011-02-26

    Which I would like to ask an expert analysis of the following types of SQL statement execution order .SQL Prototype statement ; 1.select Table column list from the list of names / View a list of names where the conditions . 2.select Table column list

  • ORACLE SQL statement Categories 2010-03-11

    Oracle SQL statements can be divided into the following categories: Data manipulation language statements [Data manipulation language, DML] From one or more tables or views in the query data (SELECT); for operation [fetch] is scrollable [scrollable]

  • oracle sql statement (a) 2010-12-10

    Note: The database version is 10g, but also applies to most of 9i, 9i flashback no. 1. Who does not care to develop a database table to delete all the library was scared to death. Results found the following statement to restore the data before 1 hou

  • Oracle SQL parsing of hard and soft analysis 2011-08-31

    Original link: http://www.linuxidc.com/Linux/2011-04/34783p2.htm We all know that each SQL statement in Oracle is required prior to implementation through analysis, there is further divided into soft and hard analytical resolution. In Oracle, there a

  • ORACLE SQL statement optimization summarized in 2010-02-23

    ORACLE SQL statement optimization summarized in (1) Select the most efficient sequence table name (only the effective rule-based optimizer): ORACLE parser in accordance with the order processing from right to left in the FROM clause of the table name

  • Oracle SQL statement of the operator Optimization - Optimization Analysis 2010-09-04

    Oracle SQL statement of the operator Optimization - Optimization Analysis Operator optimization IN operator IN written with the advantages of SQL is easier to write and clear easy to understand, is more suitable for modern software development style.

  • sql statement execution order - Introduction 2010-11-13

    A, sql statements execution steps: 1) syntax analysis, syntax analysis of whether the specification statement to measure the expression of the meaning of the statement. 2) semantic analysis, check the statement concerning the existence of all the dat

  • SQL statement execution steps 2011-09-26

    SQL statement execution steps of a SQL statement process to go through the following steps. 1 analysis statement parsing the syntax of the specification. 2 Check the statement in the semantic analysis of all database objects related to the existence,

  • Turn: oracle sql statement optimization 2010-10-25

    Transfer: http://www.blogjava.net/killme2008/archive/2007/03/02/101434.html (1) choose the most efficient sequence table name (only in the rule-based optimizer effectively): ORACLE parser in accordance with the order processing from right to left in

  • oracle sql statement optimization (reproduced) 2011-03-01

    (1) choose the most efficient sequence table name (only in the rule-based optimizer effectively): ORACLE parser in accordance with the order processing from right to left in the FROM clause table name, FROM clause written in the last table (base tabl

  • How to find out the history oracle10 inefficient SQL statement execution 2011-03-01

    Solve oracle10g running slow, can not determine bottlenecks, inefficient sql in order to find a lot of struggling today to find one, Is to look at awr report. In previous Oracle 10g using the Statspack for performance troubleshooting. Oracle Database

  • Improving oracle sql statement to optimize the efficiency (34 ways) 2011-04-07

    Improving oracle sql statement to optimize the efficiency (34 ways) (1) choose the most efficient sequence table name (only in the rule-based optimizer effectively): Oracle's parser in accordance with the order processing from right to left in the FR