Re: Oracle Cost-Based Optimizer
Date: 1995/11/22
Message-ID: <48v7s2$n6k_at_mailhost.qsp.co.uk>#1/1
In <48v2hk$436_at_dub-news-svc-3.compuserve.com>, 102642.2026_at_compuserve.com (Ja Uhm) writes:
>We have recently changed from Rule-based optimizer to Cost-based
>optimizer in Oracle 7.2. I have a program that ran successfully under
>rule-based optimizer. But once the Opimizer was changed to
>Cost-Based , the sql statements in the program is doing full table scans
>and sort merges under the Explain Plan. The result is that the
>program is using a lot of space in 'Temp' tablespace and blowing
>up on lack of space in that tablspace. How can the Optimzer change
>the Query plan so dramatically . Under the new optimizer it is
>doing full table scan on 800,000 record table when I am selecting
>less that 10,000 records and doing sort merge on all small tables
>instead of NESTED LOOP
> BY ROWID under Explain plan. Please comment.
>
Cost based optimsers must always compromise between the time spent in finding a solution and the time spent executing the query. There is no point in an optimiser spending 10 seconds shaving off 2 seconds from a query (to give an exagerated example). This means that they can sometimes miss what might be an obvious (to you) plan. It is important when writing queries to understand the processes that the optimiser will go through when it examines the query. Sometimes the solution can be as simple as changing the order of the search arguments.
Another thing you might have to do is to make the job of the optimiser easier by providing search arguments that you haven't in the past. For exampel you might have written the following in the past:
select a
from table1
where a=b
and b=c
you should now provide the part of the query which was implicit before, in this case
and c=a
At optimise time the optimiser needs to know certain facts about the table such as the number of rows. These are known as the storage statistics which can be either estimated or computed (which takes longer but is more acurate). If you haven't done this then the optimiser is just guessing. If you have but you have performed a lot of DML (especially inserts) since then, the statistics are out of date and you need to recreate them. An optimiser will often choose to table scan if it thinks it contains a small number of rows.
The command to collect statistics about a specific table is:
ANALYSE TABLE <table_name> COMPUTE STATISTICS
My appologies if any of this is known to you already.
Cheers,
Tony
"The less I seek my source for some definitive, Closer I am to fine..." Indigo Girls
All opinions expressed are mine and do not necessarily represent those of my employer Received on Wed Nov 22 1995 - 00:00:00 CET