Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Cost based optimizer with snapshots
When you create a snapshot, an underlying table is also created which you
can then analyze. The table is named SNAP$_'snapshot_name'.
swapn187_at_my-dejanews.com wrote in message
<6ur038$dpa$1_at_nnrp1.dejanews.com>...
>I have Oracle 7.3.4 running on two servers on separate HP-Unix systems,
each
>implemented as a data warehouse. One is a staging server and the other is a
>production server. The production server has read-only snapshots (with fast
>refresh) of the fact and dimension tables of the staging server. I have a
>Brio query that joins 10 dimension tables with a Fact table (having more
than
>8 million records) and has multiple sorts and group by's. The Choose option
>is set, but the cost-based optimizer was not used since I had not analyzed
>the tables. This caused the Sort-Merge joins to be used, needing more than
6G
>space in the Temp tablespace ! I analyzed the dimension tables: the
>cost-based optimizer was used, with hash-joins needing only 85M temp space.
>This was great !! Question: The same query needs to be run on the
Production
>server. I am expecting that it would not use the Cost-based optimizer,
since
>the objects used in the query are snapshots (not tables) that cannot be
>analyzed. Can anyone give any suggestions/information about forcing the
>cost-based optimizer to be used for queries against snapshots ?
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Tue Sep 29 1998 - 22:19:17 CDT