Re: display_cursor with PLAN_HASH_VALUE

I like to get a "distinct" into the query against v$sql - and arrange the text slightly differently: https://jonathanlewis.wordpress.com/2011/05/11/dbms_xplan-reprise/

Regards
Jonathan Lewis


________________________________________
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Andy Sayer <andysayer@gmail.com>
Sent: 03 February 2020 20:26
To: Mauro Pagano
Cc: ORACLE-L; kyle Hailey
Subject: 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<mailto:mauro.pagano@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<mailto:kylelf@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


--