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

#oracleF1 #PeformanceTuning #SQL

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.

Follow Me On:

[fansderin]

Just reading PPT line by line doesn't mean a session. Please try to improve