KFED,KFOD,AMDU in oracle11g ASM

KFOD =Kernel Files Osm Disk
KFED =Kernel Files metadata EDitor
AMDU = ASM Metadata Dump Utility


kfed,kfod is available from Oracle Database 11g Release 1 and forward and can be used to read and write ASM metadata, in particular disk headers and ASM metadata contents. kfed in write mode is a powerful but can be potentially destructive tool in the wrong hands. kfed invoked with the -help option displays the online help for the utility.

KFOD confirm that DISK header is working fine or not .
KFOD is used during installation time also (by OUI,DBCA or ASMCA) perform disk discovery


# $GRID_HOME/bin/kfod status=TRUE asm_diskstring=’/dev/raw/raw*’ disk=all dscvgroup=TRUE
——————————————————————————–
Disk Size Header Path Disk Group User Group
================================================================================
1: 512000 Mb MEMBER /dev/raw/raw1 DATA ora11g dba
2: 512000 Mb PROVISIONED /dev/raw/raw2 DATA ora11g dba
3: 512000 Mb MEMBER /dev/raw/raw3 DATA ora11g dba
4: 512000 Mb PROVISIONED /dev/raw/raw4 DATA ora11g dba
5: 512000 Mb MEMBER /dev/raw/raw5 DATA ora11g dba
6: 512000 Mb MEMBER /dev/raw/raw6 DATA ora11g dba
——————————————————————————–
ORACLE_SID ORACLE_HOME
================================================================================
+ASM /oracle/grid11g



Kfed utility is used to read /repair disk :It allows to analyze ASM disk header information when ASM diskgroups can not be mounted.


# $GRID_HOME/bin/kfed read /dev/raw/raw2

