CPU reaches 100% on instance startup. Please Please Help..... Soon
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    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.

  3. #3
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    What is your setup? Does an application server automatically connect with the DB?
    David Knight
    OCP DBA 8i, 9i, 10g

  4. #4
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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

  5. #5
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    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

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938


    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 :-)


  7. #7
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    you need more memory.
    for tunig go through this doc
    http://metalink.oracle.com/metalink/...&p_id=115698.1
    sonofsita
    http://www.ordba.net

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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 :-)




  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width