Print Friendly, PDF & Email
A way to catch and store every single SQL ever executed in a Standard Edition database. Creation SQL execution snapshots for later analysis.

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 INFOMATION 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

TABLESPACE

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.

USER
-- 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"
TABLE AND INDEX
-- 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);
PROCEDURES

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;
/
JOBS

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 JOBS

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 HISTORICAL SQL TABLE

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.

CLEAN UP

To remove all objects created with help of this blog note follow the CLEAN UP DATABASE FROM THE SESSHIS USER AND HIS OBJECTS

 
 

Version  : 14:45 06-Apr-2021
Database : 12c+