PARTITION HASH SUBQUERY Oddity

Having spent the last few months working in an environment where CURSOR_SHARING = FORCE I have seen a number of interesting issues, but the one I’ll focus on below has particularly grabbed my attention.

CURSOR_SHARING = FORCE is set at a database level for the example below and as you’ll see the use of a hint changes the output of the query… Clearly a bug.

SQL> select --+ query_01
  2         id, status, state
  3    from a
  4   where id in (select a.childid
  5  	             from b
  6  	            where b.parentid = '0123456789ABCDEF'
  7  	              and b.relation = 'TEST01');

no rows selected

But…

SQL> select --+ cursor_sharing_exact query_02
  2  	    id, status, state
  3    from a
  4   where id in (select a.childid
  5  	             from b
  6  	            where b.parentid = '0123456789ABCDEF'
  7  	              and b.relation = 'TEST01');

ID               STATUS                         S
---------------- ------------------------------ -
999030550440700D COMPLETE                       U
9990306350106Z07 COMPLETE                       U
9991304333406Q0J COMPLETE                       U
9991304334706P0T COMPLETE                       U
9992604330506Q0M COMPLETE                       U

* For reasons of confidentiality the table names, column names and data have been modified.

OK, so this isn’t anything too radical. We all know that wrong results can happen. They make us scared, but if we catch them and can work around the error then we can return to our happy lives.

I’ve skipped over a lot of the digging around I did get to this point, but for those that are interested the other workarounds that produced the correct results are:

SQL> select --+ query_03
  2         id, status, state
  3    from a
  4   where id in (select to_char(a.childid)
  5  	             from b
  6  	            where b.parentid = '0123456789ABCDEF'
  7  	              and b.relation = 'TEST01');

ID               STATUS                         S
---------------- ------------------------------ -
999030550440700D COMPLETE                       U
9990306350106Z07 COMPLETE                       U
9991304333406Q0J COMPLETE                       U
9991304334706P0T COMPLETE                       U
9992604330506Q0M COMPLETE                       U

And…

SQL> select --+ opt_param ('_subquery_pruning_enabled','false') query_04
  2         id, status, state
  3    from a
  4   where id in (select a.childid
  5  	             from b
  6  	            where b.parentid = '0123456789ABCDEF'
  7  	              and b.relation = 'TEST01');

ID               STATUS                         S
---------------- ------------------------------ -
999030550440700D COMPLETE                       U
9990306350106Z07 COMPLETE                       U
9991304333406Q0J COMPLETE                       U
9991304334706P0T COMPLETE                       U
9992604330506Q0M COMPLETE                       U

I’d found the workarounds used in query_03 and query_04 first (in that order) and looking at the execution plans for all of all the queries there are 2 distinct types of plan:

Query 01 and 02 have the following plan:

