What is Database Tuning?
Goal Of Database Tuning? To maximize use of system resources To perform task as efficiently To work rapidly as possible Why and when should one tune? Slow Physical I/O: -caused by poorly-configured disks -caused by unnecessary physical I/O -caused by poorly-tuned SQL.
Excessive CPU usage: -It means that there is little idle CPU on the system -caused by an inadequately-sized system, -caused by untuned SQL statements -caused inefficient application programs.
Latch Contention: Rarely is latch contention tunable by reconfiguring the instance. Rather, latch contention usually is resolved through application changes. Causes for Low Performance Bad Connection Management Bad Use of Cursors and the Shared Pool Bad SQL Use of Nonstandard Initialization Parameters Getting Database I/O Wrong Redo Log Setup Problems Long Full Table Scans High Amounts of Recursive (SYS) SQL Where should we do the tuning? Database Design Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements.
Application Tuning: Approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL.
Memory Tuning: By Properly size your database buffers (shared pool, buffer cache, log buffer, etc) By looking at your wait events, buffer hit ratios, system swapping and paging, etc.
Disk I/O Tuning: Database files needs to be properly sized. Also look for frequent disk sorts, full table scans, data fragmentation, etc.
Eliminate Database Contention: Study database locks, latches and wait events carefully and eliminate where possible. Tune the Operating System: Monitor and tune operating system CPU, I/O and memory utilization.
I am interested in hearing your feedback about this article , So that I can improve my lab series on sql for you. connect with us on facebook and Youtube
