| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance analysis (enqueue and buffer busy waits)
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
------- ------ -------- ---------- ---------- ---------- ----------
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.
>
>===============================================================
>===============
>
-- 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
 |
 |