issue:
unable to create the sqlpatch in oracle database SAP system version 12.1
got the below error
08:11:38 SQL> SET SERVEROUTPUT ON;
DECLARE
v_patch_name VARCHAR2(300);
BEGIN
v_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
sql_id => '5rt7rtf7gzx85',
hint_text => 'INDEX(@"SEL$1" "TFYE"@"SEL$1" "TFYE~0")',
name => '5rt7rtf7gzx85/I346808/SQL_Opt_TFYE',
description => 'INDEX(@"SEL$1" "TFYE"@"SEL$1" "TFYE~0")'
);
DBMS_OUTPUT.PUT_LINE('SQL Patch created: ' || v_patch_name);
END;
/
got the below error:
*
ERROR at line 4: v_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
ORA-06550: line 4, column 34:
PLS-00302: component 'CREATE_SQL_PATCH' must be declared
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored
Root cause:
PLS-00306 means the procedure exists, but the parameter list you're using does not match the version of I_CREATE_PATCH available in your Oracle release.
Solution:
In many Oracle 11g/SAP systems, I_CREATE_PATCH expects the SQL text instead of a SQL_ID, for example:
DECLARE
l_sql CLOB;
BEGIN
SELECT sql_fulltext
INTO l_sql
FROM v$sqlarea
WHERE sql_id = '5rt7rtf7gzx85';
SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(
sql_text => l_sql,
hint_text => 'INDEX(@"SEL$1" "TFYE"@"SEL$1" "TFYE~0")',
name => 'SQL_OPT_TFYE',
description => 'Force TFYE index'
);
END;
/
finally SQLpatch got created using the above script which pushed the sql text for the SQL and then created it.
Note
-----
- SQL patch "SQL_OPT_TFYE" used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Optimizer picked up this sql patch and performed the query with 10x faster.
