Oracle 高 Version counts 问题说明

 

一. version count 说明

            MOS上有文档说明此问题,参考:[ID296377.1]

 

            Oracle里的所有SQL 语句都是implicitly sharable的。 当用户提交了一个SQL语句,RDBMS 会对这个SQL 语句进行hash 运算,然后产生一个hash 值, 在拿这个hash 值与shared pool 里已经存在的SQL 进行hash 值比较,如果相同,就重用之前SQL 的cursor,如果没有,就需要进行硬解析。

 

            在硬解析时,Oracle 至少会产生一个parent cursor 和 child cursor, parent cursor 用来保存hash value。 child cursor 用来保存SQL 语句的metadata。 parent cursor和child cursor都是shared cursor,它们都是以library cache object handle的方式存在library cache里。

 

注意:

            parentcursor和child cursor都是library cache object,它们的结构是一摸一样的.  这个可以通过dump library cache 来确认。

 

            看下library cache 的一个管理结构图,这几张图是从DSI 里找到的。

 

            Oracle通过Hash buckets来存放相关的对象,如cursor,那么Oracle会把cursor 放到某个具体的buckets里,由Oracle自己的算法来操作。 在每个bucket里面,会有很多的object,Oracle 通过list 的方式将每个bucket里的对象连接起来。

            每个Hash bucket 指向一个object Handle,handle 又指向其他的handle,每个Handle 又指向对应的Library Cache Object。 如下图所示。

 

 

 

 

            LibraryCache Object是由一些独立的heap所组成, Library cache handle指向Library cache Object,其实handle是指向第一个heap,这个heap 我们就称之为heap 0。Heap 0记录了指向其他heap的指针信息。

 

            heap0 代表的是parentcursor,它保存的是sql 语句的hash value,同时heap 0 的child table里保存所有child cursor 的Data Heap地址。 在每个Child cursor  对应的handle 的Heap 0中的Data Blocks指向的heap 6保存了SQL 对应的执行计划。

 

            当SQL 语句第一次执行时,在硬解析时,会创建parent cursor 和child cursor。 这2个是必须的。这2个过程是比较费资源的。也是硬解析里主要做的事。 当再次执行这个SQL时,Oracle 会先对SQL 语句进行hash 运算,产生一个hash 值,然后用这个HASH 值到buckets里去查找,hash value 存放在parent cursor里。 如果找到了,就去检查child cursor。 如果可以重用这个child cursor,那么就直接调用cursor里的执行计划。 如果不可重用,就会创建一个新的child cursor。 这个child cursor 的个数,就是version count。 不同parent cursor 对应的child cursor 越多,version count 就越高。

 

            可以通过v$sqlarea 和 v$sql 视图来查新相关信息,v$sqlarea是父游标相关信息视图,v$sql是子游标的。

           

            如:

select sql_id,version_countfromv$sqlarea  order by 2 desc ;

select  address,child_address,sql_textfrom v$sqlwheresql_id='70th7d08hqjf7';

 

            v$sql中通过child_number,hash_value,address来确定一个子游标,而v$sqlarea通过address和hash_value就可以确定一个父游标;而从10g过后,通过sql_id就能确定一个游标。

 

二. cursor 不能共享原因查看

之前整理过一篇:

            由 bind_mismatch 引起的 大量 version_count 问题

            http://blog.csdn.net/tianlesoftware/article/details/6566658

这个只是其中的一种情况。

 

2.1 查看方法

通过如下方法我们来查看具体不能共享的原因:

(1)查看parent cursor 的hash value 和address:

            如:

            SQL>selectsql_text, hash_value,address from v$sqlarea where sql_text like 'selectcount(*) from emp%';
SQL_TEXT HASH_VALUE ADDRESS
------------------------------------- ----------------
select count(*) from emp 4085390015 0000000386BC2E58

 

(2)检查child cursor

在Oracle 9i 使用如下语句:
            SQL>select * fromv$sql_shared_cursor where kglhdpar = '0000000386BC2E58'
