Re: Does Optimizer use P.A.T to calculate cost..?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 30 Oct 2006 08:37:51 -0000
Message-ID: <F5KdnWmlJahHJNjYRVnyhw@bt.com>

<mccmx_at_hotmail.com> wrote in message
news:1161955043.086095.304490_at_m73g2000cwd.googlegroups.com...
>> Time to start investigating what is happening, a 10046 trace at level 8
>> will help. Also, take a look at the output of this SQL statement:
>
> The 10046 hasn't helped. It has just shown me that the plan has
> changed. I've done some tests and I can confirm that I can get much
> more stable execution times if I use the old method of manually
> specifying *_area_size parameters.
>
> I've seen advice on this newsgroup which says that you can set the PGA
> target to a very high value as long as you have the memory available.
>
> But I am seeing that a value that is too high can cause instablity in
> execution plans.
>
> Matt
>

What is your

    total physical memory
    memory allocated to SGA
    number of expected sessions
    setting for db_file_multiblock_read_count

Do you have any hidden parameters set,
and what values do you have for any
optimizer-related parameters that are not at their default ?

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Mon Oct 30 2006 - 02:37:51 CST