Randy Johnson & Karl Arao
A PeopleSoft & OBIEE Consolidation Success Story
In today’s competitive business climate companies are under constant pressure to reduce costs without sacrificing quality. Many companies see database and server consolidation as the key to meeting this goal. Since its introduction, Exadata has become the obvious choice for database and server consolidation projects. It is the next step in the evolutionary process. But managing highly consolidated environments is difficult, especially for mixed workload environments. If not done properly the quality of service suffers. In this session we tell the tale of a large real estate investment company that successfully consolidated their global operations onto a Maximum Availability Architecture Exadata platform. Applications sharing this environment include PeopleSoft Financials, PeopleSoft HR, Portal, and OBIEE. Accurate provisioning and management of system resources was absolutely essential to our success. In this session we share lessons learned and the tools you’ll need to ensure that your consolidation story has a happy ending.

E4 is an Exadata focused event and we have a very interesting speaker line up and abstract list http://www.extremeexadata.com/e4/abstracts which are powerful & useful for Oracle DBAs/Architects/Managers who are seriously into performance and Exadata not just from a “wow that’s interesting” but also from a practical perspective. So I’m pretty sure you Exadata hackers out there will surely love this event because it’s going to be all technical!

Just to give you a glimpse on what Randy and I are presenting, I have here a mind map of our topic list which we will transform into an interesting story..

I’ll be blogging some of them in the next couple of days.. the ASM writes IOPS effect is already out and I’d like to focus next on the CPU capacity planning… taking the quantitative information and make it visual so both the technical and non-technical can “see” what’s going on leading to solid solutions.

See you at E4!

This post will detail on the ASM redundancy/parity effect on IOPS… if… by changing the ASM redundancy (external, normal, and high) will it decrease the workload read/write IOPS or stay as is. I’ll walk you through the step by step on how I did the instrumentation + the test case itself then end with some results and observations.

Let’s get started!

The Environment

My R&D server running OEL 5.7 on UEK kernel with

•  Intel 2nd gen Sandy Bridge
• 8 x 1TB 7200 RPM SATA3 – shortstroked
  • on first 320GB vbox LVM stripe (VirtualBox .vdi files)
  • next 320GB ASM (DATA disk group – external redundancy)
  • remaining RECO area (for backups)
• 16GB physical memory having lots of VirtualBox VMs (12 VMs can run concurrently with 0.45 load average and no CPU WAIT IO)

SLOB installation

Follow the “Installation” and “Run the benchmark” sections on my wiki here http://karlarao.tiddlyspot.com/#%5B%5Bcpu%20-%20SillyLittleBenchmark%20-%20SLOB%5D%5D

Instrumentation

Essentially you have to be able to instrument the different layers of things. The sample screenshot below are the terminal sessions I have while doing the test cases and it’s really helpful to have them all in a single view because you’ll be able to quickly notice changes as the workload grow and reach its sustained state. So when you run sh ./runit.sh 0 128 you’ll be able to see from load average of 2 it will gradually go up to 128 and it will stay on that range and that’s the sustained state of the workload. Then from there I’ll start to take screenshots and gather all sorts of data that will serve as my data points from that test case. So with the single view you’ll be able to see how everything behaves from the ramp-up to the sustained state and when you cancel the run or whenever you do any changes to your test case environment.

1) Hardware:

On the hardware side I’ve got collectl (http://collectl.sourceforge.net) which shows the overall workload of the OS and CPU + IO details in a time series manner (see the first 3 terminal windows above). And I usually execute the following commands for every run and package the spooled output in a test case folder

collectl --all -o T -o D >> collectl-all.txt
collectl -sc --verbose -o T -o D >> collectl-cpuverbose.txt
collectl -sD --verbose -o T -o D >> collectl-ioverbose.txt

The critical numbers would be the aggregate IOPS from all the 8 disks, and the detailed IOPS per disk together with the IO size, service time, and Pct% Utilization. All these numbers gives you an IO characterization of what SLOB is doing.

2) Database:

Load Profile
The OS numbers has to be correlated to the physical reads/writes (load profile) on the database side and just relying on the AWR snapshots taken at start and end of the test case is not really a good measure especially on a small scale box like mine with just 4cores and 8spindles which essentially I’m making it work for 128 concurrent users doing an update on their own tables.. and ultimately the queueing effect will kick in and the disks will be really busy that you’ll get to a point where the AWR snapshots will not be able to do its thing and may even hang or queue or it will wait for it to be able to grab some IO or until it gets an IO bandwidth to do its own operations then that’s the time it will be able to complete the AWR snapshot/report…whew.

So to get around that, you have to read those performance numbers from memory and it’s only possible by doing a query from a v$ view.. luckily there’s a script created by Timur Akhmadeev a few months ago http://timurakhmadeev.wordpress.com/2012/02/21/load-profile/ that outputs the same Load Profile section of the AWR report but pulls data from the v$sysmetric view. The view outputs the past 15 and 60 seconds of a bunch of metrics which is similar to V$SYSSTAT but some metrics are already transformed into per/sec or per/transaction numbers. The one that Timur used is the 60secs time slice which is equivalent to creating 1min AWR reports which is good enough since the workload is sustained and the numbers will most likely not vary that much plus the Load Profile section has all the stuff that I need to see what’s happening on the physical reads/writes because SLOB is essentially a test case to guage your reads/writes IOPS.

I’ve modified the SQL https://www.dropbox.com/s/3ijoy3zljqps0wx/loadprof.sql to add the “tm” column so I can correlate it with the time series output of collectl. The sample output is shown on the left side of the image above. You can download the script and put it on the SLOB directory and run it as

while : ; do sqlplus "/ as sysdba" @loadprof ; echo "--"; sleep 2 ; done

ASH
I also want to see how the workload looks like in ASH.. so I’ve got a script called ash that calls a SQL script by Kyle Hailey which gives you the Average Active Sessions (AAS) for the past 5 seconds time slice.. so if let’s say I’m doing a sustained number of 128 readers I should be able to see 128 AAS doing “db file sequential read” (random IO).. so rather than pulling up an OEM or creating a repo for OEM just to see what’s happening on the SLOB run I can just go with this text graph or text version of what I’d like to see..

The sample output is shown on the right hand side of the image, see the FASL column having the 128 AAS on “db file sequential read”.. also the time column at the far left can be correlated to the collectl and loadprof.sql output. The script is available here
https://www.dropbox.com/s/pbcewv374dq5bc0/ash-aveactn300.sql

3) Sessions:

Another important thing is measuring the latency… I could have written a SQL that will mimic the top 5 timed events pulling from v$ views that also shows the latency column but for now I don’t really have the time to do that. So a very good alternative is to do it with the “awesome snapper” then spool it to a file and grep for stuff… and because the SLOB is doing a sustained reads/writes workload the numbers will most likely stay the same or within the range of that sustained number and with that I can just do 5 samples or 5 runs of snapper and that will tell me what my latency is.

Good thing about using the snapper is the latency is being measured from the SLOB users themselves and that’s what the sessions are experiencing for each IO operation that they are doing.  Also take note that with the snapper command below I can grep for DBWR, CKPT, LGWR processes which I can’t do in the usual top 5 timed events..which I was able to build a cool comparison of latency graphs across redundancy levels (more on this on the results section). And each snapper sample has a timestamp that you can correlate with the collectl, loadprof, and ash output. Below are the commands I used:

spool snapper.txt
@snapper ash=event+wait_class,stats,gather=tsw,tinclude=CPU,sinclude=redo|reads|writes 5 5 "select sid from v$session where username like 'USER%' or program like '%DBW%' or program like '%CKP%' or program like '%LGW%'"
spool off

cat snapper.txt | grep write | egrep "DBW|CKP|LGW" | grep WAIT | sort -rnk2
cat snapper.txt | grep "db file sequential read"
cat snapper.txt | grep "free buffer waits"
cat snapper.txt | grep "write complete waits"

Check out the script here http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper

The test case

Before the test case I executed a backup of the SLOB database then I make use of that backup as a starting point of each iteration which makes the underlying data consistent across runs. Then for each ASM redundancy I did the following:

1) Wipe out the diskgroup

sqlplus "/ as sysasm"
alter diskgroup data dismount force;
drop diskgroup data force including contents;

2) Create the new diskgroup (1st iteration – normal, 2nd iteration – high, 3rd iteration – external)
3) Restore the backup
4) Execute 128 readers and gather perf data
5) Execute 128 writers and gather perf data
6) Go back to step1

Results

>>128 Readers Testcase

External Redundancy

Normal Redundancy

High Redundancy

>>128 Writers Testcase

External Redundancy

Normal Redundancy

High Redundancy

>>Read IOPS

• Shown on the left is the IOPS comparison across redundancy
• on the right is the read:write ratio

>>Write IOPS

• Shown on the left is the IOPS comparison across redundancy
• on the right is the read:write ratio
• the graph at the bottom is just the “write iops” part of the graph above.. since SLOB is executing a massive UPDATE SQL with some filter predicates and that causes scan on some rows which translates to “read iops”

>>Read Latency (ms)

• Here are the graph definitions:
  • ckpt -> CKPT process – “control file parallel write” latency
  • lgwr -> LGWR process – “log file parallel write” latency
  • dbwr -> DBWR process – “db file parallel write” latency
  • phyreads -> SLOB users – “db file sequential read” latency
  • fbw -> SLOB users – “free buffer waits” latency
  • wcw -> SLOB users – “write complete waits” latency

>>Write Latency (ms)

• (see graph definitions above)

Observations

>>Some insights on the numbers above…

First of all.. I can say I did a really good job on documenting all these stuff  the results are in 6 parts

• part 1 >>128 Readers Testcase
• part 2 >>128 Writers Testcase
• part 3 >>Read IOPS
• part 4 >>Write IOPS
• part 5 >>Read Latency (ms)
• part 6 >>Write Latency (ms)

but as you can see at a high level as I change redundancy the read IOPS stayed at the range of 2400+ IOPS while on the writes as I move to “normal” redundancy it went down to half and “high” redundancy it went down to 1/3 and if you are a big SLOB fan, the first two parts of the results will be gold mine for you because that’s more of a geeky output and I essentially pulled the numbers from those raw screenshots to build the graphs.

So let’s move ahead on explaining the charts…

On the Read IOPS and latency I don’t see any huge difference and I can say it’s not being affected.. the IOPS stayed at the range of 2400+ IOPS then the latency at the range of 900ms across redundancy levels. It clearly shows that you don’t have a raid penalty or parity penalty to sacrifice for reads.

On the Write IOPS and latency

• it’s pretty clear that on normal you lose half and on high you only get 1/3 of the hardware IOPS
• And what’s interesting about this is when I was watching the workload of normal and high I can see an increasing number of AAS (average active sessions) spent on “free buffer waits” and “write complete waits”… well I’m seeing them on external redundancy but not as much.
• Actually, as we change redundancy we are lowering the IOPS capacity which we sacrifice for the redundancy/parity and still streaming the same amount of workload and these wait events are just signs of inadequate bandwidth. The DBWR is working harder to make free buffers available for the same workload with lesser IOPS to work on and that translates to higher latencies.
• You will also notice on the raw screenshots that the hardware write IOPS numbers from collectl stayed at around 1000+ at full throttle while on the database load profile you’ll see that it’s just around 300 (high redundancy) which is the effect of the ASM parity.

>>How can this affect your Storage Capacity Planning?

I’ll pull a conversation I had with a really good friend of mine.. his question was ”Quick question on your AWR mining script AWR-gen-wl…, is IOPs calculated before or after ASM mirroring? For example on Exadata, if I see 10,000 write IOPs, did the cells do 10k or did they do 20k (normal redundancy)?”

and here’s my response..

The script awr_iowl.sql and awr_iowlexa.sql have the columns that accounts for RAID1.. that is read penalty of 1 and write penalty of 2.
read on the section “the iops raid penalty” on this link http://www.zdnetasia.com/calculate-iops-in-a-storage-array-62061792.htm and the “real life examples” on this link http://www.yellow-bricks.com/2009/12/23/iops/

so those computations should also apply for Exadata since normal redundancy is essentially RAID1 that’s write penalty of 2, and the high redundancy is penalty of 3.

Now I remember this sizing exercise I had with an EMC engineer on a project bid before https://www.evernote.com/shard/s48/sh/03602b99-3274-4c64-b5d1-bbe7bd961f8d/95be02ccf9aa75cf863bb19115353eb0
and that’s why I created those columns to get the data directly from AWR.. so for every snapshot you’ve got the “hardware iops needed” and “number of disks needed”, what’s good about that is as your workload vary those two numbers will be representative of that workload. So since you have a lot of data samples, I usually make a histogram on those two columns and get the top percentile numbers because most likely those are the peak pereiods and I can investigate on it by drilling down on the snap_ids and looking into the SQLs and validating it to the app owners as to what’s the application is running at that time.

I’ve attached an excel sheet which you can just plug the total workload iops on the yellow box. So in your case, let’s say you have 10K workload IOPS with 50:50 read/write ratio… that’s equivalent to 15K hardware IOPS for normal redundancy and 20K hardware IOPS for high redundancy.

the excel screenshot is actually here —-> https://lh6.googleusercontent.com/-00PkzwfwnOE/T-N0oo2Q-FI/AAAAAAAABqE/EbTOnHBlpmQ/s2048/20120621_IOPS.png

Note that I’m particular with the words “workload IOPS” and “hardware IOPS”
so on this statement
“if I see 10,000 write IOPs, did the cells do 10k or did they do 20k (normal redundancy)?” <– if this 10,000 is what you pulled from the AWR then it’s the database that did the 10K IOPS so that’s the “workload IOPS”.. and that’s essentially your “IO workload requirements”.

Then let’s say you haven’t migrated to Exadata.. you have to take into account the penalty computation shown above.. so you’ll arrive with 15000 “hardware IOPS” needed (normal redundancy).. and say each disk has IOPS of 180 then you need at least 83 disks so that’s 83disks / 12 disks each cell = 6.9 storage cells … and that’s Half Rack Exadata. But looking at the data sheet https://www.dropbox.com/s/ltvr7caysvfmvkr/dbmachine-x2-2-datasheet-175280.pdf it seems like you can fit the 15000 on a quarter rack (because of the flash).. mmm.. well I’m not pretty confident with that because if let’s say 50% of 15000 IOPS are writes (7.5K IOPS) then I would investigate on the IOPS write mix if most of it are DBWR related (v$sysstat.physical write IO requests) or LGWR related (v$sysstat.redo writes) and if most of it are DBWR related then I don’t think you’ll ever benefit from the smart flash log. So I would still go with the Half (12500 disk IOPS) or Full Rack (25000 disk IOPS) for my “hardware IOPS” capacity. And I’ll also take into consideration the MB/s needed for that database but that should be augmented by the flash cache.

In addition to my response above.. Also be aware that Smart Flash Log does not lower the HDD (HardDisk) touch load…both sides of the write go to flash and HDD concurrently. So you should not be too optimistic about the Smart Flash Log when sizing Exadata for “write IOPS” because you can’t predict who will always win on the fastest-write-race…so as much as possible base your write IOPS sizing on HardDisk IOPS. But definitely on the read IOPS even small (OLTP) or large IOPS (DSS) the flash cache will be able to augments those HDD numbers.

So with my test case shown here.. the statement on the reply above is true. The ASM redundancy/parity affects the “workload write IOPS” number (be it small-OLTP or large-DSS IOPS).. but not for the “workload read IOPS”. This behavior is the same even on a regular SAN environment… which you have to be careful/aware when sizing storage. So now you know that 50K IOPS in any data sheet is not equal to 50K write IOPS.. factor in the redundancy  and always remember Utilization=Requirements/Capacity no matter what the type workload or platform is as long as as the total workload IOPS/MBs requirements is well within the total hardware IOPS/MBs capacity you are up a notch on not seeing any IO bottlenecks.

Hope I’ve shared you some good stuff

when you run

./saturate 4 dbm1 2 exadb1

cat *log | grep benchmark
Sample output below:
 benchmark ,instance       ,start            ,end              ,elapsed   ,MBs
 ----------,---------------,-----------------,-----------------,----------,-------
 benchmark ,dbm1           ,05/13/12 19:18:28,05/13/12 19:19:32,        64,    537
 benchmark ,dbm1           ,05/13/12 19:18:28,05/13/12 19:19:30,        62,    554
 benchmark ,dbm1           ,05/13/12 19:18:28,05/13/12 19:19:32,        63,    545
 benchmark ,dbm1           ,05/13/12 19:18:28,05/13/12 19:19:32,        64,    537
 benchmark ,exadb1         ,05/13/12 19:18:28,05/13/12 19:19:32,        64,    539
 benchmark ,exadb1         ,05/13/12 19:18:28,05/13/12 19:19:32,        64,    539

This AWESOME text graph is similar to what you see in the Enterprise Manager performance page IO tab. Note that you’ll be seeing higher numbers of MB/s on the smartscanloop compared to EM because of a more fine grained interval (2secs) which is also the same behavior when you measure the IO latency as I explained here (avg latency issue)

then I’ve modified the script to have the advanced output that shows the Hierarchy of Exadata IO. See the updated README for more details on how to use it. Below is the output

What’s good about this is the numbers are about the same when you do a per 10secs snapshot of AWR.. compare the AAS and latency (avgwt ms) columns of the above image and below

Again, this is pretty useful for monitoring the high level smart scans IO that’s happening across your Exadata cluster, if you are on an environment where there’s separation of duties you can even hand off this script to the sys admins that are monitoring the storage cells with their home grown alerting scripts, kSar, or nagios.. so this will serve as their view on the database side of things.

And if any of your clients haven’t adopted the IORM, this is very useful for DEMOs to customer sites to showcase the IORM capabilities.. and if you don’t want to show the latency and other columns you can opt to just use the simple output which only shows the smart scans MB/s (see get_smartscan.simple on README ). Most of the time.. the simpler the output the easier for them (users) to understand.

Sweet! right?!?

Wait.. Does the toolkit work on non-Exadata DBs?

The SECTION 1 works on Exa and non-Exa (I’ve also mentioned this at the beginning of this post)

The SECTION 2 works well on Exa because the underlying scripts just makes use of dcli commands, some shell and SQL.. but if you are interested to have the smartscanloop output on non-Exa environment I have the script get_smartscan.nonexa on the toolkit that shows the “physical read total bytes” instead of the “cell physical IO bytes eligible for predicate offload”..

then on each node do this if it’s a non-Exa RAC

$ while : ; do ./get_smartscan.nonexa | grep "%" ; echo "--" ; sleep 2 ; done
%,05/12/12 12:57:33,DEMO1     ,      0,
%,05/12/12 12:57:35,dbm1      ,      0,
%,05/12/12 12:57:38,DBFS1     ,      0,
%,05/12/12 12:57:40,exadb1    ,      0,
%,05/12/12 12:57:42,PRIMARY1  ,      0,
--
%,05/12/12 12:57:46,DEMO1     ,      1,
%,05/12/12 12:57:48,dbm1      ,   2078, @@@@@@@@@@@@@@@@@@@@@
%,05/12/12 12:57:50,DBFS1     ,      0,
%,05/12/12 12:57:52,exadb1    ,    972, @@@@@@@@@@
%,05/12/12 12:57:55,PRIMARY1  ,      0,
--
%,05/12/12 12:57:59,DEMO1     ,      0,
%,05/12/12 12:58:01,dbm1      ,   1088, @@@@@@@@@@@
%,05/12/12 12:58:03,DBFS1     ,      0,
%,05/12/12 12:58:05,exadb1    ,   2356, @@@@@@@@@@@@@@@@@@@@@@@@
%,05/12/12 12:58:07,PRIMARY1  ,      0,
--
%,05/12/12 12:58:11,DEMO1     ,      0,
%,05/12/12 12:58:14,dbm1      ,   1000, @@@@@@@@@@
%,05/12/12 12:58:16,DBFS1     ,      0,
%,05/12/12 12:58:18,exadb1    ,   2336, @@@@@@@@@@@@@@@@@@@@@@@
%,05/12/12 12:58:20,PRIMARY1  ,      0,
--
%,05/12/12 12:58:24,DEMO1     ,      0,
%,05/12/12 12:58:26,dbm1      ,   1031, @@@@@@@@@@
%,05/12/12 12:58:29,DBFS1     ,      0,
%,05/12/12 12:58:31,exadb1    ,   2071, @@@@@@@@@@@@@@@@@@@@@
%,05/12/12 12:58:33,PRIMARY1  ,      0,
--

But wait! there’s more!

$ less smartscanloop.txt | sort -nk5 | tail
pd01db03: %,05/10/12 21:29:28,biprd2    ,   7358, @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
pd01db03: %,05/11/12 08:57:12,biprd2    ,   7363, @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
pd01db03: %,05/10/12 14:55:54,biprd2    ,   7454, @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
pd01db03: %,05/11/12 09:29:57,biprd2    ,   7556, @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
pd01db03: %,05/10/12 18:17:50,biprd2    ,   7785, @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
pd01db03: %,05/10/12 19:12:19,biprd2    ,   7880, @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
pd01db03: %,05/11/12 10:31:24,biprd2    ,   7886, @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
pd01db03: %,05/10/12 19:15:48,biprd2    ,   8112, @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
pd01db03: %,05/11/12 10:31:40,biprd2    ,   8138, @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
pd01db03: %,05/10/12 18:49:46,biprd2    ,   9315, @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

and since it’s a CSV file you can then easily graph it in Tableau! (just put a header on it first)

host       , time            , inst     , smartscan,
pd01db01: %,05/10/12 13:08:14,hcmprd1   ,      0,
pd01db01: %,05/10/12 13:08:16,paprd1    ,      0,
pd01db01: %,05/10/12 13:08:18,rmprd1    ,      0,
pd01db01: %,05/10/12 13:08:20,lmprd1    ,      0,
pd01db01: %,05/10/12 13:08:22,DBFSPRD1  ,      0,
pd01db01: %,05/10/12 13:08:24,hcrprd1   ,      0,
pd01db02: %,05/10/12 13:08:14,hcrprd2   ,      0,
pd01db02: %,05/10/12 13:08:16,rmprd2    ,      0,
pd01db02: %,05/10/12 13:08:18,paprd2    ,      0,
pd01db02: %,05/10/12 13:08:20,hcmprd2   ,      0,
pd01db02: %,05/10/12 13:08:22,DBFSPRD2  ,      0,
pd01db02: %,05/10/12 13:08:24,lmprd2    ,      0,
pd01db03: %,05/10/12 13:08:14,biprd2    ,   3412, @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
pd01db03: %,05/10/12 13:08:16,DBFSPRD3  ,      0,
pd01db04: %,05/10/12 13:08:14,DBFSPRD4  ,      0,
pd01db04: %,05/10/12 13:08:16,mvwprd2   ,      0,
pd01db04: %,05/10/12 13:08:18,fsprd2    ,    763, @@@@@@@@
pd01db04: %,05/10/12 13:08:20,mtaprd112 ,      0,
... output snipped ...

and hack the data! remember on the advanced output you can slice and dice it by host, time, inst, aas, latency, returned, ic, smartscan..

Here’s the IO graph per instance

and IO graph per instance and host

Hope I’ve shared you some good stuff

So I’ve got a couple of performance and capacity planning use case scenarios which I will blog in parts in the next few days or weeks. But before that I need to familiarize you on how I mine this valuable AWR performance data.

Let’s get started with the AWR top events, the same top events that you see in your AWR reports but presented in a time series manner across SNAP_IDs…

So I’ve got this script called awr_topevents_v2.sql http://goo.gl/TufUj which I added a section to compute for “CPU wait” (new metric in 11g) to include the “unaccounted for DB Time” on high run queue or CPU intensive workloads. This is a newer version of the script that I used here http://karlarao.wordpress.com/2010/07/25/graphing-the-aas-with-perfsheet-a-la-enterprise-manager/

On the screenshots below I spooled the output on a text file and transformed it to a CSV format. Then from there you can play around with the data points using Tableau that you can download here http://www.tableausoftware.com/public/download

Is this a free software? Yes. But the catch is Tableau Public only allows you to have 100K rows on a given text or csv file. But for my capacity planning and troubleshooting purposes that’s enough to do the work The paid version of Tableau (Desktop) allows you to connect directly to the database but that will cost you $1999. ouch. The Tableau (Desktop Personal) is much like the Public version but with no data limit and it allows you to save your work on a specific file format (.twb and .tde files), Yes guys in Public version you are only allowed to save it to their Public portal… but that’s fine as long as I have the data points I can quickly work on the visualization again. Here’s the comparison if you are interested on the detailed product comparison http://www.tableausoftware.com/products/desktop/specs

Once you get the CSV file ready which you can download HERE

1) This is a trial version of Tableau Desktop.. the steps should be the same or similar with the Public version. Click “Connect to data”

2) Select the CSV file

3) Select “Text File”

4) Then you can view the data points

5) Data Points are shown below

6) Click OK

7) Import all data

8) It will create a file format that I believe where it stores the dimension data

9) After the data points import

10) Drag the “Events” on the color section, then “Add all members”

11) Click OK

12) Drag the AAS on the Rows

13) SNAP_ID on the columns. That’s your AWR SNAP_IDs

14) Filter the negative values. Those are the periods where you had instance shutdown.

15) Exclude the Nulls

16) And now you can select an area! hover on it and it will give you the event and the AAS number (the higher the AAS the more database work is happening)

17) Play around with the AAS data points

18) Now here it will show you detailed SNAP_ID data points (the dots) and you can hover on it

19) You can also view the underlying data points of those dots

20) Here it shows the event, SNAP_IDs, and the AAS value.. so here you can grab the SNAP_ID and execute the AWRRPT.sql on the database server to generate the larger AWR report

21) Drilling down more on the data points

22) You can also focus just on specific events. Let’s say just the CPU..

23) or the event that’s hogging most of the resources

24) and just select the data points from that bottleneck

25) Copy the summary info

26) Then play around with the data in command line

The screenshots above just makes sense of the SNAP_ID for the time series data. After a little more AWR data hacking I was able to make sense of the date value which is really useful because I no longer have think of SNAP_IDs across instances and getting them aligned on a particular period… and the trick here is Tableau automatically creates a time dimension for the time column of my AWR scripts.  Kewlness  and that way I can stitch performance data across hostnames and consolidated instances of an entire Exadata Full Rack! and I’ll show how it is done on the next couple of posts.

Hope I’ve shared you some good stuff!

• CPU Wait (new metric in 11g Performance page)
• Latency issues on virtualized environments

So if you are attending the RMOUG training days, stop by at my session @ room 401 Thursday 9:45 am-10:45 am

There will also be a RAC Attack at the exhibition area so that must be fun

and don’t forget to follow RMOUG’s twitter @RMOUG_ORG for updates during the conference!

See yah!

Update: 

I had a great time at RMOUG, was able to meet old/new friends and had a good number of attendees. The organizers really did a good job  Here’s the updated PPT I used during the presentation.
That’s the most recent, the one on the RMOUG is not yet updated. For the scripts http://karlarao.wordpress.com/scripts-resources/ they are all at the “PerformanceAndTroubleshooting” folder

Thanks to all who attended my presentation, I know that’s a bit too much technical stuff for 1hour presentation squeezing all 101 slides and all that info. The best way is to go through my notes on the slides, check out all the links I pointed, and try to poke around on the scripts. And I assure you will learn tons of stuff & greatly appreciate the material  :)

SQLTXPLAIN:

I’ve been using SQLTXPLAIN for quite a while, and I can say I’m a really big fan. It’s a cool tool to use for systematic SQL Tuning so I got them all neatly documented here http://karlarao.tiddlyspot.com/#SQLTXPLAIN.

BTW, Carlos Sierra, the author of the tool will present at Hotsos 2012 http://www.hotsos.com/sym12/sym_speakers_sierra.html so I may drop by the conference and say hello

The wiki link will probably show you all the things you need to know about SQLTXPLAIN. My personal use case favorites would be the following:

• Mining 10053 output
  • On the execution plan view of the main HTML report whenever you hover on each step of the plan it will pop up an info about the Query Block (QB) details which you can mine on the raw 10053 text file that is also on the zip file. This is helpful when troubleshooting the specific part of the SQL where it has gone wrong.
• Instant SQL Monitor output
  • The HTML and text versions of SQL Monitor is readily available for the particular SQL_ID you are troubleshooting
• Compare good/bad plan of SQLs
  • This is pretty handy because the output report of the SQLT-compare highlights the difference in red text. So instead of chasing some delta numbers, you can just go straight to the red text!
• Fix bad stats
  • Let’s say you have a prod and dev that have completely different run times. And you found out that the problem is the stats, you can do a SQLT on the good plan and transfer the stats to the other environment. You will be using the sqltimp.sql for this
• Transfer stored outline/profiles
  • This makes use of exp/imp to transfer and outline + DBMS_SQLTUNE for the SQL Profile
  • Manual SQL Profile is readily available as sqlt_<sqlt_id>_sqlprof.sql or can be created using sqlt/utl/coe_xfr_sql_profile.sql
• Reproducing an execution plan from one system to another and Creating test cases
  • I usually pull SQLT from the server I’m troubleshooting and load it on my laptop. And from there I can have the same optimizer environment then I can do anything I want with it. (well you can also do this from prod then load the SQLT to dev/QA environment)
  • I mainly use the SQLT-tc (test case builder) and that is totally different from the TCB (test case builder) which uses the DBMS_SQLDIAG.. wait, am I confusing you with the TCB terms?
    • Let me give you a little background

