O.P.S. Setup in Benchmark Runs :-
------------------------------
2 DB Servers - ES40 Alpha machines - 4 CPUs , 8 GB RAM - O.S. DEC 5.1
1st APP Server - ES40 Alpha machines - 4 CPUs , 8 GB RAM - O.S. DEC 5.1
2nd APP Server - SUNe420R - 4 CPUs , 4 GB RAM - Solaris 2.6
3rd APP Server - SUNe420R - 4 CPUs , 4 GB RAM - Solaris 2.6
ORACLE (on BOTH DB & APP Servers) - 8.1.7.0.0
CASE 1 - Database Brought up on 1 DB Server in SINGLE Instance WITHOUT the
OPS parameters Set
-------
ACTION 1 - When Firing 32,000 Transactions using 1200 Concurrent Processes
from 3 APP Servers onto a SINGLE DB Server
RESULT CPU Utilization = 70 % (on DB Server)
ACTION 2 - When Firing 16,000 Transactions using 600 Concurrent Processes
from the APP Servers onto the SINGLE DB Server
RESULT CPU Utilization = 40 % (on DB Server)
CASE 2 - SAME Database Brought up with 2 Instances on 2 DB Servers on a 2
Node Cluster by Setting the OPS Parameters
ACTION - When Firing 16,000 Transactions using 600 Concurrent Processes
from the APP Servers onto Each of the 2 DB Servers Simultaneously
RESULT CPU Utilization Again = 70 % on Each of the 2 DB Servers
Qs. 1 Why is CPU Utilization on 1 Instance of Database WITH OPS so Excessive
( = 70 % ) as Compared to that on a SINGLE Instance NON-OPS Database ( = 40
% ) for the SAME Transactions Volume ?
NOTE - Transactions Volume Constists of 65 % OLTP & 35 % Small Batch
Transactions which are More Read Than Computation Intensive
SVRMGR> Rem System wide wait events for non-background processes (PMON,
SVRMGR> Rem SMON, etc). Times are in hundreths of seconds. Each one of
SVRMGR> Rem these is a context switch which costs CPU time....
SVRMGR> select n1.event "Event Name",
2> n1.event_count "Count",
3> n1.time_waited "Total Time",
4> round(n1.time_waited/n1.event_count, 2) "Avg Time"
5> from stats$event n1
6> where n1.event_count > 0
7> order by n1.time_waited desc;
Event Name Count Total Time Avg Time
-------------------------------- ------------- ------------- -------------
SQL*Net message from client 5878909 246021256 41.85
row cache lock 33214 1516007 45.64
buffer busy due to global cache 179233 1263079 7.05
enqueue 43631 710976 16.3
latch free 380304 559890 1.47
global cache cr request 351465 485146 1.38
global cache lock null to x 39040 257749 6.6
db file sequential read 284104 160145 .56
global cache lock open x 44210 157445 3.56
buffer busy waits 66486 102513 1.54
log file sync 191919 100685 .52
Prompt TOP 10 WRITE PINGING OBJECTS :-
INST_ID NAME KIND FILE# STAT BLOCKS READ_PINGS
WRITE_PINGS
-------- -------------------- ---------- -------- ---- -------- ----------
-----------
2 NAME_AND_ADD_TABLE TABLE 7 cr 5 39
37
2 NAME_AND_ADD_TABLE TABLE 7 scur 2 17
15
2 LOGIN_TABLE TABLE 9 scur 1 23
13
2 NEXT_NUMBER_TABLE TABLE PART 8 cr 1 5
5
2 INTEREST_SUMMARY_MOD TABLE 9 scur 1 5
5
5 rows selected.
Prompt TOP 10 READ PINGING OBJECTS :-
INST_ID NAME KIND FILE# STAT BLOCKS READ_PINGS
WRITE_PINGS
-------- -------------------- ---------- -------- ---- -------- ----------
-----------
2 NAME_AND_ADD_TABLE TABLE 7 cr 4 29
27
2 PRINT_QUEUE_TABLE TABLE 9 cr 2 25
25
2 NAME_AND_ADD_TABLE TABLE 7 scur 2 17
15
2 NEXT_NUMBER_TABLE TABLE PART 8 cr 1 5
5
2 INTEREST_SUMMARY_MOD TABLE 9 scur 1 5
5
5 rows selected.
Prompt TOP 10 FALSE PINGING OBJECTS :-
INST_ID NAME KIND FILE# STAT BLOCKS READ_PINGS
WRITE_PINGS
-------- -------------------- ---------- -------- ---- -------- ----------
-----------
2 PRINT_QUEUE_TABLE TABLE 9 cr 6 81
83
2 NAME_AND_ADD_TABLE TABLE 7 cr 3 27
27
2 NEXT_NUMBER_TABLE TABLE PART 8 cr 1 5
5
3 rows selected.
P.S. Any Best Practices / parameters with OPS ??
Will Furnish Any Details Required ...
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: VIVEK_SHARMA
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
