I was wondering does anybody know what ora_dbw0_<SID> and ora_p000_<SID> actually do, because my Oracle 8i database is running considerable slower than normal. And these processes seem to be using alot of the disk time.
dbw0 is your database writer process, writing the blocks to your datafiles.
The p000 is your pmon process (monitoring the user processes i believe)
(This second I'm not sure about, if I'm wrong please someone correct me)
Do you have a lot of users accessing your database doing a lot of updates. This could account for what you see.
You may want to consider increasing the number of database writer processes to see if it helps.
If ora_p000_<SID> is PMON process, then what is ora_pmon_<SID> ???
$ ps -ef | grep ora_
oracle 28557 1 0 Mar 18 ? 0:11 ora_pmon_TEMA
oracle 28561 1 0 Mar 18 ? 14:49 ora_lgwr_TEMA
oracle 28565 1 0 Mar 18 ? 0:20 ora_smon_TEMA
oracle 28567 1 0 Mar 18 ? 0:00 ora_reco_TEMA
oracle 28563 1 0 Mar 18 ? 19:30 ora_ckpt_TEMA
oracle 28559 1 0 Mar 18 ? 212:13 ora_dbw0_TEMA
ora_dbw0_<SID> is the background Oracle process DBWR. The number 0 means that there is only one process running at this time.
Basically, this process(es) (DBWn) writes the contents of buffers to datafiles. The DBWn processes are responsible for writing modified (dirty) buffers in the database buffer cache to disk.
Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes (DBW1 through DBW9) to improve write performance if your system modifies data heavily. These additional DBWn processes are not useful on uniprocessor systems
ora_p000_<SID> are parallel processes executing SQL queries, DML, DDL or dataloads. Parallel executions are created for instance when you create tables with PARALLEL clause and setup those parameters in init<SID>.ora file:
Check Oracle docs for parallel execution.
If you are having performance problems, check the memory usage of those processes over a period of time by:
$ ps -ef | grep -v | grep ora_dbw0_<SID>
oracle 8350 1 0 10:21:45 ? 0:00 ora_pmon_db48
Then get the memory map for this background process (you will see the total memory used on the bottom):
I have been doing some reading up and trying this out with DBWn processes, but it seems that you can't just add the line
db_writer_process = 2
because it doesn't work.
What I think you also have to do is add the line.
db_block_lru_latches = 2
I haven't tried it yet because my system is live and I have to schedule time in to take it down.
Has anyone else increased the amount of DBWn processes they have? and what lines did you add to your init<SID>.ora file?
It is great that you read more info regarding this issue. That is the way to learn.
You are right, since Oracle8 db_writer_processes, each writer process is assigned to a LRU latch set. Oracle is recommending to set db_writer_processes equal to the number of LRU latches
(db_block_lru_latches) and not to exceed the number of CPUs on the system. In your case, if db_writer_processes =2 and db_lru_latches=2, then each writer process will manage its corresponding set.
You can use also the db_io_slaves parameter to do the same thing. With dbwr_io_slaves, there is still a master DBWR process and its slave processes. The IO slaves are now capable of asynchronous I/O on systems that provide native async I/O, thus allowing for much better throughput as slaves are not blocked after the I/O call.
Keep in mind that the both parameters relay on the fact that your OS trully supports asynchronous I/O. Check your kernel parameters to make sure that is true. Without asynchronous I/O the benefits from multiple DBWR are minimal - all writes/reads will be synchronous and the switching between the both DBWR could be overhead.
Thank you Clio, but I am still a little confused.
I am running HP 11.0 with Oracle 8.1.6 .
As I understand it Asynchronous means sending and receiving data, one character at a time, whereas synchronous means sending and receiving one field or screen at a time. Would it not be more advantagous to use synchronous instead of asynchronous?
Also from Oracle Metalink with subject matter "db_writer_processes vs dbwr_io_slaves: Which is Better?" started 15-12-99.
They say that you can have either have multiple slaves or mulitple dbwr's but not both.
I checked my kernel parameters and I found one "fs_async" = 0 , it's description was "select asynchronous writes"
However I looked at the database Orainit parameters and the option "disk_asynch_io" = TRUE , it's description was "Use asynch i/o for random access"
So which type should I increase slaves or writers?
Is my OS trully support async I/O?