Data … as usual

All things about data by Laurent Leturgez

Category Archives: Internals

Dump ASM disk header … method #2

In a previous post, I explained how to dump ASM Disk header and how to analyze it. This method, which used kfed binary, is a little bit complex because of fields with a complex name extracted from the internal structure.

Another way to proceed and to see main information about asm disks is to use an undocumented binary located in $ORACLE_HOME/bin named amdu.

If amdu is not available in your $ORACLE_HOME/bin directory, you can build it with the next make command:

[oracle@oel ~]$ make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk iamdu

To dump your ASM diskheader, you just have to execute this command with the -diskstring option and specify the location of your ASM disk devices (default uses ASM lib directory):

[oracle@oel ~]$ amdu -diskstring='/dev/oracleasm/disks/*'
amdu_2012_03_21_11_14_36/

Now, change directory to the output generated and have a look to the report.txt file:

[oracle@oel ~]$ cd amdu_2012_03_21_11_14_36/
[oracle@oel amdu_2012_03_21_11_14_36]$ cat report.txt
-*-amdu-*-
******************************* AMDU Settings ********************************
ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1
System name: Linux
Node name: oel
Release: 2.6.18-274.18.1.0.1.el5
Version: #1 SMP Thu Feb 9 19:07:16 EST 2012
Machine: x86_64
amdu run: 21-MAR-12 11:14:36
Endianess: 1
--------------------------------- Operations ---------------------------------
------------------------------- Disk Selection -------------------------------
 -diskstring '/dev/oracleasm/disks/*'
------------------------------ Reading Control -------------------------------
------------------------------- Output Control -------------------------------
********************************* DISCOVERY **********************************
----------------------------- DISK REPORT N0001 ------------------------------
 Disk Path: /dev/oracleasm/disks/ASM1
 Unique Disk ID:
 Disk Label:
 Physical Sector Size: 512 bytes
 Disk Size: 1019 megabytes
 Group Name: DATA
 Disk Name: DATA_0000
 Failure Group Name: DATA_0000
 Disk Number: 0
 Header Status: 3
 Disk Creation Time: 2011/05/27 10:57:54.822000
 Last Mount Time: 2012/03/21 08:54:10.221000
 Compatibility Version: 0x0b200000(11020000)
 Disk Sector Size: 512 bytes
 Disk size in AUs: 1019 AUs
 Group Redundancy: 1
 Metadata Block Size: 4096 bytes
 AU Size: 1048576 bytes
 Stride: 113792 AUs
 Group Creation Time: 2011/05/27 10:57:54.456000
 File 1 Block 1 location: AU 2
 OCR Present: NO
----------------------------- DISK REPORT N0002 ------------------------------
 Disk Path: /dev/oracleasm/disks/ASM2
 Unique Disk ID:
 Disk Label:
 Physical Sector Size: 512 bytes
 Disk Size: 1019 megabytes
 Group Name: DATA
 Disk Name: DATA_0001
 Failure Group Name: DATA_0001
 Disk Number: 1
 Header Status: 3
 Disk Creation Time: 2011/05/27 10:57:54.822000
 Last Mount Time: 2012/03/21 08:54:10.221000
 Compatibility Version: 0x0b200000(11020000)
 Disk Sector Size: 512 bytes
 Disk size in AUs: 1019 AUs
 Group Redundancy: 1
 Metadata Block Size: 4096 bytes
 AU Size: 1048576 bytes
 Stride: 113792 AUs
 Group Creation Time: 2011/05/27 10:57:54.456000
 File 1 Block 1 location: AU 0
 OCR Present: NO
******************************* END OF REPORT ********************************

As mentioned, now you know if your disk is part of a disk group, the DG Allocation Unit size, disk size, last mounted time etc.

Of course, this tool can be used regarless of the asm instance status.

 

 

Advertisements

Trace Oracle CBO computations for a specific sql_id

In this post, I will explain how to trace CBO computation (aka 10053 event) for a specific sql_id and in another session.

To do this, we need to know two things:

1) How to trace another session? To do this, I will use undocumented oracle tool “oradebug”. More precisely, I will use the new event declaration syntax which is not based on event id.

2) How to trace CBO computation for a specific sql_id? To do this, I will use the new event declaration syntax (more details in the demonstration above)

To demonstrate this trick, I will consider two sessions:

