Re: SQL_FULLTEXT

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