What? Fixed? Problem? Huh?
While going through and checking and updating my Oracle Performance Firefighting
course for Oracle Database 12c, I once again compared session level CPU consumption from v$session and v$sess_time_model. In the past v$sesstat server process CPU consumption could
be significantly less than what is shown in v$sess_time_model. Also, (more...)
I Know That's Not A Fair Question
Is 12c faster than 11g? Yeah, I know that's not a fair question. But I do want to know if 12c can process 1000 buffer gets
faster than 11g... don't you?
I want to know how much CPU time is consumed when Oracle (more...)
Is it OK to be an Oracle Database heretic?
Oracle Database performance tuning and analysis has come a long way in the last 20 years. First there was the “just add more resources” approach and tuning the blatantly poor SQL. Then there was ratio analysis, followed by wait event (more...)
Of course they do. Right?
Oracle Database performance tuning and analysis has come a long way in the last 20 years. First there was the “just add more resources” approach and tuning the blatantly poor SQL. Then there was ratio analysis, followed by wait event analysis, time based analysis, and (more...)
You're joking. Right?
Oracle Database performance tuning and analysis has come a long way in the last 20 years. First there was the “just add more resources” approach and tuning the blatantly poor SQL. Then there was ratio analysis, followed by wait event analysis, time based analysis, and unit of (more...)
You're joking. Right?
Oracle performance analysis has come a long way in the last 20 years. First there was the “just add more resources” approach and tuning the blatantly poor SQL. Then there was ratio analysis, followed by wait event analysis, time based analysis, and unit of work time based (more...)
Q: Are Oracle SQL CPU times reliable?
During performance analysis, it's usually important to collect the CPU consumption for specific SQL statements for opportunity and comparison analysis. There are multiple SQL CPU consumption data sources. I'm the type of person who asks, "Are they to be trusted? Is one method (more...)
How Many CPU Cores Do I Really Have?
The view operating system statistic view v$osstat is can be misleading with regards to CPU cores. Not that the information is incorrect, it's well... let's say troubling. If I ask ten people to email a sample AWR report, I'm likely to see CPU core-like statistics such as CPU_SOCKETS, NUM_CPUS, VCPU, LCPU, CPU_THREADS, and probably a variety of other names. Wow… what a mess!
But I'd still like to know because it's important for my work. For two reasons:
First, it helps me to understand how high the CPU utilization can go before (more...)
Another (Simpler) Way to Calculate CPU Utilization
Back in April of 2011, I blogged about how to calculate
the operating system CPU utilization from data in a Statspack or AWR report. All that is necessary is the report snapshot interval and a couple of columns from the Operating System Statistics view, v$osstat.
Utilization Made Easy
It's pretty simple actually. Utilization is simply requirements divided by capacity. If you have one cup that contains 1/2 cup of water, the cup is 50% full/busy/utilization/etc. Here is the basic utilization formula:
U = R / C
U is utilization
R is requirements (more...)
Our Experience Tells Us So...
We know from hard-fought experience that the more work we push through a system the more processing time involved. That's not rocket science... but to mathematically represent the relationship between work and time is rocket science.
Decades ago Operations Research queuing theory researchers
quantitatively established the relationship between work and time. They put into math what we all have experienced. In our DBA world this means that as the system activity increases, then so does CPU consumption and eventually Oracle non-idle wait time.
Find The Big Bar
The classic way to graphically represent all the (more...)
The Back Story
With all the blog postings and forum mishegas
about mutex issues, bind mismatch, and child cursors, you would think it would be a simple thing to create hundreds if not thousands of child cursors. Well... it wasn't.
My original intention for this posting was to gather experimental evidence about the performance impact of having hundreds or thousands of child cursors. While it was easy to create a few child cursors (details below), it was difficult to create hundreds of child cursors. I'm also noticing what many DBAs think what is a "bind mismatch" is really not a (more...)
Over 1100 Times Slower is... Bad!
In my previous posting I experimentally demonstrated hard parsing was not just bad, but really bad. In fact, my non-load test showed the initial parse (hard parse) was 180 times longer than the second parse (soft parse)! Also, once session cache cursors kicked in after the third parse, parse time dropped over 1100 times when compared to the initial hard parse! While that's useful (and interesting), rarely is parsing an issue in a no-load situation, that is, single session situation. That brings us to this posting...
This posting is focused on parse time when (more...)
This is very unusual indeed. This post is not about Oracle, but about one of the things that I do besides Oracle performance analysis.
Many people ask me what I do besides Oracle and I reply with a long list of things like coffee roasting, beekeeping, spiritually focused youth work, and the list goes on (just ask my wife).
One of the things I love is riding my motorcycle and this is what this post is about. I just returned from a 14 day trip from where I live (Portland, OR USA area) riding down south about 1000 miles to (more...)
Could Someone Please Quantify, "Bad"...
It seems like everywhere I turn someone says, "You don't want to hard parse. It's bad." But why? And how much "bad"? And what can I do about it? That's what I want to turn my attention to in the next few postings. This will lead us down the path to some very interesting Oracle performance topics; including issues relating to hundreds or thousands of child cursors, mutexes, etc. So it should be exciting!
Visualizing the Library Cache (LC)
Before we look at the experimental evidence (you know there's got to be some in (more...)
You're row processing on the Java client? Surprise!
Two weeks ago I joined a good friend on-site at one of his clients. Upon arriving a discouraged developer wanted to know why his report was currently running so slow, when previously it had run just fine. For sure it was the database, right?
Long story short, the report sucked data from a couple of database tables into a Java client front end placing this data into memory structures, and then the Java code did all sorts of (I'm sure) amazing sequential row/record processing. This worked great with smaller quantities of data, (more...)
Creating sensible think times is kind of tricky
My job requires I spend a tremendous amount of time doing performance analysis research. Over the years I have developed a list of things I do to help ensure my research is up to the level I need for a specific experiment. One of the more interesting things on my list is creating a more realistic workload by using sleep times (i.e., think times) that are not constant. When sleep times are the same the system behaves unrealistically constant. On real systems, even a CPU bottlenecked system exhibits an ebb (more...)
Eventually you'll want to really know...
When performance data is periodically collected (i.e., snapshotted) and stored, the question undoubtedly will cross our minds, "Is the stored value correct at the beginning or the ending snapshot time?" In relation to Oracle's AWR system and specifically the dba_hist_sysstat table, this is what this posting is all about.
If you have ever written a data collection program or pulled data from either Statspack or AWR, you've had to figure this out.... or guessed and know you were kinda close.
Let me demonstrate this problem. Let's say this is the data:
Snap # (more...)
When Performance Statistics Are Useful
Statistics are meaningful when we understand them and potentially dangerous when we don't. In the Oracle performance analysis arena, the instance statistics views (v$[ses,sys]stat
) are used quite a bit. While there are many sources describing what
a statistic means, what remains elusive is when
a statistic is updated. For example, is the execute count updated when a SQL statements starts, ends, or something else?
This posting will provide the answer to when some of the key instance statistics are updated based on experimental evidence. Plus you can perform the tests yourself. So when does (more...)
Suppose there is a room of 75 Oracle DBAs. I randomly pick 25 of them and ask them to randomly divide up into 5 groups of 5 each. Then I ask each group to compute the average age for their group and give me the results. The averages are 38.2, 34.7, 41.2, 43.9, and 42.1. I do a little math but don't say anything. I then look to the remaining 50 DBAs, ask them to break up into 10 groups of 5 each, compute their group's average age, write their result on a (more...)