Partitioned Table Slower - Where's Tim Gorman when you need him?
- From: "Brian Lucas" <moabrivers@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 8 Jan 2008 08:31:49 -0700
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
id 54914)
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
id 54914)
.
Other related posts:
