RE: Excessive CPU Utilization with OPS ?
You probably have the releasable locking which is much more CPU intensive then the static (hashed) locking. Try with setting hashed locking on the tablespaces that you do not write too often (this assumes that your reference tables like ZIP codes, states, countries or discovered bugs in Microsoft Windows are all in the same tablespace or in a separate group of tablespaces). -Original Message- Sent: Monday, April 23, 2001 5:31 AM To: Multiple recipients of list ORACLE-L 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 5from stats$event n1 6where n1.event_count 0 7order by n1.time_waited desc; Event Name Count Total TimeAvg Time - - - SQL*Net message from client5878909 246021256 41.85 row cache lock 33214 1516007 45.64 buffer busy due to global cache 179233 1263079 7.05 enqueue 43631710976 16.3 latch free 380304559890 1.47 global cache cr request 351465485146 1.38 global cache lock null to x 39040257749 6.6 db file sequential read 284104160145 .56 global cache lock open x 44210157445 3.56 buffer busy waits66486102513 1.54 log file sync 191919100685 .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 scur2 17 15 2 LOGIN_TABLE TABLE 9 scur1 23 13 2 NEXT_NUMBER_TABLETABLE PART8 cr 1 5 5 2 INTEREST_SUMMARY_MOD TABLE 9 scur1 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_TABLETABLE 9 cr 2 25 25 2 NAME_AND_ADD_TABLE TABLE 7 scur2 17 15 2 NEXT_NUMBER_TABLETABLE PART8 cr 1 5 5 2 INTEREST_SUMMARY_MOD TABLE 9 scur1 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_TABLETABLE 9 cr 6 81 83 2 NAME_AND_ADD_TABLE TABLE
RE: Excessive CPU Utilization with OPS ?
Gogala OK , Will Check Revert Results It is True However that we have Set up Releasable Locks Primarily ( gc_releasable_locks=8) Qs. 1 Oracle lmd process on DB Server is taking About 30% of the CPU During the Benchmark Run period (of 1 Hour) . How can this be minimized ? Qs 2 Please Review the Parameters Correct Whereever Common Parameters for Both instances :- gc_releasable_locks=8 gc_files_to_locks=1=25000 gc_rollback_locks=0-635=100REACH db_block_buffers = 4 shared_pool_size = 13000 shared_pool_reserved_size = 2500 large_pool_size = 1000 PARALLEL_SERVER = true lm_ress=906840 lm_ress=906840 parallel_max_servers= 0 lm_locks=1160260 java_pool_size=1024000 Parameters Differing for the 2 Individual Instances For instance 1 :- instance_groups=abc parallel_instance_group=abc For instance 2 :- instance_groups=def parallel_instance_group=def NOTE - Thus we Have Diabled Inter Intra Node Parallel Query because in the Source Code we are NOT using PARALLEL Hints or Alter Session Commands SVRMGR show sga Total System Global Area 1425716196 bytes Fixed Size 103396 bytes Variable Size 1093722112 bytes Database Buffers32768 bytes Redo Buffers 4210688 bytes Thanks -Original Message- From: Gogala, Mladen [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 23, 2001 9:51 PM To: Multiple recipients of list ORACLE-L Subject: RE: Excessive CPU Utilization with OPS ? You probably have the releasable locking which is much more CPU intensive then the static (hashed) locking. Try with setting hashed locking on the tablespaces that you do not write too often (this assumes that your reference tables like ZIP codes, states, countries or discovered bugs in Microsoft Windows are all in the same tablespace or in a separate group of tablespaces). -Original Message- Sent: Monday, April 23, 2001 5:31 AM To: Multiple recipients of list ORACLE-L 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 selectn1.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 5from stats$event n1 6where n1.event_count 0 7order by n1.time_waited desc; Event Name Count Total TimeAvg Time - - - SQL*Net message from client5878909 246021256 41.85 row cache lock 33214 1516007 45.64 buffer busy due to global cache 179233 1263079 7.05 enqueue 43631710976 16.3 latch free 380304559890 1.47 global cache cr request 351465485146 1.38 global cache lock null to x 39040257749 6.6 db file sequential read 284104160145 .56 global cache lock open x 44210
RE: Excessive CPU Utilization with OPS ?
You should set your lm_locks parameter according to the instructions for your specific platform and try putting all locks into releasable mode first, by setting the number of locks to 0 in gc_files_to_locks -Original Message- Sent: Monday, April 23, 2001 2:46 PM To: Multiple recipients of list ORACLE-L Gogala OK , Will Check Revert Results It is True However that we have Set up Releasable Locks Primarily ( gc_releasable_locks=8) Qs. 1 Oracle lmd process on DB Server is taking About 30% of the CPU During the Benchmark Run period (of 1 Hour) . How can this be minimized ? Qs 2 Please Review the Parameters Correct Whereever Common Parameters for Both instances :- gc_releasable_locks=8 gc_files_to_locks=1=25000 gc_rollback_locks=0-635=100REACH db_block_buffers = 4 shared_pool_size = 13000 shared_pool_reserved_size = 2500 large_pool_size = 1000 PARALLEL_SERVER = true lm_ress=906840 lm_ress=906840 parallel_max_servers= 0 lm_locks=1160260 java_pool_size=1024000 Parameters Differing for the 2 Individual Instances For instance 1 :- instance_groups=abc parallel_instance_group=abc For instance 2 :- instance_groups=def parallel_instance_group=def NOTE - Thus we Have Diabled Inter Intra Node Parallel Query because in the Source Code we are NOT using PARALLEL Hints or Alter Session Commands SVRMGR show sga Total System Global Area 1425716196 bytes Fixed Size 103396 bytes Variable Size 1093722112 bytes Database Buffers32768 bytes Redo Buffers 4210688 bytes Thanks -Original Message- From: Gogala, Mladen [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 23, 2001 9:51 PM To: Multiple recipients of list ORACLE-L Subject: RE: Excessive CPU Utilization with OPS ? You probably have the releasable locking which is much more CPU intensive then the static (hashed) locking. Try with setting hashed locking on the tablespaces that you do not write too often (this assumes that your reference tables like ZIP codes, states, countries or discovered bugs in Microsoft Windows are all in the same tablespace or in a separate group of tablespaces). -Original Message- Sent: Monday, April 23, 2001 5:31 AM To: Multiple recipients of list ORACLE-L 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 selectn1.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 5from stats$event n1 6where n1.event_count 0 7order by n1.time_waited desc; Event Name Count Total TimeAvg Time - - - SQL*Net message from client5878909 246021256 41.85 row cache lock 33214 1516007 45.64 buffer busy due to global cache 179233 1263079 7.05 enqueue 43631710976 16.3 latch free 380304
