Oracle Database Performance Tuning - Oracle Database SQL Performance Issues - OPT Tips and Overview!

March 10, 2021  

 Dear DBA Connections,

In this video explained all about Oracle Database Performance Tuning Tips and Tricks Overview.

What is Database Tuning?

Database tuning is a group of activities used to optimize the performance of a database.

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. 

Hope this helps!

Cheers!

Ramesh.

Blog: https://www.sachinrameshoracledbablog.com/

LinkedIn: https://www.linkedin.com/in/ramesh-kumar-krishnamoorthy-3a67ba69/

Twitter: https://twitter.com/sachinrameshdba

Facebook: https://www.facebook.com/rameshkumar.krishnamoorthy.9/

Facebook Page: https://www.facebook.com/oraclef1

Instagram: https://www.instagram.com/oraclef1_1

YouTube: https://www.youtube.com/oraclef1

YouTube: https://www.youtube.com/oracledbatutorialtamil