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