Introduction
For me, tracking details of queries, how they change over time, how they ran previously and using the compare and contrast method of tuning is crucial to really get to the bottom of those problem transactions, applications and queries. As such, the following 23c new feature is a welcome addition to the armory.
The feature is known as Enhanced Query History Tracking and Reporting.
In a nutshell, it allows you to monitor and report all user queries in more detail previous Oracle versions.
For example, you can now track nearly all actions a users makes during a session, even those lightning-fast queries that run in under five seconds, which were not tracked by real-time SQL monitor without a hint. You can also view a report into your own session or if you have elevated privileges, reports for all users.
Anything that moves a database further in this direction is a win-win for DBAs. So much information can be gathered about an application, just by looking at the SQL it runs.
I sometimes refer to SQL as the shadow an application leaves of its function and behavior. Ignore this feature at your peril
How to Enabled Enhanced Query History Tracking and Reporting
As with most of these types of features, you need switch them on with an initialization parameter. More often than not, the default setting when they are first introduced is disabled.
- SQL_HISTORY_ENABLED = {[TRUE][FALSE]}
No need to bounce your database. This is a dynamic parameter. Should there be any other types these days? <-—— Oracle Devs, please take note :)
Using Enhanced Query History Tracking and Reporting
So having enabled the feature, how do you make use of it.
The package responsible for its use is our old friend - DBMS_SQLTUNE
There are two new sub-programs available as listed below
- DBMS_SQLTUNE.REPORT_SQL_HISTORY_LIST
- DBMS_SQLTUNE.REPORT_SQL_HISTORY
Its probably worth noting, that at the time of writing this, the sub-programs table in the online documentation is missing the actual sub-programs listed above. See here DBMS_SQLTUNE Sub-program Table No doubt this will be fixed in the future.
Luckily for us however, the documentation writers have not forgotten to include the sub-program documentation itself. Just scroll down the page a little and you will find the details
here
A few examples would have been useful two, although the sub-programs are quite simple and self explanatory.
Published 1st December 2023