在Oracle 10g 使用如下语句:
            SQL>select * fromv$sql_shared_cursor where address = '0000000386BC2E58'

ADDRESS KGLHDPAR U S O O S L S E B P I S TA B D L T R I I R L I O S M U T N F
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - -
0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N NN N N N N N N N
0000000386A91AA0 0000000386BC2E58 N N N N N N N N N N N N N Y N N N Y N N N N N N NN N N N N N

 

--这个查询结果显示该parent cursor 有2个child cursor。 显示结果中为Y的就是不能共享的原因。 在这个示例是是因为AUTH_CHECK_MISMATCH 和TRANSLATION_MISMATCH

 

2.2 不能共享原因详细说明

(1)UNBOUND_CURSOR

            Theexisting child cursor was not fully built (in other words, it was notoptimized)
(2)SQL_TYPE_MISMATCH

             The SQL type does not match the existing childcursor
(3)OPTIMIZER_MISMATCH

            Theoptimizer environment does not match the existing child cursor.
For example:

SQL>select count(*) from emp; ->>1 PARENT, 1 CHILD
SQL>alter session set optimizer_mode=ALL_ROWS
SQL>select count(*) from emp; ->> 1 PARENT, 2 CHILDREN

            Theoptimizer mode has changed and therefore the existing child cannot be reused,Thesame applies with events - if I turned on tracing with 10046 than I would getthe OPTIMIZER_MISMATCH again and a 3rd child.

(4)OUTLINE_MISMATCH

 Theoutlines do not match the existing child cursor,If my user had created stored outlines previously for this commandand they were stored in seperate categories (say "OUTLINES1" and"OUTLINES2") running:

for example:

SQL>alter session setuse_stored_outlines = OUTLINES1;
SQL>select count(*) from emp;
SQL>alter session set use_stored_oulines= OUTLINES2;
SQL>select count(*) from emp;

--> Would create a 2ndchild as the outline used is different than the first run.

(5)STATS_ROW_MISMATCH

            Theexisting statistics do not match the existing child cursor,Check that 10046/sql_trace is notset on all sessions as this can cause this.

(6)LITERAL_MISMATCH

             Non-data literal values do not match theexisting child cursor

(7)SEC_DEPTH_MISMATCH

             Security level does not match the existingchild cursor

(8)EXPLAIN_PLAN_CURSOR

             The child cursor is an explain plan cursor andshould not be shared,Explain plan statements will generate a new child by default - themismatch will be this.

(9)BUFFERED_DML_MISMATCH

             Buffered DML does not match the existing childcursor

(10)PDML_ENV_MISMATCH

            PDMLenvironment does not match the existing child cursor
(11)INST_DRTLD_MISMATCH

             Insert direct load does not match the existingchild cursor
(12)SLAVE_QC_MISMATCH

            Theexisting child cursor is a slave cursor and the new one was issued by thecoordinator ,or, the existing child cursor was issued by the coordinator andthe new one is a slave cursor.
(13)TYPECHECK_MISMATCH

            Theexisting child cursor is not fully optimized
(14)AUTH_CHECK_MISMATCH

             Authorization/translation check failed for theexisting child cursor,         The user does not havepermission to access the object in any previous version of the cursor. Atypical example would be where each user has it's own copy of a table

(15)BIND_MISMATCH

            Thebind metadata does not match the existing child cursor。

For example:

variable a varchar2(100);
select count(*) from emp where ename = :a ->> 1 PARENT, 1 CHILD
variable a varchar2(400);
select count(*) from emp where ename = :a ->> 1 PARENT, 2 CHILDREN
(The bind 'a' has now changed in definition)

(16)DESCRIBE_MISMATCH

            Thetypecheck heap is not present during the describe for the child cursor
(17)LANGUAGE_MISMATCH

            Thelanguage handle does not match the existing child cursor
