Re: display_cursor with PLAN_HASH_VALUE

Easiest would be to just save this query as a script and call it with sql_id and PHV when necessary
Select t.*
From v$sql s
,table(dbms_xplan.display_cursor(sql_id=> s.sql_id, cursor_child_no=>s.child_number,format=>'typical allstats')) t Where s.sql_id = '&1.'
And s.plan_hash_value = &2.
/

On Mon, 3 Feb 2020 at 20:18, Mauro Pagano <mauro.pagano_at_gmail.com> wrote:

> DBMS_XPLAN.DISPLAY takes a table and filters, you can
> pass gv$sql_plan_statistics_all and your filter predicates, e.g. *inst_id=1
> AND sql_id='xxxxx' AND plan_hash_value=4325435454 AND child_number=0*
> SQLd360 does it that way (here
> <https://github.com/sqldb360/sqldb360/blob/unstable/sql/sqld360_2f_plans_analysis.sql> ~line#
> 320), IIRC SQLT does the same
>
> On Mon, Feb 3, 2020 at 10:45 AM kyle Hailey <kylelf_at_gmail.com> wrote:
>
>>
>> Wondering if there is a way to use display_cursor or some similar
>> procedure with PLAN_HASH_VALUE instead of SQL_ID.
>> The procedure display_cursor takes the SQL_ID and CHILD_NUMBER as
>> arguments, but what if I just had SQL_ID and PLAN_HASH_VALUE?
>> I guess I could query the PLAN views directly or look up the child # from
>> the PLAN_HASH_VALUE.
>> But just wondering if there is already a procedure where I could just
>> give the PLAN_HASH_VALUE directly and get output like display_cursor.
>>
>> Kyle
>>
>>
>>

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