Hi Terry,
Do you have statistics on that/these tables?
This is the scenario I am seeing this possible:
- the SQL is removed from the cache (small cache, ...)
- Oracle needed to parse the SQL again
- while parsing your table grows, so the costs changed
when optimizer used the fact how big is the table (I
know that Wolfgang mentioned that the table size is
the one of things Oracle optimizer knows without
having statistics)
I got into my mind that the table growth on the table
without statistics may cause optimizer to change the
plan on the table.
Maybe I got it wrong.
Regards,
Zoran Martic
If this is sort of batch job and the SQL get rid of
from the cache, so Oracle needed to parse and prepare
the execution plan again
- Terry Barnett <tbarne_at_landmark-information.co.uk>
wrote:
>
>
> We are running version 9.2.0.1 on a Sunfire V880 (6
> * 1.2GHZ CPUs 24Gb
> memory). DB parameter optimizer_dynamic_sampling is
> set to 1.
>
> The particular SQL statements in question do use
> bind variables.
> Typically what's happening is that fast nested loop
> range scan joins are
> turning into full table scan hash joins (for
> relatively small resulting
> record sets).=20
>
> As already mentioned, we deliberately keep
> statistics 'stale' and DB
> parameters constant when performance is at an
> acceptable level. The only
> varying factor (I am aware of) is the size of the
> tables i.e. the join
> tables are constantly growing with 1000's of new
> record inserts per day.
>
>
> Regards,
> Terry
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail
--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 20 2005 - 05:57:46 CDT
