Oracle how to analyze the execution plan (reproduced)

2011-03-31  来源:本站原创  分类:Database  人气:88 

Example 1:
Suppose LARGE_TABLE is a large table, and there is no index on the username column, then run the following statement:
SQL> SELECT * FROM LARGE_TABLE where USERNAME = 'TEST';
Query Plan
-----------------------------------------
SELECT STATEMENT Optimizer = CHOOSE (Cost = 1234 Card = 1 Bytes = 14)
TABLE ACCESS FULL LARGE_TABLE [: Q65001] [ANALYZED]

In this example, TABLE ACCESS FULL LARGE_TABLE is the first operation, meaning that LARGE_TABLE to do full table scan on the table. When the operation is complete, the resulting row source of data is sent to the next steps for processing, in this case, SELECT STATEMENT operation is the final step of this query.

ptimizer = CHOOSE specify the query optimizer_mode, that optimizer_mode initialization parameter specifies the value, it really does not mean that the statement is executed using the optimizer. Decide what the optimizer to use the statement only way is to look behind the cost part. For example, if given the following form, this indicates that the CBO optimizer, cost, said here that the optimizer execution plan cost:
SELECT STATEMENT Optimizer = CHOOSE (Cost = 1234 Card = 1 Bytes = 14)

However, if given the execution plan is similar to the following information, then the optimizer to use RBO, as part of the cost value is empty, or are they not part of the cost.
SELECT STATEMENT Optimizer = CHOOSE Cost =
SELECT STATEMENT Optimizer = CHOOSE
So we back information from the Optimizer can be drawn with the implementation of the statement in the end, what kind of optimizer. In particular, if the Optimizer = ALL_ROWS | FIRST_ROWS | FIRST_ROWS_n, using the CBO optimizer; if the Optimizer = RULE, then the optimizer to use the RBO.

cost value of the property is an oracle internal implementation plan to compare the cost of each cost value, so that the optimizer can choose the best execution plan. The cost value of the different statements can not be compared only with a statement on the implementation plan of the different cost values.

[: Q65001] indicates that the part of the query is run in parallel. The data indicates that the operation which is a parallel query slave process handle, so that the operation can be different from the serial execution of the operation.

[ANALYZED] that operate in the referenced object is analyzed, the data dictionary of the object of statistical information for the CBO to use.

Example 2:
Assume that A, B, C are not small table, and in Table A on a composite index: A (a.col1, a.col2), as a index to guide attention a.col1 column.
Consider the following query:
select A.col4
from A, B, C
where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT ptimizer = CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'

Statistics
-------------------------------------------------- --------
0 recursive calls
8 db block gets
6 consistent gets
0 physical reads
0 redo size
551 bytes sent via SQL * Net to client
430 bytes received via SQL * Net from client
2 SQL * Net roundtrips to / from client
2 sorts (memory)
0 sorts (disk)
6 rows processed

Make connections in the table, only two tables do first connection, then connect the result as a row source, make connections with the rest of the table, in the above example, the connection sequence for the B and A, the first connection, and then and then connect to C:
B <---> A <---> C
col3 = 10 col3 = 5

If there is no implementation plan, analysis of the above three tables which should take a drive as the first table? It seems from the SQL statement, Table B and C only have restrictions on the table, so the first table should be a driver for this two table one, in the end is which one?

Table B are predicates B.col3 = 10, Table B to do so in the full table scan when the restrictions will be in the where clause condition (B.col3 = 10) to spend, resulting in a smaller row source, so Table B should be a driver as the first table. And so, if we do associate with the Form A, Form A can effectively use the index (Table A col1 because as a leading column).

Of course, the above query on the table there predicate C (C.col3 = 5), one might think that the C drive as the first table the table can get better performance. Let us analyze this: If a driver C table as the first table, the table can guarantee the driver generates a small row source, but look at the join condition A.col2 = C.col2, then there is no opportunity to use the index of Table A , because A table col2 column is not leading column, so that nested loop efficiency is poor, resulting in poor efficiency of the query. So for the NL select the correct drive connection table is very important.

