11g新特性:X$DBGALERTEXT一个很酷的内部视图

以往我们为了管理数据库的便利,总是希望能够利用SQL查询Oracle关键的Alert.log告警日志,在11g以前可以通过创建Alert.log内容的外部表来实现查询日志内容的目的的。

 

从11g 开始引入了新的ADR(Automatic Diagnostic Repository ADR is a file-based repository for database diagnostic data, such as traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more. It has a unified directory structure across multiple instances and multiple products stored outside of any database. It is,therefore, available for problem diagnosis when the database is down.)自动诊断仓库特性,默认的Alert.log转换为LOG.XML的形式,默认存放在$ADR_HOME/alert&trace目录下,并且为日志条目增加了如Level之类的属性,使得告警日志能够为Oracle Support提供更多有用的信息。

 

 

得益于XML格式的日志,可以更方便地将数据库告警日志内容转换为行列数。 所以从11g开始 , 我们甚至于不需要去手动创建外部表了,Oracle 直接提供了X$DBGALERTEXT 这一内部视图来方便我们访问Alert文本:

 

 

SQL>desc X$DBGALERTEXT;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 ORIGINATING_TIMESTAMP                              TIMESTAMP(3) WITH TIME ZONE
 NORMALIZED_TIMESTAMP                               TIMESTAMP(3) WITH TIME ZONE
 ORGANIZATION_ID                                    VARCHAR2(64)
 COMPONENT_ID                                       VARCHAR2(64)
 HOST_ID                                            VARCHAR2(64)
 HOST_ADDRESS                                       VARCHAR2(46)
 MESSAGE_TYPE                                       NUMBER
 MESSAGE_LEVEL                                      NUMBER
 MESSAGE_ID                                         VARCHAR2(64)
 MESSAGE_GROUP                                      VARCHAR2(64)
 CLIENT_ID                                          VARCHAR2(64)
 MODULE_ID                                          VARCHAR2(64)
 PROCESS_ID                                         VARCHAR2(32)
 THREAD_ID                                          VARCHAR2(64)
 USER_ID                                            VARCHAR2(64)
 INSTANCE_ID                                        VARCHAR2(64)
 DETAILED_LOCATION                                  VARCHAR2(160)
 PROBLEM_KEY                                        VARCHAR2(64)
 UPSTREAM_COMP_ID                                   VARCHAR2(100)
 DOWNSTREAM_COMP_ID                                 VARCHAR2(100)
 EXECUTION_CONTEXT_ID                               VARCHAR2(100)
 EXECUTION_CONTEXT_SEQUENCE                         NUMBER
 ERROR_INSTANCE_ID                                  NUMBER
 ERROR_INSTANCE_SEQUENCE                            NUMBER
 VERSION                                            NUMBER
 MESSAGE_TEXT                                       VARCHAR2(2048)
 MESSAGE_ARGUMENTS                                  VARCHAR2(128)
 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(128)
 SUPPLEMENTAL_DETAILS                               VARCHAR2(128)
 PARTITION                                          NUMBER
 RECORD_ID                                          NUMBER

SQL> set linesize 100 pagesize 1400
SQL> select originating_timestamp, message_group, problem_key, message_text
  2    from X$DBGALERTEXT
  3   where message_text like '%ORA-00600%'
  4     and rownum < 10;

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_GROUP
----------------------------------------------------------------
PROBLEM_KEY
----------------------------------------------------------------
MESSAGE_TEXT
----------------------------------------------------------------------------------------------------
10-NOV-11 03.15.52.025 AM -05:00
Generic Internal Error
ORA 600
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc  (incident=11106):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

