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 . . .

Advertisements

Locking issues in Oracle Database 10g

Many a times we face some issue, that a perticular sql query is holding a lock(s) on certain object. This will impact concurrency issue also for other DMLs. Some other user may complaint that he is using a DML query(Insert/delete/update) and qurey is taking lot of time. One of the reason for this might be that, the object which 2nd query wants to use is already locked by first query. In Oracle database 10g we can check the objects, which are locked by any other user. We have a table called V$LOCKED_OBJECTS, which lists all locks acquired by every transaction on the system.

Example, lets say a user is holding lock on certain table by some DML query from long time. As a DBA you want to find out the session and kill the same so that other can use the resource. Following steps can be followed to get this info and remove the lock.

SQL> select object_id, session_id, oracle_username, os_user_name, process from v$locked_object
2 ;


OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS
———- ———– ———————- —————– ——–
1991774 368 APPS plnops 26188
2019121 368 APPS plnops 26188
34376 507 APPS plnops 25922

Based on the above output you can check the object, which the user has locked. Example, if we take a object ID 1991774, then following query will give us the output as object_name.

SQL> select object_name from dba_objects
2 where object_id = 1991774;


OBJECT_NAME
——————————————————————————–
MSC_ST_DEMANDS

Also from SESSION_ID value we can get the session details of the user. Lets check for SESSION_ID 368. Here the SESSION_ID column of V$LOCKED_OBJECT is same as SID column of V$SESSION.

SQL> select SID, SERIAL#, sql_id from v$session
2 where SID=368;

SID SERIAL# SQL_ID
———- ———- ————-
368 545 ag47457n3xck1

From the SQL_ID we can easily get the SQL query, the user is using as shown below.

SQL> select sql_text from v$sql
2 where sql_id = ‘ag47457n3xck1’;

SQL_TEXT
——————————————————————————–
insert into MSC_ST_DEMANDS ( INVENTORY_ITEM_ID, SOURCE_INVENTORY_ITEM_ID,
ORGANIZATION_ID, WIP_ENTITY_ID, SOURCE_WIP_ENTITY_ID, ORDER_NUMBER,
WIP_STATUS_CODE, WIP_SUPPLY_TYPE, OPERATION_SEQ_NUM, USING_REQUIREMENT
_QUANTITY, QUANTITY_ISSUED, USING_ASSEMBLY_ITEM_ID, DEMAND_TYPE, PRO
JECT_ID, TASK_ID, PLANNING_GROUP, END_ITEM_UNIT_NUMBER, DEMAND_CLASS
, ORIGINATION_TYPE, USING_ASSEMBLY_DEMAND_DATE, MPS_DATE_REQUIRED, D
ELETED_FLAG, QUANTITY_PER_ASSEMBLY, ASSET_ITEM_ID, ASSET_SERIAL_NUMBER,
COMPONENT_SCALING_TYPE, COMPONENT_YIELD_FACTOR, REFRESH_ID, SR_INST
ANCE_ID) select x.INVENTORY_ITEM_ID, x.INVENTORY_ITEM_ID, x.ORGANIZATI
ON_ID, x.WIP_ENTITY_ID, x.WIP_ENTITY_ID, x.WIP_ENTITY_NAME, x.STATU
S_CODE, x.WIP_SUPPLY_TYPE, x.COPY_OP_SEQ_NUM, x.NEW_REQUIRED_QUANTITY,

SQL_TEXT
——————————————————————————–
x.QUANTITY_ISSUED, x.JOB_REFERENCE_ITEM_ID, x.DEMAND_TYPE, x.PROJECT_I
D, x.TASK_ID, x.PLANNING_GROUP,

The above query gives first 1000 characters of SQL query. To get the complete SQL you can check the column SQL_FULLTEXT in same table(V$SQL).

Once you get the session information for SQL, you can kill the session using following commands.

SQL> alter system kill session ‘368, 545’;

System altered.

Check performance after killing 1 session. Usually the performance improves and other users will appriciate you, as there query will run faster. (:-)).

Handling Large Objects (LOB) – Oracle Database 10g

