48 lines
1.8 KiB
SQL
48 lines
1.8 KiB
SQL
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
|
|
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
|
|
|
|
-- you can query patch info from DBA_SQL_PATCHES
|
|
-- the actual hints can be seen using:
|
|
-- select comp_data from sqlobj$data;
|
|
--
|
|
-- a sql patch can be dropped with:
|
|
-- dbms_sqldiag.drop_sql_patch('&patch_name');
|
|
--
|
|
-- Note that it's best to use manual hinting first and if the hint works, extract it in its full
|
|
-- format from the OUTLINE section of the plan. for example, I had to use this format:
|
|
--
|
|
-- FULL(@"SEL$1" "T"@"SEL$1")
|
|
--
|
|
-- ... instead of just FULL(t) to make the patch work (12.1)
|
|
--
|
|
-- The DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH actually requires the SQL Text (as a CLOB) instead of just
|
|
-- the SQL_ID, in 12.1 and earlier this script fetches the SQL Text from v$sql (the cursor needs
|
|
-- to be in library cache). Similarly the DBMS_SQLDIAG.CREATE_PATCH(sql_id=>...) needs the cursor to be
|
|
-- in library cache in order to find the corresponding SQL text.
|
|
|
|
SET SERVEROUT ON SIZE 1000000
|
|
|
|
DECLARE
|
|
v_sql_text CLOB;
|
|
ret VARCHAR2(100);
|
|
BEGIN
|
|
-- rownum = 1 because there may be multiple children with this SQL_ID
|
|
DBMS_OUTPUT.PUT_LINE(q'[Looking up SQL_ID &1]');
|
|
SELECT sql_fulltext INTO v_sql_text FROM v$sql WHERE sql_id = '&1' AND rownum = 1;
|
|
DBMS_OUTPUT.PUT_LINE('Found: '||SUBSTR(v_sql_text,1,80)||'...');
|
|
|
|
-- TODO: should use PL/SQL conditional compilation here
|
|
-- The leading space in hint_text is intentional.
|
|
|
|
-- 12.2+
|
|
ret := DBMS_SQLDIAG.CREATE_SQL_PATCH(sql_id=>'&1', hint_text=>q'[ &2]', name=>'SQL_PATCH_&1');
|
|
|
|
-- 11g and 12.1
|
|
--DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(sql_text=>v_sql_text, hint_text=>q'[ &2]', name=>'SQL_PATCH_&1');
|
|
DBMS_OUTPUT.PUT_LINE(q'[SQL Patch Name = SQL_PATCH_&1]');
|
|
END;
|
|
/
|
|
|
|
SET SERVEROUT OFF
|
|
|