HINTs from V$SQL_PLAN

In this post I want to understand how to extract the hint, querying the V$SQL_PLAN.

The select I use, have to return just one row, otherwise

If it return more than one row, then you have the following exception

ORA-01427: single-row subquery returns more than one row

If it return NULL, then you have the following exception

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "SYS.XMLTYPE", line 272

ORA-06512: at line 1

Investigating SQL_ID= '93hwgvnjag3sz', I have more than 1 row in V$SQL_PLAN

select SQL_ID,CHILD_NUMBER,OTHER_XML 
from v$sql_plan 
where sql_id ='93hwgvnjag3sz';

For this reason, I have to modify the statement as

select other_xml

from gv$sql_plan

where sql_id       = '93hwgvnjag3sz'

  and child_number = 0

  and other_xml    is not null;

In order to understand how this xml is composed, I manually format the output

So I have two sections: "other_xml" and "outline_data". Because I want to get just the hints, I rewrite the query as

select xmltype(other_xml).extract('/other_xml/outline_data')

from v$sql_plan

where sql_id       = '93hwgvnjag3sz'

  and child_number = 0

  and other_xml    is not null;

and if I reformat the output ......

It's not enough, I have to go down another level. To do this I rewrite the query

select xmltype(other_xml).extract('/other_xml/outline_data/hint')

from v$sql_plan

where sql_id       = '93hwgvnjag3sz'

  and child_number = 0

  and other_xml    is not null;

Because I want to see the output in better way, I use XMLTABLE function

select *

from xmltable ('/*' passing (

                             select xmltype(other_xml).extract('/other_xml/outline_data/hint')

                             from v$sql_plan

                             where sql_id       = '93hwgvnjag3sz'

                               and child_number = 0

                               and other_xml    is not null)

);

XMLTABLE function, change the output of xml in an "xml table" (I don't know if it's correct) and the output is well formatted. In order to simplify the statement, I move the logic into outer select.

select *

from xmltable ('/other_xml/outline_data/hint' passing (

                                              select xmltype(other_xml)

                                              from v$sql_plan

                                              where sql_id     = '93hwgvnjag3sz'

                                              and child_number = 0

                                              and other_xml    is not null)

);

(obviously, the output is the same). At this point, I extract the hints from the XML

select extractvalue(value(xmltab), '.') hints

  from xmltable ('/other_xml/outline_data/hint' passing (

                                                select xmltype(other_xml)

                                                from v$sql_plan

                                                where sql_id     = '93hwgvnjag3sz'

                                                and child_number = 0

                                                and other_xml    is not null)

) xmltab;

Cool!!!