|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630756 is a reply to message #630744] |
Mon, 05 January 2015 03:09   |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Thank you for the separate reports, but please upload them as html as MC said. Or you can zip them, and again add a different suffix if necessary. I will not look at another PDF file.
The reports confirm my initial suggestions, and also show that the pattern of activity is fairly even, rather than figures being distorted by averaging over a long time. As your DB CPU time plus PL/SQL Exec time is only about 55% of DB TIme, it is probably worth doing some basic instance and database tuning before attacking the SQL.
Your worst wait event is free buffer wait, and the memory advisors recommend more buffer cache. I would
alter system set memory_max_target=80g scope=spfile;
alter system set memory_target=70g scope=spfile;
alter system set db_writer_processes=12 scope=spfile;
and restart.
Your next wait event is "log file switch (private strand flush incomplete. This is because you are log switching on average about every 4 minutes, so the peak rate is probably much higher. I would create new logfile groups of 4GB and drop the 2GB groups.
These changes should save you perhaps 35% of your DB Time: let's see what the reports look like after that.
And while that is running, what was your reasoning behind setting those _underscore parameters to cripple the cursor sharing mechanism? Have you considered using parallel processing?
Note that the "enq KO - fast object checkpoint" is caused by the changes in behaviour for serial direct reads, we can address that (and parallel query) next if necessary. However, you will probably need to identify the individual SQLs that are under performing and tune them individually.
|
|
|
|
| Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630763 is a reply to message #630756] |
Mon, 05 January 2015 03:45 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Looks to me like you're struggling because everything is waiting behind dbwr. I've seen this sort of thing before.
However...why 12 writer processes? That's a hell of a lot - surely making sure async io is working properly is the first port of call?
Check the OS is good for async and investigate disk_asynch_io and filesystemio_options values. The defaults (as I recall) actually disable async IO on linux.
Additionally check the availability of hugepages on the host and strongly consider manual memory management. Otherwise you might well thrash the host to death managing the memory.
|
|
|
|
| Re: Oracle Database 11g 11.2.0.4.0 running slow [message #630788 is a reply to message #630763] |
Mon, 05 January 2015 07:06 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I suggested 12 DBWRs because by default OP will have 6, and doubling this seemed reasonable given the possibility that the cache isn't being cleaned fast enough and there is plenty of spare CPU capacity. However, you may well be correct about the async I/O. The only Linux system I have right now is an Amazon EC2 with RedHat 5.9 and DB 12.1.0.1. By default, the aio libraries are linked in but not enabled:apexres1> select FILETYPE_NAME, ASYNCH_IO from v$iostat_file;
FILETYPE_NAME ASYNCH_IO
---------------------------- ---------
Other ASYNC_OFF
Control File ASYNC_OFF
Log File ASYNC_OFF
Archive Log ASYNC_OFF
Data File Backup ASYNC_OFF
Data File Incremental Backup ASYNC_OFF
Archive Log Backup ASYNC_OFF
Data File Copy ASYNC_OFF
Flashback Log ASYNC_OFF
Data Pump Dump File ASYNC_OFF
External Table ASYNC_OFF
Data File ASYNC_OFF
Temp File ASYNC_OFF
Data File ASYNC_OFF
Data File ASYNC_OFF
Data File ASYNC_OFF
apexres1> select FILETYPE_NAME, ASYNCH_IO from v$iostat_file;
FILETYPE_NAME ASYNCH_IO
---------------------------- ---------
Other ASYNC_OFF
Control File ASYNC_OFF
Log File ASYNC_OFF
Archive Log ASYNC_OFF
Data File Backup ASYNC_OFF
Data File Incremental Backup ASYNC_OFF
Archive Log Backup ASYNC_OFF
Data File Copy ASYNC_OFF
Flashback Log ASYNC_OFF
Data Pump Dump File ASYNC_OFF
External Table ASYNC_OFF
Data File ASYNC_ON
Temp File ASYNC_ON
Data File ASYNC_ON
Data File ASYNC_ON
Data File ASYNC_ON
@OP, so that is another thing to check.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