So good to connect the above order of the query (B - -> A) - -> C. If the database is based on the cost optimizer, it will use to calculate the price to determine the appropriate form with the appropriate drive connection sequence. In general, CBO will choose the correct connection sequence, if the CBO choose the relatively poor connection to the order, we can also use the ORACLE CBO provided hints to make the connection using the correct order. As follows:

select / * + ordered * / A.col4
from B, A, C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5

Now select the correct driver table is so important, so let's look at the execution plan, in the end how the various tables related to implementation of the plan which should drive the table table:
In the execution plan, you need to know which operation is first performed, after which the operation is performed, which determine which table to drive the table useful. Before a determination, if access to the table by rowid, and the value of the rowid from the index scan was more, then scan the index to start the implementation of the plan temporarily removed. Then the rest of the implementation plan, the execution order to determine the guiding principle is: the most right, most operations on the first execution. Specific explanation is as follows:
Prevent removal of the index to be scanned to determine the execution plan:
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT ptimizer = CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
Implementation plan to see the first three, that is part of the letter, each column value with spaces as indentation characters left. In the left column space, the more value, indicating that the indentation more column values, the value of the more right-hand column. Implementation plan as shown above: the first column is the line of indentation up to 6, that is the most right-hand line; the first column is the same as the indentation of lines 4 and 5, the right-hand the extent of the same, but The first column is 4 rows than the first column of the row by the value of 5; talk about up and down relationship, only a continuous, consistent line indent effective.

From this figure we can see that, for the NESTED LOOPS part, the most right, most of operations are TABLE ACCESS (FULL) OF 'B', so the first implementation of this operation, so the operation corresponding to Table B for the first drive table (external form), natural, A table on the internal table. Can also be seen from the figure, B and A table to do nested loops epigenetic become a new row source, to the row source for sorting, and C correspond to the sort of table of row source (applied C.col3 = 5 constraints) to MSJ connection operation. Therefore, the fact can be drawn from the above: B Table A table with the first to do nested loops, then the resulting row source to do with the C table sort - merge join.

By analyzing the implementation of the above program, we can not say that the table must be in C B, A table before being read, in fact, B and C table table could also be read into memory, because the data in the table read into memory operation may be parallel. In fact, many operations may be interleaved, because the ORACLE read data if the data is the need for a line is the line where the entire data block is read into memory, and there may be multi-block read.
To see the execution plan, we look at what the key is not the first execution operation, which operation is executed, but the key to watch the connection between the order (such as that which is driven table, which requires the operation of the order from the judge), the use of what type of association and specific access path (such as to determine whether use of the index)

Implementation plan from the table to determine which driver the table, based on our knowledge to determine the table as the driving table (as the above table to determine the ABC did) is appropriate, if not appropriate, change the SQL statement, the optimizer can Select the right driver table.

For RBO Optimizer:
In the ORACLE documentation, said: For the RBO, the with a from clause in the order from right to left select the drive table, the far right of the table as the first drive table, which is the original English text: All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT. However, I do the test, never verified this claim is correct. In my opinion, even in the RBO, is also a set of rules to decide which type of connection and which table as the driving table in the selection index will take into account the current situation may also consider where the limits, but is certainly and where the constraints of the position-independent.

Test:
If I create three tables:
create table A (col1 number (4,0), col2 number (4,0), col4 char (30));
create table B (col1 number (4,0), col3 number (4,0), name_b char (30));
create table C (col2 number (4,0), col3 number (4,0), name_c char (30));
create index inx_col12A on a (col1, col2);
Execute the query:
select A.col4
from B, A, C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT ptimizer = RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'

select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT ptimizer = RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'

A table will be deleted on the index inx_col12A:
select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT ptimizer = RULE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'C'
6 3 SORT (JOIN)
7 6 TABLE ACCESS (FULL) OF 'A'
8 1 SORT (JOIN)
9 8 TABLE ACCESS (FULL) OF 'B'

Through these examples above, so I have oracle on the document "All things being equal RBO chooses the driving order by taking the tables in the FROM clause RIGHT to LEFT" This sentence skeptical. At this point, I can not use hints to force the optimizer to use a nested loop, if the hints, so you automatically use the CBO optimizer, the optimizer instead of RBO.

