Re: SQL_FULLTEXT

Now I have to review SQL Costs | Oracle Scratchpad (wordpress.com) <https://jonathanlewis.wordpress.com/2007/04/10/sql-costs/>

There have been a lot of changes to the v$sqlarea, v$sqltext, v$sqltext_plan_hash (including the arrival of v$sqlarea_plan_hash) since 2007.
Among other details - why is the plan_hash_value in the views without "_plan_hash" in their names - is just reporting the "last execution" plan_hash_value, or the xxx and xxx_plan_hash views now (nearly) the same thing.

Regards
Jonathan Lewis

On Sat, 13 May 2023 at 05:10, Nenad Noveljic <nenad.noveljic_at_gmail.com> wrote:

> Sometimes it is for a single known SQL_ID, sometimes it’s a join with X$
> structures underpinning V$SESSION on SQL_ID and PLAN_HASH_VALUE. On dbs
> like RDS w/o access to X$ we need to join with V$SESSION which doesn‘t
> expose the PLAN_HASH_VALUE.
>
> Best regards,
> Nenad
>
> Von meinem iPhone gesendet
>
> Am 13.05.2023 um 05:06 schrieb Tanel Poder <tanel_at_tanelpoder.com>:
>
> 
> Do you need the SQL full text for a single known SQL_ID? Youl could just
> query V$SQL.SQL_FULLTEXT WHERE rownum = 1.
>
> Note that page 20 of the 10gR2 era "Understanding Shared Pool Memory
> Structures" whitepaper mentions improvements to V$OPEN_CURSOR and V$SQLAREA.
>
>
> https://www.oracle.com/technetwork/database/manageability/ps-s003-274003-106-1-fin-v2-128827.pdf
>
> --
> Tanel Poder
> https://learn.tanelpoder.com
>
>
> On Fri, May 12, 2023 at 3:43 AM 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