Data … as usual

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

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 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.