Performace Tuning Oracle Database 10g

Introduction

For tuning oracle database 10g we need to following some of the standard steps, which I call as protocol for performance tuning. To begin with as a DBA, we need to know at first that there is a problem with our database. Sounds weird !! but a fact. Many a times some DBA dont even know that, there is an issue with the database they are managing. Source for knowing about this are very normal like user will complaint about there programs are taking unusually long time. Also Database 10g provides lots of performance monitoring tool for checking the health of database. You can proactively check the health of your database manually by running some scripts or by checking the alerts on Enterprise manager. Also you can check the alerts from backend using DBA_OUTSTANDING_ALERTS table.

So once you come to know there is an issue, you need to find out what the issue is about. I mean to say that is the issue and not what is the cause of issue. Cause for the issue can be found once we know the issue. The issue can be realted to query taking time, or may be related to memory structure, or can be related to I/O issue as well.

Once you identify the issue, you can check the trend, get the root cause and form the solution. This may involve changing some parameters, clearing locks etc…

Final steps is implementing the solution.

In oracle 10g we have lot of performance view avaiable for checking the performanace of database. We will describe some of the ways to improve the performance and tune your database. I hope following discussion will help you getting the right configuration for your database to perform good.

Performance monitoring views

CPU resource monitoring:

At the very top we have to identify, whether an oracle process is consuming the resource or its some other OS process which is using CPU. This you can get from top command of unix. Following is the brief description.

_________________________________________________________

04:24:24 up 28 days, 2:54, 31 users, load average: 0.80, 1.12, 1.26
1195 processes: 1190 sleeping, 2 running, 3 zombie, 0 stopped
CPU states: cpu    user    nice system    irq softirq iowait    idle
total    1.9%    0.0%    4.7%   0.0%     0.1%    0.0%   93.0%
cpu00    2.9%    0.0%    4.4%   0.0%     0.0%    0.0%   92.6%
cpu01    1.5%    0.0%    5.6%   0.0%     0.3%    0.0%   92.4%
cpu02    2.5%    0.0%    3.8%   0.1%     0.3%    0.0%   93.0%
cpu03    0.9%    0.0%    5.0%   0.0%     0.0%    0.0%   93.9%
Mem: 8103716k av, 7862584k used, 241132k free,       0k shrd,   14136k buff
5462332k actv, 1102660k in_d, 146424k in_c
Swap: 24314368k av, 1574788k used, 22739580k free                 4345360k cached

PID USER     PRI NI SIZE RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
5194 appmgr02 16   0 2652 2652   844 S     0.8 0.0 25:43   0 top
29525 oracle03 16   0 2524 2524   844 R     0.7 0.0   0:00   0 top
2357 root      15   0 19384 1952   984 S     0.6 0.0 129:28   1 dcstor32d
2356 root      15   0 19384 1952   984 S     0.1 0.0 15:30   3 dcstor32d
8367 oracle03 15   0 636M 633M 632M S     0.1 8.0   1:48   2 oracle
1 root      15   0   508 472   436 S     0.0 0.0   1:02   2 init
2 root      RT   0     0    0     0 SW    0.0 0.0   0:00   0 migration/0
3 root      RT   0     0    0     0 SW    0.0 0.0   0:00   1 migration/1
4 root      RT   0     0    0     0 SW    0.0 0.0   0:00   2 migration/2
5 root      RT   0     0    0     0 SW    0.0 0.0   0:00   3 migration/3
6 root      15   0     0    0     0 SW    0.0 0.0   0:00   1 keventd
7 root      34 19     0    0     0 SWN   0.0 0.0   0:00   0 ksoftirqd/0
8 root      34 19     0    0     0 SWN   0.0 0.0   0:00   1 ksoftirqd/1
9 root      34 19     0    0     0 SWN   0.0 0.0   0:00   2 ksoftirqd/2
10 root      34 19     0    0     0 SWN   0.0 0.0   0:00   3 ksoftirqd/3
________________________________________________________

From above stats we have following thing to say. Lets split the first line
04:24:24 –> Current time
up 28 days, 2:54 –> Continuous up time till now (28 Days, 2 hours and 54 mins)
31 users –> total number of users currently logged in
load average: 0.80, 1.12, 1.26 –> system load averages for the past 1, 5, and 15 minutes.

1195 processes: 1190 sleeping, 2 running, 3 zombie, 0 stopped –> This is clear, except zombie means not dead but halted because of some reason.

cpu00 – cpu03 are the 4 CPUs on the system. Stats about each CPU is available in the form of usage table.
User column –> CPU used by users
Nice column –> The NI (nice) value is the dispatching priority of the task, and refers to the rate that the task receives services from that CPU.
system column –> CPU usage by system processes
iowait column –> wait events on different CPUs.
idle column –> Percentage of CPU, which is idle.

Next comes the memory part for the server.

Mem: 8103716k av, 7862584k used, 241132k free, 0k shrd, 14136k buff
5462332k actv, 1102660k in_d, 146424k in_c
Swap: 24314368k av, 1574788k used, 22739580k free 4345360k cached

The important numbers to note here is

8103716k is the total memory available.
7862584k is the currently used memory
241132k is the free memeory

Total swap memory 24314368k
Used swap memory is 1574788k
and free swap memory is 22739580k.

Other listing is the list of processes that are currently running on the system and it list the process in descending order. Here you can see if there are some oracle processes then we can conclude that oracle processes are consuming CPU time.

To check oracle processes involved in high CPU usage we can check the views V$SYSSTAT and V$SESSTAT

