Menu
I have an application that executes some sql queries. How can I get execution plan for currently executing query from sqlplus or some other oracle client? I can amend oracle session that is used in application, if that is necessary.
I do not want to use explain plan and execute that query by hand, I need to get actual execution plan that is used for query.
425
The estimated SQL execution plan The estimated execution plan is generated by the Optimizer without executing the SQL query. 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.
On the SQL Server Management Studio toolbar, click Database Engine Query. You can also open an existing query and display the estimated execution plan by clicking the Open File toolbar button and locating the existing query. Enter the query for which you would like to display the actual execution plan.
The SQL_ID of a statement can be found in an AWR or ASH report or by selecting it from the database data dictionary using the V$SQL view. If the SQL can be identified with a particular identifiable string or by some kind of unique comment such as: /* TARGET SQL */ then this will make it easier to locate.
You can run explain plain on historical queries from the SGA -examples
And listing B.
Example:
SELECT username, prev_sql_id
FROM v$session
WHERE username = 'RDEVALL' -- example user
SQL ID returned
RDEVALL a1d4z5ruduzrd
RDEVALL avmbutuknvb6j
RDEVALL 75g0tqd9x743y
RDEVALL 5fnkh6c8mqqt3
RDEVALL 75g0tqd9x743y
Pick query ID and use here:
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('a1d4z5ruduzrd')); -- replace with sql ID as needed
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
