data block waits is the indicator of freelists contention. I have never
seen a freelists contention, even though I have been running 11
processing doing inserts using 11 CPUs. I have seen library cache,
Shared pool and cache buffer chains waits. I took care of buffer busy
waits and db file sequential reads by increasing freelists, initrans to
11 and by partitioning the disks. In my case most of the data was
writting to the same block by as many as 9 processes. I am working on a
Sparc 4500 Solaris 2.1.6 with Oracle 8.1.6.0 and 8.1.7.2
Library Shared Pool latch contention was found due to Literal or
non-shared SQL. Check SQL_text in SQLarea to find literal SQL. Shared
Pool contention seems to be due to a very large Shared pool. I found a
bug report that indicates that cache buffer chain problem was fixed in
8.1.3.4 and 9.0.1.3
Cache buffer chain is also an indicator of high physical and logical
I/O. You can check on that as well.
Hope this helps.
Shakir
- Paul Baumgartel <treegarden_at_yahoo.com> wrote:
> Greetings!
>
> I am trying to diagnose a performance difference between two
> databases
> running the same test. They are similarly configured (same SGA size,
> etc.), and the servers are identical except for the number of CPUs
> (server A has 4, server B has 6).
>
> On database A, INSERT performance is about 190 rows/second.
>
> On database B, INSERT performance is over 500 rows/second.
>
> I saw some cache buffers chains, buffer busy, and library cache latch
> waits on database A while the test was running, as well as redo log
> sync waits. The waits didn't seem excessive, though. I checked for
> "checkpoint not complete" redo allocation messages in database A's
> alert log and found none. The db_block_lru_latches parameter is set
> to
> one-half the number of CPUs in both machines.
>
> I'd much appreciate any suggestions as to what else to check. I know
> that freelists can be an issue (there are multiple sessions
> performing
> inserts); how can I check to see if there's freelist contention?
> Anything else I should investigate?
>
> Many TIA,
>
>
>
>
>
> =====
> Paul Baumgartel, Adept Computer Associates, Inc.
> paul.baumgartel_at_aya.yale.edu
>
>
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Tax Center - online filing with TurboTax
> http://taxes.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Paul Baumgartel
> INET: treegarden_at_yahoo.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).
Mohammed Shakir
CompuSoft, Inc.
11 Heather Way
East Brunswick, NJ 08816-2825
(732) 672-0464 (Cell)
(732) 257-6001 (Home)
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammed Shakir
INET: mshakir08816_at_yahoo.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 Wed Apr 10 2002 - 21:03:20 CDT