Nov 16

Dynamic Sampling Killing Me in 12c

Eleven days ago, I blogged about how Adaptive Dynamic Stats was consuming resources in my production RAC databases.

After putting out that fire, I was on to examine some poorly performing queries being reported by our QA people in Test and other non-production databases. I did as any good Oracle DBA would do. I gathered a stored procedure call that duplicated the problem. In my session, I started a SQL trace and ran the stored procedure. It took 50 seconds to complete, when it used to take 5 seconds or less before I upgraded from to This stored procedure contains a number of SQL statements and a SQL trace seemed like a logical place to start. I needed to know which SQL statement in the procedure was causing the problems.

I ran the SQL trace file through TKPROF and was surprised by the results. The SQL statements in the stored procedure seemed to be executing pretty quickly. But I was greeted by many statements similar to the following:

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
 optimizer_features_enable(default) no_parallel */ SUM(C1)
 (SELECT /*+ qb_name("innerQuery") INDEX_FFS( "XXX"
 "XXX") innerQuery

This is Dynamic Sampling at work. In looking at all of the Dynamic Sampling statements being executed in my trace file, I was able to determine that these accounted for 45 seconds of the overall runtime! Yikes!

Dynamic Sampling is supposed to help me out. The time spent obtaining some sample statistics is supposed to be much smaller than the amount of time saved by executing the SQL statement with better stats. If it doesn’t, your SQL statement performance can suffer, as was my case.

I noted one thing that I thought was interesting was that these Dynamic Sampling queries were executed once for each table and once for each of its indexes. One of the tables involved in my query has 7 indexes on it, so for that one table, I had 8 Dynamic Sampling queries!

In my blog post 11 days ago, I had set the optimizer_dynamic_sampling parameter to 0, which stops these queries from being executed. I had not  yet put that change into our Test environment so I needed to do so. As soon as I did, query performance returned to normal. The default value of this parameter for my database is 2. Your default value can different depending on the value of the optimizer_features_enable setting. According to this blog post, a value of 2 means that dynamic sampling will kick in when at least one of the tables has no statistics. But to be honest, dynamic sampling isn’t giving me any benefits and only causes me harm. So I’ll just leave it off in its entirety for now.



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, EM is a fine product and I still recommend it and EM does so much more than performance tuning. But for watching and troubleshooting database performance problems, Lighty is one of the top tools in my list!

It was quite an adventure getting the product licensed at my company. I figured I’d describe our adventure. While the adventure was ongoing, I had to resort to using Lighty after my trial expired, which was a bit painful at times. Thankfully this is all over, but here’s a recap of how the trip unfolded.

The first twist to purchasing Lighty, was that the quote came to us in Euros. SETRA Conseil, who bought Orachrome, is a French company. So its natural they would quote in Euros. Their web site lists prices in both Euros and US Dollars. I needed the quote in the latter and it took an extra day to obtain the quote. We’ll dock Lighty one point since they couldn’t read my mind and know exactly what I wanted.   Lighty -1.

Next, we received an invoice and Lighty wanted us to perform a wire transfer, after which they would send us the license keys. Apparently, my company cannot perform wire transfers here in the US where I’m based. So they sent it to our parent company, which sent the invoice to an accounting department on the other side of the planet. I’m not sure what happened, but our accounting department was unable to successful perform a wire transfer.   My Company -1.

The fine folks at Lighty did not have a direct method to accept a credit card payment.   Lighty -1.   (now down two points)

However, they were more than willing to work with us, so they had a way for us to pay via credit card through Pay Pal. Light +1  (now down only 1 point. We’re both tied at -1).

My company had problems paying via credit card on Pay Pal. My Company -1 (now down two points).

The fine folks at Lighty offered us an extra license for our troubles. Lighty +1  (they are now holding steady at zero…neither up nor down…).

My company was finally able to sort out the issues with Pay Pal. Within an hour of my purchasing agent letting me know the transaction was complete, Lighty had shipped me the license. Lighty +1 (they are now on the positive side).

So it was a bit of an adventure. I’m not in accounting so I’m not sure why there was so much hassle. Life could have been made easier if Lighty could have accepted a credit card payment directly, but this ended up not being a workable situation.

So in the final score, I have Lighty +1 and My Company -2. :)

But if you factor in the great product I am now fully licensed for, I think its Lighty +999 My Company -2.


Nov 11

Addnode resolv.conf Failures

Today I was trying to add a new node to an existing Oracle RAC cluster. Grid Infrastructure is version running on Oracle Linux 5.11.

I ran through the usual Cluster Verification Utility (CVU) checks and all checks passed. To add the node, I issued the following command from the $GRID_HOME/addnode directory.

./ -silent "CLUSTER_NEW_NODES={newnode}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={newnode-vip}"

The operation would fail. The log file showed the following error:

INFO: *********************************************
INFO: Task resolv.conf Integrity: This task checks consistency of file /etc/resolv.conf file across nodes

I know for certain that there are no issues with resolv.conf. It has been working just fine on the two existing nodes. Everything checks out. This hasn’t been my first issue with resolv.conf over the years. Normally, I know that the OUI gets the check wrong and I ignore the condition and continue.

I did find some help via Google to set the following environment variable before running


This sounded promising. However, it made no difference and I received the same error. Since is calling the OUI in silent mode, I figured I would try the directive to ignore prerequisites.

./ -silent "CLUSTER_NEW_NODES={drorp05}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={drorp05-vip}" -ignoreSysPrereqs

I still received the same error. So how do I get to get past the failed check? The solution was too obvious to be seen initially.

The script just calls the OUI. So the answer is to run just the following:


Run the script with no parameters. This fires up the GUI version of the OUI. The next screen asks for the new host name and its VIP, which were previously parameters to the script. The checks were run and of course, a failure was seen. But this time, I could check the box to Ignore the failures and continue with the installation.

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 upgrade to get me to fix things that have been broken for quite a while.

While looking at performance issues, I’ve come across two SQL statements that are becoming pigs in our system.  Here is a screenshot of the two SQL statements as seen in Lighty:






We can see that the first SQL statement (SQL ID 4b4wp0a8dvkf0) is consuming CPU and waiting on reads from the Control File. The second SQL statement (SQL ID frjd8zfy2jfdq) is using lots of CPU and has a number of other wait events as well. Here is the SQL text of these statements.

SQL ID: frjd8zfy2jfdq

    ,elapsed_time / px_servers elapsed_time
    ,cpu_time / px_servers cpu_time
    ,buffer_gets / executions buffer_gets
          SUM (executions) AS executions
          ,SUM (
              WHEN px_servers_executions > 0
              THEN px_servers_executions
              ELSE executions
          ) AS px_servers
          ,SUM (end_of_fetch_count) AS end_of_fetch_count
          ,SUM (elapsed_time) AS elapsed_time
          ,SUM (cpu_time) AS cpu_time
          ,SUM (buffer_gets) AS buffer_gets
          executions > 0
          AND sql_id = : 1
          AND parsing_schema_name = : 2


SQL ID: 4b4wp0a8dvkf0

    ,elapsed_time / px_servers elapsed_time
    ,cpu_time / px_servers cpu_time
    ,buffer_gets / executions buffer_gets
          SUM (executions_delta) AS EXECUTIONS
          ,SUM (
              WHEN px_servers_execs_delta > 0
              THEN px_servers_execs_delta
              ELSE executions_delta
          ) AS px_servers
          ,SUM (end_of_fetch_count_delta) AS end_of_fetch_count
          ,SUM (elapsed_time_delta) AS ELAPSED_TIME
          ,SUM (cpu_time_delta) AS CPU_TIME
          ,SUM (buffer_gets_delta) AS BUFFER_GETS
          DBA_HIST_SQLSTAT s
          ,V$DATABASE d
          ,DBA_HIST_SNAPSHOT sn
          s.dbid = d.dbid
          AND bitand (
            nvl (
          ) = 0
          AND sn.end_interval_time > (
                systimestamp AT TIME ZONE dbtimezone
          ) - 7
          AND s.sql_id = : 1
          AND s.snap_id = sn.snap_id
          AND s.instance_number = sn.instance_number
          AND s.dbid = sn.dbid
          AND parsing_schema_name = : 2


Both of these are part of the new Adaptive Query Optimization features now in 12c. More specifically, these relate to the Automatic Dynamic Statistics portion of this feature. SQL ID frjd8zfy2jfdq is Oracle obtaining information on SQL statement performance from GV$SQL. SQL ID 4b4wp0a8dvkf0 is Oracle obtaining the same information on SQL statement performance from the Active Session History tables.

Bertand Drouvot discusses this on his blog here:

Additionally, I sat in on a session by Christian Antognini at Oak Table World 2015 where he mentioned these SQL statements. His slides from OTW are pretty much the same as these:

Those links above and the MOS Notes I reference below provided much of the basis of the information I present here.

All of the Adaptive Query Optimization features are supposed to make the DBA’s life better. They are supposed to help the Optimizer make better decisions, even after a SQL statement has started executing. In this specific case, these queries are supposed to help the CBO obtain better statistics, even if the stats are missing. This can help improve SQL performance, but as I found in my case, it is hampering overall system performance.

For more on Adaptive Query Optimization, see Note 2031605.1. This will lead you to other notes, but in particular to this discussion, Note 2002108.1 Automatic Dynamic Statistics.

Making matters worse is that my production system seeing this behavior is Oracle RAC. When SQL ID frjd8zfy2jfdq is performed on Oracle RAC systems, parallel query is used which is obvious from my screen shot by the enq: PS – contention and “PX%” wait events.

We can turn of dynamic sampling as follows:

alter system set optimizer_dynamic_sampling=0 scope=both;

The default value of this parameter is 2.

For me, these queries are consuming resources and impacting overall database performance. Yet these features are designed to improve performance. There is always a risk that if I turn off the feature to help performance in one area, it will hurt performance in another area. But since optimizer_dynamic_sampling<>11 for me, I’m not using that feature to its fullest so I’m not obtaining all of the benefit I could be. Also, our code is not relying on dynamic sampling to occur. So it’s safe for me to turn this off.

After changing the parameter, I could see an immediate change as shown below.





The red line indicates the time I made the change. It is clear that the ASH version of this query is no longer executing. The V$SQL version is still executing but no longer seeing parallel query wait events. It’s mostly just consuming CPU now. I consider this progress, but not a full resolution.

As a side note, I could have turned off all Adaptive Query Optimization features with the following:

alter system set optimizer_adaptive_features=false scope=both;

But I do know that I have queries “enjoying” Adaptive Join Optimization and I don’t want to turn it all off, just dynamic sampling.

So now what to do with SQL ID frjd8zfy2jfdq? Let’s see if we can resolve the remaining issue. From one of the MOS Notes I linked above, I know that we can set this hidden parameter:

alter system set "_optimizer_dsdir_usage_control"=0 scope=both;

The default value for this hidden parameter was 126 in my system. I found the default setting with the following:

select a.ksppinm name, b.ksppstvl value, b.ksppstdf deflt,
  sys.x$ksppi a,
  sys.x$ksppcv b
where  a.indx = b.indx
  and  a.ksppinm like '\_%' escape '\'
  and  a.ksppinm like '%dsdir%'
order by name;


This value is important in case I want to set it back without having to take the parameter out of the SPFILE, which would require downtime.

I can now proceed with changing this hidden parameter and setting it to zero. Here is how that SQL statement looks in Lighty after the change:





Looks to be “mission accomplished” in stopping those two SQL statements from executing.

Those running on Oracle RAC may want to verify that these two SQL statements are not causing performance problems of their own.

This seems to be one of those cases where a feature that is supposed to help performance actually does the opposite.


Oct 28

My OOW15 Trip Is Complete

I’m leaving Open World 2015 a day early. I managed to pack in an awful lot of activity into the three days I was here and I met up with some very nice people. I expect that my OOW experience was similar to many others. There was so much to do and not enough time. But I’m happy to be going home as well.

I spent most of my Monday attending sessions and learning about the exciting new changes coming out in Oracle 12cR2 (due to be released in 2016).There was a lot of talk about cloud, Cloud and more CLOUD, which is no surprise. “On-Prem” is now a new word I learned this week. I blogged about 12.2’s new Sharding capabilities, which I think will be a big deal down the road. The ability to add sharding to the world’s #1 database will only make the engine more competitive. There are a number of new RAC features coming as well. Some of these made my head spin but I’m sure I’ll make sense of it sooner or later. One of the sessions I attended today even discussed some new Data Guard features.

I never once visited any vendor booth on the exhibition floor, which was a first for me. I never saw any vendors that I really wanted to talk to and part of it is because I was so busy doing other things. In the Exhibition Hall, I did spend some time on the Demo Grounds. I like these because, as I tweeted out, this gives me a chance to ask pointed questions out a specific feature. Most people like to use the Demo Grounds to get a little demo of that feature. But typically for me, I’ve already seen the feature or been exposed to it, but I like to learn just a bit more about that feature and the expert is right there just waiting for me to ask.

I met up with a number of people whom I’ve never seen before in person but had virtual conversations with in some social media environment in the past. Its always good to put a face with the name. And there were some people I wanted to meet up with but our collective schedules didn’t quite allow it. I was invited to a meetup event where I was able to interact with people that I know from the OTN or MOSC communities. All the people there I knew from these forums over the years but had never had a face to face discussion with so it was nice to talk them in person. The collection of people in the room spanned a number of countries all over the world and their expertise covered many different areas of the forums, so we had quite the diverse group.

I wanted to get to more of the Oak Table World events, but I only got to one: Christian Antognini’s discussion about Adaptive Dynamic Sampling. Next time I’ll try to get a few more of these as I’m sure all of them are excellent.

And then there are all of the things that I did not do. Oh well..maybe next time! I’m not sure if I’ll make it back for OOW16, but if not, then OOW17 for sure.

Oct 28

Cursor Obsoletion Dump

After our recent upgrades to Oracle, we noticed a few non-production databases that were filling up the DIAGNOSTIC_DEST. There were huge trace files in there which contained this type of information:

----- Cursor Obsoletion Dump sql_id=6aca4drrt76x3 -----
Parent cursor obsoleted 1 time(s).

It appears this is a known bug in Oracle turned on some diagnostic info and forgot to turn it off. But you can turn it off in your environments with the following:


alter system set "_kks_obsolete_dump_threshold" = 0;

Oct 26

Oracle 12.2 Sharding

I’ve been working with MongoDB a bit and one of the things I like about it is how sharding helps the platform achieve high performance. MongoDB is not the only database platform that uses sharding, just one of the more mainstream ones. While I’ve been working with MongoDB and admiring sharding and the ability to handle JSON documents, I’ve also been troubled by a few other things. This is not meant as a total trashing of MongoDB as many database platforms, especially the new players on the block, suffer from the same things. This isn’t an exhaustive list, but here are some of the things I wish I could find in MongoDB.

  • ACID compliance – The idea of eventually consistent never sits well with the DBA in me that wants to protect critical corporate data.
  • Security – I come from Oracle, the most secure database around. No other DB platform has the level of security features found in Oracle.
  • Tools – I’m used to Oracle Enterprise Manager for monitoring/managing my Oracle environment. I rely heavily on all that the Diagnostics Pack provides. My new favorite tool is Lighty for Oracle. Being a performance tuning specialist, I appreciate how well instrumented the Oracle kernel is and how many things I can leverage to resolve database performance issues. No other database comes close.
  • Backup and Recovery – RMAN automates everything and takes all the hard work out of the picture when backing up and recovering the database.


Well if I’m looking for web-scale, Oracle is now going to provide it with 12cR2. Oracle 12c brought native JSON support, which is great. Oracle 12.2 will now let me create a sharded table and enjoy that web-scale-ability that MongoDB provides. My applications can connect to an Oracle database and enjoy sharded and non-sharded tables with the same database connection. I don’t lose any of the other stuff I’ve grown so fond of.

I heard a few other people walking around talking about 12.2’s new sharding ability. To me, this is a major point and could probably be at the top of the list of highlights from OOW15.

Oct 26

12.2 RAC/GI New Features

I attended a session today at Oracle Open World 2015 by Markus Michalewicz which highlighted some new features we can look forward to for Oracle Grid Infrastructure 12.2, currently in beta.  There were lots of new features, but a few stood out as major changes to the architecture.

Oracle 12.1 introduced Flex Clusters about two years ago. With Flex Clusters, you can have Hub Nodes and Leaf Nodes, the primary difference being that Leaf Nodes do not need access to the shared storage of the cluster. When this was announced, many people assumed you could run an Oracle RAC instance on a Leaf Node, but this was not the case. Not until Oracle 12.2 anyway. With 12.2, you can now run an Oracle RAC instance on a Leaf Node. There is one catch though. Since the Leaf Node does not have access to shared storage, there is no access to Online Redo Logs which means the instances on the Leaf Nodes are read-only to the data. This is a great way to offload reporting activities similar to the way people have been leveraging Active Data Guard. I haven’t seen this in action, but my initial thoughts would be that the DBA needs to weigh the performance carefully. Surely offloading reporting capabilities has its merits but it comes at the cost of additional latency when an instance on a Leaf Node needs to contact an instance on a Hub Node and transfer the blocks via Cache Fusion. After all, the Leaf Node has no direct access to disk so it must need to get the data block via a Hub Node.

The other big change I wanted to discuss is in relation to the GIMR database. The Grid Infrastructure Management Repository is a database to help collect performance metrics, introduced in Oracle The version made the GIMR mandatory. This database runs on only one node of the Oracle RAC cluster. In GI 12.2, we can now have a centralized GIMR on one cluster that other GI clusters can leverage. There are a whole host of ramifications for those that get to enjoy many RAC clusters in their enterprise.

While watching the presentation, it didn’t seem like these were major announcements. It almost sounded low-key. But I think that as we get to play with the 12.2 version, we’ll get a better handle of how much Oracle GI/RAC features just got a lot more expansive, which some might read as more complicated.

Oct 26

Oracle 12cR2 now in Beta

Today at Oracle Open World 2015, I learned that Oracle12cR2 is now in beta. I attended a session from Oracle VP Andy Mendelsohn where he highlighted a few of the new features found in 12.2. This is not an exhaustive list, but contains some highlights from that session.


  • Sharding for web-scale applications
  • Table and column names can be longer than 30 bytes, now up to 128 bytes.
  • AWR reports on Active Data Guard standby databases.
  • In-Memory operations can run on Active Data Guard standby databases.
  • Application Container
  • PDB Hot Clones
  • PDB Refresh is just a click away.
  • PDB Relocate with zero downtime


Here is the official press release.

Oct 23

SQL Developer 4.1.2

SQL Developer 4.1.2 was just released today. The new version can be downloaded from the usual place on OTN. I had no problems installing it on my MacBook Pro.



Older posts «