Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance analysis (enqueue and buffer busy waits)

RE: Performance analysis (enqueue and buffer busy waits)

From: Post, Ethan <epost_at_kcc.com>
Date: Mon, 20 Aug 2001 11:10:20 -0700
Message-ID: <F001.0037151C.20010820112205@fatcity.com>

In regards to my last post, here is an example of the type of statement that is killing my CPU.

#! ITEM/CUSTOMER KEY ID MASTER FILE
SELECT *
FROM
 CRPDTA.F4094 WHERE ( KIPRGR = :KEY1 AND KIIGP1 = :KEY2 AND KIIGP2 = :KEY3   AND KIIGP3 = :KEY4 AND KIIGP4 = :KEY5 AND KICPGP = :KEY6 AND KICGP1 = :KEY7
  AND KICGP2 = :KEY8 AND KICGP3 = :KEY9 AND KICGP4 = :KEY10 ) call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------



Parse 316 0.08 0.12 0 0 0 0
Execute 55789 4.78 4.23 0 0 0 0
Fetch 72058 2.56 2.11 0 72058 0 16269
------- ------ -------- ---------- ---------- ---------- ----------

total 128163 7.42 6.46 0 72058 0 16269

Check out the number of executions and fetches. Are there any ways I can reduce this number like CURSOR SPACE FOR TIME or something? I have a ton of these types of statements and that is why user calls trippled when we activated advanced pricing. I set CURSOR_SHARING = FORCE and got no improvements on anything during testing.

Thanks,
Ethan

>-----Original Message-----
>From: Post, Ethan [mailto:epost_at_kcc.com]
>Sent: Monday, August 20, 2001 11:13 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Performance analysis (enqueue and buffer busy waits)
>
>
>FYI, in a previous message I detailed some of the tuning
>efforts we have
>been working on in regards to getting a EDI sales order
>processing job to
>process 150,000 lines per hour. Well the major point of contention was
>freelists. We went from 250 lines per minute to over 1500
>lines per minute
>simply by increasing freelists on some tables and indexes. We saw an
>unbelievable 20,000 commits per minute on a 12 CPU AIX box.
>Looked like we
>could actually achieve the client's goal until we turned on advanced
>pricing. It added 60% more overhead and increased user calls
>3 fold which
>pegged all 12 CPU's at 100%. I have a detail trace file event
>10046 level 8
>to look at and see if I can improve any. Think I will give
>unil-bialism a
>try.
>
>Thanks,
>Ethan
>http://www.geocities.com/epost1
>
>
>>-----Original Message-----
>>From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
>>Sent: Monday, August 20, 2001 7:56 AM
>>To: Multiple recipients of list ORACLE-L
>>Subject: Re: Performance analysis (enqueue and buffer busy waits)
>>
>>
>>
>>Small rollback segments can be recycled
>>without being written to disc. This can
>>reduce the total write-load on the system
>>and enhance your general use of the
>>db_block_buffer.
>>
>>
>>Jonathan Lewis
>>
>>Host to The Co-Operative Oracle Users' FAQ
>>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>>
>>Author of:
>>Practical Oracle 8i: Building Efficient Databases
>>See http://www.jlcomp.demon.co.uk/book_rev.html
>>
>>For latest news of public appearances
>>See http://www.jlcomp.demon.co.uk
>>
>>Screen saver or Life saver: http://www.ud.com
>>Use spare CPU to assist in cancer research.
>>
>>
>>
>>
>>-----Original Message-----
>>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>>Date: 20 August 2001 14:03
>>
>>
>>|I am curious.
>>|What are you trying to accomplish by
>>|decreasing rollback segment size??
>>|
>>|- Babette
>>|
>>
>>
>>--
>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>--
>>Author: Jonathan Lewis
>> INET: jonathan_at_jlcomp.demon.co.uk
>>
>>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).
>>
>
>---------------------------------------------------------------
>---------------
>This e-mail is intended for the use of the addressee(s) only
>and may contain privileged, confidential, or proprietary
>information that is exempt from disclosure under law. If you
>have received this message in error, please inform us promptly
>by reply e-mail, then delete the e-mail and destroy any
>printed copy. Thank you.
>
>===============================================================
>===============
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Post, Ethan
> INET: epost_at_kcc.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).
>
>---------------------------------------------------------------
>---------------
>This e-mail is intended for the use of the addressee(s) only
>and may contain privileged, confidential, or proprietary
>information that is exempt from disclosure under law. If you
>have received this message in error, please inform us promptly
>by reply e-mail, then delete the e-mail and destroy any
>printed copy. Thank you.
>
>===============================================================
>===============
>



This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  INET: epost_at_kcc.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 Mon Aug 20 2001 - 13:10:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US