CPU reaches 100% on instance startup. Please Please Help..... Soon

[ronnie]

Hi,
When I start my Oracle instance the cpu reaches 100 5 in a matter of seconds.

I am running Oracle 8.1.7 on a Windows 2000 machine.

Here is my init.ora file extract


db_name = "sid7"

instance_name = sid7

service_names = sid7

db_files = 1024

control_files = ("D:\oracle\oradata\sid7\control01.ctl", "D:\oracle\oradata\sid7\control02.ctl", "D:\oracle\oradata\sid7\control03.ctl")

open_cursors = 300
max_enabled_roles = 30
db_file_multiblock_read_count = 8

db_block_buffers = 20000

shared_pool_size = 200837330

large_pool_size = 614400
java_pool_size = 0

log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800

processes = 150

parallel_max_servers = 5

log_buffer = 65536

utl_file_dir=*

#audit_trail = true # if you want auditing
#timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5M each

# Uncommenting the line below will cause automatic archiving if archiving has
# been enabled using ALTER DATABASE ARCHIVELOG.
# log_archive_start = true
# log_archive_dest_1 = "location=C:\oracle\oradata\sid7\archive"
# log_archive_format = %%ORACLE_SID%%T%TS%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, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6, RBS7, RBS8, RBS9, RBS10, RBS11, RBS12, RBS13, RBS14, RBS15, RBS16, RBS17, RBS18, RBS19, RBS20, RBS21, RBS22, RBS23, RBS24 )

# Global Naming -- enforce that a dblink has same name as the db it connects to
global_names = false

# 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 = D:\oracle\admin\sid7\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 = D:\oracle\admin\sid7\udump

db_block_size = 8192

remote_login_passwordfile = exclusive

os_authent_prefix = ""

distributed_transactions = 10
compatible = 8.1.0
sort_area_size = 131072
sort_area_retained_size = 65536
query_rewrite_enabled=true
query_rewrite_integrity=enforced


Please help

Ronnie

[gandolf989]

Your SGA is about 370 mb. If you don't have enough memory you system will need to do alot of swapping to start the database. Also you init file has some unusual parameters. If its not the memory, you may want to start commenting out those parameters and starting the database until you find the one that is the problem. The rule of thumb that I use for sizing an SGA is that it should be 50-60% of the total memory. So if you have 600-700 megs of ram then an SGA of 370 megs is ok. If you only have 256 megs then the system would be very inefficient.

[dknight]

What is your setup? Does an application server automatically connect with the DB?

[ronnie]

I think I found the problem.

One of our developers has written a pl/sql stored procedure which when executed results in this problem.

I briefly looked at his procedure and it looks fine.
Any ideas how do i debug the procedure and find out the killer sql.

Thanks
Ronnie

[JChiappa]

The best way is : make a TRACE of the PL/SQL in question and look for high access costs (lots of readed blocks, bad paths, and so on). If you want, put the TKPROFed results and the EXPLAIN PLAN in this forum, surelly we can say something.

Chiappa

[julian]

db_files = 1024

I have seen that in several init.oras (1024 instance files). I wonder what drives people to setting db_files to 1024 :-) I mean if you want so many why not 1000 :-)

[sonofsita]

you need more memory.
for tunig go through this doc
http://metalink.oracle.com/metalink/...&p_id=115698.1

[jmodic]

Originally posted by julian

db_files = 1024

I have seen that in several init.oras (1024 instance files). I wonder what drives people to setting db_files to 1024 :-) I mean if you want so many why not 1000 :-)

Because Oracle has chosen 1024 instead of 1000, so why should one bother changing that parameter? The default setting for that init.ora parameter by Oracle's Database Configuration Assistant (at least for some predefined configurations and at least on some releases) is 1024.

[julian]

Because Oracle has chosen 1024 instead of 1000, so why should one bother changing that parameter?

For one to decrease the PGA. You seldom have even more than 100 files associated with a given instances of the DB, not to mention 1024 :-)

[jmodic]

I was talking about the subject: "Why 1024, why not 1000 ?" .

And you will save an enormous amount of memory by lowering that parameter from 1024 to let's say 100, yes?

What I'm trying to say is: I bet that on the databases where you saw db_files = 1024 there are far more other parameters that will realy make a difference if you change them to more reasonable settings.