https://uhesse.files.wordpress.com/2015/10/helps.png?w=600&h=558 600w, https://uh
https://uhesse.files.wordpress.com/2016/12/fight_contention_2.png?w=576&... 576w, https://uhesse.files.wordpress.com/2016/12/fight_contention_2.png?w=144&... 144w" sizes="(max-width: 288px) 100vw, 288px" />
Large OLTP sites may suffer from Buffer Busy Waits. Hash Partitioning is one way to reduce it on both, Indexes and Tables. My last post demonstrated that for Indexes, now let’s see how it looks like with Tables. Initially there is a normal table that is not yet hash partitioned. If many sessions do insert now simultaneously, the problem shows:
https://uhesse.files.wordpress.com/2016/12/fight_contention.png?w=150&h=132 150w, https://uhesse.files.wordpress.com/2016/12/fight_contention.png 474w" sizes="(max-width: 300px) 100vw, 300px" />
Buffer Busy Waits can be a serious problem for large OLTP systems on both tables and indexes. If e.g. many inserts from multiple sessions occur simultaneously, they may have to compete about the same index leaf blocks like the picture below shows:
With your database in archive log mode, a Data Pump Import may be severely slowed down by the writing of much redo into online logs and the the generation of many archive logs. A 12c New Feature enables you to avoid that slow down by suppressing redo generation for the import only. You can keep the database in archive log mode the whole time. Let’s see that in action!
First without the new feature:
One of the questions we see being asked reasonably frequently is how to allow read only access to someone who wants to view database performance issues, using tools such as Real Time ADDM, ASH Analytics and so on. This is generally asked for someone like an application developer, who can use this information to identify performance issues in Production and then fix the root cause in their Development environments.
Well, that’s a reasonable question to ask, and indeed if you search the internet you can find different ways of doing something like this. For EM12c, one of the best write-ups I’ve seen done on this was of course by one of my colleagues, Courtney Llamas. That write-up is available here.
Suppose you want to find out which type of index is best for performance with your workload. Why not set up a competition and let the optimizer decide? The playground:
Something I presented recently during an Oracle Database 12c Performance Management and Tuning class. Hints are a double-edged sword; they may do more harm than good. What if hinted SQL comes from an application that you as the DBA in charge can’t modify? You can tell the Optimizer to ignore that nasty hint.
One method is to use alter session set “_optimizer_ignore_hints”=true; This will make the optimizer ignore all hints during that session – also the useful ones, so maybe that is not desirable. The method I show here works on the statement level. The playground:
After reading my blog entry about a performance issue due to excessive HCC decompression ( Accessing HCC compressed objects using index access path, a reader asked me about the CPU profiling method I mentioned in that blog entry. I started responding to that comment, and realized that the response was too big for a comment. So, in this blog entry, I will cover basics of the CPU profiling in Linux. Other platform provides similar utilities, for example, Solaris provides an utility dtrace.
I came across another strange SQL performance issue: Problem was that a SQL statement was running for about 3+ hours in an User Acceptance (UA) database, compared to 1 hour in a development database. I ruled out usual culprits such as statistics, degree of parallelism etc. Reviewing the SQL Monitor output posted below, you can see that the SQL statement has already done 6 Billion buffer gets and steps 21 through 27 were executed 3 Billion times so far.
Statistics and execution plan
I was testing an application performance in 12c, and one job was constantly running slower than 11g. This post is to detail the steps. I hope the steps would be useful if you encounter similar issue.
In an one hour period, over 90% of the DB time spent on waiting for library cache lock waits. Upon investigation, one statement was suffering from excessive waits for ‘library cache lock’ event. We recreated the problem and investigated it further to understand the issue.
Following is the output of wait_details_rac.sql script (that I will upload here) and there are many PX query servers are waiting for ‘library cache lock’ wait event.