kfbh.endian:                          1 ; 0×000: 0×01
kfbh.hard:                          130 ; 0×001: 0×82
kfbh.type:                            1 ; 0×002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0×003: 0×01
kfbh.block.blk:                       0 ; 0×004: blk=0
kfbh.block.obj:              2147483648 ; 0×008: disk=0
kfbh.check:                   945480537 ; 0x00c: 0x385ae359
kfbh.fcn.base:                        0 ; 0×010: 0×00000000
kfbh.fcn.wrap:                        0 ; 0×014: 0×00000000
kfbh.spare1:                          0 ; 0×018: 0×00000000
kfbh.spare2:                          0 ; 0x01c: 0×00000000
kfdhdb.driver.provstr:ORCLDISK ; 0×000: length=32
kfdhdb.driver.reserved[0]:     33686018 ; 0×008: 0×02020202
kfdhdb.driver.reserved[1]:     33686018 ; 0x00c: 0×02020202
kfdhdb.driver.reserved[2]:     33686018 ; 0×010: 0×02020202
kfdhdb.driver.reserved[3]:     33686018 ; 0×014: 0×02020202
kfdhdb.driver.reserved[4]:     33686018 ; 0×018: 0×02020202
kfdhdb.driver.reserved[5]:     33686018 ; 0x01c: 0×02020202
kfdhdb.compat:                186646528 ; 0×020: 0x0b200000
kfdhdb.dsknum:                        0 ; 0×024: 0×0000
kfdhdb.grptyp:                        1 ; 0×026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0×027: KFDHDR_MEMBER
kfdhdb.dskname:               DATA_0000 ; 0×028: length=9
kfdhdb.grpname:                    DATA ; 0×048: length=4
kfdhdb.fgname:                DATA_0000 ; 0×068: length=9
kfdhdb.capname:                         ; 0×088: length=0
kfdhdb.crestmp.hi:             32983952 ; 0x0a8: HOUR=0×10 DAYS=0x1c MNTH=0×2 YEAR=0x7dd
kfdhdb.crestmp.lo:           3911025664 ; 0x0ac: USEC=0×0 MSEC=0×361 SECS=0×11 MINS=0x3a
kfdhdb.mntstmp.hi:             32984464 ; 0x0b0: HOUR=0×10 DAYS=0xc MNTH=0×3 YEAR=0x7dd
kfdhdb.mntstmp.lo:           3973637120 ; 0x0b4: USEC=0×0 MSEC=0×239 SECS=0xd MINS=0x3b
kfdhdb.secsize:                     512 ; 0x0b8: 0×0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0×1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0×00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                  512000 ; 0x0c4: 0x0007d000
kfdhdb.pmcnt:                         6 ; 0x0c8: 0×00000006
kfdhdb.fstlocn:                       1 ; 0x0cc: 0×00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0×00000002
kfdhdb.f1b1locn:                      2 ; 0x0d4: 0×00000002
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0×0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0×0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0×0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0×0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32983952 ; 0x0e4: HOUR=0×10 DAYS=0x1c MNTH=0×2 YEAR=0x7dd
kfdhdb.grpstmp.lo:           3909777408 ; 0x0e8: USEC=0×0 MSEC=0x29e SECS=0×10 MINS=0x3a
kfdhdb.vfstart:                       0 ; 0x0ec: 0×00000000
kfdhdb.vfend:                         0 ; 0x0f0: 0×00000000
kfdhdb.spfile:                        0 ; 0x0f4: 0×00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0×00000000
kfdhdb.ub4spare[0]:                   0 ; 0x0fc: 0×00000000
kfdhdb.ub4spare[1]:                   0 ; 0×100: 0×00000000
kfdhdb.ub4spare[2]:                   0 ; 0×104: 0×00000000
kfdhdb.ub4spare[3]:                   0 ; 0×108: 0×00000000
kfdhdb.ub4spare[4]:                   0 ; 0x10c: 0×00000000
kfdhdb.ub4spare[5]:                   0 ; 0×110: 0×00000000
kfdhdb.ub4spare[6]:                   0 ; 0×114: 0×00000000
kfdhdb.ub4spare[7]:                   0 ; 0×118: 0×00000000
kfdhdb.ub4spare[8]:                   0 ; 0x11c: 0×00000000
kfdhdb.ub4spare[9]:                   0 ; 0×120: 0×00000000
kfdhdb.ub4spare[10]:                  0 ; 0×124: 0×00000000
kfdhdb.ub4spare[11]:                  0 ; 0×128: 0×00000000
kfdhdb.ub4spare[12]:                  0 ; 0x12c: 0×00000000
kfdhdb.ub4spare[13]:                  0 ; 0×130: 0×00000000
kfdhdb.ub4spare[14]:                  0 ; 0×134: 0×00000000
kfdhdb.ub4spare[15]:                  0 ; 0×138: 0×00000000
kfdhdb.ub4spare[16]:                  0 ; 0x13c: 0×00000000
kfdhdb.ub4spare[17]:                  0 ; 0×140: 0×00000000
kfdhdb.ub4spare[18]:                  0 ; 0×144: 0×00000000
kfdhdb.ub4spare[19]:                  0 ; 0×148: 0×00000000
kfdhdb.ub4spare[20]:                  0 ; 0x14c: 0×00000000
kfdhdb.ub4spare[21]:                  0 ; 0×150: 0×00000000
kfdhdb.ub4spare[22]:                  0 ; 0×154: 0×00000000
kfdhdb.ub4spare[23]:                  0 ; 0×158: 0×00000000
kfdhdb.ub4spare[24]:                  0 ; 0x15c: 0×00000000
kfdhdb.ub4spare[25]:                  0 ; 0×160: 0×00000000
kfdhdb.ub4spare[26]:                  0 ; 0×164: 0×00000000
kfdhdb.ub4spare[27]:                  0 ; 0×168: 0×00000000
kfdhdb.ub4spare[28]:                  0 ; 0x16c: 0×00000000
kfdhdb.ub4spare[29]:                  0 ; 0×170: 0×00000000
kfdhdb.ub4spare[30]:                  0 ; 0×174: 0×00000000
kfdhdb.ub4spare[31]:                  0 ; 0×178: 0×00000000
kfdhdb.ub4spare[32]:                  0 ; 0x17c: 0×00000000
kfdhdb.ub4spare[33]:                  0 ; 0×180: 0×00000000
kfdhdb.ub4spare[34]:                  0 ; 0×184: 0×00000000
kfdhdb.ub4spare[35]:                  0 ; 0×188: 0×00000000
kfdhdb.ub4spare[36]:                  0 ; 0x18c: 0×00000000
kfdhdb.ub4spare[37]:                  0 ; 0×190: 0×00000000
kfdhdb.ub4spare[38]:                  0 ; 0×194: 0×00000000
kfdhdb.ub4spare[39]:             254613 ; 0×198: 0x0003e295
kfdhdb.ub4spare[40]:                  0 ; 0x19c: 0×00000000
kfdhdb.ub4spare[41]:                  0 ; 0x1a0: 0×00000000
kfdhdb.ub4spare[42]:                  0 ; 0x1a4: 0×00000000
kfdhdb.ub4spare[43]:                  0 ; 0x1a8: 0×00000000
kfdhdb.ub4spare[44]:                  0 ; 0x1ac: 0×00000000
kfdhdb.ub4spare[45]:                  0 ; 0x1b0: 0×00000000
kfdhdb.ub4spare[46]:                  0 ; 0x1b4: 0×00000000
kfdhdb.ub4spare[47]:                  0 ; 0x1b8: 0×00000000
kfdhdb.ub4spare[48]:                  0 ; 0x1bc: 0×00000000
kfdhdb.ub4spare[49]:                  0 ; 0x1c0: 0×00000000
kfdhdb.ub4spare[50]:                  0 ; 0x1c4: 0×00000000
kfdhdb.ub4spare[51]:                  0 ; 0x1c8: 0×00000000
kfdhdb.ub4spare[52]:                  0 ; 0x1cc: 0×00000000
kfdhdb.ub4spare[53]:                  0 ; 0x1d0: 0×00000000
kfdhdb.acdb.aba.seq:                  0 ; 0x1d4: 0×00000000
kfdhdb.acdb.aba.blk:                  0 ; 0x1d8: 0×00000000
kfdhdb.acdb.ents:                     0 ; 0x1dc: 0×0000
kfdhdb.acdb.ub2spare:             43605 ; 0x1de: 0xaa55

