Obtain SQL Execution plans from the most authoritative Oracle Database

Obtain SQL Execution plans from the most authoritative Oracle Database

This document is compiled and summarized based on relevant information. It mainly describes the most authoritative and correct methods and steps for obtaining SQL statement execution plans in Oracle databases. In addition, the meanings and usage of options in each method are also described in detail to help you check and use them in your daily work. As I have not found that this blog supports the attachment upload function, if you need a PDF file format, you can ask for it or download it from the group. For more information, see the source.

1. query v $ SQL _plan:

SQL> col "Query Plan_Table" format a100SQL> select id, lpad ('', 2*(level-1 )) | operation | ''| options |'' | object_name | ''| decode (id, 0, 'cost = '| Cost) "QueryPlan_Table" fromv $ SQL _planstartwith id = 0andsql_id = '& SQL _id 'andplan _ hash_value = & plan_hashconnect byprior id = require =' & SQL _id 'andplan _ hash_value = & plan_hash; -- Note: SQL _ID can be used to query v $ sqltext and dba_hist_sqltext. It can also be obtained through other methods.

2. 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: name of the table storing the query plan. The default value is PLAN_TABLE.

2. STATEMENT_ID: SQL statement ID. SetStatement _

Id clause. If it is NULL, take the last interpreted statement.

3. FORMAT: output FORMAT. In the DISPLAY function, the following predefined formats are available:

1) 'basic ': BASIC format. Only the ID, name, option, and Object Name of each operation in the query plan are output.

2) 'typical': a TYPICAL format. In addition to the content in the basic format, it also outputs the number of record lines for each operation,

Number of bytes, cost and time, and related prompts (such as remote SQL statements and optimizer recommendations ). For example

If a predicate exists, filter conditions and access conditions in each operation are output. In addition, if partition tables are involved, partition pruning information is also output; if parallel queries are involved, parallel operation information (such as table queue information and parallel query distribution mode) is also output ). The default format.

3) 'serial': SERIAL execution format. This format is basically the same as the output content of a typical format. The difference is that for parallel queries, it does not output relevant parallel content.

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

In addition, you can add or shield some keywords in the formatted string to refine the output,

For example, "BASICROWS" and "TYPICAL-PREDICATE"

1) ROWS: number of records estimated by the optimizer;

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

3) COST: The COST estimated by the optimizer;

4) PARTITION: PARTITION pruning;

5) PARALLEL: PARALLEL query;

6) PREDICATE: PREDICATE;

7) PROJECTION: field PROJECTION;

8) ALIAS: ALIAS;

9) REMOTE: Distributed Query Information;

10) NOTE: related comments.

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:

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

2. You must have the SELECT permission for views V $ SQLV, $ SQL _PLAN, and V $ SQL _PLAN_STATISTICS_ALL;

3. parameters:

1) SQL _ID: If SQL _ID is not specified (NULL), the last SQL statement executed in the current session is displayed by default.

2) CURSOR_CHILD_NO: subcursor sequence number of the statement. If this parameter is not specified, the execution plan of all child game targets of the statement is displayed.

3) FORMAT: FORMAT the control string. The formatting of the DISPLAY function controls that all options of the string apply to the DISPLAY_CURSOR function. If you use GATHER_PLAN_STATISTICS or set the system parameter STATISTICS_LEVEL to "ALL" to collect the performance statistics of statement running when running a statement, additional options are available in the refinement options to select whether to output the data.

4) IOSTATS: whether to output the input/output (IO) statistics of the plan;

5) MEMSTATS: whether to output the input memory statistics (memory usage, memory read count, etc.) when automatic PGA management is enabled );

6) ALLSTATS: contains all IOSTATS and MEMSTATS;

7) LAST: the preceding three options output the total data generated by all the executions of the cursor. You can also add the LAST option to limit only the statistics for the LAST run.

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

8) 'advanced ': ADVANCED format. In addition to all the content in the full format, the advanced format also outputs the Bind Variable peek information and plan summary information as needed;

9) OUTLINE: whether to display the plan summary as a prompt;

10) PEEKED_BINDS: whether the Bind Variable peeping information is displayed;

11) BUFFSTATS: whether to display the number of memory reads (including consistent reads and current reads). This information is part of IOSTATS;

12) PLAN_HASH: whether to DISPLAY the hash value of the plan. This option is also applicable to the DISPLAY function.

3) DISPLAY_AWR

