Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2006
    Posts
    3

    Unanswered: how to change oracle parameters

    hi
    i have increased RAM from 512MB to 1 GB in a windost NT machine which has oracle 9.0.1 configured.

    changed the Ram for better performance, but could not find any change.

    changed the sga and pga parameters

    shared pool from 44 to 100
    buffer area from 32 to 100
    long data pool from 1Mto 16MB
    java pool on 32MB and no change
    max to 400MB

    concurrent users from 700 to 50
    sort area size from 512KB to 2MB

    but after appling, on restart of machine or instance parameters reset to original value.

    why can anyone help me.

    iam very new to oracle

    radha

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not a DBA, but - when I had to do such things in Oracle 7 - it was done by editing INIT.ORA file.

    Now you are talking about "applying changes", but after restart nothing happened. Things have obviously changed since Oracle 7 (there was nothing to "apply" back then), but I guess it wouldn't cost much to check the INIT.ORA and see what is written in it.

    If parameters settings are different from the ones you'd want to have, try to edit them manually, save changes and restart the database. Maybe it will help.

    P.S. Don't forget to create a backup copy first!

  3. #3
    Join Date
    Jan 2006
    Posts
    3

    oracle parameters oracle 9i

    thankyou and I used oem tool to edit the configuration.
    I shall try out editting directly the init.ora and let you know the result

    radha

    Quote Originally Posted by Littlefoot
    I'm not a DBA, but - when I had to do such things in Oracle 7 - it was done by editing INIT.ORA file.

    Now you are talking about "applying changes", but after restart nothing happened. Things have obviously changed since Oracle 7 (there was nothing to "apply" back then), but I guess it wouldn't cost much to check the INIT.ORA and see what is written in it.

    If parameters settings are different from the ones you'd want to have, try to edit them manually, save changes and restart the database. Maybe it will help.

    P.S. Don't forget to create a backup copy first!
    Last edited by radkrishnan; 01-16-06 at 08:12. Reason: spelling

  4. #4
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    On Oracle 9i there're two ways to storage initialization parameters, not only the PFILE (INIT.ORA) but also SPFILE.
    First, find out which alternative you're using running this query as dba:
    Code:
    select decode(count(*), 1, 'spfile', 'pfile' )
      from v$spparameter
     where rownum=1
       and isspecified='TRUE'
    Here you'll find an interesting article:
    http://asktom.oracle.com/pls/ask/f?p...:6644781942843

  5. #5
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    If you are using the PFILE then you can change the settings in the INIT.ORA file, & the changes will take effect at next start-up. Further to this, if the parameter is dynamically configurable (through the 'alter session' or 'alter system' commands) the change will take effect immediately, but only for that instance; as soon as you stop the instance running the changes will be lost.

    If you are using the SPFILE, then any dynamic parameter changes take effect immediately AND are also stored in the SPFILE, so the changes will stay permanent. Thus, if you shut down the database & then restart it, the changes will not have been lost, unlike with the PFILE.

    HTH
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  6. #6
    Join Date
    Jan 2006
    Posts
    3

    using spfile

    Thank you so much
    i ran the query and found that i am using spfile

    Now Can you tell me what should i change for improving the performance on increasing RAM from 512MB to 1GB on an WindowsNT (used as database server only )machine running oracle 9i.

    radha

    Quote Originally Posted by cis_groupie
    If you are using the PFILE then you can change the settings in the INIT.ORA file, & the changes will take effect at next start-up. Further to this, if the parameter is dynamically configurable (through the 'alter session' or 'alter system' commands) the change will take effect immediately, but only for that instance; as soon as you stop the instance running the changes will be lost.

    If you are using the SPFILE, then any dynamic parameter changes take effect immediately AND are also stored in the SPFILE, so the changes will stay permanent. Thus, if you shut down the database & then restart it, the changes will not have been lost, unlike with the PFILE.

    HTH
    Last edited by radkrishnan; 01-17-06 at 00:42. Reason: refining the question

  7. #7
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Using OEM, in the left hand pane select your database, then Instance, then Configuration - in the right hand pane select the Memory tab, & you should then see your current settings, with advice tabs to show you the results you can expect with different values. If you increase the Shared Pool & Buffer Cache size then make sure that you also adjust the SGA Max Size to cater for the new figures.

    However, just increasing values won't necessarily increase performance - you really need to read up on Tuning (or even better, get on a course), as a tweak in one area may very well have an impact in another area.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    cis_groupie is correct. A change in one parameter may have adverse effects in another area. You performance issues could be in the SQL statements themselves and not necessarily the database parameters. The one area you should look at is the buffer cache values. There are 3 different buffer caches available for you

    default
    recycle
    keep

    You appear to only be using the default. You might enable and run STATSPACK to get a high level feel for where you waits are and what area
    you need to be attacking

    HTH
    Gregg

Posting Permissions

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