2023-04-08

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.


2 Kommentare:

Geert De Paep hat gesagt…

Very nice feature, however:
- I hope they won't put it behind a licensed pack like diagnostic or tuning pack in the production release of 23c.
- How sad, a missed opportunity by Oracle! "All sql lost on disconnect". If you need to trace or troubleshoot something, very often the session disconnects at the end. You cannot control this, it is the application that decides. This makes this great feature useless in many situations. I would really like Oracle to think more from practical point of view and discuss these features with people really working in the field.

Anonym hat gesagt…

If you enable SQL history for SYS , it would end up in a loop recording the last insert into the history…