Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: View performance problem
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