Oracle Scratchpad

October 11, 2013

Testing Partitions

Filed under: Oracle,Partitioning — Jonathan Lewis @ 7:47 am BST Oct 11,2013

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 in production, but unnoticeable in development unless you were testing to scale:


select /*+ first_rows(1) ordered */ 1
 "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.


  1. Would definitely like more info on steps 4 and 5…

    Comment by Jason Bucata — October 11, 2013 @ 2:42 pm BST Oct 11,2013 | Reply

  2. 6. Test in combination with other features – thinking of an ORA-00600 bug in with interval partitioning and dynamic sampling and a current problem that I’ve got that seems to involve daily interval partitioning and one node of a two node active dataguard standby (but might be jumping the gun as to cause).

    But I suppose that point applies to any feature as a significant proportion of bugs arise when you combine features.

    Comment by Dom Brooks — October 11, 2013 @ 4:15 pm BST Oct 11,2013 | Reply

    • Dom,

      I find the “combination of features” is often the breaking point. I think your point 6 falls into the more general point that your test should eventually emulate what you’re really trying to do.

      Comment by Jonathan Lewis — October 11, 2013 @ 6:50 pm BST Oct 11,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by