Aggregate unique and ordered collection elements with the COLLECT function in 11g. November 2009
The past year has been well, many words come to mind but let's go with challenging. It's also been interesting, frustrating, enlightening, exhausting, but right about now, it feels like it was a very, very good year. Those of you that have read through the previous posts will remember that, right around the time I left for the Miracle Oracle Open World conference in October of 2008, I was
Instance caging is another small but useful feature of Oracle Database 11g Release 2. Thanks to it the database resource manager is able, for the first time, to limit the number of CPUs that can be used by a given instance. (By the way, note that this limit has no “impact” on the number of [...]
I just finished reading Oracle Data Guard Handbook written by Larry Carpenter, Joseph Meeks, Charles Kim, Bill Burke, Sonya Carothers, Joydip Kundu, Michael Smith and Nitin Vengurlekar. In 544 pages you will learn everything you need to know about Data Guard. This well written book begins with a lengthy introduction to the Data Guard architecture [...]
Yesterday WordPress introduced a really cool new feature: blog subscriptions. It is now easier than ever to follow my blog, just subscribe and you will receive an email whenever I post something new. Cool huh? You can subscribe by leaving your email address above the SignMeUp! button located in the right side-bar on my blog [...]
I read through several posts that I had saved as drafts today. Some were close to done but are no longer relevant. Others were nothing more than a sentence or a link, and now I don't recall where I intended to take the story. I had saved only the link for this one and it's non-technical, but I liked the article and considering some of the churning over MOS, it seemed appropriate. (Draw your
Zero-size unusable indexes and index partions is a small but useful feature of Oracle Database 11g Release 2. Simply put, its aim is to save space in the database by immediately releasing the segment associated to unusable indexes or index partitions. To illustrate this, let’s have a look to an example…
Create a partitioned table, insert [...]
When I start a new project, I like to check performance from as many layers as possible. This helps to verify things are working as expected and helps me to understand how the pieces fit together. My recent work with dNFS and Oracle 11gR2, I started down the path to monitor performance and was surprised to see that things are not always as they seem. This post will explore the various ways to monitor and verify performance when using dNFS with Oracle 11gR2 and Sun Open Storage “Fishworks“.
“iostat(1M)” is one of the most common tools to monitor IO. Normally, I can see activity on local devices as well as NFS mounts via iostat. But, with dNFS, my device seems idle during the middle of a performance run.
bash-3.0$ iostat -xcn 5 cpu us sy wt id 8 5 0 87 extended device statistics r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device 0.0 6.2 0.0 45.2 0.0 0.0 0.0 0.4 0 0 c1t0d0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 toromondo.west:/export/glennf cpu us sy wt id 7 5 0 89 extended device statistics r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device 0.0 57.9 0.0 435.8 0.0 0.0 0.0 0.5 0 3 c1t0d0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 toromondo.west:/export/glennf
From the DB server perspective, I can’t see the IO. I wonder what the array looks like.
The analytics package available with fishworks is the best way to verify performance with Sun Open Storage. This package is easy to use and indeed I was quickly able to verify activity on the array.
There are 48,987 NFSv3 operations/sec and ~403MB/sec going through the nge13 interface. So, this array is cooking pretty good. So, let’s take a peek at the network on the DB host.
nicstat is wonderful tool developed by Brendan Greg at Sun to show network performance. Nicstat really shows you the critical data for monitoring network speeds and feeds by displaying packet size, utilization, and rates of the various interfaces.
root@saemrmb9> nicstat 5 Time Int rKB/s wKB/s rPk/s wPk/s rAvs wAvs %Util Sat 15:32:11 nxge0 0.11 1.51 1.60 9.00 68.25 171.7 0.00 0.00 15:32:11 nxge1 392926 13382.1 95214.4 95161.8 4225.8 144.0 33.3 0.00
So, from the DB server point of view, we are transferring about 390MB/sec… which correlates to what we saw with the analytics from Fishworks. Cool!
Ok, I wouldn’t be a good Sun employee if I didn’t use DTrace once in a while. I was curious to see the Oracle calls for dNFS so I broke out my favorite tool from the DTrace Toolkit. The “hotuser” tool shows which functions are being called the most. For my purposes, I found an active Oracle shadow process and searched for NFS related functions.
root@saemrmb9> hotuser -p 681 |grep nfs ^C oracle`kgnfs_getmsg 1 0.2% oracle`kgnfs_complete_read 1 0.2% oracle`kgnfswat 1 0.2% oracle`kgnfs_getpmsg 1 0.2% oracle`kgnfs_getaprocdata 1 0.2% oracle`kgnfs_processmsg 1 0.2% oracle`kgnfs_find_channel 1 0.2% libnfsodm11.so`odm_io 1 0.2% oracle`kgnfsfreemem 2 0.4% oracle`kgnfs_flushmsg 2 0.4% oracle`kgnfsallocmem 2 0.4% oracle`skgnfs_recvmsg 3 0.5% oracle`kgnfs_serializesendmsg 3 0.5%
So, yes it seems Direct NFS is really being used by Oracle 11g.
There are a set of V$ tables that allow you to sample the performance of the performance of dNFS as seen by Oracle. I like V$ tables because I can write SQL scripts until I run out of Mt. Dew. The following views are available to monitor activity with dNFS.
With some simple scripting, I was able to create a simple script to monitor the NFS IOPS by sampling the
v$dnfs_stats view. This script simply samples the nfs_read and nfs_write operations, pauses for 5 seconds, then samples again to determine the rate.
Excellent! Oracle shows 48,000 NFS IOPS which agrees with the analytics from Fishworks.
Consulting the AWR, shows “Physical reads” in agreement as well.
Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~ --------------- --------------- ---------- ---------- DB Time(s): 93.1 1,009.2 0.00 0.00 DB CPU(s): 54.2 587.8 0.00 0.00 Redo size: 4,340.3 47,036.8 Logical reads: 385,809.7 4,181,152.4 Block changes: 9.1 99.0 Physical reads: 47,391.1 513,594.2 Physical writes: 5.7 61.7 User calls: 63,251.0 685,472.3 Parses: 5.3 57.4 Hard parses: 0.0 0.1 W/A MB processed: 0.1 1.1 Logons: 0.1 0.7 Executes: 45,637.8 494,593.0 Rollbacks: 0.0 0.0 Transactions: 0.1
iostat(1M) monitors IO to devices and nfs mount points. But with Oracle Direct NFS, the mount point is bypassed and each shadow process simply mounts files directly. To monitor dNFS traffic you have to use other methods as described here. Hopefully, this post was instructive on how to peel back the layers in-order to gain visibility into dNFS performance with Oracle and Sun Open Storage.
The Solaris x86-64 port of Oracle Database 11g Release 2 can now be downloaded from OTN. Get it while it’s hot!
Tweet This Post
Various techniques for sorting collections. November 2009