Re: tuning problem

Thanks for help. I looked into Stored Outlines and found that I can't use them because it's a feature that's only available in Enterprise Edition and we're running Standard Edition.

Since my last post, I went and checked out the init.ora parameters and found that the db_file_multi_block_read_count was set to 128 on the dev server. It was set to 8 on my new machine. I knew increasing the size would probably cause more full table scans, but I tried it anyway and yes...I got an even worse plans.

Last ditch effort: I did full statistics computations on every single table and index (Many of which are huge). ... I got the same bad plans.

The only other thing I noticed (And I'll try this tomorrow) is the dev server has the compatible parameter set to 8.1.0 and the new machine has it set to 8.1.5 (Both are running 8.1.6)... I doubt it will make a difference, but I'm willing to try anything at this point.

As for your 2nd question. We're running a hybrid database. It's generally modelled as a datawarehouse with a star schema with a low level of aggregation (to the hour, day, week etc...). However, unlike most datawarehouses that are refreshed periodically, late at night or on weekends, our system is real time. Those were the business requirements. The users need to analyze realtime data as it's coming in, in 'internet time.'

The loading program that we've written gets the new data cleans it up and then "slowly" loads it into the reporting database in small chunks of 1000-20,000 rows at 2 minute intervals (the batch size automatically changes based on the time of day). We do this to not degrade the performance of the reporting database at peak usage times.

So because there's a constant inflow of data coming in, I can't disable the integrity constraint.

Such is my predicament.

...very frustrated.

In article <B6AB34DB.3354%markbtownsend_at_home.com>,   Mark Townsend <markbtownsend_at_home.com> wrote:
> Two things to try - both specific to 8.1.x
>
> 1) Stored execution plans - if the execution plans are correct in the
 dev
> instance, then you can have the database save and store them. You can
 export
> and import these from dev into your production machine. See the doc
 under
> stored outlines. Note that you can do this for just the SQL
 statements that
> are giving you the wrong plans
>
> 2) You CAN have a constraint without the enforcing index. However,
 this is
> typically designed for when you have a data warehouse and want the
> constraint metadata for end user tools without the cost of an index.
> Typically you would also not want the index as you are doing parallel
 scans
> in a DW environment. The constraint itself is enforced only on large
 data
> loads. In you situation it sounds like you are in an OLTP
 environment, so
> this capability really won't help you. If you are still interested
 look in
> the doc under contraints for the DISABLE and VALIDATE,NOVALIDATE
 options
>
> > From: gdas_at_my-deja.com
> > Organization: Deja.com
> > Newsgroups: comp.databases.oracle.server
> > Date: Sat, 10 Feb 2001 22:33:59 GMT
> > Subject: tuning problem
> >
> > I've got one hand tied behind my back on this one and the CBO isn't
> > cooperating with me. I'm running 8.1.6
> >
> > The reason I'm hampered is that the application doesn't support
 usage
> > of hints. So we rely completely on CBO and effective statistics
> > gathering to yield optimal query performance.
> >
> > There are a handful of queries that I have that simply will not do
 what
> > I want them to do in regards to the execution plans.
> >
> > We have a dev system that DOES everything properly. I exported that
> > database and loaded it onto a new server (more powerful server) and
> > still can't get the right execution plans on particular queries.
> >
> > I've tried every form of stats gathering method on the tables,
 indexes,
> > columns I could think of. tried histograms with various bucket
 sizes
> > and I still can't get oracle to cooperate.
> >
> > The two problems are 1. Wrong index selected. 2. Choice to use
 hash
> > join when it should be using nested loops.
> >
> > I've struggled with this for an entire day and I'm completely
 stumped.
> >
> > The databases are identical on the dev server and the new server.
 The
> > only difference is that the new server has a bigger sga and shared
 pool
> > because it's a more powerful machine.
> >
> > Related question: (this would solve my problem too). Is there
 anyway
> > to create an integrity constraint that is used only for the purpose
 of
> > data integrity. This one table that I'm having the problem with
 has a
> > composite primary key (4 columns). I need this to enforce data
> > integrity, but I really don't want this index used for query
> > plans...and this is the index oracle keeps selecting on this new
> > machine.
> >
> > I've tried reordering the columns in that integrity constraint, most
> > selective to least selective, vice-versa etc... and I still can't
 get
> > CBO to work the same on this new machine as it does on the
 development
> > machine.
> >
> > If I manually write the queries to use the USE_NL hint along with
 the
> > index hint to use the appropriate index (and not the composite
 primary
> > key index)... I get an excellent low cost plan with low sql
 recursion,
> > low consistent gets etc...
> >
> > This new machine is about 10 times more powerful than the dev
 machine
> > but right now, the dev machine is outperforming this new machine on
> > about 5 queries. I'm trying to figure this out before my management
> > realizes this.
> >
> > I'd appreciate any help.
> >
> > Thanks,
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>

Sent via Deja.com
http://www.deja.com/ Received on Sun Feb 11 2001 - 00:17:50 CST