Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2002
    Posts
    9

    Post Unanswered: Oracle Processes!!!

    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.

    Please Help a desparate Oracle wannabe DBA.

    Thanks

  2. #2
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    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)

    Regards

    Ruud
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Tell 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.

    Rgs,
    Breen.

  4. #4
    Join Date
    Apr 2002
    Posts
    3
    Breen is absolutely correct and ehspal you are correct. P001 is for PMON which monitors Process of users..

    increase the DB writers to get the speed , even though they consume RAM little extra.

    KP/-

  5. #5
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Well,

    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:

    parallel_min_servers=
    parallel_max_servers=

    or

    parallel_automatic_tuning=TRUE

    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):

    $ /usr/proc/bin/pmap 8350

    Hope that helps,


  6. #6
    Join Date
    Apr 2002
    Posts
    9
    Many thanks clio_USA, I do have alot of users on the system, but I think the problem arose from a user running a script against the db when it was scheduled to go down for backing up.

    The db never went down and the only way I could shut it down was by killing the dbw0 process. After this the shutdown command worked.

    I do have two processors so the idea of having two db writers is a great idea. I am running Oracle8i on Hp-UX 11.0, so how would I go about creating another db writer process?

    Really hope you or someone else can help.

    Many thanks in advance

  7. #7
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    In the init<sid>.ora file, put in the line
    db_writer_processes=2

    Hope it helps
    Breen.

  8. #8
    Join Date
    Apr 2002
    Location
    beijing,china
    Posts
    20
    if you are not using Disk Array, the DBWR number no more than the number of yor hard disk drivers, and max is 10
    db2oracle

  9. #9
    Join Date
    Apr 2002
    Posts
    9
    Thanks everyone,

    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?

    Many thanks

    Let this thread continue please

  10. #10
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    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.

    Hope that helps,



    Trifon Anguelov
    Senior Oracle DBA

  11. #11
    Join Date
    Apr 2002
    Posts
    9
    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?

    I really hope you are someone else can help.

    Please

  12. #12
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    Check this out: http://www.netapp.com/tech_library/3146.html#6.4. (bullet 6.4). It will give an idea how to setup fs_async.

    In your case fs_async=0, the OS do not use async writes to file systems.

    Regarding "They say that you can have either have multiple slaves or mulitple dbwr's but not both", that is what it is - can't use them both.

    Hope that helps,


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •