"The art of performance tuning is there is no rules". You have to use hit and try.
For incomplete transactions during AWR snapshot duration, AWR takes help from ASH snapshot to capture details.
DB Time=CPU+Wait time
enq: TM – contention can also happen due to table level lock(may be due to DDL)
Buffer busy waits: There can be storage issue as well where buffers are not getting flushed to disk.
Latch sleep breakdown: <Read more>
To reduce rows per block: alter table t minimize records_per_block;
Load profile:
Instance efficiency percent:
If parse CPU to parse elapsed is low then there can be shared pool issue also.
Why my cursor not shared?
Check details in v$sql_shared_cursor.
Gather system stats only when you change system components.
We can set optimizer_ignore_hints to true to ignore any hints supplied as part of queries. This can be done as part of upgrade activity to validate if hints are no longer required. Specially the hints those added to support missing functionality.
Oracle disk and I/O tuning:
/var/log/messages: to check storage issues
Disk scrubbing: is a background process that reads disks during idle periods to detect irremediable read er- rors in infrequently accessed sectors. Timely detection of such latent sector errors (LSEs) is important to reduce data loss.
In exadata it's done automatically and it keeps checking disk corruption.
iscsi storage: Cheapest and slowest
NFS storage:
We can even ask to store important data files into outer space of disk that is faster.
No comments:
Post a Comment