Re: Different index usage by Oracle* optimizer

Look at the parameter DB_FILE_MULTBLOCK_READ_COUNT in the init.ora file. Do it have the same value? If this parameter is bigger, the CBO can decide do to a full table scan.

bye

In article <7rt7lm$1q4$1_at_nnrp1.deja.com>,   broeni_at_my-deja.com wrote:
> I'm having 2 similar databases on 2 different UNIX servers, both
> `OSF1 V4.0 1091 alpha'.
>
> The database on server 1 is:
> Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
>
> while the database on server2 is running with the partitioning option:
> Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
> With the Partitioning option
>
> The table CUSTOMER_ALL contains ~200'000 rows in each database, but I
> observe the following difference:
>
> server 1 and server 2: select a distinct customer:
> 1* select customer_id from customer_all where customer_id = 50000
> Execution Plan
> ----------------------------------------------------------
> 0 ~ SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26)
 ~
> 1 0 INDEX (UNIQUE SCAN) OF 'PKCUSTOMER_ALL' (UNIQUE) (Cost=1 C
~
> ard=1 Bytes=26)
>
> The behaviour of the optimizer changes if a range of customers is
> selected.
> On server 1 the index is still used:
> 1 select customer_id from customer_all
> 2* where customer_id > 50000
> Execution Plan
> ----------------------------------------------------------
> 0 ~ SELECT STATEMENT Optimizer=CHOOSE (Cost=546 Card=275477 Byte
~
> s=7162402)
> 1 0 INDEX (RANGE SCAN) OF 'PKCUSTOMER_ALL' (UNIQUE) (Cost=546
 ~
> Card=275477 Bytes=7162402)
>
> On server 2 the optimzer makes a full table scan and uses the index
> only if I include the hint FIRST_ROWS:
> 1 select customer_id from customer_all
> 2* where customer_id > 50000
> Execution Plan
> ----------------------------------------------------------
> 0 ~ SELECT STATEMENT Optimizer=CHOOSE (Cost=482 Card=342379 Byte
~
> s=8901854)
> 1 0 TABLE ACCESS (FULL) OF 'CUSTOMER_ALL' (Cost=482 Card=34237
~
> 9 Bytes=8901854)
>
> On both databases the table customer_all and the index
> pkcustomer_all are fully analyzed with `ANALYZE ... COMPUTE
STATISTICS'.
>
> Another difference that I could find is that on server 1 -the one
using
> the index- the table customer_all uses 5 extents, while on server 2
> only 1 extent is used for the table.
>
> My problem is not restricted to the example I've described.
> The programs show a very poor performance on server 2 because many of
> the statements cause full table scans.
>
> Questions
> =========
> + Is it "normal" that when the partitioning option is used the
> optimizer has a tendency to make full table scans?
> + Can I prevent this by setting `optimizer_mode = COST' in the
init.ora
> file?
> + Are there any other explanations for the different behaviour?
>
> Many thanks in advance for any response, Stephan
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

--
Geraldo Milagre
Oracle DBA and Instructor
milagre_at_softdes.com.br

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Sep 20 1999 - 16:38:47 CDT