RE: flush shared_pool and query performance
- From: Asif Momen <asif_oracle@xxxxxxxxx>
- To: genegurevich@xxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx, UNEXPECTED_DATA_AFTER_ADDRESS@.SYNTAX-ERROR
- Date: Thu, 3 Apr 2008 16:45:43 -0700 (PDT)
Hi,
Is CURSOR_SHARING set to FORCE?
It could be the reasons.
Regards
Asif Momen
http://momendba.blogspot.com
genegurevich@xxxxxxxxxxxx wrote: OK. I am still confused here.
Here is what I do (oracle 10.2.0.2);
execute a SQL - select col1, col2 from table where col3='1123' - completes
in about 0.01s
execute a SQL - select col1, col2 from table where col3='4567' - completes
in about 0.01s
alter system flush shared_pool;
execute a SQL - select col1, col2 from table where col3='1123' - completes
in about 0.3
execute a SQL - select col1, col2 from table where col3='1123' - completes
in about 0.01s
execute a SQL - select col1, col2 from table where col3='4567' - completes
in about 0.01s
Why would the third SQL complete that fast. I have a different SQL because
of the value 4567 (and I don't
use bind variables), so shouldn't this be a hard parse too?
thank you
Gene Gurevich
"Bobak, Mark"
est.com> To
Sent by: "genegurevich@xxxxxxxxxxxx"
oracle-l-bounce@f ,
reelists.org "oracle-l@xxxxxxxxxxxxx"
cc
04/03/2008 04:59
PM Subject
RE: flush shared_pool and query
performance
Please respond to
Mark.Bobak@proque
st.com
Sure, I don't think that's unreasonable at all. As an added confirmation,
if you have 0.01 secs, and then you flush and first execution is 0.3 secs,
does the second execution after the flush go back to 0.01 secs? If so,
then the difference is the hard parse.
-Mark
--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059
mark.bobak@xxxxxxxxxxxx
www.proquest.com
www.csa.com
ProQuest...Start here.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of genegurevich@xxxxxxxxxxxx
Sent: Thursday, April 03, 2008 5:40 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: flush shared_pool and query performance
Hi all:
I am noticing that the same SQL executed before and after 'alter system
flush shared_pool' completes in VASTLY
different time - 0.01s before the command and 0.3s after the command. I
wonder why is that. When I flush the
shared_pool, the library cache is flushed and so my SQL needs to be
reparsed. Is that reasonable for parsing
to take 0.3s?
thank you
Gene Gurevich
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
---------------------------------
You rock. That's why Blockbuster's offering you one month of Blockbuster Total
Access, No Cost.
Other related posts:
