Skip navigation.

DBA Blogs

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

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

Oracle instance activity repartition

DBA Scripts and Articles - Fri, 2014-04-04 10:16

Description This query will show you repartition percentage between I/O, WAITS and CPU from the v$active_session_history view Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it. Instance activity repartition [crayon-5398ce47da050995799081/] This query returns percentage of I/O, CPU and waits consumed by [...]

The post Oracle instance activity repartition appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

VIDEO: The Evolution of IT Outsourcing

Pythian Group - Fri, 2014-04-04 07:46

“For us to understand what’s going on in the industry at large, it helps for us to have a deeper understanding of the history of the outsourcing industry.” Pythian founder, Paul Vallée shares his insights on the history and evolution of IT outsourcing.

Pythian has developed 5 criteria for choosing a data management outsourcing partner. Download the white paper here.

Categories: DBA Blogs

Log Buffer #366, A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2014-04-04 07:43

While the Oracle blogosphere is buzzing with the Collaborate news and views, SQL Server and MySQL bloggers are also getting upbeat about their respective fields and producing gems of blog posts. This Log Buffer Edition covers that all and more.

Oracle:

Run Virtual Machines with Oracle VM.

Last Call to Submit to the JavaOne Java EE Track.

UX – No Time To Kill.

Updated: Price Comparison for Big Data Appliance and Hadoop.

PLSQL_OPTIMIZE_LEVEL paraeter and optimizing of the PL/SQL code.

SQL Server:

SQL Server 2012 AlwaysOn Groups and FCIs.

How to recover a suspect msdb database in SQL Server.

Data Science Laboratory System – Distributed File Databases.

Stairway to T-SQL: Beyond The Basics Level 5: Storing Data in Temporary Tables.

The Girl with the Backup Tattoo.

MySQL:

pt-online-schema change and row based replication.

MariaDB Client Library for C 2.0 and Maria JDBC Client 1.1.7 released.

Help MariaDB gather some statistics!

Uninitialized data in the TokuDB recovery log.

MySQL at LOADays Conference in Antwerp, Belgium (Apr 5-6, 2014)

Categories: DBA Blogs

SQL Developer’s Interface for GIT: Cloning a GitHub Repository

Galo Balda's Blog - Wed, 2014-04-02 22:05

SQL Developer 4 provides an interface that allows us to interact with Git repositories. In this post, I’m going to show how to clone a GitHub (A web based hosting service for software development projects that uses the Git revision control system) repository.

First you need to sign up for a GitHub account. You can skip this step if you already have one.

Your account will give you access to public repositories that could be cloned but I suggest you create your own repository so that you can play with SQL Developer and see what the different options can do.

Once you have an account, click on the green button that says “New Repository”. It will take you to a screen like this:

github_create_repo

Give your repository a name, decide if you want it to be public or private (you have to pay), click on the check box and then click on the green button. Now you should be taken to the main repository page.

github_repo

Pay attention to the red arrow on the previous image. It points to a text box that contains the HTTPS clone URL that we’re going to use in SQL Developer to connect to GitHub.

Let’s go to SQL Developer and click on Team –> Git –> Clone… to open the “Clone from Git Wizard”. Click on the next button and you should see the screen that lets you enter the repository details:

remote_repo

Enter the repository name, the HTTPS clone URL, your GitHub user name and your password. Click on next to connect to the repository and see the remote branches that are available.

remote_branch

The master branch gets created by default for every new repository. Take the defaults on this screen and click on next to get to the screen where you specify the destination for your local Git repository.

destination

Enter the path for the local repository and click on next. A summary screen is displayed and showing the options you chose. Click on finish to complete the setup.

How do we know if it worked? Go to the path of your local repository and it should contain the same structure as in the online repository.

local_repo

On a next post I’ll show how to commit changes to the local repository and how to push them to GitHub.


Filed under: GIT, SQL Developer, Version Control Tagged: GIT, SQL Developer, Version Control
Categories: DBA Blogs

Two Adaptive Plans Join Methods Examples

Bobby Durrett's DBA Blog - Wed, 2014-04-02 14:49

Here is a zip of two examples I built as I’m learning about the new adaptive plans features of Oracle 12c: zip

The first example has the optimizer underestimate the number of rows and the adaptive plans feature switches the plan on the fly from nested loops to hash join.

In the second example the optimizer overestimates the number of rows and the adaptive plans feature switches the plan from merge join to nested loops.

I ran the same scripts on 12c and 11.2.0.3 for comparison.

Example 1 11g:

Plan hash value: 2697562628

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.01 |      18 |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |      1 |00:00:00.01 |      18 |
|   2 |   NESTED LOOPS                |      |      1 |        |      8 |00:00:00.01 |      18 |
|   3 |    NESTED LOOPS               |      |      1 |      1 |      8 |00:00:00.01 |      17 |
|*  4 |     TABLE ACCESS FULL         | T1   |      1 |      1 |      8 |00:00:00.01 |      14 |
|*  5 |     INDEX RANGE SCAN          | T2I  |      8 |      1 |      8 |00:00:00.01 |       3 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2   |      8 |      1 |      8 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------

Example 1 12c:

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.01 |       6 |       |       |          |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|*  2 |   HASH JOIN         |      |      1 |      1 |      8 |00:00:00.01 |       6 |  2168K|  2168K|     1/0/0|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |      1 |      8 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |      1 |     16 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Example 2 11g

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.01 |      16 |       |       |          |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |      1 |00:00:00.01 |      16 |       |       |          |
|   2 |   MERGE JOIN                  |      |      1 |      4 |      1 |00:00:00.01 |      16 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2   |      1 |     16 |      2 |00:00:00.01 |       2 |       |       |          |
|   4 |     INDEX FULL SCAN           | T2I  |      1 |     16 |      2 |00:00:00.01 |       1 |       |       |          |
|*  5 |    SORT JOIN                  |      |      2 |      4 |      1 |00:00:00.01 |      14 | 73728 | 73728 |          |
|*  6 |     TABLE ACCESS FULL         | T1   |      1 |      4 |      1 |00:00:00.01 |      14 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Example 2 12c

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |      1 |00:00:00.01 |       5 |
|   2 |   NESTED LOOPS                |      |      1 |        |      1 |00:00:00.01 |       5 |
|   3 |    NESTED LOOPS               |      |      1 |      4 |      1 |00:00:00.01 |       4 |
|*  4 |     TABLE ACCESS FULL         | T1   |      1 |      4 |      1 |00:00:00.01 |       3 |
|*  5 |     INDEX RANGE SCAN          | T2I  |      1 |        |      1 |00:00:00.01 |       1 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2   |      1 |      1 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------

The output of the plans for the 12c examples end with this line:

Note
-----
   - this is an adaptive plan

So, that tells me it is the adaptive plan feature that is changing the plan despite the wrong estimate of the number of rows.

- Bobby

 

Categories: DBA Blogs

Wed, 1969-12-31 18:00