Performance Tuning Approaches Oracle
Contributions, Health Check, Linux, Oracle

Performance Tuning Approaches (Oracle)

An understanding of systems architecture and design provides the foundation for a proactive monitoring stance, but there is no better prevention than a periodic health check. Whether you accomplish this with OraChk, AWR reporting, or Statspack, regular inspection of a database is the most effective way of preventing issues before they happen.

Server issues (memory, CPU, and network bandwidth) all affect system performance. But how can you differentiate those from SQL performance issues, requiring different strategies and scope in the diagnostic phase?

SQL performance issues require your best (and quickest) assertions as a DBA. Are you equipped to identify SQL bottlenecks on the fly? Does high level messaging from the alert log leave you without a clue when it comes to weeding out the culprits? A well equipped DBA needs to be able to rule out any number of server related issues, network latencies, I/O , or storage problems to assert the nature of the problem quickly – and vice versa when you have determined the problem is on the server. But where do you start?

This first installment in a four part series will demonstrate how to quickly and effectively identify systems level and SQL tuning tasks, allowing you to eliminate culprits along the way and provide a reliable starting point for the performance tuning task ahead of you.

The series is broken into the following parts:

I. Server Diagnostics
II. Workload Analysis
III. Object Analysis
IV. SQL Tuning

 

A typical scenario:

Complaints about application performance are coming in and time is of the essence.  To get off on the right foot, you’re likely compiling mental notes for all of the important checkpoints:

a) Logical IO’s per execution.

b) Physical IO’s per execution.

c) CPU

d) Parse Calls

e) Elapsed time.

f) Total number of executions

g) SGA / shared memory

h) Runaway processes.

 

All of this is good, but rather like shooting fish in a barrel.  The time you will take and the order you establish for investigating each of them will impact the outcome of your performance tuning effort.  Taking the above into account and looking at what an RDBMS ‘is’ – and what its components are, we can say the following:

– An Oracle environment contains instances.
– The instances are subject to a specific and sometimes varied workload.
– The workload (ETL, OLTP, DSS, etc) access objects within the database.
– The objects are containers for data, accessed by SQL statements.

*This establishes the order in which you should conduct your analysis.

You must have a toolkit (methodology/scripts) providing straightforward insight into
each of the metrics listed above. With thousands of scripts and views provided by Oracle and in online forums, where do you start? A top-down analytic approach is recommended, because it saves you time by ruling out systems, network and/or storage issues. This is also important because it prevents the sometimes unwanted outcome from over-soliciting the inputs of developers and end users which can lead in dozens of directions, further wasting precious time.

The ‘top level’ of any RDBMS environment is the server, the first point of reference in a careful analysis that when executed properly, should steer you in the right direction.

Things you can do:

Check the server kernel for memory deficits. Check your disk and I/O subsystems. There are several performance counters at our disposal to assist us in this task. RAM, and CPU of course are also major considerations.

Simple tools for gathering this data include: iostat, vmstat, top, and sar for Linux systems.
In Windows, the Perfmon utility can be used to gather the same sort of information.
Different flags (or switches) can be used to enhance data gathering, essential to time sensitive situations where SLA adherence determines the success or failure of your task.

From the Linux ‘Man’ pages:

Iostat

“The iostat command is used for monitoring system input/output device loading by
observing the time the devices are active in relation to their average transfer
rates. The iostat command generates reports that can be used to change
system configuration to better balance the input/output load between physical disks.”

 

Vmstat

“Vmstat (virtual memory statistics) is a computer system monitoring tool that collects
and displays summary information about operating system memory, processes, interrupts,
paging and block I/O. Users of vmstat can specify a sampling interval which permits
observing system activity in near-real time.”

 

Here is a useful script that can be used on a one-time basis for collecting vmstat and iostat metrics:

(You may have any number of graphic tools at your disposal to perform this analysis with, such as OEM Diagnostic Pack, Solar Winds, or even statspack will do the trick – it really depends on your preference and what is available to you.)

#!/bin/ksh
# get_iostat_linux.ksh
while true
do
iostat -x 300 1|\
sed 1,2d|\
awk ‘{ printf(“%s %s %s\n”, $1, $4, $5) }’ |\
while read HDISK VMSTAT_IO_R VMSTAT_IO_W
do
echo $HDISK
echo $VMSTAT_IO_R
echo $VMSTAT_IO_W

sqlplus -s / <<!
insert into
perfstat.stats\$iostat
values
(SYSDATE, 300, ‘$HDISK’, $VMSTAT_IO_R,$VMSTAT_IO_W);
exit
!

done
sleep 300
done

You may also run this in the background to collect and overwrite the statistics:

#!/bin/ksh

# Set the environment
ORACLE_SID=xxxx
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID|cut -f2 -d’:’`
PATH=$ORACLE_HOME/bin:$PATH
MON=`echo ~oracle/iostat`

#—————————————-

# Check to see if running/or start

#—————————————-

check_stat=`ps -ef|grep get_iostat|grep -v grep|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -lt 1 ]
then nohup $MON/get_iostat_linux.ksh > /dev/null 2>&1 &
fi

 

Top

The top command is used to show all the running processes in a Linux environment along with
current uptime, system load, memory and CPU usage.

Much of this information can be correlated to your Oracle instances, such as running processes that use shared memory of the SGA. The RES, SHR and %MEM columns are especially useful columns for diagnosing SGA issues.

Free

‘Free’ shows memory statistics for main memory and swap memory.
You can display the total amount of memory available to your system by utilizing the -t switch and you then display the amounts in bytes or megabytes by specifying the -b or -m switches.
Free can also be run for longer periods of time using the -s switch with a user specified delay (seconds).

Example: free -s 5

Sar

—–
Sar is a tool for viewing (and trending) performance statistics. It displays data over long periods of time and provides aggregated output with a minimal scripting effort.

With sar, you can collect statistics on memory, devices, network nodes, and many other metrics using switches (the -b switch allows you to see block device statistics, the -n switch to see network data and the -r switch to see memory utilization). You can also specify the -A switch to see all information.

Here is an example that allows you to select all information, while sending the output to another file for extended archival. To do this, use the -o switch and filename, the gathering interval and count how many intervals you will record.  If you omit the ‘count’ then sar will collect in a circular fashion, for example:

$ sar -A -o /var/log/sar/sar.log 600 >/dev/null 2>&1 &

This should be done judiciously however, because of possible system performance issues.

This concludes Part One. 

We now have a means of gathering information at the server level to assist us in determining at the outset,   whether the network, memory allocation, and/or device utilization are causing issues that impact performance. Stay tuned for part 2!

About The Author

Jason Katz

Jason Katz is an Oracle DBA of 18 years with experience in analyzing, planning, developing, testing and maintaining Oracle databases. Jason’s experience spans several industries including pharmaceutical, manufacturing, engineering, entertainment, and DoD. His background with platforms includes Solaris, HP-UX, AIX, Linux, Windows, Irix, and SCO. Jason’s personal interests include stringed instruments, printmaking, web page development, and raising Labrador Retrievers.