Analyzing and Optimizing Oracle Database Performance – Part I
Analyzing and Optimizing Oracle Database Performance – Part I
For Part II – Analyzing and Optimizing Oracle Database Performance – Part II
You will find summary information about memory management, analyzing and optimizing database performance below.
For detailed information check the related SAP Notes and related documentation, please.
I- Memory Management:
● The Dynamic System Global Area (SGA) is activated using the following parameters:
SGA_MAX_SIZE
DB_CACHE_SIZE (replaces previous parameter DB_BLOCK_BUFFERS)
How to Activate Dynamic System Global Area
1. Use BR*Tools to reset the db_block_buffers parameter.
2. Estimate good start values for the sga_max_size and db_cache_size parameters.
3. Use BR*Tools to change the parameter value of the db_cache_size and sga_max_size parameters.
4. Restart the database to activate your settings.
Related SAP Note:
617416 – Oracle9i: Dynamic SGA
789011 – FAQ: Oracle memory areas
● The Automatic Oracle Program Global Area (PGA) is activated using the following parameters:
WORKAREA_SIZE_POLICY (Default = AUTO)
This parameter sets the automatic PGA tuning dynamically to AUTO or switch off (MANUAL).
PGA_AGGREGATE_TARGET
This parameter defines the maximum amount of memory, in bytes, kilobytes (KB), megabytes (MB), or gigabytes (GB), that can be used for PGA. It is dynamically adjustable.
The following general recommendations apply to a standalone database server:
OLTP systems
PGA_AGGREGATE_TARGET = <Total physical memory> * 20%
Data warehouse systems
PGA_AGGREGATE_TARGET = <Total physical memory> * 40%
Additional guide value and lower limit
PGA_AGGREGATE_TARGET >= (SORT_AREA_SIZE + HASH_AREA_SIZE +…) *
PGA_AGGREGATE_TARGET is restricted by the SGA_MAX_SIZE + PGA_AGGREGATE_TARGET <= <phys. memory of DB server> formula.
● Related SAP Note:
619876 – Oracle9i: Automatic PGA Memory Management
789011 – FAQ: Oracle memory areas
II- DBA Cockpit Performance Monitors
The Performance Overview monitor is the entry screen of the performance monitor in the new DBA Cockpit. The following list describes some key performance figures for the Oracle database:
● The data buffer quality is based on the ratio of the number of physical reads to the total number of reads. The lower the ratio, the better the buffer quality. The data buffer quality must be greater than 94%. The statistics must be based on 15 million total reads. This number of reads ensures that the database is in a state of equilibrium.
● A ratio of user and recursive calls that is greater than 2 indicates good performance; otherwise, the number of recursive calls compared to the number of user calls is too high. Over time, this ratio declines because an increasing number of SQL statements are parsed in the meantime.
● If the number of reads per user call exceeds 30 blocks, it might indicate an expensive SQL statement.
● Values larger than 15 ms indicate an optimization issue. Check the value of the time/user call.
● The Data Dictionary (DD) cache quality needs to be better than 80%.
● In the comparison between the busy wait time and the CPU time, a ratio of 60:40 indicates a well-tuned system. Significantly higher values, such as 80:20, indicate room for improvement.
As of Oracle 10g, the relevant relationship between Busy Waits and CPU time for all user processes can be easily determined from V$SYS_TIME_MODEL.
SELECT
ROUND((STM1.VALUE – STM2.VALUE) / 1000000) “BUSY WAIT TIME (S)”,
ROUND(STM2.VALUE / 1000000) “CPU TIME (S)”,
ROUND((STM1.VALUE – STM2.VALUE) / STM1.VALUE * 100) || ‘ : ‘ ||
ROUND(STM2.VALUE / STM1.VALUE * 100) RATIO
FROM V$SYS_TIME_MODEL STM1, V$SYS_TIME_MODEL STM2
WHERE STM1.STAT_NAME = ‘DB time’ AND STM2.STAT_NAME = ‘DB CPU’;
● Related SAP Note;
618868 – FAQ: Oracle performance
● The Feature Monitoring submonitors in the DBA Cockpit show information about special Oracle features.
● How to prepare a list of SQL statements with the FULL TABLE SCAN access plan.
Go to:
Performance → Additional Functions → Display GV$SQL_Plan
Filter, enter TABLE ACCESS in the OPERATION field
enter FULL in the OPTIONS field.
Goto Shared Cursor Cache with SQL_ID to find the expensive SQL.
You can check GV$SQL_Text view also.
Or
Enter the following SQL statement in the Enter SQL statement register:
select operation, options, v$sqltext.sql_id, v$sqltext.piece, v$sqltext.sql_text
from v$sqltext inner join v$sql_plan on (v$sqltext.address=v$sql_plan.address)
where operation=’TABLE ACCESS’ and options=’FULL’
order by v$sqltext.sql_id, v$sqltext.piece;
● Detailed Analysis of Expensive SQL Statements
Run transaction DBACOCKPIT and choose Performance → SQL Statement Analysis → Shared Cursor Cache: Analysis of Shared Cursor Cache.
On the resulting screen, analyze the SQL statements that are placing the greatest load on your database system. You can find the statement that causes the buffer gets in the SQL statement column. Expensive statements that place less than a 5% load on the database have little impact on system performance, even when optimized.
III- Expensive Programs
Use the ST03N Transaction Profile to Find Expensive SQL Statements
Sort the Total Database Time column in ascending order and calculate the sum. Consider tuning transactions that consume more than 5% of the summed (total) database time.
IV- Create Database Statistics
Recommended strategies for creating database statistics are as follows:
- SAP recommends regular creation of database statistics. Note that inaccurate database statistics, known as old statistics, may lead the cost-based optimizer to an inappropriate access path resulting in expensive SQL statements and long dialog response times.
- SAP recommends that, in production operation, you update statistics daily using the DBA Planning Calendar (transaction DB13). If you use a tool, such as cron (UNIX) or at (Windows), SAP recommends the standard command, brconnect -u / -c -f stats -t all.
- SAP recommends that you use the mentioned strategies to update statistics, and also recommends that you use this call immediately after an SAP system upgrade or after a large amount of data has been changed in the database.
Oracle Dictionary Statistics
SAP recommends creating new dictionary statistics when there may be change in Oracle dictionary. After installing DB patch set, after SAP upgrade.
Create dictionary statistics once per quarter.
Using BRCONNECT : brconnect -u / -c -f stats -t oradict_stats.
Oracle System Statistics
SAP recommends creating system statistics when there is initial db creation, tablespace creation, DB upgrade and HW change.
Create system statistics also once per quarter.
Use BRCONNECT : brconnect -u / -c -f stats -t system_stats.
Identification of Problems with Optimizer Statistics
Detecting missing parts of statistics, like missing index or column statistics, is more difficult. To find tables with missing index or column statistics, special SQL scripts must be executed (for details, see SAP Note 588668).
The following SQL script finds missing index statistics:
SELECT T.TABLE_NAME, I.INDEX_NAME FROM DBA_TABLES T, DBA_INDEXES I WHERE I.TABLE_NAME = T.TABLE_NAME AND T.LAST_ANALYZED IS NOT NULL AND I.LAST_ANALYZED IS NULL AND INDEX_TYPE != ‘LOB’;
The database administrator can check the table statistics by executing the following SQL statement:
SELECT * from DBA_TABLES WHERE NUM_ROWS > 500000 AND SAMPLE_SIZE < 0.5 * 0.1 * NUM_ROWS;
The script focuses on tables with more than 500,000 table entries. For this category, the new table statistics must be based on a 10% estimate. The script then searches for tables where the sampling size is lower than 50% of the recommended threshold. In principle, you can choose other search options.
Related SAP Notes;
588668 – FAQ: Database statistics
724545 – Adjusting the CBO statistics manually using DBMS_STATS
to be continued…
Hi Yuksel,
I like your approach to gather all major topics of Oracle Database Performance. It's easy to deep-dive into every topic e.g. Instance optimization, SQL Statistics or SQL tuning. Can you drop a word about your stream of thinking? Under which surcumstances should we think about which topic?
THX for your approach to summarize things ...
Seasonal Greetings
Henrik
Hello Henrik,
When I was investigating performance issues I was always looking for this kind of info and KPIs. Percentages, where to look, which transactions etc.
At last I found a document and I tried to summarize the topics in general. May it helps the ones that are looking for this kind of info.
As it is mentioned in the general note 618868 - FAQ: Oracle performance we can answer your question as follows;
How can I determine whether the general database performance can be optimized?
You can use transaction ST03 or ST03N to determine the extent to which accesses to the Oracle database are involved in the overall response time. This portion is determined by using the "Database time" column and should not exceed 40% of the overall response time.
In addition to this we can check these also;
* Check Oracle parameters according to the note and arrange accordingly.
* Statistics (system, catalog and database) must be updated reqularly
* Memory management must be automatic
* Performance Overview KPIs are important. They must be below or over the standart values depending on the parameter
* Keep Datafiles' Rd Avg(ms) below 20 ms.
* Check fragmentation of the frequently accessed indexes
* According to the ST03N and ST04 values you can select programs and SQL statements to tune
Regards,
Yuksel AKCINAR
thanks for sharing... great document!!