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 12 Release 2 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 12cR2 that will not invalidate cursors for  EXCHANGE PARTITION,  TRUNCATE PARTITION or MOVE PARTITION is fully backportable to 11.2.0.4 or 12c R1. 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.

2 Responses 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!

  2. RobK says:

    Dear Maria,

    for me it is rather difficult to grasp the importance of this feature. Are there many customers who have so frequent DDLs on partitioned tables, and so many of these affected tables that this feature is a relief for them in terms of performance?

    In contrast there is another feature request (potential help for big number of clients and backed by some famous:)) tuning experts)
    https://community.oracle.com/ideas/21032
    ENHANCEMENT REQUEST 26588854 – ER: ADD UNBOUND SQL SECTION IN AWR.

    What are your thoughts? How come this “Avoiding reparsing…” feature got implemented. There are many more useful idea coming from the community I think.

    Thanks,
    RobK

Leave a Reply

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