(18)TRANSLATION_MISMATCH

             The base objects of the existing child cursordo not match
            The definition of the objectdoes not match any current version. Usually this is indicative of the sameissue as "AUTH_CHECK_MISMATCH" where the object is different

(19)ROW_LEVEL_SEC_MISMATCH

             The row level security policies do not match
(20)INSUFF_PRIVS

             Insufficient privileges on objects referencedby the existing child cursor
(21)INSUFF_PRIVS_REM

             Insufficient privileges on remote objectsreferenced by the existing child cursor
(22)REMOTE_TRANS_MISMATCH

             The remote base objects of the existing childcursor do not match

USER1: select count(*) from table@remote_db
USER2: select count(*) from table@remote_db
            Although the SQL is identical,the dblink pointed to by remote_db may be a private dblink which resolves to adifferent object altogether.

(23)LOGMINER_SESSION_MISMATCH
(24)INCOMP_LTRL_MISMATCH
(25)OVERLAP_TIME_MISMATCH

             error_on_overlap_time mismatch
(26)SQL_REDIRECT_MISMATCH

             sql redirection mismatch
(27)MV_QUERY_GEN_MISMATCH

             materialized view query generation
(28)USER_BIND_PEEK_MISMATCH

             user bind peek mismatch
(28)TYPCHK_DEP_MISMATCH

             cursor has typecheck dependencies
(29)NO_TRIGGER_MISMATCH         

             no trigger mismatch
(30)FLASHBACK_CURSOR

             No cursor sharing for flashback
(31)ANYDATA_TRANSFORMATION

             anydata transformation change
(32)INCOMPLETE_CURSOR

            incompletecursor,When bindlength is upgradeable (i.e. we found a child cursor that matches everything elseexcept that the bind length is not long enough). In this case, we mark the oldcursor is not usable and build a new one. This means the version can beignored.
(33)TOP_LEVEL_RPI_CURSOR          

             top level/rpi cursor,In a Parallel Query invocation thisis expected behaviour (we purposely do not share)
(34)DIFFERENT_LONG_LENGTH    

             different long length
(35)LOGICAL_STANDBY_APPLY

             logical standby apply mismatch
(36)DIFF_CALL_DURN

             different call duration
(37)BIND_UACS_DIFF

             bind uacs mismatch
(38)PLSQL_CMP_SWITCHS_DIFF

             plsql compiler switches mismatch
(39)CURSOR_PARTS_MISMATCH

            cursor-partsexecuted mismatch
(40)STB_OBJECT_MISMATCH

             STB object different (now exists)
(41)ROW_SHIP_MISMATCH

            rowshipping capability mismatch
(42)PQ_SLAVE_MISMATCH

             PQ slave mismatch,Check you want to be using PX withthis reason code, as the problem could be caused by running lots of small SQLstatements which do not really need PX. If you are on < 11i you may behitting Bug:4367986

(43)TOP_LEVEL_DDL_MISMATCH

             top-level DDL cursor
(44)MULTI_PX_MISMATCH   

            multi-pxand slave-compiled cursor
(45)BIND_PEEKED_PQ_MISMATCH 

             bind-peeked PQ cursor
(46)MV_REWRITE_MISMATCH

             MV rewrite cursor
(47)ROLL_INVALID_MISMATCH

             rolling invalidation window exceeded,This is caused by the rollinginvalidation capability in DBMS_STATS. The child cannot be shared as it'sinvalidation window is exceeded. See:Note:557661.1Rolling Cursor Invalidations with DBMS_STATS in Oracle10g (Doc ID 557661.1)

(48)OPTIMIZER_MODE_MISMATCH

            optimizermode mismatch
(49)PX_MISMATCH

            parallelquery mismatch,If running11.1.0.6 and RAC see Bug:7352775.Check that if (on each instance) parallel_instance_groups is set theninstance_groups is set to the same.
(50)MV_STALEOBJ_MISMATCH

             mv stale object mismatch
(51)FLASHBACK_TABLE_MISMATCH

            flashbacktable mismatch
