RE: Session_wait

From: Mohan, Ross <MohanR_at_STARS-SMI.com>
Date: Fri, 25 Jan 2002 10:13:53 -0800
Message-ID: <F001.003FB71B.20020125095529@fatcity.com>

This is a busy little beaver...how long you say this runs?

opened cursors cumulative                                              1072
session logical reads                                              77233609
db block gets                                                       2642119
consistent gets                                                    74591490
physical reads                                                       148822
db block changes                                                    3005410
consistent changes                                                      141
no work - consistent read gets                                     72058049
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
leaf node splits                                                       4018
execute count                                                         74445
bytes sent via SQL*Net to client                                      59650
bytes received via SQL*Net from client                                84233
SQL*Net roundtrips to/from client                                       342
buffer is not pinned count                                         73513922

I see some updates (or inserts) and heavy reads...at first glance, though, you don't appear to be I/O throttled but likely have inefficiencies in buffer cache. Pin some small tables? Reexamine access paths for fts, even if on "small" tables (generally defined to be around 5% in blocks of buffer cache size?)and consider seeking out and destroying nested loops joins in favor of hash joins. There's more CPU, but less buffer cache splashing around....don't forget to review init.ora settings for hash, buffer pools, and query planning. (...be happy to do a flyover of those as well, if you like.)

That's about all i can get in a one minute glance, but there are alot of people on the list who'll see more. Look to them for longer posts with more explanation.

Good Luck!

• Ross
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.com
  --
  Author: Mohan, Ross INET: MohanR_at_STARS-SMI.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 - 12:13:53 CST