Feedback of Modern Consolidated Database Environment

 

Since the launch of Oracle 12c R1 Beta Program (August 2012) at Trivadis, we have been intensively testing, engineering and implementing Multitenant architectures for our customers.

Today, we can provide our feedbacks and those of our customers!

The overall feedback related to Oracle Multitenant is very positive, customers have been able to increase flexibility and automation, improving the efficiency of the software development life cycles.

Even the Single-tenant configuration (free of charge) brings few advantages compared to the non-CDB architecture. Therefore, from a technology point of view I recommend adopting the Container Database (CDB) architecture for all Oracle databases.

 

Examples of Multitenant architectures implemented

Having defined Oracle Multitenant a technological revolution on the space of relational databases, when combined with others 12c features it becomes a game changer for flexibility, automation and velocity.

Here are listed few examples of successful architectures implemented with our customers, using Oracle Container Database (CDB):

 

  • Database consolidation without performance and stability compromise here.

 

  • Multitenant and DevOps here.

 

  • Operating Database Disaster Recovery in Multitenant environment here.

 

 


 

Advertisements

New Resource Manager options on Oracle 12c R2 Muntitenant

Since its introductions Oracle Resource Manager has been a key tool to guarantee performance stability and predictability, regulating the access to the database resources.

In the era of extreme consolidation, the role of Oracle Resource Manager is more important then ever, and Oracle Multitenant has extended its functionalities.

In Oracle 12.1 the resource manager was capable to handle:

  • Parallel server processes
  • CPU

The version 12.2 the following options have been introduced:

  • I/O rate limits for PDBs (Not supported on Exadata enviroment where I/O Resource Manager exists)
  • Memory  Management
  • Performance Profiles
  • Resource Monitoring

 

How to limit a PDB I/O consumption

--Limiting the number of I/O per second.
Alter System set MAX_IOPS = 9500 scope = BOTH;

--Limiting the amount of MB per second.
Alter System set MAX_MBPS = 70 scope = BOTH;

 

Memory  Management

Optionally starting from Oracle 12.2 it is possible to limit the PDB memory utilization using the following parameters:

  • SGA_Target                       – Max SGA size for PDB
  • SGA_Min_Size                   – Amount of guaranteed SGA size for PDB.
  • DB_Cache_Size                  – Amount of guaranteed Buffer Cache size for PDB.
  • DB_Shared_Pool_Size      – Amount of guaranteed Shared Pool size for PDB.
  • PGA_Aggregate_Limit      – Max PGA size for PDB.
  • PGA_Aggregate_Target    – Target PGA size for PDB.

 

Performance Profiles

The Profiles allows to create standard and common  Resouce Profiles, based for example on the different SLA levels (platinum, gold, silver), to grant directly to the PDBs.

DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
 plan => 'PDB_Silver', 
 profile => 'silver', 
 shares => 2, 
 utilization_limit => 30, 
 parallel_server_limit => 20,
 memory_limit=> 30
);

 

Resource Monitoring

The new system view  V$RSRCPDBMETRIC can be used to monitor  chargeback the I/O and memory consumpion.

SELECT r.CON_ID, p.PDB_NAME, r.IOPS,r.SGA_BYTES, r.SHARED_POOL_BYTES
FROM V$RSRCPDBMETRIC r, CDB_PDBS p WHERE r.CON_ID = p.CON_ID;

 


 

Oracle Cloud Computing

What is a database Cloud Computing?

This looks like the million dollar question; what we know for sure is that it is a quite recent technology and different people identify the Cloud Architecture by different key features (On-Demand, Broad Network Access, Resource Pooling, Rapid Elasticity, Measured Service). There are two main categories: Private and Public Cloud, which identifies respectively in house and outsourced Cloud installation. Focusing on Oracle Database technology the Private Cloud is a clustered infrastructure hosted on the company?s data center, therefore the IT department is responsible of the installation, maintenance and life cycle of all hardware and software components. In case of Public Cloud the company demands the management of the databases to a third party, which owns the infrastructure used to manage the databases of different customers.

Beyond the different marketing definitions of database cloud computing, Oracle provides a reach set of features to realize this kind of setup. The main component of this architecture is the Grid Infrastructure, which provides the cluster and storage foundation of Oracle Cloud Computing. On top of the Grid Infrastructure we have the RDBMS which enables RAC, RAC One Node and stand-alone database setups.

