![]() To visualize the actual execution plan, you can use DBMS_XPLAN.DISPLAY_CURSOR: SELECT *įROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE')) To instruct Oracle to store the actual execution plan for a given SQL query, you can use the GATHER_PLAN_STATISTICS query hint: SELECT /*+ GATHER_PLAN_STATISTICS */ The actual plan should not differ significantly from the estimated one, as long as the table statistics have been properly collected by the underlying relational database. So, unlike the estimated Execution Plan, you need to execute the SQL query in order to get its actual execution plan. The actual SQL execution plan is generated by the Optimizer when running the SQL query. If you have installed SQL Developer, you can easily get the estimated execution plan for any SQL query without having to prepend the EXPLAIN PLAN FOR command: The ALL +OUTLINE formatting option allows you to get more details about the estimated execution plan than using the default formatting option. To view the estimated execution plan, you need to use DBMS_XPLAN.DISPLAY, as illustrated in the following example: SELECT *įROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE')) ![]() ![]() When using Oracle, if you prepend the EXPLAIN PLAN FOR command to a given SQL query, the database will store the estimated execution plan in the associated PLAN_TABLE: EXPLAIN PLAN FOR You can generate the estimated execution plan from any SQL client using EXPLAIN PLAN FOR or you can use Oracle SQL Developer for this task. The estimated execution plan is generated by the Optimizer without executing the SQL query. ![]()
0 Comments
Leave a Reply. |