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

In my pervious post, I promised to provide an alternative solution to avoiding repasing SQL queries due to partition level DDLs.

So, what is it?

In Oracle Database 18c we implementing a fine-grained cursor invalidation mechanism, so that cursors can remain valid if they access partitions that are not the target of an EXCHANGE PARTITION, TRUNCATE PARTITION or MOVE PARTITION command.

As I said in my previous post, this enhancements can’t help in the case of a DROP PARTITION command due to the partition number changing but hopefully you can change the DROP to either an EXCHANGE PARTITION or a TRUNCATE PARTITION command to avoid the hard parse, as I have done in the example below. 

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 will now TRUNCATE 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.

Let’s begin by executing both queries and checking their execution plans.

-- 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   | 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 |
-------------------------------------------------------------------

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')

Each child cursor has been loaded only once and is currently valid. Now let’s TRUNCATE the oldest partition in the table and see what impact it has on our cursors.

ALTER TABLE sales TRUNCATE PARTITION MR_170422_09;
 
TABLE truncated.
 
commit;
 
Commit complete.

With the oldest partition truncated, 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	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')

As you can see, nothing has happen to our cursors. Neither of them were invalidated. Just to be sure let me re-execute both statements and check v$SQL to see what happened to our cursors.

SQL> SELECT display_name, VALUE
  2  FROM   v$mystat m, v$statname n
  3  WHERE display_name LIKE 'parse%'
  4  AND   m.statistic#=n.statistic#;
 
DISPLAY_NAME							      VALUE
---------------------------------------------------------------- ----------
parse TIME cpu								161
parse TIME elapsed							155
parse COUNT (total)							953
parse COUNT (hard)							363
parse COUNT (failures)							  0
parse COUNT (DESCRIBE)							  0
 
6 ROWS selected.
 
 
-- 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 
 
-- 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
 
SQL> SELECT display_name, VALUE
  2  FROM   v$mystat m, v$statname n
  3  WHERE display_name LIKE 'parse%'
  4  AND   m.statistic#=n.statistic#;
 
DISPLAY_NAME							      VALUE
---------------------------------------------------------------- ----------
parse TIME cpu								161
parse TIME elapsed							155
parse COUNT (total)							956
parse COUNT (hard)							363
parse COUNT (failures)							  0
parse COUNT (DESCRIBE)							  0
 
6 ROWS selected.
 
 
 
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	2      2	0        SELECT /* part_name */
							 SUM(s.amount_sold) FROM   sale
							 s partition (SALES_Q2_2000) s
 
dwjm8acfky1j8	0        1	2      2	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')

So we see both cursors are still valid and have been loaded only once. But both cursors have executed twice. What is interesting is the parse calls have also gone up. So did we avoid the hard parse or not?

We can confirm we avoided the hard parse by looking at the session statistic “parse count (hard)” which has remained constant at 363 before and after we executed the queries. So, these are just soft parses.

Now if you are thinking that’s great Maria but I’m not on 18c, don’t panic.

The fix in 18c that will not invalidate cursors for  EXCHANGE PARTITION,  TRUNCATE PARTITION or MOVE PARTITION is fully backportable to 11.2.0.4 or 12c. All you need to do is request a back port for Bug ID 24817834.

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

One Response to Avoiding reparsing SQL queries due to partition level DDLs – Part 2

  1. Andy Klock says:

    Well, dang. That’s a game changer. Thanks for the timely post Maria!

Leave a Reply

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