Search

Top 60 Oracle Blogs

Recent comments

January 2017

Graphics for SQL Optimization

Dan Tow, in his book SQL Tuning, lays out a simple method of tuning SQL queries. The method is

  • Draw a diagram of each table in the query with Children above Parents
  • Draw join lines between each join (many-to-many, one-to-many)
  • Mark each table with a predicate filter and calculate the amount of table filtered out

Then to find a great optimal optimization path candidate

  1. Start at the table with the strongest predicate filter (the filter that returns the fewest % of the table)
  2. join down to children (if multiple children join to child with strongest predicate filter)
  3. If you can’t join to children, join up to parent

The basics are pretty simple and powerful. Of course there are many cases that get more complex and Dan goes into these complex cases in his book.

Hot cloning and refreshing PDBs in #Oracle 12cR2

Hot cloning PDBs is new in 12.2, so you don’t have to put the source PDB into READ ONLY mode before the cloning if you have it in local undo mode. I suppose shared undo mode will become very unpopular. Another 12.2 New Feature is the option to clone a PDB that can be refreshed from the source PDB. I will show both features with this article, but you may of course do hot cloning without a later refresh. In this case, just leave out the REFRESH MANUAL clause and you don’t have to OPEN READ ONLY the cloned PDB afterwards. On a high level, what I demonstrate is this:

Oracle Database Cloud (DBaaS) Performance - Part 1 - CPU

After having looked at the performance consistency provided by the Oracle Database Cloud offering in the previous series, I'll focus here on the raw performance figures I've measured during my tests, starting with the CPU related performance findings.

One of the first surprises is related to the fact that Oracle uses a unit called "OCPU" to specify the CPU capacity provided, which is explained here:

Trusted Information Sharing – Some Underlying Concepts

In a recent post, I explained a little bit about what my new role at archTIS is. archTIS is a company that focuses on the area of Trusted Information Sharing. Trusted Information Sharing is a concept that not too many people would understand the complexities of. In fact, when I first started in my new role I wasn’t aware of just how complex it was myself! To explain all that complexity in a single post would make for an incredibly long post, so what I’m going to do is explain it in a series of blog posts. Doing so will help you (the reader) understand this more readily, and assist me in checking my level of understanding as well. So let’s start off at the very basic level of some of the underlying concepts of Trusted Information Sharing (hereafter referred to as TIS) and why we need to have it.

Happy New YouTube Channel!

As announced, I start into 2017 with my own YouTube channel. You can access it at the right upper part of this site by clicking on the red icon:

Removing Unnecessary Indexes: 1. The Need for Extended Statistics

This is the first post in a series about unnecessary indexes and some of the challenges that they present 

I have always noted that it is remarkably easy to add indexes to systems, especially OLTP systems. However, removing them is generally more difficult.
The reward for removing an unused, or at least an unnecessary, index is that you no longer need to maintain it during DML operations, thus reducing I/O and not loading index blocks into the buffer cache. However, the risk is that performance will degrade somewhere, because an execution plan changes.