Re: tuning problem

According to 10053 dumps, there are 18 public parameters and 33 hidden ones that affect the optimizer calculations - not to mention a further 6 (at least) that probably do, but are not quoted as doing so . (This is 8.1.7).

If the 'small table' fits in the hash_area_size, then the cost of a hash join is basically the cost of scanning the small table, plus the cost of scanning the large table plus a little bit; where the cost of the scans is

    blocks in table / db_file_multiblock_read_count

As well as hash_area_size, you might also look at hash_multiblock_io_count, as this also affects the cost of hash joins if the  'small table' will not fit completely in the hash_area_size. However, from your comments, this will be the same (and probably shows as 0) on both boxes.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



gdas_at_my-deja.com wrote in message <966n3m$ct3$1_at_nnrp1.deja.com>...


>I think I might have solved my problem.  However, I'm still in the

>progress of checking all the queries.

>

>After an exhaustive reading of all the init.ora parameter

>descriptions.  I found something called "hash_area_size."  I never set

>this parameter on either the new server or the dev server.

>

>The Oracle documentation, however says that this parameter if not set,

>defaults to sort_area_size * 2.

>

>Since this new machine is so much more powerful (more/faster ram,

>more/faster disks, more/faster processors) than the dev machine, I had

>in fact increased the sort_area_size.  I had increased it to be double

>the value that it was on the development server.

>

>In my init.ora, I then went and specifically set the hash_area_size to

>be the same as the sort_area_size.

>

>I've done a test with one query and now the CBO seems to generate a

>better plan.  For one, nested loops are being favored.  I still have

>one query where it appears the wrong driving table is being used,

>however the overall cost figures and consistent gets are lower than the

>plan the dev system generates.

>

>I'm going to keep testing this with every query in our system today.

>

>If I find more questionable plans being generated.  I will post the

>plans as you've asked for.

>

>The Oracle documentation says nothing about hash_area_size affecting

>the way in which the CBO selects it's join strategy.  It does.  I've

>just proved it.

>