Each SQLTXPLAIN run (START sqltxtract.sql SQL_ID) packages everything in one zip file

[oracle@karl sqlt_s54491-good]$ du -sm sqlt_s54491.zip
 47 sqlt_s54491.zip

and inside that zip file includes the test case zip file that has a *_tc.zip extension (sqlt_s54491_tc.zip)
Note that this is different from the *_tcb.zip extension (sqlt_s54491_tcb.zip) which is using the DBMS_SQLDIAG TCB (test case builder) package

[oracle@karl sqlt_s54491-good]$ unzip -l sqlt_s54491.zip
 Archive: sqlt_s54491.zip
 Length Date Time Name
 --------- ---------- ----- ----
 104557695 11-27-2011 02:36 sqlt_s54491_10053_explain.trc
 2587 11-27-2011 09:18 sqlt_s54491_driver.zip
 660035 11-27-2011 02:35 sqlt_s54491_lite.html
 28711 11-27-2011 09:18 sqlt_s54491_log.zip
 34589692 11-27-2011 02:35 sqlt_s54491_main.html
 316568 11-27-2011 09:18 sqlt_s54491_opatch.zip
 40487 11-27-2011 02:35 sqlt_s54491_p73080644_sqlprof.sql
 21526 11-27-2011 02:35 sqlt_s54491_readme.html
 461504 11-27-2011 02:35 sqlt_s54491_sql_detail_active.html
 385239 11-27-2011 02:35 sqlt_s54491_sql_monitor_active.html
 424680 11-27-2011 02:35 sqlt_s54491_sql_monitor.html
 57213 11-27-2011 02:35 sqlt_s54491_sql_monitor.txt
 333117 11-27-2011 02:35 sqlt_s54491_sta_report_mem.txt
 1488 11-27-2011 02:35 sqlt_s54491_sta_script_mem.sql
 616556 11-27-2011 02:36 sqlt_s54491_tcb.zip         <-- ''DBMS_SQLDIAG TCB'' (test case builder) package zip file
 8659 11-27-2011 09:18 sqlt_s54491_tc_script.sql
 7793 11-27-2011 09:18 sqlt_s54491_tc_sql.sql
 4254816 11-27-2011 09:18 sqlt_s54491_tc.zip         <-- ''SQLT-tc test case'' zip file
 33308174 11-27-2011 09:18 sqlt_s54491_trc.zip
 --------- -------
 180076540 19 files

• And from the wiki, you will see the related wiki topics that starts with “testcase -”. Actually, you can generate test cases using the following methods:
  • by Manually reproducing the SQL
  • by using SQLT-tc 
  • by using the DBMS_SQLDIAG TCB (test case builder) package
  • by using the DBMS_SQLDIAG TCB from ADRCI

• And last but not the least, the most awesome part is 1) using the generated SQLT zip file, 2) building the testcase using SQLT-tc, and 3) then generating a DB Optimizer VST on the SQL_ID!
  • Right after the DB Optimizer http://dboptimizer.com/db-optimizer was released last 2010, and right after I got the tool for $99! http://www.freelists.org/post/oracle-l/Special-Offer-for-readers-of-OracleL I was chatting with Kyle Hailey about the possibilities of making a VST out of SQLTXPLAIN.. and all along I was right

DB Optimizer:

Below you will see that the highlighted table name starts with the schema TC<sqlt_id>, that means it was generated from SQLT-tc and I was also able to reproduce underlying objects of the SQL_ID and the optimizer environment on my laptop.

The cool thing about DB Optimizer is that it automates a lot of tedious tasks for you. It takes away the pain of manual diagramming just like what I’m doing here http://goo.gl/VZFB that turns out just look like this http://goo.gl/ttrh

I’m a very visual guy, and I can have a better understanding of the problem and solve things quickly (and creatively) by making use of visualization.

The highlighted table on the diagram below shows a missing join condition. This version of the SQL takes FOREVER!… here with DB Optimizer, right after it prints the VST diagram and correlating it with the “merge join cartesian” on the SQLT.. it’s pretty easy to identify the missing join (see highlighted SQL on the left) which is the root cause of the performance problem.

and a quick modification on the SQL made it run for just 10 seconds! that’s the power of SQLTXPLAIN and DB Optimizer VST 

You may also want to check out this ultimate SQL tune off by Kyle Hailey and Jonathan Lewis to know more about DB Optimizer http://dboptimizer.com/2010/06/14/jonathan-lewis-kyle-hailey-webinar-replay/

And my example usage of the tool on a high load SAP OLTP environment – DB Optimizer example – 3mins to 10secs

That’s it for now, hope I’ve shared you some good stuff

Update: 

I attended the 2nd day of Hotsos and I was able to meet Carlos, he’s a really nice guy. Randy Johnson, Carlos, and I had a great discussion about the tools we are working on and currently developing… we were like kids on a table with a laptop showing off each others work & that includes Jamey Johnston showing his performance monitoring tool from his server at home… I was able to show Carlos how the VST of DB Optimizer works and he was impressed by the power of easily slicing and dicing on the query visually. Of course we shared a couple of SQLTXPLAIN tips and tricks and war stories where the tool was really useful. And all of these stuff over beer and good food that was a fun Hotsos party

So I always make use of this script
http://karlarao.tiddlyspot.com/#%5B%5Bstep%20by%20step%20environment%5D%5D

and for every server that I access I just have to do three bits of typing:

. ~oracle/.karlenv <<– set the environment
2 <<– choose an instance
s <<– rlwrap'd sqlplus alias

see example usage below:

[root@desktopserver ~]# su - oracle
[oracle@desktopserver ~]$
[oracle@desktopserver ~]$ vi .karlenv      <-- copy the script from the "Install environment framework - karlenv" section of the wiki link above
[oracle@desktopserver ~]$
[oracle@desktopserver ~]$ ls -la | grep karl
-rw-r--r--  1 oracle dba   6071 Dec 14 15:58 .karlenv
[oracle@desktopserver ~]$
[oracle@desktopserver ~]$ . ~oracle/.karlenv      <-- set the environment
<HOME_LIST>
<HOME NAME="Ora11g_gridinfrahome1" LOC="/u01/app/11.2.0/grid" TYPE="O" IDX="1" CRS="true"/>
<HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="2"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>


 1-       +ASM
 2-         dw

Select the Oracle SID with given number [1]:
2      <-- choose an instance
Your profile configured for dw with information below:
The Oracle base has been set to /u01/app/oracle

ORACLE_SID=dw
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

oracle@desktopserver.local:/home/oracle:dw
$ s      <-- rlwrap'd sqlplus alias, also you can use the "s1" alias if you don't have rlwrap installed

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 5 15:41:15 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP and Real Application Testing options


USERNAME             INST_NAME    HOST_NAME                 SID   SERIAL#  VERSION    STARTED  SPID            OPID  CPID            SADDR            PADDR
-------------------- ------------ ------------------------- ----- -------- ---------- -------- --------------- ----- --------------- ---------------- ----------------
SYS                  dw           desktopserver.local       5     8993     11.2.0.3.0 20111219 27483           24    27480           00000000DFB78138 00000000DF8F9FA0


SQL> @gas      <-- calling one of Kerry's scripts from the /home/oracle/dba/scripts directory

 INST   SID PROG       USERNAME      SQL_ID         CHILD PLAN_HASH_VALUE        EXECS       AVG_ETIME SQL_TEXT                                  OSUSER                         MACHINE
----- ----- ---------- ------------- ------------- ------ --------------- ------------ --------------- ----------------------------------------- ------------------------------ -------------------------
    1     5 sqlplus@de SYS           bmyd05jjgkyz1      0        79376787            3         .003536 select a.inst_id inst, sid, substr(progra oracle                         desktopserver.local
    1   922 OMS        SYSMAN        2b064ybzkwf1y      0               0       50,515         .004947 BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2 oracle                         desktopserver.local

SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP and Real Application Testing options
oracle@desktopserver.local:/home/oracle:dw

BTW, parts of the code came from this blog by Coskan Gundogar http://goo.gl/cqRPK and I’ve edited/added some lines on the setsid and showsid sections making it suitable for most unix(solaris,aix)/linux environments

Also I’ve added lines of code before and after the setsid and showsid to get the following info:

• software homes installed

– very useful on troubleshooting if you have multiple homes (RDBMS/Grid Infra/EMGC)

• get DBA scripts location

– quickly plugin your script folders by editing the “# SCRIPTS LOCATION” section

• set alias

– quickly add alias by editing the “# ALIAS” section

Hope I’ve shared you some good stuff!

This simple query from the MGMT views will output something like this

SELECT t.target_name, t.target_type,
       substr(s.message,1,50) , substr(l.message,1,11) emailed,
       TO_CHAR(s.load_timestamp,'DD-MON-YYYY HH24:MI:SS') loaded,
       s.severity_guid,
       TO_CHAR(a.timestamp,'DD-MON-YYYY HH24:MI:SS') annotated,
       TO_CHAR(l.timestamp,'DD-MON-YYYY HH24:MI:SS') logged
FROM    mgmt_severity s, mgmt_targets t, mgmt_annotation a, mgmt_notification_log l
WHERE   
       s.target_guid     = t.target_guid
  AND  s.severity_guid   = a.source_obj_guid (+)
  AND  s.severity_guid   = l.source_obj_guid (+)
  -- AND a.annotation_type = 'NOTIFICATION'
  -- AND lower(s.message) like '%tablespace%percent%'
  -- AND lower(t.target_name) like '%mta%'
  -- AND s.severity_guid = 'B33A7C7DF7A0293CE040460A22EA5E84'
ORDER BY s.load_timestamp desc;

and with this data you can actually do filters.. lets say search for the tablespace alerts test cases that I created. This shows that it reached the metric threshold but it did not send the email because the “Emailed” column shows as as “null”

another thing you can do is have a query that shows monthly count of tablespace alerts that were sent or not sent..

select count(*), TO_DATE(loaded,'MON') loaded, emailed from (
SELECT t.target_name, t.target_type,
       substr(s.message,1,20) , substr(l.message,1,11) emailed,
       TO_CHAR(s.load_timestamp,'MON') loaded,
       s.severity_guid,
       TO_CHAR(a.timestamp,'DD-MON-YYYY HH24:MI:SS') annotated,
       TO_CHAR(l.timestamp,'DD-MON-YYYY HH24:MI:SS') logged
FROM    mgmt_severity s, mgmt_targets t, mgmt_annotation a, mgmt_notification_log l
WHERE   
       s.target_guid     = t.target_guid
  AND  s.severity_guid   = a.source_obj_guid (+)
  AND  s.severity_guid   = l.source_obj_guid (+)
  AND  lower(s.message) like '%tablespace%percent%'
ORDER BY s.load_timestamp desc
)
group by loaded, emailed
order by loaded desc;

and for sure there’s much more things you can do with all of these filters

— AND a.annotation_type = ‘NOTIFICATION’ <– annotated as NOTIFICATION
— AND lower(s.message) like ‘%tablespace%percent%’ <– search string on the alert message
— AND lower(t.target_name) like ‘%mta%’ <– search string on the target name
— AND s.severity_guid = ‘B33A7C7DF7A0293CE040460A22EA5E84′ <– search the alert ID

That’s it for now.. hope I’ve shared you some good stuff

Andy Colvin has some detailed reviews about the Oracle Database Appliance.. check out these links if you want to see the internals of the machine
http://blog.oracle-ninja.com/2011/09/inside-the-oracle-database-appliance-part-1/
http://blog.oracle-ninja.com/2011/09/oracle-announces-oracle-database-appliance/

But this post will walk you through the installation and configuration of the Oracle Database Appliance.. well all I can say.. at the end of the ODA installation, all I had was 8 screenshots and that’s it. Complete install of the Grid infrastructure, ASM diskgroups, RDBMS software, and a fully functional clustered database in just two hours. Compared to a similar recent RAC project, the installation alone required about 80 screenshots.. and took a couple of days.. and involved multiple teams in the IT group. That’s the wonder of the super simplified installation using the Oracle Appliance Manager or the OAK Configurator which you will see below:

And again all of that took just 2hours to have a 2node RAC..

Hope I’ve shared you some good stuff .. Keep posted for more ODA at Enkitec Blogs!

I was at the Virtual Room #100, and the staff earlier were really helpful and most of all I had fun doing that webinar.. and I get to “doodle” on a lot of my slides!

Overall, that was a fun first time experience

Check out the presentation slides below and the scripts here..

BTW, as per Tariq Farooq.. Everything is being recorded! and they will be “broken up” and uploaded within a week or so.. So I’ll be updating this post and include that recording once the video comes out

Hope I’ve shared you some good stuff

BTW, the Chapters 01 (What’s Exadata), 08 (Configuring Exadata), 14 (Disk Layout), 15 (Node Layout) of the Expert Oracle Exadata will help you familiarize with the ins-and-outs of the Exadata configuration.

On the other hand I’ve been sitting in this interesting Exadata class for the past few days aimed for the Developers and DBAs. Enkitec having a bunch of hands-on Exadata Experts created a course that focuses on optimizing the performance of Exadata and how to troubleshoot and write SQL to fully utilize the critical features and achieve the ultimate performance improvement.. This course is really a technical hands-on, it’s full of labs, test cases, and real world customer stories. And most of all the students get to play on Enkitec’s X2-2 and V2 environments sitting just across the classroom

Tim Fox and Randy Hardee are the primary instructors for this class and they’ve got a solid Exadata background from troubleshooting and tuning, benchmarks, POCs, and both of them have been involved on most of the 25+ Exadata implementations. Plus Kerry Osborne’s office is just a few steps away so interesting discussions and questions are all welcome

For the Summer/Fall 2011 Schedule and all of the details kindly visit this website. Also, by popular demand.. we will be putting up a class dedicated to Exadata Administration that will tackle the areas of provisioning/capacity planning/resource management, cell administration, backup and recovery, etc.

Below are some of the actions on the class:

So keep posted for new stuff on this URL –>

http://enkitec.com/education

I met Dave at Hotsos 2011 when he attended my presentation.. apparently we have the same interest about Statistics, Linear Regression, AWR, AAS and he’s got awesome blogs about it at http://aberdave.blogspot.com. Also being the Journal Editor at Northern California Oracle Users’s Group, he invited me to write a short article about the question:

Why is my database slow?

The NoCOUG Journal having a very wide readership with the last issue being downloaded around 6000 times from more than 200 countries would be a really good venue to share my performance troubleshooting principle which works every time!

C’mon, click the link.. and turn to page 10  =)

As I was searching for Exadata viewlets in Oracle By Example portal (http://goo.gl/HjshR) I clicked on the “Advanced Search”

and then filtered by Product Family (Database) and then by Product…

to my surprise there was an option for “Database 12g”….

oh well.. I checked on the Lifetime Support Policy (http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf) and looking at the release dates
it could be possible that they will release the new version anytime this year..

could be in Oracle Open World 2011? hmmm…

I like that presenters and attendees are curious about what each performance geek has to say..

I like that whenever their brains are already fried. They consume as much coffee/soda as they can.. and just lay their butts on this couch.. and still.. talk about performance.

I like that after all the sessions, they will head straight to the hotel’s bar.. drink beer.. and still.. talk about performance.

I like that you can get yourself into healthy debates, validate each other’s methods and work, and also possible to collaborate with some other geeks doing the same research.

I like that I had a blast on my presentation..

I like that I got a good number of attendees, good feedback, and good questions..

Below is the presentation slides BTW..

and below are the daily updates of the conference

Day1 http://goo.gl/H9rJ9

Day2 http://goo.gl/wLh4H

Day3 http://goo.gl/l74C8

Training Day http://goo.gl/cTXZq

So.. Definitely I’ll come back, and.. I’ll try to make a good research every a year, so I can go again for free!

Well this netbook has thrice the battery life (or more) and half the weight of my old laptop so I must say it’s really good for everyday use!

But as a DBA, I would be happier if I can run my VMware VMs on this lightweight lappie!

So coming from a 15″ laptop on Fedora OS, I’ve already installed the tools and all the software I need to be productive on this new machine. And then I’ve copied the VMware files.. then the last to be installed was the VMWare Server 2.0..

the installation went fine. But it took too long and I already had a feeling I’ll encounter performance issues when I start to boot even just one VM. And yes I was correct. Even a Windows XP VM won’t boot. It was just not working… that made me worry if the Atom n550 can’t just handle the virtualization. Or VMware (and its slow web console) was just not right…

Presenting Virtual Box

So I was already looking for an alternative virtualization software, but it would be nice if I don’t have to build my VMs from scratch.. because it will just take a lot of effort and time to do that. It would be nice if it can instantly read the same VMDK files without much modification (especially on networking/shared storage) and it should be less hassle making it work.  Well.. Virtual Box seems to be what I need! It supports the following: - VMDK files http://www.virtualbox.org/manual/ch05.html#vdidetails - Shareable hard disks for RAC http://www.virtualbox.org/manual/ch05.html#hdimagewrites - NAT networking http://www.virtualbox.org/manual/ch06.html After skimming through the documentation and accumulating some web links http://karlarao.tiddlyspot.com/#VirtualBox I’ve installed the Virtual Box, the installation was way faster than VMware and I really like the lightweight interface

The next sections will walk you through on how to make use of your existing VMware VMs on Virtual Box.

Just click on them to redirect you to the Wiki page…

VMware to Virtual Box – Single Instance Database on Linux

VMware to Virtual Box – RAC on Linux

VMware to Virtual Box – Windows XP

I can say.. VirtualBox is way faster than VMware on my netbook. I can even run two (2) node RAC!

I love VirtualBox.

Since my self study modules/materials are on RHEL5, and it would take a while for the RHEL6 exam to come out and of course to review on that version.. and it’s really one of my personal goals to be RHCE asap! why not just take the RHEL5 RHCE? so I inquired, the exam center said the last RHCE exam for RHEL5 would be this December. So immediately I reserved and paid for my slot (yeah my own money!).

I had 1 month to prepare to read all the books, references, take notes, do experimentations, and simulate some critical exam scenarios. The catch is I have to do all of these on top of work time.. so I’ve got other stuffs to do like being engaged on pre-sales activities, creating work plans, doing some storage migration, doing performance tuning reports, attending a Golden Gate training, to name a few.. plus I got sick for 1 week, then here goes the Christmas parties which is too bad that I have to avoid all the fun and alcohol because of the much needed brain cellzzz.. Then even on the night before the exam I was cramming too much to make the email services work on my test environment. So yeah the preparation was a wild ride and I had lots of sleepless nights making sure that I’ll be able to do/simulate what I learn in theory.

Last December 18 I took the RHCE (it’s a hands-on performance based/situational exam).. I was really confident on the exam proper up to the finishing touches on my exam machine. When I came out of the exam center I really felt relieved because all the sacrifices are over!

After waiting for 3 days for the results.. I’m happy to be tweeting this

Wheeew whatta relief, I am now a RedHat Certified Engineer! the exam was challenging, all the sleepless nights paid off http://goo.gl/Qpd7Z

What’s even more surprising for me is when I was browsing on Red Hat Certified Engineer – Philippines Facebook page, I noticed that the last recorded RHCE passer (announcement) was last March 20, 2010.. cool hmm but I still have to verify this..

Thanks for all the congratulations, and to my loved ones, family, friends who supported and helped me I love you all!

• Application Testing
• Benchmark
• Capacity Planning
• Database Tuning
• Hardware and OS
• Performance Tools
• SQL Tuning
• Troubleshooting & Internals

Yesterday, I received an email that I have been accepted as a presenter for Hotsos 2011 this is really cool .. !

My presentation title is Mining the AWR Repository for Capacity Planning, Visualization, and Other Real World Stuff

I’ve been researching about the area of Capacity Planning and Mining the AWR for quite a while. And I was able to create some useful scripts along the way plus I was able to present the idea on the recent Oracle Open World Unconference and Oracle Closed World, and I had very good feedback. So I’ll be sharing this again and some other things as I go on with my research but this time to a larger geeky audience.

Also it’s really good to know that the following speakers will also talk about how to make better use of the AWR repository.. I just wish any of these sessions will not conflict with my speaking slot so I could watch all of them

Dan Fink – Variance Analysis, Profiling and Trending Using ASH and AWR Data
Henry Poras – Determining Resource Utilization and Saturation Limits in a Multi-User, Mixed Workload Environment
Tim Gorman – Forensic Analysis using AWR and ASH
Wolfgang Breitling – Time Series Analysis Techniques for Statspack and AWR

So that’s all about it.. that really made my day.. I’m really happy and excited about this conference.. I submitted my abstract super late already but still it was chosen. Cool!

BTW.. as a speaker I’m entitled to attend the training day of Karen Morton for free and the topic is Managing SQL Performance – Practical Information and Tools for Writing and Maintaining Optimally Performing SQL

And the party must be fun!

… Dallas here I come!!!
 
 
 
 

It can’t even boot to BIOS, certainly a case worse than BSOD.

So after fiddling with the laptop and systematically ruling out other component failures (power cable,monitor,memory,HD), Yes it’s much like troubleshooting an Oracle database! … we decided to bring it to the service center.

But wait! it may take too long to repair the machine & my precious data (wiki,photos,research,downloads,VMs,scripts) is still on the 2.5 hard disk.. So I pulled the disk, then I bought first a sata enclosure at Park Square, now I can plug the disk to another machine (Linux box) so I can get my data. Apparently the only available at that time was my R&D server running on RHEL5.4 64bit.

But I had a problem… all of my data is on LVM (logical volume) with a VG (volume group) name “vgsystem” that has the same name as my VG on the R&D server. So a pvscan errors with a “WARNING: Duplicate VG name”

So I had to rename first the “vgsystem” VG on the R&D server to “vgsystem1″ before I can activate the VG on my 2.5HD.

BTW, I had the following LVs (Logical Volumes) on the R&D server so I need to unmount them all first to be able to rename to “vgsystem1″

[root@beast dev]# ls -l /dev/vgsystem
total 0
lrwxrwxrwx 1 root root 28 Oct  7 14:21 lvhome -> /dev/mapper/vgsystem-lvhome
lrwxrwxrwx 1 root root 27 Oct  7 14:21 lvtmp -> /dev/mapper/vgsystem-lvtmp
lrwxrwxrwx 1 root root 27 Oct  7 14:21 lvu01 -> /dev/mapper/vgsystem-lvu01
lrwxrwxrwx 1 root root 27 Oct  7 14:21 lvusr -> /dev/mapper/vgsystem-lvusr
lrwxrwxrwx 1 root root 27 Oct  7 14:21 lvvar -> /dev/mapper/vgsystem-lvvar

Here are the steps I did to rename the “vgsystem” to “vgsystem1″

1) Edit the /etc/fstab entries of the LVs, comment on them so they will not be detected on boot. Anyway my root is on a separate ext3 partition and I’ll be doing the rename on runlevel1 so it’s all good. But here’s the catch, since I will be unmounting the /usr filesystem the binaries used for the VG rename (/usr/sbin/vgchange, /usr/sbin/vgrename) is not available so I must use of the /sbin/lvm.static (part of LVM tools) for the rename operation
2) Reboot the server, enter runlevel1
3) Execute the commands
# /sbin/lvm.static help
# /sbin/lvm.static vgchange -an
4) Execute the command
# /sbin/lvm.static vgrename vgsystem vgsystem1
5) Execute the command
# /sbin/lvm.static vgchange -ay
6) Edit the /etc/fstab, uncomment the LVs then replace the entries of vgsystem to vgsystem1
7) Reboot, enter runlevel5

Now let’s mount the 2.5HD and get the data!

1) Scan all PVs (physical volume), notice the vgsystem (from my 2.5HD) and vgsystem1 (from my R&D server)

[root@beast ~]# pvscan
  PV /dev/sdf3    VG vgsystem    lvm2 [146.80 GB / 0    free]
  PV /dev/sda5    VG vgsystem1   lvm2 [99.97 GB / 0    free]
  PV /dev/sda6    VG vgsystem1   lvm2 [99.97 GB / 0    free]
  PV /dev/sda10   VG vgsystem1   lvm2 [95.34 GB / 0    free]
  PV /dev/sda11   VG vgsystem1   lvm2 [95.34 GB / 0    free]
  PV /dev/sda12   VG vgsystem1   lvm2 [95.34 GB / 0    free]
  Total: 6 [632.77 GB] / in use: 6 [632.77 GB] / in no VG: 0 [0   ]

2) Activate the Logical Volume on the 2.5HD

[root@beast ~]# lvchange -ay vgsystem

3) Check on the PVs, notice that we both have PVs from vgsystem and vgsystem1

[root@beast ~]# pvdisplay 
  --- Physical volume ---
  PV Name               /dev/sdf3
  VG Name               vgsystem
  PV Size               146.80 GB / not usable 3.31 MB
  Allocatable           yes (but full)
  PE Size (KByte)       4096
  Total PE              37581
  Free PE               0
  Allocated PE          37581
  PV UUID               Ut6sSm-uCXd-h1Mi-402u-3QoU-IQQQ-wgOljt
   
  --- Physical volume ---
  PV Name               /dev/sda5
  VG Name               vgsystem1
  PV Size               100.00 GB / not usable 30.66 MB
  Allocatable           yes (but full)
  PE Size (KByte)       32768
  Total PE              3199
  Free PE               0
  Allocated PE          3199
  PV UUID               eMdH4e-POuO-0tDy-LWVs-8lnW-oF0N-ikPxGJ
   
  --- Physical volume ---
  PV Name               /dev/sda6
  VG Name               vgsystem1
  PV Size               100.00 GB / not usable 30.66 MB
  Allocatable           yes (but full)
  PE Size (KByte)       32768
  Total PE              3199
  Free PE               0
  Allocated PE          3199
  PV UUID               ia3nKZ-Ldyr-3zmz-OD2d-Es9P-lzDm-rfrhuz
   
  --- Physical volume ---
  PV Name               /dev/sda10
  VG Name               vgsystem1
  PV Size               95.37 GB / not usable 28.74 MB
  Allocatable           yes (but full)
  PE Size (KByte)       32768
  Total PE              3051
  Free PE               0
  Allocated PE          3051
  PV UUID               rgeD2t-TLEh-DRWn-hdiC-5Sio-pczW-st19Yg
   
  --- Physical volume ---
  PV Name               /dev/sda11
  VG Name               vgsystem1
  PV Size               95.37 GB / not usable 28.74 MB
  Allocatable           yes (but full)
  PE Size (KByte)       32768
  Total PE              3051
  Free PE               0
  Allocated PE          3051
  PV UUID               S2wtrJ-Bw60-VcCo-eE9X-QopS-bac2-3REU9c
   
  --- Physical volume ---
  PV Name               /dev/sda12
  VG Name               vgsystem1
  PV Size               95.37 GB / not usable 28.74 MB
  Allocatable           yes (but full)
  PE Size (KByte)       32768
  Total PE              3051
  Free PE               0
  Allocated PE          3051
  PV UUID               VeBQCq-06XK-02lI-3gP5-nuRw-D0n1-RSdNO4

4) Check on the VGs, notice that we both have VGs from vgsystem and vgsystem1

[root@beast ~]# vgdisplay 
  --- Volume group ---
  VG Name               vgsystem
  System ID             
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  2
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                1
  Open LV               0
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               146.80 GB
  PE Size               4.00 MB
  Total PE              37581
  Alloc PE / Size       37581 / 146.80 GB
  Free  PE / Size       0 / 0   
  VG UUID               v4ler4-aC9L-7ugQ-m9yQ-adYK-rrcM-sRLCqD
   
  --- Volume group ---
  VG Name               vgsystem1
  System ID             
  Format                lvm2
  Metadata Areas        5
  Metadata Sequence No  11
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                5
  Open LV               5
  Max PV                0
  Cur PV                5
  Act PV                5
  VG Size               485.97 GB
  PE Size               32.00 MB
  Total PE              15551
  Alloc PE / Size       15551 / 485.97 GB
  Free  PE / Size       0 / 0   
  VG UUID               kvUydX-Abxb-k3LO-q9wq-jF13-dGLz-vG9JUy

5) Check on the LVs, notice that we both have LVs from vgsystem and vgsystem1

[root@beast ~]# lvdisplay 
  --- Logical volume ---
  LV Name                /dev/vgsystem/lvroot
  VG Name                vgsystem
  LV UUID                COUkrC-4ygb-fBQx-3VLr-3CdP-Hupd-InPAGW
  LV Write Access        read/write
  LV Status              available
  # open                 0
  LV Size                146.80 GB
  Current LE             37581
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:5
   
  --- Logical volume ---
  LV Name                /dev/vgsystem1/lvusr
  VG Name                vgsystem1
  LV UUID                UNyiOM-SvAZ-8KVl-fGbF-SvsB-h9FO-u3YLyq
  LV Write Access        read/write
  LV Status              available
  # open                 1
  LV Size                4.00 GB
  Current LE             128
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:0
   
  --- Logical volume ---
  LV Name                /dev/vgsystem1/lvhome
  VG Name                vgsystem1
  LV UUID                odNeAE-Krf6-90yn-fN6Q-BDyC-FeKk-U7QeOa
  LV Write Access        read/write
  LV Status              available
  # open                 1
  LV Size                9.75 GB
  Current LE             312
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:1
   
  --- Logical volume ---
  LV Name                /dev/vgsystem1/lvtmp
  VG Name                vgsystem1
  LV UUID                Uyvudz-OCTR-xJrQ-Iftz-CTa4-62XL-HOEN8o
  LV Write Access        read/write
  LV Status              available
  # open                 1
  LV Size                1.00 GB
  Current LE             32
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:2
   
  --- Logical volume ---
  LV Name                /dev/vgsystem1/lvvar
  VG Name                vgsystem1
  LV UUID                7uWs2z-hAzj-b3Aj-6KI8-Qaec-pKqd-15UQgk
  LV Write Access        read/write
  LV Status              available
  # open                 1
  LV Size                1.00 GB
  Current LE             32
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:3
   
  --- Logical volume ---
  LV Name                /dev/vgsystem1/lvu01
  VG Name                vgsystem1
  LV UUID                mta43w-NEnF-QaAx-td4P-osHg-gOAt-B2KrC5
  LV Write Access        read/write
  LV Status              available
  # open                 1
  LV Size                470.22 GB
  Current LE             15047
  Segments               5
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:4

