| Database Performance (merged2) 10.2 [message #405005] |
Tue, 26 May 2009 01:08  |
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I m taking database performance using Statspack report and giving some recommendations. I am pasting the recommendations below. Please tell me how to solve those recommendations. I am not able find the solution.
1 Recommendations(1):
Your database has relatively high logical I/O at 135,580 reads per second. Logical Reads includes data block reads from both memory and disk. High LIO is sometimes associated with high CPU activity. CPU bottlenecks occur when the CPU run queue exceeds the number of CPUs on the database server, and this can be seen by looking at the "r" column in the vmstat UNIX/Linux utility or within the Windows performance manager. Consider tuning your application to reduce unnecessary data buffer touches (SQL Tuning or PL/SQL bulking), using faster CPUs or adding more CPUs to your system.
2 Recommendations(1):
You have a high value for log file sync waits at 1.1 per transaction. Check to ensure that your application does frequent commits and consider moving your redo log files to the Write Accelerator. Also consider increasing your log_buffer size.
3. Recommendations(5):
You have high network activity with 650.6 SQL*Net roundtrips to/from client per second, which is a high amount of traffic. Review your application to reduce the number of calls to Oracle by encapsulating data requests into larger pieces (i.e. make a single SQL request to populate all online screen items). In addition, check your application to see if it might benefit from bulk collection by using PL/SQL "forall" or "bulk collect" operators.
You have 1,192.3 consistent gets examination per second. "Consistent gets - examination" is different than regular consistent gets. It is used to read undo blocks for consistent read purposes, but also for the first part of an index read and hash cluster I/O. To reduce logical I/O, you may consider moving your indexes to a large blocksize tablespace. Because index splitting and spawning are controlled at the block level, a larger blocksize will result in a flatter index tree structure.
You have high update activity with 964.1 db block changes per second. The DB block changes are a rough indication of total database work. This statistic indicates (on a per-transaction level) the rate at which buffers are being dirtied and you may want to optimize your database writer (DBWR) process. You can determine which sessions and SQL statements have the highest db block changes by querying the v$session and v$sessatst views.
You have high disk write activity with 55.0 per second. You should drill-down and identify the sessions that are performing the disk writes as they can cause locking contention within Oracle. Also investigate moving your high-write datafiles to a smaller data buffer to improve the speed of the database writer process. In addition, you can dramatically improve your disk write speed by moving the high-write datafiles to a WriteAccelerator.
You have high small table full-table scans, at 46.3 per second. Verify that your KEEP pool is sized properly to cache frequently referenced tables and indexes. Moving frequently-referenced tables and indexes to SSD or the WriteAccelerator will significantly increase the speed of small-table full-table scans.
4.Recommendations(3):
You have a high value for cache buffer LRU chain waits with 0.3% get miss, and you need to reduce the length of the hash chains for popular data blocks in your RAM buffer. Investigate the specific data blocks that are experiencing the latches and reduce the popularity of the data block by spreading the rows across more data blocks by reorganizing with a higher value for PCTFREE.
You have high library cache waits with 0.1% get miss. Consider pinning your frequently-used packages in the library cache with dbms_shared_pool.keep.
You have high redo allocation misses with 0.2% get miss. The redo allocation latch controls the allocation of space for redo entries in the redo log buffer (as defined by the log_buffer parameter). The redo allocation latch is a serialization latch that enforces the sequence of entries in the log buffer. A process can only write a redo entry after the redo allocation latch, which is why it´s a will-to-wait latch. Investigate increasing your log_buffer parameter.
Please advice this is very urgent.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
