Oracle bug之v$access

今天在客户数据库查询一表格的访问情况，结果系统出现

引用

SQL> /

EVENT
----------------------------------------------------------------
SQL*Net message to client
latch: library cache

只能将查询语句ctrl+c

引用

SQL> select count(*) from v$access where OWNER='HZ_YC' and OBJECT='W_HZ_QYHZNR';
^Cselect count(*) from v$access where OWNER='HZ_YC' and OBJECT='W_HZ_QYHZNR'
                     *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

metalink一查。又准确命中一bug

引用

Symptoms

Querying v$access leads to contention on library cache and almost freezing database.


Cause

As given in the Bug 5880432 closed as not a bug, this is a expected behaviour.

It is difficult to get a fix in current releases to reduce the latch gets required, as one off fixes
cannot change V$/X$ views.

Oracle提供了一临时解决办法，就是替代v$accsee的创建脚本

引用

REM Example for how it may be possible to reduce latch gets
REM needed to see SGA data like V$ACCESS

REM nahsh() is a function to compute the KGLNAHSH value for a REM given owner, name, namespace (ntyp)
REM  OWNER is upper case owner
REM  NAME is upper case object name
REM  NTYP is numeric object namespace id - typically:
REM             1 for 'TABLE/PROCEDURE',
REM             2 for 'BODY',
REM             3 for 'TRIGGER',
REM             4 for 'INDEX',
REM             5 for 'CLUSTER',
REM             6 for 'OBJECT',
REM             7 for 'PIPE', REM             13 for 'JAVA SOURCE',
REM             14 for 'JAVA RESOURCE',
REM             32 for 'JAVA DATA'
REM This only function only works for little endian platforms (like Linux)
REM so cannot be used on HPUX. REM create or replace function nahsh( owner varchar2, name varchar2, ntyp number)
return number is
n number;
x1 raw(20);
x2 number;
begin
/* Little endian */
n:=dbms_utility.get_sql_hash(
name||'.'||owner||chr(ntyp)||chr(0)||chr(0)||chr(0),x1,x2);
/* Big endian
* In theory this but it gives wrong value so cannot use on big endian  * platforms.
* n:=dbms_utility.get_sql_hash(
*  name||'.'||owner||chr(0)||chr(0)||chr(0)||chr(ntyp),x1,x2);
*/
return(n);
end;
/
REM XX_acccess view like V$ACCESS but exposes NAHSH column
REM which can then be supplied in queries

create or replace view XX_access (
sid,owner,object,type,nahsh
) as select /*+ ORDERED */ distinct s.ksusenum,o.kglnaown,o.kglnaobj, decode(o.kglobtyp,    0, 'CURSOR',    1, 'INDEX',    2, 'TABLE',    3, 'CLUSTER',    4, 'VIEW',
  5, 'SYNONYM',   6, 'SEQUENCE', 7, 'PROCEDURE',    8, 'FUNCTION',      9, 'PACKAGE',    10,'NON-EXISTENT',    11,'PACKAGE BODY',      12,'TRIGGER',    13,'TYPE', 14,'TYPE BODY',    15,'OBJECT',      16,'USER',    17,'DBLINK',    18,'PIPE', 19,'TABLE PARTITION',      20,'INDEX PARTITION',    21,'LOB',    22,'LIBRARY',   23,'DIRECTORY',    24,'QUEUE',    25,'INDEX-ORGANIZED TABLE',      26,'REPLICATION OBJECT GROUP',    27,'REPLICATION PROPAGATOR',      28,'JAVA SOURCE',    29,'JAVA CLASS',    30,'JAVA RESOURCE',      31,'JAVA JAR',    'INVALID TYPE') ,
o.kglnahsh
from x$kglob o,x$kgllk l, x$kgldp d, x$ksuse s
where l.kgllkuse=s.addr and l.kgllkhdl=d.kglhdadr  and l.kglnahsh=d.kglnahsh and o.kglnahsh=d.kglrfhsh  and o.kglhdadr=d.kglrfhdl
and s.inst_id=USERENV('INSTANCE')
;
                                                                               REM Example usage

REM V$ACCESS query
set timing on
select * from v$access where owner='SYS' and object='OBJ$' and type='TABLE'
/


REM Using XX_ACCESS
REM  a. Get a hash value for the query REM     On little endian use the function. On others you need to get
REM     this from a lookup table or some other way.
REM variable n number
exec :n:=nahsh('SYS','OBJ$',1);

REM and run the query which includes NAHSH in the predicates
select * from xx_access where owner='SYS' and object='OBJ$' and type='TABLE'
and nahsh=:n  /* Added predicate to get fixed index access on x$kglob */
;