Bind variable peeking and partitioned tables

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.

About these ads

2 thoughts on “Bind variable peeking and partitioned tables

  1. SQL> INSERT /*+ APPEND */ INTO ptab (partid,id1,id2,desc1,desc2)
    2 SELECT MOD(ROWNUM,5)+1
    3 , MOD(ROWNUM,2000)+1
    4 , ROW_NUMBER() OVER (PARTITION BY MOD(ROWNUM,5)+1 ORDER BY ROWNUM)
    5 , RPAD(‘a’,30,’a’)
    6 , RPAD(‘b’,30,’b’)
    7 FROM dual
    8 CONNECT BY ROWNUM true);
    CONNECT BY ROWNUM true)
    *
    ERROR at line 8:
    ORA-00920: invalid relational operator

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s