The header information was corrupted. kfed output for the disk affected shows 0xaa55 on kfdhdb.acdb.ub2spare field. I backed up the header information using dd command.

# dd if=/dev/raw/raw2 of=/tmp/DATA.dd bs=1M count=10

I got the allocation unit size of damaged disk.

# $GRID_HOME/bin/kfed read /dev/raw/raw2 | grep ausize

Now we can repair damaged header information.

# $GRID_HOME/bin/kfed repair /dev/raw/raw2 aus=1048576

I rechecked the header information of damaged disk using kfed utility. Now, kfed output for the disk affected shows 0×0000 on kfdhdb.acdb.ub2spare field

# $GRID_HOME/bin/kfed read /dev/raw/raw2

kfbh.endian:                          1 ; 0×000: 0×01
kfbh.hard:                          130 ; 0×001: 0×82
kfbh.type:                            1 ; 0×002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0×003: 0×01
kfbh.block.blk:                       0 ; 0×004: blk=0
kfbh.block.obj:              2147483648 ; 0×008: disk=0
kfbh.check:                   945480537 ; 0x00c: 0x385ae359
kfbh.fcn.base:                        0 ; 0×010: 0×00000000
kfbh.fcn.wrap:                        0 ; 0×014: 0×00000000
kfbh.spare1:                          0 ; 0×018: 0×00000000
kfbh.spare2:                          0 ; 0x01c: 0×00000000
kfdhdb.driver.provstr:ORCLDISK ; 0×000: length=32
kfdhdb.driver.reserved[0]:     33686018 ; 0×008: 0×02020202
kfdhdb.driver.reserved[1]:     33686018 ; 0x00c: 0×02020202
kfdhdb.driver.reserved[2]:     33686018 ; 0×010: 0×02020202
kfdhdb.driver.reserved[3]:     33686018 ; 0×014: 0×02020202
kfdhdb.driver.reserved[4]:     33686018 ; 0×018: 0×02020202
kfdhdb.driver.reserved[5]:     33686018 ; 0x01c: 0×02020202
kfdhdb.compat:                186646528 ; 0×020: 0x0b200000
kfdhdb.dsknum:                        0 ; 0×024: 0×0000
kfdhdb.grptyp:                        1 ; 0×026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0×027: KFDHDR_MEMBER
kfdhdb.dskname:               DATA_0000 ; 0×028: length=9
kfdhdb.grpname:                    DATA ; 0×048: length=4
kfdhdb.fgname:                DATA_0000 ; 0×068: length=9
kfdhdb.capname:                         ; 0×088: length=0
kfdhdb.crestmp.hi:             32983952 ; 0x0a8: HOUR=0×10 DAYS=0x1c MNTH=0×2 YEAR=0x7dd
kfdhdb.crestmp.lo:           3911025664 ; 0x0ac: USEC=0×0 MSEC=0×361 SECS=0×11 MINS=0x3a
kfdhdb.mntstmp.hi:             32984464 ; 0x0b0: HOUR=0×10 DAYS=0xc MNTH=0×3 YEAR=0x7dd
kfdhdb.mntstmp.lo:           3973637120 ; 0x0b4: USEC=0×0 MSEC=0×239 SECS=0xd MINS=0x3b
kfdhdb.secsize:                     512 ; 0x0b8: 0×0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0×1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0×00100000
kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize:                  512000 ; 0x0c4: 0x0007d000
kfdhdb.pmcnt:                         6 ; 0x0c8: 0×00000006
kfdhdb.fstlocn:                       1 ; 0x0cc: 0×00000001
kfdhdb.altlocn:                       2 ; 0x0d0: 0×00000002
kfdhdb.f1b1locn:                      2 ; 0x0d4: 0×00000002
kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0×0000
kfdhdb.redomirrors[1]:                0 ; 0x0da: 0×0000
kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0×0000
kfdhdb.redomirrors[3]:                0 ; 0x0de: 0×0000
kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi:             32983952 ; 0x0e4: HOUR=0×10 DAYS=0x1c MNTH=0×2 YEAR=0x7dd
kfdhdb.grpstmp.lo:           3909777408 ; 0x0e8: USEC=0×0 MSEC=0x29e SECS=0×10 MINS=0x3a
kfdhdb.vfstart:                       0 ; 0x0ec: 0×00000000
kfdhdb.vfend:                         0 ; 0x0f0: 0×00000000
kfdhdb.spfile:                        0 ; 0x0f4: 0×00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0×00000000
kfdhdb.ub4spare[0]:                   0 ; 0x0fc: 0×00000000
kfdhdb.ub4spare[1]:                   0 ; 0×100: 0×00000000
kfdhdb.ub4spare[2]:                   0 ; 0×104: 0×00000000
kfdhdb.ub4spare[3]:                   0 ; 0×108: 0×00000000
kfdhdb.ub4spare[4]:                   0 ; 0x10c: 0×00000000
kfdhdb.ub4spare[5]:                   0 ; 0×110: 0×00000000
kfdhdb.ub4spare[6]:                   0 ; 0×114: 0×00000000
kfdhdb.ub4spare[7]:                   0 ; 0×118: 0×00000000
kfdhdb.ub4spare[8]:                   0 ; 0x11c: 0×00000000
kfdhdb.ub4spare[9]:                   0 ; 0×120: 0×00000000
kfdhdb.ub4spare[10]:                  0 ; 0×124: 0×00000000
kfdhdb.ub4spare[11]:                  0 ; 0×128: 0×00000000
kfdhdb.ub4spare[12]:                  0 ; 0x12c: 0×00000000
kfdhdb.ub4spare[13]:                  0 ; 0×130: 0×00000000
kfdhdb.ub4spare[14]:                  0 ; 0×134: 0×00000000
kfdhdb.ub4spare[15]:                  0 ; 0×138: 0×00000000
kfdhdb.ub4spare[16]:                  0 ; 0x13c: 0×00000000
kfdhdb.ub4spare[17]:                  0 ; 0×140: 0×00000000
kfdhdb.ub4spare[18]:                  0 ; 0×144: 0×00000000
kfdhdb.ub4spare[19]:                  0 ; 0×148: 0×00000000
kfdhdb.ub4spare[20]:                  0 ; 0x14c: 0×00000000
kfdhdb.ub4spare[21]:                  0 ; 0×150: 0×00000000
kfdhdb.ub4spare[22]:                  0 ; 0×154: 0×00000000
kfdhdb.ub4spare[23]:                  0 ; 0×158: 0×00000000
kfdhdb.ub4spare[24]:                  0 ; 0x15c: 0×00000000
kfdhdb.ub4spare[25]:                  0 ; 0×160: 0×00000000
kfdhdb.ub4spare[26]:                  0 ; 0×164: 0×00000000
kfdhdb.ub4spare[27]:                  0 ; 0×168: 0×00000000
kfdhdb.ub4spare[28]:                  0 ; 0x16c: 0×00000000
kfdhdb.ub4spare[29]:                  0 ; 0×170: 0×00000000
kfdhdb.ub4spare[30]:                  0 ; 0×174: 0×00000000
kfdhdb.ub4spare[31]:                  0 ; 0×178: 0×00000000
kfdhdb.ub4spare[32]:                  0 ; 0x17c: 0×00000000
kfdhdb.ub4spare[33]:                  0 ; 0×180: 0×00000000
kfdhdb.ub4spare[34]:                  0 ; 0×184: 0×00000000
kfdhdb.ub4spare[35]:                  0 ; 0×188: 0×00000000
kfdhdb.ub4spare[36]:                  0 ; 0x18c: 0×00000000
kfdhdb.ub4spare[37]:                  0 ; 0×190: 0×00000000
kfdhdb.ub4spare[38]:                  0 ; 0×194: 0×00000000
kfdhdb.ub4spare[39]:                  0 ; 0×198: 0×00000000
kfdhdb.ub4spare[40]:                  0 ; 0x19c: 0×00000000
kfdhdb.ub4spare[41]:                  0 ; 0x1a0: 0×00000000
kfdhdb.ub4spare[42]:                  0 ; 0x1a4: 0×00000000
kfdhdb.ub4spare[43]:                  0 ; 0x1a8: 0×00000000
kfdhdb.ub4spare[44]:                  0 ; 0x1ac: 0×00000000
kfdhdb.ub4spare[45]:                  0 ; 0x1b0: 0×00000000
kfdhdb.ub4spare[46]:                  0 ; 0x1b4: 0×00000000
kfdhdb.ub4spare[47]:                  0 ; 0x1b8: 0×00000000
kfdhdb.ub4spare[48]:                  0 ; 0x1bc: 0×00000000
kfdhdb.ub4spare[49]:                  0 ; 0x1c0: 0×00000000
kfdhdb.ub4spare[50]:                  0 ; 0x1c4: 0×00000000
kfdhdb.ub4spare[51]:                  0 ; 0x1c8: 0×00000000
kfdhdb.ub4spare[52]:                  0 ; 0x1cc: 0×00000000
kfdhdb.ub4spare[53]:                  0 ; 0x1d0: 0×00000000
kfdhdb.acdb.aba.seq:                  0 ; 0x1d4: 0×00000000
kfdhdb.acdb.aba.blk:                  0 ; 0x1d8: 0×00000000
kfdhdb.acdb.ents:                     0 ; 0x1dc: 0×0000
kfdhdb.acdb.ub2spare:                 0 ; 0x1de: 0×0000