(52)LITREP_COMP_MISMATCH

             literal replacement compilation mismatch

 

在Oracle 11g里又添加了如下原因:

(1)PLSQL_DEBUG - debugmismatch

            Sessionhas debugging parameter plsql_debug set to true

(2)LOAD_OPTIMIZER_STATS

             Load optimizer stats for cursor sharing

(3)ACL_MISMATCH

             Check ACL mismatch

(4)FLASHBACK_ARCHIVE_MISMATCH

             Flashback archive mismatch

(5)LOCK_USER_SCHEMA_FAILED

             Failed to lock user and schema

(6)REMOTE_MAPPING_MISMATCH

             Remote mapping mismatch

(7)LOAD_RUNTIME_HEAP_FAILED

             Runtime heap mismatch

(8)HASH_MATCH_FAILED

             Hash mismatch

 

三. V$SQL_SHARED_CURSOR Report by SQLID orHash Value 脚本

            Oracle提供了一个脚本,可以通过sqlid或者hash value 来查看v$sql_shared_cursor的报告。 脚本的具体内容,参考MOS: [ID 438755.1].

            脚本必须使用SYS 用户来安装和运行,使用与9iR2 , 10gR1, 10gR2,11gR1, 11gR2。

            脚本比较长,会作为附件,贴到最后。

 

运行脚本,如:

SQL>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 usingHASH_VALUE:

SQL>select b.* from v$sqlarea a ,table(version_rpt(null,a.hash_value)) bwhere loaded_versions>=100;
-- Generate the report for cursor with sql_id cyzznbykb509s:
SQL>select * from table(version_rpt('cyzznbykb509s'));

 

 

            cursor的不可重用,也可能与cursor_sharing参数值设置有关。 关于该参数值,参考我的Blog:

            Oracle cursor_sharing 参数 详解

            http://blog.csdn.net/tianlesoftware/article/details/6551723

 

 

附件一: V$SQL_SHARED_CURSOR 脚本

 

/* Formatted on 2011/7/23 16:35:16(QP5 v5.163.1008.3004) */

CREATE OR REPLACE VIEWSQL_SHARED_CURSOR

AS

   SELECT * FROM sys.v$sql_shared_cursor;

 

CREATE OR REPLACE FUNCTIONversion_rpt (p_sql_id    VARCHAR2 DEFAULT NULL,

                                       p_hash      NUMBER DEFAULT NULL)

   RETURNDBMS_DEBUG_VC2COLL

   PIPELINED

IS

   TYPEvc_arr IS TABLE OF VARCHAR2 (32767)

                    INDEX BY BINARY_INTEGER;

 

   TYPEnum_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;

 

   theCursor       NUMBER;

   columnValue     CHAR (1);

   status          NUMBER;

 

   v_driver        VARCHAR2 (1000);

 

   TYPEcursor_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;

