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

First, how to view the implementation plan

In the Oracle database, we can usually use the following methods (including but not limited to) to get the execution plan of the target SQL:

Explain plan command

DBMS_ XPLAN package

AUTOTRACE switch in SQLPLUS

10046 event

10053 event

AWR report or Statspack report

Some ready-made scripts (such as display_cursor_9i.sql, etc.)

The first four methods are commonly used.

1.1 explain plan command

Anyone who is used to using PL/SQL Developer knows that pressing the shortcut key F5 can display the execution plan of the target SQL. In fact, PL/SQL Developer invokes the explain plan command, and the shortcut key F5 is just a layer of encapsulation on the explain plan command.

The syntax of the explain plan command is to execute the following two commands in sequence:

Explain plan for + Target SQL

Select * from table (dbms_xplan.display)

First use the explain plan command to explain the target SQL, and then use "select * from table (dbms_xplan.display)" to view the execution plan obtained by using the explain plan command above.

Explain plan for select empno,ename,dname fromscott.emp,scott.dept where emp.deptno=dept.deptno

Select * from table (dbms_xplan.display)

What exactly did the explain plan command do? In Oracle 10g and above, if we execute the explain plan command against the target SQL, Oracle writes the specific execution steps generated by parsing the target SQL into PLAN_TABLE$, and then executes "select * from table (dbms_xplan.display)" only to display these specific execution steps in formatted form from PLAN_TABLE$. PLAN_TABLES$ is a GLOBALTEMPORARY TABLE of ON COMMIT PRESERVE ROWS, so here Oracle can make sure that each Session can only see the execution plan generated by the SQL that it executes, and the process of each Session writing the execution plan to the PLAN_TABLE$ does not interfere with each other.

1.2 DBMS_ Xplan package

Using the method in the DBMS_XPLAN package is the second way to get the execution plan of the target SQL in the Oracle database. For different application scenarios, you can choose one of the following four methods:

Select * from table (dbms_xplan.display)

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

Select * from table (dbms_xplan.display_cursor ('sql_id/hash_value',child_cursor_number,'advanced'))

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

Method 1 needs to be used in conjunction with the explain plan command, which was described in the previous section.

Method 2 is used to view the execution plan of the SQL that has just been executed in SQLPLUS. Here, the value of the first and second parameter passed in for the method DBMS_XPLAN.DISPLAY_CURSOR is null, the third parameter value is "advanced", and the value of the third input parameter can also be "all", but the result displayed with "advanced" will be more detailed than the result displayed by "all".

Set linesize 800 pagesize 900

Col plan_table_output for a200

Select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno

Select * fromtable (dbms_xplan.display_cursor (null,null,'advanced'))

Select empno,ename,dname from scott.emp,scott.deptwhere emp.deptno=dept.deptno

Select * fromtable (dbms_xplan.display_cursor (null,null,'all'))

The result obtained by "all" has less content in the "Outline Data" section than the result displayed by "advanced".

Method 3 is used to view the execution plan of the specified SQL. Here, the value of the first parameter passed in for the method DBMS_XPLAN.DISPLAY_CURSOR is the SQL ID or SQL HASH VALUE of the specified SQL, the value of the second parameter is the Chile Cursor Number of the execution plan to be viewed, and the third parameter has been mentioned when introducing method 2. This parameter usually uses "advanced".

Selectsql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'selectempno,ename%'

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER

-

Selectempno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno 3yfu3wh250aqt 38808281 0

The sql_id is 3yfu3wh250aqt.SQL HASH_VALUE is 38808281 and the corresponding ChildCursor Number is 0.

SQL ID and SQLHASH_VALUE are essentially the same thing, and they can be converted to each other, which is why the value of the first parameter passed in by the method DBMS_XPLAN.DISPLAY_CURSOR can be either SQL ID or SQLHASH_VALUE.

Selectlower (trim ('3yfu3wh250aqt')) sql_id

Trunc (mod (sum ((instr ('0123456789abcdfghjkmnpqrstuvwxyz')

Substr (lower (trim ('3yfu3wh250aqt'), level,1))-1) * power (32 power (trim (' 3yfu3wh250aqt'))-level))

Power (2jre 32)) hash_value

Fromdual

Connectby level

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.