Now, we can mount DATA diskgroup successfully.

I recommend that you should add ASM disk header information backup to your current backup plan.

I did not take a backup of damaged ASM disk header information before. So how was the recovery process above? There is a second copy of ASM disk header information. kfed repairs the corrupted block from second copy. If our second copy of the header information was corrupted also then we could repair the header information using backup.




AMDU = ASM Metadata Dump Utility

How to upgrade dataguard primary and standby databases

When you have dataguard setup in 10g version, would like to upgrade both primary & standby to 11g 
The idea is that you install the 11g binaries on both primary and standby;
shutdown the primary and the standby;
mount the standby off the 11g home,
 start the MRP; upgrade the primary.
 So the standby gets upgraded implicitly, by applying the redo generated during the upgrade of the primary.

Automatic db_file_multiblock_read_count (MBRC)

Automatic db_file_multiblock_read_count:

The default value corresponds to the maximum I/O size that can be efficiently performed and is platform-dependent

DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

As of Oracle Database 10g release 2, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size.
 

According to Oracle, this is the formula for setting db_file_multiblock_read_count:

                                 max I/O chunk size
db_file_multiblock_read_count  = -------------------
                                 db_block_size

MaxMultiblockread count=1MB/8K = 128 (approxmately)

Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.

Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.
The maximum value is the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.