6) Now the device /dev/mapper/vgsystem-lvroot is ready to be mounted

[root@beast dev]# ls -l /dev/vgsystem
total 0
lrwxrwxrwx 1 root root 27 Oct  7 14:28 lvroot -> /dev/mapper/vgsystem-lvroot
[root@beast dev]# 
[root@beast dev]# ls -l /dev/vgsystem1
total 0
lrwxrwxrwx 1 root root 28 Oct  7 14:21 lvhome -> /dev/mapper/vgsystem1-lvhome
lrwxrwxrwx 1 root root 27 Oct  7 14:21 lvtmp -> /dev/mapper/vgsystem1-lvtmp
lrwxrwxrwx 1 root root 27 Oct  7 14:21 lvu01 -> /dev/mapper/vgsystem1-lvu01
lrwxrwxrwx 1 root root 27 Oct  7 14:21 lvusr -> /dev/mapper/vgsystem1-lvusr
lrwxrwxrwx 1 root root 27 Oct  7 14:21 lvvar -> /dev/mapper/vgsystem1-lvvar

7) Mount it baby!

[root@beast dev]# mount /dev/mapper/vgsystem-lvroot /mnt/usb/

8 ) Now I got my data

[root@beast ~]# ls -l /mnt/usb/home/karao/Documents/
total 32
drwx------  8 oracle oracle 4096 Oct  5 08:58 Desktop
drwx------  4 oracle oracle 4096 Sep 17 12:01 KnowledgeFiles
drwxrwxr-x  2 oracle oracle 4096 Jan 10  2010 My Music
drwxrwxr-x  7 oracle oracle 4096 Oct  5 09:03 My Pictures
drwxrwxr-x  3 oracle oracle 4096 Jan 11  2010 My Shapes
drwxrwxr-x  2 oracle oracle 4096 Jan 10  2010 My Videos
drwx------ 11 oracle oracle 4096 Sep  9 19:34 Softwares
drwxrwxrwt  9 oracle oracle 4096 Sep 28 23:29 VirtualMachines

Then I just plugged in my 1TB NTFS drive (I have NTFS-3G on the server) and copied all my files.

9) After copying it’s just a normal unmount operation but you also have to deactivate the Logical Volume

[root@beast ~]# umount /mnt/usb
[root@beast ~]# lvchange -an vgsystem

If you missed copying some files you can just plug again the 2.5HD and reexecute the mounting/unmounting process.

BTW I’m using my old 17inch HP Pavilion laptop which is already 5+ years old but still on good condition.

Hope I’ve shared you some good stuff

Last Monday (Sept. 20) I was able to present at the Oracle Closed World @ Thirsty Bear. The full agenda is here http://www.amiando.com/ocw.html?page=434169

I did my presentation after Tanel’s live hacking session. There were 30 (+-5) attendees and some of guys are also OakTable members, that was the first time I presented my material to such a geeky audience (that is drinking beer at the same time) and I was sure they’ll love what I have to share. So I pulled it off (although a bit nervous), and at the end of the session I’ve got some questions + nice comments from the guys. Especially from Mark Farnham where we had a small discussion about how interesting it would be to have a multiple regression of AAS to the different subsystems (cpu,memory,storage,network) plus having the facility to identify their cross sections. It was truly an awesome “meat.beer.explode” experience, plus I’ve got this cool shirt!

My Oracle Unconference presentation covers the same theme but has more details and scenarios. It was the time where Larry was about to do his second “Keynote” so I was expecting few attendees but I had a great time too and happy to show how to use the Perfsheet to one of the attendees (for his reporting purposes) plus a little discussion about the problems he’s having around his environment.

To all the guys that attended my session, thanks a lot! and I hope I’ve shared you some good stuff  :)

Then we went to Redwood Shores to check out the Oracle office

Then headed back to the Moscone area to register and attend the sessions

On the way to the 3rd floor to get the conference materials I saw a familiar face.. yeah it’s my friend Craig Shallahamer! finally we’ve met.. and we had a long talk & catching up about performance related stuff before his talk about troubleshooting “free buffer waits” at 3pm

On the “RAC customer panel” I saw Alex Gorbachev, Kai Yu, Philip Newlan and some of the RAC Pack, also Jeremy Schneider attending the session. After that we went straight to Larry Ellison’s keynote which is mainly about “Exalogic” – the very very very fast integrated middleware machine

Unfortunately I missed the fun on the Oracle ACE dinner at Pier 40, I came out of the keynote too late! But I’ll drop by at the Mason Street Tent tomorrow to catch up with Lillian Buziak. That’s it for now.. I’m expecting more exciting stuff to happen in the coming days so keep posted!

So that would be:

• 13 conference sessions,
• 6 unconference,
• 4 users group,
• 3 meetups, and
• 1 panel,
• +1 Oracle Closed World.

You can check my detailed schedule below … (I know it’s a jam-packed sched! I hope I can go to all of them..)

This will be my first time attending the OOW and I’m sure I’ll be having a superb time I’m registered as a Blogger so I got the full conference pass for FREE. I’ll be updating this blog as much as I can with small write ups, photos & videos about the conference so keep posted.

Plus it will be my birthday on Sept. 21st so you can send/give your gifts to me by going to my unconference session on Sept. 22nd 2pm Mason at Hotel Parc 55, I’ll be talking about a topic that is very dear to my heart.. that is Mining the AWR repository and making use of it for Capacity Planning, Visualization, & other real world stuff.. you can check out the mind map of my presentation here http://sites.google.com/site/karlarao/mindmap/mining-the-awr-repository

And one more thing, I’m excited to finally meet the people I happen to chat and exchange ideas that made me learn a lot of technical stuff that really helped me grow as a DBA for the past years. Expect a fan picture from me and a fan photo album coming :p

– SUNDAY
[UG] S315266,September 19,12:30,13:30,Moscone West L2 / Rm 2004,IOUG: Capacity Management for the Oracle Exadata V2
[UG] S313207,September 19,13:30,14:30,Moscone West L2 / Rm 2004,Near-Zero-Downtime Oracle Database Migration
[UG] S316246,September 19,15:00,16:00,Moscone West L2 / Rm 2004,IOUG: Testing to Destruction–Part 2
[UG] S318709,September 19,16:00,17:00,Moscone West L2 / Rm 2001,IOUG: Oracle RAC Customer Panel
[KEY] S319177 Sunday, September 19, 17:30 | Moscone North, Hall D – Oracle Welcome Keynote – 120 min.
[meetup] 18:30 – 23:00 Annual Oracle ACE Dinner, Commodore Cruises, Pier 40, 7:00-11:00pm

– MONDAY
[KEY] S319178 Monday, September 20, 08:00 | Moscone North, Hall D – Oracle OpenWorld Keynote – 105 min.
[unconference] 11am Lombard:Optimizing Internal Serialization Control by Craig Shallahamer
[CS] S316157 12:30-13:30 Moscone South, Rm 308 Implementing Oracle Exadata: An IBM DB2-to-Oracle Exadata Case Study
[CS] S317164,September 20,14:00,15:00,Moscone South / Rm 104,The Latest Real World Performance Challenges
[CS] S317078,September 20,15:30,16:30,Moscone South / Rm 308,Extreme Consolidation with Oracle RAC One Node OR Do 5minutes presentation at Oracle Closed World (http://www.amiando.com/ocw.html?page=434167) about Graphing the AAS &/or Linear Regression
[CS] S315110 17:00-18:00 Moscone South, Rm 303 Optimizing Servers for Oracle Database Performance”
[KEY] S319179 Monday, September 20, 17:45 | Moscone North, Hall D – JavaOne Keynote – 90 min.
[meetup] 19:30 – 22:30 – OTN Night Party, Mason Street Tent, 7:30-10:30pm

– TUESDAY (my 26th birthday!)
[KEY] S319180 Tuesday, September 21, 08:00 | Moscone North, Hall D – Oracle OpenWorld Keynote 105 min.
[unconference] 9am Mason: Developer Coding Strategies for Very Large Databases with Daniel Morgan [Oracle ACE Director]
[unconference] 10am Mason: Oracle Indexes: Q & A Session with Richard Foote [Oracle ACE Director]
[unconference] 11:00 – Lombard: Do-It-Yourself Exadata-like Performance? Kevin Closson, Performance Architect, Systems Technology Group, Oracle.
[CS] S315449,September 21,12:30,13:30,Moscone South / Rm 306,Thinking Clearly About Performance
[CS] S317165,September 21,14:00,15:00,Moscone South / Rm 306,Oracle Database Performance Secrets Finally Revealed
[PAN] S317090,September 21,15:30,16:30,Moscone South / Rm 308,Oracle RAC on Sun Oracle Database Machine Customer Panel
[KEY] S319181 Tuesday, September 21, 16:30 | Hilton San Francisco, Grand Ballroom AB – Oracle Develop Keynote – Tom Kyte – 60min
[CS] S319101,September 21,17:00,18:00,Moscone South / Rm 304,Managing Transactions with Disappearing Indexes

– WEDNESDAY
[KEY] S319184 Wednesday, September 22, 08:00 | Hilton San Francisco, Grand Ballroom AB – Oracle Develop Keynote 90min
[KEY] S319182 Wednesday, September 22, 08:00 | Moscone North, Hall D – Oracle OpenWorld Keynote 45min.
[unconference] 11am Lombard: Everything A DBA Should Know About TCP/IP Networks – Oracle ACE Gwen (Chen) Shapira
[unconference] 1pm Lombard:Evaluating the Anticipated Effect of Performance Solutions by Craig Shallahamer
[unconference] 2pm Mason: Mining the AWR repository for Capacity Planning, Visualization, & other real world stuff by Karl Arao
[KEY] S319183 15:15-16:15 Moscone North, Hall D Oracle Keynote – Larry Ellison – Database” Moscone North, Hall D Oracle Keynote – Larry Ellison – Database
[CS] S317367 16:45-17:45 Moscone West L3, Rm 3020 Turkcell Transforms Its Business with Oracle Data Integrator and Oracle Exadata”
[meetup] 17:30 – 19:30 – Wednesday, Sep 22: – Blogger Meetup (organized by ACE Director Alex Gorbachev), Jillian’s, 4th Street, 5:30-7:30pm

– THURSDAY
[KEY] S319185 09:00-10:30 Hilton San Francisco, Grand Ballroom AB – JavaOne Keynote: The Java Frontier
[CS] S317114 09:00-10:00 Moscone South, Rm 103 What Else Can I Do with System and Session Performance Data?
[CS] S317103 10:30-11:30 Moscone South, Rm 300 Online Application Upgrade of Oracle’s Bug DB with Edition-Based Redefinition
[CS] S313301 12:00-13:00 Moscone South, Rm 307 Calisthenics for DBAs
[CS] S315724 13:30-14:30 Moscone South, Rm 302 Quantifying Oracle Database System Performance
[CS] S316417 15:00-16:00 Moscone South, Rm 307 When Oracle Databases Meet the Cloud, It Might Rain

http://3quarksdaily.blogs.com/3quarksdaily/2008/09/useless-calcula.html

In the Oracle side… we also use this back-of-the-envelope-calculations for coming up with man-days, capacity planning, hardware recommendations, or just simply knowing what’s happening. And I like these phrases from the article…

“But how can we know the actual figure? We cannot. We can only get closer and closer approximations by measuring things more and more accurately (the volume, not just of the building, but of everything in it, which must be subtracted). It’s not like there’s an easy way to pour the air out of the building and weigh it!
The fun in doing these estimates is in NOT looking anything up, and instead trying to answer questions by using, along the way, what we do know to estimate everything we need to know to answer our question.”

Which means if you are building a mission critical system.. the goodness of the guesstimates is determined by the quality of the sizing requirements and how detailed it could be. And it is impossible to build good systems without this information which may result in being oversized (money loss) or undersized (customer loss).

I also like this comment from Greg Rahn (http://goo.gl/gFNO)

“The problem I see with IT shops and planning is there is there is either no planning, or it is done incorrectly. Very few shops take the time to build and test, it is more like build and deploy, then “tune”. Let me put this into a slightly different context: If you were tasked with building a bridge from point A to point B what information would you need? Would things like amount of traffic, both peak and average, type of traffic: train, bus, trucks, cars, bicycle & foot be important? It would to me. I see IT shops that design a foot bridge (because it is cheaper) and then try and put cars onto it and then it falls down and they complain. Capacity planning and building computer systems is not about guessing, it is about engineering and calculations. That being said, what calculations go into your system design?”

How about you? Do you have any interesting Oracle Guesstimations?

Having this info made it easier for me to notice trends (text or visualization) and play around with the data (some statistics out of it)… which I can say a big help on finding the root cause of the problem (here and here)

Before going any further… I recommend you read first Kyle Hailey’s slide about AAS (Average Active Sessions).

For me, AAS has become my default (golden) metric on finding the periods where the database could be having a bottleneck or just idle.. yes, the slide I mentioned above was right on this.. AAS could be your “stethoscope” but it doesn’t stop there. For it to be more useful you must be aware about the components of AAS (much like drilling down on the time components) and have this kind of data over a period of time (across SNAP_IDs). Well Enterprise Manager does this nice graphs and slicing the AAS components to different wait classes and it’s got a “historical” view which you could go back and drill down on the past load activity.

What could be the problem?

I know some of you have encountered this at some point.. you are configured with a long AWR retention period (365 days to exaggerate it) but Enterprise Manager won’t let you go back farther.. all because there was an instance shutdown between the date you want to go and the date you are now. Or could be some other issue where Enterprise Manager really can’t just give you the visualization you need.

So what could be the alternative?

1) Use the Top Timed Events SQL (awr_topevents.sql) listed on the Scripts Section and focus on the AAS and wait class columns across SNAP_IDs
2) Or use the script together with Perfsheet! … a great tool for ad-hoc performance visualization (see some examples here, here, here, here)

IMPORTANT NOTE: Diagnostic Pack License is needed for the scripts

Now time for Perfsheet a la Enterprise Manager!

We will focus on the sudden spike that happened around 6:20 to 7:01 AM (SNAP_ID 335-339) as shown on this previous blog post or you could just check the highlighted black & green output below where the AAS is on the range of 2.2 to 3.5 (SNAP_ID 335-339) then on the right side of it is a stacked area chart of the awr_topevents.sql using Perfsheet. It’s clear from the image that there’s a big spike on the database load… but we want to know more about it by drilling down on the AAS components.

awr_genwl.sql output	Stacked area chart of AAS

Some more background

On the Enterprise Manager Performance and Top Activity Page you’ll see the AAS components are sliced into different wait classes. But, did you know that their data sources are different?

From the 2nd slide of this presentation AAS (Average Active Sessions) it says that there are two ways to calculate AAS…
1) Time Statistics
2) Sampling…

AAS on the Performance Page uses Time Statistics and is actually from v$system_event + CPU from time model. This is also what the script awr_topevents.sql is doing… it unions the output of v$system_event and the CPU from time model and then filter only the top 5 and do this across the SNAP_IDs but for graphing purposes on the Perfsheet to make it look similar to the Enterprise Manager Performance Page I have to include all of the events so that all the AAS values will be counted. BTW, on 10g below the load chart is coming from v$system_event + v$sysstat “CPU used by this session”.

AAS on the Top Activity Page uses Sampling and by default is taking advantage of ASH (samples) and does it on a 15sec refresh rate (you’ll see this proof on the Top Activity Perfsheet graph below)… but as I have observed when you switch from the Real Time 15 sec refresh to Historical then it also starts to behave like the Performance Page (pulls data from v$system_event + CPU from time model).

So what’s the effect? mm… on a high CPU activity period you’ll notice that there will be a higher AAS on the Top Activity Page compared to Performance Page. Simply because ASH samples every second and it does that quickly on every active session (the only way to see CPU usage realtime) while the time model CPU although it updates quicker (5secs I think) than v$sysstat “CPU used by this session” there could still be some lag time and it will still be based on Time Statistics (one of two ways to calculate AAS) which could be affected by averages.

If you want more info about the stuff around the Performance and Top Activity page.. this is worth reading.. History of Session Load

Going back in time – Historical view

Performance Page – Historical	Top Activity Page – Historical

Now back to the Perfsheet!  Check out the Top Activity Page – Historical above.. I have made a Perfsheet version of that graph which you could see below, on the left is broken down into wait class which really looks the same as the Top Activity Page (above) even their AAS values. While on the right is broken down into wait events.. aside from being more colorful it let’s you see what wait event is consuming the AAS.

Stacked area chart AAS components – wait class	Stacked area chart AAS components – wait events

Ooops, don’t get too excited.. important reminder… the 2-dimensional Stacked area chart that Enterprise Manager uses could hide important information and sometimes could be misleading.. and it really helps to have another view and see the data clearly separated into their respective components, rather than being stacked… As you compare the above and below charts, you’ll know what I mean.. Wait Class and Wait Events in 3D area chart view could tell a more meaningful story.

Compare the wait class chart… above notice the blue (Other wait class) on the range of AAS of 1 while below it’s on the range of 0.11 (hidden between CPU and System IO)… that’s a big difference!

Then compare the wait event chart… notice the big difference on the chart? above you can’t really tell what’s happening.. but on 3D you can see that only the db file sequential read and direct path read are on the AAS of 1.6 on SNAP_ID 335 and 336. Yes, you will also not be fooled when you look at the raw data… but visualization is much easier and the way to go but you must be able to sense and validate if it’s driving you to bad conclusions.

BTW, the Perfsheet and the raw data that I used are uploaded here Perfsheet-AAS.zip

3D area chart AAS components – wait class	3D area chart AAS components – wait events

AAS based on ASH – Real Time

As I have mentioned above, the Top Activity page charts the ASH samples on 15sec refresh rate. You can see below that doing this on Perfsheet will give us the same visualization, yes I’m querying v$active_session_history and this was also mentioned here

Top Activity Page – Real Time	Stacked area chart AAS wait class – Real Time

AAS throughout the AWR retention period!

On my test machine I have 365 days retention period.. this enables me to have a data warehouse of performance data. You can see from the chart above (stacked area chart), that what we are focusing on (6:20 to 7:01 AM SNAP_ID 335-339) happens to be the highest load period from all the AAS samples for the lifetime of my test database. You could also see the period of shutdowns (negative value) and other time period where AAS went beyond my maximum CPU which could justify the drill down on the specific SNAP_IDs or time frame… from there you could either use ASH, run the AWR report, run ADDM, or make use of your high caliber scripts!

The good thing here is, you are not guessing!

Hope I’ve shared you some good stuff

The comment made me curious about the effect of snap intervals on the performance numbers of the datafiles and block devices.. especially on the latency numbers.. so I made a few test cases and created some scripts that would give me 5 seconds, 10 minutes, and 60 minutes output of latency numbers on the database. Also running 5 seconds interval of OSWatcher to give me a view on the block devices.

As I was doing all of this, I had an interesting discovery about how the latency output of the datafiles are being computed and I was able to quantify by having the performance numbers how average could be misleading and mask the problem on the datafile IO latency.

I did a one-take (amateur ) clip straight from my iPhone3gs to give you an overview about it..

On the next post I will detail on the following:
- where and how to get datafile IO latency
- how is it computed
- how does the long average can affect the latency output
- how does this affect performance tuning?
- what can you do about it?

First is titled Performance: The “Not a Problem” Problem which I could also relate when doing performance analysis for example…

• When a client instantly jump into conclusion that the performance degradation is caused by the database link well in reality when everything is measured/profiled it turned out that it’s because of the slow IO subsystem..
• Or when the Data Guard environment was being blamed because of the slow batch processing but when everything is measured/profiled it turned out that it’s the bad SQL..

there are lots of interesting customer scenarios we could imagine.. but if there would be a systematic approach, a clear problem definition, and measuring the right stuff.. then things would be better..

Second is titled Performance: Experimentation which highlights the importance of the test cases and isolating the problem.. and how this could make a good learning experience while also solving the problem..

Third is the series of videos titled “Little Shop of Performance Horrors” posted on this blog post and tackled about these horrific topics:

• The worst perf issues I’ve ever seen!
• Common misconfigurations
• The encyclopedia of poor assumptions
• Unbelievably bad perf analysis
• Death by complexity
• Bad benchmarking
• Misleading analysis tools
• Insane performance tuning
• The curse of the unexpected

I hope I’ve shared you some good stuff Enjoy!

“New CPUs and storage arrays, they are getting faster but these resources are finite and come at a cost. Hence, capacity planning plays a very important role to ensure proper resources are available to handle expected and unexpected workloads. Another critical matter for the DBAs and IT managers is justifying the expense of adding resources on the system. With guesswork you’ll end up getting the most expensive hardware. With proper measurement, proper planning and management of growth, you’ll be able to get just the right hardware for your workload with allowance for a particular growth period. This will result to huge savings for the company and a happier IT shop.

AWR is a built-in data store that started in 10gR1 and it’s very much like a “Statspack on steroids”. It has improved significantly in 11gR2. What’s exciting about 10g and higher versions is we could utilize the “Time Model” and the “OSSTAT” view and use them together with the “SYSTAT” view to have a far better workload information when going through all the AWR snapshots.

From the AWR data samples, we could build amazing reports that will let us notice trends and makes it possible to visualize data and use statistical methods for analysis. Even more surprising about the AWR data samples is we are able to define the database server’s Capacity, Requirements, and Utilization in terms of CPU, IO, memory, and network which are very important key metrics for Capacity Planning.

In this session, attendees will be able to learn how to make use of the AWR specifically the DBA_HIST tables to have a clear-cut measurement on resources to aid in Capacity Planning, Predictive Analysis, and Performance Firefighting.”

it is really cool I’m telling you… c’mon, just click on the VOTE button

But the real highligth for me is having my scripts run on the database machine

BTW, to get the scripts.. check out the Scripts Section for the file name and the most recent version of the script..

IMPORTANT NOTE: Diagnostic Pack License is needed for the scripts

All the time they were doing demos they were just showing some graphed output of V$SYSSTAT statistics related to Exadata cell effectiveness and  a customized output of collectl showing the storage servers and the RAC nodes stacked together on a putty session and from there we can see the IO, CPU, network statistics as they were hammering the full rack Exadata..

So I was also really curious about seeing the database performance numbers.. I’ve asked if we can see the Enterprise Manager as they were doing the demos.. Unfortunately it was not available..

With the spooled output of my scripts (executed on the node where the demo was done).. I was able to see the following across SNAP_IDs:

- Load (awr_genwl.sql)
- Top Timed Events (awr_topevents.sql)
- Tablespace and File IO statistics (awr_io_ts.sql, awr_io_file.sql)
- Top SQLs (awr_topsql.sql, awr_topsqlx.sql)

Below are the output:

Load:

I was interested on the period when the demo was run (May 20th afternoon)… the time that I saw the storage servers being hammered and giving 1 GB/s throughput on each. I’d like to see CPU and IO numbers on the database side plus the AAS (Average Active Sessions) and other metrics.. more like characterizing the workload.. you can see on SNAP_ID 1211 that there was a sudden surge on DB Time, IO read MB/s, and CPU utilization..

I was surprised to see that the utilization numbers (rightmost) are very low even for the whole AWR retention period (I did not include here), and take note that this is the output from one node only so I can’t really conclude what’s happening to the entire cluster..

                                                                                                              AWR CPU and IO Workload Report

                         i                        ***                                                       ***                 ***
                         n                      Total                                                     Total               Total                                                                                                           U    S
       Snap              s       Snap   C         CPU                                             A      Oracle                  OS   Physical                                                                            Oracle RMAN   OS    S    Y    I
  Snap Start             t        Dur   P        Time         DB        DB        Bg     RMAN     A         CPU      OS         CPU     Memory      IOPs      IOPs      IOPs      IO r      IO w      Redo           Exec    CPU  CPU  CPU    R    S    O
    ID Time              #        (m)   U         (s)       Time       CPU       CPU      CPU     S         (s)    Load         (s)       (mb)         r         w      redo    (mb)/s    (mb)/s    (mb)/s Sess        /s      %    %    %    %    %    %
------ --------------- --- ---------- --- ----------- ---------- --------- --------- -------- ----- ----------- ------- ----------- ---------- --------- --------- --------- --------- --------- --------- ---- --------- ------ ---- ---- ---- ---- ----

  1208 10/05/20 08:00    2      60.25   8    28920.00      24.08      8.43     10.02     0.00   0.0       18.44    0.29      334.80   32189.62     0.000     0.082     0.050     0.000     0.001     0.000   40     1.549      0    0    1    1    0    0
  1209 10/05/20 09:00    2      59.89   8    28747.20      21.80      8.43     10.29     0.00   0.0       18.72    0.40      320.49   32189.62     0.003     0.078     0.034     0.000     0.002     0.000   40     1.490      0    0    1    1    0    0
  1210 10/05/20 10:00    2      59.87   8    28737.60      24.86      9.43      9.78     0.00   0.0       19.21    0.03      318.18   32189.62     0.006     0.080     0.039     0.000     0.002     0.000   40     1.539      0    0    1    1    0    0
  1211 10/05/20 11:00    2      60.00   8    28800.00    3211.59   1732.40     19.19     0.00   0.9     1751.59    0.39     2157.32   32189.62    56.584     4.423     0.453    55.847     0.477     0.008   41     3.404      6    0    7    7    0    0
  1212 10/05/20 12:00    2      60.02   8    28809.60     353.16     42.58     12.79     0.00   0.1       55.37    0.17      420.83   32189.62    10.901     0.210     0.145    10.734     0.004     0.001   49     2.263      0    0    1    1    0    0
  1213 10/05/20 13:00    2      60.25   8    28920.00    1415.78    465.06     18.32     0.00   0.4      483.38    0.37      839.79   32189.62     0.079     8.471     0.478     0.001     1.321     0.004   42     3.358      2    0    3    2    0    0
  1214 10/05/20 14:00    2      59.80   8    28704.00      20.56      8.60     10.49     0.00   0.0       19.09    0.03      318.41   32189.62     0.012     0.127     0.052     0.000     0.002     0.001   42     1.780      0    0    1    1    0    0

Top Timed Events:

Drilling down on the Top Events on the SNAP_ID 1211, I’m wondering if I’ll be seeing something different… you can see that half of the DB Time is attributed to “CPU time” but with just an AAS of .5 which means there’s still a lot of CPU available on the RAC node and the database is probably not blocked. You could also see that there’s some external table read going on (hmm that could be the 1st demo), and notice the event “cell smart table scan”..

                                                                                                                   AWR Top Events Report

                         i
                         n
       Snap              s       Snap                                                                                                    A
  Snap Start             t        Dur                                          Event                          Time    Avgwt DB Time      A
    ID Time              #        (m) Event                                     Rank          Waits            (s)     (ms)       %      S Wait Class
