Re: why does CBO not use available indices

When I upgraded a database from 8.1.6.3 to 9.2.0.4 I found that some of my queries on the 9i database were doing full table scans instead of using indexes. This caused the query to run about 10 times longer. I went back and compared all the differences between both databases and found that if I set my

sort_area_size

to be the same as the 8i version (about 1.5Mb) then the execution plan changed and it started using the index resulting in the query running in the same amount of time as the 8i database.

I wasn't using sort_area_size in the 9i database due to the new parameter,

pga_aggregate_target

So, I experimented by adding the pga_aggregate_target back into the 9i database and found it started doing full table scans again instead of index lookups. I then lowered the value of pga_aggregate_target down to about 30Mb and at this point the query started using indexes.

So I learnt from this that having too much sort memory can cause problems, this was a big surprise to me as I thought having a large value for pga_aggregate_target was good - not always the case it seems. I need to research this a lot more, but until then I will stick with the old parameter of sort_area_size.

"Anke Heinrich" <anke.heinrich_at_marconi.com> wrote in message news:9042145d.0310060903.4ed4e354_at_posting.google.com...
| Hi,
|
| I have a problem getting the cost based optimizer to use the available
| indices instead of performing a full table scan. Using hints is not
| possible as I can't change the source code at the moment. I've heard
| about outlines, but I'm not sure if this works for us.
|
| I don't have much experience with query optimization or export/import
| of outlines, so any help from you is greatly appreciated. The facts
| follow:
|
| SQL statement:
|
| select * from TP, ETP
| where ( TP.FK_RESOURCE_ETP = ETP.ID ) and ( ETP.FOREIGN_ID = ? )
| order by ETP.FK_CONTAINER_EMLSN, ETP.FOREIGN_TYPE, ETP.FOREIGN_LABEL
|
| best select strategy:
| 1) search for entries in ETP with given FOREIGN_ID
| using index ETP_FOREIGN_ID on ETP (FOREIGN_ID) which is not unique,
| but very selective (usually only one match)
| 2) join the results from ETP with TP.FK_RESOURCE_ETP = ETP.ID
| using unique index TP_KEY_RESOURCE on TP (FK_RESOURCE_ETP)
| 3) sort results
|
| table statistics:
| have been calculated using
| analyze table TP compute statistics
| for all indexes for all indexed columns;
| analyze table ETP compute statistics
| for all indexes for all indexed columns;
|
| optimizer parameter:
| optimizer_index_cost_adj=10
| optimizer_max_permutations=1000
|
| we've had a good execution plan when both tables had around 400
| entries:
| 0 SELECT STATEMENT Optimizer=CHOOSE
| 1 0 SORT (ORDER BY)
| 2 1 NESTED LOOPS
| 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'ETP'
| 4 3 INDEX (RANGE SCAN) OF 'ETP_FOREIGN_ID' (NON-UNIQUE)
| 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'TP'
| 6 5 INDEX (UNIQUE SCAN) OF 'TP_KEY_RESOURCE' (UNIQUE)
|
| with around 35000 entries (and updated statistics) the execution plan
| now is:
| 0 SELECT STATEMENT Optimizer=CHOOSE
| 1 0 SORT (ORDER BY)
| 2 1 MERGE JOIN
| 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'ETP'
| 4 3 INDEX (FULL SCAN) OF 'ETP_ID' (UNIQUE)
| 5 2 SORT (JOIN)
| 6 5 TABLE ACCESS (FULL) OF 'TP'
|
| we tried out the hint which produced the preferred execution plan, but
| as I said we can't change the source code, so this is not a real
| option:
|
| /*+ USE_NL(ETP TP) INDEX(ETP ETP_FOREIGN_ID) INDEX(TP
| TP_KEY_RESOURCE) */
|
| performance difference is 0.04 with good old plan, 0.46 with new one
| :-(
|
| Is there a way to force the optimizer to use a specific plan for
| dynamically generated statements without changing source code?
|
| Are there any other options left?
|
| Thanks in advance,
| Anke
Received on Mon Oct 06 2003 - 21:46:13 CDT