Shulou(Shulou.com)05/31 Report--

This article mainly explains "introduction of six commonly used ways to view sql implementation plans". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "introduction of six commonly used ways to view the implementation plan of sql"!

1. Explain play

F5 key in PL/SQL

Explain plan for sql statement

Select * from table (dbms_xplan.display)

After explain plan for, the execution plan is written into plan_table$, and select * from table (dbms_xplan.display) writes out the specific steps in formatted form.

2. Dbms_ xplan package

Select * from table (dbms_xplan.display) can use this direct query after executing the explain plan for sql statement

Select * from table (dbms_xplan.display_cursor (null,null,'advanced')); after executing a sql, you can view the execution plan by executing this button immediately after execution.

Select * from table (dbms_xplan.display_cursor ('sql_id/hash_value',child_cursor_number,'advanced')); view the execution plan of the specified sql_id/hash_value

Select * from table (dbms_xplan.display_awr ('sql_id')); check the historical execution plan of the specified sql_id. The first 2 and 3 need to be in the execution plan or in the share pool before it can be collected. If the share pool has been emptied or the age out has been released, the execution plan can only be obtained from the awr repository through this method.

III. Set autot

Set autot on

Set autot trace

Set autot trace exp

Set autot trace stat

Set autot off

Fourth, 10046 and tkprof

Compared with the above three, 10046 defines the logical read, physical read, and time spent on each step in the actual execution plan of sql

Steps

1. Activate the 10046 event alter session set events' 10046 trace name context forever,level 12; or oradebug event 10046 trace name context forever,level 12

2. Execute sql

3. Close the 10046 event alter session set events' 10046 trace name context off' or oradebug event 10046 trace name context off

Of the first three of the above four methods, the implementation plan may not be correct.

At this point, I believe you have a deeper understanding of the "introduction of six commonly used ways to view sql implementation plans". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.