------ --------------- --- ---------- ---------------------------------------- ----- -------------- -------------- -------- ------- ------ ---------------
  1208 10/05/20 08:00    2      60.25 reliable message                             1        1674.00           8.80     5.26      37    0.0 Other
  1208 10/05/20 08:00    2      60.25 CPU time                                     2           0.00           8.43     0.00      35    0.0 CPU
  1208 10/05/20 08:00    2      60.25 PX Deq: Slave Session Stats                  3        3150.00           8.23     2.61      34    0.0 Other
  1208 10/05/20 08:00    2      60.25 control file sequential read                 4       11400.00           7.12     0.62      30    0.0 System I/O
  1208 10/05/20 08:00    2      60.25 enq: PS - contention                         5        8342.00           2.66     0.32      11    0.0 Other
  1209 10/05/20 09:00    2      59.89 CPU time                                     1           0.00           8.43     0.00      39    0.0 CPU
  1209 10/05/20 09:00    2      59.89 PX Deq: Slave Session Stats                  2        3130.00           7.27     2.32      33    0.0 Other
  1209 10/05/20 09:00    2      59.89 control file sequential read                 3       11330.00           6.68     0.59      31    0.0 System I/O
  1209 10/05/20 09:00    2      59.89 reliable message                             4        1608.00           4.14     2.58      19    0.0 Other
  1209 10/05/20 09:00    2      59.89 enq: PS - contention                         5        8347.00           2.38     0.28      11    0.0 Other
  1210 10/05/20 10:00    2      59.87 CPU time                                     1           0.00           9.43     0.00      38    0.0 CPU
  1210 10/05/20 10:00    2      59.87 control file sequential read                 2       11314.00           7.98     0.71      32    0.0 System I/O
  1210 10/05/20 10:00    2      59.87 PX Deq: Slave Session Stats                  3        3305.00           7.83     2.37      31    0.0 Other
  1210 10/05/20 10:00    2      59.87 reliable message                             4        1654.00           5.81     3.51      23    0.0 Other
  1210 10/05/20 10:00    2      59.87 enq: PS - contention                         5        8520.00           1.98     0.23       8    0.0 Other
  1211 10/05/20 11:00    2      60.00 CPU time                                     1           0.00        1732.40     0.00      54    0.5 CPU
  1211 10/05/20 11:00    2      60.00 external table read                          2       12567.00         410.76    32.69      13    0.1 User I/O
  1211 10/05/20 11:00    2      60.00 cell smart table scan                        3       86393.00         238.88     2.77       7    0.1 User I/O
  1211 10/05/20 11:00    2      60.00 gc buffer busy acquire                       4        4137.00         196.75    47.56       6    0.1 Cluster
  1211 10/05/20 11:00    2      60.00 external table misc IO                       5         119.00         119.18  1001.48       4    0.0 User I/O
  1212 10/05/20 12:00    2      60.02 cell smart table scan                        1       33606.00         197.91     5.89      56    0.1 User I/O
  1212 10/05/20 12:00    2      60.02 cell smart file creation                     2       92753.00          84.68     0.91      24    0.0 User I/O
  1212 10/05/20 12:00    2      60.02 ASM file metadata operation                  3         830.00          80.80    97.35      23    0.0 Other
  1212 10/05/20 12:00    2      60.02 control file sequential read                 4       11440.00          56.00     4.90      16    0.0 System I/O
  1212 10/05/20 12:00    2      60.02 CPU time                                     5           0.00          42.58     0.00      12    0.0 CPU
  1213 10/05/20 13:00    2      60.25 CPU time                                     1           0.00         465.06     0.00      33    0.1 CPU
  1213 10/05/20 13:00    2      60.25 gc buffer busy acquire                       2       15209.00         379.47    24.95      27    0.1 Cluster
  1213 10/05/20 13:00    2      60.25 external table read                          3        5706.00         211.29    37.03      15    0.1 User I/O
  1213 10/05/20 13:00    2      60.25 gc current block busy                        4        1091.00          94.23    86.37       7    0.0 Cluster
  1213 10/05/20 13:00    2      60.25 external table misc IO                       5          48.00          69.21  1441.82       5    0.0 User I/O
  1214 10/05/20 14:00    2      59.80 CPU time                                     1           0.00           8.60     0.00      42    0.0 CPU
  1214 10/05/20 14:00    2      59.80 PX Deq: Slave Session Stats                  2        3159.00           7.09     2.24      34    0.0 Other
  1214 10/05/20 14:00    2      59.80 control file sequential read                 3       11363.00           6.35     0.56      31    0.0 System I/O
  1214 10/05/20 14:00    2      59.80 reliable message                             4        1706.00           4.86     2.85      24    0.0 Other
  1214 10/05/20 14:00    2      59.80 enq: PS - contention                         5        8312.00           2.23     0.27      11    0.0 Other

Tablespace IO statistics:

Having seen the 1 GB/s throughput on each storage server I’d like to know how it is with the tablespace and datafile latency.. As you can see below there are high reads on tablespace DG14D7 and writes on DG14D1

                                                                                                                 AWR Tablespace IO Report

                         i
                         n
       Snap              s       Snap
  Snap Start             t        Dur                        IO                IOPS                                      IOPS                                              IOPS
    ID Time              #        (m) TS                   Rank    Reads Av Reads/s Av Rd(ms) Av Blks/Rd   Writes Av Writes/s Buffer Waits Av Buf Wt(ms) Total IO R+W Total R+W
------ --------------- --- ---------- -------------------- ---- -------- ---------- --------- ---------- -------- ----------- ------------ ------------- ------------ ---------
  1208 10/05/20 08:00    2      60.25 SYSAUX                  1        0          0       0.0        0.0      203           0            0           0.0          203         0
  1208 10/05/20 08:00    2      60.25 UNDOTBS2                2        0          0       0.0        0.0       90           0            0           0.0           90         0
  1208 10/05/20 08:00    2      60.25 SYSTEM                  3        0          0       0.0        0.0        2           0            0           0.0            2         0
  1209 10/05/20 09:00    2      59.89 SYSAUX                  1        7          0       1.4        1.0      185           0            0           0.0          192         0
  1209 10/05/20 09:00    2      59.89 UNDOTBS2                2        0          0       0.0        0.0       91           0            0           0.0           91         0
  1209 10/05/20 09:00    2      59.89 SYSTEM                  3        1          0       0.0        1.0        5           0            0           0.0            6         0
  1210 10/05/20 10:00    2      59.87 SYSAUX                  1       20          0       2.5        1.0      194           0            0           0.0          214         0
  1210 10/05/20 10:00    2      59.87 UNDOTBS2                2        0          0       0.0        0.0       88           0            0           0.0           88         0
  1210 10/05/20 10:00    2      59.87 SYSTEM                  3        0          0       0.0        0.0        4           0            0           0.0            4         0
  1211 10/05/20 11:00    2      60.00 DG14D7                  1   112984         31      21.0       63.5        0           0            0           0.0       112984        31
  1211 10/05/20 11:00    2      60.00 DG14D1                  2      382          0       5.8        1.0    15317           4         2257          37.0        15699         4
  1211 10/05/20 11:00    2      60.00 UNDOTBS2                3        0          0       0.0        0.0      263           0           44           0.0          263         0
  1211 10/05/20 11:00    2      60.00 SYSAUX                  4       12          0       1.7        1.0      239           0            0           0.0          251         0
  1211 10/05/20 11:00    2      60.00 SYSTEM                  5       44          0       2.0        1.0      101           0         1160         121.1          145         0
  1212 10/05/20 12:00    2      60.02 SYSAUX                  1       20          0      60.0        1.0      298           0            0           0.0          318         0
  1212 10/05/20 12:00    2      60.02 UNDOTBS2                2        0          0       0.0        0.0      176           0            0           0.0          176         0
  1212 10/05/20 12:00    2      60.02 SYSTEM                  3       41          0       3.9        1.0       27           0            0           0.0           68         0
  1213 10/05/20 13:00    2      60.25 DG14D1                  1      259          0       3.7        1.0    30061           8         1068          29.0        30320         8
  1213 10/05/20 13:00    2      60.25 SYSAUX                  2        9          0       2.2        1.0      265           0            0           0.0          274         0
  1213 10/05/20 13:00    2      60.25 UNDOTBS2                3        0          0       0.0        0.0      249           0            3          10.0          249         0
  1213 10/05/20 13:00    2      60.25 SYSTEM                  4       17          0     222.9        1.0       45           0         1881         192.4           62         0
  1214 10/05/20 14:00    2      59.80 SYSAUX                  1       44          0       4.1        1.0      285           0            0           0.0          329         0
  1214 10/05/20 14:00    2      59.80 UNDOTBS2                2        0          0       0.0        0.0      160           0            1           0.0          160         0
  1214 10/05/20 14:00    2      59.80 SYSTEM                  3        1          0      20.0        1.0       10           0            0           0.0           11         0

Datafile IO statistics:

Below are the datafiles with read/write activity across SNAP_IDs… You may notice that tablespaces DG14D7 and DG14D1 on disk group +DISKVOL1 contains only user data while +DISKVOL3 contains the SYSTEM, SYSAUX, UNDO tablespaces

                                                                                                                    AWR File IO Report

                         i
                         n
       Snap              s       Snap
  Snap Start             t        Dur                                                                                           IO                IOPS                                      IOPS                                              IOPS
    ID Time              #        (m) TS                   File# Filename                                                     Rank    Reads Av Reads/s Av Rd(ms) Av Blks/Rd   Writes Av Writes/s Buffer Waits Av Buf Wt(ms) Total IO R+W Total R+W
------ --------------- --- ---------- -------------------- ----- ------------------------------------------------------------ ---- -------- ---------- --------- ---------- -------- ----------- ------------ ------------- ------------ ---------
  1208 10/05/20 08:00    2      60.25 SYSAUX                   2 +DISKVOL3/rtldb/datafile/sysaux.299.714681829                   1        0          0                           203           0            0           0.0          203         0
  1208 10/05/20 08:00    2      60.25 UNDOTBS2                 4 +DISKVOL3/rtldb/datafile/undotbs2.260.714681979                 2        0          0                            90           0            0           0.0           90         0
  1208 10/05/20 08:00    2      60.25 SYSTEM                   1 +DISKVOL3/rtldb/datafile/system.300.714681827                   3        0          0                             2           0            0           0.0            2         0
  1209 10/05/20 09:00    2      59.89 SYSAUX                   2 +DISKVOL3/rtldb/datafile/sysaux.299.714681829                   1        7          0       1.4        1.0      185           0            0           0.0          192         0
  1209 10/05/20 09:00    2      59.89 UNDOTBS2                 4 +DISKVOL3/rtldb/datafile/undotbs2.260.714681979                 2        0          0                            91           0            0           0.0           91         0
  1209 10/05/20 09:00    2      59.89 SYSTEM                   1 +DISKVOL3/rtldb/datafile/system.300.714681827                   3        1          0       0.0        1.0        5           0            0           0.0            6         0
  1210 10/05/20 10:00    2      59.87 SYSAUX                   2 +DISKVOL3/rtldb/datafile/sysaux.299.714681829                   1       20          0       2.5        1.0      194           0            0           0.0          214         0
  1210 10/05/20 10:00    2      59.87 UNDOTBS2                 4 +DISKVOL3/rtldb/datafile/undotbs2.260.714681979                 2        0          0                            88           0            0           0.0           88         0
  1210 10/05/20 10:00    2      59.87 SYSTEM                   1 +DISKVOL3/rtldb/datafile/system.300.714681827                   3        0          0                             4           0            0           0.0            4         0
  1211 10/05/20 11:00    2      60.00 DG14D7                  15 +DISKVOL1/rtldb/datafile/dg14d7.501.714759895                   1   112984        187      21.0       63.5        0           0            0           0.0       112984        31
  1211 10/05/20 11:00    2      60.00 DG14D1                  13 +DISKVOL1/rtldb/datafile/dg14d1.500.714758119                   2      382          1       5.8        1.0    15317          25         2257          37.0        15699         4
  1211 10/05/20 11:00    2      60.00 UNDOTBS2                 4 +DISKVOL3/rtldb/datafile/undotbs2.260.714681979                 3        0          0                           263           0           44           0.0          263         0
  1211 10/05/20 11:00    2      60.00 SYSAUX                   2 +DISKVOL3/rtldb/datafile/sysaux.299.714681829                   4       12          0       1.7        1.0      239           0            0           0.0          251         0
  1211 10/05/20 11:00    2      60.00 SYSTEM                   1 +DISKVOL3/rtldb/datafile/system.300.714681827                   5       44          0       2.0        1.0      101           0         1160         121.1          145         0
  1212 10/05/20 12:00    2      60.02 SYSAUX                   2 +DISKVOL3/rtldb/datafile/sysaux.299.714681829                   1       20          0      60.0        1.0      298           0            0           0.0          318         0
  1212 10/05/20 12:00    2      60.02 UNDOTBS2                 4 +DISKVOL3/rtldb/datafile/undotbs2.260.714681979                 2        0          0                           176           0            0           0.0          176         0
  1212 10/05/20 12:00    2      60.02 SYSTEM                   1 +DISKVOL3/rtldb/datafile/system.300.714681827                   3       41          0       3.9        1.0       27           0            0           0.0           68         0
  1213 10/05/20 13:00    2      60.25 DG14D1                  13 +DISKVOL1/rtldb/datafile/dg14d1.500.714758119                   1      259          0       3.7        1.0    30061          50         1068          29.0        30320         8
  1213 10/05/20 13:00    2      60.25 SYSAUX                   2 +DISKVOL3/rtldb/datafile/sysaux.299.714681829                   2        9          0       2.2        1.0      265           0            0           0.0          274         0
  1213 10/05/20 13:00    2      60.25 UNDOTBS2                 4 +DISKVOL3/rtldb/datafile/undotbs2.260.714681979                 3        0          0                           249           0            3          10.0          249         0
  1213 10/05/20 13:00    2      60.25 SYSTEM                   1 +DISKVOL3/rtldb/datafile/system.300.714681827                   4       17          0     222.9        1.0       45           0         1881         192.4           62         0
  1214 10/05/20 14:00    2      59.80 SYSAUX                   2 +DISKVOL3/rtldb/datafile/sysaux.299.714681829                   1       44          0       4.1        1.0      285           0            0           0.0          329         0
  1214 10/05/20 14:00    2      59.80 UNDOTBS2                 4 +DISKVOL3/rtldb/datafile/undotbs2.260.714681979                 2        0          0                           160           0            1           0.0          160         0
  1214 10/05/20 14:00    2      59.80 SYSTEM                   1 +DISKVOL3/rtldb/datafile/system.300.714681827                   3        1          0      20.0        1.0       10           0            0           0.0           11         0

Top SQLs:

I’d like to know the top SQLs running on the server, to my surprise as I was investigating on the output… the number 1 SQL was the massive data load executed on the demo (SNAP_ID 1211)…

This direct-path load (INSERT /*+ APPEND */) was using an external table with flat files (multiple small files in similar size with total size roughly around 50GB) staged on a DBFS (database filesystem) and loading it to a compressed table… This is very similar to the Data Warehouse data loading best practice mentioned here

What’s interesting about this it was able to process the 199million rows in just 27mins! and take note of the low AAS (0.39) doing this operation… and it would be nice to see the SQL Monitor Report for this execution as well as the output of my AWR scripts on other RAC nodes.. and yes the final “select count(*)…” on the target table…

BTW, I’ve briefly explained the output of the AWR Top SQL script on this reply to Oralce-l

                                                                                                                    AWR Top SQL Report

                         i                                                                 Ela
                         n                                                                Time
       Snap              s    Snap                         Plan                 Ela        per        CPU         IO        App        Ccr    Cluster                                                                               PX       A
  Snap Start             t     Dur SQL                     Hash                Time       exec       Time       Wait       Wait       Wait       Wait                                 Direct                            Parse   Server       A Time SQL
    ID Time              #     (m) ID                     Value Module          (s)        (s)        (s)        (s)        (s)        (s)        (s)          LIO          PIO       Writes         Rows     Exec      Count     Exec       S Rank Text
------ --------------- --- ------- --------------- ------------ -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ------------ ------------ ------------ -------- ---------- -------- ------- ---- ------
  1211 10/05/20 11:00    2   60.00 gz90qgc3f72fa     1967845001 SQL*Plus    1411.13    1411.13     565.92     305.14       0.00      64.05     347.28       536400           93        99690    199553620        1         17       16    0.39    1 INSERT
  1213 10/05/20 13:00    2   60.25 gz90qgc3f72fa     1967845001 SQL*Plus    1359.58    1359.58     443.98     291.29       0.00      16.64     518.73       466179           17       292337    198131707        1         17       16    0.38    1 INSERT
  1211 10/05/20 11:00    2   60.00 4tb00t5k8jc41     3807662380 SQL*Plus    1306.22     326.56     999.78     307.32       0.00       0.00       0.00     13140180     13107338            0            3        4         20       12    0.36    2 select
  1050 10/05/13 18:00    2   59.94 g2cd0wdckd7rn     3807662380 SQL*Plus     785.48                575.09     210.45       0.00       0.00       0.00      7226484      7208526            0            0        0          8        8    0.22    1 select
  1049 10/05/13 17:00    2   59.93 g2cd0wdckd7rn     3807662380 SQL*Plus     785.42                577.15     208.40       0.00       0.00       0.00      7191030      7173160            0            0        0          8        8    0.22    1 select
  1213 10/05/20 13:00    2   60.25 0kkhhb2w93cx0     2170058777              469.05       0.29       3.21       0.00       0.00      11.60     452.32        32076            0            0         1610     1610       1609        0    0.13    2 update
  1211 10/05/20 11:00    2   60.00 2nszgcfgcgvts      666733144 SQL*Plus     418.08     418.08     151.28     233.01       0.00       0.03       0.00         1782           17            0            1        1         17       16    0.12    3 select
  1211 10/05/20 11:00    2   60.00 0kkhhb2w93cx0     2170058777              215.14       0.17       1.37       0.00       0.00       4.70     208.65        12228            0            0         1263     1263       1263        0    0.06    4 update
  1212 10/05/20 12:00    2   60.02 4tb00t5k8jc41      597344198 SQL*Plus     125.51     125.51       1.75     123.71       0.00       0.00       0.00       519885       518076            0            1        1          5        4    0.03    1 select

Well this is really different from what Greg Rahn has done on the Exadata V2 loading 7.8 billion rows in 275 seconds! hmmm because of a whole lot of factors.. bigger bandwidth, faster CPUs, the Sun machines, new cool stuff on the V2, compression, table definition, # of columns, data type, etc. etc. etc. etc.

But this is astounding compared to the data load rate I’m seeing on other Data Warehouse environments…

And this is where the importance of having a balanced configuration kicks in.. On some environments I see storage saturation (worst is seeing the disks 100% busy with high service times and queue) and low storage bandwidth causing low data load rate and longer response times affecting the whole ETL process… as an example you can see from the figures below that even if you have the right amount of CPU (16 powerful CPUs) but if the IO subsystem can’t cope with the application requirements you clearly have a bottleneck.. This is a linear regression I made on IO read and write MB/s on Direct Path Read and Large Writes respectively in relation to the scheduled ETL run times and daily workload patterns.

IO read	IO write

Having seen how Exadata behaves and learning about the architecture, administration, IORM (IO Resource Management), monitoring, and migration paths… although lacking hands-on experience and there are more things I still have to learn! I can say that it’s no longer a black box… it has already been demystified..  ;)  I have to say.. hands-down to the Exadata Storage Server Software without it all the storage servers will just behave as a traditional storage server and it’s the cool stuff that makes each of them able to run on its full speed given the bandwidth limits..

Also there are some stuff I am curious about in Exadata V2 or V1…

1) Having most of the work done underneath, I wonder what’s the saturation point of the Storage Servers and when will be the trigger that I have to add another storage server or Exadata rack..

2) And how is it going to be if applied with IORM (IO Resource Management) and production and test databases are sharing Exadata Storage Servers… more like large scale consolidation of databases… hmm..

3) I wonder how this will change the capacity planning space.. because you now have Storage Servers that could work smart and work hard making the difference for the leap in performance.. hmm..

but our brain is multidimensional…

the information as it is being absorbed could be travelling sequentially to our brain, but internally it is not being serviced in simple lists and lines. There is a complex process of sorting and selecting and the whole network of words and ideas are being juggled and interlinked in order to have a far better meaning.

We also receive each word that we read in the context of the words that surround it and also basing it on our own special interpretation as dictated by our experiences and personal information patterns.

You can see from the illustrations below, that just by looking at them particularly on the key words and connections we could easily get the whole idea about the topic…

Tony Buzan’s mind map of his bio	Mind map of Oracle Products (not updated)

And this is where the Mind Map is very useful.. the non-linear structure of words and topics and interconnections makes us easy to “slot in” and relate to the main idea and branch out as dictated by the individual ideas and general form of the central theme.

I also do this when reading books and important articles, on the meetings, and doing presentations. Which is also very helpful for recall and retention and going back to the state of my mind when I’m studying that particular topic or idea. Yes.. there are really moments that I become very forgetful about stuffs on the time that I really need it, and I just get back on my notes/sketches to refresh and get things done

As for my files I also file and organize them on my “knowledge folders”. As an Oracle database consultant, I need to learn and read a lot of stuff (whitepapers,presentations,howto,etc.) about Oracle and related technologies… as time goes on, I’ll be reading more and more and will add up to my body of knowledge… just like the concept of Mind Map, these folders and the documents in it are well organized and very useful to get things done

Then, a friend of mine, Tommy Lim mentioned something about a personal wiki his friend shared him that is very useful for his Java development work, all of his useful stuffs are there all on one single file and could even be stored on a USB and shared to others… Yes.. this is the TiddlyWiki, a self-contained wiki So I tried it, I first got the empty TiddlyWiki file here (from TiddlyWiki.org) and started playing with it and put some contents. Yes, it was just one file.. see the image on the right —>

And then I realized that I wanted something hosted so people can make use of my TiddlyWiki and also I will be able to access my useful stuffs and cheat sheets anywhere that has a browser and internet connection.. so I made use of Tiddlyspot (FREE!) BTW, I made use of the MonkeyPirateTiddlyWiki (MPTW) flavour for its powerful TagglyTagging feature for organizing the TiddlyWiki data (to avoid hand maintaining “index” pages of the tiddlers). Still not convinced? Check here for more info..

So will I still be blogging? …
what can you get from my TiddlyWiki?

YES I will still blog! For me, each blog post has its own story and experience just like what I’ve shared on my past entries that will be very useful for people who have not yet been on a similar problem or scenario.. (yeah, still more to come )

But the sequential nature of the blog makes it difficult to organize things just like the concept of the Mind Map.. So expect my TiddlyWiki to contain more cheat sheets, howtos, guides, scripts, test cases, notes, and links to important documents or sites.

In short, more on my body of knowledge.. BTW, RSS is also available

You can start making your own hosted TiddlyWiki by following this video:

Below are some links for further reading about TiddlyWiki:

Reviews:

http://jaybyjayfresh.com/2008/01/23/tiddlytemplating-using-tiddlywiki-to-create-webpages/

Howto:

http://faq.tiddlyspot.com/
http://www.giffmex.org/twfortherestofus.html
http://tiddlywiki.org/wiki/TiddlyWiki_Resources
http://tiddlythemes.com/#Home
http://tiddlyspot.blogspot.com/2007/09/tiddlythemes-images-now-work-on-your.html
http://tiddlywiki.org/wiki/How_To/Setting_Up_TiddlyWiki_As_a_Website
http://parand.com/say/index.php/2006/01/06/howto-using-tiddlywiki/
Tiddlywiki Formatting Guide
Intro to TiddlyWiki

Facts:

http://en.wikipedia.org/wiki/TiddlyWiki
http://en.wikipedia.org/wiki/Personal_wiki
http://en.wikipedia.org/wiki/Getting_Things_Done
http://en.wikipedia.org/wiki/Comparison_of_wiki_software
http://en.wikipedia.org/wiki/List_of_wiki_software

I hope I’ve shared you some good stuff Enjoy!

In this post (which has been looong overdue :p ) I’ll show you how you could make use of Social Networking Sites to make knowledge sharing and meeting people more fun. As you can see from the illustration above, I’ve made use of the following sites/services to build a dynamic and multidimensional means of communication:

• Facebook
• Twitter
• FeedBurner RSS
• FeedBurner Mail Subscription
• Google Sites
• LinkedIn

The story behind it…

I am a DBA for 4years+, and started with zero knowledge about Oracle. I joined SQL*Wizard, which has a great training program and exposed me to a lot of difficult DBA situations. After a while I got my OCA and OCP then got interested on Linux and got my RHCT… well, hopefully RHCE soon. All of my accumulated knowledge came from three things. First: lots of reading/research Second: doing test cases….. Third:…..guess what….

it’s the community.

And speaking of community, we have our local users group here, called Philippine Oracle Users Group (POUG) which has been inactive for a long time (speaking based on my experience) I joined the POUG Yahoo Group last November 2007, ever since I joined there was no activity… then months have passed I was able to gain more knowledge and experience as a DBA, joined other forums, participated on online communities, mailing list, and even follow other Users Group activities. Then one day, I was checking on some of the Yahoo Groups that I’ve joined and a couple of mouse clicks brought me again to POUG Yahoo Group and noticed the last message was still year 2007…. yes I felt bad…. I think the community spirit among technology groups in the US or other countries seems to be stronger… I admire these guys, and I want to have one here, locally. It’s just difficult to motivate people to share ideas and help each other.

BTW the latest news about POUG… it is closing down as a corporation. There could be a number of reasons and one of them could be the inactive status. Whatever are the reasons for closing it, I am sure I’m not alone wanting to have a local community…

It got me thinking, why not do some social experiment? … If I could pool a number of Oracle Users (DBA/Developers), feed them updates about the latest in Oracle, give helpful articles, links, blogs, videos, or any technical info that would be useful on their everyday tasks will stir up the curiosity and I may find like minded people that could help me build (or revive) the local Users Group/community. Well, I’d like to have an environment similar to Oracle-L (http://www.freelists.org/list/oracle-l) or China’s ITPUB (http://www.itpub.net), where you can find lots of good stuff, good questions, good replies, no one is ranting about something (but this can’t be completely avoided), somebody is sharing of a problem then the other guy (expert or a newbie) shares his solution, nobody thinks of competition (company A vs. company B, or consultant A or B vs C)…

just pure technology…(but yes, preferably there should be real interaction)

All of these inspired me to create a Facebook page, and called it “Oracle Users – Philippines” (sorry I can’t think of a better name ) … One service/site followed after the other (LinkedIn, Twitter, RSS Feed and Mail Subscription)…. and somehow I managed to create a useful link between them. But the primary objective for putting up these networking accounts is to have a pool of professionals & newbies sharing knowledge, experiences, and helping each other.

Since it was created last year (latter part of 2009), it has now grown to 300+ members! (and still increasing) you can see the demographics below:

And you can see the rate of page views over the period of October to March which is a proof that there are people reading the daily entries

So what’s happening?

Also, we’ve been using these networking accounts to spread the news about the periodic DBA round table, technical sessions, enablement and workshops. See a sample post from Shaheen (from Oracle Singapore) below. She’s on the same team as Blair Layton, and they’ve been doing good on putting up community programs here in the Philippines. Thanks for them for giving me the chance to present twice on the DBA round table.

Also, just recently the Upgrade Guys were here in Manila (see the blog post here) for a one day 11gR2 upgrade seminar. There were a lot of attendees! .. roughly around 70+..  composed of customers, partners, and even Oracle employees… and I was happy to meet new friends and see a comment like the one below after the event

….So even if I don’t frequently post on this blog, I microblog a lot of useful stuff on these networking sites

And I think connecting people within these networking sites is a good place to start…. so let’s see what happens next

The PSIA, PSIA Tech Council, together with the Awesome and Cool sponsors will be having the Luzon leg of DEVCON here in Manila!

Below are the details of this awesome event:

09 February 2010, 4-9pm, SMX Convention Center Function Room 1

Sync. Support. Succeed.

Get together to be connected, enhance skills and support each other to achieve success!

Designed to be a premier gathering of all Filipino software engineers, DEVCON facilitates collaboration, interaction and mentoring among leading practitioners of the Philippine software industry. DEVCON adapts global best practices for skills improvement and professional advancement among Filipino software engineers. It features three main elements which has successful formats used in international technology gatherings:

> Lightning Talks – a fast-paced presentation on any topic of interest
> Birds of a Feather – a dynamic discussion of opposing perspectives on mutual topics
> Hackathon – providing rapid learning of a new technology through hands-on demonstration or joint coding onsite

Register online for your FREE seat.

http://devcon.ph
Check us out on Facebook.

DEVCON is a pioneering event of the Philippine Software Industry Association.

Registration Volunteer Luzon Visayas Mindanao

The awesome venue

Map of the venue

BTW, the program mentioned the words Pizza and Beer (twice). That would be cool See you there!

Update (20100319):
We were invited by Asia Pacific College to host a DEVCON on their College Week. I did a Lightning Talk (10-15mins presentation) about Virtualization giving the students a historical background (mainframe days) and moving forward to x86 architecture (xen,kvm,etc.). I also explained to them the comparison of different methods to do x86 Virtualization. These are very useful info when they start to apply or try these technologies on the real world. Overall the event was great and we had a very interesting Q&A/discussion about career matters.

Check out my presentation here (pdf) – DEVCON Event@Asia Pacific College, 03/19/2010

Earlier I had a few mail exchanges with him and got to check his new work.. called the Stress Identifier. Hmm.. so how it will be helpful? Well if you have already characterized your workload, you may want to definitively determine which internal database activity is stressing your CPU, IO, and network subsystems. So this tool will avoid the guess work of selecting that “unit of work”… check out the link for more details…

And most of all.. grab a copy of his new book Oracle Performance Firefighting… which got lots of good stuff on performance

Well, this post is about taking advantage of the built in data store that started in 10gR1.. yeah it’s AWR, and it’s very much like a “Statspack on steroids” (got that from Kyle Hailey’s modules). What’s exciting about 10g and above is we could utilize the “Time Model” and the OSSTAT view and use them together with the SYSTAT view to have a far better workload information when going through all the SNAP_IDs.

I have this “little” issue before of getting lazy when generating multiple AWR reports… yeah it’s just so daunting, you’ll get tired just by generating those reports by hand and the catch is… you’ve not yet analyzed anything yet.. :p   (but yeah, you’re lucky if you’ve got access to the OEM performance page)

So.. I was thinking, if I get to see all the relevant workload info across SNAP_IDs it would be easier for me to notice trends and even possible for me to visualize the data, or even possible to do some statistics out of it.

On the Chapter 9 of Craig Shallahamer’s book (Oracle Performance Firefighting)… there he explained in detail what information you need to get for you to be able to define the Database Server’s Capacity, Requirements, and Utilization (U=R/C)…
I’ve outline some of the points here (see the Firefighting tab)

…. since the AWR report is based on DBA_HIST tables, I was able to make a script that shows the following:

1) CPU capacity – see image below, column 4 to 6
2) CPU requirements – see image below, column 7 to 14
3) Memory requirements – see image below, column 15
4) IO requirements – see image below, column 16 to 21
5) some SYSSTAT delta values – see image 22 to 23
6) Utilization – see image below, the last 6 columns

