Identify inefficient execution plans with Dbms_xplan.display_cursor

Dbms_xplan.display_cursor definition:

function Display_cursor (sql_id varchar2 default NULL,
Cursor_child_no integer default 0,
Format varchar2 default ' typical ')
Return dbms_xplan_type_table
pipelined;

Identify problem SQL We can get it in the following ways:
1. AWR
2. ASH
3. Find the corresponding SQL based on the process of high CPU usage at that time

The problem SQL is found, and then we want to look at the SQL execution plan, but the question is, if you quickly find out which part of the execution plan is out of the question?

If we can get the number of rows actually returned by each step of SQL execution, we can compare the number of rows that are expected to be returned by the execution plan, the data is not very different, the implementation plan can be considered to be OK, and the difference between the two shows that there is a problem with the implementation plan.

Starting from 10g Oracle provides the Display_cursor function of the Dbms_xplan package to show both the number of rows expected to be returned by the execution plan and the number of rows actually returned, so we can use this package to quickly find out which part of the execution plan is out of the question, and then to take the approach.

The Display_cursor function of the Dbms_xplan package is to get the execution plan from the libary cache, so to access the function, you must first grant the permission: Grant Select any dictionary to Scott;

The steps to use the Dbms_xplan.display_cursor function are:

1. Set initialization parameters Statistics_level to all, as follows:
Sql> alter session set statistics_level= ' all ';
Statistics_level control the level at which the database collects statistics, with three values:
Basic: Collect essential statistical information
Typical: Collect most of the statistics (default settings for the database)
All: Collect all statistics

2. Execute the problem SQL, such as:
Sql> Select Ename,sal from emp,dept where Emp.deptno=dept.deptno and dept.loc= ' CHICAGO ';

3. Use the Dbms_xplan.display_cursor package to view the execution plan with the actual return rows:
Set Lines 300
Set Pages 9000
Sql> select * FROM table (Dbms_xplan.display_cursor (null,0, ' allstats last '));

The Dbms_xplan.display_cursor function is defined as:
--Display from V$sql_plan (or V$sql_plan_statistics_all)
function Display_cursor (sql_id varchar2 default NULL,
Cursor_child_no integer default 0,
Format varchar2 default ' typical ')
Return dbms_xplan_type_table
pipelined;

Where the parameter sql_id is the parent cursor, or NULL, indicates the SQL execution plan before the session is displayed. Cursor_child_no is the ordinal of a child cursor, default is 0, and if set to NULL, all execution plans for all child cursors under that parent cursor are returned.
The parameter format specifies what information to display, commonly used: iostats (I/O information display), Allstats (I/O information display +PGA information), Advanced (Show All statistics), iostats last, or allstats Last (displays only the statistics that were executed at the end). The default value typical can only display a normal execution plan and cannot display the actual returned rows.

The output of the 3rd step is:
Plan_table_output
--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------
sql_id CUQ0VS99SCTNM, child number 0
-------------------------------------
Select Ename,sal from emp,dept where Emp.deptno=dept.deptno and
Dept.loc= ' CHICAGO '

Plan Hash value:844388907

--------------------------------------------------------------------------------------------------------------- --------------
| Id | Operation | Name | Starts | E-rows |   A-rows | A-time |  Buffers |  Omem | 1Mem | Used-mem |
--------------------------------------------------------------------------------------------------------------- --------------
| 0 |         SELECT STATEMENT |      |        1 |      |      6 |00:00:00.01 |       11 |       |          | |
|  1 |         MERGE JOIN |      |      1 |      4 |      6 |00:00:00.01 |       11 |       |          | |
|* 2 | TABLE ACCESS by INDEX rowid|      DEPT |      1 |      1 |       1 |00:00:00.01 |       4 |       |          | |
|    3 | INDEX Full SCAN |      pk_dept |      1 |      4 |       4 |00:00:00.01 |       2 |       |          | |
|* 4 |         SORT JOIN |      |     1 |      14 |       6 |00:00:00.01 |  7 |  2048 | 2048 | 2048 (0) |
|    5 | TABLE ACCESS Full |      EMP |     1 |     14 |       14 |00:00:00.01 |       7 |       |          | |
--------------------------------------------------------------------------------------------------------------- --------------

predicate information (identified by Operation ID):
---------------------------------------------------

