Category: Tuning

RAC Sequence Contention

I recently ran into a case where selecting the next value from a sequence was causing contention issues in Oracle RAC.  See this screen shot from Lighty (click on the image to see a larger picture) The wait events will look the same if viewed in Enterprise Manager’s performance screens, which does require one to …

Continue reading

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

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

V$SQL_SHARED_CURSOR TOP_LEVEL_RPI_CURSOR

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

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

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

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 12.1.0.2, 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

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

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

Adaptive Dynamic Stats Kills Performance in 12.1.0.2 RAC

After a recent upgrade to 12.1.0.2, 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 11.2.0.4 release. This just means it’s always been an issue. But people are taking the opportunity of the …

Continue reading