This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Feb 8, 2013 5:41 PM by sb92075 RSS

Recommended  init.ora values for PROD setup

yxes2013 Newbie
Currently Being Moderated
Hi All,
-Assuming that I am using a 64-bit version of Oracle, 
-that there is nothing else running on the server, 
-that there will be no other databases on the server, 
-that I have substantially more data on disk than I have RAM, 
-and that I am using 11g. 
I want to setup a production database 11g. The server specs is OEL 5.6 with 12Gb Ram and a Quadcore CPU.
There will be 200 users simultaneously accessing the database. I understand that 10g and 11g are "self-tuning".
So in this regard do I need to still change values in the default init.ora?

Can you give me sample PROD template of your init.ora?

Or maybe since my RAM is 12Gb I will just set the "target SGA = 8Gb" and the database will now be good?


Thanks,

Yx

Edited by: yxes2013 on 8.2.2013 16:21

Adding Justin's assumptions
  • 1. Re: Recommended  init.ora values for PROD setup
    Justin Cave Oracle ACE
    Currently Being Moderated
    Are you asking solely about how to configure the memory in your database? Or are you asking something more?

    There is no "default init.ora file". If you are using the DBCA GUI to create the database, you'll be asked a few questions that will help it to set some parameters reasonably. I'd be surprised if the DBCA created a database that used a parameter file rather than a spfile in any version of 10g or 11g.

    Justin
  • 2. Re: Recommended  init.ora values for PROD setup
    marksmithusa Journeyer
    Currently Being Moderated
    It depends.
  • 3. Re: Recommended  init.ora values for PROD setup
    RobertGeier Oracle ACE
    Currently Being Moderated
    How much data do you expect in the database from day one ?

    What sort of activity are the users performing ? OLTP, with small insert, update, or OLAP with large table scans and joins ?

    These are not decisions that should be made after purchasing a PROD server. Normally there would have been some performance testing in DEV or UAT to be sure the PROD server was sized correctly.
  • 4. Re: Recommended  init.ora values for PROD setup
    asahide Expert
    Currently Being Moderated
    ex.
    There will be 200 users simultaneously accessing the database.
    12Gb Ram
    processes = 200
    memory_target = 8G (if 11g)
  • 5. Re: Recommended  init.ora values for PROD setup
    RobertGeier Oracle ACE
    Currently Being Moderated
    No, you should not set processes to the expected number of users, as this is likely to lead to failures. To be safe, double the expected number to account for idle sessions, background processes, timeouts etc.

    How can you decide that 8GB is correct without any knowledge of the application or data ? If there is only 1GB of data, should you really waste 8GB of memory ?
  • 6. Re: Recommended  init.ora values for PROD setup
    yxes2013 Newbie
    Currently Being Moderated
    Thanks Just,

    Yes, that is what I have meant, the default init.ora after you chose what type of database you need ( oltp, multipurpose, etc).
    After that, since I have a given12Gb Ram and Quadcore CPU what else do I tweak to maximize these 2 resources?
  • 7. Re: Recommended  init.ora values for PROD setup
    Justin Cave Oracle ACE
    Currently Being Moderated
    But if we're talking about using the DBCA, you're asked about memory configuration separately from the OLTP/ data warehouse/ etc. question. If memory serves, the memory configuration question comes first.

    As others have pointed out, it depends on a variety of factors that you haven't mentioned. If we assume that you're using a 64-bit version of Oracle, that there is nothing else running on the server, that there will be no other databases on the server, that you have substantially more data on disk than you have RAM, and that you're using 11g, I'd probably start with a MEMORY_TARGET of 10 or 11G.

    But as has been pointed out, it seems quite odd that you've already got the hardware and you're trying to figure out the appropriate configuration for the production environment based on guesswork. It would generally make much more sense to have figured this out in the lower environments by testing your application with your data using your workloads and your performance requirements.

    Justin

    Edited by: Justin Cave on Feb 8, 2013 12:09 AM
  • 8. Re: Recommended  init.ora values for PROD setup
    karan Pro
    Currently Being Moderated
    Important parameters in production will be



    MEMORY_TARGET TO AUTO TUNE SGA AND PGA

    best will be to take advice from v$pga_target_Advice, v$sga_target_Advice, v$memory_target_Advice

    This will help you as as well for memory management http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm

    SESSION_CACHED_CURSORS

    Oracle monitors the SQL statements that are submitted for each session. If it detects the same statement has been submitted multiple times, it moves the statement to the cursor cache and keeps the cursor open for subsequent reuse. The SESSION_CACHED_CURSORS parameter controls the number of cursors allowed in the cursor cache.

    The default value for SESSION_CACHED_CURSORS varies by Oracle release. If your instance is not configured to cache at least 50 cursors, increase the value of this parameter to 50.

    OPEN_CURSORS

    The Oracle initialization parameter OPEN_CURSORS specifies the number of cursors a session can have open at any one time. The default value is 300. If the session attempts to open a new cursor but already has the maximum number of cursors open, the Oracle error -1000 will be returned.

    Parameter that affects Oracle statistics

    OPTIMIZER_MODE Keep the default value for the Oracle parameter OPTIMIZER_MODE. For Oracle9i, the default value is choose; for Oracle 10g, it is all_rows.

    Ram management

    PGA_AGGREGATE_TARGET

    sga_target

    ALSO UNDO_RETENTION WILL BE IMPORTANT SEE http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams222.htm

    Edited by: Karan on Feb 8, 2013 10:50 AM
  • 9. Re: Recommended  init.ora values for PROD setup
    yxes2013 Newbie
    Currently Being Moderated
    I thank you all :)
    How can you decide that 8GB is correct without any knowledge of the application or data ? If there is only 1GB of data, should you really waste 8GB of memory ?
    Correct me if I'm wrong ;). I guess its not the amount of data that matters? but the number of users processing against the data. Even if I have only 100Mb of data but I have 1000 users
    running processing the data, or even just viewing it over and over for query purposes. Comparing it to if you have 1Tb of data and only 10 users is accessing it.
    What sort of activity are the users performing ? OLTP, with small insert, update, or OLAP with large table scans and joins ?
    Lets us say I have these activities

    500 users doing oltp
    20 users doing batch & report generation with long ops

    Then what do I set in the init.ora for these inputs?
  • 10. Re: Recommended  init.ora values for PROD setup
    Justin Cave Oracle ACE
    Currently Being Moderated
    The SGA is shared among all users. It generally wouldn't make sense to have an SGA larger than the data in your database. You might want to cache a few versions of a few blocks but that's generally pretty insignificant.

    PGA is memory allocated to individual sessions. How much PGA to allocate depends on the number of sessions and what those sessions are doing. 10 sessions doing data warehouse queries will probably need more PGA than 1000 sessions doing OLTP queries.

    Justin
  • 11. Re: Recommended  init.ora values for PROD setup
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    I guess its not the amount of data that matters? but the number of users processing against the data.
    It is more the amount of data and some factoring for the number (volume) of changes to the data.

    The number of users matters in terms of sizing memory for server processes, and some allocation of the SHARED_POOL_SIZE, not for the DB_CACHE_SIZE component.
    If more users means more distinct SQL statements, the SHARED_POOL_SIZE usage would be greater.

    However, multiple users accessing the same version of the data will be "sharing" one buffer in memory for each block of data being accessed. It doesn't matter if 1 user is reading a block or 8 or 64 or 128 users are reading the same block -- in either case it is one buffer in memory.
    It is only if the block is frequently being changed and concurrently queried, might there be current and a few consistent versions of the buffer being maintained in the SGA.


    Hemant K Chitale
  • 12. Re: Recommended  init.ora values for PROD setup
    RobertGeier Oracle ACE
    Currently Being Moderated
    You need to understand what the memory is used for. Please read the documentation on SGA and PGA components and parameters and how they impact performance.

    The SGA contains the buffer cache, which improves performance by keeping frequently used data blocks in memory. If you don't have much data it can be small.
    The SGA contains the shared pool, which contains historical sql and plans to speed up queries when they run again. If you have a simple application it can be small.
    The PGA is used for sorting in memory. If you don't have much data it can be small.

    The number of users is irrelevant to memory sizing, but what they are doing matters.

    You need to TEST to confirm the correct parameter sizes, not ask random strangers on the internet.
  • 13. Re: Recommended  init.ora values for PROD setup
    yxes2013 Newbie
    Currently Being Moderated
    I Thank you all :)
    You need to understand what the memory is used for. Please read the documentation on SGA and PGA components and parameters and how they impact performance.
    Yeah I read it already....but it does not clearly explain like what all you did here ;)

    Hmm interesting....I got lots of tips learned. So If I have 500Gb data (im sure it does not include index right?) Broken into the following:

    Table1 300Gb
    Table2 100Gb
    Table3 50Gb
    Table4 30Gb
    Table5 10Gb
    Table6 5Gb
    Others 5Gb ( comprised of hundreds of tables)

    Do I need to have my memory target SGA equal to 500Gb to attain a maximum performance? supposing memory is not a budget constraint?

    Supposing Table6 is the most frequentlly read/accessed and I want to load/cache it all in the memory so I do not need to index this table, how can I do it?

    What is the rule of thumb of PGA size against the size of the table? Maybe 20% sorting of the most accessed table?

    @Justin
    **10 sessions doing data warehouse queries will probably need more PGA than 1000 sessions doing OLTP queries.
    **I'd probably start with a MEMORY_TARGET of 10 or 11G. 
    How about the approx. PGA size dear? :)

    @Hemant
    It is more the amount of data and some factoring for the number (volume) of changes to the data.
    
    The number of users matters in terms of sizing memory for server processes, and some allocation of the SHARED_POOL_SIZE, not for the DB_CACHE_SIZE component.
    If more users means more distinct SQL statements, the SHARED_POOL_SIZE usage would be greater.
    
    However, multiple users accessing the same version of the data will be "sharing" one buffer in memory for each block of data being accessed. It doesn't matter if 1 user is reading a block or 8 or 64 or 128 users are reading the same block -- in either case it is one buffer in memory.
    It is only if the block is frequently being changed and concurrently queried, might there be current and a few consistent versions of the buffer being maintained in the SGA.
    I think in 11g I do not need to set those because it is "self-tuning" right? All I need is the target SGA size. :)

    Given that I will perform a "benchmark" test/simulation on the DEV server. Is there a tool that will gather the right sizing of init.ora parameters? Can I extract it from AWR report?



    Thanks to all again,

    Yxz
  • 14. Re: Recommended  init.ora values for PROD setup
    EdStevens Guru
    Currently Being Moderated
    yxes2013 wrote:
    I Thank you all :)
    You need to understand what the memory is used for. Please read the documentation on SGA and PGA components and parameters and how they impact performance.
    Yeah I read it already....but it does not clearly explain like what all you did here ;)

    Hmm interesting....I got lots of tips learned. So If I have 500Gb data (im sure it does not include index right?) Broken into the following:

    Table1 300Gb
    Table2 100Gb
    Table3 50Gb
    Table4 30Gb
    Table5 10Gb
    Table6 5Gb
    Others 5Gb ( comprised of hundreds of tables)

    Do I need to have my memory target SGA equal to 500Gb to attain a maximum performance? supposing memory is not a budget constraint?

    Supposing Table6 is the most frequentlly read/accessed and I want to load/cache it all in the memory so I do not need to index this table, how can I do it?

    What is the rule of thumb of PGA size against the size of the table? Maybe 20% sorting of the most accessed table?

    For Justin
    **10 sessions doing data warehouse queries will probably need more PGA than 1000 sessions doing OLTP queries.
    **I'd probably start with a MEMORY_TARGET of 10 or 11G. 
    How about the approx. PGA size dear? :)





    Thanks again,

    Yxz
    For an 'approximate' size, take the defaults suggested by DBCA. You can always adjust them later in response to real issues.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points