RAC performance Tuning with JMeter and AWR

Tuning Steps

  • Run AWR to figure out any RAC specific performance problems   
  • Change your application / Change schema objects
  • Verify performance gains with Jmeter
  • Repeat step 1 to step 3

 

Review current Performance status by using AWR

Reviewing SQL performance and Cluster Wait Time 
SQL ordered by CPU Time (Global)
    Captured SQL account for 54.4% of Total CPU Time (s): 235
    Captured PL/SQL account for 1.0% of Total CPU Time (s): 235
     Total     Per Execution     Percentage of Total      
SQL Id         CPU (s)   Elapsed (s) IOWait (s)   Gets   Reads    Rows    Cluster(s)    Execs   DB CPU  DB time  IO Wait    Gets    Read  Cluster    Execs      SQL Text
5m84n5c8k0k4c    49.37    3,317.61    6.99     319,368    792    30,000    2,023.00    30,000     21.04   41.29     1.80    5.83    2.99    57.73      8.07     Insert into logemp2 values (se...
a1h541jgwjdsj    25.56      533.35    6.66   3,566,809    285    30,000      459.23    10,000     10.89    6.64     1.71   65.06    1.08    13.11      2.69     select logemp2x0_.LOGID as LOG...
1aa2fpqtx557g    11.53       43.59    0.06      94,906     37    30,034       17.74    30,034      4.92    0.54     0.02    1.73    0.14     0.51      8.08     update seq$ set increment$=:2,...
8fv480az2kx9h     7.15      568.46    0.12      34,968     11    17,377      556.21    17,377      3.05    7.08     0.03    0.64    0.04    15.87      4.68     select increment$, minvalue, m...
anxm5vn9kxahy     5.91      289.35    1.71      57,697    151    10,000      266.43    10,000      2.15    1.77     0.08    0.68    0.01     3.77      2.69     select emp2x0_.EMPNO as EMPNO1...
8nrq20tnp5wvx     3.03      75.80      0.83      40,162     22    10,000       68.54    10,000     1.29    0.94     0.21    0.73    0.08     1.96      2.69     update EMP2 set ENAME=:1 , JOB...

Top DB Objects
    Top DB Objects by DB Time with respect to Application, Cluster, User I/O, buffer busy waits and In-Memory DB events only.
    Tablespace name is not available for reports generated from the root PDB of a consolidated database.
Object ID    % Activity    Event                    % Event     Object Name (Type)          Tablespace
99421        11.28         gc current block busy     6.05       SCOTT.LOGEMP2_PK (INDEX)    USERS
                           gc buffer busy release    3.11     
                           gc buffer busy acquire    2.04     
99419          4.82        gc buffer busy acquire    2.04       SCOTT.EMP2_PK (INDEX)       USERS
                           gc cr block busy          1.80     
99420          4.42        gc cr block busy          2.78       SCOTT.LOGEMP2 (TABLE)       USERS

-> Insert into log table logemp2 takes much DB CPU time and also consumers very high cluster Wait time 
   We insert 3x more records into our log table ( 30.000 ) compared the record count for table EMP2 ( 10.000 )

-> Waits for Primay Key SCOTT.LOGEMP2_PK seems to be the major Wait Event and needs to be tuned first 

Review and change/modify DB objects responsible for Logging

Current Object Creation scripts :  
create table logemp2 ( LOGID number, EMPNO number(22),  message varchar(300) );
alter table logemp2 add CONSTRAINT  logemp2_pk  PRIMARY KEY (logid);
desc logemp2;

drop sequence SEQ_LOGEMP2;
create sequence SEQ_LOGEMP2  minvalue 1 maxvalue 9999999999999999999999999999
  start with 1 increment by 1 NOCACHE ORDER;
  
-> First tuning suggestions:  
   Drop Primary Key for table Logemp2  andcreate sequence with CACHE Noorder 
  
New Object creation script :
create table logemp2 ( LOGID number, EMPNO number(22),  message varchar(300) );
desc logemp2;
drop sequence SEQ_LOGEMP2;
create sequence SEQ_LOGEMP2  minvalue 1 maxvalue 9999999999999999999999999999
  start with 1 increment by 1 cache 1000 noorder;    

Rerun JMETER tests and compare results

  • Jmeter Thread Group Configurtion : 10 Threads / Loop Count: 1000
  • Initial Jmeter test results

AWR1

  • Jmeter test results after applying above DDL changes

AWR2

  • This simple change increase the RAC performance by 50 %  . Not bad for 10  minutes tuning

 

Further Tuning steps

  • Lets have a look at the next tuning steps by reviewing AWR
 
Top Events
    Top Events by DB Time
    % Activity is the percentage of DB Time due to the event
Event                       Event Class   Session Type   % Activity  Avg Active Sessions
log file sync               Commit        FOREGROUND      35.32            3.20
log file parallel write     System I/O    BACKGROUND      15.39            1.39
gc buffer busy acquire      Cluster       FOREGROUND       9.06            0.82
gcs log flush sync          Other         BACKGROUND       8.67            0.78
gc cr block busy            Cluster       FOREGROUND      8.15             0.74

-> Using faster REDO LOGS and reduce the number COMMIT may be the next step of tuning

 

Reference

Leave a Reply

Your email address will not be published. Required fields are marked *