Oracle version count info metalink 438755.1

High SQL Version Counts - Script to determine reason(s) (Doc ID 438755.1)



create or replace view SQL_SHARED_CURSOR
as select * from sys.v$sql_shared_cursor;


create or replace view h$pseudo_cursor as
select Pseudo_cursor, sql_id,obj_id hex_obj_id
     ,obj# object_id, u.name owner, o.name object_name
     ,address,hash_value,SHARABLE_MEM,parse_calls,VERSION_COUNT,is_obsolete
from (select distinct 
             KGLNAOBJ Pseudo_cursor,kglobt03 sql_id
        ,KGLHDPAR address,KGLNAHSH hash_value
        ,KGLOBHS0+KGLOBHS1+KGLOBHS2+KGLOBHS3+KGLOBHS4+KGLOBHS5+KGLOBHS6 SHARABLE_MEM
        ,KGLOBT12 parse_calls
        ,KGLHDEXC executions
        ,KGLOBCCC VERSION_COUNT
        ,decode(kglobt33, 1, 'Y', 'N') is_obsolete
        ,substr(KGLNAOBJ
               ,instr(KGLNAOBJ,'_',1,3)+1
               ,instr(KGLNAOBJ,'_',1,4)-instr(KGLNAOBJ,'_',1,3)-1) obj_id 
       ,(case when 
         replace(translate(substr(upper(KGLNAOBJ)
                                 ,instr(KGLNAOBJ,'_',1,3)+1
                                 ,instr(KGLNAOBJ,'_',1,4)
                                  -instr(KGLNAOBJ,'_',1,3)-1)
                          ,'0123456789ABCDEF','................')
                ,'.') is null then 'Y' else 'N' end) is_safe_to_compare
            from x$kglob) k
   , obj$ o, user$ u
where obj#=decode(is_safe_to_compare,'Y',to_number(obj_id,'xxxxxxxxxx'),0)
   and o.owner#=u.user#;

Create or replace view H$PARAMETER
as
select a.ksppinm  NAME,
     a.ksppdesc DESCRIPTION,
     b.ksppstvl SESSION_VALUE,
     c.ksppstvl SYSTEM_VALUE
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx;
   

create or replace function debug_version_rpt return DBMS_DEBUG_VC2COLL PIPELINED is
v_status number;
v_info varchar2(32767);
begin
 loop
  v_status := dbms_pipe.receive_message('version_rpt',0);
  if v_status = 0 then
   dbms_pipe.unpack_message(v_info);
   pipe row (v_info);
  else
   return;
  end if;
 end loop ;
end;
/

create or replace function version_rpt(p_sql_id varchar2 default null,p_hash number default null,p_debug char default 'N') return DBMS_DEBUG_VC2COLL PIPELINED is
 type vc_arr is table of varchar2(32767) index by binary_integer;
 type num_arr is table of number index by binary_integer;

 v_version varchar2(100);
 v_instance varchar2(100);
 v_colname vc_arr;
 v_Ycnt num_arr;
 v_count number:=-1;
 v_no number;
 v_all_no number:=-1;

 v_query varchar2(4000);
 v_sql_where varchar2(4000):='';
 v_sql_where2 varchar2(4000):='';
 v_sql_id varchar2(15):=p_sql_id;
 v_addr varchar2(100);
 V_coladdr varchar2(100);
 v_hash number:=p_hash;
 v_mem number;
 v_parses number;
 v_execs number;
 v_value varchar2(100);

 theCursor number;
 columnValue char(1);
 status number;

 v_driver varchar2(1000);
 TYPE cursor_ref IS REF CURSOR;
 vc cursor_ref;

 v_bind_dumped boolean:=false;
 v_auth_dumped boolean:=false;

           v_phv num_arr;
          v_phvc num_arr;

procedure debugme(p_info varchar2) is
v_st number;
begin
 if p_debug='Y' then
  dbms_pipe.pack_message(p_info);
  v_st := dbms_pipe.send_message('version_rpt',5);
  if v_st=1 then dbms_pipe.purge('version_rpt'); end if;
 end if;
