The most authoritative Oracle acquisition SQL execution Plan Daquan

This document is based on the relevant data collated, summarized, mainly on the Oracle database, to obtain the most authoritative and correct method of SQL statement execution plan, and also explained in detail the meaning of the options available in each method and how to use, so as to facilitate everyone and their daily work to consult the use of Because I did not find this blog support attachment upload function, need to PDF file format friends can to me, also can be downloaded to the group, reproduced please indicate the source .

1, Query V$sql_plan:

sql> col "Query plan_table" format a100

Sql> Select Id,lpad (", (LEVEL-1)) | | operation| | ' | | options| | ' ' | | object_name| | ' ' | | Decode (ID, 0, ' cost= ' | | Cost) "Queryplan_table"

Fromv$sql_plan

Startwith id = 0

andsql_id = ' &&sql_id '

Andplan_hash_value = &&plan_hash

Connect Byprior id = parent_id

andsql_id = ' &&sql_id '

Andplan_hash_value = &&plan_hash;

--Note: sql_id can be found in V$sqltext and Dba_hist_sqltext, and can be obtained by other means.

2, through the package Dbms_xplan

1) DISPLAY

Sql>explain plan for SELECT * from T_users whereuser_id= ' TEST ';

Sql>select * FROM table (Dbms_xplan.display ());

Sql>select * fromtable (Dbms_xplan.display (null,null, ' BASIC ROWS BYTES '));

--Note:

1.table_name: Stores the table name of the query plan, and the default value is plan_table.

The 2.statement_id:sql statement ID. The Explainplan command can be executed by Setstatement_

The ID clause to specify. If NULL, the last interpreted statement is taken.

3.FORMAT: Output format. In the display function, the following predefined formats are selected:

1) ' Basic ': basic format. The output is minimal, outputting only the ID, name, and options of each operation in the query plan, and the name of the action object.

2) ' typical ': typical format. In addition to the content in the basic format, the number of record rows per operation is also output,

Number of bytes, cost and time, and related hints (such as remote SQL, optimizer recommendations, etc.). Such as

The predicate is present, and the filter conditions and access conditions in each operation are also output. In addition, if the partition table is involved, the output partition clipping information, such as the parallel query, also output parallel operation information (such as table queue information, parallel query distribution mode, etc.). to the default format.

3) ' SERIAL ': Serial execution format. This format is basically consistent with the output of a typical format, except that it does not output related parallel content for parallel queries.

4) ' All ': Full format. The content of the output is relatively complete. In addition to the typical format content, field projection information and alias information are also output.

In addition, users can refine the output by adding or masking some keywords in the formatted string.

For example: "Basicrows", "typical-predicate", etc.

1) Rows: The number of recorded rows estimated by the optimizer;

2) BYTES: The number of bytes estimated by the optimizer;

3) Cost: The price the optimizer estimates;

4) PARTITION: partition clipping;

5) PARALLEL: Parallel query;

6) predicate: predicate;

7) PROJECTION: field projection;

8) Alias: alias;

9) REMOTE: Distributed query information;

Note: The relevant note information.

2) Display_cursor

Sql>select/*+gather_plan_statistics*/* fromt_users where user_id= ' TEST ';

Sql>select * fromtable (Dbms_xplan.display_cursor (Null,null, ' BASIC last Allstats '));

--Note:

The 1.display_cursor function can display the execution plan of one or more cursors in memory;

2. Users must have permission to view V$sqlv, $SQL _plan, and V$sql_plan_statistics_all Select;

3. Parameters:

1) sql_id: If you do not specify SQL_ID (specify null), the SQL statement executed by the last execution of the current session is displayed by default.

2) Cursor_child_no: The child cursor ordinal of the statement, if not specified, displays the execution plan for all child cursors of the statement.

3) Format: Formats the control string. All options for the format control string of the DISPLAY function apply to the Display_cursor function. If you run the statement with Gather_plan_statistics or set the system parameter Statistics_level to collect the performance statistics for the "All" collection statement, there are additional options in the refinement options to choose whether to output the data.

4) Iostats: Outputs the planned input/output (IO) statistics;

5) Memstats: If the PGA Auto-management is enabled, outputs the planned input memory statistics (memory usage, memory read frequency, etc.);

6) Allstats: Contains all the contents of iostats and memstats;

7) Last: The above three options output the sum of the data generated by all executions of the cursor. You can also add the last option to limit the statistics that are displayed only for the final run.

In addition, there are options available for the output control of the function:

8) ' Advanced ': Premium format. In addition to outputting all the content in the full format, the advanced format also outputs the binding variable peek Information and plan summary information, as appropriate;

9) OUTLINE: Whether to display the plan summary in a prompt manner;

Peeked_binds: Whether to display the binding variable peep information;

Buffstats: Whether to display memory reads (including consistent reads and current reads), this information is part of the iostats;

Plan_hash: Whether to display the planned hash value, the same option applies to the Display function.

3) Display_awr

Sql>select sql_id, To_char (substr (sql_text,0,2000))

Fromdba_hist_sqltext

Whereupper (Sql_text) like ' SELECT * from test% ';

Sql>select * fromtable (Dbms_xplan.display_awr (' &sql_id '));

--Note:

