Analyzing and Optimizing Oracle Database Performance - Part II

[yakcinar]

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.

For Part I - Analyzing and Optimizing Oracle Database Performance - Part I

V- Analyzing Storage Quality

• DBACOCKPIT --> Space → Segments → Detailed Analysis

        

For a storage quality below %25 you must requilt the index. Between %25 and %50 you can check the index whether it is worth to rebuilt.

• Rebuild Indexes Using DBA Cockpit

               DBACOCKPIT--> Space → Segments → Detailed Analysis.

               Enter the name of the index in the Segment column --> Continue

               Main Data tab page.

               Use Rebuild button.

         

• To detect index fragmentation follow the instructions on SAP Note 771929 - FAQ: Index fragmentation

• The RSORAISQN report provides various heuristic algorithms for fast, exact analysis, and can be used for mass analysis and defragmentation of nonpartitioned B*tree indexes.

         

Ratio of Leaf Rows and Deleted Leaf RowsFigure

With "ANALYZE INDEX VALIDATE STRUCTURE" command total and deleted leaf rows can be determined for a nindex.

You can also do this using DBACOKPIT.

• Related SAP Notes;

For information on how to avoid problems while rebuilding or coalescing indexes, see SAP Notes on index rebuild online and index coalesce: 682926, 869521, 904188, and 332677.

332677 - Rebuilding fragmented indexes

444287 - Checking the index storage quality

682926 - Composite SAP note: Problems with "create/rebuild index"

869521 - Oracle <= 10g: TM locks with REBUILD ONLINE / CREATE ONLINE

904188 - Locks when you execute Index Rebuilds Online in parallel

970538 - Collective note RSORAISQN

VI- Identification of I/O Contention

I/O contention may occur when Oracle write and read processes  accesses the same disks at the same time.

I/O Contention – Identify Tablespace and Data File

You can check using: Performance → Wait Event Analysis → Filesystem Requests

Hot disks are caused by the following situations:

● If the total number of reads and writes is relatively low, there is no I/O contention.

● If the total number of reads and writes is high, check whether the Rd Avg (ms) is higher than 20 microseconds (ms).

● If the values deviate by more than 20% from the median value of the Rd Avg (ms) or Wrt Avg (ms).

VI- Cache Advisory Statistics

• DBACOCKPIT--> Performance → Statistical Information → SGA Monitor → Cache Advisory Stats

             

The figure shows that if the cache were 43,776 MB, rather than the current size of 49,152 MB, the estimated additional number of physical reads does not increase significantly. Increasing the cache size beyond 49,152 MB does not provide a significant benefit.

This view assists in cache sizing by providing information that predicts the number of physical reads for each potential cache size.

• Efficiency of the Usage of the Shared Pool

              

The Performance Overview monitor shows the respective parameters as follows:

● Shared SQL area-

The ratio of reloads to pins must be, at maximum, 0.04.

The pin ratio must be larger than or equal to 95%.

●  Dictionary cache-

The ratio of user calls to recursive calls must be at least 2 or higher.-

The data dictionary cache quality must also be greater than 80%.

• The Shared Pool Advisory Statistics

       Performance → Statistical Information → SGA Monitor→ Shared Pool Advice

         

The Parse Time field refers to the amount of time saved by keeping library cache memory (shared SQL area) objects in the shared pool, as opposed to the need to reload these objects.

• Tuning the PGA Using Advisory Statistics

Performance → Statistical Information → PGA Monitor

● Avoid PGA memory over-allocation

Make sure to set PGA_AGGREGATE_TARGET high enough (that is, large enough) so that the estimated over-allocation count is equal to zero.

● Maximize the PGA cache hit percentage

The optimum, ideal cache hit percentage value is 100%; however, this is only achievable with a high memory requirement.

VI- AWR (Automatic Workload Repository) Reports

The AWR enables historical performance analysis by taking snapshots on a periodic basis. The statistic values taken at the AWR snapshot reside in the memory of the database instance. Every time a snapshot is taken, these values are stored in tables located in the SYSAUX tablespace.

The AWR collects many classes of statistics when creating a snapshot. Some of them are wait event statistics , ASH, system statistics, SQL statistics, top segment statistics, OS statistics, Memory statistics and parameter values.

         

           

Related SAP Note 853576.

Active Session History Graph

You can check the Oracle Session History using this ASH List and ASH Graphic.

ADDM Report

You can create ADDM report that contains automated tuning recommendations based on the AWR Data.