39 lines
995 B
MySQL
39 lines
995 B
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Hints in a SQL Statement
|
|
* Parameters : 1 - SQLID
|
|
* 2 - Child Number
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 01-May-12 Vishal Gupta First Draft
|
|
*
|
|
*/
|
|
|
|
|
|
set pages 50
|
|
|
|
DEFINE sql_id="&&1"
|
|
DEFINE child_no="&&2"
|
|
|
|
|
|
SELECT extractvalue(value(d), '/hint') AS outline_hints
|
|
FROM xmltable('/*/outline_data/hint'
|
|
passing ( SELECT xmltype(other_xml) AS xmlval
|
|
FROM gv$sql_plan
|
|
WHERE sql_id LIKE NVL('&sql_id',sql_id)
|
|
AND child_number = &child_no
|
|
AND other_xml IS NOT NULL
|
|
AND rownum = 1
|
|
)
|
|
) d
|
|
;
|
|
|
|
UNDEFINE sql_id
|
|
UNDEFINE child_no
|
|
|
|
@@footer |