Re: How is optimization calculated on a query

Here is what I've found out and it leads to another question. First, I had been experimenting with another database/instance of 8K db_block_size. My selects were using the indexes. I tweaked many init parameters with no noticeable affect on the execution path or performance in general. I then created an indentical database/instance with 16K db_block_size and copied the rest of the init.ora file. The 16K instance does not use the indexes.

One of the parameters I toyed with was the db_file_multiblock_read_count. I wasn't sure if 64K was the OS block size, so I upped that number. The book said it would use the OS block size if the Oracle value was too big. Someone said,though, that the value is part of the cost base optimization calculation.

I changed that multiblock value back, in fact halved it from the original since I doubled the block size. Doing an Analyze did not affect the execution path. I had to drop and recreate the index.

My question now is when is the cost based optimization path updated? What can trigger the calculation? And another question is what are the other init parameters involved with the optimization calculations?

Thanks for any help.
-Rick

In article <6v2jif$s3t_at_sjx-ixn6.ix.netcom.com>,   "Ross Mohan" <nonspam_rmohan_at_greencastle-corp.com> wrote:
> You might consider adding to Jonathon's good suggestions by
> making a multikey index: on c1, then c2. It should be able
> to do any index-only query plan.
>
> Let us know how it all works out.....
>
> ricke1_at_my-dejanews.com wrote in message <6uomu3$uf5$1_at_nnrp1.dejanews.com>...
> >Thanks for the hint. I tried it and it didn't seem to have any effect on
> my
> >execution plan. I do sometimes get an index scan on a 1 date constraint
> and I
> >have gotten in the past index scans on multiple dates without the to_date.
> I
> >am getting the correct number of rows selected.
> >
> >I would think that doing a select --+ index would force an index scan if
> >possible and it doesn't.
> >
> >Thanks in advance for any other suggestions.
> >-Rick
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sun Oct 04 1998 - 14:14:24 CDT