![pl sql developer explain plan pl sql developer explain plan](https://www.thatjeffsmith.com/wp-content/uploads/2017/01/42_plans.png)
The query takes the same time to return (about 10 seconds) whether the TRACEONLY option is used or not. | 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |ģ32 bytes received via SQL*Net from client | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | SQL> SELECT pause_for_secs(10) FROM DUAL The following example show this in practice.ĬREATE OR REPLACE FUNCTION pause_for_secs(p_seconds IN NUMBER) RETURN NUMBER A As such, long running queries will still take a long time to complete, but they will not present their data. At first glance, using the TRACEONLY option of AUTOTRACE seems to remove this issue, but this option merely suppresses the output of the query data, it doesn't prevent the statement being run. If the query is particularly inefficient and/or returns many rows, this may take a considerable time. In order to get the execution plan the statement must be run to completion. This is a relatively easy way to get the execution plan but there is an issue. Switching on the AUTOTRACE parameter in SQL*Plus causes an explain to be performed on every query.ģ 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'Ĥ 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)Ĥ25 bytes received via SQL*Net from client SQL> GRANT plustrace TO my_test_user AUTOTRACE - The Easy Option? SQL> $ORACLE_HOME/sqlplus/admin/plustrce.sql This role is created using the following script. Some operations will likely need the PLUSTRACE role to be granted to the user performing the operation.
![pl sql developer explain plan pl sql developer explain plan](https://i.stack.imgur.com/kw1jV.png)
In Oracle 11g a shared PLAN_TABLE is created by default, but you can still create a local version of the table using the "utlxplan.sql" script. SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table SQL> GRANT ALL ON sys.plan_table TO public Creating a shared PLAN_TABLE prior to 11g This table can be located in the current schema or a shared schemaĪnd is created using in SQL*Plus as follows. The explain plan process stores data in the PLAN_TABLE.