…. I’ve used the tables below for the query:

- dba_hist_snapshot
- dba_hist_osstat
- dba_hist_sys_time_model
- dba_hist_sysstat

Check out the scripts here:
For Linux/Unix: awr_genwl.sql
For Windows: awr_genwl_win.sql

IMPORTANT NOTE: Diagnostic Pack License is needed for the scripts, I’m updating my scripts frequently.. if the download link is not working here.. check out the Scripts Section for the file name and the most recent version of the script

Now time for some action!

As you can see from above.. there is a highlighted portion (in green). By running the script, we have identified that there was a sudden spike on the following:

- “DB Time” (7th column).. which means there is some significant user level activity going on!
- IOPs read (16th column) and IO read MB/s (19th column).. which means that the stresser is possibly more on SELECT operations
- Oracle and OS CPU% utilization (last 6 columns)… which means the spike on activity is attributed by Oracle and not other operations on the OS, also you could see that it is not attributed by an RMAN activity (because it’s zero!)
- Plus the Average Active Sessions (AAS) (11th column) and the OS Load (13th column) increased… which correlates to the high CPU%

The sudden spike happened around 6:20 to 7:01 (SNAP_ID 335-339)

If you are a fan of AAS (Average Active Sessions) see below how I was able to derive the value (example for SNAP_ID 335):

AAS = DB Time/Elapsed Time
= (1871.36/60)/10.06
= 3.100331345

BTW, I only have 1 CPU (see column 5)…

So you have a lot of info (facts, numbers, figures) to justify the drill down on specific SNAP_IDs or time frame. From here you could either use ASH, run the AWR report, run ADDM, or use OEM. The good thing here is, you are not guessing! and you’ve done some quick correlation on the OS and the database before drilling down further…

Now…

Let’s see if we could correlate the output of the script with the SAR data.. from the image below, you can see that the spike on load happened around 6:20 and ended around 7:30. And yes, attributed by wait on IO (see the red color).

BTW, I’m using kSar for visualizing the SAR data.

Also you’ll see the Load Average, which is the same from the output of the AWR script above.

Now, let’s check out the IO devices… the database is using ASM with 3 ASM disks, I would get the major and minor numbers and correlate these with the SAR data…

GROUP_NUMBER NAME		  HEADER_STATU MOUNT_S STATE	  TOTAL_MB    FREE_MB LABEL		   PATH 		REDUNDANCY
------------ -------------------- ------------ ------- -------- ---------- ---------- -------------------- -------------------- --------------------
	   1 DATA_1_0000	  UNKNOWN      OPENED  NORMAL	      3067	    0			   /dev/sdc1		UNKNOWN
	   1 DATA_1_0001	  UNKNOWN      OPENED  NORMAL	      3067	    0			   /dev/sdd1		UNKNOWN
	   1 DATA_1_0002	  UNKNOWN      OPENED  NORMAL	      3067	    0			   /dev/sdf1		UNKNOWN

[oracle@dbrocaix01 osm]$ ls -l /dev/sd[c,d,f]
brw-rw----  1 root disk 8, 32 Jan 17  2010 /dev/sdc
brw-rw----  1 root disk 8, 48 Jan 17  2010 /dev/sdd
brw-rw----  1 root disk 8, 80 Jan 17  2010 /dev/sdf

You can see from the images of the block devices below that they are mainly on Read Activity.. which
validates the output of IOPs read (16th column) and IO read MB/s (19th column) of the AWR script… cool!

Another thing… When you get to run the script on your environment also try to generate AWR report on particular SNAP_IDs… you’ll notice that the values you got from the query will be the same on the AWR report…

Update (20100201):

… I’d like you to see the output of the Enterprise Manager Performance Page on the time when the sudden workload spike happened.

The first and second images above came from the root page, you’ll see the following similarities on the AWR script output:

- Load Average, which is on the range of 3.2 to 4.4
- Average Active Sessions (AAS), which is on the range of 2.2 to 3.5
- Disk IO read MB/s… see that the graph is reaching the 80000 Physical Reads KB/s.. check the SNAP_ID 338 (Time – 6:50) from the AWR script output you’ll see that it generated 73.372 MB/s! Also, see the output of the ADDM run below… which further validates the output of the script:

FINDING 2: 18% impact (236 seconds)
-----------------------------------
The throughput of the I/O subsystem was significantly lower than expected.

   RECOMMENDATION 1: Host Configuration, 18% benefit (236 seconds)
      ACTION: Consider increasing the throughput of the I/O subsystem.
         Oracle's recommended solution is to stripe all data file using the
         SAME methodology. You might also need to increase the number of disks
         for better performance. Alternatively, consider using Oracle's
         Automatic Storage Management solution.
      RATIONALE: During the analysis period, the average data files' I/O
         throughput was 74 M per second for reads and 102 K per second for
         writes. The average response time for single block reads was 13
         milliseconds.

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Wait class "User I/O" was consuming significant database time.
               (37% impact [488 seconds])

The third image is the Top Activity section… you can see that most of the activity are SELECTs and the database is waiting on User and System I/O… (see the Top SQLs and Top Sessions)

BTW, this script could also be used on a RAC environment… you have to run it on each node. I’ve made it pull the DBID and instance_number automatically..

Update (20100202):

On Windows environment for the DBA_HIST_OSSTAT view it does not contain the LOAD and IOWAIT_TIME stat_name so I removed these columns from the query. See the sample output below:

That’s all for this post! Now you’ll know better about the workload of your environment

I have never seen a huge compilation of SQL tuning tips or rewrite scenarios (with test cases) and got them only on OracleFans forum… ooops… so you can’t read Chinese? try this translated version, whew.. good thing Google has this translate service that I am able to read in Chinese..

So how did I end up on this forum? mm… I can’t really remember… I’m a curious wayward Googler, either I was having problems on a particular SQL on a client or I’m getting really curious about SQL Tuning and trying to get the whole idea (building up a mind map) by doing multiple reads..

Even the language barrier (Chinese) does not even stop me from digging good knowledge and I even subscribed & created accounts on some other similar forums.. Then luckily… I’ve stumbled upon this series! A guy named David wrote it.. I’ve already mailed him & thanked him for compiling this very useful good stuff but I did not get any response yet.. maybe I should translate my English to Chinese :p .. I’m just kidding..

So here it goes… just click on the links and you’ll be redirected to the translated version!
well regarding the accuracy of his findings… as a reader, I don’t believe instantly on all what he wrote… before you take in something there has to be some form of validation (you have to be some kinda scientist yourself)…

For those who have objections or if you feel that some claims are inaccurate or if you have done your own validation but the results are different from what is written, please do post your comment on that particular item or topic.. Your feedback is very valuable and it would be useful for other viewers too

If you are having problems with the links, you can check all the URL here

ORACLE SQL Performance Optimization Series (1)

1. The types of ORACLE optimizer
2. The way to visit Table
3. Shared SQL statement

ORACLE SQL Performance Optimization Series (2)

4. Select the table name of the most efficient order (only in the effective rule-based optimizer)
5. WHERE clause in the order of the connections
6. SELECT clause to avoid using ‘*’
7. Access to the database to reduce the number of

ORACLE SQL Performance Optimization Series (3)

8. Using the DECODE function to reduce the processing time
9. Integration of simple, non-associated database access
10. Remove duplicate records
11. Alternative DELETE with TRUNCATE
12. As much as possible the use of COMMIT

ORACLE SQL Performance Optimization Series (4)

13. Calculate the number of records
14. Where clause with the HAVING clause to replace
15. To reduce the query table
16. Through an internal function to improve SQL efficiency

ORACLE SQL Performance Optimization Series (5)

17. Use the table alias (Alias)
18. Replace IN with EXISTS
19. Replace NOT IN with NOT EXISTS

ORACLE SQL performance optimization Series (6)

20. Connect with the table to replace EXISTS
21. Replace DISTINCT with EXISTS
22. Recognition ‘inefficient implementation of the’ in SQL statements
23. Use TKPROF tool to query SQL Performance Status

ORACLE SQL Performance Optimization Series (7)

24. Analysis of SQL statements with EXPLAIN PLAN

ORACLE SQL Performance Optimization Series (8)

25. With the index to improve efficiency
26. Operation index

ORACLE SQL Performance Optimization Series (9)

27. The choice of the basis of the table
28. Number of equal index
29. Comparing and scope of the comparison equation
30. The index level is not clear

ORACLE SQL Performance Optimization Series (10)

31. Force index failure
32. Avoid the use of columns in the index calculation.
33. Auto Select Index
34. Avoid the use of NOT in the index column
35. With “= substitute”

ORACLE SQL Performance Optimization Series (11)

36. UNION replaced with the OR (for the index column)
37. To replace the OR with the IN
38. Avoid the use of columns in the index IS NULL and IS NOT NULL

ORACLE SQL Performance Optimization Series (12)

39. Always use the first column index
40. ORACLE internal operations
41. With the UNION-ALL replaced UNION (if possible)
42. Usage Tips (Hints)

ORACLE SQL Performance Optimization Series (13)

43. WHERE replaced with ORDER BY
44. Avoid changing the index of the column type
45. Need to be careful of the WHERE clause

ORACLE SQL Performance Optimization Series (14)

46. Connect multiple scan
47. CBO to use a more selective index of
48. Avoid the use of resource-intensive operations
49. GROUP BY Optimization
50. Use Date
51. Use explicit cursor (CURSORs)
52. Optimization EXPORT and IMPORT
53. Separate tables and indexes

ORACLE SQL Performance Optimization Series (15)

EXISTS / NOT EXISTS must be better than IN / NOT IN the efficiency of high?

ORACLE SQL Performance Optimization Series (16)

I used the view of how query results are wrong?

ORACLE SQL Performance Optimization Series (17)

Page Which writing efficient SQL?

ORACLE SQL Performance Optimization Series (18)

COUNT (rowid) / COUNT (pk) the efficiency of high?

ORACLE SQL Performance Optimization Series (19)

ORACLE data type implicit conversions

ORACLE SQL Performance Optimization Series (20)

The use of INDEX should pay attention to the three questions
Includes date range example

ORACLE Tips (HINT) use (Part 1) (21)

ORACLE Tips (HINT) use (Part 2) (22)

Analysis of function-based index (Part 1) (23)

Includes date range example

Analysis of function-based index (Part 2) (24)

How to achieve efficient paging query (25)

ORACLE achieved in the SELECT TOP N method (26)

RAC Assurance Support Team: RAC Starter Kit and Best Practices (Generic) (Doc ID 810394.1)
RAC Assurance Support Team: RAC Starter Kit and Best Practices (Windows) (Doc ID 811271.1)

On the Generic note, just recently they’ve uploaded a new version of the RAC System Load Testing white paper… you can also find on the attached docs the RAC System Test Plan…

Both of these docs are good stuff.. we all know that critical to the RAC install is testing the right components in the stack, either from the perspective of Performance, Scalability, or Reliability..

so these docs will guide you through the tools (yes you’ll have choices), steps, and structure on how to ensure a highly available RAC setup…

Soon I’ll have some Virtualization related posts about my experiences RHEV-iiinnggg…..

You may also want to download the RedHat whitepaper on Scaling Oracle Database on RHEV:
Scaling Oracle 10g in a Red Hat® Enterprise Virtualization Environment – RedHat Reference Architecture Series

For more info go to these links:
http://www.redhat.com/virtualization/rhev/server
http://www.redhat.com/v/swf/rhev/demo.html

Enjoy RHEViiinnggg…..

A trace file was sent and I was able to pinpoint the cause of the deadlock and the session that caused it.
The deadlock was a TX enqueue with mode of 4 (S – share) which could be verified by looking at the following lines of the Process State dump:

   last wait for 'enq: TX - row lock contention' blocking sess=0x 7000000cb239d60 seq=7849 wait_time=2929705 seconds since wait started=3
            name|mode=54580004, usn<<16 | slot=a0028, sequence=283f2

the “enqueue and lock mode” is explained as:
mode=54580004 (see above)
5458 (hex) = TX (ascii)
0004 (hex) = mode 4 (S – share)

The possible cause would be multiple concurrent sessions insert the same key value into a table that has one or more unique key. The table involved on the SQL was identified and has a unique key (on 7 columns) and 4 foreign keys (on different tables). But still I have to verify…

And I may have to look into the application logic, and may have to start with the insert statement causing the deadlock.
But, it could also be possible that there are more statements involved in this “Deadlock storm”.. and I’m just looking on one occurence..
hmm.. interesting..

Lately, I’ve been using this shell command I got from Kyle Hailey‘s site for quickly getting ORA- errors on the alert log.

See the code I’m using below:

Go to the bdump directory to run these shell commands

 Date and errors in alert.log

     cat alert_orcl.log | \
     awk 'BEGIN{buf=""}
          /[0-9]:[0-9][0-9]:[0-9]/{buf=$0}
          /ORA-/{print buf,$0}' > ORA-errors_orcl.txt

On the client site, I executed the command on the alert log. Then to my surprise.. there are so many ORA-00060 errors (total of 1,616 lines)! which means, there are more trace files that I have to look on. Great, Now I’m overwhelmed.

First…I want to know how often Deadlocks occur, I want it per month, day, and hour. So I could see the pattern of the occurrence then, if there’s a pattern I could ask the Developer what he’s running on that period or what are the modules that are frequently accessed. That way I could correlate what’s happening on the trace files to what are they doing on the application.

The alert views of 10g could be useful to get that kind of info (DBA_OUTSTANDING_ALERTS, DBA_ALERT_HISTORY, DBA_ALERT_ARGUMENTS, DBMS_ALERT_INFO, V$ALERT_TYPES).

But I want to dig on the alert log using some shell scripting.. which I’m not very good at :p

I’m playing with this very simple command, this shows me that on August 7 11AM there are 55 occurrence of “ORA-” errors. Some or all of them could be “ORA-00060″ errors.

karao@karl:~/Desktop$ export Month=Aug
karao@karl:~/Desktop$ export Date=7
karao@karl:~/Desktop$ export Time=11
karao@karl:~/Desktop$ echo $Month $Date $Time "," `cut -d " " -f1,2,3,4,5,6 alert_orcl.log | grep "$Month  $Date $Time" | wc -l`
Aug 7 11 , 55

So it’s possible! I could have an output of “month, day, hour, and number of occurrence”
I just have to iterate through the month and day…… then have a count of the hourly data so I would know the rate of deadlocks per hour.

But I need some help from a shell script expert :p One of the internal Unix/DBA admins helped me and finished it just the way I wanted it (Thanks a lot Mel! and for integrating Kyle’s script)

Here is the script (I’ve used this on AIX 5L with Oracle 10.2.0.4, use at your own risk. If I have more time I could test this on Linux and other database versions, or if you have modified version of this script, let me know )

So now I could directly parse on the alert log, search for the search string that I want (most likely an ORA- error code).. then output the number of occurrence… (hours with count=0 is not shown)

See the sample below… I’m searching for the exact error message “ORA-00060″ on the alert log.

karao@karl:~/Desktop$ ./AlertCount.sh alert_orcl.log ORA-00060
Jul 6 19 , 10
Jul 6 20 , 120
Jul 6 21 , 130
Jul 6 22 , 120
Jul 6 23 , 130
Jul 7 00 , 120
Jul 7 01 , 119
Jul 7 02 , 120
Jul 7 03 , 130
Jul 7 04 , 120
Jul 7 05 , 130
Jul 7 06 , 120
Jul 7 07 , 80
Jul 31 11 , 87
Aug 1 08 , 20
Aug 7 10 , 10
Aug 7 11 , 50

Then search for the word “Deadlock” that gives the same output..

karao@karl:~/Desktop$ ./AlertCount.sh alert_orcl.log Deadlock
Jul 6 19 , 10
Jul 6 20 , 120
Jul 6 21 , 130
Jul 6 22 , 120
Jul 6 23 , 130
Jul 7 00 , 120
Jul 7 01 , 119
Jul 7 02 , 120
Jul 7 03 , 130
Jul 7 04 , 120
Jul 7 05 , 130
Jul 7 06 , 120
Jul 7 07 , 80
Jul 31 11 , 87
Aug 1 08 , 20
Aug 7 10 , 10
Aug 7 11 , 50

Looking at the raw data.. this gives me a hint that on July 6-7 there were a lot of Deadlock occurrence. Then it’s possible that they have fixed some code which cut the Deadlock count to half on August. Also you’ll notice that from July 7-31 there were no Deadlock errors (I wonder why). And, could there be a periodic batch process running on every 7th of the month? Well, we still have to further investigate.. but at least we have the numbers to help us..

Also, to have a more meaningful data.. you could show your boss a flashy graph.. just spool the output to a .csv file then open it on MS Excel

karao@karl:~/Desktop$ ./AlertCount.sh alert_orcl.log ORA-00060 > ORA-00060.csv

Select the data then graph it

Here’s the graph…. That’s the “trend” that I want to see!

But the troubleshooting does not stop here.. I have to drill down on the specific hours, then look into the trace files. Or make use of some other tools or source of info that could help me identify the root cause..

And I’m starting to read the posts of Doug and Miladin, I’m sure I’ll get something useful on their posts.

Doug Burns
http://oracledoug.com/serendipity/index.php?/archives/1413-Good-News-for-ASH-Fans.html
http://oracledoug.com/serendipity/index.php?/archives/1478-Diagnosing-Locking-Problems-using-ASH-Part-2.html
http://oracledoug.com/serendipity/index.php?/archives/1480-Diagnosing-Locking-Problems-using-ASH-Part-3.html
http://oracledoug.com/serendipity/index.php?/archives/1481-Diagnosing-Locking-Problems-using-ASH-Part-4.html
http://oracledoug.com/serendipity/index.php?/archives/1484-Diagnosing-Locking-Problems-using-ASH-Part-5.html
http://oracledoug.com/serendipity/index.php?/archives/1487-Diagnosing-Locking-Problems-using-ASH-Part-6.html
http://oracledoug.com/serendipity/index.php?/archives/1488-Diagnosing-Locking-Problems-using-ASHLogMiner-Part-7.html
http://oracledoug.com/serendipity/index.php?/archives/1491-Diagnosing-Locking-Problems-using-ASHLogMiner-Part-8.html
http://oracledoug.com/serendipity/index.php?/archives/1492-Diagnosing-Locking-Problems-using-ASHLogMiner-Part-9.html
http://oracledoug.com/serendipity/index.php?/archives/1495-Diagnosing-Locking-Problems-using-ASHLogMiner-The-End.html

Miladin Modrakovic
http://oraclue.com/2009/04/20/detecting-deadlock-source/
http://oraclue.com/2009/04/23/detecting-deadlock-source-part-2/
.
.

Amazon sells it for 2400pesos..

It’s a pretty old book and was published around 1995-1996 but the content is still interesting. This led me to research more on the author and all of his work. Well hey! This guy is a recipient of the Michelson Award by Computer Measurement Group (CMG) I strongly suggest you subscribe to the monthly MeasureIT publication.. lots of good stuff on performance. The name of the mystery guy is Adrian Cockcroft… he got the award year 2007 then Neil Gunther on the next year. Cool so Adrian’s book must be a real contributing factor. Plus, he’s blogging at http://perfcap.blogspot.com

You could view Adrian and Neil’s acceptance speech on these links:

Adrian Cockcroft http://perfcap.blogspot.com/2007/12/a-michelson-award-acceptance-speech.html
Neil Gunther http://perfdynamics.blogspot.com/2008/12/gunther-receives-aa-michelson-award.html

And talking about Solaris performance. Below are some more personalities on the area of performance.

Brendan Gregg http://www.brendangregg.com, http://www.brendangregg.com/Perf/solarismetrics.html, http://blogs.sun.com/brendan, http://bdgregg.blogspot.com
Jim Mauro and Richard McDougall http://www.solarisinternals.com, http://blog.richardmcdougall.com

Below is the summary of the setup:
- Server and Storage: SunFire X4200 with LUNs on EMC CX300
- OS: RHEL 4.3 ES
- Oracle 10.2.0.3 (database and clusterware)
- Database Files, Flash Recovery Area, OCR, and Voting disk are located on OCFS2 filesystems
- Application: Forms and Reports (6i and also lower)

As per the DBA, the workload on the database was normal and there were no changes on the RAC nodes and on the applications. Hmm, I can’t really tell because I haven’t really looked into their workload so I don’t have past data to compare.

I first setup the OS Watcher on both nodes (with 60sec snapshot interval) to have an end-to-end view of the server performance while I’m doing the diagnosis on the database. It’s a cool tool and automatically gathers a lot of info when you start it (cpu, io, memory, network, process info), also this tool has a graphing facility which makes it faster to detect spikes and any trends.

Then, I looked into the database parameters and compared it with the RDA of the database I had before. This is just to check if there are any changes on the parameters that might cause the sudden slow down. Hmm…there was nothing new

Then, I queried on the V$SESSION

SQL> select count(*) from v$session;		-- server1

  COUNT(*)
----------
       385

SQL> select count(*) from v$session;		-- server2

  COUNT(*)
----------
	49

.
I was surprised! The session count on both servers show that there are more users connected on server1 (almost 89% of the total users). This could be because of
1) the clients having lower versions (< Sql*Plus 8.1 or OCI8, see Note 97926.1) that may not support TAF (FAILOVER_MODE) and Load Balancing (LOAD_BALANCE) or
2) they are using TNS entries explicitly connecting to server1
Since the RAC database has different applications connected to it, knowing which applications are on server1 and how many they are will be enough to determine which users on particular applications could be transferred to server2 to at least balance the number of users on both nodes, but doing this may still not solve the issue as I don’t have enough info that this is the root cause of the problem..

The query below shows that all the users don’t have FAILOVER capabilities which could be attributed by the two points I mentioned above

select distinct s.inst_id instance_id, s.failover_type failover_type, s.failover_method
failover_method, s.failed_over failed_over
from gv$session s;
INSTANCE_ID FAILOVER_TYPE        FAILOVER_METHOD      FAILED_OVER
----------- -------------------- -------------------- --------------------
          1 NONE                 NONE                 NO
          2 NONE                 NONE                 NO

.
Below query shows the distinct applications/modules and their count which are all on server1. Also you’ll notice that most of them are inactive (some of these users should connect to server2 to have balance on both nodes)

SQL> select distinct inst_id, module, program, status, count(*)
from gv$session
group by inst_id, module, program, status
order by 1,2,3,4;

   INST_ID MODULE					      PROGRAM						 STATUS       COUNT(*)
---------- -------------------------------------------------- -------------------------------------------------- ---------- ----------
	 1 F45RUN32.EXE 				      F45RUN32.EXE					 INACTIVE	    23		 

... output snipped ....

	 1 xxxPgm					      xxxPgm						 INACTIVE	    21		

... output snipped ....

	 1 c:\xxxwin95\BIN\F45RUN32.EXE L:\xxx\VS\FORMS\MAI   c:\xxxwin95\BIN\F45RUN32.EXE L:\xxx\VS\FORMS\MAI	 INACTIVE	    44
	 1 c:\xxxwin95\BIN\F45RUN32.EXE L:\MAIN.FMX	      c:\xxxwin95\BIN\F45RUN32.EXE L:\MAIN.FMX		 INACTIVE	    31
	 1 c:\xxxwin95\BIN\F45RUN32.EXE w:\xxxMENU.fmx       c:\xxxwin95\BIN\F45RUN32.EXE w:\xxxMENU.fmx	 INACTIVE	    14
	 1 c:\xxxwin95\BIN\F45RUN32.EXE w:\xxxlogin.fmx       c:\xxxwin95\BIN\F45RUN32.EXE w:\xxxlogin.fmx	 INACTIVE	    96	

... output snipped ....

	 1 c:\xxxwin95\BIN\R25SRV32.exe 		      c:\xxxwin95\BIN\R25SRV32.exe			 INACTIVE	    54		

... output snipped ....

	 1 ifweb90.exe					      ifweb90.exe					 INACTIVE	    11

.
Below info shows some data samples of DBA_HIST_ACTIVE_SESS_HISTORY which is graphed using Tanel Poder’s Perfsheet (could be found here and here) to clearly distinguish the distribution of the modules and number of users. Most of the users on server1 uses the “c:\xxxwin95\BIN\F45RUN32.EXE w:\xxxlogin.fmx”

Having the graph above may lead us to conclusion that the significant number of users on server1 attributes to the slow down of the transactions. But as per the DBA, there were no changes made and they were running the same transactions as before on server1 which has acceptable response time. Also the OS Watcher shows that the CPU utilization (peak is 60% and most of the time at 40%) and run queue was low and the disk IO utilization and service time were average (always 10ms below), and there were enough memory on the server (3GB free out of 12GB.. the SGA is 2GB) and no swapping.

Hmm…Drilling down on the wait interface and on per session level that is doing the important business operations will give us a definite conclusion on what is really the bottleneck on the database.

The graph below is another sample from DBA_HIST_ACTIVE_SESS_HISTORY that shows server1 (in blue box) is suffering from “gc cr block lost” and “gc cr multi block request” from 7am to 4pm. The “Metalink Doc ID: 563566.1 gc lost blocks diagnostics” indicates that it is a cluster problem which could be a problem on the network interconnect

To confirm the bottleneck shown from the sample of DBA_HIST_ACTIVE_SESS_HISTORY (above), the output of ADDM and AWR report from a peak period was analyzed (from 2-3 & 3-4 pm). Below are the output of ADDM and AWR reports:

ADDM output:

          DETAILED ADDM REPORT FOR TASK 'TASK_137854' WITH ID 137854
          ----------------------------------------------------------

              Analysis Period: 28-MAY-2009 from 13:58:59 to 14:59:12
         Database ID/Instance: 3967623528/2
      Database/Instance Names: xxx
                    Host Name: xxx
             Database Version: 10.2.0.3.0
               Snapshot Range: from 15642 to 15643
                Database Time: 3710 seconds
        Average Database Load: 1 active sessions

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

FINDING 1: 52% impact (1917 seconds)
------------------------------------
Cluster multi-block requests were consuming significant database time.

   RECOMMENDATION 1: SQL Tuning, 37% benefit (1389 seconds)
      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
         "0h0fn2d19adtk". Look for an alternative plan that does not use
         object scans.
         RELEVANT OBJECT: SQL statement with SQL_ID 0h0fn2d19adtk

         ... output snipped ... 

   RECOMMENDATION 2: SQL Tuning, 8.4% benefit (310 seconds)
      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
         "8jd43xr4rp00u". Look for an alternative plan that does not use
         object scans.
         RELEVANT OBJECT: SQL statement with SQL_ID 8jd43xr4rp00u

         ... output snipped ... 

   RECOMMENDATION 3: SQL Tuning, 4.6% benefit (172 seconds)
      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
         "6wpgf3s0vzuks". Look for an alternative plan that does not use
         object scans.
         RELEVANT OBJECT: SQL statement with SQL_ID 6wpgf3s0vzuks

         ... output snipped ... 

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Inter-instance messaging was consuming significant database
               time on this instance. (94% impact [3488 seconds])
         SYMPTOM: Wait class "Cluster" was consuming significant database
                  time. (94% impact [3488 seconds])

FINDING 2: 51% impact (1874 seconds)
------------------------------------
Global Cache Service Processes (LMSn) in other instances were not processing
requests fast enough.

   RECOMMENDATION 1: DB Configuration, 51% benefit (1874 seconds)
      ACTION: Increase throughput of the Global Cache Service (LMSn)
         processes. Increase the number of Global Cache Service processes by
         increasing the value of the parameter "gcs_server_processes".
         Alternatively, if the host is CPU bound consider increasing the OS
         priority of the Global Cache Service processes.
      RATIONALE: The value of parameter "gcs_server_processes" was "2" during
         the analysis period.

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Inter-instance messaging was consuming significant database
               time on this instance. (94% impact [3488 seconds])
         SYMPTOM: Wait class "Cluster" was consuming significant database
                  time. (94% impact [3488 seconds])

FINDING 3: 36% impact (1330 seconds)
------------------------------------
Cluster communications that were retried due to lost blocks consumed
significant database time.

   RECOMMENDATION 1: Host Configuration, 36% benefit (1330 seconds)
      ACTION: Check the configuration of the cluster interconnect. Check OS
         setup like adapter setting, firmware and driver release. Check that
         the OS's socket receive buffers are large enough to store an entire
         multiblock read. The value of parameter
         "db_file_multiblock_read_count" may be decreased as a workaround.
      RATIONALE: The instance was consuming 477 kilo bits per second of
         interconnect bandwidth.

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Inter-instance messaging was consuming significant database
               time on this instance. (94% impact [3488 seconds])
         SYMPTOM: Wait class "Cluster" was consuming significant database
                  time. (94% impact [3488 seconds])

#######################################################################

          DETAILED ADDM REPORT FOR TASK 'TASK_137874' WITH ID 137874
          ----------------------------------------------------------

              Analysis Period: 28-MAY-2009 from 14:59:12 to 15:58:44
         Database ID/Instance: 3967623528/2
      Database/Instance Names: xxx
                    Host Name: xxx
             Database Version: 10.2.0.3.0
               Snapshot Range: from 15643 to 15644
                Database Time: 1563 seconds
        Average Database Load: .4 active sessions

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

