Partitioned Table Slower - Where's Tim Gorman when you need him?

All, I'm experimenting with partitioning in 10g (10.2.0.3 64-bit) over Oracle Enterprise Linux 5 64-bit. I am using ASM over FC tied in to a NetApp FAS 3020 HA cluster. I have a table with 8 million records of which one particular group of records takes about 30%. In trying to see if partitioning them off to their own partition with local prefixed indexes matching the indexes of the original heap table, I'm finding that the original heap table query with regular btree indexes is consistently faster and shows a lower total cost in the explain plan. My query specifically includes a predicate that causes the optimizer to select the partitioned indexes and partitioned section of the table. The cost using the heap table is 6 and the cost of the partitioned table is over 8000. All statistics have been gathered and updated. This is a multijoin query of 4 tables and only the one large table have I chose for this partition test. Some tkprof analysis shows that using the partition results in it reading over 2 million records whereas the same index on the heap table only reads about 51000. The query portion of tkprof shows the large discrepancy. Can someone help my understanding of why the partition chooses to read all of the records in the partition whereas the heap table doesn't? Does it have to do with the selectivity of the index at the local partition level and that somehow the btree's are built differently within a partition than they are for heaps?

all count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch        2      0.17       0.17          0       4963
0           7

total        4      0.17       0.17          0       4963
0           7

Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 115

Rows Row Source Operation
------- ---------------------------------------------------

      7  SORT ORDER BY (cr=4963 pr=0 pw=0 time=172431 us)
      7   HASH UNIQUE (cr=4963 pr=0 pw=0 time=172288 us)
      7    COUNT STOPKEY (cr=4963 pr=0 pw=0 time=71444 us)
      7     NESTED LOOPS OUTER (cr=4963 pr=0 pw=0 time=71327 us)
      7      NESTED LOOPS OUTER (cr=4940 pr=0 pw=0 time=69933 us)
      7       NESTED LOOPS  (cr=4917 pr=0 pw=0 time=68401 us)
      7        TABLE ACCESS BY INDEX ROWID ASN (cr=4894 pr=0 pw=0 time=67058
us)
  51560         INDEX RANGE SCAN ASN_ORG_IX (cr=323 pr=0 pw=0 time=464207
us)(object id 55269)
      7        TABLE ACCESS BY INDEX ROWID B1PT (cr=23 pr=0 pw=0 time=956
us)
      7         INDEX UNIQUE SCAN B1PT_PK (cr=16 pr=0 pw=0 time=484
us)(object id 54884)
      7       TABLE ACCESS BY INDEX ROWID B3AD (cr=23 pr=0 pw=0 time=860 us)
      7        INDEX RANGE SCAN B3AD_PK (cr=16 pr=0 pw=0 time=491 us)(object
id 54895)
      7      TABLE ACCESS BY INDEX ROWID B3OW (cr=23 pr=0 pw=0 time=899 us)
      7       INDEX RANGE SCAN B3OW_PK (cr=16 pr=0 pw=0 time=495 us)(object

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.02       0.02          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch        2      5.20       5.09          0      83809
0           7

total        4      5.23       5.11          0      83809
0           7

Rows     Row Source Operation


-------  ---------------------------------------------------

      7  SORT ORDER BY (cr=83809 pr=0 pw=0 time=5091910 us)
      7   HASH UNIQUE (cr=83809 pr=0 pw=0 time=5091816 us)
      7    COUNT STOPKEY (cr=83809 pr=0 pw=0 time=93934 us)
      7     NESTED LOOPS OUTER (cr=83809 pr=0 pw=0 time=93828 us)
      7      NESTED LOOPS OUTER (cr=83786 pr=0 pw=0 time=92905 us)
      7       NESTED LOOPS  (cr=83763 pr=0 pw=0 time=91989 us)
      7        PARTITION LIST SINGLE PARTITION: KEY KEY (cr=83740 pr=0 pw=0
time=91089 us)
      7         TABLE ACCESS BY LOCAL INDEX ROWID ASN_PART PARTITION: 1 1
(cr=83740 pr=0 pw=0 time=90954 us)
2540907          INDEX RANGE SCAN ASN_PART_ORG_IX PARTITION: 1 1 (cr=9988
pr=0 pw=0 time=22868284 us)(object id 64296)
      7        TABLE ACCESS BY INDEX ROWID B1PT (cr=23 pr=0 pw=0 time=815
us)
      7         INDEX UNIQUE SCAN B1PT_PK (cr=16 pr=0 pw=0 time=388
us)(object id 54884)
      7       TABLE ACCESS BY INDEX ROWID B3AD (cr=23 pr=0 pw=0 time=639 us)

      7        INDEX RANGE SCAN B3AD_PK (cr=16 pr=0 pw=0 time=381 us)(object
id 54895)
      7      TABLE ACCESS BY INDEX ROWID B3OW (cr=23 pr=0 pw=0 time=705 us)
      7       INDEX RANGE SCAN B3OW_PK (cr=16 pr=0 pw=0 time=351 us)(object

.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jan 08 2008 - 09:31:49 CST