Oracle Performance Impact of High % User Rollbacks

Recently one of my customers asked to investigate a database performance problem on a system where for multiple reasons I wasn’t having direct access.

To troubleshoot the performance, I started asking few AWR reports; at the first glance I didn’t spot any bottleneck, but while re-reading the statistics I found a strange ration between number of Transactions and Rollbacks.

 

Extract of AWR Load Profile

 — Per Second Per Transaction
Rollbacks 476.8 0.9
Transactions 546.6

 

Because all others OS and database statistics were quite good, I decided to follow the trail of the high percentage of transactions rolled back.

Before any fast conclusion I checked the nature of the rollbacks:

  • transaction rollbacks: Oracle is automatically executing a rollback, this happens for example in case of constraint violation (i.e. Primary Key violation).
  • user rollbacks: number of times users manually issue the ROLLBACK statement or an error occurs during users’ transactions.

 

Extract of AWR Activity Stats

Total      per Second per Trans
user commits

125,878

69.90 0.13

user rollbacks

858,562 476.76

0.87

transaction rollbacks

2,704

0.75

0.00

rollback changes – undo records applied

46,132

25.60

0.06

data blocks consistent reads – undo records applied

423,396

235.14

0.49

transaction tables consistent reads – undo records applied

128

0.08

0.00

consistent changes

5,599,562

3,109.48

12.7

 

From the AWR analysis has emerged the following data:

  • 87% of the user’s transactions ended with a rollback.
  • While rolling back, Oracle applies 25.6 undo records/sec. which means that at least a certain percentage of user rollbacks generates “real work“.
  • Even more important in term of performance analysis
    • number of data block consistent reads 235.14 records/sec.
    • number of consistent changes 3109.48 times/sec.

 

Because the database load profile is 90% SELECT and 10% DML, and there are important values about data block consistent reads and consisten changes, we can assume that most of the activity is concentrated in a small number of user objects.

 

Next step: I have to gain access to the system and continue the investigation…

 

 

Advertisements

Oracle DB stored on ASM vs ACFS

Nowadays a new Oracle database environment with Grid Infrastructure has three main storage options:

  1. Third party clustered file system
  2. ASM Disk Groups
  3. ACFS File System

While the first option was not in scope, this blog compares the result of the tests between ASM and ACFS, highlighting when to use one or the other to store 12c NON-CDB or CDB Databases.

The tests conducted on different environments using Oracle version 12.1.0.2 July PSU have shown controversial results compared to what Oracle  is promoting for the Oracle Database Appliance (ODA) in the following paper: “Frequently Asked Questions Storing Database Files in ACFS on Oracle Database Appliance

 

Outcome of the tests

ASM remains the preferred option to achieve the best I/O performance, while ACFS introduces interesting features like DB snapshot to quickly and space efficiently provision new databases.

The performance gap between the two solutions is not negligible as reported below by the  AWR – TOP Timed Events sections of two PDBs, sharing the same infrastructure, executing the same workload but respectively using ASM and ACFS storage:

  • PDBASM: Pluggable Database stored on  ASM Disk Group
  • PDBACFS:Pluggable Database stored on ACFS File System

 

 

PDBASM AWR – TOP Timed Events and Other Stats

topevents_asm

fg_asm

 

 

PDBACFS AWR – TOP Timed Events and Other Stats

TopEvents_ACFS.png

fg_acfs

 

Due to the different characteristics and results when ASM or ACFS is in use, it is not possible to give a generic recommendation. But case by case the choise should be driven by business needs like maximum performance versus fast and efficient database clone.

 

 

 

 

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;