oracle query related

grchap
grchap used Ask the Experts™
on
I currently have a query that uses all the indexes properly and runs fine in a RULE base optimization mode in Oracle 9.
When I run the same query in the COST based optimization mode in Oracle9, it takes THRICE the time.

I know some time COST based is not efficient.

I have simplified the query as below

select a.code, sum(e.qty*e.price)
from
   tab1 a, tab2 e
where
   a.key = e.tab1_key
group by a.code
/

tab1 has an index on key.

In RULE based, that index was getting used .
But COST based , it is doing a FULL table scan not using the index.


I know you can add hints etc to make it use the index.
I did the below, it uses the index.

select /*+ index(a TAB1_KEY) */ a.code, sum(e.qty*e.price)
from
   tab1 a, tab2 e
where
   a.key = e.tab1_key
group by a.code
/


This will be a time cosuming way to change all the queries in the system.

Is there any other way to address this speed issue?

Thanks
I currently have a query that uses all the indexes properly and runs fine in a RULE base optimization mode in Oracle 9.
When I run the same query in the COST based optimization mode in Oracle9, it takes THRICE the time.
 
I know some time COST based is not efficient.
 
I have simplified the query as below
 
select a.code, sum(e.qty*e.price)
from
   tab1 a, tab2 e
where
   a.key = e.tab1_key
group by a.code
/
 
tab1 has an index on key.
 
In RULE based, that index was getting used .
But COST based , it is doing a FULL table scan not using the index.
 
 
I know you can add hints etc to make it use the index.
I did the below, it uses the index.
 
select /*+ index(a TAB1_KEY) */ a.code, sum(e.qty*e.price)
from
   tab1 a, tab2 e
where
   a.key = e.tab1_key
group by a.code
/
 
 
This will be a time cosuming way to change all the queries in the system.
 
Is there any other way to address this speed issue?
 
Thanks

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
1 - I would probably test the impact of indexing tab2 on tab1_key

2 - index(a TAB1_KEY) is probably wrong, you probably mean either index(a key) or index(e tab1_key)
flow01IT-specialist

Commented:
are the statistics for the tables and indexes  gathered ?
try maximum  option of statistics gathering inclusive histogram of column values
(if your don't oracle considers the values evenly distributed)

Author

Commented:
fibo, I meant a key only (typo).

flow01,
Yes, statistics for the tables and indexes  are gathered
what do you mean by
"try maximum  option of statistics gathering inclusive histogram of column values"
please?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

impact of indexing tab2 on tab1_key?
flow01IT-specialist

Commented:
we used
dbms_stat.gather_table_stats
(
,estimate_percent => null  -- compute instead of estimated
,granularity =>'ALL'
)
for some table we had simular problemns of non-optimal access and it had the desired effect.
 
I had an special option in my memory for espacially making analysis of the distribution of data in a column but i can't find it back. (may be its include in de 'ALL';

Author

Commented:
fibo,
Explain plan shows both the tables tab1 and tab2 are accesed as full scan even though there are indexes
>> impact of indexing tab2 on tab1_key?
this might avoid a complete scan

Now, may be a complete scan is NOT a bad option, depending on your dataset.
Wich % of records will be used in tab1? in tab2?
flow01IT-specialist

Commented:
You wrote
I currently have a query that uses all the indexes properly and runs fine in a RULE base optimization mode in Oracle 9.
When I run the same query in the COST based optimization mode in Oracle9, it takes THRICE the time.
In which way did you measure that ? Different databases or same database , what did you alter ?

For measurements in the same oracle database always run each query twice.
If you run only once the second query benefits the blocks that oracle had already in the buffer from the first query.

If you use the /*+ rule */  hint in the query the indexes will be used : is it still faster ?


Author

Commented:
Hi flow01:
Yes, these queries were run in the same DB with RBO setting and CBO setting.
Yes, these were run more than 2 times to confirm.

With the hint it is using the indexes. But that really is not an option for me.
Because we can't be changing the queries manually to use indexes using hint which is very unproductive.

In CBO, for majaority of queries it is ok,  timewise.
But for some, it  is slow, like the one I mentioned above.

> In the subject query,  I can use explain plan and see that its not using indexes in CBO.
Is there something that I can do to understand why its not using them?

Thanks

IT-specialist
Commented:
grchap
my questions where only ment for excluding some pittfalls, I could not know you already jumped over them.
The rule hint could be used to measure old behaviour in the CBO setting
I also don't want to add hints to queries but  also in our application there are some queries that do perform worse costbased and until now I don't know why.
I try to keep the number of indeed unproductive adaptions a small as possible because in the next version of the database problaby an other solution is necessary (we are now using 10g and the rule-hint officially doesn't not exist anymore , but does work), but reducing some queries by 95% with a small risc (*1)  while gaining time to learn and understand I can't resist.
*1 (small change in source and as result same behaviour as before CBO)  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial