Avoiding reparsing SQL queries due to partition level DDLs – Part 1

A couple of weeks ago, I published a blog post that said specifying a partition name in the FROM clause of a query would prevent an existing cursor from being hard parsed when a partition is dropped from the same table. This was not correct.

It’s actually impossible for us not to re-parse the existing queries executing against the partitioned table when you drop a partition, because all of the partition numbers change during a drop operation. Since we display the partition numbers in the execution plan,  we need the re-parse each statement to generate a new version of the plan with the right partition information.

What actually happened in my example was the SQL statement with the partition name specified in the FROM clause reused child cursor 0 when it was hard parsed after the partition drop, while the SQL statement that just specified the table name in theFROM clause got a new child cursor 0.

But it’s not all bad news. I do have a solution that will reduce hard parses when executing DDL operations on partitioned tables that you can check out in part 2 of this blog post. But before you click over to read the alternative solution, let me explain in detail what was really happening in the original example I posted.

If you recall, we have a METER_READINGS table that is partitioned by time, with each hour being stored in a separate partition. Once an hour we drop the oldest partition in the table as a new partition is added. We also had two versions of the same SQL statement, one that explicitly specifies the partition name in the FROM clause and one that uses a selective WHERE clause predicate to prune the data set down to just 1 partition.

-- STMT with no partition name
 
SELECT   /* part_name_no */
         SUM(m.power_used)
FROM     METER_READINGS m
WHERE    m.time_id BETWEEN
         TO_DATE('2018-04-21 00:09:00', 'SYYYY-MM-DD HH24:MI:SS')
         AND TO_DATE('2018-04-21 00:10:00', 'SYYYY-MM-DD HH24:MI:SS');
 
SUM(M.POWER_USED)
------------------
86237.4
 
SELECT *
FROM   TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
SQL_ID dwjm8acfky1j8, child NUMBER 0
-------------------------------------------------------------------
SELECT /* part_name_no */ SUM(m.power_used)
FROM    meter_readings m  WHERE m.time_id BETWEEN
TO_DATE(' 2018-04-21 00:09:00', 'SYYYY-MM-DD HH24:MI:SS')
AND TO_DATE(' 2018-04-21 00:10:00', 'SYYYY-MM-DD HH24:MI:SS');
 
Plan hash VALUE: 2075634019
-------------------------------------------------------------------
| Id | Operation            | Name         | ROWS | Pstart| Pstop |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT      |              |      |       |       |
| 1 | SORT AGGREGATE        |              | 1    |       |       |
| 2 | PARTITION RANGE SINGLE|              | 98   | 113   |   113 |
|*3 | TABLE ACCESS FULL     |METER_READINGS| 98   | 113   |   113 |
-------------------------------------------------------------------
 
24 ROWS selected.
 
-- Same STMT but with partition explicitly named
 
SELECT /* part_name */
SUM(m.power_used)
FROM meter_readings partition (MR_180421_09) m;
 
SUM(M.POWER_USED)
------------------
86237.4
 
SELECT *
FROM   TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
SQL_ID 5bxq4xvdhp28p, child NUMBER 0
-------------------------------------------------------------------
SELECT /* part_name */ SUM(m.power_used)
FROM    meter_readings partition (MR_180421_09) m
 
Plan hash VALUE: 2075634019
 
-------------------------------------------------------------------
| Id | Operation            | Name         | ROWS | Pstart| Pstop |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT      |              |      |       |       |
| 1 | SORT AGGREGATE        |              | 1    |       |       |
| 2 | PARTITION RANGE SINGLE|              | 98   | 113   |   113 |
|*3 | TABLE ACCESS FULL     |METER_READINGS| 98   | 113   |   113 |
-------------------------------------------------------------------

As you can see the execution plans are identical, as are the query results. If I query v$SQL I see two distinct cursors, each of which has a single child cursor, child cursor 0.

