Category Archive: Tuning

Jul 20

The Performance Tuning Maze

One day, you wake up and find that you are an Oracle database administrator. The gods have finally seen the light to your true potential and allowed you to work in the best job in the world! You begin your DBA career so bright-eyed and bushy-tailed. You’re creating new databases, granting privileges, writing PL/SQL code. …

Continue reading »

Apr 17

SQL Dev 4.2 Top SQL

One of the things that I’ve always liked about SQL Developer is how it saves me time. Sure, I get this fancy looking GUI tool that enables me to enter SQL commands to the database. But I’ve had that capability from the very beginning with SQL*Plus and Server Manager (if you’ve been around long enough …

Continue reading »

Apr 05


I was working with an individual on a question in the MOSC forums recently where they asked about the TOP_LEVEL_RPI_CURSOR column of the V$SQL_SHARED_CURSOR view. There is little documentation on what this column is trying to tell the DBA. All the Oracle docs say is that this column contains “(Y|N) Is top level RPI cursor”. So …

Continue reading »

Feb 17

A Tale Of Two Clustering Factors

I was looking at a post on the MOSC forums today about the Clustering Factor (CF) for an index. One thing that people tend to forget when talking about the CF is that while the DBA can do some reorg activity to improve the CF for an index, it will potentially come at the expense …

Continue reading »

Jan 26

Identifying ASH Sequence Contention in RAC

In Chapter 3 of Oracle RAC Performance Tuning, I showed how improper CACHE values for sequences can cause poor performance in Oracle RAC. I also showed how to spot sequence contention when looking at a session’s wait events. Today, I was working with a developer who was creating a new sequence. The developer had a …

Continue reading »

Dec 22

A Trip Through the GIMR

Oracle Grid Infrastructure includes the Cluster Health Monitor (CHM) which regularly captures OS-related performance information. In early versions, CHM use a Berkeley DB for its data store. In Grid Infrastructure, it is now required to use an Oracle database for the data store. This Oracle database is called the Grid Infrastructure Management Repository (GIMR). …

Continue reading »

Dec 08

Fast Split Partitioning

I have a partitioned table for some application logging. A few years ago, I partitioned the table with one partition per month. As we near 2016, its time for me to add partitions for the new year. The partitioned table has, as its last two partitions, the partition for December 2015 and a partition using …

Continue reading »

Nov 16

Lighty Purchasing Adventure

Those that follow me on Twitter and in this blog know that I really like Orachrome’s Lighty for performance tuning. I prefer this over Enterprise Manager. I’ve gotten to the point where I get depressed if Lighty isn’t available to me and I am forced to use EM in its place. Don’t get me wrong, …

Continue reading »

Nov 05

Adaptive Dynamic Stats Kills Performance in RAC

After a recent upgrade to, I’ve been working on a number of performance issues. Many such issues are related to poor SQL and a number of issues I’ve resolved I’ve proven were issues in the old release. This just means it’s always been an issue. But people are taking the opportunity of the …

Continue reading »

Oct 21

Oracle 12c Upgrade Issues

I recently upgraded a critical production system from Oracle to This is also a 3-node Oracle RAC database. I ran into a few issues which needed resolution after the upgrade. Hopefully this helps out others in their upgrades. The first issue was related to a Global Temp Table that is central to our …

Continue reading »

Older posts «