10-NOV-11 03.15.54.882 AM -05:00
Generic Internal Error
ORA 600
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc  (incident=11108):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.13.07.918 AM -05:00
Generic Internal Error
ORA 600 [4194]
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_smon_4689.trc  (incident=16965):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.13.11.260 AM -05:00

Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_smon_4689.trc:
ORA-01595: error freeing extent (3) of rollback segment (10))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.16.56.765 AM -05:00
Generic Internal Error
ORA 600 [4194]
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_4935.trc  (incident=16997):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.17.00.852 AM -05:00
Generic Internal Error
ORA 600 [ORA-00600: internal error code, arguments: [4194], [],
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_4935.trc  (incident=16998):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.20.07.859 AM -05:00
Generic Internal Error
ORA 600 [4194]
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_5029.trc  (incident=16999):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.20.09.654 AM -05:00
Generic Internal Error
ORA 600 [ORA-00600: internal error code, arguments: [4194], [],
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_5029.trc  (incident=17000):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.22.43.632 AM -05:00
Generic Internal Error
ORA 600 [4194]
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_m000_5137.trc  (incident=17141):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

 

X$DBGALERTEXT的内容极其丰富,包括message记录发生的日期、发生问题的进程号、trace日志的位置、问题的关键(problem key),可以为我们分析数据库日志提供极为强大的接口,节约大量的时间。

 

X$DBGALERTEXT, that allows the log.xml file to be queried like a table. The X$DBGALERTEXT table is effectively indexed by the date the alert was raised so queries against this table based on a date range, which we are doing here, is efficient and performant.

In 11g, X$DBGALERTEXT now exists as a table. You can query the table for the specific columns needed.

 

 

 

但是请注意X$DBGALERTEXT的数据实际来源于ADR中的log.xml文件, 若xml被删除或者内容缺失均会影响到X$DBGALERTEXT的信息完整性:

 

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn & www.askmac.cn

SQL> select count(*) from X$DBGALERTEXT;

  COUNT(*)
----------
     51740

SQL> ! echo > /s01/orabase/diag/rdbms/vprod/VPROD1/alert/log.xml

SQL> select count(*) from X$DBGALERTEXT;

  COUNT(*)
----------
     34005

SQL> ! echo > /s01/orabase/diag/rdbms/vprod/VPROD1/alert/log_1.xml      

SQL>  select count(*) from X$DBGALERTEXT;

  COUNT(*)
----------
         0

 

可以看到X$DBGALERTEXT的数据来源于 log.xml及其归档, 在11g中 当log.xml的大小超过10MB时,Oracle会将其内容归档到如log_$N.xml这样的归档文件中,并清空当前的log.xml的内容。

 

通过X$DBGALERTEXT内部视图接口, 我们可以实现一些原本想都不敢想的日志分析目的, 例如我要找出最近30天内出现过的Internal Errors:

 

SQL> select originating_timestamp, message_text
  2    from X$DBGALERTEXT
  3   where originating_timestamp > sysdate - 30
  4     and message_group = 'Generic Internal Error';

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-DEC-11 03.12.10.998 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc
 (incident=27673):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.12.13.105 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc
 (incident=27674):

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.12.14.107 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc
 (incident=27675):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-DEC-11 03.12.16.017 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc
 (incident=27676):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.40.25.562 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_mmon_30374.trc
 (incident=29076):

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.40.26.989 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_mmon_30374.trc
 (incident=29077):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-DEC-11 03.40.29.020 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30561.trc
 (incident=29244):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.40.31.595 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30561.trc
 (incident=29245):

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.41.17.338 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30640.trc
 (incident=29260):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-DEC-11 03.41.18.668 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30640.trc
 (incident=29261):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

10-DEC-11 01.45.51.556 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ckpt_1026.trc
(incident=33838):

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [],
[], [], [], [], [], [], []

10-DEC-11 01.45.52.304 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ckpt_1026.trc
(incident=33839):
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [],
[], [], [], []

 

或者我要审计最近2天内管理员执行过的DDL语句:

 

SQL> select originating_timestamp, message_text
  2    from X$DBGALERTEXT
  3   where originating_timestamp > sysdate - 2
  4     and message_group = 'admin_ddl';

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
07-JAN-12 02.05.48.770 AM -05:00
alter database open

07-JAN-12 02.05.48.781 AM -05:00
ORA-1531 signalled during: alter database open...

 

Metalink文档How to – Monitor Non Critical 11g Database Alert Log Errors Using a SQL UDM [ID 961682.1] 介绍了一种利用X$DBGALERTEXT实现计算告警日志信息度量的方法。

 

Oracle Freelist上对于X$DBGALERTEXT在近期也有精彩的讨论,值得移步一看

Oracle内部视图:x$targetrba

SQL> desc x$targetrba
 Name
 -----------------------------------------
 ADDR                               N/A

 INDX                               N/A

 INST_ID                            N/A

 TOTALLOGSZ                         select sum(bytes)/redo_standard_size from v$log

 LGLOGSZ                            select bytes/512 from v$Log where status='CURRENT'

 CUR_EST_RCV_READS                  Number of dirty buffers in the buffer cache.
                                    In the Standard Edition, this column is always null.       

 ACTUAL_REDO_BLKS                   Current actual number of redo blocks required for recovery           

 TARGET_RBA_SEQ                     target redo block address log sequence    

 TARGET_RBA_BNO                     target redo block address block number

 TARGET_RBA_BOF                     target redo block address offset

 MIN_LAG                            Current target number of redo blocks that must be processed for recovery.
                                    This value is the minimum value of the following 3 columns,
                                    and identifies which of the 3 user-defined limits determines checkpointing.               

 LOGFILESZ                          Maximum number of redo blocks required to guarantee that a
                                    log switch does not occur before the checkpoint completes.  

 CT_LAG                             Number of redo blocks that need to be processed during
                                    recovery to satisfy the LOG_CHECKPOINT_TIMEOUT parameter.
                                    The value displayed is not meaningful unless that parameter has been set.

 CI_LAG                             Number of redo blocks that need to be processed during recovery to
                                    satisfy the LOG_CHECKPOINT_INTERVAL parameter.
                                    The value displayed is not meaningful unless that parameter has been set.

Oracle内部视图:x$ktfbfe

x$ktfbue:kernel transaction, file bitmap free extents,Free extent bitmap in file header for LMT (equivalent to fet$ in DMT); check dba_free_space view definition,ktfb –space/spcmgmt support for bitmapped space manipulation of files/tablespaces

KTFBFE means K[Kernel] T[Transaction] F[File] B[Bitmap] F[Free] E[Extents]

SQL>  desc x$ktfbfe;
 Name                                     
 ----------------------------------------- 
 ADDR               N/A                            
 INDX               N/A                      
 INST_ID            N/A                         
 KTFBFETSN          TS#  containing this free extent                       
 KTFBFEFNO          Relative number of the file containing the free extent                             
 KTFBFEBNO          Starting block number of the free extent                                
 KTFBFEBLKS         Size of the extent in ORACLE blocks

Oracle内部视图:x$ktfbue

x$ktfbue:kernel transaction, file bitmap used extent,used extent bitmap in file header for LMT (equivalent to uet$ in DMT); check dba_extents view definition,ktfb –space/spcmgmt support for bitmapped space manipulation of files/tablespaces

KTFBUE means K[Kernel] T[Transaction] F[File] B[Bitmap] U[Used] E[Extents]


SQL> desc x$ktfbue;

Name	        Meaning
ADDR	        N/A
INDX	        N/A
INST_ID	        N/A
KTFBUESEGTSN	ts# containing this segment
KTFBUESEGFNO	Relative number of the file containing the segment header
KTFBUESEGBNO	segment header block number
KTFBUEEXTNO	Extent number
KTFBUEFNO	Relative number of the file containing the extent
KTFBUEBNO	Starting block number of the extent
KTFBUEBLKS	Size of the extent in ORACLE blocks
KTFBUECTM	commit_jtime,Commit Time of the undo in the extent expressed as Julian date
KTFBUESTT	commit_wtime,Commit Time of the undo in the extent expressed as wall clock time
KTFBUESTA	Transaction Status of the undo in the extent;1, 'ACTIVE', 2, 'EXPIRED', 3, 'UNEXPIRED', 0 for non-undo-extent

Latches and Tuning:The Library Cache

1. The shared pool is determined by the value of SHARED_POOL_SIZE as indicated in the init.ora file.  The library
cache is part of the shared pool.

a.  Determine the size of the shared poool by executing the following query:> select name,value from v$system_parameter where name = ‘shared_pool_size’;

The shared pool size is part of the VARIABLE SIZE value returned by querying V$SGA.

2.  The hidden parameter _KGL_BUCKET_COUNT determines the initial size of the hash table.  Typically, you
will accept the default value and not change this parameter.  The maximum value is 8.

a.  Determine the initial size of the hash table by executing the following query:> select ksppinm, ksppity from x$ksppi where ksppinm  = ‘_kgl_bucket_count’;

3. Object types are stored in a namespace.  While there can be 32 different namespaces, objects of the same
type will always be stored in the same namespace.

a.  Determine the namespaces allocated in the library cache by executing the following query:> select namespace from v$librarycache;

The number of namespaces are subject to increase at any time.  Common namespaces are:

CRSR: stores library cache objects of type cursor (shared SQL statements)
TABL/PRCD/TYPE: stores tables, views, sequences, synonyms, and procedure specifications
BODY/TYBD: stores procedure, function, package, and type bodies
INDX: stores librarcy cache objects of type index
TRGR: stores librarcy cache objects of type trigger
CLST: stores librarcy cache objects of type cluster

4.  Object tables are maintained for each object.  While the contents of each X$ table are fairly obscure, you can
query them to derive information about objects.

a.  Describe and/or query one of the following tables.X$KGLDP: (Dependency Table) One entry for each object that this object depends on.  For example, a view would depend on underlying tables or views.
X$KGLTR: (Translation Table) Contains records explaining how names referenced by this object were resolved to base objects.
X$KGLAU: (Authorization Table) Contains entries for each privilege defined on the object.
X$KGLXS: (Access Table) One or more entries for each entry in the dependency table.
X$KGLRD: (Read-only dependency table) Like the dependency table but for read only objects.
X$KGLSN: (Schema Name table) Only cursors have schema name tables and they store the schema names for the objects in the authorization table.
(Child Table) One entry for each object that is a child of this object.

5.  Remember that there are 32 namespaces in the library cache.  Also, each object has three types of flags; public,
status, and special status.
a.  Determine the number of namespaces by querying the OBJ$ table:

> select distinct(namespace) from obj$;

b. You can see the name of objects, their namespace, and flags by executing the following query.  Since there are many objects the query is limited using the psuedo column rownum.

> select name,namespace,flags from obj$ where flags > 1 and rownum < 10;

6. Locks and pins are used to control acccess to library cache objects. The X$KGLLK table, as indicated by the
letters LK, records locks on library cache objects.

a.  Connect as SYS and query the X$KGLLK table using the following query:> select user_name from x$kgllk where user_name = ‘SCOTT’;

Provided SCOTT is not logged in, this query should return no rows.:

b.  Create a second SQL*PLUS session as SCOTT/TIGER.

c.  Switch to SYS’s SQL*PLUS session and execute the following query:

> select user_name, kglnaobj from x$kgllk where user_name = ‘SCOTT’;

The user SCOTT acquired object handles on the objects DBMS_APPLICATION_INFO and DBMS_OUTPUT.

d. Switch to SCOTT’s SQL*PLUS session and execute the following update statement:

> update dept set dname = ‘TEST’ where deptno = ’10’;

e. Switch to SYS’s SQL*PLUS session and execute the following query:

> select user_name, kglnaobj from x$kgllk where user_name = ‘SCOTT’;

You will see that SCOTT has acquired additional locks as a result of the update statement.

7.  The X$KGLLPN table, as indicated by the letters PN, records pins on library cache objects.  The contents of the
X$KGLPN table are obscure but you may want to take a look at the data.
a.  Describe the X$KGLN table:

> desc X$KGLN

8. Library cache contention can be caused by excessive parsing of SQL statements.

a.  Determine the parse count in the library cache by executing the following query:b.  Create a second SQL*PLUS session as SCOTT/TIGER.c.  Switch to SYS’s SQL*PLUS session and execute the following query:

> select user_name, kglnaobj from x$kgllk where user_name = ‘SCOTT’;

9.  One diagnostic you can use for determing library cache performance is querying the V$LIBRARYCACHE
view.

a.  Execute the following query:> select namespace, gets, gethitratio, pins, pinhitratio, reloads, invalidations
from v$librarycache;

  • NAMESPACE: the different library cache namespaces
  • GETS: the total number of calls to locate and lock an object in the library cache
  • PINS: total number of calls to pin an object heap (to examine and possibly change)
  • GET/PINHITRATIO: ratio of overall requests to successful acquisitions for the GET and PIN calls in the cache
  • RELOADS: object reloads due to being aged out of the library cache
  • INVALIDATIONS: number of times the object was invalidated
  • Tuning Recommendations:

  • Keep the HITRATIOS above 90%
  • Keep the RELOADS to a minimum, ideally close to zero
  • Avoid DDL and minimize user role changes in a busy production environmentto prevent INVALIDATIONS
  • Size the shared pool appropriately so as to avoid objects getting aged out of the library cache
  • Similar SQL statements must be identical to be shared – use bind variables instead of literals
  • 10.  By performing a library cache dump you can gather extensive information about the library cache.  The dump will
    show you all of the namespaces, buckets, librarcy cache statistics, and content of the librarcy cache.  Beware, if you have
    a large database this dump file can be quite large and take a long time to generate.  You may want to select the
    appropriate level 1 – 4, depending upon the information you want to see.

    a.  As user SYS, execute the following query:> alter session set events ‘immediate trace name library_cache level 4’;

    The output will be generated in the USER_DUMP_DEST directory.

  • Level 1: dump libracy cache statistics
  • Level 2: include a hash table histogram; each bucket has one asterisk for each included handle
  • Level 3: include the dump of the object handles
  • Level 4: include the dump of the object structures (heap 0 only)
  • 11.  The X$KSMLRU fixed table tracks allocations in the shared pool that cause other objects to be aged out.  This
    table can be used to identify what is causing the large allocation.

    a.  Query the X$KSMLRU fixed table:> select * from x$ksmlru where ksmlru > 4000;

    The table contents are deleted after a SELECT.  This is done because the table stores only the largest allocations that have occurred.

    b.  Describe the X$KSMLRU table:

    > desc X$KSMLRU

    KSMLRSIZ: amount of contiguous memory being allocated.  Values over 5KB start to be a problem.
    KSMLRNUM: number of objects that were flushed from the shared pool in order to allocate the memory.
    KSMLRHON: the name of the object being loaded into the shared pool if the object is a PL/SQL object or a cursor.
    KSMLROHV: hash value of object being loaded.
    KSMLRSES: SADDR of the session that loaded the object.

    12.  One way to decrease the load on the library cache latch is to reduce the number of parse calls that are coming into
    the system.

    a.  To identify statements that are receiving a lot of parse calls, execute the following statement:> select sql_text, parse_calls, executions
    from v$sqlarea
    where parse_calls > 100
    and executions< 2 * parse_calls;

    13.  An additional method to tune the library cache is to convert anonymous blocks into packages if possible.
    a.  Find anonymous  blocks by executing the following query (47 is the command for an anonymous PL/SQL block):

    > select sql_text
    from v$sqlarea
    where command_type = 47;

    SQL脚本:监控当前重做日志文件使用情况

    这个脚本可以用来分析当前重做日志文件(redo logfile)已被用到了什么位置(position)、还剩余多少空间和已使用的百分比:

    set linesize 200 pagesize 1400;
    select le.leseq "Current log sequence No",
           100 * cp.cpodr_bno / le.lesiz "Percent Full",
           (cpodr_bno - 1) * 512  "bytes used exclude header",
           le.lesiz * 512 - cpodr_bno * 512 "Left space",
           le.lesiz  *512       "logfile size"
      from x$kcccp cp, x$kccle le
     where LE.leseq = CP.cpodr_seq
       and bitand(le.leflg, 24) = 8;
    

    Sample:

    SQL> set linesize 200 pagesize 1400;
    SQL> select le.leseq "Current log sequence No",
      2         100 * cp.cpodr_bno / le.lesiz "Percent Full",
      3         (cpodr_bno - 1) * 512  "bytes used exclude header",
      4         le.lesiz * 512 - cpodr_bno * 512 "Left space",
      5         le.lesiz  *512       "logfile size"
      6    from x$kcccp cp, x$kccle le
      7   where LE.leseq = CP.cpodr_seq
      8     and bitand(le.leflg, 24) = 8;
    
    Current log sequence No Percent Full bytes used exclude header Left space logfile size
    ----------------------- ------------ ------------------------- ---------- ------------
                        189   90.7612305                  95169536    9687552    104857600
    
    /*  如上结果显示当前重做日志号为189,使用量百分比是90.7%
        当前日志被使用到了95169536+512 bytes(重做日志文件头)的位置,
        还剩余9687552 bytes的空间,该重做日志的总大小为104857600=100MB
    */
    

    Oracle X$ View:X$KJBL

    The status of buffer locks can be checked through the table X$KJBL; it is not necessary to dump the locks with
    the ORADEBUG command, in order to get to this information. The table contains the client and shadow locks.

     

     

    Column name            Data type            Description
    KJBLLOCKP              RAW,kjbl*            Lock pointer. Can be joined with X$LE.LE_KJBL.
    KJBLGRANT              VARCHAR2 (9)         Current grant level, valid values are:
                                                KJUSERNL (0), KJUSERCR (1), KJUSERCW (2), KJUSERPR
                                                (3), KJUSERPW (4), KJUSEREX (5)
    KJBLREQUEST            VARCHAR2 (9)         Request Level, valid values are:
                                                (See KJBLGRANT)
                                                KJBLROLE NUMBER Granted Role:
                                                KJBL_GRANT_N (0x00), KJBL_GRANT_S (0x01)
                                                KJBL_GRANT_X (0x02), KJBL_REQ_S (0x04),
                                                KJBL_REQ_X (0x08), KJBL_REQ_STL (0x10),
                                                KJBL_REQ_CR (0x10), KJBL_OACK (0x20),
                                                KJBL_ROLE_G (0x40), KJBL_HAS_PI (0x80)
                                                
    KJBLRESP               RAW, kjbr*          Current Resource Info (Resource pointer)
    KJBLNAME               VARCHAR2 (30)       Resource name in Hex notation ([id1], [id2], [Type])
    KJBLNAME2              VARCHAR2 (30)       Resource name in Decimal notation (id1, id2, Type)
    KJBLQUEUE              NUMBER              Grant / Convert queue:
                                               Grant Queue (0x00), Convert Queue 0x80
    KJBLLOCKST             VARCHAR2 (64)       Lock state:
                                               KJUSERGRANTED (0x00), KJUSEROPENING (0x01),
                                               KJUSERCLOSING (0x02), KJUSERCANCELLING (0x04),
                                               KJUSERCVTING (0x08), KJUSERSTANDALONE (0x10),
                                               KJUSERASTDELAYED (0x20), KJUSERMSGPENDING(0x40),
                                               KJUSERFROZEN (0x80)
    KJBLWRITING            NUMBER              Writing
                                               ·0, not writing; 1, writing
    KJBLREQWRITE                               Write requested
                                               ·  0, no request; 1, write requested
    KJBLOWNER              NUMBER              Owning instance – node id
    KJBLMASTER             NUMBER              Master instance – node id
    KJBLBLOCKED            NUMBER              Blocked lock? – Checks if there is a converting lock
    KJBLBLOCKER            NUMBER              Blocking lock? - Checks if there is a lock held in an incompatible mode

     

     

    The columns KJBLBLOCKER and KJBLBLOCKED in particular let you determine if you are waiting on
    blocked lock or if you are blocking other sessions. With this table, it is also possible to determine where the
    lock is mastered and the role of the lock.

    Oracle X$ View:X$KJMSDP

    X$KJMSDP

    The table X$KJMSDP has entries for each LMS process that is configured for the instance. The default
    number of LMS processes is the number of CPU’s divided by 4, but 2 as a minimum. The LMS processes are
    responsible for the delivery of the GCS messages and blocks to the other instances.

     

     

    Column name          Data Type               Description
    ADDR                 RAW(4)                  Identifier / Address
    INDX                 NUMBER                  LMS id (starts with 0)
    INST_ID              NUMBER                  Instance id
    PID                  NUMBER                  LMS process id (starts with 0)
    FLAG                 NUMBER                  Flags:
    ACTUAL_RCV           NUMBER                  Actual Global Cache Service messages received
    LOGICAL_RCV          NUMBER                  Logical Global Cache Service messages received
    LOGICAL_PTIME        NUMBER                  Logical Global Cache Service messages process time (ms)
    SBUF_TIME            NUMBER                  Time for flushing send buffer (ms)
    FC_SENT              NUMBER                  Number of flow control messages sent
    NULL_REQ             NUMBER                  Number of null requests send by this process
    WAIT_TICKET          NUMBER                  Number of times waited for tickets
    CRB_SENT             NUMBER                  Number of CR blocks sent
    CRB_STIME            NUMBER                  CR block sent time (ms)
    RCVQ_TIME            NUMBER                  Receive message queue time
    ERRCHK_TIME          NUMBER                  Error check time
    FMSGBUFS_TIME        NUMBER                  Flush message buffer time
    RCFGFRZ_TIME         NUMBER                  Reconfiguration freeze time
    RCFGSYNC_TIME        NUMBER                  Reconfiguration sync time
    PBATFLUSH_TIME       NUMBER                  Process batch flush time
    SQFLUSH_TIME         NUMBER                  Send queue flush time
    DRMSYNC_TIME         NUMBER                  DRM sync time
    RCVMSG_TIME          NUMBER                  Time to receive messages
    BPMSG_TIME           NUMBER                  Batch message process time
    PMSG_TIME            NUMBER                  Time to process message from receiver
    SCANQ_TIME           NUMBER                  Scan queue time
    PDCQ_TIME            NUMBER                  Time to check down-convert queue
    PTOQ_TIME            NUMBER                  Time to check deferred ping queue
    FSCH_TIME            NUMBER                  Time to flush the side-channel messages
    IPBAT_TIME           NUMBER                  Time for embedded batch message processing
    RETRYQ_TIME          NUMBER                  Retry queue time

     

     

    X$KJMSDP
    As with the LMD process, it is important to check if there are waits for tickets. If this occurs, look at the
    corresponding section at the description of the table X$KJMDDP for further explanation.

    [Repost]List of X$ Tables

    This is a summary list of X$ Table Definitions - Last revision was 7.3.2
    The main purpose of this note is to show the naming conventions.
    
    [K]ernel Layer
    [2]-Phase Commit
    [G]lobal [T]ransaction [E]ntry
    X$K2GTE  - Current 2PC tx
    X$K2GTE2 - Current 2PC tx
    [C]ache Layer
    [B]uffer Management
    Buffer [H]ash
    X$BH - Hash Table
    Buffer LRU Statistics
    X$KCBCBH - [C]urrent [B]uffers (buckets) - lru_statistics
    X$KCBRBH - [R]ecent [B]uffers (buckets) - lru_extended
    Buffer [WAIT]s
    X$KCBWAIT  - Waits by block class
    X$KCBFWAIT - Waits by File
    [W]orking Sets - 7.3 or higher
    X$KCBWDS - Set [D]escriptors
    [C]ontrol File Management
    [C]ontrol [F]ile List - 7.0.16 or higher
    X$KCCCF - Control File Names & status
    [D]atabase [I]nformation
    X$KCCDI - Database Information
    Data [F]iles
    X$KCCFE - File [E]ntries ( from control file )
    X$KCCFN - [F]ile [N]ames
    [L]og Files
    X$KCCLE - Log File [E]ntries
    X$KCCLH - Log [H]istory ( archive entries )
    Thread Information
    X$KCCRT - [R]edo [T]hread Information
    [F]ile Management
    X$KCFIO - File [IO] Statistics
    [L]ock Manager Component ( LCK )
    [H]ash and Bucket Tables - 7.0.15 to 7.1.1, and 7.2.0 or higher
    X$KCLFH - File [H]ash Table
    X$KCLFI - File Bucket Table
    X$LE - Lock [E]lements
    X$LE_STAT - Lock Conversion [STAT]istics
    X$KCLFX - Lock Element [F]ree list statistics - 7.3 or higher
    X$KCLLS - Per LCK free list statistics - 7.3 or higher
    X$KCLQN - [N]ame (hash) table statistics - 7.3 or higher
    [R]edo Component
    [M]edia recovery  - kcra.h - 7.3 or higher
    X$KCRMF - [F]ile context
    X$KCRMT - [T]hread context
    X$KCRMX - Recovery Conte[X]t
    [F]ile read
    X$KCRFX - File Read Conte[X]t -  7.3 or higher
    Reco[V]ery Component
    [F]ile [H]eaders
    X$KCVFH - All file headers
    X$KCVFHMRR - Files with [M]edia [R]ecovery [R]equired
    X$KCVFHONL - [ONL]ine File headers
    [K]ompatibility Management - 7.1.1 or higher
    X$KCKCE - [C]ompatibility Segment [E]ntries
    X$KCKTY - Compatibility [TY]pes
    X$KCKFM - Compatibility [F]or[M]ats ( index into X$KCKCE )
    [D]ata Layer
    Sequence [N]umber Component
    X$KDNCE - Sequence [C]ache [E]ntries - 7.2 or lower
    [S]equence Enqueues - common area for enqueue objects
    X$KDNSSC - [C]ache Enqueue Objects - 7.2 or lower
    X$KDNSSF - [F]lush Enqueue Objects - 7.2 or lower
    X$KDNST - Cache [ST]atistics - 7.2 or lower
    Inde[X] Block Component
    X$KDXHS - Index [H]i[S]togram
    X$KDXST - Index [ST]atistics
    [G]eneric Layer
    [H]eap Manager
    X$KGHLU - State (summary) of [L]R[U] heap(s) - defined in ksmh.h
    [I]nstantiation Manager
    [C]ursor [C]ache
    X$KGICC - Session statistics - defined in kqlf.h
    X$KGICS - System wide statistics - defined in kqlf.h
    [L]ibrary Cache Manager  ( defined and mapped from kqlf )
    Bind Variables
    X$KKSBV - Library Object [B]ind [V]ariables
    Object Cache
    X$KGLOB - All [OB]jects
    X$KGLTABLE   - Filter for [TABLE]s
    X$KGLBODY    - Filter for [BODY] ( packages )
    X$KGLTRIGGER - Filter for [TRIGGER]s
    X$KGLINDEX   - Filter for [INDEX]es
    X$KGLCLUSTER - Filter for [CLUSTER]s
    X$KGLCURSOR  - Filter for [CURSOR]s
    Cache Dependency
    X$KGLDP - Object [D]e[P]endency table
    X$KGLRD - [R]ead only [D]ependency table - 7.3 or higher
    Object Locks
    X$KGLLK - Object [L]oc[K]s
    Object Names
    X$KGLNA - Object [NA]mes (sql text)
    X$KGLNA1 - Object [NA]mes (sql text) with newlines - 7.2.0 or higher
    Object Pins
    X$KGLPN - Object [P]i[N]s
    Cache Statistics
    X$KGLST - Library cache [ST]atistics
    Translation Table
    X$KGLTR - Address [TR]anslation
    Access Table
    X$KGLXS - Object Access Table
    Authorization Table - 7.1.5 or higher
    X$KGLAU - Object Authorization table
    Latch Cleanup - 7.0.15 or higher
    X$KGLLC - [L]atch [C]leanup for Cache/Pin Latches
    [K]ompile Layer
    [S]hared Objects
    X$KKSAI - Cursor [A]llocation [I]nformation - 7.3.2 or higher
    [L]oader
    [L]ibrary
    X$KLLCNT - [C]o[NT]rol Statistics
    X$KLLTAB - [TAB]le Statistics
    [M]ulti-Threaded Layer
    [C]ircuit component
    X$KMCQS - Current [Q]ueue [S]tate
    X$KMCVC - [V]irtual [C]ircuit state
    [M]onitor Server/dispatcher
    [D]ispatcher
    X$KMMDI - [D]ispatcher [I]nfo (status)
    X$KMMDP - [D]ispatcher Config ( [P]rotocol info )
    [S]erver
    X$KMMSI - [S]erver [I]nfo ( status )
    X$KMMSG - [SG]a info ( global statistics)
    X$KMMRD - [R]equest timing [D]istributions
    s[Q]l Version and Option Layer
    Kernel [V]ersions
    X$VERSION - Library versions
    Kernel [O]ptions - 7.1.3 or higher
    X$OPTION - Server Options
    [Q]uery Layer
    [D]ictionary Cache Management
    X$KQDPG - [PG]a row cache cursor statistics
    [F]ixed Tables/views Management
    X$KQFCO - Table [CO]lumn definitions
    X$KQFDT - [D]erived [T]ables
    X$KQFSZ - Kernel Data structure type [S]i[Z]es
    X$KQFTA - Fixed [TA]bles
    X$KQFVI - Fixed [VI]ews
    X$KQFVT - [V]iew [T]ext definition - 7.2.0 or higher
    [R]ow Cache Management
    X$KQRST - Cache [ST]atistics
    X$KQRPD - [P]arent Cache [D]efinition - 7.1.5 or higher
    X$KQRSD - [S]ubordinate Cache [D]efinition - 7.1.5 or higher
    [S]ervice Layer
    [B]ackground Management
    [D]etached Process
    X$KSBDD - Detached Process [D]efinition (info)
    X$KSBDP - Detached [P]rocess Descriptor (name)
    X$MESSAGES - Background Message table
    [I]nstance [M]anagement - 7.3 or higher
    X$KSIMAT - Instance [AT]tributes
    X$KSIMAV - [A]ttribute [V]alues for all instances
    X$KSIMSI - [S]erial and [I]nstance numbers
    [L]ock Management
    [E]vent Waits
    X$KSLED - Event [D]escriptors
    X$KSLEI - [I]nstance wide statistics since startup
    X$KSLES - Current [S]ession statistics
    [L]atches
    X$KSLLD - Latch [D]escriptor (name)
    X$KSLLT - Latch statistics [ + Child latches @ 7.3 or higher ]
    X$KSLLW - Latch context ( [W]here ) descriptors - 7.3 or higher
    X$KSLPO - Latch [PO]st statistics - 7.3 or higher
    X$KSLWSC- No[W]ait and [S]leep [C]ount stats by Context -7.3 or higher
    [M]emory Management
    [C]ontext areas
    X$KSMCX - E[X]tended statistics on usage - 7.3.1 or lower
    Heap Areas
    X$KSMSP - SGA Hea[P]
    X$KSMPP - [P]GA Hea[P] - 7.3.2 and above
    X$KSMUP - [U]GA Hea[P] - 7.3.2 and above
    X$KSMHP - Any [H]ea[P] - 7.3.2 and above
    X$KSMSPR- [S]hared [P]ool [R]eserved List - 7.1.5 or higher
    [L]east recently used shared pool chunks
    X$KSMLRU - LR[U] flushes from the shared pool
    [S]GA Objects
    X$KSMSD - Size [D]efinition for Fixed/Variable summary
    X$KSMSS - Statistics (lengths) of SGA objects
    SGA [MEM]ory
    X$KSMMEM - map of the entire SGA - 7.2.0 or higher
    X$KSMFSV - Addresses of [F]ixed [S]GA [V]ariables - 7.2.1 or higher
    [P]arameter Component
    X$KSPPI  - [P]arameter [I]nfo ( Names )
    X$KSPPCV - [C]urrent Session [V]alues - 7.3.2 or above
    X$KSPPSV - [S]ystem [V]alues - 7.3.2 or above
    En[Q]ueue Management
    X$KSQDN - Global [D]atabase [N]ame
    X$KSQEQ - [E]n[Q]ueue Object
    X$KSQRS - Enqueue [R]e[S]ource
    X$KSQST - Enqueue [S]tatistics by [T]ype
    [U]ser Management
    [C]ost
    X$KSUCF - Cost [F]unction (resource limit)
    [L]icence
    X$KSULL - Licence [L]imits
    [L]anguage Manager
    X$NLS_PARAMETERS - NLS parameters
    X$KSULV - NLS [V]alid Values - 7.1.2 or higher
    [MY] [ST]atistics
    X$KSUMYSTA - [MY] [ST]atisics (current session)
    [P]rocess Info
    X$KSUPL - Process (resource) [L]imits
    X$KSUPRLAT - [LAT]ch Holder
    X$KSUPR - Process object
    [R]esource
    X$KSURU - Resource [U]sage
    [S]tatistics
    X$KSUSD - [D]escriptors (statistic names)
    X$KSUSGSTA - [G]lobal [ST]atistics
    [SE]ssions
    X$KSUSECST - Session status for events
    X$KSUSESTA - Session [STA]tistics
    X$KSUSECON - [CON]nection Authentication - 7.2.1 or higher
    X$KSUSE - [SE]ssion Info
    X$KSUSIO - [S]ystem [IO] statistics per session
    [T]imer
    X$KSUTM - Ti[M]e in 1/100th seconds
    Instance [X]
    X$KSUXSINST - [INST]ance state
    [T]race management
    X$TRACE - Current traced events
    X$TRACES - All possible traces
    X$KSTEX - Code [EX]ecution - 7.2.1 or higher
    E[X]ecution Management
    Device/Node [A]ffinity - 7.3.2 and above
    X$KSXAFA - Current File/Node Affinity
    [T]ransaction Layer
    Table [A]ccess [D]efinition
    X$KTADM - D[M]L lock
    [C]ontrol Component
    X$KTCXB - Transaction O[B]ject
    [S]or[T] Segments - 7.3 or higher
    X$KTSTSSD - [S]ort [S]egment [D]escriptor - per tablespace statistics
    [T]ablespace
    X$KTTVS - [V]alid [S]aveundo
    [U]ndo
    X$KTURD - Inuse [D]escriptors
    X$KTUXE - Transaction [E]ntry (table) - 7.3.2 or above
    Performance Layer [V] - 7.0.16 or higher
    [I]nformation tables
    X$KVII - [I]nitialisation Instance parameters
    X$KVIS - [S]izes of structure elements
    X$KVIT - [T]ransitory Instance parameters
    Security Layer [Z]
    [D]ictionary Component
    X$KZDOS - [OS] roles
    [S]ecurity State
    X$KZSPR - Enabled [PR]ivileges
    X$KZSRO - Enabled [RO]les
    [R]emote Logins - 7.1.1 or higher
    X$KZSRT - [R]emote Password File [T]able entries
    E[X]ecution Layer
    Parallel Query (Execute [F]ast) - 7.1.1 or higher
    [P]rocess and Queue Manager
    Statistics - 7.1.3 or higher
    X$KXFPYS - S[YS]tem Statistics
    X$KXFPDP - [D]etached [P]rocess (slave) statistics
    X$KXFQSROW - Table [Q]ueue Statistics - 7.3.2 or higher
    [C]oordinator Component
    X$KXFPCST - Query [ST]atistics
    X$KXFPCMS - [M]essage [S]tatistics
    X$KXFPCDS - [D]equeue [S]tatistics
    [S]lave Component
    X$KXFPSST - Query [ST]atistics
    X$KXFPSMS - [M]essage [S]tatistics
    X$KXFPCDS - [D]equeue [S]tatistics
    [S]hared Cursor
    X$KXSBD - [B]ind [D]ata - 7.3.2 and above
    X$KXSCC - SQL [C]ursor [C]ache Data - 7.3.2 and above
    [N]etwork Layer - 7.0.15 or higher
    Network [CO]nnections
    X$UGANCO - Current [N]etwork [CO]nnections
    
    

    Fixed X$ Tables in ASM

    From Vinod Haval‘s <Inside Overview of ASM Metadata>
    These Views helps in understanding the following metrics

    • Physical Mapping
    • Provides Undocumented Information
    • 18 X$ Tables (May be more)
    TABLE NAME DESCRIPTION
    X$KFALS This table gives the details about aliases 

    created in ASM

    X$KFCBH This is similar to x$kfbh and have same number 

    of rows as x$kfbh

    X$KFCCE This table helps to locate the particular block
    X$KFBH This table gives more physical block level info
    X$KFDSK_STAT This table provides the usage metrics data which can be used for performance analysis
    X$KFGRP This table provides the disk groups info in ASM
    X$KFGRP_STAT This table gives the usage metrics data for all the disk groups within the ASM
    X$KFGMG This table provides the details about ASM operations
    TABLE NAME DESCRIPTION
    X$KFKID This table provides the info about ASM disks
    X$KFNCL This is similar to x$kfbh and have same number 

    of rows as x$kfbh

    X$KFTMTA This table provides the info about DB instance 

    connected to ASM instance

    X$KFFIL This table gives more physical block level info
    X$KFFXP This table provides the physical extent allocation mapping info within ASM files
    X$KFDAT
    X$KFDPARTNER
    X$KFCLLE

    沪ICP备14014813号-2

    沪公网安备 31010802001379号