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.

• Query to extract statement from a subsequent SQL_ID EXPLAIN PLAN FOR the statement
• Query per vedere se è cambiato il piano d’accesso ai dati – PLAN_HASH_VALUE
• Query to extract the statement from SQL_ID