FINDING 1: 42% impact (654 seconds)
-----------------------------------
Cluster communications that were retried due to lost blocks consumed
significant database time.

   RECOMMENDATION 1: Host Configuration, 42% benefit (654 seconds)
      ACTION: Check the configuration of the cluster interconnect. Check OS
         setup like adapter setting, firmware and driver release. Check that
         the OS's socket receive buffers are large enough to store an entire
         multiblock read. The value of parameter
         "db_file_multiblock_read_count" may be decreased as a workaround.
      RATIONALE: The instance was consuming 134 kilo bits per second of
         interconnect bandwidth.

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Inter-instance messaging was consuming significant database
               time on this instance. (88% impact [1371 seconds])
         SYMPTOM: Wait class "Cluster" was consuming significant database
                  time. (88% impact [1372 seconds])

.
AWR output:

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
xxxxx         3967623528 xxxxxx              2 10.2.0.3.0  YES xxx

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     15642 28-May-09 13:58:58        57      27.0
  End Snap:     15643 28-May-09 14:59:12        64      28.7
   Elapsed:               60.23 (mins)
   DB Time:               61.83 (mins)

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
gc cr multi block request            19,644       1,902     97   51.3    Cluster
gc cr block lost                      1,011       1,117   1104   30.1    Cluster
enq: WF - contention                    626         268    429    7.2      Other
gc current block lost                   186         203   1089    5.5    Cluster
cr request retry                        678         162    240    4.4      Other

Per Wait Class
~~~~~~~~~~~~~~~~~~
                                                                  Avg
                                       %Time       Total Wait    wait     Waits
Wait Class                      Waits  -outs         Time (s)    (ms)      /txn
-------------------- ---------------- ------ ---------------- ------- ---------
Cluster                        34,450    7.0            3,385      98       6.8
Other                          91,292   64.7              467       5      18.1
User I/O                       26,510     .0              102       4       5.3
Concurrency                    29,668     .0               54       2       5.9
System I/O                     13,360     .0               11       1       2.6
Commit                          1,313     .0                4       3       0.3
Application                     2,374     .0                2       1       0.5
Network                       129,774     .0                0       0      25.7
Configuration                      20     .0                0       0       0.0

#######################################################################

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
xxxxx         3967623528 xxxxxx              2 10.2.0.3.0  YES xxx

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     15643 28-May-09 14:59:12        64      28.7
  End Snap:     15644 28-May-09 15:58:43        64      29.6
   Elapsed:               59.53 (mins)
   DB Time:               26.05 (mins)

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
gc cr block lost                        389         429   1103   27.5    Cluster
gc cr multi block request             2,360         422    179   27.0    Cluster
enq: WF - contention                    510         212    416   13.6      Other
gc current block lost                   188         204   1084   13.0    Cluster
cr request retry                        326         181    554   11.6      Other

Per Wait Class
~~~~~~~~~~~~~~~~~~
                                                                  Avg
                                       %Time       Total Wait    wait     Waits
Wait Class                      Waits  -outs         Time (s)    (ms)      /txn
-------------------- ---------------- ------ ---------------- ------- ---------
Cluster                        10,239    8.9            1,247     122       2.1
Other                         153,082   71.9              468       3      31.0
Application                     1,784   18.0              161      90       0.4
Concurrency                    33,014     .0               53       2       6.7
User I/O                        4,246     .0               27       6       0.9
System I/O                     13,648     .0               14       1       2.8
Commit                          1,546     .0                4       3       0.3
Network                       138,333     .0                1       0      28.0
Configuration                      16     .0                0       4       0.0

.
The only thing that is left to do is to drill down on the complaining users and check on the network performance and interconnect… unfortunately the users were going home and I can’t see any significant database activity. So I called it a night, and just analyzed my activity log and read on Metalink Doc ID 563566.1. Also I advised the DBA to divide the users across the nodes.

Next day afternoon, and it’s time to do the drill down. You can see on the graph below (a sample from GV$SESSION) that the users were already distributed, but still there is a performance problem. I told you this will not solve the issue ! it is just a plus that we manage to distribute the users

A query on GV$SESSION_LONGOPS will show users with large SQLs. Drilling down on the session statistics of these user’s will give detail on what are they waiting and how much time they spent on what resource. However, it would be a repetitive step to get all the sessions on the GV$SESSION_LONGOPS.

select * from (
			select
			a.inst_id inst, a.START_TIME, b.osuser, b.module,  a.sid, a.serial#,
			a.opname, b.event, a.target, a.sofar, a.totalwork, round(a.sofar/a.totalwork, 4)*100 pct, a.units, a.elapsed_seconds, a.time_remaining time_remaining_sec, round(a.time_remaining/60,2) min
			,a.sql_hash_value
		-- 	,message
			from gv$session_longops a, gv$session b
			where a.sid = b.sid
			and b.serial# = b.serial#
			and sofar < totalwork
			order by totalwork desc)

INST START_TIME        OSUSER     MODULE             SID SERIAL# OPNAME     EVENT           TARGET          SOFAR  TOTALWORK        PCT UNITS      ELAPSED_SECONDS TIME_REMAINING_SEC        MIN SQL_HASH_VALUE
---- ----------------- ---------- --------------- ------ ------- ---------- --------------- ---------- ---------- ---------- ---------- ---------- --------------- ------------------ ---------- --------------
   2 20090529 18:36:38 oraprod    oracle@xxxxx280    454      82 Table Scan gc cr multi blo xxxxxxxx.B       2205      25403       8.68 Blocks                 343               3609      60.15     1857468103
                                  R (TNS V1-V3)                             ck request      ENMAS

   2 20090529 18:30:39 oraprod    oracle@xxxxx280    607     546 Table Scan gc cr multi blo xxxxxxxx.C      11062      15203      72.76 Blocks                 708                265       4.42     1477847824
                                  R (TNS V1-V3)                             ck request      OVMAS

   1 20090529 15:51:58 PC829      c:\xxxwin95\BIN    474     721 Table Scan gc cr request   xxxxxxxx.C      11108      15203      73.06 Blocks               10317               3803      63.38     1636678807
                                  \F45RUN32.EXE w                                           OVMAS
                                  : \xxxlogin.fmx

   1 20090529 17:27:49 PC1280     c:\xxxwin95\BIN    473     171 Table Scan gc cr request   xxxxxx.DTT        336      12918        2.6 Blocks                4449             166599    2776.65     1117075250
                                  \R25SRV32.exe                                             _DVDTL

   1 20090529 17:27:49 oracle     sqlplus@server2    473     171 Table Scan PX Deq: Executi xxxxxx.DTT        336      12918        2.6 Blocks                4449             166599    2776.65     1117075250
                                  .xxxx.xxx (TNS                           on Msg          _DVDTL
                                   V1-V3)

.
I already have an idea that my problem is on the cluster configuration. So I asked the DBA that we go up to the Accounting Department (where there are a lot of users complaining) and check on the desktop terminals of the users. We saw two users which has the application module on total hang. These users are PC1069 (with SID 601) and PC918 (with SID 483). So a query on the GV$SESSION_LONGOPS does not show these users but a query on GV$SESSION_WAIT shows that they are waiting on “gc cr request” event

select s.inst_id, w.sid, s.sql_hash_value, s.osuser, s.program, w.event, w.seconds_in_wait seconds_in_wait, w.state, w.p1, w.p2, w.p3
from gv$session s, gv$session_wait w
where s.sid = w.sid and s.type = 'USER'
and w.state = 'WAITING'
and s.osuser in ('PC1069','PC918')
order by 6 asc;

   INST_ID    SID SQL_HASH_VALUE OSUSER     PROGRAM                 EVENT              SECONDS_IN_WAIT STATE                     P1         P2         P3
---------- ------ -------------- ---------- ----------------------- ------------------ --------------- ----------------- ---------- ---------- ----------
         1    483      449494677 PC918      c:\xxxwin95\BIN\F45RUN3 SQL*Net message fr            3853 WAITING            675562835          1          0
                                            2.EXE w:\xxxlogin.fmx   om client

         1    601     1874068660 PC1069     c:\xxxwin95\BIN\F45RUN3 gc cr request                    6 WAITING                   12    1173863          1
                                            2.EXE w:\xxxlogin.fmx

         1    483      449494677 PC918      c:\xxxwin95\BIN\F45RUN3 gc cr request                    0 WAITING                    7    1649989          1
                                            2.EXE w:\xxxlogin.fmx

.
Focusing on these two sessions (SID 601 and 483) and getting their session statistics will give us an idea if they are doing something, on a total hang, or waiting for something. Below query on V$SESSION_WAIT shows that the SEQ# is being incremented (a number that uniquely identifies this wait) which means the session (SID 601) is still waiting for something, the event “gc cr request”

BTW, I’m using SW script of Tanel Poder (downloadable here), and you’ll also see some usage of Snapper and USQL

SQL> @sw 601

    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT         P1         P2         P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ---------- ---------- ---------- ------------------------------------------
    601 WAITING gc cr request                                 27223           3          7    1647259          1

... output snipped ...

SQL> @sw 601

    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT         P1         P2         P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ---------- ---------- ---------- ------------------------------------------
    601 WAITING gc cr request                                 27225           0          7    1647259          1

... output snipped ...

SQL> @sw 601

    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT         P1         P2         P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ---------- ---------- ---------- ------------------------------------------
    601 WAITING gc cr request                                 27227           0          7    1647259          1

... output snipped ...

SQL> @sw 601

    SID STATE   EVENT                                          SEQ# SEC_IN_WAIT         P1         P2         P3 P1TRANSL
------- ------- ---------------------------------------- ---------- ----------- ---------- ---------- ---------- ------------------------------------------
    601 WAITING gc cr request                                 27229           7          7    1647259          1

.
A query on V$SESSTAT will show the performance/wait counters being incremented (SID 601), the output below was a snap of the session for 5 seconds period done 5 times. You’ll see that “gc current” waits are significant

SQL> @snapper out 5 5 601
-- Session Snapper v1.10 by Tanel Poder ( http://www.tanelpoder.com )
---------------------------------------------------------------------------------------------------------------------------------------------
HEAD,     SID, SNAPSHOT START   ,  SECONDS, TYPE, STATISTIC                               ,         DELTA,  DELTA/SEC,     HDELTA, HDELTA/SEC
---------------------------------------------------------------------------------------------------------------------------------------------
DATA,     601, 20090529 18:48:36,        6, STAT, opened cursors current                  ,            -1,         -0,         -1,       -.17
DATA,     601, 20090529 18:48:36,        6, STAT, recursive calls                         ,             5,          1,          5,        .83
DATA,     601, 20090529 18:48:36,        6, STAT, session logical reads                   ,            24,          4,         24,          4
DATA,     601, 20090529 18:48:36,        6, STAT, cluster wait time                       ,           492,         82,        492,         82
DATA,     601, 20090529 18:48:36,        6, STAT, user I/O wait time                      ,             1,          0,          1,        .17
DATA,     601, 20090529 18:48:36,        6, STAT, session pga memory                      ,        -65536,     -10923,    -65.54k,    -10.92k
DATA,     601, 20090529 18:48:36,        6, STAT, physical read total IO requests         ,             1,          0,          1,        .17
DATA,     601, 20090529 18:48:36,        6, STAT, physical read total bytes               ,          8192,       1365,      8.19k,      1.37k
DATA,     601, 20090529 18:48:36,        6, STAT, gcs messages sent                       ,             3,          1,          3,         .5
DATA,     601, 20090529 18:48:36,        6, STAT, db block gets                           ,             6,          1,          6,          1
DATA,     601, 20090529 18:48:36,        6, STAT, db block gets from cache                ,             6,          1,          6,          1
DATA,     601, 20090529 18:48:36,        6, STAT, consistent gets                         ,            18,          3,         18,          3
DATA,     601, 20090529 18:48:36,        6, STAT, consistent gets from cache              ,            18,          3,         18,          3
DATA,     601, 20090529 18:48:36,        6, STAT, consistent gets - examination           ,             8,          1,          8,       1.33
DATA,     601, 20090529 18:48:36,        6, STAT, physical reads                          ,             1,          0,          1,        .17
DATA,     601, 20090529 18:48:36,        6, STAT, physical reads cache                    ,             1,          0,          1,        .17
DATA,     601, 20090529 18:48:36,        6, STAT, physical read IO requests               ,             1,          0,          1,        .17
DATA,     601, 20090529 18:48:36,        6, STAT, physical read bytes                     ,          8192,       1365,      8.19k,      1.37k
DATA,     601, 20090529 18:48:36,        6, STAT, db block changes                        ,             3,          1,          3,         .5
DATA,     601, 20090529 18:48:36,        6, STAT, free buffer requested                   ,             3,          1,          3,         .5
DATA,     601, 20090529 18:48:36,        6, STAT, calls to get snapshot scn: kcmgss       ,             5,          1,          5,        .83
DATA,     601, 20090529 18:48:36,        6, STAT, redo entries                            ,             2,          0,          2,        .33
DATA,     601, 20090529 18:48:36,        6, STAT, redo size                               ,           536,         89,        536,      89.33
DATA,     601, 20090529 18:48:36,        6, STAT, gc current blocks received              ,             1,          0,          1,        .17
DATA,     601, 20090529 18:48:36,        6, STAT, gc remote grants                        ,             1,          0,          1,        .17
DATA,     601, 20090529 18:48:36,        6, STAT, undo change vector size                 ,            84,         14,         84,         14
DATA,     601, 20090529 18:48:36,        6, STAT, no work - consistent read gets          ,             9,          2,          9,        1.5
DATA,     601, 20090529 18:48:36,        6, STAT, table fetch by rowid                    ,             4,          1,          4,        .67
DATA,     601, 20090529 18:48:36,        6, STAT, index scans kdiixs1                     ,             4,          1,          4,        .67
DATA,     601, 20090529 18:48:36,        6, STAT, buffer is not pinned count              ,             8,          1,          8,       1.33
DATA,     601, 20090529 18:48:36,        6, STAT, execute count                           ,             4,          1,          4,        .67
DATA,     601, 20090529 18:48:36,        6, TIME, PL/SQL execution elapsed time           ,           139,         23,      139us,    23.17us
DATA,     601, 20090529 18:48:36,        6, TIME, DB CPU                                  ,         10000,       1667,       10ms,     1.67ms
DATA,     601, 20090529 18:48:36,        6, TIME, sql execute elapsed time                ,      27552125,    4592021,     27.55s,      4.59s
DATA,     601, 20090529 18:48:36,        6, TIME, DB time                                 ,      27552133,    4592022,     27.55s,      4.59s
DATA,     601, 20090529 18:48:36,        6, WAIT, db file sequential read                 ,          8374,       1396,     8.37ms,      1.4ms
DATA,     601, 20090529 18:48:36,        6, WAIT, gc current request                      ,       9664835,    1610806,      9.66s,      1.61s
--  End of snap 1
DATA,     601, 20090529 18:48:42,        5, STAT, opened cursors cumulative               ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, STAT, opened cursors current                  ,            -1,         -0,         -1,        -.2
DATA,     601, 20090529 18:48:42,        5, STAT, recursive calls                         ,             4,          1,          4,         .8
DATA,     601, 20090529 18:48:42,        5, STAT, recursive cpu usage                     ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, STAT, session logical reads                   ,            42,          8,         42,        8.4
DATA,     601, 20090529 18:48:42,        5, STAT, cluster wait time                       ,           419,         84,        419,       83.8
DATA,     601, 20090529 18:48:42,        5, STAT, user I/O wait time                      ,             2,          0,          2,         .4
DATA,     601, 20090529 18:48:42,        5, STAT, enqueue requests                        ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, STAT, global enqueue gets sync                ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, STAT, physical read total IO requests         ,             2,          0,          2,         .4
DATA,     601, 20090529 18:48:42,        5, STAT, physical read total bytes               ,         16384,       3277,     16.38k,      3.28k
DATA,     601, 20090529 18:48:42,        5, STAT, gcs messages sent                       ,             2,          0,          2,         .4
DATA,     601, 20090529 18:48:42,        5, STAT, db block gets                           ,            41,          8,         41,        8.2
DATA,     601, 20090529 18:48:42,        5, STAT, db block gets from cache                ,            41,          8,         41,        8.2
DATA,     601, 20090529 18:48:42,        5, STAT, consistent gets                         ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, STAT, consistent gets from cache              ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, STAT, physical reads                          ,             2,          0,          2,         .4
DATA,     601, 20090529 18:48:42,        5, STAT, physical reads cache                    ,             2,          0,          2,         .4
DATA,     601, 20090529 18:48:42,        5, STAT, physical read IO requests               ,             2,          0,          2,         .4
DATA,     601, 20090529 18:48:42,        5, STAT, physical read bytes                     ,         16384,       3277,     16.38k,      3.28k
DATA,     601, 20090529 18:48:42,        5, STAT, db block changes                        ,            26,          5,         26,        5.2
DATA,     601, 20090529 18:48:42,        5, STAT, exchange deadlocks                      ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, STAT, free buffer requested                   ,             3,          1,          3,         .6
DATA,     601, 20090529 18:48:42,        5, STAT, calls to get snapshot scn: kcmgss       ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, STAT, redo entries                            ,            14,          3,         14,        2.8
DATA,     601, 20090529 18:48:42,        5, STAT, redo size                               ,          3504,        701,       3.5k,      700.8
DATA,     601, 20090529 18:48:42,        5, STAT, redo subscn max counts                  ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, STAT, gc local grants                         ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, STAT, gc remote grants                        ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, STAT, undo change vector size                 ,          1140,        228,      1.14k,        228
DATA,     601, 20090529 18:48:42,        5, STAT, deferred (CURRENT) block cleanout applic,             4,          1,          4,         .8
DATA,     601, 20090529 18:48:42,        5, STAT, parse count (total)                     ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, STAT, execute count                           ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:42,        5, TIME, parse time elapsed                      ,            41,          8,       41us,      8.2us
DATA,     601, 20090529 18:48:42,        5, TIME, PL/SQL execution elapsed time           ,           125,         25,      125us,       25us
DATA,     601, 20090529 18:48:42,        5, TIME, sql execute elapsed time                ,       5433501,    1086700,      5.43s,      1.09s
DATA,     601, 20090529 18:48:42,        5, TIME, DB time                                 ,       5433501,    1086700,      5.43s,      1.09s
DATA,     601, 20090529 18:48:42,        5, WAIT, db file sequential read                 ,         17812,       3562,    17.81ms,     3.56ms
DATA,     601, 20090529 18:48:42,        5, WAIT, gc current request                      ,      -4221489,    -844298,     -4.22s,   -844.3ms
DATA,     601, 20090529 18:48:42,        5, WAIT, gc current block lost                   ,       6634963,    1326993,      6.63s,      1.33s
DATA,     601, 20090529 18:48:42,        5, WAIT, gc current grant 2-way                  ,           254,         51,      254us,     50.8us
DATA,     601, 20090529 18:48:42,        5, WAIT, events in waitclass Other               ,           600,        120,      600us,      120us
--  End of snap 2
DATA,     601, 20090529 18:48:47,        5, STAT, session logical reads                   ,             3,          1,          3,         .6
DATA,     601, 20090529 18:48:47,        5, STAT, cluster wait time                       ,           503,        101,        503,      100.6
DATA,     601, 20090529 18:48:47,        5, STAT, gcs messages sent                       ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:47,        5, STAT, db block gets                           ,             3,          1,          3,         .6
DATA,     601, 20090529 18:48:47,        5, STAT, db block gets from cache                ,             3,          1,          3,         .6
DATA,     601, 20090529 18:48:47,        5, STAT, exchange deadlocks                      ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:47,        5, STAT, free buffer requested                   ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:47,        5, WAIT, gc current request                      ,       1221742,     244348,      1.22s,   244.35ms
DATA,     601, 20090529 18:48:47,        5, WAIT, gc current block lost                   ,       3813435,     762687,      3.81s,   762.69ms
DATA,     601, 20090529 18:48:47,        5, WAIT, events in waitclass Other               ,           609,        122,      609us,    121.8us
--  End of snap 3
DATA,     601, 20090529 18:48:52,        5, STAT, session logical reads                   ,             3,          1,          3,         .6
DATA,     601, 20090529 18:48:52,        5, STAT, cluster wait time                       ,           524,        105,        524,      104.8
DATA,     601, 20090529 18:48:52,        5, STAT, gcs messages sent                       ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:52,        5, STAT, db block gets                           ,             3,          1,          3,         .6
DATA,     601, 20090529 18:48:52,        5, STAT, db block gets from cache                ,             3,          1,          3,         .6
DATA,     601, 20090529 18:48:52,        5, STAT, exchange deadlocks                      ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:52,        5, STAT, free buffer requested                   ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:52,        5, TIME, sql execute elapsed time                ,       6613095,    1322619,      6.61s,      1.32s
DATA,     601, 20090529 18:48:52,        5, TIME, DB time                                 ,       6613095,    1322619,      6.61s,      1.32s
DATA,     601, 20090529 18:48:52,        5, WAIT, gc current request                      ,       3000843,     600169,         3s,   600.17ms
DATA,     601, 20090529 18:48:52,        5, WAIT, gc current block lost                   ,       4018577,     803715,      4.02s,   803.72ms
DATA,     601, 20090529 18:48:52,        5, WAIT, events in waitclass Other               ,           570,        114,      570us,      114us
--  End of snap 4
DATA,     601, 20090529 18:48:57,        5, STAT, session logical reads                   ,             3,          1,          3,         .6
DATA,     601, 20090529 18:48:57,        5, STAT, cluster wait time                       ,           523,        105,        523,      104.6
DATA,     601, 20090529 18:48:57,        5, STAT, gcs messages sent                       ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:57,        5, STAT, db block gets                           ,             3,          1,          3,         .6
DATA,     601, 20090529 18:48:57,        5, STAT, db block gets from cache                ,             3,          1,          3,         .6
DATA,     601, 20090529 18:48:57,        5, STAT, exchange deadlocks                      ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:57,        5, STAT, free buffer requested                   ,             1,          0,          1,         .2
DATA,     601, 20090529 18:48:57,        5, TIME, sql execute elapsed time                ,       5230170,    1046034,      5.23s,      1.05s
DATA,     601, 20090529 18:48:57,        5, TIME, DB time                                 ,       5230170,    1046034,      5.23s,      1.05s
DATA,     601, 20090529 18:48:57,        5, WAIT, gc current request                      ,          -473,        -95,     -473us,    -94.6us
DATA,     601, 20090529 18:48:57,        5, WAIT, gc current block lost                   ,       5230137,    1046027,      5.23s,      1.05s
DATA,     601, 20090529 18:48:57,        5, WAIT, events in waitclass Other               ,           390,         78,      390us,       78us
--  End of snap 5

PL/SQL procedure successfully completed.

.
Below is the SQL being executed for SID 601 which is a batch INSERT operation that is just a normal operation as per the DBA

SQL> @usql 601

HASH_VALUE  CH# SQL_TEXT
---------- ---- ------------------------------------------------------------------------------------------------------------------------------------------------------
   6143262    0 INSERT INTO xxx(
                BATxxx,POLxxx,SRCxxx,PAYxxx,DATxxx,DATxxx,AMxxxx,ACCxxx,NEWxxx,POLxxx,FUNxxx,Uxxxxx,Txxxx,Cxxxx,PRxxxx,Exxxx,xxxxID,POxxxx,Nxxxx,xxxAMT,xxxPRM,xxxMAV
                ,COLxxxx,NOPxxx )  VALUES ( :b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15,:b16,:b17,:b18,:b19,:b20,:b21,:b22,:b23,:b24  )

 CH# PARENT_HANDLE    OBJECT_HANDLE        PARSES   H_PARSES EXECUTIONS    FETCHES ROWS_PROCESSED       LIOS       PIOS      SORTS     CPU_MS     ELA_MS USERS_EXECUTING
---- ---------------- ---------------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------
   0 00000000D0A5FC68 00000000D0A5FA40         10          1       2714          0           2714     300753      17731          0  12136.359  5899798.3               1

SQL hash value:         6143262    Cursor address:  00000000D0A5FC68    |   Statement first parsed at: 2009-05-29/13:01:42  |  20903 seconds ago

.
Below is the SQL being executed for SID 483 which is a SELECT operation that is just a normal operation as per the DBA

SQL> @usql 483

HASH_VALUE  CH# SQL_TEXT
---------- ---- ------------------------------------------------------------------------------------------------------------------------------------------------------
 449494677    0 SELECT x.xxxPxD,F.xxxNMx,B.xxxPxD,x.xxxNMx,D.PxLMxD,D.PxLFRQ,x.xxVxMT,x.xxVSTx,x.xxVxFF,x.xxVDxx,SxM(G.xxVMxN) MxDPRxM   FRxM RxPMxS x,BxNMxS B,xxxMxS
                x,PxLMxS D,xxVMxS x,xxxMxS F,xxVMxS G  WHxRx x.PxLNxM = NVL(:b1,'X')  xND x.RxLTYP = 'PxY'  xND (x.RxLPRx = 100  xR x.RxLPRx xS NxLL  ) xND x.xxxPxD =
                F.xxxPxD  xND B.PxLNxM (+)   = NVL(:b2,'X')  xND B.RxLTYP (+)   = 'xNS'  xND B.xxVNxM = 1  xND (B.RxLPxR = 100  xR B.RxLPxR xS NxLL  ) xND B.xxxPxD =
                x.xxxPxD (+)    xND D.PxLNxM = NVL(:b3,'X')  xND x.PxLNxM = NVL(:b4,'X')  xND x.xxVNxM = 1  xND G.PxLNxM = NVL(:b5,'X')  GRxxP BY
                x.xxxPxD,F.xxxNMx,B.xxxPxD,x.xxxNMx,D.PxLMxD,D.PxLFRQ,x.xxVxMT,x.xxVSTx,x.xxVxFF,x.xxVDxx

 CH# PARENT_HANDLE    OBJECT_HANDLE        PARSES   H_PARSES EXECUTIONS    FETCHES ROWS_PROCESSED       LIOS       PIOS      SORTS     CPU_MS     ELA_MS USERS_EXECUTING
---- ---------------- ---------------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------------
   0 00000000C2E54DD0 00000000C2E5E700          8          1       1047       2089           1043      58793       3905       2087   2676.616 11108807.7               1

.
A query on V$SESSTAT will show the performance/wait counters being incremented (SID 483), the output below was a snap of the session for 5 seconds period done 10 times. You’ll see that “gc cr” waits are significant

SQL> @snapper out 5 10 483
-- Session Snapper v1.10 by Tanel Poder ( http://www.tanelpoder.com )
---------------------------------------------------------------------------------------------------------------------------------------------
HEAD,     SID, SNAPSHOT START   ,  SECONDS, TYPE, STATISTIC                               ,         DELTA,  DELTA/SEC,     HDELTA, HDELTA/SEC
---------------------------------------------------------------------------------------------------------------------------------------------
DATA,     483, 20090529 19:00:17,        5, STAT, cluster wait time                       ,           527,        105,        527,      105.4
DATA,     483, 20090529 19:00:17,        5, STAT, gcs messages sent                       ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:17,        5, STAT, free buffer requested                   ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:17,        5, WAIT, gc cr request                           ,      -1220551,    -244110,     -1.22s,  -244.11ms
DATA,     483, 20090529 19:00:17,        5, WAIT, gc cr block lost                        ,       6485830,    1297166,      6.49s,       1.3s
DATA,     483, 20090529 19:00:17,        5, WAIT, events in waitclass Other               ,           530,        106,      530us,      106us
--  End of snap 1
DATA,     483, 20090529 19:00:22,        5, STAT, session logical reads                   ,             4,          1,          4,         .8
DATA,     483, 20090529 19:00:22,        5, STAT, cluster wait time                       ,           454,         91,        454,       90.8
DATA,     483, 20090529 19:00:22,        5, STAT, messages sent                           ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:22,        5, STAT, gcs messages sent                       ,             2,          0,          2,         .4
DATA,     483, 20090529 19:00:22,        5, STAT, consistent gets                         ,             4,          1,          4,         .8
DATA,     483, 20090529 19:00:22,        5, STAT, consistent gets from cache              ,             4,          1,          4,         .8
DATA,     483, 20090529 19:00:22,        5, STAT, consistent gets - examination           ,             3,          1,          3,         .6
DATA,     483, 20090529 19:00:22,        5, STAT, free buffer requested                   ,             2,          0,          2,         .4
DATA,     483, 20090529 19:00:22,        5, STAT, shared hash latch upgrades - no wait    ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:22,        5, STAT, gc current blocks received              ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:22,        5, STAT, no work - consistent read gets          ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:22,        5, STAT, table fetch by rowid                    ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:22,        5, STAT, index fetch by key                      ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:22,        5, STAT, buffer is not pinned count              ,             2,          0,          2,         .4
DATA,     483, 20090529 19:00:22,        5, WAIT, gc cr request                           ,      -8443706,   -1688741,     -8.44s,     -1.69s
DATA,     483, 20090529 19:00:22,        5, WAIT, gc cr block lost                        ,       6986370,    1397274,      6.99s,       1.4s
DATA,     483, 20090529 19:00:22,        5, WAIT, gc current block 2-way                  ,           596,        119,      596us,    119.2us
DATA,     483, 20090529 19:00:22,        5, WAIT, events in waitclass Other               ,           266,         53,      266us,     53.2us
--  End of snap 2
DATA,     483, 20090529 19:00:27,        5, STAT, cluster wait time                       ,           506,        101,        506,      101.2
DATA,     483, 20090529 19:00:27,        5, STAT, gcs messages sent                       ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:27,        5, STAT, free buffer requested                   ,             1,          0,          1,         .2
--  End of snap 3
DATA,     483, 20090529 19:00:32,        5, STAT, cluster wait time                       ,           488,         98,        488,       97.6
DATA,     483, 20090529 19:00:32,        5, WAIT, gc cr request                           ,       9888169,    1977634,      9.89s,      1.98s
--  End of snap 4
DATA,     483, 20090529 19:00:37,        5, STAT, cluster wait time                       ,           548,        110,        548,      109.6
DATA,     483, 20090529 19:00:37,        5, STAT, gcs messages sent                       ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:37,        5, STAT, free buffer requested                   ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:37,        5, WAIT, gc cr request                           ,      -4444128,    -888826,     -4.44s,  -888.83ms
DATA,     483, 20090529 19:00:37,        5, WAIT, gc cr block lost                        ,       7920880,    1584176,      7.92s,      1.58s
DATA,     483, 20090529 19:00:37,        5, WAIT, events in waitclass Other               ,           341,         68,      341us,     68.2us
--  End of snap 5
DATA,     483, 20090529 19:00:42,        5, STAT, cluster wait time                       ,           494,         99,        494,       98.8
DATA,     483, 20090529 19:00:42,        5, STAT, gcs messages sent                       ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:42,        5, STAT, free buffer requested                   ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:42,        5, WAIT, gc cr request                           ,        883591,     176718,   883.59ms,   176.72ms
DATA,     483, 20090529 19:00:42,        5, WAIT, gc cr block lost                        ,       4055130,     811026,      4.06s,   811.03ms
DATA,     483, 20090529 19:00:42,        5, WAIT, events in waitclass Other               ,           337,         67,      337us,     67.4us
--  End of snap 6
DATA,     483, 20090529 19:00:47,        6, STAT, cluster wait time                       ,           448,         75,        448,      74.67
DATA,     483, 20090529 19:00:47,        6, STAT, gcs messages sent                       ,             1,          0,          1,        .17
DATA,     483, 20090529 19:00:47,        6, STAT, free buffer requested                   ,             1,          0,          1,        .17
DATA,     483, 20090529 19:00:47,        6, WAIT, gc cr request                           ,      -2105578,    -350930,     -2.11s,  -350.93ms
DATA,     483, 20090529 19:00:47,        6, WAIT, gc cr block lost                        ,       6590281,    1098380,      6.59s,       1.1s
DATA,     483, 20090529 19:00:47,        6, WAIT, events in waitclass Other               ,           496,         83,      496us,    82.67us
--  End of snap 7
DATA,     483, 20090529 19:00:53,        5, STAT, cluster wait time                       ,           547,        109,        547,      109.4
DATA,     483, 20090529 19:00:53,        5, STAT, gcs messages sent                       ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:53,        5, STAT, free buffer requested                   ,             1,          0,          1,         .2
--  End of snap 8
DATA,     483, 20090529 19:00:58,        5, STAT, cluster wait time                       ,           528,        106,        528,      105.6
DATA,     483, 20090529 19:00:58,        5, STAT, gcs messages sent                       ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:58,        5, STAT, free buffer requested                   ,             1,          0,          1,         .2
DATA,     483, 20090529 19:00:58,        5, WAIT, gc cr block lost                        ,       5286330,    1057266,      5.29s,      1.06s
DATA,     483, 20090529 19:00:58,        5, WAIT, events in waitclass Other               ,           636,        127,      636us,    127.2us
--  End of snap 9
DATA,     483, 20090529 19:01:03,        5, STAT, cluster wait time                       ,           489,         98,        489,       97.8
DATA,     483, 20090529 19:01:03,        5, WAIT, gc cr request                           ,      10886698,    2177340,     10.89s,      2.18s
--  End of snap 10

PL/SQL procedure successfully completed.

.
Another graph of DBA_HIST_ACTIVE_SESS_HISTORY will show that the server is suffering from “gc cr block lost” and “gc cr multi block request”. Again, ADDM and AWR reports also support this graph

Hmm… I already have sufficient info that on the instance and session level, I’m getting waits that point me to diagnose and look into the network interconnect. Analyzing the “netstat -s” output from OS Watcher shows that there are many errors on the TCP and UDP packets

$ cat server1_netstat.dat | egrep -i "udpInOverflows|packet receive errors|fragments dropped|reassembles failed|fragments dropped after timeout"
    34096 fragments dropped after timeout
    306030 packet reassembles failed
    15 packet receive errors
    34096 fragments dropped after timeout
    306268 packet reassembles failed
    15 packet receive errors
    34096 fragments dropped after timeout
    306574 packet reassembles failed
    15 packet receive errors
    34096 fragments dropped after timeout
    306930 packet reassembles failed
    15 packet receive errors
    34096 fragments dropped after timeout

… output snipped … 

    15 packet receive errors
    34096 fragments dropped after timeout
    328087 packet reassembles failed
    15 packet receive errors
    34096 fragments dropped after timeout
    328493 packet reassembles failed
    15 packet receive errors
    34096 fragments dropped after timeout
    328889 packet reassembles failed
    15 packet receive errors

.
We quickly advised the network team to check on the interconnect switch for any issues and if possible replace it with a new unit to see if it solves the performance problem.

Then the DBA scheduled a downtime, and at first we tried restarting just the switch if it solves the problem. Then we make them run the applications again, but still there is a performance problem.

Then we shutdown again and the network team replaced the interconnect switch with a new unit. Then startup, and run the applications again…..

tentenenenen tenen!!!

…… they got fast !!!

Another check on the “netstat -s” shows no errors!

karao@karl:~/Desktop$ cat karlarao.dat | egrep -i "udpInOverflows|packet receive errors|fragments dropped|reassembles failed|fragments dropped after timeout"
    0 packet receive errors
    0 packet receive errors
    0 packet receive errors
    0 packet receive errors
    0 packet receive errors
    0 packet receive errors
    0 packet receive errors
    0 packet receive errors
    0 packet receive errors
    0 packet receive errors
    0 packet receive errors

.
Another graph of DBA_HIST_ACTIVE_SESS_HISTORY shows that the “gc block lost” waits are gone

3d view:

Stacked graph:

Update:
————–
I was also able to share and present this performance scenario on one of our DBA round tables locally (at the Oracle office)..
- Performance Scenario – Sudden slow down on RAC (pdf) – Oracle Philippines DBA round table, 07/21/2009

…While clicking around the huge monitoring tool I’ve reached the page of “Targets -> Hosts -> Administration”…that happens to be part of the Oracle Management Pack for Linux and noticed it was not working and says it needs the “Yast wrapper available at oss.oracle.com” hmm maybe not the exact message but it lead me to download this file http://oss.oracle.com/projects/yast/dist/files/el4/20070117/yast_el4_x86_32.tar which is a port of Yast to Oracle Enterprise Linux. COOL!

Well Yast (Yet another Setup Tool) is a pretty comprehensive tool which allows you to install software, configure hardware, set up networks and servers, and more…
But I’m a RedHat guy and I’m used to the way I manually do all these stuffs using the native utilities. (So even if I have this cool tool installed on my machine maybe I will not be able to use it :p )

Here’s the step by step guide to get that cool thing running:

1) Install EMGC Release 4
2) Download YAST plugin at http://oss.oracle.com/projects/yast
3) Untar and install the plugin, see below