end;

BEGIN
 if p_debug='Y' then
  status:=DBMS_PIPE.CREATE_PIPE ( pipename=>'version_rpt',maxpipesize=>1024*1024);
  if status<>0 then pipe row ('Cannot debug'); return; end if;
 end if;
 debugme('instance version');

 select version,'Host: '||HOST_NAME||' Instance '||INSTANCE_NUMBER||' : '||INSTANCE_NAME
  into v_version , v_instance from v$instance;

 debugme('build v$sqlarea query for '||v_sql_id||' '||v_hash);

 /* 
    This aggregate query is in the cases where
    1) So many versions of the same SQL that many parents have been obsoleted.
    2) there are more than 1 SQL with the same hash value or sql_id (very rare)
 */

 v_query:='select '|| case when v_version  like '9%' then '(NULL)' else '(sql_id)' end ||'  sql_id,'
        ||  'max(sql_text) query,'
        ||  'max(hash_value) hash,'
        ||  'max(rawtohex(ADDRESS)) addr,'
        ||  'sum(SHARABLE_MEM) SHARABLE_MEM,'
        ||  'sum(PARSE_CALLS) PARSE_CALLS,'
        ||  'sum(EXECUTIONS) EXECUTIONS'
        ||  ' from v$sqlarea where'
        || case when v_sql_id is not null then ' sql_id=:v_sql_id' else ' hash_value=:v_hash' end
        || ' group by '|| (case when v_version like '9%' then 'NULL' else 'sql_id' end);

 debugme(v_query);
 if v_sql_id is not null then
  open vc for v_query using v_sql_id;
 else
  open vc for v_query using v_hash;
 end if;

 debugme('Successful open cursor');

 PIPE ROW('Note:438755.1 Version Count Report Version 3.2.5 -- Today''s Date '||to_char(sysdate,'dd-mon-yy hh24:mi')) ;
 PIPE ROW('RDBMS Version :'||v_version||' '||v_instance);

 
    debugme('fetch '||v_sql_id||' '||v_hash);
    fetch vc into v_sql_id, v_query,v_hash,v_addr,v_mem,v_parses,v_execs;
    if vc%notfound then
    /* This execption could mean 2 things
       1) The user gave a wrong SQLID
       2) The SQLID belongs to a pseudo cursor.
       
       if 2) then the info will not be in v$sqlarea so will try h$pseudo_cursor.
       I do not query h$pseudo_cursor from the start to avoid as much as possible to access x$ views directly
       due to their mutex and latch restrictions and to take advantage of any optimizations done in v$sqlarea.
    */   
     debugme('not found - alternate');
     v_query:= replace(v_query,'v$sqlarea','H$PSEUDO_CURSOR');
     v_query:= replace(v_query,'sql_text','Pseudo_cursor||''(PseudoCursor of ''||owner||''.''||object_name||'')''');
     debugme(v_query); 

     close vc;
     if v_sql_id is not null then
      open vc for v_query using v_sql_id;
     else
      open vc for v_query using v_hash;
     end if;
     fetch vc into v_sql_id, v_query,v_hash,v_addr,v_mem,v_parses,v_execs;
     if vc%notfound then
      return; /* Sorry, really is not in the library cache. */
     end if;
    end if;
    close vc;
    

    debugme('Header');
     v_colname.delete;
     v_Ycnt.delete;
     v_count:=-1;
     v_no:=0;
     v_all_no:=-1;

         PIPE ROW('==================================================================');
         PIPE ROW('Addr: '||v_addr||'  Hash_Value: '||v_hash||'  SQL_ID '||v_sql_id);
         PIPE ROW('Sharable_Mem: '||v_mem||' bytes   Parses: '||v_parses||'   Execs:'||v_execs);
         PIPE ROW('Stmt: ');

         for i in 0 .. trunc(length(v_query)/64)+1 loop
          debugme('Print query line '||i);
          PIPE ROW(i||' '||substr(v_query,1+i*64,64));
         end loop;

          debugme('Fetch SQL_SHARED_CURSOR columns');

          SELECT COLUMN_NAME,0 bulk collect into v_colname,v_Ycnt
           from cols
          where table_name='SQL_SHARED_CURSOR'
            and CHAR_LENGTH=1
         order by column_id;

         v_query:='';
         debugme('Build Select List');
         for i in 1 .. v_colname.count loop
          v_query:= v_query ||','|| v_colname(i);
         end loop;

         debugme('Build Where');

          if v_version like '9%' then
           v_sql_where:=' WHERE ADDRESS=HEXTORAW('''||V_ADDR||''')';
           v_sql_where2:=' WHERE KGLHDPAR=HEXTORAW('''||V_ADDR||''')';
          elsif v_sql_id is not null then
           v_sql_where:=' WHERE SQL_ID='''||v_sql_id||'''';
           v_sql_where2:=v_sql_where;
          else
           v_sql_where:=' WHERE ADDRESS=HEXTORAW('''||V_ADDR||''')';
           v_sql_where2:=v_sql_where;
          end if;
        
         debugme('Build Query');
         v_query:= 'SELECT '||substr(v_query,2) || ' FROM SQL_SHARED_CURSOR ';

         v_query:=v_query||v_sql_where2;
         
         debugme(substr(v_sql_where2,-80));

         debugme('Open Query');
         begin
          theCursor := dbms_sql.open_cursor;
          sys.dbms_sys_sql.parse_as_user(C=>theCursor,STATEMENT=> v_Query, LANGUAGE_FLAG=>dbms_sql.native ,USERID=>0);

          for i in 1 .. v_colname.count loop
           dbms_sql.define_column( theCursor, i, columnValue, 8000 );
          end loop;

          status := dbms_sql.execute(theCursor);

          debugme('Initiate Fetch');
          while (dbms_sql.fetch_rows(theCursor) >0) loop
           v_no:=0;
           v_count:=v_count+1;
           debugme('Fetch row '||v_count);
           for i in 1..v_colname.count loop
            dbms_sql.column_value(theCursor, i, columnValue);
--            debugme('Decode row '||v_count||' column '||i);
            if columnValue='Y' then
             v_Ycnt(i):=v_Ycnt(i)+1;
            else
             v_no:=v_no+1;
            end if;
           end loop;

           if v_no=v_colname.count then
            v_all_no:=v_all_no+1;
           end if;
          end loop;
          dbms_sql.close_cursor(theCursor);
         end;

         debugme('Version summary');
         PIPE ROW('');
         PIPE ROW('Versions Summary');
         PIPE ROW('----------------');
         for i in 1 .. v_colname.count loop
          if v_Ycnt(i)>0 then
           PIPE ROW(v_colname(i)||' :'||v_Ycnt(i));
          end if;
         end loop;
         If v_all_no>1 then
          PIPE ROW('Versions with ALL Columns as "N" :'||v_all_no);
         end if;
         PIPE ROW(' ');
         PIPE ROW('Total Versions:'||v_count);

         PIPE ROW(' ');
         PIPE ROW('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ');
         V_value:=NULL;
         v_query:='select max(SYSTEM_VALUE) into :v_value from h$parameter where name=''cursor_sharing'' ';
         execute immediate v_query into v_value;
         if v_value is not null then
          PIPE ROW('cursor_sharing = '||v_value);
         end if;
		 
          V_NO:=NULL;
          v_query:='select max(SYSTEM_VALUE) into :v_no from h$parameter where name=''_cursor_obsolete_threshold'' ';
          execute immediate v_query into v_no;
          if v_no is not null then
           PIPE ROW('_cursor_obsolete_threshold = '||v_no||' (See Note:10187168.8)');
          end if;

         PIPE ROW('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ');

         PIPE ROW(' ');

         v_phv.delete;
         v_phvc.delete;

         debugme('PHV');
         v_query:='select plan_hash_value,count(*) from v$sql '||v_sql_where||' group by plan_hash_value';

         execute immediate v_query bulk collect into  v_phv,v_phvc;

         PIPE ROW('Plan Hash Value Summary');
         PIPE ROW('-----------------------');
         PIPE ROW('Plan Hash Value Count');
         PIPE ROW('=============== =====');
         for i in 1 .. v_phv.count loop
          PIPE ROW(to_char(v_phv(i),'99999999999999')||' '||to_char(v_phvc(i)));
         end loop;
         PIPE ROW(' ');


  for i in 1 .. v_colname.count loop
   debugme('Diag for '||v_colname(i)||' Ycnt:'||v_Ycnt(i));
   if v_Ycnt(i)>0 then

    PIPE ROW('~~~~~~~~~~~~~~'||rpad('~',length(v_colname(i)),'~'));
    PIPE ROW('Details for '||v_colname(i)||' :');
    PIPE ROW('');
    if ( v_colname(i) like '%BIND%')
            or  (v_version like '11.1%' and v_colname(i)='ROW_LEVEL_SEC_MISMATCH') then
     if v_bind_dumped=true then -- Dump only once
      PIPE ROW('Details shown already.');
     else
      v_bind_dumped:=true;
      if v_version like '9%' then
       PIPE ROW('No details for '||v_version);
      else
       PIPE ROW('Consolidated details for BIND* columns:');
       PIPE ROW('BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF,BIND_LENGTH_UPGRADEABLE,etc and');
       PIPE ROW('BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)');
       PIPE ROW('');
       declare
        v_position num_arr;
        v_maxlen num_arr;
        v_minlen num_arr;
        v_dtype num_arr;
        v_prec num_arr;
        v_scale num_arr;
        v_n num_arr;
        v_bg char(3);
        v_bgflag char(1):='N';

       begin
        v_query:='select position,min(max_length),max(max_length),datatype,precision,scale,count(*) n'
               ||' from v$sql_bind_capture where sql_id=:v_sql_id'
               ||' group by sql_id,position,datatype,precision,scale'
               ||' order by sql_id,position,datatype,precision,scale';

        EXECUTE IMMEDIATE v_query
        bulk collect into v_position, v_minlen, v_maxlen , v_dtype ,v_prec ,v_scale , v_n
        using v_sql_id;

        PIPE ROW('from v$sql_bind_capture');
        PIPE ROW('COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)');
        PIPE ROW('======== ======== =============== =============== ======== =============== =================');
        for c in 1 .. v_position.count loop
         v_bg:=(case when v_maxlen(c)>v_minlen(c) then 'Yes' else 'No' end);
         v_bgflag:=(case when v_bg='Yes' and v_n(c)>99 then 'Y' end);
         PIPE ROW( to_char(v_n(c),'9999999')||' '||to_char(v_position(c),'9999999')||' '|| to_char(v_minlen(c),'99999999999999')
                  ||' '|| to_char(v_maxlen(c),'99999999999999')
                  ||' '|| to_char(v_dtype(c),'9999999')
                  ||'     '||v_bg
                  ||'          ('|| v_prec(c)||','||v_scale(c)||')'                 
                 );
        end loop;
        
        if ( v_bgflag='Y' ) then
	 PIPE ROW ('The above table shows binds with 100+ versions in the same position with different MAX_LENGTHs.');
	 PIPE ROW ('See "Add event to improve cursor sharability on BIND_LENGTH_UPGRADEABLE" ');
	end if;

        if v_version not like '10%' and v_version not like '9%' then
         v_query:='select sum(decode(IS_OBSOLETE,''Y'', 1, 0)),sum(decode(IS_BIND_SENSITIVE ,''Y'',1, 0))'
                ||',sum(decode(IS_BIND_AWARE,''Y'',1,0)),sum(decode(IS_SHAREABLE,''Y'',1,0))'
                ||' from v$sql where sql_id = :v_sql_id';

         EXECUTE IMMEDIATE v_query
         bulk collect into v_position, v_minlen, v_maxlen , v_dtype
         using v_sql_id;

         PIPE ROW('');
         PIPE ROW('SUM(DECODE(column,Y, 1, 0) FROM V$SQL');
         PIPE ROW('IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE');
         PIPE ROW('=========== ================= ============= ============');
         for c in 1 .. v_position.count loop
          PIPE ROW(to_char(v_position(c),'9999999999')||' '|| to_char(v_minlen(c),'9999999999999999')
                  ||' '|| to_char(v_maxlen(c),'999999999999')
                  ||' '|| to_char(v_dtype(c),'99999999999'));
         end loop;
        end if;
       end;
      end if;
     end if;
    elsif v_colname(i) ='OPTIMIZER_MODE_MISMATCH' then
      for c in (select OPTIMIZER_MODE,count(*) n from v$sql where hash_value=v_hash group by OPTIMIZER_MODE) loop
       PIPE ROW(c.n||' versions with '||c.OPTIMIZER_MODE);
      end loop;
    elsif v_colname(i) ='OPTIMIZER_MISMATCH' then
      if v_version like '9%' then
       PIPE ROW('No details available for '||v_version);
      else
       declare
        v_param vc_arr;
        v_value vc_arr;
        v_n num_arr;
       begin
        v_query:='select o.NAME,o.VALUE ,count(*) n '
                   ||'from V$SQL_OPTIMIZER_ENV o,sql_shared_cursor s '
                   ||'where ISDEFAULT=''NO'' '
                   ||'  and OPTIMIZER_MISMATCH=''Y'' '
                   ||'  and s.sql_id=:v_sql_id '
                   ||'  and o.sql_id=s.sql_id '
                   ||'  and o.CHILD_ADDRESS=s.CHILD_ADDRESS '
                   ||' group by o.NAME,o.VALUE ';
        EXECUTE IMMEDIATE v_query
        bulk collect into v_param,v_value,v_n using v_sql_id ;

        for c in 1 .. v_n.count  loop
         PIPE ROW(v_n(c)||' versions with '||v_param(c)||' = '||v_value(c));
        end loop;
       end;
      end if;
    elsif v_colname(i) ='AUTH_CHECK_MISMATCH' then
       declare
        v_pusr num_arr;
        v_pschid num_arr;
        v_pschname vc_arr;
        v_n num_arr;
       begin

        if v_version like '9%' then
         v_query:='select  PARSING_USER_ID, PARSING_SCHEMA_ID, ''n/a'' ,count(*) n from  v$sql '
                 ||v_sql_where
                 ||' group by PARSING_USER_ID, PARSING_SCHEMA_ID,''n/a''';
        else
         v_query:='select  PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME ,count(*) n from  v$sql '
                 ||v_sql_where
                 ||' group by PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME';
        end if;
        EXECUTE IMMEDIATE v_query
        bulk collect into v_pusr,v_pschid,v_pschname,v_n;

        PIPE ROW('  # of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME');
        PIPE ROW('========== =============== ================= ===================');
        for c in 1 .. v_n.count loop
         PIPE ROW(to_char(v_n(c),'999999999')|| TO_CHAR(v_pusr(c),'9999999999999999')|| to_char(v_pschid(c),'99999999999999999')||' '||v_pschname(c));
        end loop;
       end;
    elsif v_colname(i) = 'TRANSLATION_MISMATCH' then
       declare
        v_objn  num_arr;
        v_objow vc_arr;
        v_objnm vc_arr;
       begin
        v_query:='select distinct p.OBJECT#,p.OBJECT_OWNER,p.OBJECT_NAME'
           ||' from (select OBJECT_NAME ,count(distinct object#) n from v$sql_plan '
                  ||v_sql_where
                  ||' and object_name is not null group by OBJECT_NAME ) d'
           ||' ,v$sql_plan p where d.object_name=p.object_name and d.n>1';

        EXECUTE IMMEDIATE v_query
         bulk collect into v_objn,v_objow,v_objnm;

        If v_objn.count>0 then
         PIPE ROW('Summary of objects probably causing TRANSLATION_MISMATCH');
         PIPE ROW(' ');
         PIPE ROW('     Object# Owner.Object_Name');
         PIPE ROW('============ =================');
         for c in 1 .. v_objn.count loop
          PIPE ROW(to_char(v_objn(c),'99999999999')||' '||v_objow(c)||'.'||v_objnm(c));
         end loop;
        else
         PIPE ROW('No objects in the plans with same name and different owner were found.');
        end if;
       end;
    else
     PIPE ROW('No details available');
    end if;
   end if;
 end loop;
 debugme('cursortrace');
 IF v_version not like '9%' then
  PIPE ROW('####');
  PIPE ROW('To further debug Ask Oracle Support for the appropiate level LLL.');
  if v_version in ('10.2.0.1.0','10.2.0.2.0','10.2.0.3.0') THEN
   PIPE ROW('and read note:457225.1 Cannot turn off Trace after setting CURSORTRACE EVENT');
  end if;
  PIPE ROW('alter session set events ');
  PIPE ROW(' ''immediate trace name cursortrace address '||v_hash||', level LLL'';');
  PIPE ROW('To turn it off do use address 1, level 2147483648');
 end if;
 PIPE ROW('================================================================');
 debugme('End of version_rpt');
 return;
 exception
  when others then
   PIPE ROW('Error :'||sqlerrm);
   PIPE ROW('for Addr: '||v_addr||'  Hash_Value: '||v_hash||'  SQL_ID '||v_sql_id);
   for i in 0 .. trunc(length(v_query)/64) loop
    PIPE ROW(i||' '||substr(v_query,1+i*64,64));
   end loop;
 return;
