| Simple query causes 50% CPU utilization [message #154978] |
Wed, 11 January 2006 09:55  |
TLegend33
Messages: 203
Registered: March 2005
|
Senior Member |
|
|
My client runs Oracle 8.1.7.3.0 on Win2K advanced server. This server was setup prior to my arrival. However, once the client received this server several years ago, they've been having this type of recurring problem. A single row query runs fine, however a multiple row query can consume 50% of the CPU. Some time ago, MTS was implemented because the client saw severe performance degradation once they hit 72 users. Thus, MTS was introduced to alleviate that problem. However, when there are 10 or fewer users connected, performance decreases significantly. I want to determine why 50% of the CPU is used when multiple row queries are executed. I'm not sure what parameters are incorrectly set. Below is the init.ora:
###############################################################################
db_name = Prod
db_domain = XXXX
instance_name = Prod
service_names = Prod.XXXX
db_files = 1024
control_files = ('D:\PRODdata\Data\control01.ctl',
'E:\PRODdata\data\control02.ctl')
open_cursors = 300
max_enabled_roles = 30
db_file_multiblock_read_count = 8
use_indirect_data_buffers = TRUE
# db_block_buffers = 264451 # 1.033 G
# db_block_buffers = 350000 # 1.367 G Max incr possible 030419 cls
# db_block_buffers = 512000 # 2G cls incr 030419 per maria
db_block_buffers = 1024000 # 4G cls incr 03.05.18 per maria
db_block_size = 4096
# shared_pool_size = 361064038 # 344 M
#shared_pool_size = 524288000 # 500M cls per request 03.05.18
shared_pool_size = 600m # changed from 500m by JZ 10/15/2000
large_pool_size = 52428800 # 50M c.s 03.05.18
java_pool_size = 0 # 40M CLS 03.05.18
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
mts_dispatchers = "(PROTOCOL=TCP)(DISPATCHERS=3)" # cls 04.07.30
mts_max_dispatchers = 5 # cls 04.07.30
mts_servers = 10 # cls o4.07.30
mts_max_servers = 20 # cls o4.07.30
# processes = 150
# processes = 250 # cls 04.02.23
processes = 500 # cls 04.03.11 per maria
parallel_max_servers = 5
#log_buffer = 32768
log_buffer = 524288 # changed by JZ 10/15/2004
#audit_trail = true # if you want auditing
audit_trail = false # if you want auditing, changed by JZ 10/15/2004
timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5M each
##### For archiving if archiving is enabled #####
log_archive_start = true
log_archive_dest = 'E:\ProdData\Archive'
log_archive_format = %%ORACLE_SID%%%S.ARC
# If using private rollback segments, place lines of the following
# form in each of your instance-specific init.ora files:
rollback_segments = ( RBS0, RBS01, RBS02, RBS03, RBS04, RBS05, RBS06, RBS07,
RBS08, rbs09, rbs10, rbs11, rbs12, rbs13, rbs14, rbs15, rbs16, rbs17, rbs18,
rbs19, rbs20, rbs21, rbs22)
# Global Naming -- enforce that a dblink has same name as the db it connects to
global_names = true
# Uncomment the following line if you wish to enable the Oracle Trace product
# to trace server activity. This enables scheduling of server collections
# from the Oracle Enterprise Manager Console.
# Also, if the oracle_trace_collection_name parameter is non-null,
# every session will write to the named collection, as well as enabling you
# to schedule future collections from the console.
# oracle_trace_enable = true
oracle_trace_collection_name = ""
# define directories to store trace and alert files
background_dump_dest = C:\admin\Prod\bdump
#Uncomment this parameter to enable resource management for your database.
#The SYSTEM_PLAN is provided by default with the database.
#Change the plan name if you have created your own resource plan.#
resource_manager_plan = system_plan
user_dump_dest = C:\admin\Prod\udump
remote_login_passwordfile = exclusive
os_authent_prefix = ""
# distributed_transactions = 10 commented out by JZ 10/15/2004
compatible = 8.0.5
#sort_area_size = 327380
sort_area_size = 524288 # Updated by JZ 10/15/2004
sort_area_retained_size = 327680
pre_page_sga = true #Added this at 8/30/2004. JZ
Any recomendations would be appreciated.
Thanks.
|
|
|
|
| Re: Simple query causes 50% CPU utilization [message #156120 is a reply to message #154978] |
Sun, 22 January 2006 12:16  |
SQLAREA
Messages: 21
Registered: January 2006
Location: Belgium
|
Junior Member |
|
|
Hi
Your SGA is about 5Gb on a Win2K box ? Maybe far too much.
How much RAM does your server has ?
How many CPU' s ?
Take statspack snapshots at regular time intervals and generate reports. You may want to upload some of them here. Depending on the output we will be able to give you valuable advice.
Regards
Guy Lambregts
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