[root@emgc1 yast_el4_x86_32]# ./install.sh 

Now checking the necessary rpms...........
Checking alsa-lib ....... found
Checking alsa-utils ....... found
Checking ash ....... found
Checking beecrypt ....... found
Checking bind-utils ....... found
Checking bzip2-libs ....... found
Checking compat-libstdc++-33 ....... found
Checking cracklib ....... found
Checking cups-libs ....... found
Checking curl ....... found
Checking e2fsprogs ....... found
Checking elfutils-libelf ....... found
Checking filesystem ....... found
Checking glibc ....... found
Checking glibc-common ....... found
Checking gzip ....... found
Checking hdparm ....... found
Checking info ....... found
Checking initscripts ....... found
Checking krb5-libs ....... found
Checking libgcc ....... found
Checking libidn ....... found
Checking libselinux ....... found
Checking libstdc++ ....... found
Checking libxml2 ....... found
Checking logrotate ....... found
Checking ncurses ....... found
Checking net-tools ....... found
Checking openldap ....... found
Checking openssl ....... found
Checking parted ....... found
Checking pciutils ....... found
Checking perl ....... found
Checking perl-Digest-SHA1 ....... found
Checking popt ....... found
Checking python ....... found
Checking qt ....... found
Checking rhpl ....... found
Checking rpm ....... found
Checking rpm-libs ....... found
Checking sysfsutils ....... found
Checking zlib ....... found

Now checking the necessary tools...........
Checking  /bin/bash ....... found
Checking  /bin/ln ....... found
Checking  /bin/mktemp ....... found
Checking  /bin/rm ....... found
Checking  /bin/sed ....... found
Checking  /bin/sh ....... found
Checking  /sbin/ldconfig ....... found
Checking  /usr/bin/env ....... found
Checking  /usr/bin/find ....... found
Checking  /usr/bin/host ....... found
Checking  /usr/bin/perl ....... found
Checking  /usr/bin/python ....... found
Checking  /usr/bin/xargs ....... found
Checking  /usr/sbin/lsof ....... found

Environment is OK. Now begin installing yast.........

Preparing...                ########################################### [100%]
   1:fillup                 ########################################### [100%]
Preparing...                ########################################### [100%]
   1:aaa_base               ########################################### [100%]
Updating etc/sysconfig/language...
Updating etc/sysconfig/backup...
Updating etc/sysconfig/boot...
Updating etc/sysconfig/displaymanager...
Updating etc/sysconfig/kernel...
Updating etc/sysconfig/suseconfig...
Updating etc/sysconfig/clock...
Updating etc/sysconfig/proxy...
Updating etc/sysconfig/windowmanager...
Updating etc/sysconfig/sysctl...
Updating etc/sysconfig/java...
Updating etc/sysconfig/mail...
Updating etc/sysconfig/ide...
Updating etc/sysconfig/cron...
Updating etc/sysconfig/news...
Preparing...                ########################################### [100%]
   1:liby2util              ########################################### [100%]
Preparing...                ########################################### [100%]
   1:netcat                 ########################################### [100%]
Preparing...                ########################################### [100%]
   1:libxcrypt              ########################################### [100%]
Preparing...                ########################################### [100%]
   1:insserv                ########################################### [100%]
Preparing...                ########################################### [100%]
   1:hwinfo                 ########################################### [100%]
Preparing...                ########################################### [100%]
   1:testutf8               ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-packagemanager   ########################################### [100%]
Updating etc/sysconfig/onlineupdate...
Preparing...                ########################################### [100%]
   1:yast2-core             ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-qt               ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-theme-Linux      ########################################### [100%]
Preparing...                ########################################### [100%]
   1:perl-Config-Crontab    ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2                  ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-ncurses          ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-control-center   ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-x11              ########################################### [100%]
Preparing...                ########################################### [100%]
   1:perl-gettext           ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-perl-bindings    ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-country          ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-mail-aliases     ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-mouse            ########################################### [100%]
Preparing...                ########################################### [100%]
   1:ldapcpplib             ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-ldap             ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-online-update    ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-xml              ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-transfer         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:autoyast2-installation ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-packager         ########################################### [  9%]
   2:yast2-pam              ########################################### [ 18%]
   3:yast2-storage          ########################################### [ 27%]
   4:yast2-bootloader       ########################################### [ 36%]
Updating etc/sysconfig/bootloader...
   5:yast2-security         ########################################### [ 45%]
   6:yast2-network          ########################################### [ 55%]
   7:yast2-inetd            ########################################### [ 64%]
   8:yast2-ldap-client      ########################################### [ 73%]
Updating etc/sysconfig/ldap...
   9:yast2-users            ########################################### [ 82%]
  10:yast2-installation     ########################################### [ 91%]
  11:yast2-update           ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-sysconfig        ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-tune             ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-powertweak       ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-runlevel         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-sound            ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-bg         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-bs         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-cs         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-cy         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-da         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-de         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-el_GR      ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-en_GB      ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-en_US      ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-es         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-fi         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-fr         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-hu         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-it         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-ja         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-ko         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-lt         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-nb         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-nl         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-pl         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-pt_BR      ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-ro         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-ru         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-sk         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-sl_SI      ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-sv         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-tr         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-zh_CN      ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-trans-zh_TW      ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-nis-client       ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-firewall         ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-http-server      ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-mail             ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-kerberos-client  ########################################### [100%]
Preparing...                ########################################### [100%]
   1:perl-Parse-RecDescent  ########################################### [100%]
Preparing...                ########################################### [100%]
   1:perl-X500-DN           ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-ldap-server      ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-nfs-client       ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-nfs-server       ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-ntp-client       ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-samba-client     ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-printer          ########################################### [100%]
Preparing...                ########################################### [100%]
   1:perl-Digest-MD4        ########################################### [100%]
Preparing...                ########################################### [100%]
   1:perl-Crypt-SmbHash     ########################################### [100%]
Preparing...                ########################################### [100%]
   1:nmbstatus              ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-samba-server     ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-tftp-server      ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-support          ########################################### [100%]
Preparing...                ########################################### [100%]
   1:xmset                  ########################################### [100%]
Preparing...                ########################################### [100%]
   1:powertweak             ########################################### [100%]
Preparing...                ########################################### [100%]
   1:powertweak-extra       ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-dns-server       ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-dhcp-server      ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast2-nis-server       ########################################### [100%]
Preparing...                ########################################### [100%]
   1:yast-EM                ########################################### [100%]
Finished. Use /sbin/yast or /sbin/yast2 to start it

.
4) Once installed Yast could be invoked in three ways:

A terminal like interface

A cool GUI interface

On Enterprise Manager Grid Control go to “Targets -> Hosts -> Administration”

.

Below are some useful scripts (with sample output) as well

-- get specific datafile
col name format a50
select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select 'file in controlfile',name,checkpoint_change#
from v$datafile where lower(name) like '%&&datafile%'
union
select 'file header',name,checkpoint_change#
from v$datafile_header where lower(name) like '%&&datafile%';

SCN location	    NAME					       CHECKPOINT_CHANGE#
------------------- -------------------------------------------------- ------------------
controlfile	    SYSTEM checkpoint						  1151614
file header	    +DATA_1/ivrs/datafile/sysaux.258.652821943			  1151614
file header	    +DATA_1/ivrs/datafile/system.267.652821909			  1151614
file header	    +DATA_1/ivrs/datafile/system_02.dbf 			  1151614
file header	    +DATA_1/ivrs/datafile/undotbs1.257.652821933		  1151614
file header	    +DATA_1/ivrs/datafile/users.263.652821963			  1151614
file in controlfile +DATA_1/ivrs/datafile/sysaux.258.652821943			  1151614
file in controlfile +DATA_1/ivrs/datafile/system.267.652821909			  1151614
file in controlfile +DATA_1/ivrs/datafile/system_02.dbf 			  1151614
file in controlfile +DATA_1/ivrs/datafile/undotbs1.257.652821933		  1151614
file in controlfile +DATA_1/ivrs/datafile/users.263.652821963			  1151614

-- get distinct checkpoint_change#
select checkpoint_change#, 'SYSTEM checkpoint in controlfile' "SCN location"
from v$database
union
select distinct checkpoint_change#, 'file in controlfile'
from v$datafile
union
select distinct checkpoint_change#, 'file header'
from v$datafile_header;

CHECKPOINT_CHANGE# SCN location
------------------ --------------------------------
	   1151614 SYSTEM checkpoint in controlfile
	   1151614 file header
	   1151614 file in controlfile

-- get distinct datafile count
select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select 'file in controlfile',to_char(count(*)),checkpoint_change#
from v$datafile
group by checkpoint_change#
union
select 'file header',to_char(count(*)),checkpoint_change#
from v$datafile_header
group by checkpoint_change#;

SCN location	    NAME					       CHECKPOINT_CHANGE#
------------------- -------------------------------------------------- ------------------
controlfile	    SYSTEM checkpoint						  1151614
file header	    5								  1151614
file in controlfile 5								  1151614

-- info from x$kcvfh (All file headers)
SELECT hxfil file_num,substr(hxfnm,1,40) file_name,fhtyp type,hxerr validity, fhscn chk_ch#, fhtnm tablespace_name,fhsta status,fhrba_seq sequence
FROM x$kcvfh;

  FILE_NUM FILE_NAME					  TYPE	 VALIDITY CHK_CH#	   TABLESPACE_NAME		      STATUS   SEQUENCE
---------- ---------------------------------------- ---------- ---------- ---------------- ------------------------------ ---------- ----------
	 1 +DATA_1/ivrs/datafile/system.267.6528219	     3		0 1151614	   SYSTEM				8196	    146
	 2 +DATA_1/ivrs/datafile/undotbs1.257.65282	     3		0 1151614	   UNDOTBS1				   4	    146
	 3 +DATA_1/ivrs/datafile/sysaux.258.6528219	     3		0 1151614	   SYSAUX				   4	    146
	 4 +DATA_1/ivrs/datafile/users.263.65282196	     3		0 1151614	   USERS				   4	    146
	 5 +DATA_1/ivrs/datafile/system_02.dbf		     3		0 1151614	   SYSTEM				   4	    146

.

The seminar has 8 chapters,

1) End to end lifecycle of a database request (33 slides)
2) Oracle, OS, and hardware interaction (39 slides)
3) Oracle kernel core services (39 slides)
4) OWI and kernel instrumentation framework (34 slides)
5) Lifecycle of a cursor (27 slides)
6) SQL execution row source operators and data flow (34 slides)
7) Logical and Physical IO internals (37 slides)
8 ) Lifecycle of a transaction (32 slides)

On the first day we were able to tackle the three chapters…

The 1st was about how to “Systematically Troubleshoot” performance problems. That Oracle is just a program and every execution that is happening is not magic, using the right tool for the right problem you’ll be able to pinpoint the root cause. Also he emphasized on how important it is to make the user’s session a starting point on diagnosis and he demo’d on how to make use of v$system_wait, snapper, ASH, v$sesstat, and of course if the built-in instrumentation is not enough… the process stack sampling is very useful that will tell you what kernel function where most of the time is spent…he also taught us how to read the stack tree using that magic Metalink note (175982.1)

2nd, dives into the OS troubleshooting, how you’ll systematically approach memory and CPU starvation on both Oracle and OS drilling down to the processes using Oracle (heapdump) and OS tools (ps, pstack, strace, procmon.exe for Windows – yeah you could also do process stack samples, pmap, top, vmstat)

3rd, was more of the internals where he discussed the core services of Oracle Kernel starting with the KGH module, the shared pool, and some more heaps, heap dumps and state objects, some X$ tables, and of course the most important part of the chapter… the Latching. He demo’d on how to systematically troubleshoot latching problems using latchprof, latchprofx, and snapper.

Then… At the end of the day, I was able to discuss with Tanel the concept of “performance visualization” see links below. I mean, yeah you can do reactive tuning and be very good at internals… but it doesn’t stop there… you have to be proactive, and visualizing/trending your performance numbers will help you on that. I’ve read his paper with Neil Gunther about how Multidimensional Visualization helps on analysing Oracle session level performance data that sometimes the usual x-y charts could mislead. He showed me some cool 3d performance monitoring tools running on JVM which is still on the works, but it’s really cool

And we also discussed about “capacity planning” because Neil Gunther is the author of the “Super Serial Scalability model” which he worked with (on the Visualization CMG paper) and Craig Shallahamer discussed it on the Chapter 10 (Scalability) of his book which is very interesting. Well, Tanel will still have to find out how efficient or realistic the “forecasting models” will be when applied with Oracle performance data. Well me too! But it still trickles down to response time and visualization

The second day…

We started with the Oracle Wait Interface, he answered my question on how would you know if it’s a syscall? Usercall? Or kernel function call?… he just used pstack, pmap, and the magic Metalink note. Cool Then he explained why the v$session is the most important view in 10g above. He showed us how to pinpoint if it’s a pl/sql that is executing or just plain SQL; yeah he has scripts for that! … the “sample” script is very useful for finding out which SQLs are problematic. He ended the chapter with identifying the session whether it is client-server or N-tiered application and demo’d on how to make use of dbms_session and dbms_monitor and how to trace these sessions.

5th discussed about lifecycle of cursors. Tanel discussed the internals of Library Cache and how applications live and die by how you do cursor management He also got good scripts here!

6th for me is the most interesting, this is where you start to glue together the info about the Oracle and OS interaction from day1 that Oracle is just a big complex C program… he started the chapter by clearly explaining how to properly get and read the explain plan (root -> branch -> leaves and row source -> filters -> access path) and associating each line with the Oracle kernel functions which you could get by doing a process stack and feeding the output to his “os_explain” tool…cool Then he ended the chapter with SQL plan profiling using some of his formatted explain plan scripts…

Unfortunately we ran out of time… we still have two chapters left! Tanel have to catch a flight to Sydney at 8pm. But he told us he will be blogging more about the later chapters, and at least we have his module… So the last demo was his “perfsheet” a very good visualization tool. Nice job with the pivot tables and 3d charts!

All in all, the popular geek from Estonia showed me how Oracle really works! (well, you owe us two chapters :p)