For the CBO optimizer:
CBO select the drive according to the statistics table, if there is no statistical information is in the from clause in the order from left to right select the drive table. This is the opposite order of selection RBO. This is the original English text (CBO determines join order from costs derived from gathered statistics. If there are no stats then CBO chooses the driving order of tables from LEFT to RIGHT in the FROM clause. This is OPPOSITE to the RBO). I still can not confirm the correctness of this statement. However, after verification: "If ordered hint (in this case certainly with the CBO), places in the from clause from left to right choose the driving table" this sentence is correct. In fact, CBO, if the statistical data (ie tables and indexes were analyzed), the optimizer cost value will automatically decide which type of connection, and select the appropriate drive table, which is where each clause constraints of the location is not in any way. If we want to change the optimizer choose the type of connection or the driver table, you need to use hints, and specifically the use of hints in the introduction will be given later.

Test:
If I create three tables:
create table A (col1 number (4,0), col2 number (4,0), col4 char (30));
create table B (col1 number (4,0), col3 number (4,0), name_b char (30));
create table C (col2 number (4,0), col3 number (4,0), name_c char (30));
create index inx_col12A on a (col1, col2);

Execute the query:
select A.col4
from B, A, C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT ptimizer = ALL_ROWS (Cost = 3 Card = 1 Bytes = 110)
1 0 NESTED LOOPS (Cost = 3 Card = 1 Bytes = 110)
2 1 MERGE JOIN (CARTESIAN) (Cost = 2 Card = 1 Bytes = 52)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 1 Bytes = 26)
4 2 SORT (JOIN) (Cost = 1 Card = 1 Bytes = 26)
5 4 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 1 Bytes = 26)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)

select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT ptimizer = ALL_ROWS (Cost = 5 Card = 55 Bytes = 4620)
1 0 HASH JOIN (Cost = 5 Card = 55 Bytes = 4620)
2 1 HASH JOIN (Cost = 3 Card = 67 Bytes = 4757)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 82 Bytes = 1066)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)
5 1 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 82 Bytes = 1066)

A table will be deleted on the index inx_col12A:
select A.col4
from B, A, C
where A.col1 = B.col1
and A.col2 = C.col2;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT ptimizer = ALL_ROWS (Cost = 5 Card = 55 Bytes = 4620)
1 0 HASH JOIN (Cost = 5 Card = 55 Bytes = 4620)
2 1 HASH JOIN (Cost = 3 Card = 67 Bytes = 4757)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 82 Bytes = 1066)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)
5 1 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 82 Bytes = 1066)

select / * + ORDERED * / A.col4
from C, A, B
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5;
Execution Plan
-------------------------------------------------- --------
0 SELECT STATEMENT ptimizer = ALL_ROWS (Cost = 3 Card = 1 Bytes = 110)
1 0 NESTED LOOPS (Cost = 3 Card = 1 Bytes = 110)
2 1 NESTED LOOPS (Cost = 2 Card = 1 Bytes = 84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost = 1 Card = 1 Bytes = 26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost = 1 Card = 82 Bytes = 4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost = 1 Card = 1 Bytes = 26)
This query can be verified through the correct prompt prompt ORDERED optimizer to select which table as the optimizer.