– The first session (S1) is logged as an application user named LAURENT. This user owns two table T1 and T4, and we only want to trace a specific SQL Query (select count(*) from t4 where id between 500 and 550;).

– The second session (S2) is logged as SYS user who will launch oradebug commands.

* S1 (logged as LAURENT)

SQL> select count(*) from t4 where id between 500 and 550;
COUNT(*)
----------
 51

* S2 (logged as SYS) : querying the dictionary to obtain sql_id associated to the SQL query:

SQL> select sql_id,sql_text from v$sql
 2 where sql_text like 'select count(*) from t4 where id between 500 and 550%';
SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
2zg40utr7a08n select count(*) from t4 where id between 500 and 550

* S1 (logged as LAURENT): obtain Oracle PID and system PID (spid) of the session we will trace. This information will be used for oradebug in the next step.

SQL> select pid,spid from v$process
 2 where addr=(select paddr from v$session
 3 where sid=(select sid from v$mystat where rownum=1));
PID        SPID
---------- ---------
25         4850

* S2 (Logged as SYS) : we will use oradebug new features to trace a specific sql_id by using the new syntax for tracing CBO Computations (trace[RDBMS.SQL_Optimizer.*])

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> -- Setting Oracle PID to trace and verify by crossing the result of the system pid
SQL> oradebug setorapid 25
Oracle pid: 25, Unix process pid: 4850, image: oracle@oel (TNS V1-V3)
SQL> -- nolimit to tracefile
SQL> oradebug unlimit
Statement processed.
SQL> -- tracing SQL_Optimizer computation for a specific sql (here's our sql_id)
SQL> oradebug event trace[RDBMS.SQL_Optimizer.*][sql:2zg40utr7a08n]
Statement processed.
SQL> -- obtain the trace file name
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4850.trc

NB : Flushing shared pool is mandatory if you have already shared cursor for the statement to trace in the shared pool.

 

* S1 (Logged as LAURENT) : execute many sql statements in the session including our specific sql statement:

S1 (execute sql_id 2zg40utr7a08n one time, and others sql):
SQL> select count(*) from t4 where id between 500 and 550;
COUNT(*)
----------
 51

SQL> select count(*) from t4 ;
COUNT(*)
----------
 300000

SQL> select count(*) from t1;
COUNT(*)
----------
 294958

Finally, open the tracefile generated, you will only have the CBO computations and statistics for our specific sql_id:

[oracle@oel ~]$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4850.trc
Registered qb: SEL$1 0xe47325b8 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
 signature (): qb_name=SEL$1 nbfros=1 flg=0
 fro(0): flg=4 objn=78460 hint_alias="T4"@"SEL$1"
SPM: statement not found in SMB
**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is disabled: Parameter.
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=2zg40utr7a08n) -----
select count(*) from t4 where id between 500 and 550
*******************************************

.../...

Query Block Registry:
SEL$1 0xe47325b8 (PARSER) [FINAL]
:
 call(in-use=13920, alloc=49184), compile(in-use=88336, alloc=152104), execution(in-use=6016, alloc=8088)
End of Optimizer State Dump
Dumping Hints
=============
====================== END SQL Statement Dump ======================

Update : Bertrand Drouvot has blogged a tricky way to flush a specific sql_id before generating its CBO computation trace file. See link : http://bdrouvot.wordpress.com/2013/09/16/flush-a-single-sql-statement-and-capture-a-10053-trace-for-it/

 

Read rdbms and listener log (xml) from SQL*Plus prompt

Recently I have searched a method to read and filter entries in alert log files (rdbms and listener.log).

