oracle cursor： pin s wait on x （二）
cursor:pin s on x(二）第二种产生cursor:pin s on x的原因是version countWhat is shared SQL ?The first thing to remember is that all SQL is implicitly sharable. When a SQL statement is entered, the RDBMS will
cursor:pin s on x(二）
第二种产生cursor:pin s on x的原因是version count
What is shared SQL ?
The first thing to remember is that all SQL is implicitly sharable. When a SQL statement is entered, the RDBMS will create a hash value for text of the statement and that hash value then helps the RDBMS to easily find SQL already in the shared pool. It is not in the scope of this article to discuss this in any great detail, so let's just assume entering a series of text results in a hash value being created.
For instance :- 'select count(*) from emp' hashes to the value 4085390015
We now create a parent cursor for this sql and a single child. It does not matter that a SQL statement may never be shared - when it is first parsed a parent and a single child are created. The easy way to think of this is that the PARENT cursor is a representation of the hash value and the child cursor(s) represent the metadata for that SQL
What is 'SQL Metadata'?
Metadata is all the information which enables a statement to run. For instance, in the example I have given EMP is owned by scott and therefore has an OBJECT_ID which points to the EMP table owned by this user. When the user SCOTT logged in, optimizer parameters are initialised in that session for use by the statement, so this too is used by the optimizer and is therefore metadata. There are other examples of Metadata which will be mentioned further in this document.
Let's say this session logs out and back in again now. It then runs the same command again (as the same user). This time we already have the SQL in the shared pool (but we don't know this yet). What we do is hash the statement and then search for that hash value in the shared pool. If we find it, we can then search through the children to determine if any of them are usable by us (ie the metadata is the same). If it is, then we can share that SQL statement
I would still have one version of that SQL in the shared pool because the metadata enabled me to share the statement with the already existent child. The fundementals are that the parent is not shared, it is the children which determine shareability.
Now - another user 'TEST' has it's own version of EMP. If that user was to now run the select statement above then what would happen is :-
1. The statement is hashed - it is hashed to the value 4085390015
2. The SQL will be found in the shared pool as it already exists
3. The children are scanned (at this point we have one child)
4. Because the OBJECT_ID of the EMP table owned by TEST is different the OBJECT_ID owned by scott we have a 'mismatch'
(Essentially, what happens here is that we have a linked list of children which we move through in turn, comparing the metadata of the current SQL with that of all the children. If there were 100 children then we would scan each of them (looking for a possible mismatch and moving on) until we found one we could share. If we cannot share any (ie. have exhausted the list of children) then we need to create a new child)
5. We therefore have to create a new child - we now have 1 PARENT and 2 CHILDREN.
Why should I be concerned about 'High' Versions?
Unnecessary non-sharing of SQL, and the resultant versions of SQL, is a primary cause of library cache contention. Contention reduces the performance of your database and, in extreme cases, can cause it to appear to 'hang'. When you have unnecessary versions of a cursor, each time that cursor is executed, the parse engine has to search through the list of versions to see which is the cursor that you want. This wastes CPU cycles that you could be using on something else.
怎么查找该问题，1可以通过awr的SQL ordered by Version Count进行查看，默认该收集信息是大于20的version count，且可以关联ash进行问题定位。
另外查看到的问题，可以通过关联v$sql_shared_cursor视图获得该sql不能共享的原因。另外如下是一个脚本收集数据库中version count过高的问题情况。
###################################################################
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
,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_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(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('Version Count Report Version 3.2.2 -- 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_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 since 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.
*/
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_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);
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) in ('BIND_MISMATCH','USER_BIND_PEEK_MISMATCH','BIND_EQUIV_FAILURE','BIND_UACS_DIFF')
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 :');
PIPE ROW('BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF 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;
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 (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;
if v_version like '11%' 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);
eg:
Generate reports for all cursors with more than 100 versions using SQL_ID (10g and up)
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
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
SELECT * FROM TABLE(version_rpt('cyzznbykb509s'));
##############################################################################
session1:
QL> show user
USER is "SCOTT"
SQL> select * from emp;
session2:
SQL> show user
USER is "RHYS"
SQL> select * from emp;
1* select sql_text,sql_id,hash_value,loaded_versions,plan_hash_value,child_number from v$sql where sql_text like 'select * from emp%'
SQL_TEXT SQL_ID HASH_VALUE LOADED_VERSIONS PLAN_HASH_VALUE CHILD_NUMBER
-------------------------------------------------- ------------- ---------- --------------- --------------- ------------
select * from emp a2dk8bdn0ujx7 1745700775 1 3956160932 0
select * from emp a2dk8bdn0ujx7 1745700775 1 3956160932 1
SQL>
SQL> select sql_text,sql_id,version_count,loaded_versions,hash_value,plan_hash_value from v$sqlarea where sql_text like 'select * from emp%';
SQL_TEXT SQL_ID VERSION_COUNT LOADED_VERSIONS HASH_VALUE PLAN_HASH_VALUE
-------------------------------------------------- ------------- ------------- --------------- ---------- ---------------
select * from emp a2dk8bdn0ujx7 2 2 1745700775 3956160932
SQL> select sql_id,address,child_address,child_number,translation_mismatch,auth_check_mismatch from v$sql_shared_cursor where sql_id='a2dk8bdn0ujx7';
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER TRANSLATION_MISMATCH AUTH_CHECK_MISMATCH
------------- ---------------- ---------------- ------------ -------------------- -------------------
a2dk8bdn0ujx7 0000000060AB7280 0000000075DDF700 0 N N
a2dk8bdn0ujx7 0000000060AB7280 000000007BED7448 1 Y Y
SQL>
可以看到不同用户对应的cursor认证检查不能通过，不同用户的不同对象存在不同object-id，那么base object 也就不同了。
参考：
You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:
NB Bug Fixed Description 5650841 Hang / deadlock from ANALYZE of cluster index V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 V11020002 DEADLOCK HANG 16191248 12.1.0.1.1, 12.1.0.2, 12.2.0.0 Hang from concurrent drop of on-commit materialized views or using DBMS_REDEFINITION V12010001 DEADLOCK HANG MVIEW 14295250 11.2.0.4, 12.1.0.1 Long parse time for large query with many nested views due to much time in epxression analysis code V11020001 V11020002 V11020003 PARSEPERF 14191508 11.2.0.3.8, 11.2.0.3.BP16, 11.2.0.4, 12.1.0.1 Slow row cache load due to SEG$ and INDSUBPART$ queries V11020001 V11020002 V11020003 OPERF PART 14176247 11.2.0.4, 12.1.0.1 Many child cursors using Adaptive Cursor Sharing with binds (due to BIND_EQUIV_FAILURE) V11010006 V11010007 V11020001 V11020002 V11020003 ACS CBO LEAK/MEM POOL 15850031 11.2.0.4, 12.2.0.0 Rare instance hang: deadlock between 'row cache lock' and 'cursor: pin S wait for X' V11020001 V11020002 V11020003 V12010001 CBO DEADLOCK HANG/DB 14469756 12.2.0.0 Partition pruning causes delay in TBL$OR$IDX$PART$NUM V11020001 V11020002 V11020003 V12010001 PARSEPERF PART QPERF 14302813 11.2.0.4, 12.2.0.0 QC blocked / parse hang for parallel DML executed from remote stored procedure V11020001 V11020002 V11020003 V12010001 DBLINK DPATH HANG PLSQL PQO 14029891 11.2.0.4, 12.1.0.1 mutex deadlock having SQL baselines on recursive dictionary cursor V11010006 V11010007 V11020001 V11020002 V11020003 DBMSPKG DEADLOCK HANG/DB MUTEX SPM 11927619 11.2.0.1.BP11, 11.2.0.2.BP07, 11.2.0.3, 12.1.0.1 DBMS_STATS slow on interval composite partitions V11010006 V11010007 V11020002 DBMSPKG PART PQO RB202 11855965 11.2.0.3, 12.1.0.1 Truncate partition takes long time doing recursive delete on MLOG$ V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 V11020002 MVIEW OPERF PART TRUNCATE 10213073 11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3, 12.1.0.1 CREATE SYNONYM and CREATE PACKAGE may incorrectly invalidate objects V11020002 MISC PLSQL 10171273 11.2.0.2.8, 11.2.0.2.BP08, 11.2.0.3, 12.1.0.1 Long parse time with non-equi subpartitioning under interval partitioning V11010006 V11010007 V11020001 V11020002 PARSEPERF PART QPERF 9944129 11.2.0.1.BP12, 11.2.0.2, 12.1.0.1 SQL not shared due to INST_DRTLD_MISMATCH with global transaction V11010006 V11010007 V11020001 DBLINK LEAK/MEM POOL XA 9935787 11.2.0.3, 12.1.0.1 Long parse time for large inlists - can cause 'cursor: pin S wait on X' waits V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 V11020002 CBO CPU DUMP MUTEX 9694101 10.2.0.5.7, 11.2.0.2, 12.1.0.1 Hang / deadlock between "cursor: pin S wait on X" and "library cache lock" involving dictionary objects V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 CPU DEADLOCK HANG MUTEX 9499302 10.2.0.5.5, 11.1.0.7.7, 11.2.0.1.BP08, 11.2.0.2, 12.1.0.1 Improve concurrent mutex request handling V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 ENH MUTEX 9472669 11.2.0.1.BP12, 11.2.0.2, 12.1.0.1 'cursor: pin S wait on X' waits for invalid SQL over DB link V11010006 V11010007 V11020001 DBLINK HANG MUTEX PERF 8508078 11.2.0.2, 12.1.0.1 Contention from many concurrent bad SQLs - superseded V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 DISABLED PERF 12432089 11.2.0.3 library cache lock/cursor: pin s wait on x with parallel partition stats gatheri V11010006 V11010007 V11020001 V11020002 DBMSPKG MUTEX OPERF PQO 8441239 11.2.0.1 Library cache lock waits if long running TRUNCATE in progress V11010006 V11010007 OPERF PART TRUNCATE 8348464 11.1.0.7.2, 11.2.0.1 CREATE SYNONYM and CREATE PACKAGE may incorrectly invalidate objects V11010006 V11010007 MISC PLSQL 7234778 11.2.0.1 Unnecessary "cursor: pin S wait on X" waits V11010006 V11010007 PERF 5485914 10.2.0.4 Mutex self deadlock on explain / trace of remote mapped SQL V10020002 V10020003 V11010006 V11010007 DEADLOCK PERF/MON 6143420 10.2.0.5, 11.1.0.6 Deadlock involving "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X" V10020002 V10020003 V10020004 DEADLOCK HANG OPS STARTUP 6011045 10.2.0.5.5 DBMS_STATS causes deadlock between 'cursor: pin S wait on X' and 'library cache lock' V10020002 V10020003 V10020004 V10020005 DBMSPKG DEADLOCK HANG OPS 7462072 10.2.0.4.3, 10.2.0.5 Unnecessary "cursor: pin S wait on X" waits V10020002 V10020003 V10020004 MUTEX PERF 5983020 10.2.0.4 MMON deadlock with user session executing ALTER USER V10020002 V10020003 DEADLOCK HANG 7226463 10.2.0.5 EXECUTE IMMEDIATE no releasing mutex or library cache pin V10010005 V10020002 V10020003 V10020004 DEADLOCK HANG HANG/DB HANG/SPIN PLSQL + 5907779 10.2.0.4 Self deadlock hang on "cursor: pin S wait on X" (typically from DBMS_STATS) V10020003 DEADLOCK HANG RA203 RECOMMENDED REGRESSION
- '*' indicates that an alert exists for that issue.
- '+' indicates a particularly notable issue / bug.
- 'I' indicates an install issue / bug included for completeness.
- 'P' indicates a port specific bug.
- Fixed versions use "BPnn" to indicate Exadata bundlenn.
- "OERI:xxxx" may be used as shorthand for ORA-600 [xxxx].
汇聚全球AI编程工具，助力开发者即刻编程。
更多推荐
0
0- 0
已为社区贡献2条内容
所有评论(0)