Re: View performance problem

In article <905kue$v6u$1_at_nnrp1.deja.com>,   terry_stjean_at_my-deja.com wrote:
> Maybe you can help me out with the best way to do this.
> I have 3 tables.
> A store master.
> A Product master
> A sales file.
> I have an index on both the product master and sales by upc (not
> unique).
> I have an index on both the store and sales by store and customer
> affiliation (unique).
>
> Here is my select in the view:
> select rev_pos_sales.STARTWEEK, rev_pos_sales.STORENUM,
> rev_pos_sales.FYEAR, rev_pos_sales.FPERIOD,
> rev_pos_sales.FWEEK, rev_pos_sales.QTY, rev_pos_sales.RETTOTAL,
> rev_store_hier.CUST_NAME, rev_store_hier.PROVID,
> rev_prod_hier.BRAND_CODE, rev_prod_hier.BRAND_NAME,
> rev_prod_hier.CATEGORY_CODE, rev_prod_hier.CATEGORY_NAME,
> rev_prod_hier.sub_category_code,
> rev_prod_hier.sub_category_name,
> rev_prod_hier.FRANCHISE_CODE, rev_prod_hier.FRANCHISE_NAME,
> rev_prod_hier.FRANCHISE_GRP_CODE,
> rev_prod_hier.FRANCHISE_GRP_NAME,
> rev_prod_hier.SUB_DIVISION_CODE,
> rev_prod_hier.SUB_DIVISION_NAME,
> rev_prod_hier.SPEC_CHAR_CODE, rev_prod_hier.SPEC_CHAR_NAME,
> rev_prod_hier.HO_PRODID, rev_prod_hier.ACTIVE,
> rev_prod_hier.PDES1, rev_prod_hier.PDES2,
> rev_store_hier.PROV_NAME, rev_prod_hier.HO_UPC,
> rev_store_hier.CUSTNUM, rev_pos_sales.ENDWEEK
> from REV_POS_SALES, REV_PROD_HIER, REV_STORE_HIER
> where rev_prod_hier.divid = 'REV1' and
> rev_pos_sales.upc = rev_prod_hier.upc and
> rev_pos_sales.afid = rev_store_hier.afid and
> rev_pos_sales.storenum = rev_store_hier.storenum and
> rev_pos_sales.afid = 'H' and
> rev_pos_sales.startweek between '01-JAN-1999' and '27-JAN-2001';
> I also have an index on the sales by affiliation and startweek.
> Any suggestions? Can I use hints?
>
> Terry
>
> In article <904ame$vug$1_at_nnrp1.deja.com>,
> jdarrah_co_at_my-deja.com wrote:
> > If any init.ora paremeters were changed, it could effect the
 execution
> > plan. Does the execution plan look good (i.e. no cartiesion merge
> > joins) If the execution plan look bad look at the statistics on the
> > table they may have been deleted or, if your using the choose mode
 for
> > your optimizer and sombody analyzed the table, the execution path
 could
> > change. If it looks like the query is executing a logical plan,
 then
> > the next thing would be to look at the wait statistics for the
 session
> > while executing the query. Either look in V$SESSION_WAIT or use
> > oradebug to turn on event 10046 (I don't know how to do this on NT)
 and
> > look at the resulting trace file.
> >
> > In article <903aki$3ju$1_at_nnrp1.deja.com>,
> > terry_stjean_at_my-deja.com wrote:
> > > I have a view created which joins 3 tables. A product table, a
 store
> > > table and a sales transaction table.
> > > Up until this past week, hen I queried the view, the performance
 wasn't
> > > bad, however this week the performance is really slow. This is
 even
> > > with no on else on the system.
> > > No changes have been made to the view and no data was added to the
 3
> > > tables the view uses.
> > > I have no idea what is going on. Any ideas of things to check?
> > > We are running Oracle 8.0.5 on NT 4.
> > >
> > > Terry
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Several possibles.

One is the indexes --

      for rev_prod_hier, I would suggest upc and divid
      for rev_store_hier, I would suggest afid and storenum
      for rev_pos_sales :
           you are using upc, afid, storenum, and startweek

Based on the information given, you might want an index on all of these fields. Cardinality is low for several : startweek ( 300 is 6 years ), storenum ( 100? ). I do not know about the rest. If this was all, I might even go with a bitmap index. But a standard index should work well. Look at arranging it to cover other queries that you are writing. You may not need all of the fields. If two fields get you to a few records, that will work. The rdbms will have to fetch the record for all the fields anyway.

The other is possible changes to the system.

I had a real problem with performance because too much space was available. Oracle recommends storage to be only 10-20% bigger than needed. We had 10X and performance was horrid.

Indexes sometimes get scrambled. I do not understand why. A good idea especially with large tables is to drop and rebuild indexes on a periodic basis. I believe in one large system ( 0.5 TByte) , it was done weekly ( they had a 48 hour down time window )

Run the analysis on the tables. Sometimes Oracle thinks the information is different in the tables than what is really there. Again this could be a weekly/monthly thing to do.

A third is how are you using the view. This is selecting the known world. I assume that the view has typically other arguments. Are they effecting the performance? Why create this particular join? Would leaving out the startdate allow for more flexibility? Often it is wiser to have many views each with specific purposes rather than one the fits all.

A fourth ( or should this be second), you should run the explain plan on the view and some example SQLs that is using the view. Based on the above information and new indexes, there should not be any full table scans.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rosetta.org
              Ususual disclaimers


Sent via Deja.com http://www.deja.com/
Before you buy.