SQL>select sql_id, to_char(substr(sql_text,0,2000))fromdba_hist_sqltextwhereupper(sql_text) like 'SELECT * FROM TEST%';SQL>select * fromtable(dbms_xplan.display_awr('&sql_id'));

-- Note:

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

2. SELECT permission must be granted to the following views: DBA_HIST_ SQL _PLAN and DBA_HIST_SQLTEXT.

3. parameters:

1) SQL _ID: it can be obtained from DBA_HIST_ SQL _PLAN. SQL _ID or DBA_HIST_SQLTEXT. SQL _ID. This parameter must be null and has no default value;

2) PLAN_HASH_VALUE: If this parameter is not specified or is NULL, all execution plans of the statement are displayed;

3) DB_ID: Specifies the execution plan of the database to be displayed. The default value is the local database ID. Page to import AWR of other databases to the local database for analysis.

4) FORMAT: FORMAT the control string. Similar to the DISPLAY option.

4) DISPLAY_SQLSET

SQL>declaress_name varchar2(60);beginss_name :=dbms_sqltune.create_sqlset();dbms_sqltune.capture_cursor_cache_sqlset(ss_name,600,100);dbms_output.put_line(ss_name);end;/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:

1. The DISPLAY_SQLSET function displays the execution plan of the statements stored in an SQL tuning set.

2. parameters:

1) SQLSET_NAME: name of the SQL set. Each SQL set has a separate name.

User-specified, or automatically generated by the system). You must specify the SQL set from which the statement execution plan is read and displayed. This parameter has no default value and must be specified;

2) SQL _ID: it can be obtained from USER/DBA/ALL_SQLSET_PLANS. SQL _ID. This parameter must be set to a non-null value with no default value;

3) PLAN_HASH_VALUE: if it is not specified or NULL, all execution plans of the statement are displayed;

4) FORMAT: FORMAT the control string. The FORMAT option is the same as that of DISPLAY;

5) SQLSET_OWNER: the owner of the SQL set. The default user name is the current user name.

5) DISPLAY_ SQL _PLAN_BASELINE

SQL>select * fromtable(dbms_xplan.display_sql_plan_baseline(sql_handle =>'SYS_SQL_66cc81707e560a32'));

-- Note:

1. The DISPLAY_ SQL _PLAN_BASELINE function displays the SQL Execution Plan baseline plan stored in the data dictionary.

2. parameters:

1) SQL _HANDLE: name of the SQL handle to which the execution plan baseline belongs. It is automatically generated when Oracle creates or loads the execution plan to the baseline. It can be queried through the view dba_ SQL _plan_baselines. The default value is NULL;

2) PLAN_NAME: name of an execution plan in the execution plan baseline, which is automatically generated when an execution plan is created or loaded to the baseline by Oracle. You can use the view dba_ SQL _plan_baselines to query the plan. The default value is NULL;

3) FORMAT: FORMAT the control string. Formatting options of the DISPLAY_SQLSET function and DISPLAY

The options are the same.

4) When SQL _HANDLE and PLAN_NAME are empty, all execution plans in all baseline data are displayed.

3. AUTOTRACE

1) configure the test user to use autot

SQL>conn sys/sys as sysdbaSQL>@?/SQLPLUS/ADMIN/PLUSTRCE.SQLSQL>grant plustrace totest;

2) Usage

1. set autotrace on: Open AUTOTRACE and output all content, including the query results, execution plans, and performance statistics of the statement itself.

2. set autotrace on explain: Enable AUTOTRACE and output the query results and execution plans of the statement itself, without outputting performance statistics.

3. set autotrace on statistics: Open AUTOTRACE and output the query results and performance STATISTICS of the statement itself, without outputting the execution plan.

4. set autotrace trace: Enable AUTOTRACE and output the execution plan and performance statistics, without outputting the query results of the statement itself.

5. set autotrace trace explain: Enable AUTOTRACE and output the execution plan without outputting the query results and performance statistics of the statement itself.

6. set autotrace tracestatistics: Enable AUTOTRACE and output performance statistics without outputting the query results and execution plans of the statement itself.

7. set autotrace off: Disable AUTOTRACE.

4. Other Methods

1) SQL _TRACE (or 10046 tracking event): This method displays the execution plan and related statistics in the tracking 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_destSQL>ed/home/oracle/admin/ora10g/udump/ora10g_ora_sid.trc

2) OPTIMIZER_TRACE (or 10053 trace event): This method records the optimizer Analysis Selection execution plan process 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