Do you need a list of statements executed in your session? If it’s only about SQL statement text you may use Oracle database tools such as SQL Developer, SQLcl or SQL*Plus. In all these utilities there are history functions available. But if you want to get more information e.g. about execution performance then of course SQL Tuning Sets can be the means of choice. If you need more information on SQL Tuning Sets, read our posting Oracle SQL Tuning Sets (STS) – The foundation for SQL Tuning.

In Oracle Database 23 there is another possibility to receive information about SQL statements – it’s called SQL history. With SQL history the database tracks and reports details of your SQL history of queries in your current session. It monitors user-issued SQL statements in each user session on a best effort, depending on memory capacity. DDL, DML, and query statements are monitored; even error messages are displayed. However SQL statements issued in the background, in SYS schema and recursive SQL statements are excluded. The monitored information is exposed by the V$SQL_HISTORY dynamic performance view and can be enabled and disabled with a new introduced initialization parameter.

If you want to use it, a user with ALTER SYTEM privilege must first change the initialization parameter SQL_HISTORY_ENABLED instance-wide to TRUE in the required PDB. The default of course is FALSE. 

To get an idea how it works, let’s check V$PARAMETER in one of our pdbs. In my case I used 23ai on OCI Oracle Base Database Service and tested on an Exadata with 23.6.

SQL> alter session set container=DB23_PDB1;
Session altered.

SQL> col name format a15
SQL> col value format a10
SQL> col isdefault format a10

SQL> select name, value, default_value, isdefault from v$parameter where name like 'sql_hist%';

NAME                 VALUE      DEFAULT_VALUE             ISDEFAULT
-------------------- ---------- ------------------------- ----------
sql_history_enabled  FALSE      FALSE                     TRUE

As user with ALTER SYSTEM privilege connect to pdb DB23_PDB1 and change the parameter. 

Note: You need to change the parameter with on the SYSTEM level in your PDB otherwise it will not work.

Here is an example in PDB DB23_PDB1.

SQL> alter system set sql_history_enabled=true scope=both;
System altered.

Now a database user (not sys) can connect to a PDB and run a SQL database application and monitor the statements in V$SQL_MONITOR.   

SQL> connect scott/yyyy@DB23
Connected.

-- check the parameter
SQL> sho parameter sql_history_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql_history_enabled                  boolean     TRUE

Now let’s try out the feature with two simple statements – the second one has an error.
Note: As you may know in Oracle Database 23 the FROM clause is not required anymore. Therefore the first query is correct 😉

SQL> select sysdate;
SYSDATE
---------
08-DEC-23
1 row selected.

SQL> select xxx from dual;
select xxx from dual
 *
ERROR at line 1:
ORA-00904: "XXX": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/

Now we can query V$SQL_MONITOR and list the statement history e.g. the columns with SQL text and error number. 

SQL> select sql_text, error_number from v$sql_history;

SQL_TEXT
--------------------------------------------------------------------------------
ERROR_NUMBER
------------
SELECT DECODE(USER, 'XS$NULL',  XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) F
ROM SYS.DUAL
           0
select sysdate
           0
select xxx from dual
         904

To illustrate the functionality with a more extensive example, I connect to another database user who runs a sample SQL application.

SQL> connect dwh_data/xxxx@DB23 
SQL> start TEST_SQLS.sql

....

Please keep in mind SQL history works on a best effort, depending on memory capacity.  In my case around 60 statements were collected after script completion in V$SQL_HISTORY. So we can list them as long as we are connected in the session. If you want to keep the list of statements for further investigations you may create a persistent table for it.

Let’s find out which columns are available in V$SQL_HISTORY beyond the columns SQL_TEXT and ERROR_NUMBER.

SQL> desc v$sql_history
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 KEY                                                NUMBER
 SQL_ID                                             VARCHAR2(13)
 ELAPSED_TIME                                       NUMBER
 CPU_TIME                                           NUMBER
 BUFFER_GETS                                        NUMBER
 IO_INTERCONNECT_BYTES                              NUMBER
 PHYSICAL_READ_REQUESTS                             NUMBER
 PHYSICAL_READ_BYTES                                NUMBER
 PHYSICAL_WRITE_REQUESTS                            NUMBER
 PHYSICAL_WRITE_BYTES                               NUMBER
 PLSQL_EXEC_TIME                                    NUMBER
 JAVA_EXEC_TIME                                     NUMBER
 CLUSTER_WAIT_TIME                                  NUMBER
 CONCURRENCY_WAIT_TIME                              NUMBER
 APPLICATION_WAIT_TIME                              NUMBER
 USER_IO_WAIT_TIME                                  NUMBER
 IO_CELL_UNCOMPRESSED_BYTES                         NUMBER
 IO_CELL_OFFLOAD_ELIGIBLE_BYTES                     NUMBER
 SQL_TEXT                                           VARCHAR2(100)
 PLAN_HASH_VALUE                                    NUMBER
 SQL_EXEC_ID                                        NUMBER
 SQL_EXEC_START                                     DATE
 LAST_ACTIVE_TIME                                   DATE
 SESSION_USER#                                      NUMBER
 CURRENT_USER#                                      NUMBER
 CHILD_NUMBER                                       NUMBER
 SID                                                NUMBER
 SESSION_SERIAL#                                    NUMBER
 MODULE_HASH                                        NUMBER
 ACTION_HASH                                        NUMBER
 SERVICE_HASH                                       NUMBER
 IS_FULL_SQLTEXT                                    VARCHAR2(1)
 ERROR_SIGNALLED                                    VARCHAR2(1)
 ERROR_NUMBER                                       NUMBER
 ERROR_FACILITY                                     VARCHAR2(4)
 STATEMENT_TYPE                                     VARCHAR2(5)
 IS_PARALLEL                                        VARCHAR2(1)
 CON_ID                                             NUMBER

In my example I am interested in some execution statistics such as CPU time or ELAPSED time or if the statements run in parallel.

SQL> select sql_text, elapsed_time, cpu_time, buffer_gets, is_parallel from v$sql_history;

SQL_TEXT
--------------------------------------------------------------------------------
ELAPSED_TIME   CPU_TIME BUFFER_GETS I
------------ ---------- ----------- -
SELECT
    'B' || tt1.PG_FeatureValue_07_1 PG_FeatureValue_07_1,
    'B' || 1010415 repchannel_1,
       35          0           0 N
SELECT
    'B' || tt1.PG_FeatureValue_07_1 PG_FeatureValue_07_1,
    'B' || 1010415 repchannel_1,
  1229896    1229644      148181 N
...

Please note: The SQL text in column SQL_TEXT displays only up to the first 100 characters of the text of the SQL statement.

SQL_HISTORY_ENABLED is PDB modifiable. You may change the parameter back to FALSE after saving the SQL statements in a helper table. So you can start a new scenario with an empty V$SQL_HISTORY


Try it out yourself it’s easy to use and quite convenient to use with short ad-hoc tests when you want to find out more about SQL statements and its characteristics in your own session. It’s available in Oracle Database 23ai free or also in Oracle Cloud.

Further readings