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 


 

 

 


 

 

 
 

RAC GES Monitoring

Oracle Database Tips by Donald Burleson

This is an excerpt from the bestselling Grid book Oracle 10g Grid & Real Application Clusters, by Mike Ault and Madhu Tumma. 

RAC GES Monitoring

The monitoring of the RAC global enqueue services (GES) process is performed using the gv$enqueue_stat  view. The contents of the gv$enqueue_stat view are shown below:

 Description of the view GV$ENQUEUE_STAT

 
 Name                             Type
 -------------------------------- ----------
 INST_ID                          NUMBER
 EQ_TYPE                          VARCHAR2(2)
 TOTAL_REQ#                       NUMBER
 TOTAL_WAIT#                      NUMBER
 SUCC_REQ#                        NUMBER
 FAILED_REQ#                      NUMBER
 CUM_WAIT_TIME                    NUMBER

An example SELECT to retrieve all of the enqueues with total_wait number greater than zero would be:

select
   *
from
   gv$enqueue_stat
where
   total_wait#>0
order by
   inst_id,
cum_wait_time desc;
 
SEE CODE DEPOT FOR MORE SCRIPTS
 
 
   INST_ID EQ TOTAL_REQ# TOTAL_WAIT#  SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME
---------- -- ---------- ----------- ---------- ----------- -------------
         1 TX      31928          26      31928           0        293303
         1 PS        995         571        994           1         55658
         1 TA       1067         874       1067           0         10466
         1 TD        974         974        974           0          2980
         1 DR        176         176        176           0           406
         1 US        190         189        190           0           404
         1 PI         47          27         47           0           104
         1 CF     499314          23     499314           0            47
         1 TM      41928           8      41928           0            35
         1 MR         93          13         93           0            21
         1 HW        637           6        637           0             8
         1 XR          4           2          4           0             5
         1 DM          4           2          4           0             4
         1 SR          1           1          1           0             3
         1 SW          3           1          3           0             3
         1 TS          2           1          2           0             3
         2 TA       1064        1015       1064           0        437648
         2 PS       2208         325       1597         611        104273
         2 TX     440843          18     440843           0         62787
         2 US        197          41        197           0          8551
         2 IR        193          29        193           0          4593
         2 TT       4393         131       4393           0          3363
         2 CF     507497         540     507497           0          1726
         2 TM    1104694         101    1104694           0           766
         2 DM          5           2          5           0           483
         2 HW        444          41        444           0           108
         2 PI         90          18         90           0            81
         2 DL         32          18         32           0            55
         2 DR        176          23        176           0            52
         2 RT          4           4          3           1            12
         2 FB         10           2         10           0             6
         2 IA          1           1          1           0             4
         2 PG          1           1          1           0             3
         2 TS          4           1          4           0             3

 

According to Oracle, the enqueues of interest, as shown in the eq_type column of the gv$enqueue_stat   view in the RAC environment are:

n        SQ Enqueue: This indicates that there is contention for sequences. In almost all cases, executing an ALTER SEQUENCE command can increase the cache size of sequences used by the application. When creating sequences for a RAC environment, DBAs should use the NOORDER keyword to avoid an additional cause of SQ enqueue contention that is forced ordering of queued sequence values.

n        TX Enqueue: This is usually an application related issue pertaining to row locking.  Real Application Clusters processing can magnify the effect of TX enqueue waits. Performance bottlenecks can also appear on leaf blocks of right growing indexes as TX enqueue waits while the index block splits are occuring. TX enqueue performance issues can be resolved by setting the value of the initrans parameter for a TABLE or INDEX to be equal to the number of CPUs per node multiplied by the number of nodes in the cluster multiplied by 0.75. Another technique is to determine the number of simultaneous accesses for DML for the objects experiencing TX enqueues, and setting initrans to that value. Oracle Corporation recommends avoiding setting this parameter greater than 100. Another parameter that can reduce TX enqueues is maxtrans  . maxtrans determines the maximum number of transactions that can access a block. maxtrans will default to 255 and it is a good practice to reset this to less than 100.

PS (Parallel Slave Synchronization) and TA (Transaction Recovery) enqueues also seem to have some importance in the environment. Therefore, start with a wide sweep and then focus on the waits that are causing performance issues in the environment.

There are other interesting views that provide information on tuning that the DBA in a RAC environment should be aware of, for example:

n        gv$segment_statistics :  Provides statistics such as buffer busy waits  on a per segment basis. This allows tracking of exactly which segments, indexes or tables, are causing the buffer busy waits or other statistics to increment. To select against the gv$segment_statistics view, the user will want to SELECT for a specific statistic name where the value is greater than a predetermined limit. The contents of gv$segment_statistics are shown below:

Description of gv$segment_statistics

 Name                                      Null?    Type

 ----------------------------------------- -------- ------------

 INST_ID                                            NUMBER

 OWNER                                              VARCHAR2(30)

 OBJECT_NAME                                        VARCHAR2(30)

 SUBOBJECT_NAME                                     VARCHAR2(30)

 TABLESPACE_NAME                                    VARCHAR2(30)

 TS#                                                NUMBER

 OBJ#                                               NUMBER

 DATAOBJ#                                           NUMBER

 OBJECT_TYPE                                        VARCHAR2(18)

 STATISTIC_NAME                                     VARCHAR2(64)

 STATISTIC#                                         NUMBER

 VALUE                                              NUMBER

 

Another useful view shows which file IDs which have been remastered, which happens when they are transferred from one instance to another, this view is called gv$gcspfmaster_info and its contents are shown below:

 

Description of gv$gcspfmaster_info

 Name                                      Null?    Type

 ----------------------------------------- -------- -------

 INST_ID                                            NUMBER

 FILE_ID                                            NUMBER

 CURRENT_MASTER                                     NUMBER

 PREVIOUS_MASTER                                    NUMBER

 REMASTER_CNT                                       NUMBER

 

The file_id column corresponds to the data file ID.  The current and previous masters refer to the instances that are either the current or previous master of the specified file.  A view related to the gv$gcspfmaster_info view is the gv$gchvmaster_info view which shows the same information but for the PCM hash value IDs for specific resources that have been remastered. This views contents are shown below:

 

 Description of gv$gcshvmaster_info

 Name                                      Null?    Type
 ----------------------------------------- -------- -------
 INST_ID                                            NUMBER
 HV_ID                                              NUMBER
 CURRENT_MASTER                                     NUMBER
 PREVIOUS_MASTER                                    NUMBER
 REMASTER_CNT                                       NUMBER

 

To select against these views, it may be desirable to restrict on the remaster_cnt value being greater than a predetermined limit.

The gv$sqlarea   view has also been enhanced in Oracle Database 10g RAC.  The column cluster_wait_time in gv$sqlarea represents the wait time incurred by individual SQL statements for global cache events and will identify the SQL which may need to be tuned based on its contribution to RAC contention.

 
   
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%. 
 

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.