RAID level and Oracle files
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: RAID level and Oracle files

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    RAID level and Oracle files

    Currently, one of our customers has kept all the datafiles and redo log files on a set of disks with RAID 5? There are lot of I/O corresponding to certain datafiles. So, what can we do if severe I/O occurs at database level?

    Had it not been RAID, I would have kept the datafiles of tablespaces with high I/O on a different disk.

    Can I keep the datafiles of tablespaces with high I/O on a separate RAID 5 array? In this way, some of the datafiles would reside on one array and other datafiles would reside on other array. Would it help?

    Please correct me if my understanding is incorrect.

    Attached is the AWR report. It has high db file seuqential read. The database has run for 4 and a half hours and number of seconds of db file sequential reads is around 22000 i.e. 6 hours (approx).
    Attached Files Attached Files
    lucky

  2. #2
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Apologies. It is RAID 0. The customer just verified it.
    lucky

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    How many CPU cores you got?
    AWR duration is too high.
    I would have tuned the first 3 buffer gets and first disk read SQL and the again come back to AWR report to see how is the improvement.
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by malay_biswal View Post
    How many CPU cores you got?
    AWR duration is too high.
    I would have tuned the first 3 buffer gets and first disk read SQL and the again come back to AWR report to see how is the improvement.

    1. CPU cores: 16. Is there a way that we can multiple processors for all select statements. Is there some parameter setting?
    2. This is duration during which problematic queries were executed.
    3. first 3 buffer gets - didn't get it exactly.
    4. Nothing can be done at SQL level as application is only under maintenance. Development is stopped.
    Last edited by mahajanakhil198; 05-05-2010 at 01:57 PM.
    lucky

  5. #5
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    I found one parameter named parallel_max_servers. Should it be set equal to maximum number of CPU cores or less than that?

    I read the Oracle documentation but it does not talk about upper limit on this value.
    lucky

  6. #6
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    So your DB avg CPU usage is around 13%.
    Almost no load on CPU.
    so why to worry.
    When i said buffer gets the top 3 GETS SQL, you need to figure out do they really need those many GETS?

    I think the for disk read SQL, you can create index if it's possible to reduce disk reads. BTW that's executed 19 times, means around 4 times per hour, so not much to worry there.
    Regarding "parallel_max_servers" Yes it should be if it's a single instance on that machine. But it'll not benefit your queries since you do not use parallel hint.
    http://www.perf-engg.com
    A performance engineering forum

  7. #7
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by malay_biswal View Post
    So your DB avg CPU usage is around 13%.
    Almost no load on CPU.
    so why to worry.
    When i said buffer gets the top 3 GETS SQL, you need to figure out do they really need those many GETS?

    I think the for disk read SQL, you can create index if it's possible to reduce disk reads. BTW that's executed 19 times, means around 4 times per hour, so not much to worry there.
    Regarding "parallel_max_servers" Yes it should be if it's a single instance on that machine. But it'll not benefit your queries since you do not use parallel hint.
    No, there is lot of problem here. The queries at application level do not execute and time out UI application session.

    PARALLEL_AUTOMATIC_TUNING is set to false. Would queries benefit if I use PARALLEL_AUTOMATIC_TUNING?

    Can you also answer the RAID thing that I asked in the beginning of thread?
    lucky

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    trace the sessions, fix the problem - it really is that simple

Posting Permissions

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



Click Here to Expand Forum to Full Width