In Oracle databases, the DBA_HIST_SQLSTAT view stores historical SQL execution statistics, which can be useful for performance tuning and analysis. This guide provides a clean and optimized script to retrieve SQL text for a specific SQL ID.
Table of Contents
SQL Script:
This script will help you to retrieve SQL text for a specific SQL ID from DBA_HIST_SQLSTAT:
-- Purpose: Retrieve SQL from DBA_HIST_SQLSTAT for a specific SQL ID
SET LONG 32000;
SET VERIFY OFF;
SET PAGESIZE 999;
SET LINES 132;
COL SQL_ID FORMAT A15;
COL SQL_TEXT FORMAT A90;
SELECT sql_id,
DBMS_LOB.SUBSTR(sql_text, 3999, 1) AS sql_text
FROM dba_hist_sqltext
WHERE sql_id = '&sql_id';
Sample Output:
This script is useful for database administrators and performance tuners who need to retrieve SQL statements from the AWR history for analysis. Using this approach, you can quickly locate and examine past SQL executions for troubleshooting and optimization.
For more details on Oracle SQL performance tuning, visit: 👉 Oracle Documentation on AWR
To find Top SQL in Library Cache: Top SQL in Library Cache
