Re: SQL_FULLTEXT

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_at_gmail.com> 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_at_gmail.com>
> 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_at_gmail.com>
>> 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
>>>
>>

--
http://www.freelists.org/webpage/oracle-l