23c free - SQL History

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.