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
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.
DBACOCKPIT--> Space → Segments → Detailed Analysis.
Enter the name of the index in the Segment column --> Continue
Main Data tab page.
Use Rebuild button.
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.
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
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.
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%.
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
10 | |
7 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 |