Resource Availability - RAC

To manage the request for data blocks, Oracle needs buffers in its memory where requests and locks related to these requests could be maintained. Such resources are global cache resources and maintained in the SGA of every instance in the cluster. Figure 13-2 illustrates both global cache and global enqueue resource buffers maintained in the shared pool and SGA.

13-2 does not illustrate all of the global cache and global enqueue buffers; those that could not be  illustrated have been grouped under the other gcs buffers category.

Resources available on any machine or to an Oracle instance are limited, meaning resources are not available in abundance and that if a process on the system would need them, they may not be immediately available. There is a physical limit on the amount of resources available on any system. For example, the processor resources is limited by the number of CPUs available on the system; the amount of memory or cache area is limited by the amount of physical memory available on the system and its availability depends on how busy the system is. Now for an Oracle process, this is further limited by the actual amount of memory allocated to the SGA. Within the SGA, the shared pool, the buffer cache, and so forth, are again preallocated from the total SGA (defined by the SGA_TARGET_SIZE parameter). These are memory allocations used by a regular single-instance configuration.

In a RAC environment, there are no parameters to allocate any global-specific resources such as global cache size or global shared pool area. Oracle allocates a certain portion of the available resources from the shared pool or SGA for global activity. 

The availability of global resources can be monitored using the view GV$RESOURCE_LIMIT.  

For example, the workshop in the next section illustrates a small example on how the global cache section can  be monitored.

RAC memory resource usage of the shared pool can be monitored by querying the V$SGASTAT view for GCS, GES, and KCL (kernel cache layer) related entries. 

Similarly, the current and maximum number of GCS resources/shadows (resource names gcs_resources and gcs_shadows) and GES resources/enqueues (resource names ges_ress and ges_locks) can be obtained by querying the V$RESOURCE_LIMIT views. 

In case there is a shortage of memory for the GCS/GES related components, Oracle will dynamically allocate memory from the free memory pool in the shared pool. 

It is recommended to carefully calculate the resources and enqueues as needed because dynamic memory allocation from the shared pool is an expensive operation. 

SQL> oradebug setmypid        

SQL> oradebug call kjcsdmp

Workshop:

In this workshop, we try to understand the sizing of the resources and state objects used by the GCS and GES in the SGA.

Step 1

The following query generates the output showing the current utilization of global resources:

Script: MVRACPDnTap_reslimit.sql

SELECT inst_id INT,resource_name, current_utilization CU, max_utilization MU, initial_allocation IA, limit_value LV FROM   gv$resource_limit WHERE  max_utilization > 0 AND resource_name LIKE 'g%'ORDER  BY inst_id, resource_name;

Step 2

Instance 5 could be low on global resources; the MU (MAXIMUM_UTILIZATION) has increased and is significantly high (1225463), whereas the IA (INITIAL_ALLOCATION) and the CU (CURRENT_UTILIZATION) are identical and low, indicating at times instance 5 was low on shared pool resources or there was significant utilization of global resources.

The memory allocated for GES resources and communications buffers is part of the SGA and shared pool. 

It is not used by the library cache and also cannot be configured via the server parameter file.

Step 3

As discussed earlier, most of the shared pool related resources, specifically those showing high resource sizes, are part of the shared pool. 

It's in the best interest to check the shared pool utilization for instance 5. 

Shared pool for all instances has been set to a size of 20G. 

SQL> SELECT inst_id, NAME,VALUE FROM gv$parameterWHERE  NAME LIKE 'shared_pool_size%'ORDER  BY inst_id;

Step 4

Further investigation of the instances indicated that automatic memory management has been enabled. This would mean that the 20G allocated to the shared pool is just a startup value and should grow or shrink based on demand. 

SQL> show parameter sga_

Step 5

Checking the GV$SHARED_POOL_ADVICE indicated that the server is low on the shared pool. 

Based on the frequent need for a higher shared pool, increasing the start value to 30G provided improved performances.

The chain effect of having a low-shared pool size can also be causing issues with performance of SQL statements. 

If the SGA or shared pool needs to grow or shrink, it could result in library cache invalidation, reparses, and therefore a slowdown in SQL execution.

■  Note:- The rule should be, when the MAX_UTILIZATION (Mu) gets close to the LIMIT_VALUE (lV) or the  INITIAL_ALLOCATION (ia) and remains the same at this value for a considerable amount of time, consider increasing  the SHARED_POOL

Script Name: MVRACPDnTap_sharedpooladvice.sql

SELECT inst_id INT, SHARED_POOL_SIZE_FOR_ESTIMATE "SP SIZE ESTD", SHARED_POOL_SIZE_FACTOR "SP SIZE FCTR", ESTD_LC_SIZE "ESTD LC SIZE", ESTD_LC_MEMORY_OBJECTS "ESTD MEM OBJ", ESTD_LC_TIME_SAVED  "ESTD TM SAVED", ESTD_LC_MEMORY_OBJECT_HITS  "EST. HITS"

FROM GV$SHARED_POOL_ADVICE;

Step 6

Why are only two instances in the cluster starving for shared pool sizes compared to the other instances? This could be an indication to look at the processes or application configured to run on these instances. 

The following query lists the current service-to-instance distribution: 

SQL>!srvctl status service -d PRDDB

Service BCKUP: prddb7, prddb8

Service BIAPPS: prddb1, prddb2, prddb3, prddb4, prddb5,

Service BIETL: prddb6,prddb7, prddb8

Service DBAUSER: prddb1, prddb2, prddb3, prddb4, prddb5, prddb6

Service ENDUSER: prddb7, prddb

Step 7

The RAC cluster has workload distributed using database services. 

And the average services times for these services indicate that the workload has not been distributed evenly across all the instances in the cluster.

Script: MVRACPDnTap_SvcStats.sql

break on SERVICE_NAME skip 1

SELECT 

SERVICE_NAME,       

INSTANCE_NAME,       

ELAPSEDPERCALL SERVICE_TIME,       

CPUPERCALL CPU_TIME,       

DBTIMEPERCALL  DB_TIME,       

CALLSPERSEC THROUGHPUT

FROM  GV$INSTANCE GVI, GV$ACTIVE_SERVICES GVAS, GV$SERVICEMETRIC GVSM

WHERE GVAS.INST_ID=GVSM.INST_ID

AND   GVAS.NAME_HASH=GVSM.SERVICE_NAME_HASH

AND   GVI.INST_ID=GVSM.INST_ID

AND   GVSM.GROUP_ID  = 10

AND   GVSM.SERVICE_NAME NOT IN ('SYS$BACKGROUND')

ORDER BY   SERVICE_NAME,   GVI.INST_ID;

Step 8

From the output, it can be noticed that instances 7 and 8 are both running services BIETL; that is probably why these servers are consuming higher resources. 

BIETL is a highly insert-intensive data loading application. 

Because this is an INSERT mostly application, data sharing between other instances is much lower. 

This was noticed from the extremely low activity across the interconnect on other nodes in the cluster. 

If there are a sufficient number of servers and data can be further distributed, the additional resources required could be obtained by adding additional nodes during the time of peak load and balance resource utilization.