end;
/


=====================


Generate reports for all cursors with more than 100 versions using SQL_ID (10g and up)

set pages 2000 lines 100
SELECT b.*
FROM v$sqlarea a ,
  TABLE(version_rpt(a.sql_id)) b
WHERE loaded_versions >=100;                  /* Set to 30 in 11.2.0.3 and in versions where fix of bug 10187168 was initially introduced */


Generate reports for all cursors with more than 100 versions using HASH_VALUE

set pages 2000 lines 100
SELECT b.*
FROM v$sqlarea a ,
  TABLE(version_rpt(NULL,a.hash_value)) b
WHERE loaded_versions>=100;                   /* Set to 30 in 11.2.0.3 and in versions where fix of bug 10187168 was initially introduced */


Generate the report for cursor with sql_id cyzznbykb509s
set pages 2000 lines 100
SELECT * FROM TABLE(version_rpt('cyzznbykb509s'));




===================================================


SET LINESIZE 300 PAGESIZE 300
COLUMN sql_text FORMAT a50

SELECT 
    'SELECT * FROM table(version_rpt(''' || a.sql_id || '''));' AS version_query,
    a.cursors,
    a.sql_id,
    b.sql_text
FROM
(
    SELECT 
        COUNT(*) AS cursors, 
        ssc.sql_id
    FROM v$sql_shared_cursor ssc
    GROUP BY ssc.sql_id
) a
JOIN
(
    SELECT 
        sa.sql_id, 
        sa.sql_text 
    FROM v$sqlarea sa
    WHERE PARSING_SCHEMA_NAME = 'XXX'
    -- AND sql_id = '4dc6768syk8z2'
) b
ON a.sql_id = b.sql_id
WHERE a.cursors > 100
ORDER BY a.cursors DESC;

========================

define 1='ajqqtcxazrnx'

col sql_id format a13
col address format a16
col child_address format a16
col child_number format 9999999 head 'CHILD#'
col parameter format a25
col reason format a60
col optimizer_mode_cursor  format a10 head 'OPTIMIZER|MODE|CURSOR'
col optimizer_mode_current  format a10 head 'OPTIMIZER|MODE|CURRENT'

set long 10000

ttitle off
set term off head off verify off pause off

col u_sql_id new_value u_sql_id noprint

select '&1' u_sql_id from dual;
set term on feed on head on


set linesize 200 trimspool on

select 
	s.sql_id
	, s.address
	, s.child_address
	, s.child_number
	, decode(x.optimizer_mode_cursor, 1, 'ALL_ROWS',
		2, 'FIRST_ROWS', 
		3, 'RULE', 
		4, 'CHOOSE', x.optimizer_mode_cursor,
		'NA'
	) AS optimizer_mode_cursor
	, decode(x.optimizer_mode_current, 1, 'ALL_ROWS',
		2, 'FIRST_ROWS', 
		3, 'RULE', 
		4, 'CHOOSE', x.optimizer_mode_current,
		'NA'
	) AS optimizer_mode_current
	, s.parameter
	, x.reason
from (
	select sql_id, address, child_address, child_number, reason
		, unbound_cursor , sql_type_mismatch , optimizer_mismatch , outline_mismatch
		, stats_row_mismatch , literal_mismatch , force_hard_parse , explain_plan_cursor
		, buffered_dml_mismatch , pdml_env_mismatch , inst_drtld_mismatch , slave_qc_mismatch
		, typecheck_mismatch , auth_check_mismatch , bind_mismatch , describe_mismatch
		, language_mismatch , translation_mismatch , bind_equiv_failure , insuff_privs
		, insuff_privs_rem , remote_trans_mismatch , logminer_session_mismatch , incomp_ltrl_mismatch
		, overlap_time_mismatch , edition_mismatch , mv_query_gen_mismatch , user_bind_peek_mismatch
		, typchk_dep_mismatch , no_trigger_mismatch , flashback_cursor , anydata_transformation
		, pddl_env_mismatch , top_level_rpi_cursor , different_long_length , logical_standby_apply
		, diff_call_durn , bind_uacs_diff , plsql_cmp_switchs_diff , cursor_parts_mismatch
		, stb_object_mismatch , crossedition_trigger_mismatch , pq_slave_mismatch , top_level_ddl_mismatch
		, multi_px_mismatch , bind_peeked_pq_mismatch , mv_rewrite_mismatch , roll_invalid_mismatch
		, optimizer_mode_mismatch , px_mismatch , mv_staleobj_mismatch , flashback_table_mismatch
		, litrep_comp_mismatch , plsql_debug , load_optimizer_stats , acl_mismatch
		, flashback_archive_mismatch , lock_user_schema_failed , remote_mapping_mismatch , load_runtime_heap_failed
		, hash_match_failed , purged_cursor , bind_length_upgradeable , use_feedback_stats
	from v$sql_shared_cursor
	where sql_id = '&u_sql_id'
)
UNPIVOT (invalidated 
	for parameter in(
		unbound_cursor as           'unbound_cursor'           , sql_type_mismatch as        'sql_type_mismatch'        
		, optimizer_mismatch as       'optimizer_mismatch'       , outline_mismatch as         'outline_mismatch'         
		, stats_row_mismatch as       'stats_row_mismatch'       , literal_mismatch as         'literal_mismatch'         
		, force_hard_parse as         'force_hard_parse'         , explain_plan_cursor as      'explain_plan_cursor'      
		, buffered_dml_mismatch as    'buffered_dml_mismatch'    , pdml_env_mismatch as        'pdml_env_mismatch'        
		, inst_drtld_mismatch as      'inst_drtld_mismatch'      , slave_qc_mismatch as        'slave_qc_mismatch'        
		, typecheck_mismatch as       'typecheck_mismatch'       , auth_check_mismatch as      'auth_check_mismatch'      
		, bind_mismatch as            'bind_mismatch'            , describe_mismatch as        'describe_mismatch'        
		, language_mismatch as        'language_mismatch'        , translation_mismatch as     'translation_mismatch'     
		, bind_equiv_failure as       'bind_equiv_failure'       , insuff_privs as             'insuff_privs'             
		, insuff_privs_rem as         'insuff_privs_rem'         , remote_trans_mismatch as    'remote_trans_mismatch'    
		, logminer_session_mismatch as 'logminer_session_mismatch' , incomp_ltrl_mismatch as     'incomp_ltrl_mismatch'     
		, overlap_time_mismatch as    'overlap_time_mismatch'    , edition_mismatch as         'edition_mismatch'         
		, mv_query_gen_mismatch as    'mv_query_gen_mismatch'    , user_bind_peek_mismatch as  'user_bind_peek_mismatch'  
		, typchk_dep_mismatch as      'typchk_dep_mismatch'      , no_trigger_mismatch as      'no_trigger_mismatch'      
		, flashback_cursor as         'flashback_cursor'         , anydata_transformation as   'anydata_transformation'   
		, pddl_env_mismatch as        'pddl_env_mismatch'        , top_level_rpi_cursor as     'top_level_rpi_cursor'     
		, different_long_length as    'different_long_length'    , logical_standby_apply as    'logical_standby_apply'    
		, diff_call_durn as           'diff_call_durn'           , bind_uacs_diff as           'bind_uacs_diff'           
		, plsql_cmp_switchs_diff as   'plsql_cmp_switchs_diff'   , cursor_parts_mismatch as    'cursor_parts_mismatch'    
		, stb_object_mismatch as            'stb_object_mismatch'           , crossedition_trigger_mismatch as  'crossedition_trigger_mismatch' 
		, pq_slave_mismatch as              'pq_slave_mismatch'             , top_level_ddl_mismatch as         'top_level_ddl_mismatch'        
		, multi_px_mismatch as              'multi_px_mismatch'             , bind_peeked_pq_mismatch as        'bind_peeked_pq_mismatch'       
		, mv_rewrite_mismatch as            'mv_rewrite_mismatch'           , roll_invalid_mismatch as          'roll_invalid_mismatch'         
		, optimizer_mode_mismatch as        'optimizer_mode_mismatch'       , px_mismatch as                    'px_mismatch'                   
		, mv_staleobj_mismatch as           'mv_staleobj_mismatch'          , flashback_table_mismatch as       'flashback_table_mismatch'      
		, litrep_comp_mismatch as           'litrep_comp_mismatch'          , plsql_debug as                    'plsql_debug'                   
		, load_optimizer_stats as           'load_optimizer_stats'          , acl_mismatch as                   'acl_mismatch'                  
		, flashback_archive_mismatch as     'flashback_archive_mismatch'    , lock_user_schema_failed as        'lock_user_schema_failed'       
		, remote_mapping_mismatch as        'remote_mapping_mismatch'       , load_runtime_heap_failed as       'load_runtime_heap_failed'      
		, hash_match_failed as              'hash_match_failed'             , purged_cursor as                  'purged_cursor'                 
		, bind_length_upgradeable as        'bind_length_upgradeable'       , use_feedback_stats as             'use_feedback_stats'  
	)
) s,
-- XML from Troubleshooting Oracle Performance Version 2, Christian Antognini
XMLTable('/Root'
	PASSING XMLType('<Root>'||reason||'</Root>')
	COLUMNS child_number NUMBER                  PATH '/Root/ChildNode[1]/ChildNumber',
		id NUMBER                            PATH '/Root/ChildNode[1]/ID',
		reason VARCHAR2(100)                 PATH '/Root/ChildNode[1]/reason',
		optimizer_mode_hinted_cursor NUMBER  PATH '/Root/ChildNode[1]/optimizer_mode_hinted_cursor',
		optimizer_mode_cursor NUMBER         PATH '/Root/ChildNode[1]/optimizer_mode_cursor',
		optimizer_mode_current NUMBER        PATH '/Root/ChildNode[1]/optimizer_mode_current'
) x
where invalidated = 'Y'
order by sql_id, child_number
/





DECLARE
 l_name VARCHAR2(64);
 l_sql_text CLOB;
BEGIN
 -- get address, hash_value and sql text
 SELECT address||','||hash_value, sql_fulltext 
 INTO l_name, l_sql_text 
 FROM v$sqlarea 
 WHERE sql_id = '&&sql_id.';
 -- not always does the job
 SYS.DBMS_SHARED_POOL.PURGE (
 name => l_name,
 flag => 'C',
 heaps => 1
 );
 END;
/