How Oracle views the execution plan

1. Get an execution plan for SQL in PL developer
Enter the target SQL for which you want to view the execution plan, and then press the shortcut key F5.
2.explain Plan Command
Explain plan for + target SQL
SELECT * FROM table (Dbms_xplan.display)
3. Dbms_xplan Bag
1) SELECT * FROM table (Dbms_xplan.display_cursor (Null,null, ' advanced '))
It is used to view the execution plan of the SQL that has just executed in Sqlplus. The value of the first and second arguments passed here is null, the value of the third parameter is advanced,
The value of the third input parameter can also be all, except that using advanced will result in a more detailed display than the result of all.
2) SELECT * FROM table (dbms_xplan.display_cursor (' Sql_id/hash_value ', Child_cursor_number, ' advanced ')
It is used to view the execution plan for the specified SQL. The value of the first parameter passed in here is the SQL ID of the specified SQL or SQL HASH value, and the value of the second parameter is the one you want to view.
The child Cursor number where the execution plan resides, and the third argument, like the one above, we generally use advanced
As long as the target SQL corresponding child cursor is in the library cache, we can query the child cursor details of the target SQL from V$sql, including
SQL ID, SQL HASH VALUE, child Cursor number, and so on.
Select Sql_text,sql_id,hash_value,child_number from V$sql where sql_text like ' SQL Target statement ';
You can use this method to view the SQL execution plan as long as the child cursor where the target SQL execution plan resides is not yet out of the Shared Pool by age.
3) SELECT * FROM table (Dbms_xplan.display_awr (' sql_id '))
It is used to view all historical execution plans for the specified SQL. Using Method 1), 2) the ability to display the target SQL execution plan is based on the assumption that the SQL execution plan is still in the shared pool, and if
The SQL execution plan has been out of the Shared Pool by age, so as long as the SQL execution plan is captured by Oracle into the AWR repository, we can use this method to check
Look at all the historical execution plans for that SQL.
Method of manually collecting AWR reports: exec dbms_workload_repository.create_snapshot ();
See if the execution plan for the target SQL is out of the Shared Pool:select sql_text,sql_id,version_count,executions from V$sqlarea where
Sql_text like ' Target SQL ';
Empty the shared pool:alter system flush Shared_pool; (Do not arbitrarily execute this statement in the production environment)
4. AUTOTRACE switch
Set autotrace [off | on | traceonly]
1) Execute command set autotrace on in the current session of Sqlplus, and the autotrace switch can be fully opened in the current session. In this session, the following
All SQL statements executed, in addition to displaying the results of the SQL execution, will also show additional execution plans and resource consumption for those SQL.
2) Execute the command set Autotrace off in the current session of the Sqlplus, and the autotrace switch can be closed in the current session so that the session will be executed later
All SQL will only display the results of the SQL execution. The default value for the autotrace switch is off.
3) Execute command set Autotrace traceonly in the current session of Sqlplus, you can autotrace switch in the current session to not display the details of SQL execution results
The way to open it. The specific content that applies to those SQL execution results is particularly long, and the SQL that will continuously swipe the screen, in which case we don't care about the results of SQL execution, but only the execution plan of the relational SQL
and resource consumption.
5.10046 Events and Tkprof command (most accurate of the resulting execution plan)
This approach gives you the logical read, physical read, and time spent on each execution step in the execution plan.
1) Oradebug Setmypid
Indicates that the Oradebug command is ready to be used for the current session.
2) Oradebug event 10046 Trace name Context Forever,level 12
Activates the 10046 event in the current session.
3) Execute target SQL
4) Oradebug Tracefile_name
Displays the path and name of the trace file that corresponds to the current session activation 10046 event.
5) Oradebug Event 10046 Trace name context off
Closes the 10046 event in the current session.
6) Absolute path to the target file after tkprof trace file absolute path translation
Use the tkprof command to translate trace files to make the results more intuitive.
6. (Get real, accurate execution plan) if the execution plan of the target SQL is also in the shared pool, you can use script display_cursor-9i.sql and stored procedure printsql to get its true execution plan and resource consumption. If the execution plan for the target SQL has been out of the Shared pool by age, we can execute dbms_xplan. Display_awr either use AWR SQL reports and Statspack SQL reports to get their historical execution plan and resource consumption.
1) Display_cursor_9i.sql Usage (for Oracle 9i and later database versions)
SQL HASH VALUE and child Cursor number of the target SQL passed to the execution plan to be viewed when executing the script
A. Executing the target SQL
B. Querying Hash_value and child number
Select Sql_text,hash_value,child_number from V$sql where sql_text like ' Target SQL ';
C. Execute the Script
@ Script Hash_value Child_number
2) usage of printsql (for 9i/10g/11g)
The stored procedure Printsql is a wrapper on the script display_cursor_9i.sql, which prints the process that specifies the SPID or session ID, and the SQL that the session is executing, and the actual execution plan and resource consumption that it corresponds to.
A. Executive Topas
Get the PID of Oracle
B. Set serveroutput on size 1000000
C. exec printsql (PID, ' SPID ')
Note: PID is a query-out PID, ' SPID ' is entered as-is.
3) Get the AWR SQL report (10g and later versions)
Manually execute the script $ORACLE _home/rdbms/admin/awrsqrpt.sql and enter the report type (text/html), the snapshot range to view (snapshots in the last few days), the target SQL ID, and the name of the AWR SQL report to be produced.
4) Get Statspack SQL report (9i)
Manually execute the script $ORACLE _home/rdbms/admin/sprepsql, and enter the scope of the snapshot you want to view, the target SQL HASH VALUE, and the name of the Statspack SQL report that you want to generate.
Note: The Statspack report has been deployed beforehand and the level value of the Statspack report is greater than or equal to 6 (the default value for the Statspack report level is 5)

7. How to view the execution order of the execution plan
First from the beginning of the continuous look to the right, until you see the rightmost side of the place, for not tied to the right of the first execution; If you see the juxtaposition, from the top down, for the parallel part, the top of the first execution.

How Oracle views the execution plan