定位version count 高的脚本

朱雀 2022-08-14 04:34 132阅读 0赞

本文来自于oracle官方

REQUIREMENTS

The view and function must be installed while connected to SYS (can be done with / as sysdba).  
The function also must be run using a query as SYS.

The function accepts the Hash\_Value or SQL ID (From 10gR1 and up) as input.

CONFIGURING

Download the script in the attachment: Script to create version\_rpt function V3.2.3  
and execute as follows:  
connect / as sysdba  
start version\_rpt3\_23.sql  
INSTRUCTIONS

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;  
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;  
Generate the report for cursor with sql\_id cyzznbykb509s

set pages 2000 lines 100  
SELECT \* FROM TABLE(version\_rpt('cyzznbykb509s'));

SAMPLE OUTPUT

Note:438755.1 Version Count Report Version 3.2.3 -- Today's Date 05-feb-14 19:31  
RDBMS Version :11.2.0.3.0 Host: XXXX Instance 1 : XXXX  
==================================================================  
Addr: 00000000675792F0  Hash\_Value: 275110491  SQL\_ID b6bkg4n86bqkv  
Sharable\_Mem: 127024 bytes   Parses: 3   Execs:20  
Stmt:  
0 SELECT COUNT(\*) AMC FROM T WHERE X=:B1  
1

Versions Summary  
\----------------  
OPTIMIZER\_MISMATCH :3  
OPTIMIZER\_MODE\_MISMATCH :6  
BIND\_LENGTH\_UPGRADEABLE :6

Total Versions:8

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  
cursor\_sharing = EXACT  
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Plan Hash Value Summary  
\-----------------------  
Plan Hash Value Count  
=============== =====  
     2966233522 9

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  
Details for OPTIMIZER\_MISMATCH :

3 versions with optimizer\_mode = first\_rows\_10  
3 versions with statistics\_level = all  
3 versions with sqlstat\_enabled = true  
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  
Details for OPTIMIZER\_MODE\_MISMATCH :

3 versions with RULE  
6 versions with FIRST\_ROWS  
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  
Details for BIND\_LENGTH\_UPGRADEABLE :

Consolidated details for BIND\* columns:  
BIND\_MISMATCH,USER\_BIND\_PEEK\_MISMATCH,BIND\_UACS\_DIFF,BIND\_LENGTH\_UPGRADEABLE,etc and  
BIND\_EQUIV\_FAILURE (Mislabled as ROW\_LEVEL\_SEC\_MISMATCH BY bug 6964441 in 11gR1)

from v$sql\_bind\_capture  
COUNT(\*) POSITION MIN(MAX\_LENGTH) MAX(MAX\_LENGTH) DATATYPE BIND GRADUATION (PRECISION,SCALE)  
======== ======== =============== =============== ======== =============== =================  
       9        1              32            2000        1     Yes          (,)

SUM(DECODE(column,Y, 1, 0) FROM V$SQL  
IS\_OBSOLETE IS\_BIND\_SENSITIVE IS\_BIND\_AWARE IS\_SHAREABLE  
=========== ================= ============= ============  
          0                 0             0            3  
\#\#\#\#  
To further debug Ask Oracle Support for the appropiate level LLL.  
alter session set events  
 'immediate trace name cursortrace address 275110491, level LLL';  
To turn it off do use address 1, level 2147483648  
================================================================

脚本内容如下：

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\_obs 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,'  
        ||  'sum(case IS\_OBSOLETE when ''Y'' then VERSION\_COUNT else 0 end) obs'  
        ||  ' 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.3 -- 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,v\_obs;  
    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,v\_obs;  
     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: '||case when v\_obs>0 then '\*\*\* MULTIPLE!!! \*\*\*' else v\_addr end||'  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( theCursor, v\_Query, dbms\_sql.native );

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;  
          
         if v\_obs>0 then  
          PIPE ROW(' ');  
          PIPE ROW('Total Obsolete Versions :'||v\_obs);  
          PIPE ROW('Total Non-Obsolete Versions :'||abs(v\_count-v\_obs));  
          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('(See Note:10187168.8) \_cursor\_obsolete\_threshold = '||v\_no);  
          end if;  
         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),'99999999')||' '||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;  
/

rem select b.\* from v$sqlarea a ,table(version\_rpt(a.sql\_id,null,'Y')) b where loaded\_versions >=100;

rem select \* from table(debug\_version\_rpt);