Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

How to identify which process causing High disk I/O?

Rashid Ali
Frequent Advisor

How to identify which process causing High disk I/O?

I check the system performance using GlancePlus and found out that one process called "ora_dbw0_oracle" is the top disk I/O user and that is causing the slow response. But the question is how can I find out which oracle user's process is causing ora_dbw0_oracle to read/write so heavily?

By the way, I also noticed that average Memory utilisation is always about 98~100% and swap util is about 65%, the average disk I/O sometimes can be as high as 80%, but the cpu usage is very low. Does it mean we are having memory bottleneck or disk I/O bottleneck?

Thanks for any input
8 REPLIES
Manju Kampli
Trusted Contributor

Re: How to identify which process causing High disk I/O?

Zhang,

for the first question, Your local DBA should be able to answer. I believe there are some utilities in Oracle which can tell you what process caused the ora_dbw0_oracle process to start..


In your second question please check what is the buf cache utilisation. Default is kept for 50%.
In the swap utilisation there are two metrics.
Reserved and Used. If it is only reserved.. it means it is not doing any paging/swaping but reserved for the processes which are currently running on the system. If it shows are USED.. then the process are being paged out and swapped out.

check whether psuedo swapping enabled. If yes.. please disable it. it does eat some memory to space some swap space.

if it is really doing lot of paging/swaping then the CPU SYS utilisation will be quite ( which is not in your case)

if all the above thigs are OK then you need to buy more memory

Hope this helps
Manju

Never stop "LEARNING"
Alexander M. Ermes
Honored Contributor

Re: How to identify which process causing High disk I/O?

Hi there.
You should check, whether it is one disk, which causes the I/O load ( glance ).
Then check, which files you have on that disk. If possible, separate online logs and offline logs from the data. This should be checked by your DBA. Then ask your DBA, whether he has activated Multi Threaded Server parameters in the init.ora file. This should be done, if your recources are heavily loaded.
Let your DBA activate more than one db-writer-process.
If the I/O load is just one disk, ask your DBA to setup partitions for the tablespaces / tables.
If you need more help, contact me at
Alexander_Ermes@web.de
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Rashid Ali
Frequent Advisor

Re: How to identify which process causing High disk I/O?

I checked the disk activities and it shows that high disk I/O is reflected on many disks and it just jumps from this disk to another one within a few seconds. In addition, I also got some quesitons as below,

Q1: May I know how to activate Multi Threaded Server parameters in the init.ora file?

Q2:How do I know whether psuedo swapping is enabled or not and how can I enable it?

Q3: To what extend of swap utilisation shows short of memory?

Q4: I found swapmem_on is set to "ON", should I disable it?

Q5: How to find out cache hit/efficiency?

Q6: How do I know whether there is too high swapping/pagging?

Q7:dbc_min_pct and dbc_max_pct are set to the default value, which is 5/50 respectively, should I reduce dbc_max_pct to release more memory from buffer cache, so that application can have more user memory?

Q8: How about shared memory? How do I know whether there are any programs using shared memory? How do I know which process took up how much memory?



"swapinfo -tm" shows:
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 704 39 665 6% 0 - 1 /dev/vg00/lvol2
dev 220 38 182 17% 0 - 1 /dev/vg00/lvol4
reserve - 583 -583
memory 662 434 228 66%
total 1586 1094 492 69% - 0 -
Steve Slade
Frequent Advisor

Re: How to identify which process causing High disk I/O?

Hi Zhang,

It sounds like your database has a few performance problems! The process ora_dbw0_oracle is the Oracle background process which physically writes changes to the database. It does this on a number of instances (althought not a commit!).

1. When there are no free buffers in the buffer cache.
2. When there is a log switch.
3. When there is a checkpoint.
4. At regular timed intervals, it will process the dirty list - any buffers in the buffer cache which have been updated but not written back to disk.

As the database writer writes back oracle blocks it will be very quick and precise about what it is doing. How are you gauging that that is your problem? Usually, a server process named in your case probably oracle_oracle or something will be high on I/O as it reads from the datafiles for your data - maybe performing a large number of table scans. However, if you are performing a large amount of inserts than that will cause the database writer a lot of work, (also sql loader). What is the log writer process doing, (probably called ora_lgwr_oracle). There usually go in pairs, a large amount of writes/updates means a large amount of redo.

Do you know what is actually happening in these cases. Ask your DBA to run the utlbstat/estat reports, or have a quick look at the view v$waitstat;

The multi-thread option is not always a quick fix, but it can sometimes help if you have a large amount of users. (However, if the database is performing badly it can sometimes make it work) How about a quick look at top, does it show a large amount of users, what is their memory usage?

Hope this helps, a bit rusty on the performance front. There are a number of other things you/your dba can do at the database level to find badly performing queries.

Steve
If at first you do not succeed. Destroy all evidence that you even attempted.
Vincent Stedema
Esteemed Contributor

Re: How to identify which process causing High disk I/O?

Hi,

To monitor cache/hit efficiency, use "sar -b 10 5". Look at the %rcache and %wcache values. If %wcache is below 70 and/or %rcache is below 90, then there might be a memory bottleneck.

Is the dynamic buffer cache fully used? The ables option in glance will show this. If the buffer cache really is using up the maximum of 50 percent of system memory, then either you have to increase dbc_max_pct or put in more memory. However, judging from the data you supplied (Memory util. at 98%), increasing the buffer cache is not an option.

Regards,

Vincent
Pedro Sousa
Honored Contributor

Re: How to identify which process causing High disk I/O?

Hi!
ora_dbw0 is the process called DB writer.
This has to be running all the time, but should not be so 'heavy'.
This is maybe due to a corruption problem on the DB, or the dba is doing some customization on it. Ask him for some information on that.

About memory, CPU, SWAP, ... you can check wich process is 'eating' all this, but I bet it is dbw.

hope this helps
good luck.
Rashid Ali
Frequent Advisor

Re: How to identify which process causing High disk I/O?

May I know Oracle SGA is using buffer cache or user memory? If I reduce buffer cache, will Oracle SGA got less memory to cache data?
Steve Slade
Frequent Advisor

Re: How to identify which process causing High disk I/O?

Zhang, I am not sure what your first question is asking. The Oracle SGA is portion of Memory which is shared amongst all of the Oracle processes. It is defined by three (sometimes 4 if using MTS), in the init.ora file. (Where is the identifier of your database.) It has nothing to do with Buffer Cache in the HPUX sense, that it cache at the Operating System Level.

However, the SGA is split into three (sometimes 4 if using MTS), which are :

Shared Pool : Used for managing SQL & PL/SQL parsing & management. (Sometimes MTS)

Buffer Cache: This consists of data blocks which are used to cache access to the database. This is defined by the db_block_buffers parameter. Its total size is db_block_buffers * block size.

Redo Buffers : These are used to keep track of what updates have been carried out on the database.

Large Pool : can be used in MTS.

If you decrease the size of the buffer cache, then your disk I/O is likely to increase - the aim of the buffer cache is to try and ensure that whenever you access a row on your database it is already in memory and not on disk - for performance reasons.

You really need to state what is actually running at the time when dbwr is taking all of the CPU or I/O. Is it a large update/insert, sql loader?

If at first you do not succeed. Destroy all evidence that you even attempted.