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.
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.
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.
You can create ADDM report that contains automated tuning recommendations based on the AWR Data.