| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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=80000)
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=80000 gc_files_to_locks="1=25000" gc_rollback_locks="0-635=100REACH" db_block_buffers = 40000 shared_pool_size = 130000000 shared_pool_reserved_size = 25000000 large_pool_size = 10000000PARALLEL_SERVER = true
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 Buffers 327680000 bytes Redo Buffers 4210688 bytes
Thanks
> -----Original Message-----
> From: Gogala, Mladen [SMTP:MGogala_at_oxhp.com]
> 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> 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: VIVEK_SHARMA_at_infy.com 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: ListGuru_at_fatcity.com (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).Received on Mon Apr 23 2001 - 13:12:33 CDT
 |
 |