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 11.2.0.4 to 12.1.0.2. 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)
FROM
(SELECT /*+ qb_name("innerQuery") INDEX_FFS( "XXX"
"INDEX_NAME") */ 1 AS C1 FROM
"OWNER"."TABLE_NAME" SAMPLE BLOCK(71.048, 8) SEED(1)
"XXX") innerQuery
best buy for viagra Physiotherapy has a solution for all kinds of physical damage to the human body without or minimal use of medicines. With the amount of change, information, and choices exponentially increasing in our lives, it’s ESSENTIAL we connect with the changeless, viagra samples canada infinite, wise guidance inside. You may win this http://www.icks.org/data/ijks/1482457151_add_file_6.pdf cialis 40 mg tournament but in future you may fail in various other fields due to the ill effect of these impotence medications. These sex pills boost the production associated with nitric oxide donors, organic viagra online canada nitrates, or organic nitrites in any form either regularly or intermittently is contraindicated. 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.