Large Objects (LOB)

Large Objects (LOBs) are a set of datatypes that are designed to hold large amounts of data. A LOB can hold up to a maximum size ranging from 8 terabytes to 128 terabytes depending on how your database is configured. Storing data in LOBs enables you to access and manipulate the data efficiently in your application.

BLOB – Binary Large Object
Stores any kind of data in binary format. Typically used for multimedia data such as images, audio, and video.

CLOB – Character Large Object
Stores string data in the database character set format. Used for large strings or documents that use the database character set exclusively. Characters in the database character set are in a non-varying width format.

NCLOB – National Character Set Large Object
Stores string data in National character set format. Used for large strings or documents in the National character set. Supports characters of varying width format.

BFILE – External Binary File
A binary file stored outside of the database in the host operating system file system, but accessible from database tables. BFILEs can be accessed from your application on a read-only basis. Use BFILEs to store static data, such as image data, that does not need to be manipulated in applications. Any kind of data, that is, any operating system file, can be stored in a BFILE. For example, you can store character data in a BFILE and then load the BFILE data into a CLOB specifying the character set upon loading.

Following are the examples, which I tried my self with oracle database 10g on Linux installation and pasted the contents. I have also put the neccessary comments for your understanding. Kinly let me know in case you have any doubts.

Storing a image file in oracle database table using DBMS_LOB package

Step 1) create a table

SQL> create table loadalbum
  2  (name varchar2(100),
  3  image blob);

Table created.

Step 2) Create a Directory object in database which will point to operating system directory.

SQL> CREATE OR REPLACE DIRECTORY DOCUMENTS AS ‘/dy/oracle/product/db10g/photo’;

Directory created.

Step 3) Use the following procedure to insert a BLOB (image file) into database.
Note: The file (Sunset.jpg), which we are inserting into table should be present in DOCUMENTS directory location (/dy/oracle/product/db10g/photo) created above.

declare
    l_blob    blob;
    l_bfile    bfile;
begin
    insert into loadalbum values ( ‘Sunset’, EMPTY_BLOB() )
    // First create a Emply binary large object
    returning image into l_blob;                                              // and get a reference
    l_bfile := bfilename( ‘DOCUMENTS’, ‘Sunset.jpg’ );   // Get the pointer to a file in directory
    dbms_lob.fileopen( l_bfile );                                                // Open file
    dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );  

    dbms_lob.fileclose( l_bfile );
end;

SQL> select count(*) from loadalbum;

  COUNT(*)
———-
         1

Doing bulk upload of images to database.

You can use SQL*Loader utility to bulk upload regular text data. Same utility can be used to bulk upload images as well. Follow below steps to bulk up load the images

Step 1) Create a table

CREATE TABLE photoalbum (photolob BLOB);

Step 2)  Create a file (example photos.txt ) which will contain list of images to be uploaded.

bash-2.05$ cat photos.txt
Winter.jpg
Water_lilies.jpg
Sunset.jpg
Blue_hills.jpg

Step 3) create a control file required by SQL*Loader to upload data. Create new file called loadphotos.ctl and insert following content into it.
load data
infile photos.txt
into table photoalbum
(ext_fname filler char(200),
photolob lobfile(ext_fname) terminated by EOF)

The meaning of above code is
“please load the data listed in the file photos.txt into a table called photoalbum. The data will be loaded into the column of that table called ‘photoblob’ and has lobfile characteristics… that is, it’s binary data. Expect the file names for the binary files being loaded to be up to 200 characters in length. When you reach the end of the list of photos, terminate the load process”.

Step 4) Run SQL*Loader

Please note that photos.txt is used in control file and we are not giving absolute path, but relative path. So control file and photos.txt should be in same directory. And so also all images. See below.

