One new feature in 23c is the SQL History Monitoring and Reporting.
If enabled (SQL_HISTORY_ENABLED => TRUE), it provides information about the last 50 SQL statements of a session. This only includes top-level SQLs, no recursive or internal SQLs are recorded.
For these, tracing is a great feature to get them all!
According to v$system_parameter, SQL_HISTORY_ENABLED can be changed on session, system, pdb and instance level.
For me it seems, SYS statements are not monitored. If this is a feature or a bug I couldn't find in the documentation.
The history can be viewed in V$SQL_HISTORY. This view does not only contains the SQL_TEXT (first 100 characters) and SQL_ID, but also a lot of additional information. Beside some expected basics like CON_ID, SID, SESSION_SERIAL#, SQL_ID and PLAN_HASH_VALUE, there are also a lot of execution statistics (ELAPSED_TIME, CPU_TIME, BUFFER_GETS or PHYSICAL_READ_REQUESTS).
An important feature is the ability to log errors! Not only valid SQLs are recorded, but also failed ones - together with their ERROR_NUMBER. Some of my tests show:
select key, sql_id, sql_text, plan_hash_value, sql_exec_id, error_number, error_facility, statement_type
from v$sql_history
where (sql_text like '%1476!!%' or Sql_text like '%duhall%') and error_signalled='Y';
| KEY | SQL_ID | SQL_TEXT | PLAN_HASH_VALUE | SQL_EXEC_ID | ERROR_NUMBER | ERROR_FACILITY | STATEMENT_TYPE |
|---|---|---|---|---|---|---|---|
| 1017907380484 | 0cuvy329px4j8 | select 20;select /*1476!!*/ 1/0 | 0 | 0 | 923 | ORA | OTHER |
| 1017907380740 | 462sqgw42fjby | select /*1476!!*/ 1/0 | 1388734953 | 16777216 | 1476 | ORA | QUERY |
| 1017907380996 | 462sqgw42fjby | select /*1476!!*/ 1/0 | 0 | 0 | 1476 | ORA | QUERY |
| 1017907381252 | gwsvb6439kcxc | select * from duhall | 0 | 0 | 942 | ORA | QUERY |
If the error occurs during the SQLs execution, v$sql_history shows 2 lines - in my example for SQL_ID => 462sqgw42fjby.
v$sql_history can be queried by all users. but depending on the users permissions, different details are shown:
A DBA in the CDB can see all historic SQLs of all sessions.
A DBA in a PDB can only see all historic SQLs in its PDB.
A normal user in a PDB can only see all historic SQLs of all current sessions of this user (in the current PDB) - not only it's own session!
At disconnect, all historic SQLs related to this session are lost. (there is no view like DBA_HIST_SQL_HISTORY).
All those which want to see more than 50 SQLs per session, can alter the undocumented parameter _sql_history_buffers. In my case I need to set it on each PDB, it seems not to be inherited from the CDBs setting.
I did not find any parameter to change the 100 character limit for each SQL.
At last, v$sql_history comes with a companion: V$SQL_HISTORY_STATS. This view contains some information about the sql history buffer for each PDB:
Name Null? Type
------------------------ ----- -----------
TOTAL_BUFFERS NUMBER
ESTIMATED_BUFFERS NUMBER
TOTAL_SESSIONS NUMBER
USED_SESSIONS NUMBER
SQL_HISTORY_SGA_LIMIT_KB NUMBER
ENTRY_SIZE NUMBER
ENTRY_LIMIT_PARAM NUMBER
LIST_BYTES NUMBER
LIST_LENGTH NUMBER
LIST_COUNT NUMBER
SQLTEXT_LIMIT NUMBER
ENABLED VARCHAR2(1)
OVERFLOW VARCHAR2(1)
CON_ID NUMBER
v$sql_history is based on x$kesmasmon (kesm might be an abbreviation for kernel event SQL manageability)- in my sandbox, each row (in fact memory structure) has a size of 368 bytes - but not all structures are aligned to the same number, so I expect there are several independent pools.
V$SQL_HISTORY_STATS is based on x$kesmasmon_stat.
