When we implement the SQL Patch in Oracle for the statement, we would also like to confirm if the SQL has started using the SQL Patch or not. We can use the below query to find out if the sql is using the SQL Patch
How to find if the sql is using the SQL Patch
col sql_patch for a30 col sql_text for a60 wrap col child_number format 99999 heading CHILD col avg_etime format 9,999,999.99 col avg_lio format 999,999,99 col etime format 9,999,999.99 select sql_id, child_number, plan_hash_value plan_hash, sql_patch, executions execs, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text from v$sql s where sql_id like '&sql_id' order by 1, 2, 3 /
You may want to use the Gv$ view if you are working on an Oracle RAC Database
col sql_patch for a30 col sql_text for a60 wrap col child_number format 99999 heading CHILD col avg_etime format 9,999,999.99 col avg_lio format 999,999,99 col etime format 9,999,999.99 select inst_id, sql_id, child_number, plan_hash_value plan_hash, sql_patch, executions execs, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text from gv$sql s where sql_id like '&sql_id' order by 1, 2, 3 /
- The above queries will give results only if the sql_id is in the cursor cache. It will not provide any results if it is not present.
- In case the SQL_ID is not in the cursor cache, we can use the DBA_HIST_SQLSTAT view to find out the answer
Query to show the most recent calls of a prompted SQL_ID with a SQL Patch:
select * from DBA_HIST_SQLSTAT where sql_id='&&sql_id_val' and sql_patch is not null order by snap_id desc;
Query to show the most recent calls of a prompted SQL_ID without a SQL Patch
select * from DBA_HIST_SQLSTAT where sql_id='&&sql_id_val' and sql_patch is null order by snap_id desc;
Find using DBMS PLAN
select * from table(dbms_xplan.display_cursor('&SQL_ID'));
This will display the SQL patch being used at the bottom.
- SQL patch "SQL_Patch_9.2.0" used for this statement
It is important to know if the SQL Patch implemented is being used or not. I hope this article on how to find if the sql is using the SQL Patch helps you in the tuning effort.
Using Explain Plan
we can do the explain plan for the sql statement and it will show the SQL Patch being used
SQL>explain plan for " <Full SQL Statement">;
SQL> set linesize 132
SQL> SELECT * FROM TABLE(dbms_xplan.display);
Related Articles
Sql plan Management in Oracle: check out this post on Sql plan Management in Oracle, how it works out, what are the benefits, and how it helps in performance tuning
Autotrace in oracle
Histograms in Oracle: check out Histograms in EBS, How to check the columns for histograms, How to load new columns for histograms