Re: View performance problem

You need to include the explain plan. if you set autotrace traceonly and run your poorly performing query, that should give enough information to begin troubleshoot this. Also include how large thses tables are and all indexes that are present on them.

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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 30 2000 - 13:22:09 CST