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:[EMAIL PROTECTED]]
>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:[EMAIL PROTECTED]]
>>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 <[EMAIL PROTECTED]>
>>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: [EMAIL PROTECTED]
>>
>>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
