| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning
this is my st.
update t1 set c2=upper(c2) where c1<100000;
I found more info in enqueue wait event which is the top wait event.
SQL> select * from v$enqueue_stat where total_wait# > 0;
INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME ---------- -- ---------- ----------- ---------- ----------- -------------
2 CF 17755 69 17755 0 140
2 DL 31 19 31 0 8
2 DR 7 5 7 0 0
2 HW 263 10 263 0 5
2 IA 1 1 1 0 0
2 IR 5 2 5 0 0
2 MR 46 1 46 0 1
2 PI 33 17 33 0 18
2 PS 300 300 300 0 116
2 RT 4 4 3 1 2
2 TA 64 64 64 0 25
INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME
---------- -- ---------- ----------- ---------- ----------- -------------
2 TM 97 3 97 0 0
2 TT 259 47 259 0 30
2 TX 111 15 111 0 735287
2 US 7182 6466 7182 0 9706
15 rows selected.
TX makes most of the wait, or the range of rows should be small.
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3f9e460d$0$246$ed9e5944_at_reading.news.pipex.net>...
> 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 Fri Oct 31 2003 - 18:52:28 CST
 |
 |