SELECT sql_id, child_number child_num, loads, parse_calls parses, 
      executions exes,  invalidations, sql_text
  2  FROM   v$sql
  3  WHERE  sql_text LIKE 'SELECT /* part_name%';
 
SQL_ID	      CHILD_NUM  LOADS PARSES EXES INVALIDATIONS SQL_TEXT
------------- --------- ------ ------ ---- ------------- ------------------------------
5bxq4xvdhp28p	0        1	1      1	0        SELECT /* part_name */
							 SUM(s.amount_sold) FROM   sale
							 s partition (SALES_Q2_2000) s
 
dwjm8acfky1j8	0        1	1      1	0        SELECT /* part_name_no */
							 SUM(s.amount_sold) FROM	
							 sales s WHERE  s.time_id BETWEEN
							 	      TO_DATE(' 2000-04-0
							 1 00:00:00', 'SYYYY-MM-DD HH24
							 :MI:SS')	   AND TO_DATE('
							 2000-06-30 00:00:00', 'SYYYY-M
							 M-DD HH24:MI:SS')

You will also notice that each child cursor has been loaded only once and is currently valid. Now let’s drop the oldest partition in the table and see what impact it has on our cursors.

ALTER TABLE meter_readings DROP PARTITION MR_170421_09;
 
TABLE altered.

With the oldest partition gone, let’s check v$SQL to see what happened to our cursors.

SELECT sql_id, child_number child_num, loads, parse_calls parses, 
      executions exes,  invalidations, sql_text
  2  FROM   v$sql
  3  WHERE  sql_text LIKE 'SELECT /* part_name%';
 
SQL_ID	      CHILD_NUM  LOADS PARSES EXES INVALIDATIONS SQL_TEXT
------------- --------- ------ ------ ---- ------------- ------------------------------
5bxq4xvdhp28p	0        1	1      1	1        SELECT /* part_name */
							 SUM(s.amount_sold) FROM   sale
							 s partition (SALES_Q2_2000) s
 
dwjm8acfky1j8	0        1	1      1	1        SELECT /* part_name_no */
							 SUM(s.amount_sold) FROM	
							 sales s WHERE  s.time_id BETWEEN
							 	      TO_DATE(' 2000-04-0
							 1 00:00:00', 'SYYYY-MM-DD HH24
							 :MI:SS')	   AND TO_DATE('
							 2000-06-30 00:00:00', 'SYYYY-M
							 M-DD HH24:MI:SS')

What we find is both cursors have been invalidated because the partition numbers have changed.

Now if we re-execute both versions of our query and check the plans we see that they have new plans with the partition number 112 instead of the original 113. You should also notice that the hard parse count in v$mystat has increased, clearly indicating we had to hard parse the statements.

SELECT display_name, VALUE
FROM   v$mystat m, v$statname n
WHERE  display_name LIKE 'parse%'
AND    m.statistic#=n.statistic#;
 
DISPLAY_NAME							     VALUE
---------------------------------------------------------------- ----------
parse TIME cpu								63
parse TIME elapsed							77
parse COUNT (total)						       619
parse COUNT (hard)						       198 BEFORE 
parse COUNT (failures)                                                  0 
parse COUNT (DESCRIBE)                                                  0 
 
-- STMT with no partition name 
 
SELECT   /* part_name_no */          
         SUM(m.power_used) 
FROM     METER_READINGS m 
WHERE    m.time_id BETWEEN         
         TO_DATE('2018-04-21 00:09:00', 'SYYYY-MM-DD HH24:MI:SS')        
         AND TO_DATE('2018-04-21 00:10:00', 'SYYYY-MM-DD HH24:MI:SS'); 
 
SUM(M.POWER_USED) 
------------------ 
86237.4 
 
SELECT * 
FROM TABLE(dbms_xplan.display_cursor(statement_id='dwjm8acfky1j8',cursor_child_no='1'));
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
SQL_ID dwjm8acfky1j8, child NUMBER 1
-------------------------------------------------------------------
SELECT /* part_name_no */ SUM(m.power_used)
FROM    meter_readings m  WHERE m.time_id BETWEEN
TO_DATE(' 2018-04-21 00:09:00', 'SYYYY-MM-DD HH24:MI:SS')
AND TO_DATE(' 2018-04-21 00:10:00', 'SYYYY-MM-DD HH24:MI:SS');
 
Plan hash VALUE: 2075634019
-------------------------------------------------------------------
| Id | Operation            | Name         | ROWS | Pstart| Pstop |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT      |              |      |       |       |
| 1 | SORT AGGREGATE        |              | 1    |       |       |
| 2 | PARTITION RANGE SINGLE|              | 98   | 112   |   112 |
|*3 | TABLE ACCESS FULL     |METER_READINGS| 98   | 112   |   112 |
-------------------------------------------------------------------
 
24 ROWS selected.
 
-- Same STMT but with partition explicitly named
 
SELECT /* part_name */
     SUM(m.power_used)
FROM meter_readings partition (MR_180421_09) m;
 
SUM(M.POWER_USED)
------------------
86237.4
 
SELECT *
FROM  TABLE(dbms_xplan.display_cursor());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
SQL_ID 5bxq4xvdhp28p, child NUMBER 0
-------------------------------------------------------------------
SELECT /* part_name */ SUM(m.power_used)
FROM    meter_readings partition (MR_180421_09) m
 
Plan hash VALUE: 2075634019
 
-------------------------------------------------------------------
| Id | Operation            | Name         | ROWS | Pstart| Pstop |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT      |              |      |       |       |
| 1 | SORT AGGREGATE        |              | 1    |       |       |
| 2 | PARTITION RANGE SINGLE|              | 98   | 112   |   112 |
|*3 | TABLE ACCESS FULL     |METER_READINGS| 98   | 112   |   112 |
-------------------------------------------------------------------
 
SELECT sql_id, child_number child_num, loads, parse_calls parses, 
      executions exes,  invalidations, sql_text
  2  FROM   v$sql
  3  WHERE  sql_text LIKE 'SELECT /* part_name%';
 
SQL_ID	      CHILD_NUM  LOADS PARSES EXES INVALIDATIONS SQL_TEXT
------------- --------- ------ ------ ---- ------------- ------------------------------
5bxq4xvdhp28p	0        2	1      1	1        SELECT /* part_name */
							 SUM(s.amount_sold) FROM   sale
							 s partition (SALES_Q2_2000) s
 
dwjm8acfky1j8	1        1	1      1	0        SELECT /* part_name_no */
							 SUM(s.amount_sold) FROM	
							 sales s WHERE  s.time_id BETWEEN
							 	      TO_DATE(' 2000-04-0
							 1 00:00:00', 'SYYYY-MM-DD HH24
							 :MI:SS')	   AND TO_DATE('
							 2000-06-30 00:00:00', 'SYYYY-M
							 M-DD HH24:MI:SS')
 
SELECT display_name, VALUE
FROM   v$mystat m, v$statname n
WHERE  display_name LIKE 'parse%'
AND    m.statistic#=n.statistic#;
 
DISPLAY_NAME							     VALUE
---------------------------------------------------------------- ----------
parse TIME cpu								 63
parse TIME elapsed							 77
parse COUNT (total)							619
parse COUNT (hard)							208 AFTER
parse COUNT (failures)							 0
parse COUNT (DESCRIBE)							 0

So, why did the cursor with partition name specifically specified in the FROM clause reuse child cursor 0, while the other query got a new child cursor?

The cursor with partition name specifically specified in the FROM clause had it’s heap 6 (sql_area) flushed from the shared pool, which is why it’s able to reuse child_cursor 0 while the other cursor creates a new child cursor after the hard parse. Typically, all cursors that have been marked INVALID will have their heap 6 flushed from the shared pool and can reuse child cursor 0.

To find out how to prevent hard parsing when doing DDL operations on a partitioned table, check part 2 of this blog post!

This entry was posted in IoT, Partititoning, Top_Tip and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *