-
I am running 2 Oracle 7.3.4 databases on AIX (seperate databases but having database links to each other. Users have been complaining of slow response times. On doing some investigation I have found that there are 2 database files in each database which have the majority of I/O reads performed on them.
Looking at v$system_event the avg. figure for the db file sequential read is around 200 centisecs on both databases
I know what disk the filesystem containing the db files is on. Is there any further anlysis I could do to narrow the problem down further?
Thanks in advance
Fraze
-
Yes find out hot objects (tables/indexes), how much sorts are on , find out if buffer cache latches are enough.
u can use v$waitstat,v$session_wait, v$latch.
-
I've had a look at v$waitstat and nearly all of the waits are on data blocks. v$latch shows the most sleeps on shared pool / library cache / cache buffer chains.
How can I find out what the hot tables/indexes are (I'm getting a crash course in tuning today!!)?
-
Ok
v$waitstat:
data block wait is highest
In v$sysstat, do u see high dirty buffers inspected , increase DBWR processes.
v$system_event : db_file_sequential_read is highest, did u see high ebough buffer busy waits and buffer deadlocks.
Increase Freelists .
Also in V$latch_children , check for cache buffer chains , are sleeps divide properly on all chains, if no get ADDR of that chain.
select dbarfil,dbablk,count(*)
from x$bh
where hladdr='838E85EC'
group by dbarfil,dbablk
Get the block(s) with high counts
and from dba_extents , get the object name.
Increase Freelist or change sql code.
Take Care
GP
