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…