SQL Tuning Workshop

Last week I had the pleasure of delivering a five-part SQL Tuning Workshop for my local Oracle User Group –  Northern California Oracle User Group. The workshop explains the fundamentals of the cost-based optimizer, the statistics that feed it, the hints that influence it and key tools you need to exam executions plans.

The workshop also provides a methodology for diagnosing and resolving the most common SQL execution performance problems. Given the volume of interest in this content, I want to share all of the material from the workshop here and give you links to additional material on each of the 5 topics.

Part 1 Understanding the Oracle Optimizer

The first part of the workshop covers the history of the Oracle Optimizer and explains the first thing the Optimizer does when it begins to optimize a query – query transformation.

Query transformations or the rewriting of the SQL statement into a semantically equivalent statement allows the Optimizer to consider alternative methods of processing or executing that query, which are often more efficient than the original SQL statement would allow. the majority of Oracle’s query transactions are now cost-based, which means the Optimizer will cost the plan with and with the query transformation and pick the plan with the lowest cost. With the help of the Optimizer development team, I’ve already blogged about a number of these transformations including:

You can also download the slides here.

Part 2 Best Practices for Managing Optimizer Statistics

Part 2 of the workshop focuses on Optimizer Statistics and the best practices for managing them, including when and how to gather statistics, including fixed object statistics.
Continue reading “SQL Tuning Workshop”

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”

Extremely useful Partitioning enhancements in Oracle Database 12c

Back in January, I promised I would write a series of blogs on some of my favorite new 12c enhancements, designed to make your life easier. I’m finally getting around to keeping that promise with this weeks blog post on my favorite partitioning enhancements.

Imagine you have a large SALES table that contains information on all of the sales we have had in our chain of department stores.

DESC sales
 Name	          NULL?    TYPE
 ----------     -------- ------------------------
 PROD_ID        NOT NULL NUMBER(6)
 CUST_ID        NOT NULL NUMBER
 TIME_ID        NOT NULL DATE
 CHANNEL_ID     NOT NULL CHAR(1)
 PROMO_ID	NOT NULL NUMBER(6)
 QUANTITY_SOLD	NOT NULL NUMBER(3)
 AMOUNT_SOLD	NOT NULL NUMBER(10,2)
 REGION 	NOT NULL CHAR(3)

There are also 4 indexes on our SALES table to help with our analytic queries.

Continue reading “Extremely useful Partitioning enhancements in Oracle Database 12c”