RE: Event 10046 and Performance
Title: RE: Event 10046 and Performance No, we are 9201 on Test/Prod ... we are getting ready to test 9202 though ... As Cary suggested ... I am going to watch is today ...let's see ... it would be interesting .. 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! -Original Message- From: Binley Lim [mailto:[EMAIL PROTECTED]] Sent: Sunday, November 17, 2002 6:13 PM To: Multiple recipients of list ORACLE-L Subject: Re: Event 10046 and Performance It is not uncommon to find the problem disappearing after switching debugging/tracing on, but in this case I'm wondering if its some feature with 9201? Are your test/prod on the same patch release? or you have already applied 9202 to your test environment? This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Event 10046 and Performance
I am in the middle of this fight myself. We
had an Oracle consultant come in to look at our performance problems. He spent a
3 days examing the sql and coming up with various reasons that our code was
slow. Then the "BUG" cropped up - one of his reports that had been running great
all of a sudden started taking forever (3min vs 15min) to come back - His answer
was to file a tar - which is where I am at now. After several rda runs we still
don't have and answer.
[EMAIL PROTECTED] 11/15/02 04:48PM
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!
Re: Event 10046 and Performance
It is not uncommon to find the problem disappearing after switching debugging/tracing
on, but in this case I'm wondering if its some feature with 9201? Are your test/prod
on the same patch release? or you have already applied 9202 to your test environment?
[EMAIL PROTECTED] 11/16/02 11:48a.m.
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: Binley Lim
INET: [EMAIL PROTECTED]
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: [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).
RE: Event 10046 and Performance
Title: Event 10046 and Performance
Ah, the secret power of
event 10046 reaches the light of day at last!
J Just kidding Is there a chance that its 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 youll
have everything you need to fully diagnose the issue in the sessions
trace file.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic, Dec
911 Honolulu
- 2003 Hotsos Symposium on
Oracle System Performance, Feb 912 Dallas
- Jonathan Lewis'
Optimising Oracle, Nov 1921 Dallas
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of Jamadagni,
Rajendra
Sent: Friday, November 15, 2002
4:49 PM
To: Multiple recipients of list
ORACLE-L
Subject: Event 10046 and
Performance
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!
