Oracle.exe consuming 100%

Hi All,

We have Oracle9.0.1.x installed on a windows2000 machine with 1 GB RAM and 
800Mhtz P-III single CPU machine.

We have a medium sized OLTP cum OLAP system in which we are using Discoverer 
to view the reports. When ever i run a report on discoverer or even from 
sqlprompt, oracle.exe eats up the entire cpu of the machine, its always 
around 95-100% consupmtion by oracle.exe
I have tried increasing the shared pool , buffer cache and all other 
parameter.. but no gains..

I am copying part of my init.ora file here for your reference...

==============================================================

# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=466472960

########Added for Tunning#################

db_file_multiblock_read_count=8

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

# MTS
###########################################
dispatchers="(PROTOCOL=TCP)(SER=MODOSE)", 
"(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)", 
"(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"

# Network Registration
###########################################
instance_name=bhabha

###########################################
# Pools
###########################################
java_pool_size=52428800
large_pool_size=10485760
shared_pool_size=190443520

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
hash_area_size=1048576
#sort_area_size=1048576

###############Modified for Tunning########

sort_area_size=1048576
sort_area_retained_size=1048576
optimizer_index_caching=50
optimizer_index_cost_adj=50

###########################################

workarea_size_policy=manual

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS

######################Added for Discoverer #####
OPTIMIZER_MODE=CHOOSE
JOB_QUEUE_PROCESSES=10
============================================================

Size of my db is not more than 8-10 GB at present.

Any suggestion to tune the db or application or discoverer will be of great 
use.....

Regds
Himanshu / Pratibha





************************************************************
Pratibha Rohit Mane
Center for Development Of Advanced Computing,
Ganeshkhind Road , Pune University Campus, Pune-7

Ph : 020 5694000 (ext. 323)
     020 5694050
Email ID : [email protected]
           [email protected]
************************************************************
"Knowledge is power, but it is only useful if it is shared!"
************************************************************





>From: "Amir Hayat" <[email protected]>
>Reply-To: "Oracle" <[email protected]>
>To: "Oracle" <[email protected]>
>Subject: [oracle] User Management in Oracle
>Date: Thu, 24 Apr 2003 04:13:57 -0400
>
>Hello everyone,
>
>I am using Oracle 9i on W2k server. If I have to create dozen or two dozen 
>users, what is the best option? Do I have to create each user separetly, 
>assign him a role and profile or is there a short cut.
>
>If all the users have the same privileges then is it possible to create 
>them in a group form.
>
>Regards,
>
>Amir
>
>_________________________________________________________________
>MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.  
>http://join.msn.com/?page=features/virus
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to 
>unsubscribe send a blank email to %%email.unsub%%.


_________________________________________________________________
See the stars. Upclose! 
http://www.sulekha.com/movies/moviepicturegalleries.asp Bollywood 
photogallery.


---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].