A documented method consists in using adrci (ADR command interpreter) but I wanted an easier method, so I searched on the net and found this thread in Tanel Poder’s Blog (http://blog.tanelpoder.com/2009/03/21/oracle-11g-reading-alert-log-via-sql/).

This method seems to answer my questions but it only shows rdbms entries.

So I found another V$ table (undocumented) that resolves my problem : V$DIAG_ALERT_EXT.

This view is based on X$DIAG_ALERT_EXT and contains log entries about rdbms, tnslsnr etc. Now, I just have to write the code to exploit this.

1- First view for rdbms log entries:

create or replace view my_db_alert_log as
select ORIGINATING_TIMESTAMP,HOST_ID,HOST_ADDRESS,DETAILED_LOCATION,MODULE_ID,
CLIENT_ID,PROCESS_ID,USER_ID,MESSAGE_ID,MESSAGE_GROUP,MESSAGE_TEXT,PROBLEM_KEY,FILENAME
from V$DIAG_ALERT_EXT WHERE trim(COMPONENT_ID)='rdbms';

2- Another one for listener log entries:

create or replace view my_lsnr_alert_log as
select ORIGINATING_TIMESTAMP,HOST_ID,HOST_ADDRESS,DETAILED_LOCATION,MODULE_ID,
CLIENT_ID,PROCESS_ID,USER_ID,MESSAGE_ID,MESSAGE_GROUP,MESSAGE_TEXT,PROBLEM_KEY,FILENAME
from V$DIAG_ALERT_EXT WHERE trim(COMPONENT_ID)='tnslsnr';

Off course, you can add every column useful for you 😉

Now, you can query alert.log directly in SQL*Plus (for example, here’s my last hour rdbms alert log file entries):

SQL> select ORIGINATING_TIMESTAMP,DETAILED_LOCATION,MESSAGE_GROUP,MESSAGE_TEXT
  2  from my_db_alert_log
  3  where ORIGINATING_TIMESTAMP> systimestamp - INTERVAL '0 01:00:00.0' DAY TO SECOND(1)
  4  order by 1
  5  /

ORIGINATING_TIMESTAMP                  DETAILED_LOCATION    MESSAGE_GROUP             MESSAGE_TEXT
-------------------------------------- -------------------- ------------------------- --------------------------------------------------
16-NOV-11 05.33.03.090000000 PM +01:00                                                ALTER SYSTEM: Flushing buffer cache
16-NOV-11 05.57.16.259000000 PM +01:00 /u01/app/oracle/diag                           Errors in file /u01/app/oracle/diag/rdbms/db112/db
                                       /rdbms/db112/db112/t                           112/trace/db112_ora_6377.trc  (incident=139371):
                                       race/db112_ora_6377.                           ORA-00700: erreur logicielle interne, arguments :
                                       trc                                            [kgerev1], [600], [600], [700], [], [], [], [], []
                                                                                      , [], [], []

16-NOV-11 05.57.16.262000000 PM +01:00                                                Incident details in: /u01/app/oracle/diag/rdbms/db
                                                                                      112/db112/incident/incdir_139371/db112_ora_6377_i1
                                                                                      39371.trc

16-NOV-11 05.57.16.943000000 PM +01:00 /u01/app/oracle/diag Generic Internal Error    Errors in file /u01/app/oracle/diag/rdbms/db112/db
                                       /rdbms/db112/db112/t                           112/trace/db112_ora_6377.trc  (incident=139372):
                                       race/db112_ora_6377.                           ORA-00600: code d'erreur interne, arguments : [],
                                       trc                                            [], [], [], [], [], [], [], [], [], [], []

16-NOV-11 05.57.16.946000000 PM +01:00                                                Incident details in: /u01/app/oracle/diag/rdbms/db
                                                                                      112/db112/incident/incdir_139372/db112_ora_6377_i1
                                                                                      39372.trc

16-NOV-11 05.57.17.278000000 PM +01:00                                                Dumping diagnostic data in directory=[cdmp_2011111
                                                                                      6175717], requested by (instance=1, osid=6377), su
                                                                                      mmary=[incident=139371].

16-NOV-11 05.57.17.544000000 PM +01:00                                                Use ADRCI or Support Workbench to package the inci
                                                                                      dent.
                                                                                      See Note 411.1 at My Oracle Support for error and
                                                                                      packaging details.

16-NOV-11 05.57.18.425000000 PM +01:00                                                Dumping diagnostic data in directory=[cdmp_2011111
                                                                                      6175718], requested by (instance=1, osid=6377), su
                                                                                      mmary=[incident=139372].

16-NOV-11 05.57.19.201000000 PM +01:00                      ami_comp                  Sweep [inc][139372]: completed
16-NOV-11 05.57.19.220000000 PM +01:00                      ami_comp                  Sweep [inc][139371]: completed
16-NOV-11 05.57.19.222000000 PM +01:00                      ami_comp                  Sweep [inc2][139372]: completed
16-NOV-11 05.57.19.222000000 PM +01:00                      ami_comp                  Sweep [inc2][139371]: completed

12 rows selected.

And for the listener.log

SQL> select ORIGINATING_TIMESTAMP,DETAILED_LOCATION,MESSAGE_GROUP,MESSAGE_TEXT
  2  from my_lsnr_alert_log
  3  where ORIGINATING_TIMESTAMP> systimestamp - INTERVAL '0 01:00:00.0' DAY TO SECOND(1)
  4  order by 1
  5  /

ORIGINATING_TIMESTAMP                  DETAILED_LOCATION    MESSAGE_GROUP             MESSAGE_TEXT
-------------------------------------- -------------------- ------------------------- --------------------------------------------------
16-NOV-11 05.11.24.147000000 PM +01:00                                                16-NOV-2011 17:11:24 * service_update * db112 * 0
16-NOV-11 05.11.54.266000000 PM +01:00                                                16-NOV-2011 17:11:54 * service_update * db112 * 0
16-NOV-11 05.12.24.385000000 PM +01:00                                                16-NOV-2011 17:12:24 * service_update * db112 * 0
16-NOV-11 05.12.54.487000000 PM +01:00                                                16-NOV-2011 17:12:54 * service_update * db112 * 0
16-NOV-11 05.13.24.573000000 PM +01:00                                                16-NOV-2011 17:13:24 * service_update * db112 * 0
16-NOV-11 05.13.54.818000000 PM +01:00                                                16-NOV-2011 17:13:54 * service_update * db112 * 0
16-NOV-11 05.14.25.011000000 PM +01:00                                                16-NOV-2011 17:14:25 * service_update * db112 * 0
16-NOV-11 05.14.28.013000000 PM +01:00                                                16-NOV-2011 17:14:28 * service_update * db112 * 0
16-NOV-11 05.14.55.085000000 PM +01:00                                                16-NOV-2011 17:14:55 * service_update * db112 * 0
16-NOV-11 05.15.28.207000000 PM +01:00                                                16-NOV-2011 17:15:28 * service_update * db112 * 0
16-NOV-11 05.15.46.267000000 PM +01:00                                                16-NOV-2011 17:15:46 * service_update * db112 * 0
16-NOV-11 05.15.52.297000000 PM +01:00                                                16-NOV-2011 17:15:52 * service_update * db112 * 0

Dump ASM disk header

If you want to dump ASM disk header, you can use an Oracle internal tool to obtain information about your disk, diskgroup etc. even if the disk is offline.

This tool is named KFED (Kernel File EDitor). It is fitted by default with an Oracle 11g installation, but you’ll need to build it  if you want to use it with Oracle 10g :

[oracle@oel ~]$ make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk ikfed

Well, now have a closer look to a feature of this tool.

If you want to read information stored on the ASM Disk header, you can use it like this :

[oracle@oel ~]$ kfed read /dev/oracleasm/disks/ASM3 dsk1.dump

Now, you have in the dsk1.dump file the content of your ASM file header “/dev/oracleasm/disks/ASM3”. This file can be easily read by a text editor

 
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: T=0 NUMB=0x0
kfbh.block.obj:              2147483648 ; 0x008: TYPE=0x8 NUMB=0x0
kfbh.check:                  2930000864 ; 0x00c: 0xaea443e0
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:     ORCLDISKASM3 ; 0x000: length=12
kfdhdb.driver.reserved[0]:    860705601 ; 0x008: 0x334d5341
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        2 ; 0x026: KFDGTP_NORMAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:          MIRROR_DG_0000 ; 0x028: length=14
kfdhdb.grpname:               MIRROR_DG ; 0x048: length=9
kfdhdb.fgname:           MIRROR_DG_0000 ; 0x068: length=14
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             32959021 ; 0x0a8: HOUR=0xd DAYS=0x11 MNTH=0xa YEAR=0x7db
kfdhdb.crestmp.lo:           3500063744 ; 0x0ac: USEC=0x0 MSEC=0x3af SECS=0x9 MINS=0x34
kfdhdb.mntstmp.hi:             32959382 ; 0x0b0: HOUR=0x16 DAYS=0x1c MNTH=0xa YEAR=0x7db
kfdhdb.mntstmp.lo:            505578496 ; 0x0b4: USEC=0x0 MSEC=0xa1 SECS=0x22 MINS=0x7
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                    1019 ; 0x0c4: 0x000003fb
kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002
kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn:                      2 ; 0x0d4: 0x00000002
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000

Now, we can read some information about the file : on the structure “kfdhdb”, at the offset 0x048, and coded on 9 bytes, the name of the diskgroup which owns this ASM disk  file.

Most important information are detailed below :

* kfbh.endian: Endian used on this disk : 1 for little endian.

* kfdhdb.driver.provstr: Provision String used for ASM (which means in our case : ORCL:DISKASM3)

* kfdhdb.grptyp: type of diskgroup the disk is attached to.

* kfdhdb.hdrsts: header status. Here, the disk is a member of the diskgroup.

* kfdhdb.dskname: disk name in the disk group

* kfdhdb.grpname: disk group name

* kfdhdb.fgname: failure group name which owns the disk

* kfdhdb.secsize: sector size

* kfdhdb.blksize:  block size

* kfdhdb.ausize: allocation unit size

If you want to rename the diskgroup the disk belongs to, you can edit the dumpfile and use the “merge” command of KFED to apply changes to the disk header.

[oracle@oel ~]$ kfed merge /dev/oracleasm/disks/ASM3 text=dsk1.dump

Be careful when you use the “merge” command because, it seems the diskgroup name, or disk name is coded with a fixed length, so if you change the name, and this one is based on a 4 bytes word, rename it to a 4 bytes word.

Off course, using kfed is not supported by Oracle.

Some things about Statistics …

Every Oracle DBA knows how statistics are very important for the CBO and for database performance.

Oracle have many kind of statistics :

– object statistics : statistics on tables, indexes …

– system statistics: statistics on the system where your database runs : CPU speed, time to perform a single I/O etc.

– dictionary statistics : statistics on the dictionary tables, indexes etc. (OBJ$, TAB$ tables … I_SOURCE1,  I_FILE1 indexes …)

– Fixed objects statistics : statistics on the fixed tables (X$BH etc. A non exhaustive list and explanation of X$ tables can be found here : http://yong321.freeshell.org/computer/x$table.html)

Each statistic is important for the performance of your database and so, it’s important to know where to find information about them.

  • Object statistics

Object statistics are the most known statistics. Information about object statistics can be found in the DBA_ views. For example, LAST_ANALYZED column of the DBA_TABLES, DBA_INDEXES, DBA_TAB_PARTITIONS, DBA_IND_PARTITIONS etc. told us when was last analyzed an object. Other columns like BLOCKS, NUM_ROWS etc. are populated after this analyze.

  • System statistics
System statistics informs the CBO about the performance of the system. For example, information collected are CPU Speed, time to perform a single I/O or a multiblock I/O operation etc.
System statistics are available in the table SYS.AUX_STATS$. System stats can be gathered in a “NOWORKLOAD” mode (default) or in a “WORKLOAD” mode. The No workload mode gathers basic information about the system. WORKLOAD mode gathers more information and give more precise information about the performance of the system.
It’s recommanded to gather system stats in a workload mode during a representative workload phase. For example, if your system have a representative load every tuesday during 9 AM to 12 AM, you can gather system stats during 3 hours every tuesday.
Ex: Content of the AUX_STATS$ table after a  noworkload statistics gathering:
SQL> select * from aux_stats$;

SNAME           PNAME                PVAL1 PVAL2
--------------- --------------- ---------- ------------------
SYSSTATS_INFO   STATUS                     COMPLETED
SYSSTATS_INFO   DSTART                     09-26-2011 11:47
SYSSTATS_INFO   DSTOP                      09-26-2011 11:47
SYSSTATS_INFO   FLAGS                    1
SYSSTATS_MAIN   CPUSPEEDNW         919,606
SYSSTATS_MAIN   IOSEEKTIM             8,17
SYSSTATS_MAIN   IOTFRSPEED       55914,355
SYSSTATS_MAIN   SREADTIM
SYSSTATS_MAIN   MREADTIM
SYSSTATS_MAIN   CPUSPEED
SYSSTATS_MAIN   MBRC
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR
Ex: Content of the AUX_STATS$ table after a  workload statistics gathering of 5 minutes:
SQL> exec dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL',interval=>5);

SQL> select * from aux_stats$;

SNAME           PNAME                PVAL1 PVAL2
--------------- --------------- ---------- ------------------------------
SYSSTATS_INFO   STATUS                     COMPLETED
SYSSTATS_INFO   DSTART                     09-26-2011 11:50
SYSSTATS_INFO   DSTOP                      09-26-2011 11:55
SYSSTATS_INFO   FLAGS                    0
SYSSTATS_MAIN   CPUSPEEDNW         919,606
SYSSTATS_MAIN   IOSEEKTIM             8,17
SYSSTATS_MAIN   IOTFRSPEED       55914,355
SYSSTATS_MAIN   SREADTIM             1,537
SYSSTATS_MAIN   MREADTIM             1,818
SYSSTATS_MAIN   CPUSPEED               920
SYSSTATS_MAIN   MBRC                    19
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR

For more information about what mean PNAME values, just have a look here : http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#i41496

  • Dictionary Statistics
Dictionary statistics are similar to object statistics, but they are specific to SYS object that describe the dictionary. DBMS_STATS.GATHER_DICTIONARY_STATISTICS is the procedure used to gather statistics on these objects. To verify statistics on it, you can proceed like a classical objects except in the name of the object.
Ex:
SQL> select table_name,last_analyzed,blocks,num_rows from dba_tables where table_name in ('OBJ$','TAB$','IND$','FILE$');

TABLE_NAME LAST_ANALYZED                BLOCKS   NUM_ROWS
---------- ------------------------ ---------- ----------
TAB$       26/SEPT./2011 04PM:28:22       1236       2617
OBJ$       26/SEPT./2011 04PM:27:49        862      70094
IND$       26/SEPT./2011 04PM:28:21       1236       4103
FILE$      26/SEPT./2011 04PM:27:46          1          5

NB : Dictionary statistics are automatically gathered during DBMS_STATS.GATHER_DATABASE_STATS which is launched with the automatic optimizer statistic collection task.

  • Fixed objects
Fixed objects are a little bit particular. Indeed, a fixed table (X$) seems to be a kind of C structure mapped into a table. So, fixed tables exist but not really and are not referenced in the dictionary tables. (more information here : http://www.oracle-internals.com/?p=11)
To identify statistics on fixed objects, you have to query dictionary with this query. If there’s no fixed objects statistics gathered, the query will answer 0 line, otherwise you will have one line per fixed object.
SQL> exec dbms_stats.gather_fixed_objects_stats;
SQL> select fo.name, analyzetime, ROWCNT,samplesize
  2  from tab_stats$ t, (select OBJECT_ID,NAME from V$FIXED_TABLE) fo
  3  where t.obj#=fo.object_id
  4  order by 2;

NAME                           ANALYZETIME                  ROWCNT SAMPLESIZE
------------------------------ ------------------------ ---------- ----------
X$KGLJSIM                      26/SEPT./2011 05PM:44:27          4          4
X$KGLJMEM                      26/SEPT./2011 05PM:44:27         68         68
X$KGLAU                        26/SEPT./2011 05PM:44:27        800        800
X$KGLLC                        26/SEPT./2011 05PM:44:27         12         12
X$KGLDP                        26/SEPT./2011 05PM:44:27      14976      14976
X$KGLLK                        26/SEPT./2011 05PM:44:28        831        831
X$KGLMEM                       26/SEPT./2011 05PM:44:28         68         68
X$KGLNA1                       26/SEPT./2011 05PM:44:28      38695      38695
X$KGLNA                        26/SEPT./2011 05PM:44:28      38671      38671
X$KGLOB                        26/SEPT./2011 05PM:44:31      21054      21054
X$KGLPN                        26/SEPT./2011 05PM:44:32         38         38
X$KGLST                        26/SEPT./2011 05PM:44:32         68         68
X$KGLSIM                       26/SEPT./2011 05PM:44:32         14         14
X$KGLRD                        26/SEPT./2011 05PM:44:32       8788       8788
X$KGLSN                        26/SEPT./2011 05PM:44:32        173        173
X$KGLTR                        26/SEPT./2011 05PM:44:32       1206       1206
X$KGLXS                        26/SEPT./2011 05PM:44:33      13029      13029
X$KKSCS                        26/SEPT./2011 05PM:44:33       2311       2311
X$KKSAI                        26/SEPT./2011 05PM:44:33          0          0
X$KKSBV                        26/SEPT./2011 05PM:44:33       3116       3116
X$KQLFBC                       26/SEPT./2011 05PM:44:34       3116       3116
.../...

In a next post, I will show you how system and fixed objects statistics are very important for your database performance.