Wednesday, August 27, 2008

Quote from Thomas Kyte's book

About Thomas Kyte: Architect in Oracle Corp. Brain behind

if you want a 10 step guide to tuning a query, buy a piece of software. You are not needed in this process, anyone can put a query in, get a query out and run it to see if it is faster. There are tons of these tools on the market. They work using rules (heuristics) and can tune maybe 1% of the problem queries out there. They APPEAR to be able to tune a much larger percent but that is only because the people using these tools never look at the outcome -- hence they continue to make the same basic mistakes over and over and over.

If you want to really be aboe to tune the other 99% of the queries out there, knowledge of lots of stuff -- physical storage mechanisms, access paths, how the optimizer works -thats the only way.


Think about it for a moment. If there were a 10 step or even 1,000,000 step process by which any query can be tuned (or even X% of queries for that matter), we would write a program to do it. Oh don't get me wrong, there are many programs that actually try to do this - Oracle Enterprise Manager with its tuning pack, SQL Navigator and others. What they do is primarily recommend indexing schemes to tune a query, suggest materialized views, offer to add hints to the query to try other access plans. They show you different query plans for the same statement and allow you to pick one. They offer "rules of thumb" (what I generally call ROT since the acronym and the word is maps to are so appropriate for each other) SQL optimizations - which if they were universally applicable - the optimizer would do it as a matter of fact. In fact, the cost based optimizer does that already - it rewrites our queries all of the time. These tuning
tools use a very limited set of rules that sometimes can suggest that index or set of indexes you really should have thought of during your design.

Monitoring Windows Server Performance and Activity

Microsoft has given step by step guide on how to setup monitoring (using perfmon) on windows system. Click here to check the same.

I just don’t wanna reinvent wheel and also this saved my time.

For Linux/Unix monitoring, I am planning to write on my own(atleast 1 linux or unix version with detailed example).


Tuesday, August 26, 2008

All I Ever Need to Know about Testing I Learned in Kindergarten - By Lee Copeland

While googling, I found an interesting blog ( It is worth spending sometime reading this (to me, at least).


Monday, August 18, 2008

web_reg_save_param equallent for webload

I have found a site who claims that they have written a function equallent of web_reg_save_param. Click here to check.

Use at your own risk.


Wednesday, August 13, 2008

OS Monitoring

Monitoring a server isn't something you should do chaotically. You need to have a clear plan—a set of goals that you hope to achieve. Troubleshooting server performance problems is a key reason for monitoring. Not just to plot good looking graphs and show it to superiors. Without monitoring, tuning is almost an impossible activity.

Basically monitoring is done for 2 purposes
  1. Benchmark
  2. Tuning

While monitoring OS, following are the basic things need to be considered regardless of OS platform.

  1. CPU Utilization
  2. Memory, paging
  3. Throughput & retransmission statistics
  4. TCP statistics
  5. Disk statistics

Not all things need to be presented in report. Presenting 10-20 page report containing only relevant is always better than presenting a 100 page report (album). If you want to show your hard work, create annexure section and attached to it (I may not call it album here. After-all we are showing our hard work at relevant place. He he).

Linux Monitoring

Monitoring Linux based systems are not as complex as many people thing. All we need to know is just simple, basic shell scripting and few basic commands.

To know the version of Linux kernel
uname –a

vmstat command
vmstat reports information about processes, memory, paging, block IO, traps, and CPU activity.

Basic syntax
vmstat [[delay] count]

vmstat 10 7

The first statistics that are printed are averaged over the system uptime.Don’t consider this unless it really make sense.

See man page for more information

iostat command
iostat displays kernel I/O statistics on terminal, device and cpu operations.

Basic syntax
iostat [[delay] count]

Iostat 10 7

The first statistics that are printed are averaged over the system uptime.Don’t consider this unless it really make sense.

netstat command

netstat prints network connections, routing tables, interface statistics, masquerade connections, and multicast memberships. There are a number of output formats, depending on the options for the information presented.

See man page for more information

Nmon utility

Quote from IBM
This free tool gives you a huge amount of information all on one screen. Even though IBM doesn't officially support the tool and you must use it at your own risk, you can get a wealth of performance statistics. Why use five or six tools when one free tool can give you everything you need?

The nmon tool runs on:

  1. AIX® 4.1.5, 4.2.0 , 4.3.2, and 4.3.3 (nmon Version 9a: This version is functionally established and will not be developed further.)
  2. AIX 5.1, 5.2, and 5.3 (nmon Version 10: This version now supports AIX 5.3 and POWER5™ processor-based machines, with SMT and shared CPU micro-partitions.
  3. Linux® SUSE SLES 9, Red Hat EL 3 and 4, Debian on pSeries® p5, and OpenPower™
    Linux SUSE, Red Hat, and many recent distributions on x86 (Intel and AMD in 32-bit mode)
  4. Linux SUSE and Red Hat on zSeries® or mainframe

Click here for more information.

Windows Monitoring
Windows provide huge set of monitoring counters to end users. Not all need to be monitored always, but all counters might be useful at one point or another. All windows OS related counters can be monitoring using perfmon (just goto run and type perfmon).

I am planning to write Step by step instruction on how to monitoring windows using perfmon later in as a separate blog.