Here are some of the Top Useful Queries for SQL Patch in Oracle Database
Query to find the hints associated with a SQL Patch
select cast(extractvalue(value(x), '/hint') as varchar2(500)) as outline_hints
from xmltable('/outline_data/hint'
passing (select xmltype(comp_data) xml
from sys.sqlobj$data
where signature = (select EXACT_MATCHING_SIGNATURE from v$sql where sql_id = '&SQL_ID'))) x;
How to find all the SQL_PATCH in the Database
select * from DBA_SQL_PATCHES ;
DBA_SQL_PATCHES Contains below information
How to check the status of SQL Patch
SELECT name, status FROM dba_sql_patches WHERE name = '&sql_patch';
How to generate a sql patch tuning task
Just like we can find the sql profile using sql profile tuning task, we can find the appropriate sql patch using sql patch tuning task
SET ECHO ON SET LINESIZE 132 SET PAGESIZE 999 SET LONG 999999 SET SERVEROUTPUT ON DECLARE v_sql_diag_task_id varchar2(100); BEGIN v_sql_diag_task_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK ( sql_id=>'&sql_id', problem_type => DBMS_SQLDIAG.PROBLEM_TYPE_PERFORMANCE, time_limit => 900, task_name => 'PROBLEM_TYPE_PERFORMANCE_task_&sql_id' ); DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(v_sql_diag_task_id,'_SQLDIAG_FINDING_MODE',DBMS_SQLDIAG.SQLDIAG_FINDINGS_FILTER_PLANS); END; /
How to run the sql patch tuning task
BEGIN
DBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (
task_name => ‘PROBLEM_TYPE_PERFORMANCE_task_&sql_id’ );
END;
/
How to generate sql patch tuning report
SET LONG 9999999
SET PAGESIZE 500
SELECT DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK ('PROBLEM_TYPE_PERFORMANCE_task_&sql_id' ) as recommendations FROM DUAL;
How to accept the recommendation
BEGIN
DBMS_SQLDIAG.ACCEPT_SQL_PATCH(
task_name =>'PROBLEM_TYPE_PERFORMANCE_task_&sql_id',
replace => TRUE);
END;
/
How to disable the SQL Patch
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH(name=>'SQL_Patch_9.2.0', attribute_name=>'STATUS', attribute_value=>'DISABLED');
How to enable the SQL Patch
EXEC DBMS_SQLDIAG.ALTER_SQL_PATCH(name=>'SQL_Patch_9.2.0', attribute_name=>'STATUS', attribute_value=>'ENABLE');
How to drop the SQL Patch
EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name=>'SQL_Patch_9.2.0');
Related Articles
Queries on SQLPLAN Management in Oracle: check out the most used Queries on SQLPLAN Management in Oracle. This should be in your performance tuning tools for the Oracle database
Histograms queries in Oracle
how to check sql profile in oracle