bash-2.05$ pwd 
/dy/oracle/product/db10g/photo
bash-2.05$ ls -lrt
total 576
-rw-r–r–   1 db10g    oinstall  105542 Jun  2 23:43 Winter.jpg
-rw-r–r–   1 db10g    oinstall   83794 Jun  2 23:43 Water_lilies.jpg
-rw-r–r–   1 db10g    oinstall   71189 Jun  2 23:43 Sunset.jpg
-rw-r–r–   1 db10g    oinstall   28521 Jun  2 23:43 Blue_hills.jpg
-rw-r–r–   1 db10g    oinstall     127 Jun  2 23:46 loadphotos.ctl
-rw-r–r–   1 db10g    oinstall      54 Jun  2 23:48 photos.txt

bash-2.05$ sqlldr system/manager  control=loadphotos.ctl

SQL*Loader: Release 10.2.0.1.0 – Production on Sat Jun 2 23:48:21 2007Copyright (c) 1982, 2005, Oracle.  All rights reserved.Commit point reached – logical record count 4The above message shows that data is uploaded correctly. You can check the log file generated by SQL Loader at same location.SQL> select count(*) from photoalbum
  2  ;

  COUNT(*)
———-
         4

Uploading Word Document to oracle database

You can use database tables for storing work documents as well. Please follow the below steps for doing the same

Step 1) Create table

    CREATE TABLE my_docs
        (doc_id   NUMBER,
        bfile_loc BFILE,
        doc_title VARCHAR2(255),
        doc_blob  BLOB DEFAULT EMPTY_BLOB() );
     // Default value will be empty binary large object

Step 2) Create directory object

SQL> CREATE OR REPLACE DIRECTORY DOC_DIR AS ‘/dy/oracle/product/db10g/doc’;

Directory created.

Step 3) Create a procedure for uploading the file. Here inputs will be file_name and file_id.

Create or replace PROCEDURE load (in_doc IN VARCHAR2, in_id  IN NUMBER) IS  

temp_blob         BLOB := empty_blob();   

bfile_loc         BFILE;
BEGIN
  bfile_loc := BFILENAME(‘DOC_DIR’, in_doc);  

  INSERT INTO my_docs (doc_id, bfile_loc, doc_title)  VALUES (in_id, bfile_loc, in_doc);  
  SELECT doc_blob INTO temp_blob FROM my_docs WHERE doc_id = in_id FOR UPDATE;
  DBMS_LOB.OPEN(bfile_loc, DBMS_LOB.LOB_READONLY);  
  DBMS_LOB.OPEN(temp_blob, DBMS_LOB.LOB_READWRITE); 
  DBMS_LOB.LOADFROMFILE(temp_blob, bfile_loc, dbms_lob.getlength(bfile_loc));    

  DBMS_LOB.CLOSE(temp_blob);

  DBMS_LOB.CLOSE(bfile_loc);

COMMIT;
END load;

Step 4) Suppose I want to uplaod a .doc file present in the DOC_DIR directory (/dy/oracle/product/db10g/doc at OS level) created above.
The doc name is advait.doc.

Execute the above procedure as given below.

SQL> exec load(‘advait.doc’, 1);

PL/SQL procedure successfully completed.

SQL> select count(*) from my_docs;

  COUNT(*)
———-
         1

The docs advait.doc will be saved in database table.

Updating LOB column of database table

Step 1) Create a table
    CREATE TABLE book
    (title VARCHAR2(40),
    author VARCHAR2(40),
    text CLOB,
    author_pic BLOB);

Table created.

SQL>

Step 2) Insert some data

SQL> INSERT INTO book VALUES
  2  (‘My Book’,’Advait’,EMPTY_CLOB(), EMPTY_BLOB());

1 row created.

Step 3) Update column using PL/SQL procedure

DECLARE
my_text_handle CLOB;
my_buffer VARCHAR2(4000);
my_add_amt NUMBER := 0;
my_offset INTEGER := 1;
BEGIN
my_buffer := ‘This is my book’;    
my_add_amt := length(my_buffer); 
SELECT text  
INTO my_text_handle
FROM book
WHERE title = ‘My Book’ FOR UPDATE; 

DBMS_LOB.WRITE(my_text_handle, my_add_amt, my_offset, my_buffer);
COMMIT;
END;

PL/SQL procedure successfully completed.

SQL>

SQL> select text from book;

TEXT
——————————————————————————–
This is my book

This can hold 4G of characters.