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: Event 10046 and Performance

RE: Event 10046 and Performance

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 15 Nov 2002 16:18:43 -0800
Message-ID: <F001.00504DE8.20021115161843@fatcity.com>


Ah, the "secret power" of event 10046 reaches the light of day at last!  

:-) Just kidding. Is there a chance that it's coincidence, that some workload variation that causes poor performance just happens not to have occurred since you instrumented the form?  

My advice is to be patient and leave the instrumentation there until the problem manifests itself again. Then you'll have everything you need to fully diagnose the issue in the session's trace file.  

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic <http://www.hotsos.com/training/clinic> , Dec 9-11
Honolulu
- 2003 Hotsos Symposium <http://www.hotsos.com/events/symposium> on
OracleR System Performance, Feb 9-12 Dallas
- Jonathan Lewis' <http://www.jlcomp.demon.co.uk/seminar.html>
Optimising Oracle, Nov 19-21 Dallas

-----Original Message-----
Rajendra
Sent: Friday, November 15, 2002 4:49 PM
To: Multiple recipients of list ORACLE-L  

Okay ... here is a curved ball ...

This AM an application support person called and mentioned that we have a form that has become slow compared to 2 days ago. So we saw the slow behavior and then I did a 10046 trace on it.

After analysis, found that one query is taking about 90% of total time and isolated it to be worked on.

On our day-old instance (refreshed daily from prod), this form runs very fast. After comparing everything, I found that for some test we have db_file_multiblock_read_count = 4 whereas it is 32 (don't ask) on production.

So after changing this variable in my session to 4 (on production), I checked the explain plan and it seems to be okay, picking up the right indexes etc.

So, I asked the support person to add following line in pre-form trigger on the form and run a test

forms_ddl('alter session set db_file_multiblock_read_count=4');

ran the form, no change in the performance. So I requested following like to be addes as well

forms_ddl('alter session set events ''10046 trace name context forever, level 8''');

The form runs as fast as it can.

So we thought maybe flushing the shared pool might help, so we flushed the shared pool (again don't ask, this is prod env, lunch time, light load).

Still same, with 10046 event set, the form runs as fast as it can, but if I take it out, it slows down.

I am confused ... does anyone have a plausible explanation on why this is happening?

Oracle 9201, AIX 5l
Raj



Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Nov 15 2002 - 18:18:43 CST

Original text of this message

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