Gathering System Statistics

• System Statistics can be collected and displayed for CBO to use and apprehend CPU and system I/O information. For each plan candidate, the optimizer computes estimates for I/O and CPU costs.
• Invoke the dbms_stats.gather_system_stats procedure as an elapsed time capture, making sure to collect the statistics during a representative heavy workload
• How to collect  these  statistics.easist way  of  it  is
  
  execute dbms_stats.gather_system_stats('Start');
  -- one hour delay during high workload
  execute dbms_stats.gather_system_stats('Stop');
• 
  
   Here are the data items collected by dbms_stats.gather_system_stats: we  can query it  from  aux_stats$ system view
  
  select * from aux_stats$;
  No Workload (NW) stats::

  CPUSPEEDNW - CPU speed

  IOSEEKTIM - The I/O seek time in milliseconds

  IOTFRSPEED - I/O transfer speed in milliseconds

  Workload-related stats:

  SREADTIM - Single block read time in milliseconds

  MREADTIM - Multiblock read time in ms

  CPUSPEED - CPU speed

  MBRC - Average blocks read per multiblock read

  MAXTHR - Maximum I/O throughput

  SLAVETHR - OPQ Factotum (slave)

• If the hardware or workload has changed these  statistics must be  refreshed.
• If  the workload  differs  from day to  nigth  (run OLTP during the day and DSS at night) then  statistics can be  exported  to  a table and  One  job may be   scheculed  to  import  statistics  before  begining  of  night  and   day

See also:
Gather Optimizer Statistics For Sys

Gather Optimizer Statistics For Sys

Gathering statistics for sys objects

• It is recommended to run gather statistics reqularly, specifically if you are using Oracle APPS and also after upgrades or running catalog scripts
• To gather the dictionary stats run One of the following
  SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;
  SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
  SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

Gathering statistics for X$ tables (v$ views )

• Gather_fixed_objects_stats would gather statistics for dynamic tables e.g. the X$ tables which loaded in SGA during the startup. Gathering statistics for fixed objects would normally if we have poor performance in querying the dynamic views e.g. V$ views.
• Fixed objects record current database activity; statistics gathering should be done when database has representative activity.
• To gather the fixed objects stats
  EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

See also :
Gathering System Statistics

cache buffers chains

If "cache buffer chains" lacth is intensive then

1-Check if "cache buffer chains" exists
--If it is top in top latches


select substr(name,1,40),gets,misses*100/decode(gets,0,1,gets) misses,
spin_gets*100/decode(misses,0,1,misses) spins, immediate_gets igets,
immediate_misses*100/decode(immediate_gets,0,1,immediate_gets) imisses
from v$latch order by gets + immediate_gets
/

2-Check latch holders


set numwidth 5
select distinct lh.inst_id, s.sid, s.username, p.username os_user, lh.name
from gv$latchholder lh, gv$session s, gv$process p
where (lh.sid = s.sid and lh.inst_id = s.inst_id)
and (s.inst_id = p.inst_id and s.paddr = p.addr)
order by lh.inst_id, s.sid
/

3-List the latches and note the latch# number of most latch

SELECT latch#, substr(name,1,40), gets, misses, sleeps
FROM v$latch WHERE sleeps>0
ORDER BY sleeps ;

4-Find the latch child and note the addr of most used child

SELECT addr, latch#, gets, misses, sleeps
FROM v$latch_children
WHERE sleeps>100 and latch# = &LATCH_NUMBER_WANTED
ORDER BY sleeps ;
5-Find the file# number and block number of hot block

SELECT File# , dbablk, class, state ,tch
FROM x$bh WHERE hladdr='&ADDR_OF_CHILD_LATCH' order by tch;

6-Find the hot block

SELECT distinct owner, segment_name, segment_type
FROM dba_extents
WHERE file_id= &FILE_ID
and &BLOCK_NUMBER between block_id and block_id+blocks-1
/