At this point, anyone can say that with the exception of the name, there is almost nothing new compared to the earlier version of Oracle Real Application Cluster (RAC). But Oracle Cloud Computing is much more than a simple multi-node RAC which hosts several databases; the introduction of features like Quality of Service Management (QoS), Server Pool, Instance Caging (extension of Resource Manager) and the enhancement of the existing ones, allow to consolidate all the environments guaranteeing to each application: the performance expected, the scalability for future needs, the availability to respect the Service Level Agreement (SLA), the best time to market, the governance of the entire platform and last but not least cost saving.

Obviously Oracle provides all the instruments to reach such great result, but it is up to the single organization to define and implement the most appropriate modus operandi in terms of OM, Life Cycle, Capacity planning and management, to obtain the result promised by this great technology.

Oracle Resource Manager

 

#############################################################
##    How to implement Oracle Resource Manager granting    ##
##    CONSUMER GROUPS to Clusterware services              ##
#############################################################

--Create a service for OLTP sessions
srvctl add service -d dbrac10 -s DBRAC10_OLTP -r dbrac11,dbrac12,dbrac13
srvctl start service -d dbrac10 -s DBRAC10_OLTP

--Create a service for BATCH sessions
srvctl add service -d dbrac10 -s DBRAC10_BATCH -r dbrac11,dbrac12,dbrac13
srvctl start service -d dbrac10 -s DBRAC10_BATCH


###################################################################
## Resource Plan Design:
## MGMT_P1=75% SYS_GROUP,  MGMT_P2=80% OLTP, MGMT_P2=10% BATCH, MGMT_P2=5%
## ORA$AUTOTASK_SUB_PLAN,  MGMT_P2=5% ORA$DIAGNOSTICS, MGMT_P3=70% OTHER_GROUPS           
###################################################################       

## Resource Plan Implementation:
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'REAL_TIME_PLAN', COMMENT => 'Respurce Plan for OLTP database');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'OLTP',CATEGORY => 'INTERACTIVE', COMMENT => 'OLTP sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'BATCH', CATEGORY => 'BATCH', COMMENT => 'BATCH sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'OLTP', COMMENT => 'OLTP group', MGMT_P2 => 80);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'BATCH', COMMENT => 'BATCH group',
MGMT_P3 => 70, PARALLEL_DEGREE_LIMIT_P1 => 6, ACTIVE_SESS_POOL_P1 => 4, MAX_IDLE_TIME => 240);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'SYS_GROUP', COMMENT => 'SYS group', MGMT_P1 => 70);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'OTHER group', MGMT_P4 => 50);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN',GROUP_OR_SUBPLAN => 'ORA$AUTOTASK_SUB_PLAN', COMMENT => 'ORA$AUTOTASK_SUB_PLAN group', MGMT_P3 => 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'REAL_TIME_PLAN', GROUP_OR_SUBPLAN => 'ORA$DIAGNOSTICS', COMMENT => 'ORA$DIAGNOSTICS group', MGMT_P3 => 10);
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (DBMS_RESOURCE_MANAGER.SERVICE_NAME, 'DBRAC10_OLTP', 'OLTP');
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(DBMS_RESOURCE_MANAGER.SERVICE_NAME, 'DBRAC10_BATCH', 'BATCH');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

###################################################################


## Grant the Switch to the Users
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();    
dbms_resource_manager_privs.grant_switch_consumer_group ('PERF_TEST','OLTP',FALSE); 
dbms_resource_manager_privs.grant_switch_consumer_group ('PERF_TEST','BATCH',FALSE); 
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

###################################################################       
## Enable the Resource plan with the FORCE Option to avoid the Scheduler window to
## activate a different plan during the job execution.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:REAL_TIME_PLAN';
###################################################################       

## Example of Group and Plan Deletion:

BEGIN
DBMS_RESOURCE_MANAGER.DELETE_PLAN (PLAN => 'REAL_TIME_PLAN');
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP(CONSUMER_GROUP => 'OLTP');
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP(CONSUMER_GROUP => 'BATCH');
END;
/


###################################################################
## SQL Queries for monitoring Resource Manager  utilization:
###################################################################

## Check the service name used by each session
select inst_id, username, SERVICE_NAME, count(*) from gv$session 
where SERVICE_NAME <>'SYS$BACKGROUND'
group by inst_id, username, SERVICE_NAME order by  order by 2,3,1;


## List the Active Resource Consumer Groups:
select INST_ID, NAME, ACTIVE_SESSIONS, EXECUTION_WAITERS, REQUESTS, 
CPU_WAIT_TIME, CPU_WAITS, CONSUMED_CPU_TIME, YIELDS, QUEUE_LENGTH, 
ACTIVE_SESSION_LIMIT_HIT from gV$RSRC_CONSUMER_GROUP where name in 
('SYS_GROUP','BATCH','OLTP','OTHER_GROUPS') order by 2,1;