The 1.display_awr function displays the execution plan stored in the AWR historical data.

2. You must have SELECT permissions for the following views: Dba_hist_sql_plan and Dba_hist_sqltext.

3. Parameters:

1) sql_id: can be from Dba_hist_sql_plan. sql_id or Dba_hist_sqltext. SQL_ID obtained, the parameter must specify a non-null value, no default value;

2) Plan_hash_value: If the parameter is not specified or is null, all execution plans for the statement are displayed;

3) db_id: Specifies the execution plan of which database is displayed, default to the local database ID. Page to import the awr of other databases into the local library for analysis.

4) Format: Formats the control string. Similar to the same options for display.

4) Display_sqlset

Sql>declare

2 ss_name varchar2 (60);

3 begin

4 Ss_name: =dbms_sqltune.create_sqlset ();

5 Dbms_sqltune.capture_cursor_cache_sqlset (ss_name,600,100);

6 Dbms_output.put_line (Ss_name);

7 End;

8/

Sql>select sqlset_name,sql_id,sql_text fromdba_sqlset_statements where Upper (Sql_text)

Like ' SELECT * from test% ';

Sql>select * fromtable (Dbms_xplan.display_sqlset (' sts_6 ', ' ABCDEFG ', null, ' BASIC ROWS cost '));

--Note:

The 1.display_sqlset function displays the execution plan for statements stored in a SQL tuning set.

2. Parameters:

1) The name of the Sqlset_name:sql set. Each SQL set has a separate name that can be created

User specified, or automatically generated by the system), you need to specify from which SQL set the execution plan of the statement is read and displayed, which has no default value and must be specified;

2) sql_id: Available from User/dba/all_sqlset_plans. SQL_ID obtained, the parameter must specify a non-null value, no default value;

3) Plan_hash_value: If not specified or null, all execution plans for the statement are displayed;

4) Format: Formats the control string. Same as display's format option;

5) owner of the Sqlset_owner:sql set, default to the current user name.

5) Display_sql_plan_baseline

Sql>select * fromtable (dbms_xplan.display_sql_plan_baseline (sql_handle =

' Sys_sql_66cc81707e560a32 '));

--Note:

The 1.display_sql_plan_baseline function displays the plan that is stored in the data dictionary for the SQL Execution plan baseline.

2. Parameters:

1) sql_handle: The handle name of the SQL that the execution plan baseline belongs to is automatically generated by Oracle when it is created or loaded into the execution plan to the baseline, and can be queried by the view dba_sql_plan_baselines, default to NULL;

2) Plan_name: The name of an execution plan in the Execution plan baseline, automatically generated when Oracle creates or loads the execution plan into the baseline, can be queried by the view dba_sql_plan_baselines, default to NULL;

3) Format: Formats the control string. formatting options for the Display_sqlset function and display

The same options.

4) when both sql_handle and plan_name are empty, all execution plans in all baseline data are displayed.

3, AUTOTRACE

1) Configure test user to use Autot

Sql>conn Sys/sys as Sysdba

Sql>@?/sqlplus/admin/plustrce. Sql

Sql>grant Plustrace totest;

2) How to use

1.SET AUTOTRACE on: Opens AUTOTRACE and outputs everything, including query results, execution plans, and performance statistics for the statement itself.

2.SET AUTOTRACE on EXPLAIN: Opens the AUTOTRACE and outputs the query results and execution plan of the statement itself, without outputting performance statistics.

3.SET AUTOTRACE on STATISTICS: Opens the AUTOTRACE and outputs the query results and performance statistics for the statement itself, without outputting the execution plan.

4.SET AUTOTRACE TRACE: Opens the AUTOTRACE and outputs execution plan and performance statistics without outputting the query results of the statement itself.

5.SET AUTOTRACE TRACE EXPLAIN: Opens the AUTOTRACE and outputs the execution plan without outputting the query results and performance statistics of the statement itself.

6.SET AUTOTRACE tracestatistics: Opens the AUTOTRACE and outputs performance statistics without outputting the query results and execution plan of the statement itself.

7.SET AUTOTRACE off: AUTOTRACE off.

4. Other methods

1) sql_trace (or 10046 trace event): This method displays the execution plan and related statistics in the trace file:

Sql>alter session Set Sql_trace=true;

Sql>select * from t_users where user_id= ' TEST ';

Sql>alter session Set Sql_trace=false;

Sql>select distinct spid from v$process p,v$session s, V$mystat m where p.addr=s.paddr and S.sid=m.sid;

Sql>show parameter User_dump_dest

Sql>ed/home/oracle/admin/ora10g/udump/ora10g_ora_sid.trc

2) Optimizer_trace (or 10053 trace event): This method records the process of the optimizer analysis selection execution plan in the trace file:

Sql>alter session Set "_optimizer_trace" =all;

Sql>explain plan for SELECT * from T_users whereusername= ' TEST ';

Sql>alter session Set "_optimizer_trace" =none;

Sql>select distinct spid from v$process p,v$session s, V$mystat m where p.addr=s.paddr and S.sid=m.sid;

Sql>ed/home/oracle/admin/ora10g/udump/ora10g_ora_sid.trc

The most authoritative Oracle acquisition SQL execution Plan Daquan