A quick check-list on testing new partitioning features:
- Step 1 – test with a small amount of data in place, just to see if it works at all
- Step 2 – test with a reasonably large amount of data in place so that you can spot any unexpected time being lost
- Step 3 – test with a few uncommitted transactions from other sessions so that you can spot locking problems easily
- Step 4 – test with SQL tracing enabled so that you can check the trace files for any recursive SQL threats
- Step 5 – (optional, and harder to recognise) test with event 10704 set so that you can check the trace files for locking threats
Whatever else you do, though, make sure that you always have some data in every object because Oracle has some special optimisations for dealing with partitions that are known to be empty so, in the absence of data, you may be testing something that will never happen in production.
Update (in response to comment 1)
An example of what you do, and what you might see, with sql_trace or event 10704 enabled (the latter at level 10):
alter table pt_range exchange partition p600 with table t1 including indexes without validation ;
A couple of interesting items from SQL_trace – note particularly the join which is doing some validation and could be quite expensive, but unnoticeable unless you were testing at scale:
LOCK TABLE "PT_RANGE" PARTITION ("P600") ,"T1" IN EXCLUSIVE MODE NOWAIT select /*+ first_rows(1) ordered */ 1 from "TEST_USER"."T1" A ,"TEST_USER"."PT_RANGE" B where "A"."ID" = "B"."ID" and ( tbl$or$idx$part$num("TEST_USER"."PT_RANGE",0,0,0 ,B .rowid ) < 3 ) and tbl$or$idx$part$num("TEST_USER"."PT_RANGE",0,0,0 ,A .ID ) <> 3 and rownum < 2
And a little something for locking (I grep’ed for “TM” – table locking):
ksqgtl *** TM-000130a9-00000000 mode=6 flags=0x401 timeout=0 *** ksqgtl *** TM-000130ab-00000000 mode=3 flags=0x401 timeout=0 *** ksqgtl *** TM-000130b1-00000000 mode=6 flags=0x401 timeout=0 ***
Note that the (hex) number after the TM is the object id, and the lock mode is clearly visible. In this case we have signs of a row-exclusive lock on the partitioned table, and exclusive locks on the simple table and the partition that was being exchanged. The ksqgtl is getting the lock, ksqrcl (not shown) is the release.