To begin with you can check the view V$SYSSTAT which list all the system statistics that a database can capture for you. The table contain statistics name and its value. For example we have

SQL> select * from v$sysstat
2 where name = ‘CPU used by this session’;

STATISTIC# NAME
———- —————————————————————-
CLASS VALUE STAT_ID
———- ———- ———-
12 CPU used by this session
1 5448603 24469293

This gives the total CPU used by all the sessions.

To check the CPU and DB TIME used by individual sessions we can check V$SESSTAT. Please note that for DB TIME statistic# is 13.

SQL> select * from v$sesstat
2 where statistic# = 13
3 order by value desc;

       SID STATISTIC#      VALUE
———- ———- ———-
1025         13   14904405
993         13   14904380
1019         13   14895026
968         13   11158910
840         13    7445590
925         13    3505544
912         13    3504272

Now we see that SID 1025 is using high DB time usage. For checking the CPU time used we can use statistic# 12. These statistic# we can get from v$statname.

SQL> select * from v$sesstat
2 where statistic# = 12
3 order by value desc;

       SID STATISTIC#      VALUE
———- ———- ———-
1092         12      47445
1088         12      33410
1089         12      24896
1063         12      19364
1072         12      13726
1090         12      10588
963         12      10465
850         12       9955
871         12       9001

So as per the records, using having SID 1092 is using maximum CPU time. Note that these times are in microseconds.

Once we get the SID we can check for all the details of the user from V$SESSION. It also gives the resource intensive sql query that user is using.

SQL> select SID,serial#,sql_ID from v$session
2 where SID=1025
3 ;

SID SERIAL# SQL_ID
———- ———- ————-
1025 3307 ay44jj6hphbuz

And as usual you can get the full sql query from V$SQL.

Checking the IO resource statistics:

We can check the IO statistics from OS level commands. Again if OS commands says that oracle processes doing lot of IO then in that case we can dig inside and check which user is causing problems.

For check IO stats at unix level for example we have iostat command as follows.

-bash-2.05b$ iostat
Linux 2.4.21-37.0.0.1.4.ELhugemem (ap6188rt) 06/09/2007

avg-cpu: %user %nice %sys %iowait %idle
5.49 0.00 4.90 0.12 89.48

Device:    tps         Blk_read/s         Blk_wrtn/s         Blk_read          Blk_wrtn
sda           1.05        5.27                     13.23                   12823196         32184706
sda1         0.81        5.26                     9.05                    12800280         22003398
sda2         0.25       0.01                     4.19                     22452                10181306

If we see here, it generates 2 reports. Once is CPU utilization report and other is
disk IO report. In CPU utilization report we get to see the average CPU consumption of all
CPU. User, nice, sys, iowait and idle is already explained above.

In case of disk report we have following.

tps –> this is the transfer rate/second in terms of number of requests
Blk_read/s –> is the number of blocks read at a time per second. Each block size is 512 bytes.
Blk_wrtn/s –> is the number of blocks written at a time per second. Again each block is 512 bytes.
Blk_read and Blk_wrtn –> will give you the total number of blocks read and written.

If you see heavy input-output on a device check for oracle files location. And oracle files happen to be on same device then we can say that heavy IO is because of some oracle process (which may be because of some user executing a heavy query).

In that case again, you have 3 views to confirm that.

First check the view V$SESSION_WAIT

SQL> select SID, event, wait_time, seconds_in_wait, state from v$session_wait
2 where upper(wait_class) != ‘IDLE’;

SID EVENT
———- —————————————————————-
WAIT_TIME SECONDS_IN_WAIT STATE
———- ————— ——————-
941 SQL*Net message to client
-1 0 WAITED SHORT TIME

Here Event is SQL*Net message to client wait time = -1. Usually when wait_time column is -1 then actual wait time is less then 1/100th of second. In that case state column will show “WAITED SHORT TIME”.

Also if wait_time column is zero then state column will have value “WAITING” and we can infer that currently the process is waiting.

If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition. If WAIT_TIME 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait.

From this table you can get the SID of the user who is waiting. You can then check the view V$SESS_IO to get the info about how the user is performing in terms of writting and reading the blocks at OS level.

Also we can check V$SESS_IO to get the stats about IO activites of users. We can get the SID of user who is doing maximum physical reads.

SQL> select * from v$sess_IO
2 order by PHYSICAL_READS desc;

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES
——- ———- ————— ————– ————-
CONSISTENT_CHANGES
——————
1092 298236 3975622 170767 362249
974

1093 0 155 18401 0
0

871 6872 4535603 12013 8340
51

Here we can see SID 1092 is doing high physical reads, which is nothing by IO.

Consistent changes – logical i/o, how many changes were applied to blocks for read consistency purposes. (Consistent mode changes)

CONSISTENT_CHANGES – Consistent changes for this session
CONSISTENT_GETS – Consistent gets for this session

CONSISTENT_GETS shows the number of blocks requested in consistent mode, for consistent read operations. Consistent gets is the number of blocks requested in consistent mode. Blocks are requested in consistent mode for consistent read operations. When a block is requested in consistent mode, if any changes have been committed or made to that block since the requesting statement (or transaction) began, then they must be rolled back for the purposes of the read, to give a consistent view of the data at that time.

When a block is requested in consistent mode, any changes will be rolled back so the SELECT query can get a read-consistent view of the data as of the time it started.

Finally you can also check V$SESSION view to get all information of the user and SQL query he is running.

SQL> select program from v$session
2 where SID=1092;

PROGRAM
————————————————
oracle@ap6188rt (SMON)

So we see here SMON process was responsible for doing IO.

To Be Continued . . .