When you first begin your journey into Oracle performance tuning, you quickly discover that you’re not just working with databases, you’re deciphering a complex system of rules, statistics, and estimations. I was reminded by Connor McDonald in a post he made on LinkedIn about one of the most influential turning points in my career as a database professional came when I encountered Wolfgang Breitling’s 2003 paper – A Look Under the Hood of the CBO: the 10053 Event. At a time when I was focused on tuning queries for speed, Wolfgang’s work redefined how I thought about cost, and more…
-
-
After publishing my last post on Oracle Wait Classes, I received a great question on LinkedIn: “How do you identify the SQL statement or the cause of high CPU when the top wait event is ‘resmgr:cpu quantum’?” It’s a common (and frustrating) performance issue in Oracle. So today, we’ll walk through how to identify and troubleshoot it. What is resmgr:cpu quantum? Let’s break it down: RESMGR = Oracle Resource Manager CPU Quantum = The time slice a session is allowed to consume CPU This wait event indicates that a session is throttled by Oracle Resource Manager because it’s trying to…
-
When describing Oracle features to folks that are new to the platform or coming from other database platforms, I found it’s best to keep it simple, but take on the important aspects of the technology. I’m going to take on a few more posts on Oracle internals from the perspective of the individual new to Oracle. To start, I’ve been having some long discussions, as well as documenting how Oracle memory works and how we monitor it when you don’t have the diagnostic and tuning pack at your disposal. With the exclusion of these management packs, you have to be…
-
Disclaimer: I’m not a big fan of benchmark data. I find it doesn’t provide us as much value in the real world as we’d like to think it does. As Cary Milsap says, “You can’t hardware your way out of a software problem” and I find that many folks think that if they just get the fastest hardware, their software problems will go away and this just isn’t true. Sooner or later, it’s going to catch up with you- and it rarely tells you what your real database workload needs to run most efficiently or what might be running in…
-
Its almost standard fare to be using Azure Analysis Services with our customer deployments these days. As our customers evolve the value of their data. SSIS integration runtimes were pivotal to this and now that there is Azure Analysis Services, it’s even easier to get started with just a few clicks in the portal interface, (or for me, a simple step in a script… :)) and migrate runtimes to the cloud. One of the biggest challenges for any cloud migration project is performance issues, not due to cloud, but due to technologists attempting to perform tasks as they have on-premises…
-
Power BI, like many Microsoft products, is multi-threaded. This can be seen from the logs and even the Task Manager. I know, I know…you’ve probably heard this part all before… The importance of this information, is that the logs will display Process IDs, (PID) that are separate from the main Power BI Desktop executable, including the secondary processes.. Moving from the Power BI logs that reside in the Performance folder, (see Part I here) we can view and connect the PIDs and TID, (Transaction IDs) to information from the Task Manager and the data displayed: Note that I’ve highlighted the…
-
So we went over locations and the basics of logging and tracing in Power BI. I now want to know how to make more sense from the data. In Oracle, we use a utility called TKProf, (along with others and a number of third party tools) to make sense of what comes from the logs. SQL Server has Log Analytics and the profiler, but what can I do with Power BI? First, let’s discuss what happens when we have actual activity. In my first post, the system was pretty static. This time I chose to open up a file with…
-
Knowing where log files are and how to turn on debugging is an essential part of any technical job and this goes for Power BI, too. Remember, as I learn, so does everyone else….Come on, pretty please? Power BI Desktop Log files and traces can be accessed one of two ways- Via the Power BI Application Via File Explorer In the Power BI application, go to File –> Options and Settings –> Options –> Diagnostics. Crash and dump files are automatically stored with an option to disable them from this screen, but unsure why you’d ever want to do this. …
-
I can be an extremely impatient person about anything I think should be faster. I’m diving in deep with Power BI and for most of the early on lessons, the data sources used are Excel and…ahem…Microsoft Access. I don’t know a DBA alive that enjoys working with Access. Its great for the common user to have a database application, but we can barely use the words “Access” and “Database” in the same sentence. In my heart, I will always be a performance person and working with Power BI desktop with Microsoft Access is enough to make you want to throw…
-
Followup: The tweets from the datachat are available at http://www.confio.com/logicalread/oracle-db-performance-80-percent-hardware-dc01/ in chronological order. Confio software is hosting a live discussion on twitter tomorrow Tuesday April 15 at 12pm PST on the subject of Oracle performance. I’ll be online answering performance questions and have invited many other friends to participate. Some friends who’ve said they’ll be there are Arup Nanda Dominic Delmolino Marcin Przepiorowski Toon Koppelaars Karl Arao Participation and tracking of the discussion can accomplished by either posting with and following along with the #datachat hashtag. Get on TweetDeck or your favorite Twitter tool, search #datachat, add a column and…
-
Introduction Unfortunately, what Oracle calls “Network Waits” most often have little to do with Network but and almost exclusively to do with the time it takes to pack messages for the network before they are sent. Client = you, the tool, sqlplus, application Not the client, the other side = the shadow process is communicating to the client Of the three waits, only “more data” is possibly related to network issues and that’s not even clear, the other two are simply the time it takes to pack a message before sending it. SQL*Net message to client – time to pack…
-
Oracle 10 and 11 Buffer Busy Waits usually happen on Oracle 10 and 11 mainly because of insert contention into tables or Indexes. There are a few other rare cases of contention on old style RBS segments, file headers blocks and freelists. Before Oracle 10 and 11 there was one other major reason which was readers waiting for readers, ie one user does a phyiscal IO of a block into memory and a second user want to read that block. The second user waits until the IO is finished by the first user. Starting in 10g this wait has been given…
-
Here are the tuning metrics tables (SQL stats are not in “metric” tables per say) (*DBA_HIST_…_HISTORY views are sort of confusing. AFAI remember they were storing alert history, but apparently they are used for adaptive thresholds – an area for future investigation) I’ve noticed a number of people posting queries using DBA_HIST_SYSSTAT instead of DBA_HIST_SYSMETRIC_SUMMARY which leads me to believe that there is some confusion or lack of information on the metric tables. Oracle 10g introduced metric tables which compute deltas and rates of statistics thus hugely simplifying the ability to answer simple questions like “what is the I/O rate on my…
-
Here is a quick table comparison of different types of metrics views The first line of the table is the classic wait event and statistic views. The following lines are the metric views. The metric views were introduced in Oracle 10g. Why Metrics are good Metric views compute deltas and rates which hugely simplifying the ability to answer simple questions like “what is the I/O rate on my databases right now?” This question, before 10g, was surprisingly tedious to answer. To answer the question one would have to query v$sysstat for example: Select value from v$sysstat where name=’physical reads’; but…
-
If one is seeing waits for enq: TX – row lock contention then there could be a lot of reasons. One distinguishing factor is the lock mode. If the lock mode is exclusive (mode 6) then it’s most likely a classic row lock where two sessions are trying to modify the same row. On the other hand if the lock mode is share (mode 4) it’s typically going to be Primary/Unique Key: inserting a unique key when someone else has already inserted that key but not committed Foreign Key: Inserting a foreign when then parent value has been inserted but not…
-
One thing that I have found sorely missing in the performance pages of Enterprise Manager is latency values for various types of I/O. The performance page or top activity may show high I/O waits but it won’t indicated if the latency of I/O is unusually high or not. Thus I put together a shell script that shows latency for the main I/O waits db file sequential read db file scattered read log file parallel write direct path reads direct path reads temp Of course it would be nice to add a few others like direct path writes, direct path writes…
-
Anyone who has been around Oracle performance over the years knows the grief that hard parsing SQL queries can cause on highly concurrent applications. The number one reason for hard parsing has been applications that don’t use bind variables. Without bind variables queries that would otherwise be shared get recompiled because their text is different and Oracle treats them as different queries. Oracle addressed this issue with a parameter called cursor_sharing. The parameter cursor_sharing has three values exact – the default similar – replace literals with bind variables, if a histogram keep literal in place force – replace literals with…
-
When running the same query multiple times, several questions come to mind: Does the query always execute in the same amount of time? If some executions are slower, what is the slowest execution time? When did the slowest exectution happen? What more can I find out about the slowest exectution? All of this can be answered from data in Active Session History or ASH. The following query finds the maximum, minimum and average execution times in seconds as well as the time of the slowest execution which is given by start time and end time of the slowest exectuiton:…