Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: View performance problem

Re: View performance problem

From: Haild <haild_0234_at_hotmail.com>
Date: 30 Nov 2000 13:18:16 -0600
Message-ID: <3a26a742$0$37046$45beb828@newscene.com>

explain plan of the query.

you need to provide the following

list all the indexes you have on the tables including what type tree or bitmap indicate cardinality of indexes

can you add idexes espiclly bitmap ones if the cardinality is low?

also read the stuff on tuning views in Oracle

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.
Received on Thu Nov 30 2000 - 13:18:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US