Query to extract statement from a subsequent SQL_ID EXPLAIN PLAN FOR the statement
Alessandro Caredda
With the article “Query to extract long statements from a subsequent SQL_ID EXPLAIN PLAN FOR the statement,” we provide a small PL/SQL procedure that allows you to extract the running statement from a SQL_ID.
Additionally, there is the script provided by Oracle that allows you to view the previously explained statement.
So, once you’ve retrieved the statement, you order it, insert the owning owner in front of the table names, and re-run the statement with EXPLAIN PLAN FOR data beforehand.
Example:
EXPLAIN PLAN FOR SELECT …. FROM …. WHERE …..;
Once the Explained message is returned, you can execute the utlxplp.sql script.
Query to extract statement from a subsequent SQL_ID EXPLAIN
set serveroutput on
DECLARE
ddl CLOB;
BEGIN
select a.sql_fulltext
INTO ddl
from v$sql a
where a.sql_id = '&sql_id' and
rownum <2;
dbms_output.put_line(ddl);
END;
/
—Execution plan of the statement related to the sql_id:
@$ORACLE_HOME/rdbms/admin/utlxplp.sql
Query to extract statement from a subsequent SQL_ID EXPLAIN PLAN FOR the statement
In addition to the Query to extract statement from SQL_ID, there’s more!
To view my guides on CRS Commands,click here!
At this LINK, instead, you’ll find the SQL queries.Stay updated on our activities by joining our community (make sure to like our pages, let’s connect, and stay updated on each of our new guides!): Facebook Youtube Linkedin For those who haven’t done so yet, we recommend saving this site among your bookmarks, so you’ll always have our solutions to the most common database problems at your fingertips, especially Oracle databases.