2-filter ("DEPT". LOC "= ' CHICAGO ')
4-access ("EMP"). DEPTNO "=" DEPT "." DEPTNO ")
Filter ("EMP". " DEPTNO "=" DEPT "." DEPTNO ")

Rows selected.

Interpret the above output:
The number of times starts is executed for this SQL.
E-rows The estimated number of rows for the execution plan.
A-rows is the number of rows actually returned. A-rows compare with E-rows, you can determine which step the implementation plan is out of the question.
A-time is the actual execution time for each step (HH:MM:SS.FF), according to which you know where the SQL takes time.
Buffers the logical read or consistent read that is actually performed for each step.
Reads for physical reading.
Omem, 1Mem is the required memory evaluation value for execution, 0Mem is the evaluation value of the memory required for the optimal execution mode, and 1Mem is the evaluation value of the memory required for the One-pass mode.
0/1/m the number of times the optimal/one-pass/multipass is executed.

To find an inefficient execution plan:
1. Compare A-rows/starts with E-rows, if the difference between the two values, then the line is inefficient execution plan.
2. View the ratio of buffers/a-rows, that is, how much logical reads are consumed on an average line
Buffers/a-rows<5 means good access path
Buffers/a-rows between and 15, indicating that the access path can be accepted
Buffers/a-rows>15or20, which indicates a bad path, is an inefficient execution plan that can optimize

Another step to using the Dbms_xplan.display_cursor function is:
1. Add a hint to the problem sql: Gather_plan_statistics
Sql> Select/*+ gather_plan_statistics */ename,sal from emp,dept where Emp.deptno=dept.deptno and dept.loc= ' CHICAGO ';

2. Use the Dbms_xplan.display_cursor package to view the execution plan with the actual return rows:
Set Lines 300
Set Pages 9000
Sql> select * FROM table (Dbms_xplan.display_cursor (null,0, ' allstats last '));

Depending on the Dbms_xplan.display_cursor function, it's easy to find out where the execution plan went wrong, and then we'll analyze the reason the CBO made the wrong execution plan.
If the problem is the same as the index, we can determine whether the statistics are faulty according to the following SQL:

0. Check if the index fragment is causing the
EXEC p_show_space (' Index name ', ' indexed by Owner ', ' Index ')

1. View the statistics for this index:
Select Index_name,num_rows,distinct_keys,num_rows/distinct_keys as avg_rows_per_key,last_analyzed from user_indexes  Where index_name= ' index name '; As follows:
Sql> Select Index_name,num_rows,distinct_keys,num_rows/distinct_keys avg_rows_per_key,last_analyzed from User_ indexes where index_name= ' pk_dept ';

2. View the actual data
Select COUNT (*) Num_rows,count (Nullif (col1, column value)) Distinct_keys, COUNT (distinct column name) Avg_rows_per_key from table name; As follows:
Sql> Select COUNT (*) Num_rows,count (nullif (Loc, ' CHICAGO ')) Distinct_keys,count (distinct ' CHICAGO ') avg_rows_per_ Key from Dept;

If the previous two steps in the first two columns of data disparity, the statistical information is inaccurate, you need to re-collect statistics, if the first two columns of data is not small, the third column of data is number, and the difference is very large, it is possible to index the column of the histogram problem.

3. See if the index column does histogram statistics
Alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';
Select column_name,histogram,num_buckets,last_analyzed from User_tab_cols where table_name= ' EMP ' and column_name in (' EMPNO ', ' ename ');
column_name Histogram num_buckets last_analyzed
------------------------------ --------------- ----------- -------------------
EMPNO NONE 1 2012-10-23 22:48:28
ename HEIGHT BALANCED 16 2012-10-23 22:48:28
Histogram column None indicates that the histogram is not collected.

Histogram collection:
Method_opt parameter value: Size 1 does not collect, size 2~255 will be collected, size auto automatically judge, size skewonly as long as data skew is collected. The default value for the method_opt parameter is for all columns size auto,oracle determines the sample rate for itself. It is found that this default value sometimes collects a limited number of histograms and, if necessary, collects histograms on all indexed columns (it is not recommended to collect histograms for all columns because this causes the table sys.histgrm$ that stores the histogram information to be too large)
sql> exec dbms_stats.gather_table_stats (user, ' DEPT ', method_opt = ' For all indexed columns ', cascade=>true)

The following is a personal forced supplement:
If the problem is a table, we can tell if the statistics are out of order according to the following SQL:
1. View statistics and fragmentation rates for this table:
Sql> Select Num_rows from user_tables where table_name= ' EMP ';
Select num_rows,avg_row_len*num_rows/1024/blocks*8*100 fragment rate from user_tables where table_name= ' T '; -this method of calculating the fragmentation rate only applies to tables created by default parameter storage (initial 64K)
or with EXEC p_show_space (' Table name ', ' table owner ', ' Tables ')

2. View the actual data
Sql> Select COUNT (*) num_rows from EMP;
Collect table statistics: EXEC dbms_stats.gather_table_stats (user, ' table name ', cascade=>true)--cascade=>true means collecting indexes at the same time

http://pandarabbit.blog.163.com/blog/static/209284144201292910217427/

http://blog.csdn.net/dbanote/article/details/24516037