oracle v$access执行很慢,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 */ ;