Display and use the most comprehensive display_cursor execution plan, cursor

Display and use the most comprehensive display_cursor execution plan, cursor
1. explain and view the SQL text execution plan explain plan for select 3 + 5 from dual; select * from table (dbms_xplan.display ());
Select * from table (dbms_xplan.display (table_name => 'Plan _ table', statement_id => null, format => 'all'); explain an SQL statement using explain PLAN, related information will be put into a global temporary table named PLAN_TABLE by default. You can use this to view the details. Parameter: table_name. The default value is 'Plan _ Table'. If another TABLE has the same structure as PLAN_TABLE, you can also read the information in it. Statement_id is null by default, which is the last explain statement of the session. The default format is 'category', Which is 'basic ', 'category', 'all'. PROJECTION, ALIAS and information about remote SQL if the operation is distributed is displayed when ALL is set. In fact, in addition to specifying the three levels, you can also use the following remarks (minus signs represent removing the relevant information) 'All-PROJECTION-note' # ALL level, but do not project and NOTE information 'basic ROWS '-- the BASE does not have any ROWS information. We can also add it. Other options, such as outline
Filter_perds defaults to null. filter_preds => 'Plan _ id = 32' can use the field in plan_table to limit the output information, just like the where condition. Each connected user can use plan_table without special permissions or reading views such as v $ plan. It does not actually execute the SQL statement or generate the SQL cursor in the shared pool. Instead, it generates a cursor that only includes the word "explain plan for", but does not have an independent SQL cursor.

2. view the execution plan of the cursor in a shared pool select * from table (dbms_xplan.display_cursor (); -- display the execution plan of the last SQL statement of the session, which is only suitable for sqlplus. Select * from table (dbms_xplan.display_cursor ('fb8szhn9h5r95 '); select * from table (dbms_xplan.display_cursor ('fb8szhn9h5r95', 0, 'typical '));
# The first method is to display the execution plan of the last SQL statement of the session, which is not suitable in plsql developer. This is probably because after plsql developer executes a select statement, A new transaction is started by default, and the begin: id: = sys. dbms_transaction.local_transaction_id; end;, so this is the last statement in plsql developer. # Read cursor from the shared pool and display the execution plan in combination with views such as V $ SQL _PLAN_STATISTICS_ALL, V $ SQL, and V $ SQL _PLAN. # If you do not have the permission, SELECT privilege on V $ SQL _PLAN_STATISTICS_ALL, V $ SQL, and V $ SQL _PLAN. # Format. SQL _id and child_no uniquely identify a cursor. SQL _id specifies the SQL parent cursor in the library cache execution plan. If it is not specified, the SQL _id of the last SQL statement executed by the session is returned. Child_number is 0 by default. If it is null, the execution plan of all child game targets under the parent cursor indicated by SQL _id is returned. Format controls the output part of the SQL statement execution plan, that is, which can be displayed or not displayed. (TYPICAL by default), you can refer to display (), which is also basic, typical, all.
3. for SQL statements with detailed real-time execution information, when statistics_level = all is enabled or the gather_plan_statistics prompt is used, another table is used to record the execution plan and information at runtime in detail. Alter session set statistics_level = all; -- you can also alter systemselect/* + gather_plan_statistics * // * fwy1806 */* from t1 where rownum <30; view the execution plan in detail
Display_cursor has more things than display (). In the detailed collection mode, you can add the following options. IOSTATS: displays the accumulated IO statistics of the SQL statement. The last statement is displayed after the last statement is added. MEMSTATS: This option can be used only when automatic PGA memory management is enabled, that is, pga_aggregate_target is not 0. It shows how many memory and how many bytes are exchanged to the disk, generally, hash-join, sort, group by, and other operations that require memory are used for collection. ALLSTATS: a synonym for 'iostats memstats. LAST: the accumulated statistics of the cursor are displayed by default. The LAST statistics is displayed only when the LAST statistics is added. RUNSTATS_TOT -- for backward compatibility, it is equivalent to IOSTATSRUNSTATS_LAST -- for backward compatibility, it is equivalent to IOSTATS LAST
View the execution plan of the last statistics of the cursor
Select * from table (dbms_xplan.display_cursor ('fb8szhn9h5r95 ', null, 'allstats last'); lists the cumulative execution information of the cursor. For example, after the cursor is executed twice, starts, a-Rows and buffers are double
Select * from table (dbms_xplan.display_cursor ('fb8szhn9h5r95 ', null, 'allstats'); the most detailed list includes all in common mode and allstats in detail mode, the outline information that is not displayed by default is also displayed.
Select * from table (dbms_xplan.display_cursor ('fb8szhn9h5r95 ', null, 'all allstats last outline'); select * from table (dbms_xplan.display_cursor ('fb8szhn9h5r95', 2, 'All allstats last outline'); -- add child_no route | Id | Operation | Name | Starts | E-Rows | E-Bytes | Cost (% CPU) | E-Time | A-Rows | A-Time | Buffers | Reads | percent | 0 | select statement | 1 | 3 (100) | 25 | 00:00:00. 01 | 3 | 2 | * 1 | count stopkey | 1 | 25 | 00:00:00. 01 | 3 | 2 | 2 | table access full | T1 | 1 | 25 | 54400 | 3 (0) | 00:00:01 | 25 | 00:00:00. 01 | 3 | 2 | average ------------------------------------------------------------------------------------------------------------------------------
Starts is the number of times the SQL statement is executed. E-Rows is the expected number of Rows in the execution plan. A-Rows indicates the number of Rows actually returned. When comparing A-Rows with E-Rows, you can determine which step of the execution plan has A problem. A-Time indicates the actual execution Time of each step (HH: MM: SS. FF). You can know the Time consumed by the SQL statement based on this line. Buffers performs logical read or consistent read for each step. Reads is a physical read. OMem and 1Mem are the memory evaluation values required for execution, 0Mem is the evaluation value for the memory required for the optimal execution mode, and 1Mem is the evaluation value for the memory required for the one-pass mode. 0/1/M is the optimal number of times/one-pass/multipass is executed.

4. display_awr displays the execution plan SELECT * FROM table (explain ('09tr40mjc8vg5 ') in the awr database; SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR ('3hxb21q9h4t40', 1367077082, null, 'all '));
SQL _id -- enter SQL _id stored in AWR. You can first check dba_hist_ SQL _plan and dba_hist_sqltext to see what SQL _id a statement belongs. Plan_hash_value -- if it is null, all execution plans of the SQL _id will be output. Default nulldb_id -- if ignored, the default value is the current databaseformat -- refer to display (), or 'basic ', 'typical', 'all'. The default value is typical.
1. Make sure that AWR is running.
2. Permission: select on DBA_HIST_ SQL _PLAN, DBA_HIST_SQLTEXT, and V $ DATABASE. 3. Check SQL _id. Check SQL _id Based on the SQL text. You can check it in dba_hist_sqltext. 4. Source: Information of the displayed execution plan, from dba_hist_ SQL _plan. 5. Details: whether allstats can be used to view more detailed execution plans depends on the detailed runtime statistics of your mobile phone.
5. display_sqlset displays the SQL Execution Plan stored in the SQL turning set. Sqlset_name specified the name of the SQL tuning set. SQL _id input SQL _id stored in SQL tuning set, which can be viewed from DBA_SQLSET_PLANS. If plan_hash_value is null, all execution plans of the SQL _id will be output. The default value is null. For details about format, refer to display (), which is also 'basic ', 'typical', 'all ', default Value: 'category' sqlset _ owner Specifies the owner of the SQL tuning set default value: SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET ('fwy _ sqlset', '3hxb21q9h4t40', 1367077082, 'all', 'fwy '));
6. display_ SQL _plan_baseline: displays the execution plan of baseline stored in SPM. * FROM table (DBMS_XPLAN.DISPLAY_ SQL _plan_baseline ('SQL _ a074c4f7bacd50da', 'SQL _ scheduled ', 'all'). You can see ALL the execution plans in SQL _handle SELECT * FROM table (DBMS_XPLAN.DISPLAY_ SQL _plan_baseline (SQL _handle => 'SQL _ 351fadd1a0ec16be ')); SELECT * FROM table (DBMS_XPLAN.DISPLAY_ SQL _plan_baseline (plan_name => 'SQL _ PLAN_a0x64yyxcun6u06957ae0', 'all '));
Format: SQL _handle in SQL _handle SPM is equivalent to SQL _id in v $ SQL. By default, Nullplan_name SPM uniquely identifies an execution plan, just like plan_hash_value in v $ SQL. The default value is null. If it is null, the above SQL _handle must be specified. Format: Refer to display (). The default value is typical. Execution Plan Source: DBA_ SQL _PLAN_BASELINES