Skip navigation.

A Wider View: Experimentations and ruminations on Oracle performance analysis

Syndicate content
Oracle Database performance tuning and analysis experimentation and ruminations.Craig Shallahamer, President/Founder, OraPubhttp://www.blogger.com/profile/04109635337570098781noreply@blogger.comBlogger70125
Updated: 2 hours 55 min ago

Where Does v$osstat Get It's Data? Trustworthy?

Fri, 2014-04-04 10:45
OS Data Without An OS Prompt
Have you ever wanted to get OS data from within Oracle Database 12c/11g/10g and without being at the OS prompt? Have you ever wondered where v$osstat gets it's data?

I have! It's the kind of stuff I think about all the time. In fact, I was so fascinated by what I found, I included the details in my online seminar, Utilization On Steroids. It's that cool.

What Is So Great About v$osstat?
For starters, v$osstat provides an operating system perspective view of OS activity. In contrast, v$sysstat or v$sys_time_model provide performance data about a specific instance. Looking at v$osstat data is like looking at OS data from within Oracle. In other words, seeing OS data without an OS prompt.

But is the data from v$osstat trustworthy? That's exactly what this posting is all about. To figure this out, I'm going to first look at where vmstat gets its data and then look at where v$osstat gets its data. If their data source is the same, then it is highly likely v$osstat is trustworthy. Make sense? Read on...

Where Does vmstat Get It's Data?
It's real simple to determine vmstat's data source. Just OS trace vmstat! On Linux I use the strace command. In the exert below, I did not change any lines. If I removed lines, I replaced them a "...". Here is the output:

