I am working on an SR with Oracle support and they asked me to do a 10128 trace to look at how the optimizer is doing partition pruning. I did some quick research on this trace and wanted to pass it along.
Here are the names of the two Oracle support documents that I found most helpful:
How to see Partition Pruning Occurred? (Doc ID 166118.1)
Partition Pruning Min/Max Optimization Fails when Parallel Query Run in Serial (Doc ID 1941770.1)
The first was the one Oracle support recommended. But, the SR said to run both a level 2 and a level 7 trace and the first document did not mention level 7. But, the second document has an example of a level 7 trace and more details on how to set it up.
I also found these two non-Oracle sites or blog posts:
http://cbohl.blogspot.com/2006/10/verify-that-partition-pruning-works.html
http://www.juliandyke.com/Diagnostics/Events/EventReference.html#10128
I do not have time to delve into this further now but if you are trying to understand partition pruning then the 10128 trace may help you understand how it works.
– Bobby
