RE: system stats (9205) - can they go stale?

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Thu, 26 May 2005 10:14:13 -0700
Message-ID: <960A9B249C99994AA3C7FCA016A0DA8902AA09B4@ussccem08.corp.hds.com>

Mark,

Did you recently collect stats for histograms? When you say the query runs fast outside, did you specify bind variables or literal values? 9.x introduced the now-slowly-becoming-infamous bind peeking that has many a DBA scratching their heads wondering why performance see-saws.... Would you able to flush the shared pool and retry the query from the procedure, and if so, does the performance change?

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com

The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Teehan, Mark
Sent: Thursday, May 26, 2005 3:40 AM
To: 'oracle-l_at_freelists.org'
Subject: system stats (9205) - can they go stale?

Hi all
I have a query on a simple table of a few thousand rows that suddenly takes minutes instead of < 1 sec; and it is only slow when called from a procedure. One suspicion is that the system stats are stale; I know queries on this DB used to drag without the system stats. Is there any way to tell if the optimizer has suddenly decided that it doesnt like the contents of sys.aux_stat$ any more?

Rgds
Mark

==

This message is for the sole use of the intended recipient. If you received this message in error please delete it and notify us. If this message was misdirected, Credit Suisse, its subsidiaries and affiliates (CS) do not waive any confidentiality or privilege. CS retains and monitors electronic communications sent through its network. Instructions transmitted over this system are not binding on CS until they are confirmed by us. Message transmission is not guaranteed to be secure.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 26 2005 - 13:19:06 CDT