Translation

The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

giovedì, marzo 02, 2017

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!!!

Nessun commento: