Without doing a bit of research to confirm the details I'd guess that the
manual is an example of statements which have not been corrected after an
enhancement to the software. Some time in 10g (I think) v$sqlarea changed
from an aggregate view of v$sql to an independent representation of an x$
structure, and I suspect the manual wasn't updated to reflect this change.
So my opinion would be that v$sqlarea is probably no worse than v$sqlstats
for what you want to do. Off the top of my head, the SQL stats and the
cursor details are (usually) on two separate hash chains; and the
sql_fulltext is referenced by a pointer on the (parent) cursor, so both
views will probably be chasing the same set of pointers to get the result
you're after.
Bottom line, though, the SQL_ID (or hash_value) is the only way to query
the library cache efficiently; any other entry method will do a "full
tablescan" of the library cache - and I've found in the past that even a
simple join on sql_id or hash_value (e.g. from v$session) will make that
happen; so whatever else you do you need to check the execution path of any
query you run to see what Oracle thinks it's doing. (You could also play
games on a sandbox to see if you can spot any difference in library cache
latching accessing different v$ views in (apparently) the same way.)
Regards
Jonathan Lewis
On Fri, 12 May 2023 at 09:50, Nenad Noveljic <nenad.noveljic@xxxxxxxxx>
wrote:
I'm referring to Connor's article
https://connor-mcdonald.com/2019/03/04/less-slamming-vsql/ where he
recommends using v$sqlstats instead of v$sql not only because of
performance, but also to avoid causing contention on the shared pool. This
article quotes the Oracle documentation which qualifies v$sqlstats as "more
scalable" than v$sqlarea, though, without providing further details.
My main concern is not causing contention on the shared pool, as queries
will be running periodically.
Does querying v$sqlarea have the same effect as querying v$sql in terms of
causing shared pool contention as of Oracle version 19c?
Best regards,
Nenad
On Fri, May 12, 2023 at 10:27 AM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
Do you have any clues that v$sqlarea is less efficient than v$sqlstats.
It clearly used to be several versions ago because it's execution indicated
aggregation of v$sql, but of many years it has been an "indexed" access
path to a single row in a separate x$ structure.
In both cases (v$sqlstats and v$sqlarea) the view content is dynamically
constructed in the user memory, so they are both doing something to track
through components that make up x$kglob, so one would hope that they can be
as efficient as each other. If there is a distinct difference in
performance then you could consider querying x$kglob directly.
(You can only get the first 32765 characters if it's a really long
statement).
Regards
Jonathan Lewis
On Fri, 12 May 2023 at 08:43, Nenad Noveljic <nenad.noveljic@xxxxxxxxx>
wrote:
What's the least disruptive view to query to obtain the full SQL text?
It should be V$SQLSTATS, but unfortunately V$SQLSTATS.SQL_FULLTEXT
doesn't contain the full text. The support note "V$SQLSTATS.SQL_FULLTEXT
does not show full of sql statements (Doc ID 2398100.1)" advises querying
V$SQLAREA instead of V$SQLSTATS.
Best regards,
Nenad