相关文章
  • Oracle how to analyze the execution plan (reproduced) 2011-03-31

    Example 1: Suppose LARGE_TABLE is a large table, and there is no index on the username column, then run the following statement: SQL> SELECT * FROM LARGE_TABLE where USERNAME = 'TEST'; Query Plan ----------------------------------------- SELECT STATE

  • oracle - how to analyze the execution plan 2011-08-15

    Example 1: Suppose LARGE_TABLE is a large table, and there is no index on the username column, then run the following statement: SQL> SELECT * FROM LARGE_TABLE where USERNAME = 'TEST'; Query Plan ----------------------------------------- SELECT STATE

  • 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

  • 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 *

  • Oracle: hint to manually change the sql execution plan 2011-07-06

    Tips (hint) from Oracle7 introduced, aimed at cost-based optimizer to make up for deficiencies. Tips are usually used to manually change the SQL execution plan to improve the efficiency of enforcement. hints is the oracle to provide a mechanism to te

  • oracle execution plan 3 2010-03-02

    Environment: oracle 817 + linux + array cabinet swd_billdetail Table 50 million data SUPER_USER Table 2800 data Connect columns are indexed, and super_user in a table corresponds to swd_billdetail many records Table and Index are also analyzed. Pract

  • oracle execution plan 4 2010-03-02

    The statement by adding hints, so that the optimizer oracle nested loop, and a large table as the driving table, generate a new execution plan: select / * + ORDERED USE_NL (A) * / count (a.CHANNEL | | B.user_class) from swd_billdetail B, SUPER_USER A

  • oracle view the SQL execution plan parameters analysis 2010-04-14

    sql_trace been tracking the implementation plan: Call Count CPU Time Elapsed Time Disk Query Current Rows ------- ------ -------- ------------ ---------- ------- --- ---------- ---------- Parse 1 0.000 0.000 0 0 0 0 Execute 1 0.000 0.000 0 0 0 0 Fetc

  • Oracle execution plan from the start view 2010-08-29

    Consider the following situation (1) If your data table to manage 40 million video information; (2) If you manage 10 million kinds of product data sheets; A simple paging query, you truly feel its efficiency over it? If users complain about slow resp

  • Oracle execution plan in order to see how the 2011-01-13

    Oracle execution plan ways to read: Look to the right start at the very beginning has been, until you see the place where the far right side by side, for non-parallel, right, the first run: For side by side, relying on the first run. Namely, in the i

  • (Installed) oracle execution plan 2011-07-20

    One. Related Concepts Rowid concept Recursive Sql concept Predicate (verb) DRiving Table (table-driven) Probed Table (by probe table) Combination index (concatenated index) Selectivity (selectivity) II. oracle access to the data access methods 1) Ful

  • Oracle execution plan from the beginning to view 2010-08-29

    Consider the following scenario, (1) If your data tables to manage 30 million line of card user information; (2) If you manage 10 million kinds of product data sheets; A simple paging query, you truly felt it efficient? If users complain about slow r

  • Oracle execution plan to see how the order is 2011-01-13

    Reading oracle execution plan approach: Start at the very beginning has been to right, side by side until you see the most right place for non-parallel, right, the first execution: For side by side, relying on the first run. That is parallel to inden

  • oracle execution plan cost, card meaning 2011-03-19

    This card is a step in the planned number of rows processed. cost refers to the cbo in the resources spent on this step, this value is a relative value. This step refers to the cbo bytes to process all records of the number of bytes, is estimated fro

  • How do I view the execution plan oracle sql 2011-04-09

    Method One The first step: login sql / plus the command (no order) set time on; (Note: Open time display) set autotrace on; (Note: Turn on Automatic statistical analysis, and display the results of an SQL statement) set autotrace traceonly; (Note: Op

  • Oracle using sql plus view the execution plan (reprint) 2011-04-26

    In sql * plus, enter set autotrace on, and then directly execute sql statements; will automatically display the execution plan and statistics. Disadvantages: This method is used to view the sql statement execution time is longer, you need to wait for

  • How to view the execution plan oracle 2011-07-07

    See individual SQL method I. using explain plan If PLAN_TABLE table does not exist, execute $ ORACLE_HOME / rdbms / admin / utlxplan.sql create plan_table table. The first step: explain plan for select * from ...... Step two: select * from table (DBM

  • Use Oracle Hint hint to change the execution plan 2011-09-14

    Since each version of the optimizer becomes more complete, Oracle SQL execution to change your plans to provide a method of growing. Oracle Tip of the most common use is as a debugging tool. You can use the prompt to determine the optimal execution p

  • SQL query execution plan oracle 2011-08-03

    Explain SQL SQL> explain plan for select * from dual; View the execution plan select * from table(DBMS_XPLAN.display);

  • 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