| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Strange Cost Based Optimizer Decision Making
Hello there,
I'm experiencing some 'strangeness' from the CBO when running a query in an Oracle 8i (8.1.7) database.
Before I describe the problem, I'm just going to take some time out to request that all the Oracle Support-like users of this forum who will instantly say "You must upgrade to 10g R2 to solve this!", no disrespect intended, but please don't bother if that is all you have to contribute. We're fully aware that we need to upgrade, but a greater priority at this point in time is fixing the system within the bounds of 8i. This is a business decision, not a technical decision, and that's the way it's going to be played.
Anyway, with that out of the way, let me describe my problem (in the hope that Jonathan Lewis takes an active interest ;o)).
I have one very large range-partitioned table (on month), containing in the region of 672 million rows in total. There is a NON-UNIQUE composite index on two columns in this table; LINK_ID and TIME_STAMP. When I run the following query against it, I get a perfect execution plan for what I need:
QUERY 1 - HUGE_TABLE MAX(time_stamp) query results
MAX(TIME_
Execution Plan
0 SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=4 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL) 3 2 INDEX (FULL SCAN (MIN/MAX)) OF'HUGE_TABLE_PK'(NON-UNIQUE) (Cost=4 Card=315382450 Bytes=2523059600)
With only a 2.81 second execution time, this query does what it needs to do very quickly for such a large table. Note the "INDEX (FULL SCAN (MIN/MAX))" of the non-unique 2-column composite index on this table.
Now, I have another table that's not quite so large, containing over 65 million rows. The statistics on this table are 100% completely up to date. Once again, this table is range-partitioned on month. However, this time, there is a UNIQUE composite index present on two columns: SECTION_ID and TIME_STAMP.
I run an identical SELECT clause to the last query against this table. However, this is the execution plan I get:
QUERY 2 - LARGE_TABLE MAX(time_stamp) query results
MAX(TIME_
Execution Plan
0 SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=34394 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (ALL) 3 2 TABLE ACCESS (FULL) OF 'LARGE_TABLE' (Cost=34394Card=65623008 Bytes=524984064)
Right, I'm no longer getting a (MIN/MAX) full index scan. Instead I'm getting a full table scan of each partition, and the query is taking over four minutes to run for a table that is ten times smaller than the previous one. Now, the strange thing is, as I've already said, is all the stats are fully up to date for LARGE_TABLE. This includes the index stats.
The really strange thing is that the stats for HUGE_TABLE and its associated index are more than 6 months out of date, yet the execution plan for my query on this table is perfect, i.e. I get the (MIN/MAX) full index scan.
All indexes concerned are local (partitioned) indexes. The only structural difference between the two is that the index on HUGE_TABLE is non-unique, and the index on LARGE_TABLE is unique. Why would the unique index not be picked up for a (MIN/MAX) full index scan if the stats are all present and up-to-date?
I know some people reading this will be saying, "He must have missed something; it must be the stats.". For these people, the stats for both tables and indexes are displayed below.
COMPARISON BETWEEN GLOBAL TABLE STATS BETWEEN "HUGE_TABLE" AND
"LARGE_TABLE"
2 num_rows, 3 last_analyzed
TABLE_NAME NUM_ROWS LAST_ANAL
--------------- --------- --------- LARGE_TABLE 65623008 16-DEC-05 HUGE_TABLE 315382450 17-MAY-05
COMPARISON BETWEEN GLOBAL INDEX STATS BETWEEN "HUGE_TABLE" AND
"LARGE_TABLE"
SQL> SELECT index_name, 2 table_name, 3 uniqueness, 4 num_rows, 5 last_analyzed, 6 partitioned 7 FROM user_indexes
INDEX_NAME TABLE_NAME UNIQUENES NUM_ROWSLAST_ANAL PARTITIONED
----------------- ------------------------------ --------- ---------- --------- ----------- LARGE_TABLE_PK LARGE_TABLE UNIQUE 65623008 16-DEC-05 YES HUGE_TABLE_PK HUGE_TABLE NONUNIQUE 31618522917-MAY-05 YES COMPARISON OF TABLE PARTITION STATS BETWEEN "HUGE_TABLE" AND
TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANAL
--------------------- ---------------- ---------- --------- LARGE_TABLE JAN_2005 6880338 16-DEC-05 LARGE_TABLE FEB_2005 6175009 16-DEC-05 LARGE_TABLE MAR_2005 6853129 16-DEC-05 LARGE_TABLE APR_2005 6546195 16-DEC-05 LARGE_TABLE MAY_2005 6753167 16-DEC-05 LARGE_TABLE JUN_2005 6568995 16-DEC-05 LARGE_TABLE JUL_2005 6909996 16-DEC-05 LARGE_TABLE AUG_2005 6915392 16-DEC-05 LARGE_TABLE SEP_2005 5361894 16-DEC-05 LARGE_TABLE OCT_2005 0 16-DEC-05 LARGE_TABLE NOV_2005 0 16-DEC-05 LARGE_TABLE DEC_2005 0 16-DEC-05 LARGE_TABLE END_TIME 0 16-DEC-05 LARGE_TABLE DEC_2004 6658893 16-DEC-05 TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANAL --------------------- ---------------- ---------- --------- HUGE_TABLE JAN_2005 60081900 17-MAY-05 HUGE_TABLE FEB_2005 51027010 17-MAY-05 HUGE_TABLE END_TIME HUGE_TABLE DEC_2005 HUGE_TABLE DEC_2004 58722850 17-MAY-05 HUGE_TABLE NOV_2005 HUGE_TABLE JUN_2005 HUGE_TABLE JUL_2005 HUGE_TABLE APR_2005 56117100 17-MAY-05 HUGE_TABLE MAY_2005 30367650 17-MAY-05 HUGE_TABLE AUG_2005 HUGE_TABLE SEP_2005 HUGE_TABLE OCT_2005 HUGE_TABLE MAR_2005 59288350 17-MAY-05
COMPARISON OF INDEX PARTITION STATS BETWEEN "HUGE_TABLE_PK" AND
"LARGE_TABLE_PK"
2 partition_name, 3 num_rows, 4 last_analyzed
INDEX_NAME PARTITION_NAME NUM_ROWS LAST_ANAL
--------------------- --------------- ---------- --------- LARGE_TABLE_PK JAN_2005 6880338 16-DEC-05 LARGE_TABLE_PK FEB_2005 6175009 16-DEC-05 LARGE_TABLE_PK MAR_2005 6853129 16-DEC-05 LARGE_TABLE_PK APR_2005 6546195 16-DEC-05 LARGE_TABLE_PK MAY_2005 6753167 16-DEC-05 LARGE_TABLE_PK JUN_2005 6568995 16-DEC-05 LARGE_TABLE_PK JUL_2005 6909996 16-DEC-05 LARGE_TABLE_PK AUG_2005 6915392 16-DEC-05 LARGE_TABLE_PK SEP_2005 5361894 16-DEC-05 LARGE_TABLE_PK OCT_2005 0 16-DEC-05 LARGE_TABLE_PK NOV_2005 0 16-DEC-05 LARGE_TABLE_PK DEC_2005 0 16-DEC-05 LARGE_TABLE_PK END_TIME 0 16-DEC-05 LARGE_TABLE_PK DEC_2004 6658893 16-DEC-05 SQL> SELECT index_name, 2 partition_name, 3 num_rows, 4 last_analyzed
INDEX_NAME PARTITION_NAME NUM_ROWS LAST_ANAL
--------------------- --------------- ---------- --------- HUGE_TABLE_PK MAY_2005 30618961.3 17-MAY-05 HUGE_TABLE_PK JUN_2005 HUGE_TABLE_PK MAR_2005 59092640 17-MAY-05 HUGE_TABLE_PK FEB_2005 51459094.7 17-MAY-05 HUGE_TABLE_PK APR_2005 56270813.7 17-MAY-05 HUGE_TABLE_PK JAN_2005 59800077.5 17-MAY-05 HUGE_TABLE_PK DEC_2005 HUGE_TABLE_PK END_TIME HUGE_TABLE_PK NOV_2005 HUGE_TABLE_PK DEC_2004 58943641.5 17-MAY-05 HUGE_TABLE_PK JUL_2005 HUGE_TABLE_PK AUG_2005 HUGE_TABLE_PK SEP_2005 HUGE_TABLE_PK OCT_2005
If the situation was the reverse, I would immediately blame the lack of stats on HUGE_TABLE and its index. However, LARGE_TABLE and its index have up-to-date stats, yet the CBO is choosing to full table scan the partitions of this table, rather than (MIN/MAX) full index scan its unique local index.
For a table and index with massively out of date stats, the CBO is choosing a (MIN/MAX) full index scan for the desired query.
Does anybody reading this feel they can shed any light on why the CBO is behaving in this way, and what I might have missed?
Thanks in advance.
James Received on Mon Dec 19 2005 - 09:17:40 CST
 |
 |