This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Dec 28, 2012 12:36 PM by marksmithusa RSS

oracle performance

933651 Newbie
Currently Being Moderated
Hello!
What do you think, are these parameters good for a 64gb ram linux server with oracle 11g?


'
*.db_block_size=8192
*.db_cache_size=1024M
*.db_files=128
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.java_pool_size=536870912
*.job_queue_processes=10
*.large_pool_size=16777216
*.nls_length_semantics='CHAR'
*.open_cursors=1300
*.optimizer_features_enable='10.2.0.4'
*.pga_aggregate_target=1409286144
*.processes=255
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=286
*.shared_pool_size=5G
*.sort_area_size=1048576
*.Standby_File_Management='AUTO'
*.timed_statistics=TRUE
*.transactions=315
*.undo_management='AUTO'
*.undo_retention=10800
  • 1. Re: oracle performance
    Rob_J Journeyer
    Currently Being Moderated
    Hi,

    I would use sga_target and sga_max_size instead and bump up the memory. Is this DB the only thing running on the server? A lot of the others are dependent upon your system - see notes:

    *.db_block_size=8192 - standard size, good for OLTP not for Data Warehouse (usually)
    *.db_cache_size=1024M - replace with sga_target. Keep if you want a minimum size for the db_cache.
    *.db_files=128 - file number dependent
    *.fast_start_mttr_target=300 - depends on how quickly you want to recover from an instance crash. lower number = more disk I/O & longer to recover
    *.hash_area_size=1048576 - remove in favour of pga_aggregate_target
    *.java_pool_size=536870912 - remove unless specific reason for min value
    *.job_queue_processes=10 - depends how many jobs you want running from a scheduler?
    *.large_pool_size=16777216 - remove unless specific reason for min value
    *.nls_length_semantics='CHAR'
    *.open_cursors=1300 - application specific
    *.optimizer_features_enable='10.2.0.4' - depends if you have tested your App with 11.2.0.3 or wnat any of the features from the 11g optimizer
    *.pga_aggregate_target=1409286144 - increase if more sorting required or using shared server
    *.processes=255 - application specific
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sessions=286 - I don't think this can be higher than the number of processes? Should be the other way around because of Oracle background processes
    *.shared_pool_size=5G - why do you have a 5G shared pool and only a 1G db cache?
    *.sort_area_size=1048576 - remove and let pga deal with it. Might even be ignored. Look in docs.
    *.Standby_File_Management='AUTO' - do you use data guard?
    *.timed_statistics=TRUE
    *.transactions=315
    *.undo_management='AUTO'
    *.undo_retention=10800

    Rob
  • 2. Re: oracle performance
    hitgon Expert
    Currently Being Moderated
    Recommended to use Automatic Shared Memory Management
    http://hiteshgondalia.wordpress.com/2012/12/04/oracle10g-memory-tuning-case-study/
  • 3. Re: oracle performance
    933651 Newbie
    Currently Being Moderated
    Thanks for your reply,
    This is the only db running on the server
    I use data guard

    Edited by: 930648 on Dec 21, 2012 5:40 AM
  • 4. Re: oracle performance
    Rob_J Journeyer
    Currently Being Moderated
    If it's the only DB I would increase memory if you need to. Depends on how large your database is and if performance is a problem?
  • 5. Re: oracle performance
    933651 Newbie
    Currently Being Moderated
    I am a newbie in oracle.
    Can you suggest me how to increase values?
  • 6. Re: oracle performance
    Rob_J Journeyer
    Currently Being Moderated
    *.db_block_size=8192
    *.db_files=128
    *.fast_start_mttr_target=300
    *.job_queue_processes=10
    *.nls_length_semantics='CHAR'
    *.open_cursors=1300
    *.optimizer_features_enable='10.2.0.4'
    *.pga_aggregate_target=5G
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sessions=286
    *.sga_target=24G
    *.sga_max_size=24G
    *.Standby_File_Management='AUTO'
    *.timed_statistics=TRUE
    *.transactions=315
    *.undo_management='AUTO'
    *.undo_retention=10800

    I've removed a few that I would not set and added the SGA ones in there, and increased the PGA. Like I said in the notes above next to each parameter, a lot of them are system dependent so you need to decide what to set them to. If you DB is only 10GB then having an SGA of 24GB is not necessary. And it depends if there are performance issues with a small cache and it would benefit from increasing the size.

    Be careful when changing them and don't do it straight on a PROD DB without carefulling considering the implications and having a way to back out the changes. Take a copy of your SPFILE or INIT.ora file before you make any changes. Some of them will require a database restart.

    You should have a look at creating an [url http://www.ora00600.com/articles/oracle-awr.html]AWR report for the DB during peak load hours and see what the top wait events are.
  • 7. Re: oracle performance
    sb92075 Guru
    Currently Being Moderated
    Ready, Fire, AIM!

    proposed changes are based strictly on wishful thinking & have little basis to reality.
  • 8. Re: oracle performance
    Rob_J Journeyer
    Currently Being Moderated
    As already stated:

    "Like I said in the notes above next to each parameter, a lot of them are system dependent so you need to decide what to set them to"

    There is no other way to set them when there is no information to base the decisions on.
  • 9. Re: oracle performance
    sb92075 Guru
    Currently Being Moderated
    Rob_J wrote:
    There is no other way to set them when there is no information to base the decisions on.
    then why change anything?
  • 10. Re: oracle performance
    Rob_J Journeyer
    Currently Being Moderated
    I also said to look if performance is a problem and it would depend on the size of the DB.

    Using AMM is a definite change which should be made as all those manually configured pools are not ideal and give no flexibility to react to a change in workload.

    If there is no information to base the decision on, start with the recommended approach and what experience has shown you is a good configuration on other systems and work from there. It's an iterative process, and I agree that if it ain't broke don't fix it but some of those parameters - the ones I said to change - should be changed. I added the caveat that there is no point increasing the memory if it's not needed.
  • 11. Re: oracle performance
    Aman.... Oracle ACE
    Currently Being Moderated
    hitgon wrote:
    Recommended to use Automatic Shared Memory Management
    http://hiteshgondalia.wordpress.com/2012/12/04/oracle10g-memory-tuning-case-study/
    Why ?

    I hope you do know that in 11g, the version of OP, there is a better alternative AMM (Automatic Memory Management) .

    Aman....
  • 12. Re: oracle performance
    Aman.... Oracle ACE
    Currently Being Moderated
    930648 wrote:
    Hello!
    What do you think, are these parameters good for a 64gb ram linux server with oracle 11g?


    '
    *.db_block_size=8192
    *.db_cache_size=1024M
    *.db_files=128
    *.fast_start_mttr_target=300
    *.hash_area_size=1048576
    *.java_pool_size=536870912
    *.job_queue_processes=10
    *.large_pool_size=16777216
    *.nls_length_semantics='CHAR'
    *.open_cursors=1300
    *.optimizer_features_enable='10.2.0.4'
    *.pga_aggregate_target=1409286144
    *.processes=255
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sessions=286
    *.shared_pool_size=5G
    *.sort_area_size=1048576
    *.Standby_File_Management='AUTO'
    *.timed_statistics=TRUE
    *.transactions=315
    *.undo_management='AUTO'
    *.undo_retention=10800
    I would say that rather than looking at the parameters or looking for parameters to tune the database, it's better if you check the AWR or Statspack report after hearing a cry from the users of yours and then try to find an issue and it's solution. As stated already in the thread, rather than going for the parameters for Buffer Cache, Shared Pool, it's better to switch over to AMM which is a better alternative in 11g compared to ASMM of 10g.

    HTH
    Aman....
  • 13. Re: oracle performance
    marksmithusa Journeyer
    Currently Being Moderated
    Actually, if your memory footprint is large enough, Oracle are pushing HugePages as a 'better' alternative to AMM.

    I prefer being able to let Oracle decide what it allocates to SGA and PGA, personally, but I've been assured that HugePages are the way forward for your SGA.
  • 14. Re: oracle performance
    Rob_J Journeyer
    Currently Being Moderated
    I could be wrong but I thought that huge pages was something for a Linux server? Just like large pages is for Windows. It's nothing to do with the SGA and PGA, just how the OS assigns memory segments of RAM. The OS doesn't know if you are using SGA, PGA or any other memory configuration it just allocates the memory that is requested from the Oracle instance. The idea behind it being that if you are allocating hundreds of GBs of memory then you want large chunks of RAM contiguously rather than thousands of small pieces.

    Here is a little more about using [url http://www.ora00600.com/scripts/databaseconfig/large_pages.html]large pages on Windows
1 2 Previous Next

Legend

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