Oracle 高 Version counts 问题说明-白红宇

Oracle 高 Version counts 问题说明

阅读量：5112 次

发布时间：2019-06-13

本文共 21576 字，大约阅读时间需要 71 分钟。

一. 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 不能共享原因查看

之前整理过一篇：

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

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：

附件一: V$SQL_SHARED_CURSOR 脚本

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

CREATE OR REPLACE VIEWSQL_SHARED_CURSOR

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

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,

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;

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 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表空间和数据文件的关系，否则拒绝申请

转载于:https://www.cnblogs.com/tianlesoftware/archive/2011/07/23/3609633.html

你可能感兴趣的文章

JAVA中构造函数的参数传递给类中的实例变量

Centos 6.4 安装erlang&rabbitmq

查看>>

requests 模块的基本使用

查看>>

Populating Next Right Pointers in Each Node

查看>>

C# 海康DVR客户端开发系列（1）—— 准备

查看>>

2016，把一年的牛皮先吹了吧[生涯规划篇]

查看>>

spring中的mybatis的sqlSession是如何做到线程隔离的？