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.
Continue reading “Avoiding reparsing SQL queries due to partition level DDLs – Part 2”

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.

Continue reading “Avoiding reparsing SQL queries due to partition level DDLs – Part 1”

Day 3 of Oracle OpenWorld 2017

Hopefully you enjoyed yesterday, the second full day of technical sessions at Oracle OpenWorld and are ready for more today!

At 11:30 am today I give my first technical session, with the wonder Dominic Giles of Swingbench fame!

Dom and I will be talking about the Oracle Database & the Internet of Things in Moscone West, room 3011. In this session we will provide step-by-step instructions for deploying a high-ingest, mission critical IoT workload on Oracle D atabase. There will be even some demos to proving the impact of each recommendations. So don’t missing it!

You can also get a SQLMaria laptop sticker at the session!

Continue reading “Day 3 of Oracle OpenWorld 2017”

Oracle Database Audio White Papers

Back in April, I shared with you a new white paper that described a set of best practices or management techniques to facilitate IoT workloads with the Oracle Database. Since then there have been a number of updates in this space that I thought were worthy of a follow up post.

Just in case you haven’t found the time to read the white paper yet, don’t panic because you can now listen to it! Continue reading “Oracle Database Audio White Papers”

Best Practices For Large Volume or IoT Workloads

Over the last few years there has been a rapid surge in the adoption of smart devices. Everything from phones and tablets, to smart meters and fitness devices, can connect to the Internet and share data. You only have to follow @MarkRittman and his experiences with getting his kettle to boil remotely to see just how many devices within your own home can connect to the internet.

With all of these smart devices, comes a huge increase in the frequency and volume of data being ingested into and processed by databases. This scenario is commonly referred to as the Internet of Things or IoT.

Some people assume that a NoSQL database is required for an IoT workload because the ingest rate required exceeds the capabilities of a traditional relational database. This is simply not true.

Continue reading “Best Practices For Large Volume or IoT Workloads”