DUMPING SQL EXECUTION HISTORY INFORMATION SNAPSHOTS IN STANDARD EDITION
NEEDS
How can I get historical SQL execution information in a Standard Edition database?
GOAL
The goal of this note is to invent a method for storing and getting the historical information about the specific SQL ever executed in past in an Oracle Database. As a database on the Standard Edition does not provide this kind of historical information it was highly required to create something similar.
Without a doubt, it is not the best approach to achieve that but this approach for storing and getting historical information could be enough for some cases.
RELATED
ALTERNATIVE TO V$ACTIVE_SESSION_HISTORY VIEW IN STANDARD EDITION
CLEAN UP DATABASE FROM THE SESSHIS USER AND HIS OBJECTS
SOLUTION
Create a dedicated tablespace for storing historical session information, for instance with the SESSHIS name
CREATE TABLESPACE SESSHIS DATAFILE '/d00/oracle/oradata/meridb2/sesshis_001.dbf'
size 100M autoextend on next 500 maxsize unlimited;
You might need to add more datafiles into your tablespace in your specific case. The more workload in a database the largest tablespace would be.
-- DROP USER SESSHIS CASCADE;
CREATE USER SESSHIS IDENTIFIED BY SESSHIS DEFAULT TABLESPACE SESSHIS;
GRANT UNLIMITED TABLESPACE TO SESSHIS;
GRANT CONNECT,RESOURCE TO SESSHIS;
GRANT CREATE VIEW TO SESSHIS;
GRANT CREATE JOB TO SESSHIS;
GRANT SELECT ON V_$SESSION TO SESSHIS;
GRANT SELECT ON V_$SQL TO SESSHIS;
GRANT SELECT ON DBA_SCHEDULER_JOBS TO SESSHIS;
GRANT SELECT ON DBA_SEGMENTS TO SESSHIS;
GRANT SELECT ON DBA_SCHEDULER_RUNNING_JOBS TO SESSHIS;
GRANT EXECUTE ON DBMS_LOCK TO SESSHIS;
Connect to the database as the formerly created user
SQL> CONNECT SESSHIS/SESSHIS
Connected.
SQL> SHO USER
USER is "SESSHIS"
-- DROP TABLE SESSHIS.SQL_HISTORY;
-- DROP INDEX SESSHIS.SQL_HISTORY_IDX_001;
CREATE TABLE SESSHIS.SQL_HISTORY (
SNAPSHOT_TIMESTAMP DATE,
PARSING_SCHEMA_NAME VARCHAR2(30),
SQL_ID VARCHAR2(13),
CHILD_NUMBER NUMBER,
OBJECT_STATUS VARCHAR2(19),
IS_OBSOLETE VARCHAR2(1),
FIRST_LOAD_TIME VARCHAR2(76),
LAST_ACTIVE_TIME DATE,
ELAPSED_TIME_TOTAL_SEC NUMBER,
CPU_WT_TOTAL_SEC NUMBER,
IO_WT_TOTAL_SEC NUMBER,
CONCURRENCY_WT_TOTAL_SEC NUMBER,
APPLICATION_WT_TOTAL_SEC NUMBER,
CLUSTER_WT_TOTAL_SEC NUMBER,
EXECUTIONS NUMBER,
ELAPSED_TIME_PER_EXEC_SEC NUMBER,
CPU_WT_PER_EXEC_SEC NUMBER,
IO_WT_PER_EXEC_SEC NUMBER,
CONCURRENCY_WT_PER_EXEC_SEC NUMBER,
APPLICATION_WT_PER_EXEC_SEC NUMBER,
CLUSTER_WT_PER_EXEC_SEC NUMBER,
DISK_READS NUMBER,
BUFFER_GETS NUMBER,
DIRECT_WRITES NUMBER,
ROWS_PROCESSED NUMBER,
CHILD_LATCH NUMBER,
SQL_PROFILE VARCHAR2(64),
COMMAND_TYPE NUMBER,
SQL_TEXT VARCHAR2(1000)
);
CREATE INDEX SESSHIS.SQL_HISTORY_IDX_001 ON SESSHIS.SQL_HISTORY(SNAPSHOT_TIMESTAMP);
A procedure for gathering and storing historical session information into the SESSHIS.SQL_HISTORY table
CREATE OR REPLACE PROCEDURE SESSHIS.GATHER_SQL_STATISTICS AS
BEGIN
EXECUTE IMMEDIATE q'[ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS']';
FOR ROW IN (
SELECT TO_DATE(SYSDATE,'DD-MON-YYYY HH24:MI:SS') SNAPSHOT_TIMESTAMP,
PARSING_SCHEMA_NAME,
SQL_ID,
CHILD_NUMBER,
OBJECT_STATUS CURSOR_STATUS,
IS_OBSOLETE IS_CURSOR_OBSOLETE,
FIRST_LOAD_TIME FIRST_RUN,
TO_DATE(LAST_ACTIVE_TIME,'DD-MON-YYYY HH24:MI:SS') LAST_ACTIVE_TIME,
ROUND(ELAPSED_TIME/1000000,2) ELAPSED_TIME_TOTAL_SEC,
ROUND(CPU_TIME/1000000,2) CPU_WT_TOTAL_SEC,
ROUND(USER_IO_WAIT_TIME/1000000,2) IO_WT_TOTAL_SEC,
ROUND(CONCURRENCY_WAIT_TIME/1000000,2) CONCURRENCY_WT_TOTAL_SEC,
ROUND(APPLICATION_WAIT_TIME/1000000,2) APPLICATION_WT_TOTAL_SEC,
ROUND(CLUSTER_WAIT_TIME/1000000,2) CLUSTER_WT_TOTAL_SEC,
EXECUTIONS,
ROUND(ELAPSED_TIME/1000000/DECODE(EXECUTIONS,0,1,EXECUTIONS),2) ELAPSED_TIME_PER_EXEC_SEC,
ROUND(CPU_TIME/1000000/DECODE(EXECUTIONS,0,1,EXECUTIONS),2) CPU_WT_PER_EXEC_SEC,
ROUND(USER_IO_WAIT_TIME/1000000/DECODE(EXECUTIONS,0,1,EXECUTIONS),2) IO_WT_PER_EXEC_SEC,
ROUND(CONCURRENCY_WAIT_TIME/1000000/DECODE(EXECUTIONS,0,1,EXECUTIONS),2) CONCURRENCY_WT_PER_EXEC_SEC,
ROUND(APPLICATION_WAIT_TIME/1000000/DECODE(EXECUTIONS,0,1,EXECUTIONS),2) APPLICATION_WT_PER_EXEC_SEC,
ROUND(CLUSTER_WAIT_TIME/1000000/DECODE(EXECUTIONS,0,1,EXECUTIONS),2) CLUSTER_WT_PER_EXEC_SEC,
DISK_READS,
BUFFER_GETS,
DIRECT_WRITES,
ROWS_PROCESSED,
CHILD_LATCH,
SQL_PROFILE,
COMMAND_TYPE,
SQL_TEXT
FROM
V$SQL
WHERE
PARSING_SCHEMA_NAME NOT IN ('SESSHIS')
)
LOOP
INSERT INTO SESSHIS.SQL_HISTORY VALUES (
ROW.SNAPSHOT_TIMESTAMP,
ROW.PARSING_SCHEMA_NAME,
ROW.SQL_ID,
ROW.CHILD_NUMBER,
ROW.CURSOR_STATUS,
ROW.IS_CURSOR_OBSOLETE,
ROW.FIRST_RUN,
ROW.LAST_ACTIVE_TIME,
ROW.ELAPSED_TIME_TOTAL_SEC,
ROW.CPU_WT_TOTAL_SEC,
ROW.IO_WT_TOTAL_SEC,
ROW.CONCURRENCY_WT_TOTAL_SEC,
ROW.APPLICATION_WT_TOTAL_SEC,
ROW.CLUSTER_WT_TOTAL_SEC,
ROW.EXECUTIONS,
ROW.ELAPSED_TIME_PER_EXEC_SEC,
ROW.CPU_WT_PER_EXEC_SEC,
ROW.IO_WT_PER_EXEC_SEC,
ROW.CONCURRENCY_WT_PER_EXEC_SEC,
ROW.APPLICATION_WT_PER_EXEC_SEC,
ROW.CLUSTER_WT_PER_EXEC_SEC,
ROW.DISK_READS,
ROW.BUFFER_GETS,
ROW.DIRECT_WRITES,
ROW.ROWS_PROCESSED,
ROW.CHILD_LATCH,
ROW.SQL_PROFILE,
ROW.COMMAND_TYPE,
ROW.SQL_TEXT
);
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Something went wrong');
END;
/
A procedure for purging historical session information from the SESSHIS.SQL_HISTORY table
CREATE OR REPLACE PROCEDURE SESSHIS.PURGE_SQL_STATISTICS AS
KEEP_DAYS NUMBER := 14;
BEGIN
DELETE FROM SESSHIS.SQL_HISTORY WHERE SNAPSHOT_TIMESTAMP <= SYSDATE - KEEP_DAYS ;
COMMIT;
END;
/
A job for executing the SESSHIS.GATHER_SQL_STATISTICS procedure on an automatic manner
CONNECT SESSHIS/SESSHIS
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STORING_SQL_STATISTICS',
job_type => 'STORED_PROCEDURE',
job_action => 'SESSHIS.GATHER_SQL_STATISTICS',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
comments => 'Performing a dump of V$SQL view every 15 minutes.');
END;
/
-- Enable job
BEGIN
DBMS_SCHEDULER.ENABLE ('STORING_SQL_STATISTICS');
END;
/
-- Delete job (if needed)
-- BEGIN
-- DBMS_SCHEDULER.DROP_JOB ('STORING_SQL_STATISTICS');
-- END;
-- /
A job for executing the SESSHIS.PURGE_SQL_STATISTICS procedure on an automatic manner
CONNECT SESSHIS/SESSHIS
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'PURGING_SQL_STATISTICS',
job_type => 'STORED_PROCEDURE',
job_action => 'SESSHIS.PURGE_SQL_STATISTICS',
repeat_interval => 'FREQ=HOURLY;INTERVAL=1',
comments => 'Purge records older than 14 days in the SESSHIS.SQL_HISTORY table');
END;
/
-- Enable job
BEGIN
DBMS_SCHEDULER.ENABLE ('PURGING_SQL_STATISTICS');
END;
/
-- Delete job (if needed)
-- BEGIN
-- DBMS_SCHEDULER.DROP_JOB ('PURGING_SQL_STATISTICS');
-- END;
-- /
Verify that jobs have been created
-- Short version
SET LINES 300
SET PAGES 999
COL OWNER FOR A10
COL JOB_NAME FOR A30
COL LAST_START_DATE FOR A20
SELECT OWNER,
JOB_NAME,
JOB_TYPE,
TO_CHAR(LAST_START_DATE,'DD-MON-YYYY HH24:MI:SS') LAST_START_DATE,
ENABLED,
AUTO_DROP,
STATE
FROM
DBA_SCHEDULER_JOBS
WHERE
OWNER = 'SESSHIS';
OWNER JOB_NAME JOB_TYPE LAST_START_DATE ENABL AUTO_ STATE
---------- ------------------------------ ---------------- -------------------- ----- ----- ---------------
SESSHIS STORING_SQL_STATISTICS STORED_PROCEDURE 06-APR-2021 14:35:26 TRUE TRUE SCHEDULED
SESSHIS PURGING_SQL_STATISTICS STORED_PROCEDURE 06-APR-2021 14:35:29 TRUE TRUE SCHEDULED
-- Extended version
SELECT OWNER,
JOB_NAME,
JOB_ACTION,
JOB_TYPE,
TO_CHAR(START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE,
REPEAT_INTERVAL,
TO_CHAR(END_DATE,'DD-MON-YYYY HH24:MI:SS') END_DATE,
ENABLED,
AUTO_DROP,
STATE,
RUN_COUNT,
FAILURE_COUNT,
TO_CHAR(LAST_START_DATE,'DD-MON-YYYY HH24:MI:SS') LAST_START_DATE,
LAST_RUN_DURATION,
TO_CHAR(NEXT_RUN_DATE,'DD-MON-YYYY HH24:MI:SS') NEXT_RUN_DATE
FROM
DBA_SCHEDULER_JOBS
WHERE
OWNER = 'SESSHIS';
Query the historical SQL execution information table
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT DISTINCT SNAPSHOT_TIMESTAMP FROM SESSHIS.SQL_HISTORY ORDER BY 1;
SNAPSHOT_TIMESTAMP
-----------------------------
06-APR-2021 14:35:26
1 rows selected.
That's it. By querying the table with historical SQL execution information you can find SQL statement details for any SQL that was executed some time ago.
To remove all objects created with help of this blog note follow the CLEAN UP DATABASE FROM THE SESSHIS USER AND HIS OBJECTS
Database : 12c+