Will this replace sql_trace one day ?
Buffers?
Image: (copied from some "History" page...) Keeping History is Important.
Background: What SQL you did spent your time on...?
After tackling most of the latency-problems, the "suspicion of slow SQL" came back. Not in the last place, because most python programs started reporting their "App-time", "Network-time" and "DB-time" components. So now we need to find the slow-SQL again...
From AWR and ASH, we have a fair idea what the most over-all time-consuming SQL stmnts are. And they are "known good": they need to be running, and they have little room left for improvement.
But what else is there? Which stmnts remain "under the radar". Which New stmnts needed attention, and which ones might be performing erratic.
Hence the wish: Let Each program report on "What it Did in the Database", and notably, what were the most time-consuming SQL-stmts.
This is what v$sql_history looked Ideal for...
Earlier attempts...soso.
In a previous blog (link), I described how I hit the limits of sql_history: only on 23ai, and only when the parameter was set. I made a feeble attempt (used an old trick), to report on SQL done by specific programs or modules, based on "module" in the session-properties. This worked to a point, but it wasnt what I wanted. Not after I had seen the possibilities of v$sql_history.
additional note + disclaimer: I've tweaked a lot of Oracle databases and I have also recently experimented with the active_session_history that was built into Yugabyte, and did some experimenting with a the feature. I guess I am in hobby-obsession-territory here (link to sibling blog).
Oracle wise, I would expect a (hidden) parameter to overcome that limit of 60? But searching the view x$ksppi didnt seem to give me any candidates, so I started asking around...
Friends (from oracle) to the rescue...
Martin Berger, from Oracle RWP-Group, pointed me to his blog (link) : Yes, there was a hidden parameter to help increase the nr of stmnts kept in v$sql_history (and it turned out I had queried for hidden parameters against a v19 database... sigh)
And a week later, Connor also "answered the question" in his office-hours session of 24 Sept 2025. Thx Connor! (link).
Of course you need the, non-default, enabler:
Alter system set sql_history_enabled=True;
And then this (hidden)parameter to allow more history than the dflt 50:
Alter system set "_sql_history_buffers"=10000 ;
Setting those parameters had my problem fixed, to some point. Development happens mostly on laptops, the Dev's very own machines, using the 23ai containers from Gerald Venzl (link). Hence, I could have the feature enabled and buffers set for developers fairly easily (those containers have a very easy provision to allow scripts at db-create-time and/or at db-startup time. Great tools for simple-devs!).
The (python) code.
Firstly, I experimented with SQL*plus, and the result was the script mysqlhist.sql (link below). I'll probably improve that over time as it gets used. Notice that I report "aggregated" values per SQL. In some cases you might want to report the complete history, all statements in order of execution. That list can get Very Long Very Quicly... I was more interested in which of the statements took most time overall, e.g. accumulated.
But my original search into history was for the python programs...
A function got includee into my generic py-file with oracle-stuff, ora_login.py. This import-able file begun life with the login-function, and gradually grew to contain my oracle-toolkit. Link below.
I included the checks on version and parameters, just in case, so when someone accidentally runs this code against the wrong database, nothing bad happens:
Notice: this test was against v19, and it only produces the friendly 3-line message. The same message will appear if history-enabled is not set.
But when a call to the function ora_sess_hist ( ora_conn) is done at the end of a python program that is connected to an "enabled" database, it will use the ora_conn to query v$sql_history. And output will looks like this:
Notice how, at the end of the run, this program called two functions: first to report history and do the listing x lines of SQL (limited to 100, to keep it readable). We notice 8 different SQLs, one of which was executed 1000 times totalling some 236495 microsec. But the "biggest SQL" in this run was the one that was executed 5 times, totalling 5.6 seconds.
After listing the history, this program also calls the "ora time spent" function to report how much time it has spent in the various components: in App, in DB, and (a guesstimate) in Network doing RoundTrips. You can combine this time-reporting with the linux time command to double-check (see previous blogs as well).
Notice that the DB-time, listed as 6.612 sec, and it corresponds roughly with the total of the sum of the elapsed time values (which are in microseconds!). Consider this a useful double-check.
This example of course contains just some bogus SQL for demo-reasons. The self-test of ora_login.py will yield similar limited results.
But please feel free to test this yourself and let me know.
What we dont quite know yet...
The main uncertainty is the behaviour of sessions with the new parameters. This is probably why the feature is default not enabled (yet), and why the value of "buffers" is (IMHO) rather low at 50 or 60.
On our current dev-container-version, v23.9.0.25.07, the default value for sql_history_enabled is False, and when enabled the dflt buffers seems set to 50. If this parameter ever hits "Production" with high values, what will be the effect on PGA and behaviour ? Where are the practical limits and what do they depend on....?
There are a few other anomalies to note, possibly to investigate and keep an eye on in future releases:
Buffers?
The actual nr of records in the view that came out was mostly just a few more than the setting of "buffers", say 8-10 more lines more. Not sure why/how.
(future test: various buffer-settings, and the actual impact on PGA with/out history)
Session-history private?
Sessions by the same user could see each others records. This may be why the SID is part of the view. Not sure if this is intentional or not. I'm curious to see how this feature will end up in Prod.
(future check: can system or sysdba also see all history from every session ? )
Double counting?
Rows 2 and 6 from sql_hist in the screen-print above: The pl/sql block from line 6 contains the create-stmnt from line2. I suspect there is a double count in there. Not a big problem, but I'd like to verify that some time. There is probably some way to avoid that using top_level_sql_id... (Later!)
Summary: Got it Working.
This now works for Dev, and can work in production if/when we every get this feature in a Prod database. We can call a python function to list the v$sql_history, or at least the last 10k statements from that program.
With some checks, that function-code can now also be run without causing errors in Prod, on v19 or other database, or on a system with the history parameters "disabled", as they are by default.
I would still be careful to use this feature "in anger" as the effect on PGA-memory is not really known or tested yet.
And if needed, we can always revert to searching statements from v$sqlarea, as demonstrated in previous blog (link).
In future, I might even enhance the python-function to save the history to a table before exiting the program. Since history is currently only taken in DEV, on machines and databases that are very volatile (many re-starts, re-creates...), saving that data is not practical or useful, yet.
Once history is also available in pre-prod and Production systems, I might want to persist history before exiting program. Until then, Prod will have to rely on AWR and ASH to find problematic SQL.
And I can see how this feature may replace sql_trace ?
Wishlist... Yes!
(Someone at Oracle is probably also thinking about this ...)
Session: I would like to be able to set the parameters on a Session basis and only consume resource for specific sessions that Need/Want to keep their History. A bit like sql-trace, but without the file-interface.
AWR/ASH: I expect the v$sql_history to be available in combination with AWR or ASH in the near future.
-- -- -- -- -- End of this blogpost, for now -- -- -- -- --
Appendix: links to source-files.
mysqlhist.sql : for use in SQL*Plus or SQLcl
tst_hist.py : short demo program.
ora_login.py : contains the actual funtion ora_sess_hist ( conn )
.env : used by dotenv, edit to add your own credentials.
Also needed are these python components:
pip oracledb : install the oracle driver
pip dotenv : install dotenv (used to store credentials)
Additionally: the grants...
To get to your v$ views, ... If you can get it past your DBA and your security-folks,... I can recommend these two:
grant ADVISOR to scott ;
grant SELECT ANY DICTIONARY to scott ;
Your account still wont be able to see data in other schemas, but you will see the v$stuff, all the relevant views..
-- -- -- -- -- End of this blogpost, For Real -- -- -- -- --
No comments:
Post a Comment