Trace one of the sessions of interest using event 10046,level 8. This will
generate a trace file in udump with the sessions activity and what it was
waiting on.
if you run this trace file through tkprof (version 9) with sort=prsela
exeela fchela you should get the most time consuming sqlstatements and what
they waited on at the top of the file.
--
Niall Litchfield
Oracle DBA
Audit Commission Uk
"gdb_dsr" <ge_2000_2001_at_yahoo.com> wrote in message
news:6299d152.0310271636.4c3c10e8_at_posting.google.com...
> Hi,
>
> My system looks OK with normal load, but when more clients
> conect and put more load it doesn't responding quickly.
> 9ir2 rac installed on linux(suse8.0) with scsi shared disks.
>
> I found that there are,
> 'free buffer waits/write complete waits/db file sequential read'
> parameters were not normal. Linux asynclib rpm is installed and I found
> the default async is enabled on oracle.
> I suspect the disks but how do I locate the problem. Any help appreciated.
>
>
> System-wide Wait Analysis
> for current wait events
>
>
Average
> Event Total Seconds Total
Wait
> Name Waits Waiting Timeouts (in
secs)
> ------------------------------ ------------ -------- ------------ --------
-
> gcs remote message 588,609 9,831 477,300
.020
> db file sequential read 152,158 8,879 0
.060
> ges remote message 127,407 4,921 99,837
.040
> CGS wait for IPC msg 120,911 1 120,599
.000
> SQL*Net more data from client 99,718 4 0
.000
> KJC: Wait for msg sends to com 47,967 11 91
.000
> plete
>
> global cache cr request 37,024 16 30
.000
> global cache s to x 34,733 1 2
.000
> PX Deq: reap credit 19,622 0 18,849
.000
> free buffer waits 18,468 14,543 12,045
.790
> global cache open x 16,209 2,317 1,880
.140
> ksxr poll remote instances 10,647 0 7,538
.000
> control file sequential read 9,764 247 0
.030
> write complete waits 9,155 9,084 9,095
.990
> direct path write 7,313 0 0
.000
> db file parallel write 5,752 0 1,674
.000
> buffer busy waits 4,993 6,376 4,678
1.280
> log file parallel write 4,067 0 4,065
.000
> enqueue 3,678 360 1,314
.100
> wait for master scn 3,256 1 0
.000
> row cache lock 2,175 0 0
.000
> control file parallel write 1,682 114 0
.070
> PX Deq: Execution Msg 1,249 71 0
.060
> global cache open s 1,242 0 330
.000
> PX Deq: Txn Recovery Start 918 1,351 376
1.470
> library cache lock 730 0 4
.000
> PX Deq: Join ACK 626 0 319
.000
> PX Deq: Parse Reply 560 0 0
.000
> PX Deq: Execute Reply 474 139 27
.290
> log file switch (checkpoint in 410 405 396
.990
> complete)
>
>
> library cache pin 376 0 0
.000
> IPC send completion sync 312 0 0
.000
> LGWR wait for redo copy 260 0 0
.000
> log buffer space 235 13 0
.050
> rdbms ipc reply 197 1 0
.010
> db file scattered read 184 2 0
.010
> async disk IO 162 0 0
.000
> latch free 153 1 52
.010
> wait for a undo record 140 1 0
.000
> direct path read 126 0 0
.000
> PX Deq: Signal ACK 92 0 31
.000
> log file sync 55 9 3
.170
> PX Deq: Txn Recovery Reply 51 32 51
.620
> wait for stopper event to be i 32 3 30
.090
> ncreased
>
> name-service call wait 30 11 18
.370
> log file sequential read 27 1 0
.020
> ges2 LMON to wake up lms - mrc 24 1 24
.030
> vr 2
>
> log file switch completion 24 12 2
.520
> process startup 19 5 3
.260
> checkpoint completed 17 77 15
4.560
> ges global resource directory 16 0 0
.010
> to be unfrozen
>
> ges cgs registration 15 3 15
.190
> ges LMON to get to FTDONE 15 0 3
.010
> log file single write 13 0 0
.010
> ges lmd/lmses to freeze in rcf 9 0 9
.020
> g - mrcvr
>
> global cache null to x 9 0 0
.010
> ges lmd/lmses to unfreeze in r 8 0 8
.030
> cfg - mrcvr
>
> ges2 LMON to wake up LMD - mrc 8 0 6
.020
> vr
>
> global cache null to s 8 0 0
.000
> ges LMD to inherit communicati 6 0 1
.020
> on channels
>
> ges reconfiguration to start 6 0 6
.030
> db file single write 6 0 0
.010
> ges master to get established 5 0 2
.010
> for SCN op
>
> global cache busy 4 0 0
.010
> ges global resource directory 3 0 3
.030
> to be frozen
>
> wait for tmc2 to complete 3 0 3
.030
> library cache load lock 3 0 0
.010
> ges1 LMON to wake up LMD - mrc 2 0 2
.020
> vr
>
> control file single write 2 0 0
.010
> control file heartbeat 2 8 2
3.990
> buffer busy global CR 2 0 0
.020
> recovery read 2 0 0
.000
> instance state change 2 0 0
.000
> db file parallel read 2 0 0
.060
> lock escalate retry 2 0 2
.000
> reliable message 1 0 0
.000
> ges LMON for send queues 1 0 1
.050
> cr request retry 1 0 1
.000
> buffer busy global cache 1 0 0
.010
> refresh controlfile command 1 0 0
.040
> wait for sync ack 1 1 1
.500
>
>
> Thanks,
> Raju
Received on Tue Oct 28 2003 - 04:33:48 CST
