Tools for tuning the db:
Oracle Database 11g Enterprise Edition
Oracle Enterprise Manager
Oracle Diagnostics Pack
- AWR, Automatic workload repository
- ADDM, automatic database diagnostic monitor
- ASH, Active session History
Oracle Database Tuning Pack
--SQL Tuning Advisor
This feature enables you to submit one or more SQL statements as input and receive output in the form of specific advice or recommendations for how to tune statements, along with a rationale for each recommendation and its expected benefit. A recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statements, or creation of SQL profiles.
– SQL Access Advisor
This feature enables you to optimize data access paths of SQL queries by recommending the proper set of materialized views and view logs, indexes, and partitions for a given SQL workload.
Oracle Real Application testing
- Database Replay
- SQL Performance analyzer
Performance improvement is an iterative process,removing the first bottleneck may not lead to performance improvement immediately becz another bottleneck leads greater performance impact, so accurately diagnosing the performance proplem is the fist step.
performance proplems results lack of throughput(the amount of work that can be completed in a specified time), unacceptable user or job response time
Gathering Database Statistics Using the Automatic Workload Repository
By default, the database gathers statistics every hour and creates an AWR snapshot, which is a set of data for a specific time that is used for performance comparisons
Initialization parameters are relevant for AWR:
STATISTICS_LEVEL, CONTROL_MANAGEMENT_PACK_ACCESS
statistics_level - TYPICAL or ALL -- for enabling AWR reports
control_management_pack_access - DIAGNOSTIC+TUNING - for enablin ADDM
Setting the DBIO_EXPECTED Parameter
ADDM analysis of I/O performance partially depends on a single argument,DBIO_EXPECTED, that describes the expected performance of the I/O subsystem. The value of DBIO_EXPECTED is the average time it takes to read a single database block, in microseconds. Oracle Database uses the default value of 10 milliseconds..
Set the value one time for all subsequent ADDM executions.For example, if the measured value is 8000 microseconds, then execute the following PL/SQL code as the SYS user:
EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 8000);
The database statistics collected and processed by AWR include:
■ Time Model Statistics■ Wait Event Statistics■ Session and System Statistics■ Active Session History Statistics■ High-Load SQL Statistics
Common Performance Problems Found in Oracle Databases
CPU Bottlenecks
Undersized Memory Strucutures
I/O Capacity issues
Suboptimal use of Oracle Database by the application
Concurrency issues
Database configuration issuesShort-lived performance problems
Degradation of database performance over time
Inefficient or high-load SQL statements
Object contention
Unexpected performance regression after tuning SQL statements
No comments:
Post a Comment