----------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |       |       |  1708 (100)|          |       |       |
|*  1 |  HASH JOIN RIGHT SEMI     |             |     1 |   104 |  1708   (2)| 00:00:21 |       |       |
|*  2 |   INDEX RANGE SCAN        | IX_PARENTID |     1 |    75 |     2   (0)| 00:00:01 |       |       |
|   3 |   PARTITION RANGE ALL     |             | 14478 |   410K|  1705   (1)| 00:00:21 |     1 |    54 |
|   4 |    PARTITION HASH SUBQUERY|             | 14478 |   410K|  1705   (1)| 00:00:21 | KEY(S | KEY(S |
|   5 |     TABLE ACCESS FULL     | A           | 14478 |   410K|  1705   (1)| 00:00:21 |     1 |   108 |
----------------------------------------------------------------------------------------------------------

And Query 02 and 04 have the following plan:

-----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |  1708 (100)|          |       |       |
|*  1 |  HASH JOIN RIGHT SEMI|             |     1 |   104 |  1708   (2)| 00:00:21 |       |       |
|*  2 |   INDEX RANGE SCAN   | IX_PARENTID |     1 |    75 |     2   (0)| 00:00:01 |       |       |
|   3 |   PARTITION RANGE ALL|             | 14478 |   410K|  1705   (1)| 00:00:21 |     1 |    54 |
|   4 |    PARTITION HASH ALL|             | 14478 |   410K|  1705   (1)| 00:00:21 |     1 |     2 |
|   5 |     TABLE ACCESS FULL| A           | 14478 |   410K|  1705   (1)| 00:00:21 |     1 |   108 |
-----------------------------------------------------------------------------------------------------

The key difference being the PARTITION HASH SUBQUERY vs PARTITION HASH ALL.

My view is that of the above workarounds the most appropriate depends on the number of subpartitions per partition, if you the query filters on the subpartition key and the number of distinct queries that will be hitting the database. However, this isn’t a post to discuss cursor reuse…

The above information is really just for completeness and context, but from this point on I’ll only be focusing on query_01 and query_02, and specifically on the recursive queries that run as part of the PARTITION HASH SUBQUERY operation.

The test case that follows does not give wrong results, but it does highlight the behaviour I want to draw attention to. I’m interested in the behaviour simply because it does not match with what I would expect, and having discussed it with others and tested other recursive queries, I’m drawing the conclusion that it must be a bug…

Test Case


set pages 300 lines 150 echo on
col string for a30

create user test identified by pass_word01
/

grant create session
    , create table
    , unlimited tablespace
    , alter session
   to test
/

conn test/pass_word01

create table part_tab (
       rec_date date
     , id number 
     , pad varchar2(300)
     , status number(1) default 0
     )
  partition by range (rec_date) 
    subpartition by hash (id) 
    subpartition template (
       subpartition a
     , subpartition b) (
       partition part_20100101 values less than (to_date('02-01-2010','DD-MM-YYYY'))
     , partition part_20100102 values less than (to_date('03-01-2010','DD-MM-YYYY'))
     , partition part_20100103 values less than (to_date('04-01-2010','DD-MM-YYYY'))
     , partition part_20100104 values less than (to_date('05-01-2010','DD-MM-YYYY'))
     , partition part_20100105 values less than (to_date('06-01-2010','DD-MM-YYYY'))
     , partition part_20100106 values less than (to_date('07-01-2010','DD-MM-YYYY'))
     , partition part_20100107 values less than (to_date('08-01-2010','DD-MM-YYYY'))
     , partition part_20100108 values less than (to_date('09-01-2010','DD-MM-YYYY'))
     , partition part_20100109 values less than (to_date('10-01-2010','DD-MM-YYYY'))
     , partition part_20100110 values less than (to_date('11-01-2010','DD-MM-YYYY'))
     , partition part_20100111 values less than (to_date('12-01-2010','DD-MM-YYYY'))
     , partition part_20100112 values less than (to_date('13-01-2010','DD-MM-YYYY'))
     , partition part_20100113 values less than (to_date('14-01-2010','DD-MM-YYYY'))
     , partition part_20100114 values less than (to_date('15-01-2010','DD-MM-YYYY'))
     , partition part_20100115 values less than (to_date('16-01-2010','DD-MM-YYYY'))
     , partition part_20100116 values less than (to_date('17-01-2010','DD-MM-YYYY'))
     , partition part_20100117 values less than (to_date('18-01-2010','DD-MM-YYYY'))
     , partition part_20100118 values less than (to_date('19-01-2010','DD-MM-YYYY'))
     , partition part_20100119 values less than (to_date('20-01-2010','DD-MM-YYYY'))
     , partition part_20100120 values less than (to_date('21-01-2010','DD-MM-YYYY'))
     )
/

set timing on
insert into part_tab (rec_date, id, pad)
select to_date(mod(rownum,20)+1 || '-01-2010','DD-MM-YYYY') rec_date
     , rownum id
     , rpad(rownum,300,'X')
  from dual connect by rownum <= 100000
/

exec dbms_stats.gather_table_stats(user,'PART_TAB')

create table other_tab (
       id number
     , name varchar2(30)
     , sex varchar2(1)
     )
/

insert into other_tab (id, name, sex)
select rownum id
     , username
     , decode(mod(rownum,2),1,'M','F')
  from all_users
/

exec dbms_stats.gather_table_stats(user,'OTHER_TAB')

alter session set cursor_sharing = force;

alter session set tracefile_identifier = PHS;

alter session set events '10046 trace name context level 4, forever';

set echo on lines 120 pages 100

col string for a30
select --+ query_01
       rec_date, id, substr(pad,1,20) string
  from part_tab
 where id in (select id
		from other_tab
	       where name = 'DBSNMP');

select --+ cursor_sharing_exact query_02
       rec_date, id, substr(pad,1,20) string
  from part_tab
 where id in (select id
		from other_tab
	       where name = 'DBSNMP');

alter session set events '10046 trace name context off';

Inspecting the generated trace file shows that when the recursive query for the PARTITION HASH SUBQUERY operation runs it uses “SELECT distinct TBL$OR$IDX$PART$NUM …” for each partition in order, I assume, to establish whether or not it needs to visit every subpartition in order to satisfy the query. And here’s the bit that just strikes me as wrong…

cursor_sharing = force results in recursive query:

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", 0, 2, 0, "ID") FROM (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE "OTHER_TAB"."NAME"=:B1) ORDER BY 1

cursor_sharing = exact results in recursive query:

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", :"SYS_B_0", :"SYS_B_1", :"SYS_B_2", "ID") FROM (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE "OTHER_TAB"."NAME"=:"SYS_B_3") ORDER BY :"SYS_B_4"

Using TKPROF to format the trace file gives:

When CURSOR_SHARING = FORCE

select --+ query_01
       rec_date, id, substr(pad,:"SYS_B_0",:"SYS_B_1") string
  from part_tab
 where id in (select id
		from other_tab
	       where name = :"SYS_B_2")

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.04          0       2448          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.04          0       2448          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  HASH JOIN RIGHT SEMI (cr=2588 pr=0 pw=0 time=19501 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=153 us)
  49846   PARTITION RANGE ALL PARTITION: 1 20 (cr=2581 pr=0 pw=0 time=549987 us)
  49846    PARTITION HASH SUBQUERY PARTITION: KEY(SUBQUERY) KEY(SUBQUERY) (cr=2581 pr=0 pw=0 time=381745 us)
  49846     TABLE ACCESS FULL PART_TAB PARTITION: 1 40 (cr=2441 pr=0 pw=0 time=149931 us)

********************************************************************************

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", 0, 2, 0, "ID") 
FROM
 (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE 
  "OTHER_TAB"."NAME"=:B1) ORDER BY 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE (cr=7 pr=0 pw=0 time=113 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=65 us)

********************************************************************************

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", 0, 2, 1, "ID") 
FROM
 (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE 
  "OTHER_TAB"."NAME"=:B1) ORDER BY 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE (cr=7 pr=0 pw=0 time=89 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=55 us)

********************************************************************************

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", 0, 2, 2, "ID") 
FROM
 (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE 
  "OTHER_TAB"."NAME"=:B1) ORDER BY 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE (cr=7 pr=0 pw=0 time=97 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=61 us)

********************************************************************************

<-- SNIP - Note how the 4th parameter to TBL$OR$IDX$PART$NUM is incremented -->

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", 0, 2, 19, "ID") 
FROM
 (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE 
  "OTHER_TAB"."NAME"=:B1) ORDER BY 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          7          0           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE (cr=7 pr=0 pw=0 time=108 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=70 us)

********************************************************************************

When CURSOR_SHARING = EXACT (via hint)

select --+ cursor_sharing_exact query_02
       rec_date, id, substr(pad,1,20) string
  from part_tab
 where id in (select id
		from other_tab
	       where name = 'DBSNMP')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.04          0       2448          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.04          0       2448          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  HASH JOIN RIGHT SEMI (cr=2588 pr=0 pw=0 time=20539 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=72 us)
  49846   PARTITION RANGE ALL PARTITION: 1 20 (cr=2581 pr=0 pw=0 time=550191 us)
  49846    PARTITION HASH SUBQUERY PARTITION: KEY(SUBQUERY) KEY(SUBQUERY) (cr=2581 pr=0 pw=0 time=386274 us)
  49846     TABLE ACCESS FULL PART_TAB PARTITION: 1 40 (cr=2441 pr=0 pw=0 time=199771 us)

********************************************************************************

SELECT distinct TBL$OR$IDX$PART$NUM("PART_TAB", :"SYS_B_0", :"SYS_B_1", 
  :"SYS_B_2", "ID") 
FROM
 (SELECT "OTHER_TAB"."ID" "ID" FROM "OTHER_TAB" "OTHER_TAB" WHERE 
  "OTHER_TAB"."NAME"=:"SYS_B_3") ORDER BY :"SYS_B_4"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       20      0.00       0.00          0          0          0           0
Execute     20      0.02       0.02          0          0          0           0
Fetch       40      0.00       0.00          0        140          0          20
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       80      0.02       0.03          0        140          0          20

Misses in library cache during parse: 20
Misses in library cache during execute: 20
Optimizer mode: ALL_ROWS
Parsing user id: 76     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT UNIQUE (cr=7 pr=0 pw=0 time=86 us)
      1   TABLE ACCESS FULL OTHER_TAB (cr=7 pr=0 pw=0 time=52 us)

********************************************************************************

I did a double take when I spotted this in the trace file, so let me point it out if it’s not clear:

CURSOR_SHARING = FORCE => recursive query uses literals!

CURSOR_SHARING = EXACT => recursive query uses system generated binds.

I’m not too surprised by the behaviour when CURSOR_SHARING = EXACT, but I didn’t expect to find that CURSOR_SHARING = FORCE causes the recursive query to use literals.

I found myself discussing this with Jeremy Schneider during OpenWorld and he seemed equally surprised by the evidence. Since getting back to the UK I have done some further testing and playing around. I’m now confident that that CURSOR_SHARING = FORCE does not cause all recursive queries to run as if CURSOR_SHARING = EXACT. In fact all the recursive queries I looked into ran as if CURSOR_SHARING = FORCE regardless of what CURSOR_SHARING was set to.

I have checked the OPTIMIZER_ENV_HASH_VALUE for both sets of recursive queries and it matches… I’m stuck for where to go next in order to explain this behaviour, and understand if it is by design. If anyone has any ideas then I’d love to know about them.

… And after all this playing around I’m still no closer to understanding what is actually causing the wrong results :-)

About these ads

2 thoughts on “PARTITION HASH SUBQUERY Oddity

  1. It looks like something is re-using the code to substitute system generated binds for literals. the bit that is throwing me is :

    ORDER BY :”SYS_B_4″

    That’s ordering by a bind value (ie a constant, at least in terms of the SQL) not by a number indicating a column position in the result set. Similar to :

    declare
    v_num number := 2;
    begin
    for c_rec in (select username from all_users order by v_num) loop
    dbms_output.put_line(c_rec.username);
    end loop;
    end;
    /

    As such, the result set won’t be ordered, rather than being ordered by the partition number. That would likely be the reason that, even if the bind variable values match the literals in the original SQL, the results are incorrect/processed incorrectly. And the bug may therefore only be apparent if the ordering is impacted in the example data.

    Have you logged an SR ?

    • Hi Gary,

      Thanks for the comment. Insightful as ever.

      Yes, an SR has been raised, but unfortunately I believe it has turned into a personal best when it comes to being ignored by the support analyst. Even my requests for acknowledgement that anyone is actually looking at it are being ignored :-( I assume that because I said we can work around the problem it is not seen as important. Personally, I see wrong results is a more serious problem than outright errors. I’d rather know something has failed than believe I’ve been provided with the correct results when in fact I haven’t.

      In response to your observation regarding the ORDER BY, interestingly when the query runs with CURSOR_SHARING = EXACT, and the recursive queries run with the system generated binds in the ORDER BY, the correct results are returned. Which, based on what you’ve pointed out, means the ORDER BY must not be required to get the right results?

      You’ve certainly got me thinking… Thank you.

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