The "real" mbrc is actually somewhere in between since serial multiblock read requests are processed by the buffer cache and split in two or more requests if some blocks are already pinned in the buffer cache, or when the segment size is smaller than the read size.

The mbrc value gathered as part of workload statistics is thus useful for FTS estimation."

One of the new features that was released in 10gR2 is the automatic DB_FILE_MULTIBLOCK_READ_COUNT

The DB_FILE_MULTIBLOCK_READ_COUNT parameter controls the amount of block prefetching done in the buffer cache during scan operations, such as full table scan and index fast full scan. The value of this parameter can have a significant impact on the overall database performance. This feature enables Oracle Database to automatically select the appropriate value for this parameter depending on the operating system optimal I/O size and the size of the buffer cache.
This feature simplifies manageability by automating the tuning of DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.

This parameter specifies the number of blocks that are read in a single I/O during a full table scan or index fast full scan. The optimizer uses the value of DB_FILE_MULTIBLOCK_READ_COUNT to cost full table scans and index fast full scans. Larger values result in a cheaper cost for full table scans and can result in the optimizer choosing a full table scan over an index scan. If this parameter is not set explicitly (or is set is 0), the optimizer will use a default value of 8 when costing full table scans and index fast full scans.

Statistics collection in oracle 11gR2

Oracle has addressed 2 major issues in statistics gathering. 1) Automatic statistics collection 2) Speed and accuracy by changing the sample data 1) Automatic statistics collection: In 11gR2 statistics collection is automatic by default.The statistics will be collected by using an auto job, if the statistics found to be stale for an object or if any DDL command used against the object. Please see the below for the job details. select client_name,status,consumer_group,attributes from DBA_AUTOTASK_CLIENT CLIENT_NAME STATUS CONSUMER_GROUP ------------ --------- -------------- auto optimzer stat collection ENABLED ORA$AUTOTASK_STATS_GROUP The stale percent is determined based on the number of DML operations occured on the table ie., the percentage of number of rows updated or inserted or deleted on the table. By default the threshold limit for the stale percentage is 10%. You can check this values by using the below query. select dbms_stats.GET_PREFS ('STALE_PERCENT','OWNER','TABLENAME') from dual. To check the objects considered for auto stats collection we can check the parameter AUTOSTATS_TARGET.The value of this parameter controls the objects considered for stats collection. The values: 'ALL' - Statistics collected for all objects in system 'ORACLE' - Statistics collected for all Oracle owned objects 'AUTO' - Oracle decides on which objects to collect statistics We can check this by using the below query select dbms_stats.GET_PREFS ('AUTOSTATS_TARGET') from dual; DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET') ------------------------------------------- AUTO To check the percentage of rows modified or inserted or deleted on the tables you can use the below query select * from DBA_TAB_MODIFICATIONS where table_owner='SCOTT' If you need to check the information about stats collection on your tables, you can use the below query select * from DBA_TAB_STATS_HISTORY where owner='SCOTT' select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='%stats%'; To enable or disable the Auto stats job please use the below procs. To enable or disable the Auto stats job please use the below procs. ENABLE: BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / DISABLE: BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%'; SELECT * FROM dba_autotask_client_history WHERE client_name like '%stats%'; Select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='auto optimizer stats collection'; 2) Speed and accuracy by changing the sample data: Looking back to the previous major release (10.2.0) the methodology for statistics gathering relied basically on the sample size; the larger the sample size the better the statistics at the cost of increased run time to gather those statistics. Oracle also introduced the auto sample size default, which determined the sample size to use for each table. Using the default settings did produce statistics in a shorter length of time, but did so at the expense of inaccurate histograms, plainly wrong histograms, and inaccurate density values. Partitioned tables were the most often affected when sample sizes smaller than 90% were used; the auto sample size Oracle computed often hit the 30% mark leaving histogram values woefully in need of adjustment. Even though Oracle would adjust the sampled values up at the table level (to more accurately reflect the actual number of rows) the histogram values remained at the actual sampled size throwing density/selectivity calculations off by a considerable margin and adversely affecting performance based on cost calculations. Oracle 11.2.0 has corrected these issues with a better methodology that not only increases the speed of statistics collection but also provides greater accuracy of the results. An improved sampling algorithm along with a reduction of the full table scans executed has resulted in more accurate statistics in a shorter amount of time, and better statistics definitely mean better execution plans. That isn't the only improvement in 11.2.0 statistics as the histograms generated have accurate numbers, better reflecting the state of the table data distribution. Partitioned tables no longer cause Oracle to generate spurious histograms such as the frequency histograms on single-valued, non-null columns making CBO calculations more accurate. Better statistics mean better plans and less of a headache for the users and the DBAs. Notice also that the auto sample size in 11.2.0 generates statistics percentages very close to 100, indicating that the new auto sampling algorithm will run faster yet produce results rivaling a 100% sample size and do that in approximately 1/10th of the time. The less time a DBA spends on statistics is more time to address other, more pressing, issues. That's a good thing no matter how you look at it.