We all know about the pitfalls of bind variables combined with histograms on skewed data. The example below shows how table partitions can act just like a histogram and how bind variale peeking can provide the same headaches.
CREATE TABLE ptab
( partid number
, id1 number
, id2 number
, desc1 varchar2 (30)
, desc2 varchar2 (30))
PARTITION BY LIST (partid)
( partition p1 values (1)
, partition p2 values (2)
, partition p3 values (3)
, partition p4 values (4)
, partition p5 values (5)
, partition p6 values (6));
INSERT /*+ APPEND */ INTO ptab (partid,id1,id2,desc1,desc2)
SELECT MOD(ROWNUM,5)+1
, MOD(ROWNUM,2000)+1
, ROW_NUMBER() OVER (PARTITION BY MOD(ROWNUM,5)+1 ORDER BY ROWNUM)
, RPAD('a',30,'a')
, RPAD('b',30,'b')
FROM dual
CONNECT BY ROWNUM <= 300000);
COMMIT;
CREATE INDEX ptab_idx ON ptab (partid,id1);
exec dbms_stats.gather_table_stats(null,'ptab',method_opt=>'for all columns size repeat',cascade=>true);
SELECT partition_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'PTAB'
ORDER BY 1;
PARTITION_NAME NUM_ROWS
------------------------------ ----------
P1 600751
P2 601714
P3 598125
P4 602704
P5 602127
P6 0
Note above that we have 5 evenly populated partitions and one empty one. The optimiser is aware of these figures as you can see in the queries/execution plans below.
set autotrace traceonly explain SELECT desc1, desc2 FROM ptab WHERE partid = 1 AND id1 = 1; --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1502 | 101K| 1484 (1)| 00:00:18 | | | | 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| PTAB | 1502 | 101K| 1484 (1)| 00:00:18 | 1 | 1 | |* 2 | INDEX RANGE SCAN | PTAB_IDX | 1474 | | 7 (0)| 00:00:01 | | | --------------------------------------------------------------------------------------------------------------- set autotrace traceonly explain SELECT desc1, desc2 FROM ptab WHERE partid = 6 AND id1 = 1; ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 60 | 2 (0)| 00:00:01 | | | | 1 | PARTITION LIST SINGLE| | 1 | 60 | 2 (0)| 00:00:01 | 6 | 6 | |* 2 | TABLE ACCESS FULL | PTAB | 1 | 60 | 2 (0)| 00:00:01 | 6 | 6 | ----------------------------------------------------------------------------------------------
The optimiser is aware that an index is the best option for a populated partition but a full partition scan (note the “Pstart” and “Pstop” columns) is acceptable for the empty partition. Now we introduce bind variables into the equation.
set autotrace traceonly explain variable v_partid number exec :v_partid := 6 SELECT desc1, desc2 FROM ptab WHERE partid = :v_partid AND id1 = 1; ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 299 | 20631 | 1259 (3)| 00:00:16 | | | | 1 | PARTITION LIST SINGLE| | 299 | 20631 | 1259 (3)| 00:00:16 | KEY | KEY | |* 2 | TABLE ACCESS FULL | PTAB | 299 | 20631 | 1259 (3)| 00:00:16 | KEY | KEY | ---------------------------------------------------------------------------------------------- exec :v_partid := 1 SELECT desc1, desc2 FROM ptab WHERE partid = :v_partid AND id1 = 1; ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 299 | 20631 | 1259 (3)| 00:00:16 | | | | 1 | PARTITION LIST SINGLE| | 299 | 20631 | 1259 (3)| 00:00:16 | KEY | KEY | |* 2 | TABLE ACCESS FULL | PTAB | 299 | 20631 | 1259 (3)| 00:00:16 | KEY | KEY | ----------------------------------------------------------------------------------------------
Yikes! Now we’re full scanning our 600k row partition and performance has taken a nose dive. This can be particularly problematic where partitions are added for specific time periods (e.g. each month) and then populated over that time period. Just after the addition you have an empty partition which could cause queries on other partitions to be affected.
The solution? Accurate statistics and literal values for partition keys.
ORAganism 