Re: Tuning

From: gdb_dsr <ge_2000_2001_at_yahoo.com>
Date: 31 Oct 2003 15:35:23 -0800
Message-ID: <6299d152.0310311535.1ba8c33b@posting.google.com>

update was only statement executed on node1. Second instance on node2 wasnt doing anything.

I see enqueue, db file sequential read take most of the time. I did configure anough buffers

db_block_size=16384
db_block_buffers = 10000

background_dump_dest=/opt/oracle/product/9ir2/rdbms/log
core_dump_dest=/opt/oracle/product/9ir2/rdbms/log
user_dump_dest=/opt/oracle/product/9ir2/rdbms/log

timed_statistics=TRUE
control_files=("/opt/oracle/oradata/rac/control01.ctl", "/opt/oracle/oradata/rac/control02.ctl") db_name=RAC
shared_pool_size=314572800
sort_area_size=10000000
undo_management=AUTO
cluster_database=true
cluster_database_instances=2
remote_listener=LISTENERS_RAC
instance_name=RAC2
instance_number=2
local_listener=LISTENER_RAC2
thread=2
undo_tablespace=UNDOTBS2
compatible = 9.2.0.0.0
buffer_pool_keep = 200M
buffer_pool_recycle = 50M

processes                       = 250
sessions                        = 400
db_files                        = 512
dml_locks                       = 10000
cursor_sharing                  = EXACT
open_cursors                    = 500
session_cached_cursors          = 200
enqueue_resources               = 32000

db_file_multiblock_read_count = 32

no other users and/or load on it.

t (c1 int primary key, c2 to c10 varchar2(300) ) cache. No indexes.

Trace report with

update t1 set c2=upper(c2)
where
c1<100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 22

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

Waited ----------
enqueue 268 0.49 128.05 global cache cr request 3 0.00 0.00 KJC: Wait for msg sends to complete 9913 0.05 1.56 global cache open x 9896 0.00 0.19 db file sequential read 19799 0.02 24.38 free buffer waits 409 0.56 102.47 global cache null to x 1 0.00 0.00 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 10.65 10.65

Row Source Operation

-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID FILE$
      1   INDEX RANGE SCAN I_FILE2 (object id 42)

I dont see any locks around either, vmstat shows it reads and writes 10% of
disk io capacity.

"Niall Litchfield" <n-litchfield_at_audRows it-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 - 17:35:23 CST