Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about how to get the implementation plan in Oracle. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

1. Explain plan

Explain plan takes the SQL statement as input, gets the execution plan of the SQL statement, and stores the output of the execution plan in the plan table as follows:

Explain plan for select * from t

Select * from table (dbms_xplan.display)

Note: Explain plan only generates an execution plan and does not actually execute SQL statements, so the resulting execution plan may be inaccurate because:

1) the current environment may be different from that when the plan was generated.

2) the data type of the bound variable is not considered

3) there is no variable snooping.

two。 Query dynamic performance view

If you want to get the actual execution plan of the SQL statement being executed or just finished (that is, get the execution plan in the library cache), you can query it in the dynamic performance view. The methods are as follows:

1) get the cursor of the SQL statement

Cursors are divided into parent cursors and child cursors, which are represented by the sql_id (or joint address and hash_value) fields and the child cursors by the child_number field.

If the SQL statement is running, you can get its cursor information from v$session, such as:

Select status, sql_id, sql_child_number from v$session where status='ACTIVE' and....

If you know that the SQL statement contains certain keywords, you can get its cursor information from the v$sql view, such as:

Select sql_id, child_number, sql_text from v$sql where sql_text like'% keyword'

2) get the execution plan in the library cache

To get the execution plan in the cache, you can directly query dynamic performance views such as v$sql_plan and v$sql_plan_statistics_all, but a more convenient way is:

Select * from table (dbms_xplan.display_cursor ('sql_id',child_number))

3) get the previous execution plan:

Set serveroutput off

Select * from table (dbms_xplan.display_cursor (null,null,'ALLSTATS LAST'))

3. AWR report

The AWR report saves the execution plan in the dynamic performance view to the dba_hist_sql_plan view, and we can query the execution plan in AWR in the following ways:

Select * from table (dbms_xplan.display_awr ('sql_id'))

4. Autotrace

Set autotrace is a feature of the sqlplus tool and can only be used in session connected through sqlplus. It is very suitable for testing the performance of SQL statements at development time, and there are several parameters to choose from:

SET AUTOTRACE OFF-does not display execution plans and statistics, which is the default mode

SET AUTOTRACE ON EXPLAIN-displays only the optimizer execution plan

SET AUTOTRACE ON STATISTICS-only statistics are displayed

SET AUTOTRACE ON-execution plan and statistics are displayed at the same time

SET AUTOTRACE TRACEONLY-does not really execute, only shows the expected execution plan, through explain plan

5. SQL_TRACE

SQL_TRACE can be enabled at the instance level or only at the session level as an initialization parameter. Enabling SQL_TRACE at the instance level will cause the activities of all processes to be tracked, including background processes and all user processes, which will usually lead to serious performance problems. Therefore, in general, we use sql_trace to track the current process as follows:

SQL > alter session set sql_trace=true

... The SQL statement being tracked.

SQL > alter session set sql_trace=false

If you want to track other processes, you can use the system package DBMS_SYSTEM. Oracle. SET_SQL_TRACE_IN_SESSION to implement, for example:

SQL > exec dbms_system.set_sql_trace_in_session (sid,serial#,true)-- start tracking

SQL > exec dbms_system.set_sql_trace_in_session (sid,serial#,false)-- end trace

Use the tkprof tool to convert the trace file generated by sql trace into an easy-to-read format, as follows:

Tkprof inputfile outputfile

After reading the above, do you have any further understanding of how to get the execution plan in Oracle? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.