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.
Well, dang. That’s a game changer. Thanks for the timely post Maria!
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