[oracle@sixcore local]$ strace vmstat 2 5
execve("/usr/bin/vmstat", ["vmstat", "2", "5"], [/* 39 vars */]) = 0
...
write(1, "procs -----------memory---------"..., 81procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
) = 81
write(1, " r b swpd free buff cach"..., 81 r b swpd free buff cache si so bi bo in cs us sy id wa st
) = 81
open("/proc/meminfo", O_RDONLY) = 3
lseek(3, 0, SEEK_SET) = 0
read(3, "MemTotal: 24729532 kB\nMemF"..., 2047) = 1198
open("/proc/stat", O_RDONLY) = 4
read(4, "cpu 570795858 11695 216600983 8"..., 65535) = 4643
open("/proc/vmstat", O_RDONLY) = 5
lseek(5, 0, SEEK_SET) = 0
read(5, "nr_free_pages 119649\nnr_inactive"..., 2047) = 2047
write(1, " 2 0 146388 478596 317080 22558"..., 84 2 0 146388 478596 317080 22558284 0 0 5 234 1 4 72 27 0 0 0
) = 84
...
nanosleep({2, 0}, 0x7fff0a9313b0) = 0
...
lseek(3, 0, SEEK_SET) = 0
read(3, "MemTotal: 24729532 kB\nMemF"..., 2047) = 1198
lseek(4, 0, SEEK_SET) = 0
read(4, "cpu 570796318 11695 216600996 8"..., 65535) = 4643
lseek(5, 0, SEEK_SET) = 0
read(5, "nr_free_pages 119672\nnr_inactive"..., 2047) = 2047
write(1, " 2 0 146388 478688 317080 22558"..., 84 2 0 146388 478688 317080 22558284 0 0 0 36 3781 2311 38 1 61 0 0
) = 84
...
nanosleep({2, 0},
...
exit_group(0) = ?
[oracle@sixcore local]$

There are two things I want to bring to your attention. First is the nonosleep call. If you do a man page on nanosleep, you can see that vmstat is using the call for the two second sleep between data output.

The second and the most important for this posting, is vmstat repeatedly references data in the /proc filesystem. I write about the /proc filesystem in my Oracle Performance Firefighting book and also explore it in my Utilization On Steroids online seminar. It's amazing. The bottom line is this, vmstat gets performance data from the /proc filesystem. Place that in your stack and read on.

Where Does v$osstat Get Its Data?
The Oracle Database background process mmnl, at least in part, is responsible for collecting data that we see in v$osstat. So I am going to OS trace using strace the mmnl process and see what files it opens and reads!

First I need to get the OS process ID of the mmnl background process for my Oracle Database 12c instance.

[oracle@sixcore local]$ ps -eaf|grep mmnl
oracle 11521 11170 0 08:59 pts/1 00:00:00 grep mmnl
oracle 20109 1 0 Feb24 ? 00:18:47 ora_mmnl_prod35
oracle 60274 1 0 Jan13 ? 00:24:34 ora_mmnl_prod30

Got it! The PID for my Oracle Database 12c instance is 20109. Just like I did with vmstat, except mmnl is already running, I'm going to OS trace it using strace.  In the exert below, I did not change any lines. If I removed lines, I replaced them a "...". Here is the output:

[oracle@sixcore local]$ strace -p 20109
Process 20109 attached - interrupt to quit
...
open("/proc/cpuinfo", O_RDONLY) = 15
fstat(15, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f68548bd000
read(15, "processor\t: 0\nvendor_id\t: Genuin"..., 1024) = 1024
read(15, " size\t: 12288 KB\nphysical id\t: 0"..., 1024) = 1024
read(15, "gs\t\t: fpu vme de pse tsc msr pae"..., 1024) = 1024
read(15, "_tsc arch_perfmon pebs bts rep_g"..., 1024) = 1024
read(15, "2 popcnt aes lahf_lm ida arat ep"..., 1024) = 1024
read(15, "ical, 48 bits virtual\npower mana"..., 1024) = 41
read(15, "", 1024) = 0
open("/proc/mounts", O_RDONLY) = 16
fstat(16, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f68548bc000
read(16, "rootfs / rootfs rw 0 0\nproc /pro"..., 1024) = 886
read(16, "", 1024) = 0
close(16) = 0
munmap(0x7f68548bc000, 4096) = 0
close(15) = 0
munmap(0x7f68548bd000, 4096) = 0
open("/proc/loadavg", O_RDONLY) = 15
fstat(15, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f68548bd000
read(15, "2.11 2.07 1.73 5/302 11525\n", 1024) = 27
close(15) = 0
munmap(0x7f68548bd000, 4096) = 0
open("/proc/stat", O_RDONLY) = 15
fstat(15, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f68548bd000
read(15, "cpu 571012017 11695 216602922 8"..., 1024) = 1024
close(15) = 0
munmap(0x7f68548bd000, 4096) = 0
open("/proc/meminfo", O_RDONLY) = 15
fstat(15, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f68548bd000
read(15, "MemTotal: 24729532 kB\nMemF"..., 1024) = 1024
close(15) = 0
munmap(0x7f68548bd000, 4096) = 0
open("/proc/vmstat", O_RDONLY) = 15
fstat(15, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f68548bd000
read(15, "nr_free_pages 119496\nnr_inactive"..., 1024) = 1024
close(15)
...
semtimedop(5341187, {{24, -1, 0}}, 1, {1, 0}^C
Process 20109 detached
[oracle@sixcore local]$

Just in the few lines above you can see that the mmnl background process opens and reads data from /proc/cpuinfo, /proc/mounts, /proc/loadavg, /proc/stat, /proc/meminfo, and /proc/vmstat. While a bit scary if you are violating your Oracle Corporation licensing agreement, this is truly fascinating!

While my demonstration does not prove this and I have never seen or heard anything to the contrary, it does appear the source of OS data for v$osstat is the /proc filesystem. And certainly, the Oracle Database background process mmnl repeatedly references the /proc filesystem.

Therefore, if the source of OS data is the same for both vmstat and v$osstat, I will trust the data from v$osstat unless there is a really good reason to not trust the data.

What Can I Do With v$osstat?
That's a great question, but not the focus of this posting. But just to give you some ideas, we can easily determine OS CPU utilization solely with $osstat, an AWR report, or a Statspack report. If fact, next week I'm going to give a 30 minute Quick Tip at the 2014 IOUG/Collaborate conference entitled, Calculating OS CPU Utilization From V$ Views, AWR, and Statspack. OK... here is the link where you can download the slide deck for my most recent version of the conference presentation.

Utilization is a fascinating topic. It is far reaching and touches on all things computing and even beyond. And as you might expect, I really get into this in my online seminar entitle, Utilization On Steroids. It's the simplest concepts that are the most profound.

Thanks for reading!

Craig.

If you enjoy my blog, I suspect you'll get a lot out of my online or in-class training. Go to www.orapub.com. I also offer on-site training and consulting services.

P.S. If you want me to respond to a comment or you have a question, please feel free to email me directly at craig@orapub .com. Another option is to send an email to OraPub's general email address, which is currently info@ orapub. com.
https://resources.orapub.com/OraPub_Online_Seminars_About_Oracle_Database_Performance_s/100.htm


















Categories: DBA Blogs

I think you should submit an abstract

Wed, 2014-03-12 11:49
This is not your typical posting from me. But I just received a LinkedIn message and it got me motivated  enough to write this.

A colleague, who has been working with Oracle for over 15 years, sent me a message about the pearls of working for a consulting company that has kept him on the road for about a year now. He's had enough and is looking for something else that will keep him close to home. While this type of life can be painful,  it can also be plentiful.

I challenged him to submit an abstract to IOUG/Collaborate next year. While this year's big IOUG conference only a few weeks away (April 7-11) in Las Vegas, he should start thinking about submitting an abstract now.

He has tons of experience. I wrote, "You have tons of experience that younger folks need to hear about. Perhaps taking just one topic/problem that you found really interesting and had a chance to dig into this past year. People love that kind of stuff."

Most people think that their abstract will not get accepted. I've been involved with Oracle User Groups since I started working with Oracle technology in 1989! And I can tell you from experience, that I always rank a well written abstract with a well thought out outline and a very small yet deep scope very high. In fact, I recruit people for IOUG in my track that I know will do this.

So I wrote him, "There are a lot of papers accepted in the DBA area, especially when they are about real life experiences that you turn into a rock'n conference presentation!"

Think about your DBA experiences; the real specific ones that you feel proud about, the ones that you're really interested in. That's what people love to hear about, because they feel your passion!

I feel so strongly about this, I'll offer this: I will pre-review your abstract and help make it a better one.

Hope to see your abstract at next years IOUG!

All the best in your Oracle performance endeavors,

Craig.


If you enjoy my blog, I suspect you'll get a lot out of my online or in-class training. Go to www.orapub.com . I also offer on-site training and consulting services.

P.S. If you want me to respond to a comment or you have a question, please feel free to email me directly at craig@orapub .com. Another option is to send an email to OraPub's general email address, which is currently info@ orapub. com.
  

Categories: DBA Blogs

Creating A Tool: Detailing Oracle Process CPU Consumption

Fri, 2014-01-10 09:29
Detailing an Oracle Database process's CPU consumption is amazing, a lot of fun, and can lead to some “ah ha” moments. To make this posting daily digestible, I’m breaking it up into a multi-part series. Today I’m posting the third and final part entitled, Creating A Tool. Here we go…

In the previous post I ended with a discussion about how to gather an operating system's CPU consumption by function. More specific for our purposes is gathering Oracle process (session) CPU consumption at the Oracle kernel function level.

I Want Something Like This
At this point, we are ready to take the Linux perf tool output and merge that with Oracle wait event data. The output could look something like this:

Time Component secs %
------------------------------------------------------- ---------- -------
cpu : [.] kcbgtcr 29.714 66.87
cpu : [.] kdstf000010100001km 3.716 8.36
cpu : [.] lnxsum 3.541 7.97
cpu : [?] sum of funcs consuming less than 2% of CPU ti 2.393 5.38
cpu : [.] kaf4reasrp0km 2.180 4.91
wait: latch: cache buffers chains 2.100 4.73

I suspect you're not surprised that the above output was, in fact, taken from an existing tool. The tool is called "fulltime.sh" and is the result of a collaborative effort with myself and Frits Hoogland. You can download the free tool here. If the link doesn't work, just do an OraPub.com search for "fulltime" and you'll see it.

How The Tools Works
Obviously the tool collects both process CPU consumption and also Oracle wait event data. But we wanted some usage flexibility, so it's a little more interesting than that. Also, the real trick is outputting the perf data into a comma delimited file using "perf report -t,". It's the "t" option that is key here. Oracle can easily read the coma delimited file as an external table and then combine that with the wait event data collected from the v$sess_time_model view.

Here is the basic idea:

Help user identify the PID to profile
Initial setup
Loop
get oracle wait times (snap 0)
get oracle CPU time (snap 0)
start oracle kernel cpu details collection
sleep x
get oracle wait times (snap 1)
get oracle CPU time (snap 1)
stop oracle kernel cpu collection
do some cool math and combine results
display results
End Loop

The looping capability easily allows for a single collection or multiple collection. The multiple collection option displays much like the "top" tool with a default cycle duration of three seconds.

If you're running on a virtual machine, make sure to change the shell script variable PERF_SAMPLE_METHOD to '-e cpu-clock' instead of the default '-e cycles'. If you look near the top of the fulltime.sh script, you will see a comment about this and also where to make the change.

Before you run the script, take a quick look at the script, especially the top third. There is some good information about the script, default settings, and usage details.

How To Run The Script
There are two input usages. Let's call them basic and advanced.

The BASIC USAGE helps you find the Oracle process ID, sets some defaults for you, and works on a three second cycle until you control-C. For example, let's say you don't know the OS process but you do know the Oracle SID or perhaps you know the machine name or user name. Then you're in luck because the fulltime.sh script will display this information and then prompt you for the OS PID! Here is an example of you can expect if you simply enter, "./fulltime.sh" and selected process 60505.


For each cycle, you would see something like this:


Notice that both Oracle and OS process details are shown in conjunction with the date and time. Plus the SQL statement the process is running at the end of the sample period! Then the total time is shown along with the two high level components, that is, CPU consumption and Oracle wait time. Finally, the details are shown. Notice the details clearly identify the time component as either CPU or wait time.

If the time is tagged CPU then the Oracle kernel function is displayed (thank you perf) along with it's inferred time (based on both perf and v$sess_time_model). If the time is tagged Oracle wait time then the wait event name is displayed along with the wait time (based on v$session_event).

It's important to understand that if we ran a standard wait event report, the output would be similar but with NO CPU information. The wait events detail would still be displayed. This tool simply adds value by incorporating the CPU consumption.

The ADVANCED USAGE gives us full control. The advanced usage requires the OS process ID, the cycle duration, and the number of cycles. The advanced option makes it simple to get a single long cycle or multiple shorter cycles. For example, if I wanted to watch process 1234 in 5 second intervals, I could do this:
$ ./fulltime.sh 1234 5 9999
Or suppose I wanted a single 60 second sample for process 5432. I would enter this:
$ ./fulltime.sh 5432 60 1
The output will look exactly like the output from basic usage. You are just not prompted or given information to help you pick the OS PID.

Here is a short video so you can watch this in action!


Fulltime.sh - Report both Oracle Wait and CPU time details from OraPub on Vimeo.
Why This Is So Important
Look at the example screen shot below and ask yourself, "Is there an IO bottleneck?"


If the CPU information was not combined with the wait time details, I suspect 90% of Oracle DBAs would say there is an IO problem. They would see the top wait event db file sequential read then conclude there is an IO issue. However, as the fulltime.sh script clearly shows, CPU consumption is much larger than Oracle wait time, providing us with a greater opportunity to reduce the total time and devise additional spot-on solutions. Also looking at the report's "total time" breakdown, it clearly shows CPU consumption is 97% of the total time. I wouldn't be surprised if there was a raging CPU bottleneck!

So Where Are We In This Quest?
Now that you have access to the fulltime.sh script and know how to use it, you're going to start asking, "Just what is kcbgtcr anyways?!" In my next posting I'll address this.

But until then, get the fulltime.sh script, log into your non-production yet active Linux box and give it a go! If it's not production, profile the Oracle log writer or database writer background process or perhaps an active server process!

Feeling dangerous yet? (Always!)

Here are the key resource links:

1. Presentation: Detailing Oracle CPU Consumption. Download HERE.
2. Tool: Oracle wait time and kernel function CPU monitor. Download the fulltime.sh script HERE.

Thanks for reading,

Craig.


If you enjoy my blog, I suspect you'll get a lot out of my courses; Oracle Performance Firefighting,  Advanced Oracle Performance Analysis, and my new seminar, Go Faster: Make Oracle Work For You! I teach these classes around the world multiple times each year. For the latest schedule, go to www.orapub.com . I also offer on-site training and consulting services.

P.S. If you want me to respond to a comment or you have a question, please feel free to email me directly at craig@orapub .com. Another option is to send an email to OraPub's general email address, which is currently info@ orapub. com.

Categories: DBA Blogs