Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Analysis of Performance Issues

Oracle RAC Cluster Tips by Burleson Consulting

 

The analysis of performance issues in RAC involves several key areas:

* Normal database tuning and monitoring.

* Monitoring RAC cluster interconnect performance.

* Monitoring workloads.

* Monitoring RAC-specific contention.

Normal database monitoring is covered thoroughly in any number of other texts. Thus, aspects of database tuning such as SQL tuning or standard SGA and internals tuning are not covered in this text other than the required extensions to normal database monitoring.

Monitoring RAC Cluster Interconnect Performance

The most important aspects of RAC tuning are the monitoring and tuning of the global services directory processes. The processes in the Global Service Daemon (GSD) communicate through the cluster interconnects. If the cluster interconnects do not perform properly, the entire RAC structure will suffer no matter how well everything else is tuned. The major processes of concern are the Global Enqueue Services (GES) and Global Cache Services (GCS) processes.

The level of cluster interconnect performance can be determined by monitoring GCS waits that show how well data is being transferred. The waits that need to be monitored are shown in v$session_wait, v$obj_stats, and v$enqueues_stats. The major waits to be concerned with for RAC are:

* global cache busy

* buffer busy global cache

* buffer busy global cr

In later versions of Oracle, the global cache is shortened to just gc. To find the values for these waits, the gv$session_wait view is used to identify objects that have performance issues. The gv$session_wait view contents are shown in the following results:

Description of GV$SESSION_WAIT

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 INST_ID                                            NUMBER
 SID                                                NUMBER
 SEQ#                                               NUMBER
 EVENT                                              VARCHAR2(64)
 P1TEXT                                             VARCHAR2(64)
 P1                                                 NUMBER
 P1RAW                                              RAW(4)
 P2TEXT                                             VARCHAR2(64)
 P2                                                 NUMBER
 P2RAW                                              RAW(4)
 P3TEXT                                             VARCHAR2(64)
 P3                                                 NUMBER
 P3RAW                                              RAW(4)
 WAIT_CLASS#                                        NUMBER
 WAIT_CLASS                                         VARCHAR2(64)
 WAIT_TIME                                          NUMBER
 SECONDS_IN_WAIT                                    NUMBER
 STATE                                              VARCHAR2(19)

New in 10g is the wait_class column which is used to restrict returned values based on 12 basic wait classes, one of which is the cluster wait class.

The following wait events indicate that the remotely cached blocks were shipped to the local instance without having been busy, pinned or requiring a log flush and can safely be ignored:

* gc current block 2-way

* gc current block 3-way

* gc cr block 2-way

* gc cr block 3-way

However, the object level statistics for gc current blocks received and gc cr blocks received enable the rapid identification of the indexes and tables which are shared by the active instances.

The columns p1 and p2 identify the file and block number of any object experiencing the above waits for the events, as shown in the following queries:

SELECT
  INST_ID,
  EVENT,
  P1 FILE_NUMBER,
  P2 BLOCK_NUMBER,
  WAIT_TIME
FROM
  GV$SESSION_WAIT
WHERE
  EVENT IN ('buffer busy global cr', 'global cache busy',
            'buffer busy global cache');

The output from this query should resemble the following:

INST_ID EVENT                   FILE_NUMBER BLOCK_NUMBER  WAIT_TIME
------- ----------------------- ----------- ------------ ----------
      1 global cache busy                 9          150 0
      2 global cache busy                 9          150 0

In order to find the object that corresponds to a particular file and block, the following query can be issued for the first combination on the above list:

SELECT
  OWNER,
  SEGMENT_NAME,
  SEGMENT_TYPE
FROM
  DBA_EXTENTS
WHERE
  FILE_ID = 9
  AND 150 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;

In this example, there is no need to worry about the instance as this SELECT is issued from within the cluster because they all see the same tables, indexes and objects.

The output will be similar to:

OWNER      SEGMENT_NAME                 SEGMENT_TYPE
---------- ---------------------------- ---------------
SYSTEM     MOD_TEST_IND                 INDEX

Once the objects causing the contention are determined, they should be modified by:

* Reducing the rows per block.

* Adjusting the block size.

* Modifying initrans and freelists.

All of these object modifications reduce the chances of application contention for the blocks in the object. Index leaf blocks are usually the most contended objects in the RAC environment; therefore, using a smaller block size for index objects can decrease intra-instance contention for index leaf blocks.

Contention in blocks can be measured by using the block transfer time. To determine block transfer time, examine the statistics global cache cr block receive time and global cache cr blocks received. The time is determined by calculating the ratio of global cache cr block receive time to global cache cr blocks received. The values for these statistics are taken from the gv$sysstat view shown below:

Description of GV$SYSSTAT

 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 INST_ID                                            NUMBER
 STATISTIC#                                         NUMBER
 NAME                                               VARCHAR2(64)
 CLASS                                              NUMBER
 VALUE                                              NUMBER
 HASH                                               NUMBER

The following script shows this calculation.

column "AVG RECEIVE TIME (ms)" format 9999999.9
col inst_id for 9999
prompt GCS CR BLOCKS
select b1.inst_id, b2.value "RECEIVED",
b1.value "RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG RECEIVE TIME (ms)"
from gv$sysstat b1, gv$sysstat b2
where b1.name = 'global cache cr block receive time' and
b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id 

INST_ID   RECEIVED RECEIVE TIME AVG RECEIVE TIME (ms)
------- ---------- ------------ ---------------------
      1       2791         3287                  14.8
      2       3760         7482                  19.9

If the transfer time is too high, or if one of the nodes in the cluster shows excessive transfer times, the cluster interconnects should be checked using system level commands to verify that they are functioning correctly. In the above SELECT result, instance two exhibits an average receive time that is 69% higher than instance one.

 
   
Oracle Grid and Real Application Clusters

See working examples of Oracle Grid and RAC in the book Oracle Grid and Real Application Clusters.

Order directly from Rampant and save 30%. 
 

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational