When we talk about optimization in Oracle, many DBAs immediately jump into metrics like CPU utilization, I/O throughput, or specific SQL queries. But there’s a critical layer of understanding that often gets overlooked and that’s Oracle Wait Classes. These categories are essential to making sense of what’s really going on inside your database, and they’re often the starting point for diagnosing and optimizing performance. What Are Oracle Wait Classes? In Oracle, wait events represent specifics the database is waiting on, such as a latch, a lock, a disk read, or a response from the network or application. But looking at…
-
-
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…
-
Load testing as part of a cloud migration project is expected and should be built into the requirements. The goal is to set yourself up for success. Log Latency Recently I had a Cloud Solution Architect, (CSA) escalate an Oracle performance problem after migrating from on-prem to the cloud. The information provided from the customer stated they were experiencing log sync latency in Oracle and that they hadn’t experienced this previously on-prem. They said there weren’t really any changes, just a simple lift and shift to Azure, so they were at a loss as to why the latency was being…
-
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…
-
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…
-
January is winding down and RMOUG Training Days 2015 is just around the corner, taking up much of my after work hours. With that, we are going to discuss a great performance console in the EM12c cloud control- Cluster Cache Coherency. Cluster Cache Coherency Optimization for an Oracle Real Application Cluster, (RAC) can be a daunting tasks for those that aren’t familiar with some of the most common issues to look for. Although many aspects of a single instance must always be examined, knowing what is unique to RAC is important. Be aware that Enterprise Manager is completely RAC Aware…
-
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…
-
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…
-
Image from Bertrand Drouvot UPDATE: thanks to a discussion in the comments it’s come to my attention that there should be some more clarification on values used and what the mean at the beginning of this post. Ever wonder where CPU wait comes from in EM performance screens as seen above? well the following discussion will give you a SQL query to calculate CPU wait. In the above image we see both CPU and CPU Wait as two colors (light green and dark green) in EM as well as two values from the output of the SQL script (CPU_ORA and…
-
Seeing more and more questions on “where do I start with Oracle if I want to be a DBA?” My perspective is a bit off since I’ve been surrounded by Oracle for over 20 years. I hardly remember what it was like to start with Oracle and starting with Oracle now in 2013 is quite different than starting with Oracle in 1990. Here is my list and everything on this list is excellent. I’m sure I missed a few good ones, but maybe people can add them in the comments. Start with Oracle Docs, they are free and good! http://www.oracle.com/pls/db112/homepage…