I took a 10046 level 12 trace of previous example. I really want to know how much CPU will be consumed by this small sort operation. At lease sort require Oracle allocate the sort memory, and then run the sort function. Some time you system may have thousands of sort per second, if we cut half of the sort by change the SQL, how much load will be reduced? I cannot image it. I will do more research on sort later.
Following is the trace output with bind variable on partitioned column (with sort):
SELECT * FROM TEST_SORT
WHERE COL2=:p_co1 AND PKEY=:p_key AND COL1 IN (810,510,210)
ORDER BY COL1
Rows Row Source Operation
------- ---------------------------------------------------
3 SORT ORDER BY (cr=9 r=0 w=0 time=390 us)
3 PARTITION ... (cr=9 r=0 w=0 time=246 us)
3 INLIST ITERATOR (cr=9 r=0 w=0 time=240 us)
3 TABLE ACCESS BY ... (cr=9 r=0 w=0 time=205 us)
3 INDEX RANGE SCAN ... (cr=6 r=0 w=0 time=141 us)
Following is the trace output with constant value on partitioned column (without sort):
SELECT * FROM TEST_SORT
WHERE COL2=:p_co1 AND PKEY=0 AND COL1 IN (810,510,210)
ORDER BY COL1
Rows Row Source Operation
------- ---------------------------------------------------
3 INLIST ITERATOR (cr=10 r=0 w=0 time=253 us)
3 TABLE ACCESS BY ... (cr=10 r=0 w=0 time=211 us)
3 INDEX RANGE SCAN ... (cr=7 r=0 w=0 time=152 us)
We could find that if we ignore the sort step, the CPU usage is basically equal (246 us vs. 253 us), but with sort, the CPU usage increased to 390 us, although the rows already be sorted! Here just three rows, but 50% increase of CPU usage per execution. You could image when there are thousands of sort in your OLTP system.
I have tested this on 9207 and 10g, there is no sort for this case, seems code changed in Oracle. My original environment is Oracle 9205.