Tips for quickly getting the execution plan of SQL with A-Time time

Tips for quickly getting the execution plan of SQL with A-Time time

Use spool to output the results to a file, and then go directly to the file to view the execution plan

There are two ways to get an execution plan with time:

1. Set the parameter statistics_level=all in the session

Alter session set statistics_level=all; executes SQLselect * from table (dbms_xplan.display_cursor (null,null,'allstats last'))

2. Use hint (/ * + gather_plan_statistics * /) in the statement, then find out the SQL_ID of the SQL, and then query the execution plan

Execute SQLselect / * + gather_plan_statistics * / count (*) from test; to query the SQL_IDselect sql_id,sql_text from v$sql where sql_text like'% / * + gather_plan_statistics * /%'of SQL; view the execution plan select * from table (dbms_xplan.display_cursor ('sql_id',null,'allstats'))

You can get an execution plan with time in both of the above ways.

But there is a drawback to viewing the execution plan in this way: you must wait until the full record of the SQL statement is output on the screen before you can view the execution plan.

In order not to wait for the execution of SQL before you can view the execution plan, and do not need to output the execution result of SQL on the screen.

You can output the results of the SQL and the execution plan to a file, and then go to the file to view the execution plan.

!!! Attention:!

Using spool to output the results to a file is to output the query results to a file, which contains real data, so it needs to be carefully considered.

Whether it is possible to query in this way.

Test:

You need to view a SQL execution plan with time

Method 1.

Set the session parameter alter session set statistics_level=all; to execute SQLselect / * + index (T1 idx_t1_c1) * / * from T1 where C1