一. version count 说明
MOS上有文档说明此问题，参考：[ID296377.1]
Oracle里的所有SQL 语句都是implicitly sharable的。 当用户提交了一个SQL语句，RDBMS 会对这个SQL 语句进行hash 运算，然后产生一个hash 值， 在拿这个hash 值与shared pool 里已经存在的SQL 进行hash 值比较，如果相同，就重用之前SQL 的cursor，如果没有，就需要进行硬解析。
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://www.cndba.cn/Dave/article/1537
这个只是其中的一种情况。
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
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
Note:557661.1Rolling Cursor Invalidations with DBMS_STATS in Oracle10g (Doc ID 557661.1)
（48）OPTIMIZER_MODE_MISMATCH
optimizermode mismatch
（49）PX_MISMATCH
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参数值设置有关。
Oracle cursor_sharing 参数 详解
http://www.cndba.cn/Dave/article/1543
附件一: 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;
/