BTW, Porus Homi Havewala (http://enterprise-manager.blogspot.com) was also on the class…

Below are some of the seminar pictures:

When I arrived on the client I first checked the alert logs of both ASM (they have a separate home for ASM) and RDBMS, there were no errors…

Then I checked on the server to see if there were any CPU, IO, memory, swap, and network bottlenecks going on

The CPU run queue was zero and most of the time 90% idle

The disks were also most of the time idle

The memory utilization was low with 430MB free

Real memory Usage
-----------------------------
Total Installed : 6.00000 GB
Total Used : 5.57032 GB
Total Free : 439.97264 MB
REAL MEMORY USAGE : % : 92.800
-----------------------------

The paging on the server is not significant which is 1.1GB out of 16GB

Paging Space Usage
-----------------------------
Total Pg Space : 16640.00000 MB
Pg Space Used : 1187.77340 MB
Percent Used PAGING : 7.100
-----------------------------

Then I did sqlplus “/ as sysdba” to do some SELECTs on some views (…yeah it really took about 2minutes…)

I looked at the free memory of the SGA memory structures… well, I think SGA is not the issue but I still have to find out…

select pool, round(bytes/1024/1024,2) size_mb from v$sgastat where name like '%free memory%';

POOL            SIZE_MB
------------ ----------
shared pool      655.57
large pool        302.6
java pool            16
streams pool      15.99

At that time, there were only 4 users connected (that includes me) to the database and the C program was not running. I suspected that there are some contentions (latch) or concurrency problems that I’ll see on the wait interface… here’s a portion of the AWR report

   Elapsed:               60.11 (mins)
   DB Time:              121.50 (mins)

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
os thread startup                     3,278       3,115    950   42.7 Concurrenc
latch free                            6,298       1,822    289   25.0      Other
flashback buf free by RVWR            1,771       1,721    972   23.6      Other
SQL*Net more data to client          60,087       1,574     26   21.6    Network
CPU time                                          1,088          14.9
          -------------------------------------------------------------

The DB Time was 3940.8 seconds (121.50 * 60) and the top event which is the “os thread startup” consumes 83% (3276/3940.8) of the total DB Time

And just for comparison, below is their normal workload which is generated on the same period (but different day) as the above

   Elapsed:               59.52 (mins)
   DB Time:               72.42 (mins)

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time                                          2,009          46.2
db file scattered read              151,781         423      3    9.7   User I/O
db file sequential read              66,212         301      5    6.9   User I/O
latch: shared pool                      384          67    175    1.5 Concurrenc
log file sequential read                404          19     46    0.4 System I/O
          -------------------------------------------------------------

Hmmm so what is “os thread startup”? The ADDM report also outputs this event as a top finding

FINDING 1: 79% impact (3113 seconds)
------------------------------------
Wait event "os thread startup" in wait class "Concurrency" was consuming
significant database time.

   RECOMMENDATION 1: Application Analysis, 79% benefit (3113 seconds)
      ACTION: Investigate the cause for high "os thread startup" waits. Refer
         to Oracle's "Database Reference" for the description of this wait
         event. Use given SQL for further investigation.
      RATIONALE: The SQL statement with SQL_ID "4gd6b1r53yt88" was found
         waiting for "os thread startup" wait event.
         RELEVANT OBJECT: SQL statement with SQL_ID 4gd6b1r53yt88

   RECOMMENDATION 2: Application Analysis, 79% benefit (3113 seconds)
      ACTION: Investigate the cause for high "os thread startup" waits in
         Service "SYS$BACKGROUND".

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Wait class "Concurrency" was consuming significant database
               time. (80% impact [3156 seconds])

The event is not even documented on 8i, 9i, 10g, 11g official doc… I found some articles when I Google’d but they are not related to my issue… and Metalink does not have any helpful documents on this event for my situation…

There was already a Service Request regarding the issue, and the Support Engineer defined it as

“This wait event might be seen if the database server is executing on a platform that supports multi-threading. We enter this waiting state while a thread is starting up and leave the wait state when the thread has started or the startup request is cancelled.
This indicates some high contention at OS level avoiding even new process startup.
Issue is related to OS please involve system admin to solve same.”

The manager decided that we do a hard shutdown of the LPAR (we’re on P570 AIX 5.3) from the management console, and let’s see what it can do…

The DBA told me that once they are connected, all the operations are okay (DML, DDL, SQL*Loader).

So after the restart we tried to run the program for a while just to observe the behavior and what’s going on… apparently the local authentication is still slow!

For me, the challenge is how to have a response time profile of a session from the time it starts to connect with local authentication up to the time the session is connected so I would know what part of the operation is taking a lot of time.

What I remember, is when I was doing some queries to do some drill down on a session that was connecting using sqlplus “/ as sysdba” I see the background processes being spawned (QMNC, ARCn, etc..) are taking a lot of time in WAITING state with SECONDS_IN_WAIT reaching up to 20 seconds and has a wait event of “os thread startup”, then after a while the user gets connected then he’s now ready to do his thing without problems…Meaning after he’s connected there are no more complains…

SQL> select w.sid, s.sql_hash_value, s.program, w.event, w.wait_time/100 t, w.seconds_in_wait seconds_in_wait, w.state, w.p1, w.p2, w.p3
  2  from v$session s, v$session_wait w
  3  where s.sid = w.sid
  4  and w.state = 'WAITING'
  5  and w.event like '%os thread startup%'
  6  order by 6 asc;

   SID SQL_HASH_VALUE PROGRAM                 EVENT                       T SECONDS_IN_WAIT STATE                     P1         P2         P3
------ -------------- ----------------------- ------------------ ---------- --------------- ----------------- ---------- ---------- ----------
   121              0 oracle@ps570roc01 (QMNC os thread startup)          0              18 WAITING                    0          0          0

To know what’s going on when a user connects and for me to account the operation where it takes significant time; I created a database user TRACE_USER and created a logon trigger that invokes Extended SQL Trace Level 12. This will give me the timing information that I need when the user is connecting to the database.

To create the user:

create user trace_user identified by trace_user account unlock;
grant create session,dba to trace_user;

To create the logon trigger:

create or replace trigger sys.set_trace
after logon on database
when (user = 'trace_user')
declare
lcommand varchar(200);
begin
execute immediate 'alter session set statistics_level=all';
execute immediate 'alter session set max_dump_file_size=unlimited';
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
end set_trace;
/

After the user and the trigger are created, logon as the TRACE_USER by doing the following command:

sqlplus "trace_user/trace_user" << eof
exit;
eof

This will generate an Extended SQL Trace (10046) trace file under the $ORACLE_BASE/admin/<db_name>/udump directory

Then see if there’s significant time on the database calls (PARSE,BIND,EXEC,FETCH) by looking at the “e=” section which means elapsed duration consumed by the database call

And check if there’s significant time on wait events by looking at the “ela=” section which means elapsed duration consumed by the wait event (probably by doing a system call)

Below is a small portion of the trace file generated, we are interested on the highlighted portions…

PARSING IN CURSOR #5 len=131 dep=1 uid=0 oct=3 lid=0 tim=15687899202263 hv=1389591971 ad='ca9a7948'
select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#,0)
END OF STMT
EXEC #5:c=0,e=145,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=15687899202260
WAIT #5: nam='db file sequential read' ela= 30005 file#=1 block#=24208 blocks=1 obj#=-1 tim=15687899232346
FETCH #5:c=0,e=30075,p=1,cr=2,cu=0,mis=0,r=0,dep=1,og=4,tim=15687899232396

There were no database calls that consumed significant time (numbers below are microseconds, that’s for 9i and above):

less oracle_ora_1118270.trc | grep -i ",e=" | cut -d , -f2 > karlarao.txt ; sed -n 's/e=/ /p' karlarao.txt | sort -nr | less

 358746
 183162
 61293
 44661
 32580
 30075
 28695
 26950
 25837
 24244
 23519
 20543
 20132
 19449
 19290
 18809
 18250
 17230
 16507
 16349
 13438
 11716
 11690
 11284
 10137

There were no wait events that consumed significant time (numbers below are microseconds, that’s for 9i and above):

less oracle_ora_1118270.trc | grep -i "ela=" | cut -d " " -f8 | sort -nr | less

30005
28624
13253
11592
9650

There was no “os thread startup” event!

less oracle_ora_1118270.trc | grep -i "ela=" | cut -d "=" -f2 | uniq

'SQL*Net message to client' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'db file sequential read' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'db file sequential read' ela
'SQL*Net message to client' ela
'db file sequential read' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'db file sequential read' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela
'SQL*Net message to client' ela
'SQL*Net message from client' ela

I need to have some instrumentation on the system calls, so I’ll use a UNIX tool,

presenting… TRUSS…

This tool will let you do some application tracing, for us we are interested in tracing SQL*Plus, and display the calls that an application makes to external libraries and the kernel… sounds cool

A “man truss” will give you an overview, but if you want more detail there are some good references at the IBM site or Metalink

Execute the following commands (one at a time):

truss -c sqlplus "/ as sysdba" << EOF
exit;
EOF

truss -d sqlplus "/ as sysdba" << EOF
exit;
EOF

The “-c” switch counts traced system calls, faults, and signals

The “-d” switch displays the timestamp in seconds on each line. The first line of the trace output will show the base time from which the individual time stamps are measured

Bwahaha, Now I know the problem!

The DNS service was causing the slow down on the sqlplus “/ as sysdba” operation and also the high “os thread startup” waits

The text below is the portion of “truss” output where the slow down occurs denoted by “(sleeping…)” events when reading on the /etc/resolv.conf and /etc/hosts. Please see the highlighted region below

ENOTTY and ECONNREFUSED are the errors that you’ll see, you’ll find more details on the error messages on this file /usr/include/sys/errno.h
Below are their meanings

#define	ENOTTY	25	/* Inappropriate I/O control operation	*/
#define ECONNREFUSED    79      /* Connection refused */

The portion of “truss -d” where the slow down occurs, you’ll see a leap on the timing information from 2.3379 seconds (reading /etc/resolv.conf) –> 7.3477 seconds –> 17.3489 seconds –> 37.3555 seconds (reading /etc/hosts) –> 70.3863 seconds (already connected)

The timing information below may not exactly match the real “time” that the user is waiting, it’s because the SQL*Plus is being probed to know the part of the underlying system calls where there are significant waits

0.3172:        gethostname(0x0FFFFFFFFFFF9920, 256) = 0
0.3175:        _getpid()                        = 1102056
0.3177:        open("/etc/resolv.conf", O_RDONLY) = 8
0.3180:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
0.3182:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(8, " d o m a i n     this is the domain name, i removed it".., 4096)    = 69
kread(8, " d o m a i n     this is the domain name, i removed it".., 4096)    = 0
0.3190:        close(8)                         = 0
0.3194:        socket(2, 2, 0)                  = 8
0.3197:        getsockopt(8, 65535, 4104, 0x0FFFFFFFFFFF70E4, 0x0FFFFFFFFFFF70E0) = 0
0.3199:        connext(8, 0x09001000A0018600, 16) = 0
0.3202:        send(8, 0x0FFFFFFFFFFF7FF0, 41, 0) = 41
0.3205:        _poll(0x0FFFFFFFFFFF7190, 1, 5000) = 1
0.3208:        nrecvfrom(8, 0x0FFFFFFFFFFF9320, 1024, 0, 0x0FFFFFFFFFFF7950, 0x0FFFFFFFFFFF7178) Err#79 ECONNREFUSED
0.3210:        close(8)                         = 0
0.3212:        socket(2, 2, 0)                  = 8
0.3215:        sendto(8, 0x0FFFFFFFFFFF7FF0, 41, 0, 0x09001000A0018610, 16) = 41
0.3217:        _poll(0x0FFFFFFFFFFF7190, 1, 5000) = 1
0.3220:        nrecvfrom(8, 0x0FFFFFFFFFFF9320, 1024, 0, 0x0FFFFFFFFFFF7950, 0x0FFFFFFFFFFF7178) = 108
0.3222:        close(8)                         = 0
0.3224:        socket(2, 2, 0)                  = 8
0.3227:        getsockopt(8, 65535, 4104, 0x0FFFFFFFFFFF70E4, 0x0FFFFFFFFFFF70E0) = 0
0.3229:        connext(8, 0x09001000A0018600, 16) = 0
0.3231:        send(8, 0x0FFFFFFFFFFF7FF0, 28, 0) = 28
0.3233:        _poll(0x0FFFFFFFFFFF7190, 1, 5000) = 1
0.3236:        nrecvfrom(8, 0x0FFFFFFFFFFF9320, 1024, 0, 0x0FFFFFFFFFFF7950, 0x0FFFFFFFFFFF7178) Err#79 ECONNREFUSED
0.3238:        close(8)                         = 0
0.3240:        socket(2, 2, 0)                  = 8
0.3243:        sendto(8, 0x0FFFFFFFFFFF7FF0, 28, 0, 0x09001000A0018610, 16) = 28
0.3245:        _poll(0x0FFFFFFFFFFF7190, 1, 5000) = 1
0.3248:        nrecvfrom(8, 0x0FFFFFFFFFFF9320, 1024, 0, 0x0FFFFFFFFFFF7950, 0x0FFFFFFFFFFF7178) = 28
0.3250:        close(8)                         = 0
0.3252:        socket(2, 2, 0)                  = 8
0.3373:        sendto(8, 0x0FFFFFFFFFFF7FF0, 28, 0, 0x09001000A0018600, 16) = 28
2.3379:        _poll(0x0FFFFFFFFFFF7190, 1, 5000) (sleeping...)
2.3379:        _poll(0x0FFFFFFFFFFF7190, 1, 5000) = 0
5.3468:        close(8)                         = 0
5.3470:        socket(2, 2, 0)                  = 8
5.3472:        sendto(8, 0x0FFFFFFFFFFF7FF0, 28, 0, 0x09001000A0018600, 16) = 28
7.3477:        _poll(0x0FFFFFFFFFFF7190, 1, 10000) (sleeping...)
7.3477:        _poll(0x0FFFFFFFFFFF7190, 1, 10000) = 0
15.3479:        close(8)                        = 0
15.3482:        socket(2, 2, 0)                 = 8
15.3484:        sendto(8, 0x0FFFFFFFFFFF7FF0, 28, 0, 0x09001000A0018600, 16) = 28
17.3489:        _poll(0x0FFFFFFFFFFF7190, 1, 20000) (sleeping...)
17.3489:        _poll(0x0FFFFFFFFFFF7190, 1, 20000) = 0
35.3491:        close(8)                        = 0
35.3495:        getdomainname(0x0FFFFFFFFFFF9480, 256) = 0
35.3497:        open("/etc/hosts", O_RDONLY)    = 8
35.3500:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
35.3502:        kfcntl(8, F_SETFD, 0x0000000000000001) = 0
35.3505:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(8, " #   @ ( # ) 4 7\t 1 . 1".., 4096)    = 3453
kread(8, " #   @ ( # ) 4 7\t 1 . 1".., 4096)    = 0
35.3514:        close(8)                        = 0
kwrite(9, "01 �0603 s".., 432)    = 432
37.3555:        kread(10, "16060280".., 2064) (sleeping...)
kread(10, "06 E06\b".., 2064)   = 1605
70.3762:        open("/oracle/app/product/10.2/db_1/rdbms/mesg/oraus.msb", O_RDONLY) = 8
70.3764:        kfcntl(8, F_SETFD, 0x0000000000000001) = 0
70.3767:        lseek(8, 0, 0)                  = 0
kread(8, "1513 "011303\t\t".., 256)     = 256
70.3772:        lseek(8, 512, 0)                = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
70.3776:        lseek(8, 1024, 0)               = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
70.3781:        lseek(8, 50688, 0)              = 50688
kread(8, "\f05 ] P05 ^".., 512)     = 512
70.3786:        lseek(8, 512, 0)                = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
70.3790:        lseek(8, 1024, 0)               = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
70.3795:        lseek(8, 50688, 0)              = 50688
kread(8, "\f05 ] P05 ^".., 512)     = 512
70.3799:        lseek(8, 512, 0)                = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
70.3804:        lseek(8, 1024, 0)               = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
70.3808:        lseek(8, 50688, 0)              = 50688
kread(8, "\f05 ] P05 ^".., 512)     = 512
70.3813:        lseek(8, 512, 0)                = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
70.3817:        lseek(8, 1024, 0)               = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
70.3822:        lseek(8, 51712, 0)              = 51712
kread(8, "\n0589 D058A".., 512)     = 512
70.3827:        close(8)                        = 0
kwrite(9, " <0611 k".., 60)     = 60
kread(10, " �06\b".., 2064)   = 179
kwrite(9, " U0603 h".., 85)     = 85
kread(10, "1606\b".., 2064)   = 22
kwrite(9, " U0603 h".., 85)     = 85
kread(10, "1606\b".., 2064)   = 22
70.3854:        lseek(4, 512, 0)                = 512
kread(4, "17 �".., 512)     = 512
70.3858:        lseek(4, 1024, 0)               = 1024
kread(4, "16 * R h819E".., 512)     = 512
70.3863:        lseek(4, 4096, 0)               = 4096
kread(4, "\f82 P86".., 512)     = 512

kwrite(1, "\n", 1)                              = 1
Connected to:
kwrite(1, " C o n n e c t e d   t o".., 14)     = 14
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
kwrite(1, " O r a c l e   D a t a b".., 77)     = 77
With the Partitioning, OLAP, Data Mining and Real Application Testing options
kwrite(1, " W i t h   t h e   P a r".., 78)     = 78

kwrite(1, "\n", 1)                              = 1
70.3882:        kfcntl(1, F_GETFL, 0x0000000000000008) = 2
70.3886:        __libc_sbrk(0x0000000000030020) = 0x000000001023C880
70.3892:        access("login.sql", 0)          Err#2  ENOENT
70.3895:        access("/oracle/app/product/10.2/db_1/sqlplus/admin/glogin.sql", 0) = 0
70.3898:        statfs("/oracle/app/product/10.2/db_1/sqlplus/admin/glogin.sql", 0x0FFFFFFFFFFFDA10) = 0

Apparently there was really a problem with the DNS when it was checked by the System Administrator. He just altered some network related files and instantly we checked on the local authentication… and we could quickly do a local authentication again! Whooho! and everybody was happy

Below is the good “truss” output of the local authentication, which takes about 3 seconds from the timing information

3.0713:        gethostname(0x0FFFFFFFFFFF9900, 256) = 0
3.0716:        _getpid()                        = 2539762
3.0719:        open("/etc/resolv.conf", O_RDONLY) = 8
3.0721:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
3.0724:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(8, " d o m a i n     this is the domain name, i removed it".., 4096)    = 70
kread(8, " d o m a i n     this is the domain name, i removed it".., 4096)    = 0
3.0731:        close(8)                         = 0
3.0735:        socket(2, 2, 0)                  = 8
3.0740:        getsockopt(8, 65535, 4104, 0x0FFFFFFFFFFF70C4, 0x0FFFFFFFFFFF70C0) = 0
3.0742:        connext(8, 0x09001000A0018600, 16) = 0
3.0746:        send(8, 0x0FFFFFFFFFFF7FD0, 41, 0) = 41
3.0750:        _poll(0x0FFFFFFFFFFF7170, 1, 5000) = 1
3.0753:        nrecvfrom(8, 0x0FFFFFFFFFFF9300, 1024, 0, 0x0FFFFFFFFFFF7930, 0x0FFFFFFFFFFF7158) = 108
3.0756:        send(8, 0x0FFFFFFFFFFF7FD0, 28, 0) = 28
3.0758:        _poll(0x0FFFFFFFFFFF7170, 1, 5000) = 1
3.0761:        nrecvfrom(8, 0x0FFFFFFFFFFF9300, 1024, 0, 0x0FFFFFFFFFFF7930, 0x0FFFFFFFFFFF7158) = 28
3.0764:        close(8)                         = 0
3.0767:        getdomainname(0x0FFFFFFFFFFF9460, 256) = 0
3.0769:        open("/etc/hosts", O_RDONLY)     = 8
3.0771:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
3.0774:        kfcntl(8, F_SETFD, 0x0000000000000001) = 0
3.0777:        kioctl(8, 22528, 0x0000000000000000, 0x0000000000000000) Err#25 ENOTTY
kread(8, " #   @ ( # ) 4 7\t 1 . 1".., 4096)    = 3453
kread(8, " #   @ ( # ) 4 7\t 1 . 1".., 4096)    = 0
3.0786:        close(8)                         = 0
kwrite(9, "01 �0603 s".., 432)    = 432
kread(10, "06 H06\b".., 2064)   = 1608
3.0973:        open("/oracle/app/product/10.2/db_1/rdbms/mesg/oraus.msb", O_RDONLY) = 8
3.0975:        kfcntl(8, F_SETFD, 0x0000000000000001) = 0
3.0978:        lseek(8, 0, 0)                   = 0
kread(8, "1513 "011303\t\t".., 256)     = 256
3.0983:        lseek(8, 512, 0)                 = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
3.0988:        lseek(8, 1024, 0)                = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
3.0993:        lseek(8, 50688, 0)               = 50688
kread(8, "\f05 ] P05 ^".., 512)     = 512
3.0997:        lseek(8, 512, 0)                 = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
3.1002:        lseek(8, 1024, 0)                = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
3.1006:        lseek(8, 50688, 0)               = 50688
kread(8, "\f05 ] P05 ^".., 512)     = 512
3.1011:        lseek(8, 512, 0)                 = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
3.1015:        lseek(8, 1024, 0)                = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
3.1021:        lseek(8, 50688, 0)               = 50688
kread(8, "\f05 ] P05 ^".., 512)     = 512
3.1025:        lseek(8, 512, 0)                 = 512
kread(8, "1A Q 5 C [ V u �85 �9480".., 512)     = 512
3.1029:        lseek(8, 1024, 0)                = 1024
kread(8, "18 $ 4 = G S".., 512)     = 512
3.1034:        lseek(8, 51712, 0)               = 51712
kread(8, "\n0589 D058A".., 512)     = 512
3.1038:        close(8)                         = 0
kwrite(9, " <0611 k".., 60)     = 60
kread(10, " �06\b".., 2064)   = 179
kwrite(9, " U0603 h".., 85)     = 85
kread(10, "1606\b".., 2064)   = 22
kwrite(9, " U0603 h".., 85)     = 85
kread(10, "1606\b".., 2064)   = 22
3.1064:        lseek(4, 512, 0)                 = 512
kread(4, "17 �".., 512)     = 512
3.1069:        lseek(4, 1024, 0)                = 1024
kread(4, "16 * R h819E".., 512)     = 512
3.1073:        lseek(4, 4096, 0)                = 4096
kread(4, "\f82 P86".., 512)     = 512

kwrite(1, "\n", 1)                              = 1
Connected to:
kwrite(1, " C o n n e c t e d   t o".., 14)     = 14
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
kwrite(1, " O r a c l e   D a t a b".., 77)     = 77
With the Partitioning, OLAP, Data Mining and Real Application Testing options
kwrite(1, " W i t h   t h e   P a r".., 78)     = 78

kwrite(1, "\n", 1)                              = 1
3.1095:        kfcntl(1, F_GETFL, 0x0000000000000008) = 2
3.1099:        __libc_sbrk(0x0000000000030020)  = 0x000000001023C880
3.1105:        access("login.sql", 0)           Err#2  ENOENT
3.1109:        access("/oracle/app/product/10.2/db_1/sqlplus/admin/glogin.sql", 0) = 0
3.1112:        statfs("/oracle/app/product/10.2/db_1/sqlplus/admin/glogin.sql", 0x0FFFFFFFFFFFD9F0) = 0

1. Single Instance on ASM using raw devices
2. RAC with ASM (using ASMlib) and OCFS2

Upgrading the Kernel and OS is easy and will just need some few commands. The critical part is the dependencies once the Kernel gets updated, so if you’re using ASMlib and OCFS2 you’ll notice that after the upgrade they’re not working anymore… you can’t startup the ASM, then if your OCR and Voting Disk are on OCFS2 the CRS stack wont start all because the RPMs of ASMlib and OCFS2 are kernel dependent, also there are similar components/softwares that are kernel dependent so you have to check them out and do a risk analysis before doing the upgrade.

… If that’s the case, why do you upgrade your Kernel or OS? … Normally you don’t do this often on your production environment considering that you have 24/7 operations but if the business permits it’s ideal to make an upgrade policy on your OS/systems. Below are some of the “drivers why you need to upgrade”:

• You need to upgrade the firmware of your SAN environment that requires you to be in a specific OS version

• You need to upgrade the firmware of your HBA that needs a specific kernel version

• You need to upgrade your RAID controller’s firmware that requires you to be in a specific kernel or OS version

• Because the hardware/software vendor’s certification matrix is stating that you need to be in a specific kernel or OS version to be on a certified configuration

• And many more…

So after reading this document, you know what to do/check once your boss/client/vendor tell you to upgrade the kernel or OS with an Oracle Database Single Instance or RAC running.

To view the document, click here

1) Oracle Security products (Advance Security Option, Database Vault, Audit Vault, Data Masking, etc. etc.). Well, every organization must guard their digital assets against any threat (external/internal) because once compromised it could lead to negative publicity, lost revenue, litigation, lost of trust.. and the list goes on.. I’m telling you, Oracle has a lot to offer (breadth of products and features, some of them are even free!) on this area and you just need to have the knowledge to stitch them..

I’ll recommend a great book on Security, I believe everyone should have this (developers, DBAs, CxOs)..!
It’s a book by David Knox which is the chief engineer in Oracle’s Information Assurance Center (IAC). The IAC is a center of expertise that works with Oracle’s customers, partners, development, and consulting to design and develop security and high-availability solutions. Having a long time hands-on practice (working on Oracle security ever since) he was able to architect and implement solutions on organizations like United States Department of Defense and CIA.. and produce a book called “Effective Oracle Database 10g Security by Design”

2) The other one is Forecasting Oracle Performance… This is a book by Craig Shallahamer, a former Oracle employee, back then he’s with the System Performance Group (one of the largest and best collections of Oracle performance experts in the world) together with Cary Millsap (author of Optimizing Oracle Performance) and a few others..

Cary’s book is great, I’ve already read it and it will change the way you think about performance and tuning.. This is the only book that discusses Extended SQL trace in detail, the ins and outs and how to use it in different scenarios. This book also gave me a primer on what is Queueing Theory.. it tackles on the M/M/m Queueing Model but I felt it was more of an overview and not very detailed but at least I get the picture how useful it is..

Now presenting Craig’s work of art.. Forecasting Oracle Performance..

If you’re an Oracle professional, and you love complexities, and you like to be challenged, and you want to see whats ahead of you (in performance), and you love math (well I’m not really good at it, my brother is).. better read this!

Honestly I have two more performance/capacity planning book which I’ll still have to figure out how am I going to use it (specifically the formulas) on some of my performance tuning activities. These are:

Guerrilla Capacity Planning: A Tactical Approach to Planning for Highly Scalable Applications and Services
Performance by Design: Computer Capacity Planning By Example

Craig’s book is focused on the complex Oracle environment..and the book has contents like the following:

• Learn how you manage your Service Levels or how to make Service Levels by using your workload samples,
• at what workload and utilization you’ll enter the “elbow of the curve”
• should you acquire, more CPUs (same speed)? or just replace it with faster CPUs (same number)? or both?
• should you acquire, more disks (same speed)? or just replace it with faster disks (same number)? or both?
• If there would be 200% additional workload, what will be the effect on response time?.. should I buy more CPUs? manage the workload? tune the application?
• Learn basic statistics (Sample and Population, mean & weighted average, standard deviation, skew, histogram, inferences, residual analysis, confidence level and interval).. And you’ll learn to use the statement like.. 90% OF THE TIME, THE QUERY TAKES AN AVERAGE OF 3 SECONDS PLUS OR MINUS 2 SECONDS
• Little’s law and Kendall’s notation
• and many more….

You might be overwhelmed, but the forecasting work done in this book has methods and is tackled in a gradual manner. Chapter by Chapter you’ll see yourself progressing and not feeling the pain of understanding something that is beyond human cognition.. And Craig is a very good writer, he explains it in detail with easy to understand explanations (plus humor).. If you’re worrying about math, the formulas and models (essential forecasting, forecasting with Erlang C, ratio modeling, regression analysis, queueing theory) are easy to comprehend and use, you’ll get the hang of it once you use it..

Also, I was able to submit an errata on page 68 which Craig acknowledged (see image below).

We exchanged a couple of emails, and I asked a lot of questions about Chapter 4 (Basic Forecasting Statistics).. he answered it all and he was nice.. he said he had no idea I am from the Philippines.. and there will be an upcoming self published book about Oracle Performance Firefighting… that will be a good book.. Below is a scratch pad from Craig’s firefighting class:

3) And lastly.. are some of the stuff I’d like to post soon…

• RAC kernel upgrade

• Converting RAC to single instance

• Block corruption issues, and how to resolve it

• Word size change: 32bit to 64bit on 10.2.0.4

• RAC pseudo standby database

Below are some of my opinions about it:

• Ubuntu has a vast hardware support which does not need any kernel recompilation, building package from scratch, or any hardcore geek tweaks that you have to do in order to be happy with your machine. One example, if you’re in a meeting and you ask your co-worker, “that’s cool, can you project that slide for us?”.. he replies..”I can’t I’m running on CentOS 5, I haven’t worked on the module yet”.. Another is when you’re on a client and the only way to connect to their network is through Wireless and because your Wireless card is not supported you can’t get your job done..ouch!
• I just want a user-friendly Linux desktop environment (not the server type) which will allow me to do similar things that I do on a Windows environment, also it must be backed by a very dynamic user community.. that makes the distribution keeps on evolving, and makes it very cool and exciting..
• I have a 120GB external drive that has NTFS partitions, If I were to migrate to CentOS I would be needing NTFS-3g (NTFS driver) in order to use my external drive in read-write mode, apparently it’s not native on the CentOS distribution and there are just too many steps in order to make it work which is same case with the wireless card issue. When you install Ubuntu, you could just plug your external drive and immediately use it
• Lastly, I also want to learn and be familiar with a Debian based distro..and I kinda like the Synaptic Package Manager, very easy to use with tons of packages available.. but Yum is still cool and powerful..

This is my laptop specs (NEO Elan L2110):

Technology Intel® Centrino® processor technology

Processor Intel® Core^TM2 Duo Processor T8100

Processor details 3 MB L2 cache, 2.10 Ghz, 800 Mhz FSB

Chipset Intel® GM965 + ICH8-M

Memory 4 GB DDR2

Graphics Intel® Graphics Media Accelerator X3100

Display 13.3″ WXGA LCD

Speakers 2 x High Fidelity Internal Speakers (1.5W)

Hard Disk Drive 160 GB SATA

Optical Disc Drive DVDRW SuperMulti Slot-in Type

Networking 10/100/1000 Mbps LAN

Wireless Intel® Wireless WiFi Link 4965AGN

Card Reader 4-in-1 Card Reader

Webcam 1.3 MegaPixels

Connectivity IEEE 1394, Bluetooth®

Battery 6-cell Lithium-ion Battery

Weight ~ 2.1 k

And here’s the step by step on how I did my migration:

On Windows:

1) Backup Calendar and Email (Thunderbird)

2) Backup all the files

3) Have an inventory of all the softwares installed

4) Backup bookmarks on Firefox

On Ubuntu:

5) Installation

• Used the “alternate CD” then custom partition using LVM (if you are not using the “alternate CD” you can’t do LVM upon install), LVM is very flexible.. if ever I ran out of space on a certain file system I could just execute lvextend and resize2fs.. So I allotted the following:

120MB – BOOT,

2GB – SWAP,

1.5GB – ROOT,

138GB – HOME,

1GB – TMP,

4GB – USR,

1GB – VAR

• For the keyboard layout I chose “USA” because I’m having problems with special characters when I chose the “USA International”
• If you encounter the error “ESCOM” when you are hitting ENTER key when searching for a word on a man page, it is a keyboard bug.. just hit CTRL+M or CTRL+J for the workaround (https://bugs.launchpad.net/ubuntu/+source/xkeyboard-config/+bug/40905)
• I used the 64bit Desktop Kernel of Ubuntu (although I can install the 64bit Server Kernel), why? Because I’m just running on a laptop, and I only have one SATA disk. If I install the server kernel it will have little or no difference on the performance. See the link (http://www.serverwatch.com/tutorials/article.php/3715071) for discussions on I/O scheduler, pre-emption, memory, ticks and HZ

6) After the installation, I just want to try if my VMs will run without problems on Ubuntu, my productivity depends on my VMs.. without them I can’t do all of my test cases for my clients, just follow the bullets below

• Download VMware 1.08 – latest as of Jan 3, 2009, get the tar.gz version (http://www.vmware.com/download/server/)
• Read the article (http://ubuntu-tutorials.com/2008/11/01/vmware-server-107-on-ubuntu-810-intrepid-2627-7-generic/) which you will be asked to download the http://www.insecure.ws/warehouse/vmware-update-2.6.27-5.5.7-2.tar.gz which is a patch that will update the vmware modules for the 2.6.27 kernel
• Also read the (http://ubuntuforums.org/showthread.php?t=337040) for common problems that you may encounter when installing VMware on Ubuntu
• If you’re having problems on the ENTER key inside your VMs, then read the link (http://communities.vmware.com/message/1091425), you have to append the xkeymap.nokeycodeMap = “true” on /etc/vmware/config file

7) Update Ubuntu (kernel and all packages)

• Update your OS using the Synaptic Package Manager

8 ) Re-run the perl script vmware setup

• Updating your OS will also update the kernel, VMware is kernel dependent so you have to rerun the “runme.pl” to recompile it to the new kernel

9) Install other softwares/packages

- Thunderbird and Lightning (for Email and Calendar)

- Filezilla (for easy file transfer)

- CHM viewer (for viewing my CHM ebooks)

- Kate (a powerful text editor with block selection, gedit has a column mode selection plugin but doesn’t really work)

- CrossOver (for using Windows Office 2k3, Visio, Project on a Linux environment.. I don’t have to worry about my Windows documents, got it free at http://www.codeweavers.com)

- K3B (a powerful burning tool)

- VLC player and other codecs

- VNC viewer and server

- cups-pdf (a PDF printer, solution to make it work: create PDF directory on the home directory, http://linux.derkeiler.com/Mailing-Lists/Ubuntu/2008-11/msg00745.html)

- Brasero (for creating ISO images, http://andieko.info/lang/en/how-to-create-iso-files-in-ubuntu/)

- KSnapshot (replacement for SnagIt, allows region selection)

- sysstat (system performance tools for linux) and ksh (korn shell).. I’ll be needing these to run OS watcher (by Oracle Support) on my machine..

- smbclient, samba-common, samba, libpam-smbpass (so you could share directories)

- sysv-rc-conf (SysV init runlevel config tool for the terminal, so you could have a “chkconfig” just like in RedHat)

- gpaint just like the paint program on Windows

- gwget a download manager which is a front-end for wget

10) Lastly, customize your desktop..

This is the screenshot of my Desktop Environment..

You can see that I’m using my NTFS external drive and the following VMs:

• 2 node 10gR2 RAC on OEL4.4 x86 with OCFS2 and ASMlib
• 10gR2 on OEL4.5 x86 with ASM raw devices
• 10gR2 Data Guard on OEL 4.7 x86-64 both on ASMlib
• Enterprise Manager Grid Control 10.2.0.4 on OEL 4.4 x86
• 10gR2 and 11g Database Vault and 10gR2 Audit Vault on OEL 4.4 x86

Last August 29, 2008 on one of the RSS feeds of OTN TechBlog Sergio Leunissen posted a blog about Oracle Validated being available outside ULN which is very nice to hear.. then after 2 months (October), Alejandro Vargas posted a blog on how to do the Oracle-Validated installation on OEL5..

which then made me want to try it on OEL4, and is just in time because I want to shift to 64bit RAC on Linux (test environment on VMware)..

The whole installation is documented here: Oracle-Validated installation on OEL 4.5

Below are some Metalink Notes about Oracle Validated:

Linux OS Installation with Reduced Set of Packages for Running Oracle Database Server
Doc ID: Note:728346.1

Linux OS Installation with Reduced Set of Packages for Running Oracle Database Server without ULN/RHN
Doc ID: Note:579101.1

Defining a “default RPMs” installation of the Oracle Enterprise Linux (OEL) OS
Doc ID: Note:401167.1

Defining a “default RPMs” installation of the RHEL OS
Doc ID: Note:376183.1

Defining a “default RPMs” installation of the SLES OS
Doc ID: Note:386391.1

The ‘oracle-validated’ RPM Package for Installation Prerequisities
Doc ID: Note:437743.1

Below is the summary of the document:

• The environment is a virtual machine with 1GB of RAM and two CPUs; the total time for the installation which includes media check, setting up networking, and additional RPMs was 30 minutes The good thing about this is the installation only consumed 1.5GB on the /usr filesystem and has a total of 583 RPMs, compared to my other installation (w/o using oracle-validated) which consumed around 2.5GB and has a total of 788 packages. Lesser packages will give you lesser services running on your system that makes the server easier to harden, manage, and maintain.
• Although all the required RPMs are already installed on the server, you still have to create the needed directories and edit bash_profile, /etc/profile, and set the appropriate shmmax (/etc/sysctl.conf) value for your environment.