36 lines
1.2 KiB
MySQL
36 lines
1.2 KiB
MySQL
|
/*
|
||
|
|
*
|
||
|
|
* Author : Vishal Gupta
|
||
|
|
* Purpose : Display SQL Profile hints
|
||
|
|
* Parameters : 1 - SQL Profile Name
|
||
|
|
*
|
||
|
|
*
|
||
|
|
* Revision History:
|
||
|
|
* ===================
|
||
|
|
* Date Author Description
|
||
|
|
* --------- ------------ -----------------------------------------
|
||
|
|
* 23-Mar-15 Vishal Gupta Created
|
||
|
|
*
|
||
|
|
*/
|
||
|
|
|
||
|
|
|
||
|
|
UNDEFINE SQLPROFILENAME
|
||
|
|
DEFINE SQLPROFILENAME="&&1"
|
||
|
|
|
||
|
|
COLUMN outline_hints HEADING "SQL Profile Hints" FORMAT a150
|
||
|
|
|
||
|
|
|
||
|
|
SELECT extractvalue(value(d), '/hint') AS outline_hints
|
||
|
|
FROM xmltable('/outline_data/hint' passing
|
||
|
|
(SELECT xmltype(sd.comp_data) as xmlval
|
||
|
|
FROM dba_sql_profiles sp
|
||
|
|
JOIN sys.sqlobj$ so ON so.signature = sp.signature AND so.category = sp.category AND so.obj_type = 1 /* 1 = SQLProfile, 2=SQL Plan */
|
||
|
|
JOIN sys.sqlobj$data sd ON sd.signature = so.signature AND sd.category = so.category AND sd.obj_type = 1 /* 1 = SQLProfile, 2=SQL Plan */
|
||
|
|
--JOIN sys.sqlobj$auxdata ad ON ad.signature = so.signature AND ad.category = so.category AND ad.obj_type = 1 /* 1 = SQLProfile, 2=SQL Plan */
|
||
|
|
WHERE 1=1
|
||
|
|
AND sp.name = '&&SQLPROFILENAME'
|
||
|
|
)
|
||
|
|
) d
|
||
|
|
;
|
||
|
|
|