RE: Session_wait

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Fri, 25 Jan 2002 17:15:38 -0800
Message-ID: <F001.003FBF5E.20020125163518@fatcity.com>

Sergey,

My $0.02 below: (comments below and only for relevant stats, some of which have been re-arranged):

NAME                                                                  VALUE
----------------------------------------------------------------  ---------
opened cursors cumulative                                              1072
opened cursors current                                                   11

• Significant number of cursors closed in a short time, probably due to a
  *lot* of recursive SQL. This is borne out by the ratio of 'user calls' to
  'recursive calls' below. Keep in mind that index block splits are recursive, so if you are inserting, updating or deleting data that re-arranges indexes, this could be explained away.

user calls                                                              341
recursive calls                                                      198492
recursive cpu usage                                                    4089
CPU used by this session                                             696253

• Recursive CPU usage is insignificant compared to other CPU usage, and that's another indication that recursive SQL is not the problem (if this is against indexes).

enqueue requests                                                      11878
enqueue releases                                                      11876

• You don't seem to have enqueue waits, so you didn't get into a lock wait situation.

session logical reads                                              77233609
= db block gets + consistent gets
db block gets                                                       2642119
consistent gets                                                    74591490
physical reads                                                       148822
db block changes                                                    3005410
consistent changes                                                      141
redo entries                                                        1506007
redo size                                                         1.184E+09
= approx 1.1 Gb!!!
physical writes                                                        2728
physical writes non checkpoint                                         2728

• You performed 148822 physical reads, but 77233609 logical reads!! This, along with the fact that you are performing a large number of consistent gets rather than db block gets implies that are reading and updating the same blocks repeatedly in a loop. This is most certainly due to inefficient and incorrect use of SQL. Tuning is imperative, and improvement will be measured in quantums rather than deltas. The number of writes is negligible compared to reads, supporting the above observation. The redo is based on the number of db block changes - if the same block is changed multiple times, the amount of redo goes up accordingly. This too supports the observation. However, if you have a large number of indexes that change (as a result of updates), this could also produce the same phenomenon - large number of logical reads and same-block updates (and recursive SQL).

physical reads direct                                                  3594
physical writes direct                                                 2728

• Do you have Quick I/O (or raw) on some files?

table scans (short tables)                                           210918
table scans (long tables)                                                36
table scan rows gotten                                            798264962
table scan blocks gotten                                           71788386
table fetch by rowid                                                1074164
table fetch continued row                                                12

• You seem to be performing almost all your reads via table scans of small tables (20 blocks or lesser). In this case, you aren't performing Indexed reads, which *may* be good (if these are small tables). However, it looks like you are scanning short tables repeatedly (as supported by observations above).

leaf node splits                                                       4018
branch node splits                                                        9

• This definitely points to Index block manipulation as a result of updates or inserts.

Having said all that above, I hazard the following guesses:

• You are inefficiently scanning small tables, while continually updating a limited set of blocks repeatedly. This points to ineffecient coding. SQL Tuning will go a long way!
• You are probably updating columns (or inserting rows) that support indexes. You should look at whether these indexes are really required. If indexes are not used (as in many full table scans), why are they present?

Perform a trace and TKPROF it. If you post the output, we could help further. My feeling is that 'Database' or 'Instance' level tuning via init parameters isn't going to get you much mileage. However, SQL tuning certainly will!

Have a nice weekend, all!

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Fear is the darkroom where Evil develops your negatives. Wanna break free of fear? Click on 'http://www.needhim.org'

• The opinions and statements above are entirely my own and not those of my employer or clients **

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Jan 25 2002 - 19:15:38 CST