1 2 Previous Next 18 Replies Latest reply: Feb 8, 2013 7:41 PM by sb92075 RSS

    Recommended  init.ora values for PROD setup

    yxes2013
      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
          JustinCave
          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
          • 3. Re: Recommended  init.ora values for PROD setup
            Robert Geier
            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
              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
                Robert Geier
                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
                  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
                    JustinCave
                    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
                      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
                        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
                          JustinCave
                          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
                            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
                              Robert Geier
                              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
                                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
                                  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