BEGIN

   SELECT version,

             'Host: '

          ||HOST_NAME

          || 'Instance '

          ||INSTANCE_NUMBER

          || ' : '

          ||INSTANCE_NAME

     INTOv_version,v_instance

     FROMv$instance;

 

   v_coladdr :=

      CASE WHENv_version LIKE '9%' THEN 'KGLHDPAR' ELSE 'ADDRESS' END;

 

   IFv_sql_id IS NOT NULL

   THEN

      OPEN vc FOR

         'selectsql_text query,hash_value hash,rawtohex(ADDRESS) addr , sql_id ,SHARABLE_MEM,PARSE_CALLS '

         || ' from v$sqlarea wheresql_id=:v_sql_id '

         USINGv_sql_id;

   ELSE                                                     -- UseHash Value

      OPEN vc FOR

            'selectsql_text query,hash_value,rawtohex(ADDRESS) addr,'

         || CASE WHENv_version LIKE '9%' THEN ' NULL ' END

         || 'sql_id,SHARABLE_MEM,PARSE_CALLS '

         || ' fromv$sqlarea where hash_value=:v_hash'

         USINGv_hash;

   END IF;

 

   PIPE ROW ('VersionCount Report Version 3.1.2 -- Today''s Date '

             || TO_CHAR (SYSDATE, 'dd-mon-yyhh24:mi'));

   PIPE ROW ('RDBMSVersion :' ||v_version || ' ' ||v_instance);

 

   /*

     This loop is in the remote case there are more than 1 SQL with the samehash value or sql_id

     After this loop I cannot guarantee that I can distinguish the collidingSQL from one another.

  */

 

   LOOP

      FETCH vc

      INTOv_query,v_hash,v_addr,v_sql_id, v_mem,v_parses;

 

      EXIT WHEN vc%NOTFOUND;

 

      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);

      PIPE ROW ('Stmt: ');

 

      FOR i IN 0 .. TRUNC (LENGTH (v_query) / 64)

      LOOP

         PIPE ROW (i || ' ' || SUBSTR (v_query, 1 + i * 64, 64));

      END LOOP;

 

        SELECTCOLUMN_NAME, 0

          BULK COLLECT INTOv_colname,v_Ycnt

          FROM cols

         WHEREtable_name = 'SQL_SHARED_CURSOR' ANDCHAR_LENGTH = 1

      ORDER BYcolumn_id;

 

      v_query := '';

 

      FOR i IN 1 ..v_colname.COUNT

      LOOP

         v_query :=v_query || ',' ||v_colname (i);

      END LOOP;

 

      v_sql_where := 'WHEREADDRESS =HEXTORAW(''' || V_ADDR || ''')';

 

      IFv_version NOT LIKE '9%'

      THEN

         IFv_sql_id IS NOT NULL

         THEN

           v_sql_where2 := ' AND SQL_ID=''' ||v_sql_id || '''';

         ELSE

           v_sql_where2 := ' AND hash_value=' || TO_CHAR (v_hash);

         END IF;

      ELSE

        v_sql_where2 := '';

      END IF;

 

 

      v_query :=

         'SELECT' || SUBSTR (v_query, 2) || ' FROMSQL_SHARED_CURSOR ';

      v_query :=

            v_query

         || ' WHERE'

         ||v_coladdr

         || '=HEXTORAW('''

         || V_ADDR

         || ''')'

         ||v_sql_where2;

 

      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);

 

         WHILE (DBMS_SQL.fetch_rows (theCursor) > 0)

         LOOP

            v_no := 0;

            v_count:=v_count + 1;

 

            FOR i IN 1 ..v_colname.COUNT

            LOOP

               DBMS_SQL.COLUMN_VALUE(theCursor, i,columnValue);

 

               IFcolumnValue = '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;

 

      PIPE ROW ('');

      PIPE ROW ('VersionsSummary');

      PIPE ROW ('----------------');

 

      FOR i IN 1 ..v_colname.COUNT

      LOOP

         IFv_Ycnt (i) > 0

         THEN

            PIPE ROW (v_colname(i) || ' :' ||v_Ycnt (i));

         END IF;

      END LOOP;

 

      IFv_all_no > 1

      THEN

         PIPE ROW ('Versionswith ALL Columns as "N" :' || v_all_no);

      END IF;

 

      PIPE ROW ('TotalVersions:' ||v_count);

      PIPE ROW ('~');

 

      v_phv.delete;

      v_phvc.delete;

 

      v_query :=

            'selectplan_hash_value,count(*) from v$sql '

         ||v_sql_where

         ||v_sql_where2

         || ' groupby plan_hash_value';

 

      EXECUTE IMMEDIATEv_query BULK COLLECT INTO v_phv,v_phvc;

 

      PIPE ROW ('PlanHash Value Summary');

      PIPE ROW ('-----------------------');

      PIPE ROW ('PlanHash 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), '9999'));

      END LOOP;

 

      PIPE ROW (' ');

   END LOOP;

 

   FOR i IN 1 ..v_colname.COUNT

   LOOP

      IFv_Ycnt (i) > 0

      THEN

         PIPE ROW ('~~~~~~~~~~~~~~'

                  || RPAD ('~', LENGTH (v_colname(i)), '~'));

         PIPE ROW ('Detailsfor ' ||v_colname (i) || ' :');

         PIPE ROW ('');

 

         IF (v_colname(i) IN

                ('BIND_MISMATCH',

                 'USER_BIND_PEEK_MISMATCH',

                 'BIND_EQUIV_FAILURE',

                 'BIND_UACS_DIFF')

             OR (v_versionLIKE '11.1%'

                 ANDv_colname (i) = 'ROW_LEVEL_SEC_MISMATCH'))

         THEN

            IFv_bind_dumped = TRUE

            THEN                                             --Dump only once

               PIPEROW ('Detailsshown already.');

            ELSE

              v_bind_dumped := TRUE;

 

               IFv_version LIKE '9%'

               THEN

                 PIPE ROW ('Nodetails for ' ||v_version);

               ELSE

                 PIPE ROW ('Consolidateddetails for :');

                  PIPEROW ('BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFFand');

                 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;

                  BEGIN

                    v_query :=

                       'selectposition,min(max_length),max(max_length),datatype,precision,scale,count(*) n'

                       || ' fromv$sql_bind_capture where sql_id=:v_sql_id'

                       || ' groupby sql_id,position,datatype,precision,scale'

                       || ' orderby sql_id,position,datatype,precision,scale';

 

                    EXECUTE IMMEDIATEv_query

                        BULK COLLECT INTOv_position,

                             v_minlen,

                             v_maxlen,

                             v_dtype,

                             v_prec,

                             v_scale,

                            v_n

                       USINGv_sql_id;

 

                    PIPE ROW ('fromv$sql_bind_capture');

                    PIPE ROW ('COUNT(*)POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE (PRECISION,SCALE)');

                    PIPE ROW ('================ =============== =============== ======== ================');

 

                    FOR c IN 1 ..v_position.COUNT

                    LOOP

                       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_prec (c)

                                  || ','

                                  || v_scale (c)

                                  || ')');

                    END LOOP;

 

                    IFv_version LIKE '11%'

                    THEN

                       v_query :=

                          'selectsum(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))'

                          || ' fromv$sql where sql_id = :v_sql_id';

 

                       EXECUTE IMMEDIATEv_query

                          BULK COLLECT INTOv_position,

                                v_minlen,

                                v_maxlen,

                                v_dtype

                          USINGv_sql_id;

 

                       PIPE ROW ('');

                       PIPE ROW ('SUM(DECODE(column,Y,1, 0) FROM V$SQL');

                       PIPE ROW ('IS_OBSOLETEIS_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;

         ELSIFv_colname (i) = 'OPTIMIZER_MODE_MISMATCH'

         THEN

            FOR c IN (  SELECT OPTIMIZER_MODE, COUNT (*) n

                         FROM v$sql

                        WHEREhash_value =v_hash

                     GROUP BYOPTIMIZER_MODE)

            LOOP

               PIPEROW (c.n || 'versions with ' || c.OPTIMIZER_MODE);

            END LOOP;

         ELSIFv_colname (i) = 'OPTIMIZER_MISMATCH'

         THEN

            IFv_version LIKE '9%'

            THEN

               PIPEROW ('Nodetails available for ' || v_version);

            ELSE

               DECLARE

                 v_param   vc_arr;

                 v_value   vc_arr;

                 v_n       num_arr;

               BEGIN

                 v_query :=

                       'selecto.NAME,o.VALUE ,count(*) n '

                     || 'fromV$SQL_OPTIMIZER_ENV o,sql_shared_cursor s '

                    || 'whereISDEFAULT=''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 '

                    || ' groupby o.NAME,o.VALUE ';

 

                  EXECUTE IMMEDIATEv_query

                    BULK COLLECT INTOv_param,v_value, v_n

                    USINGv_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;

         ELSIFv_colname (i) = 'AUTH_CHECK_MISMATCH'

         THEN

            DECLARE

              v_pusr       num_arr;

              v_pschid     num_arr;

              v_pschname   vc_arr;

              v_n          num_arr;

            BEGIN

               IFv_version LIKE '9%'

               THEN

                 v_query :=

                    'select  PARSING_USER_ID, PARSING_SCHEMA_ID, ''n/a'',count(*) n from  v$sql '

                    || v_sql_where

                    || v_sql_where2

                    || ' groupby 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

                    || v_sql_where2

                    || ' groupby PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME';

               END IF;

 

               EXECUTE IMMEDIATEv_query

                  BULK COLLECT INTOv_pusr,v_pschid,v_pschname, v_n;

 

               PIPEROW ('# ofVer PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME');

               PIPEROW ('======================= ================= ===================');

 

               FOR c IN 1 .. v_n.COUNT

               LOOP

                 PIPE ROW (   TO_CHAR (v_n (c), '9999999')

                            || TO_CHAR (v_pusr(c), '9999999999999999')

                            || TO_CHAR (v_pschid(c), '99999999999999999')

                            || ' '

                            || v_pschname (c));

               END LOOP;

            END;

         ELSIFv_colname (i) = 'TRANSLATION_MISMATCH'

         THEN

            DECLARE

              v_objn    num_arr;

              v_objow   vc_arr;

              v_objnm   vc_arr;

            BEGIN

              v_query :=

                  'selectdistinct p.OBJECT#,p.OBJECT_OWNER,p.OBJECT_NAME'

                 || ' from(select OBJECT_NAME ,count(distinct object#) n from v$sql_plan '

                 || v_sql_where

                 || v_sql_where2

                 || ' andobject_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 IMMEDIATEv_query

                  BULK COLLECT INTOv_objn,v_objow,v_objnm;

 

               IF v_objn.COUNT > 0

               THEN

                 PIPE ROW ('Summaryof 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), '999999')

                               || ' '

                               || v_objow (c)

                               || '.'

                               || v_objnm (c));

                  END LOOP;

               ELSE

                 PIPE ROW ('Noobjects in the plans with same name and different owner were found.');

               END IF;

            END;

         ELSE

            PIPE ROW ('Nodetails available');

         END IF;

      END IF;

   END LOOP;

 

   IFv_version NOT LIKE '9%'

   THEN

      PIPE ROW ('####');

      PIPE ROW ('Tofurther debug Ask Oracle Support for the appropiate level LLL.');

 

      IFv_version IN ('10.2.0.1.0', '10.2.0.2.0', '10.2.0.3.0')

      THEN

         PIPE ROW ('andread note:457225.1 Cannot turn off Trace after setting CURSORTRACE EVENT');

      END IF;

 

      PIPE ROW ('altersession set events ');

      PIPE ROW (   ' ''immediate trace name cursortraceaddress '

                ||v_hash

                || ', levelLLL'';');

      PIPE ROW ('To turnit off do use address 1, level 2147483648');

   END IF;

 

   PIPE ROW ('================================================================');

   RETURN;

EXCEPTION

   WHEN OTHERS

   THEN

      PIPE ROW ('Error:' || SQLERRM);

      PIPE ROW (   'for Addr: '

                ||v_addr

                || '  Hash_Value: '

                ||v_hash

                || '  SQL_ID '

                ||v_sql_id);

      PIPE ROW (v_query);

      RETURN;

END;

/

 

 

 

-------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群:62697716(满);   DBA2 群:62697977(满)  DBA3 群:62697850(满)  

DBA 超级群:63306533(满);  DBA4 群: 83829929  DBA5群: 142216823   

DBA6 群:158654907  聊天 群:40132017   聊天2群:69087192

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请

posted @ 2011-07-23 16:48  生活不是用来挥霍的  阅读(550)  评论(0编辑  收藏  举报