Re: Unique index not being used, up to date stats

On Jan 29, 5:06 pm, "Valentin Minzatu" <valentinminz..._at_yahoo.com> wrote:
> On Jan 29, 4:20 pm, "Ben" <bal..._at_comcast.net> wrote:
>
>
>
>
>
> > On Jan 29, 3:59 pm, "Valentin Minzatu" <valentinminz..._at_yahoo.com>
> > wrote:
>
> > > It looks like you have 3 options:
> > > 1. open a case with Oracle
> > > 2. keep replacing statistics with the old values (I know in 10g you
> > > can lock statistics, but I do not know of any method for doing it in
> > > 9i)
> > > 3. use stored outlinesech, ech, and ech.
>
> > :) I know that I should probably start looking into stored outlines,
> > as I'm not familiar with them at all really, other than I know you
> > create and use them.
> > I am still really new to this dba stuff, and in such, I'm a little
> > afraid of making adjustments to parameters but our opt_ind_cost_adj
> > and opt_ind_caching is still set to default values.
> > In Johnathan Lewis' article he discusses setting these and I think I
> > need to just bite the bullet and experiment. It's hard to do this kind
> > of thing in our development system, as it has no comparable workload
> > and it sits on a machine that is old and has 4X less the number of
> > cpu's. We also are not collecting system statistics. This could be
> > something else that needs to be researched.
> > thanks for the help.Sorry for having to state the obvious above, but that is pretty much
> all I can think of. I would play with the stored outlines rather than
> with optimizer_index_cost_adj - keep in mind that the first affects
> specific queries while the latter affects the whole database behavior.- Hide quoted text -- Show quoted text -

no problem, I had thought about the stored outlines, but it's just hard for me to accept the change in execution paths over such a small change in the amount of data.

I really am at a crossroads about the whole init param thing. There is so much that I see with our database that varies from everything that I've read on here that it should be setup like. The main problem is that I have no way of ever knowing why the old dba didn't change it before. Some things are so obvious that it makes me think he just didn't really know as much as he let on ( he didn't have any checks and balances, as I don't either ) or that he did try it unsuccessfully.

One thing that comes to mind is the sort_area_size of our production database. It is set to 512000, keep in mind that the value is in bytes not kb. The TEMP tbspc is set to grow in 512Kb extents. I've read ( and was taught by Oracle ) that the sort area size should be a multiple of the extent size of the TEMP tblspc. I'm not sure if there are any other implications from this other than just not utilizing the space that is allocated.

Not to mention that our hybrid OLTP/Batch system has anywhere from 10 to 30 sorts or hash joins going on at any one time during the day that are all anywhere from .5 to 10M. I'm fairly sure that our server could handle several 1 or 2M sorts at a time in memory, why have that value so low that it generates so much IO? Received on Mon Jan 29 2007 - 18:37:36 CST