help about oralce parameters
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: help about oralce parameters

  1. #1
    Join Date
    Jan 2003
    Location
    China
    Posts
    3

    help about oralce parameters

    We have an Oracle 8174 database running on COMPAQ GS160.
    It is an OLTP server which has 400--500 sessions .
    It has 50--70 active sessions .
    Our server has 8 CPU and 8GB Mem.
    Tonight we will upgrade our COMPAQ GS160 from 8 CPU 8GB Mem to 16CPU 16 GB Mem.
    But we will change our database parameters later.
    The fellowing is our pfile now (8 CPU and 8GB Mem).
    Would anybody give me some advise of our parameters later (16 CPU and 16GB Mem). Thanks !




    initSID.ora




    #
    # Copyright (c) 1991, 1998 by Oracle Corporation
    #
    ##############################################################################
    # Example INIT.ORA file
    #
    # This file is provided by Oracle Corporation to help you customize
    # your RDBMS installation for your site. Important system parameters
    # are discussed, and example settings given.
    #
    # Some parameter settings are generic to any size installation.
    # For parameters that require different values in different size
    # installations, three scenarios have been provided: SMALL, MEDIUM
    # and LARGE. Any parameter that needs to be tuned according to
    # installation size will have three settings, each one commented
    # according to installation size.
    #
    # Use the following table to approximate the SGA size needed for the
    # three scenarious provided in this file:
    #
    # -------Installation/Database Size------
    # SMALL MEDIUM LARGE
    # Block 2K 4500K 6800K 17000K
    # Size 4K 5500K 8800K 21000K
    #
    # To set up a database that multiple instances will be using, place
    # all instance-specific parameters in one file, and then have all
    # of these files point to a master file using the IFILE command.
    # This way, when you change a public
    # parameter, it will automatically change on all instances. This is
    # necessary, since all instances must run with the same value for many
    # parameters. For example, if you choose to use private rollback' segments,
    # these must be specified in different files, but since all gc_*
    # parameters must be the same on all instances, they should be in one file.
    #
    # INSTRUCTIONS: Edit this file and the other INIT files it calls for
    # your site, either by using the values provided here or by providing
    # your own. Then place an IFILE= line into each instance-specific
    # INIT file that points at this file.
    #
    # NOTE: Parameter values suggested in this file are based on conservative
    # estimates for computer memory availability. You should adjust values upward
    # for modern machines.
    #
    ###############################################################################

    db_name = "ahbill"
    instance_name = ahbill

    service_names = ahbill


    control_files = ("/devices/rvol/jfdbdg/v2_control1", "/devices/rvol/jfdbdg/v2_control2", "/devices/rvol/jfdbdg/v2_control3")

    open_cursors = 1000
    max_enabled_roles = 30

    #DB_BUFFERS=858849280
    db_block_buffers =65000
    db_block_size = 16384

    #shared_pool_size=300M
    shared_pool_size = 614572800

    large_pool_size = 15728640
    java_pool_size = 20971520

    log_checkpoint_interval = 10000
    log_checkpoint_timeout = 1800
    enqueue_resources = 8192
    #processes = 300
    processes = 600 #modified by xienan at 13 Aug,2002

    #log_buffer = 16M
    log_buffer = 16777216

    # audit_trail = false # if you want auditing
    # timed_statistics = false # if you want timed statistics
    # max_dump_file_size = 10000 # limit trace file size to 5M each

    # Uncommenting the lines below will cause automatic archiving if archiving has
    # been enabled using ALTER DATABASE ARCHIVELOG.
    # log_archive_start = true
    # log_archive_dest_1 = "location=/array/orabill/app/oracle/admin/ahbill/arch"
    # log_archive_format = arch_%t_%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 )

    rollback_segments = (RBS00,RBS01,RBS02,RBS03,RBS04,RBS05,RBS09,RBS10,RBS11,RBS12,RBS13,RBS14,RBS15,RBS16,RBS17,RBS18,RBS 19)


    # 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

    # define directories to store trace and alert files
    background_dump_dest = /array/orabill/app/oracle/admin/ahbill/bdump
    core_dump_dest = /array/orabill/app/oracle/admin/ahbill/cdump
    #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 = /array/orabill/app/oracle/admin/ahbill/udump



    remote_login_passwordfile = none

    os_authent_prefix = none

    # The following parameters are needed for the Advanced Replication Option
    #job_queue_processes = 4
    #job_queue_interval = 60
    distributed_transactions = 250
    open_links = 4

    #mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
    # Uncomment the following line when your listener is configured for SSL
    # (listener.ora and sqlnet.ora)
    # mts_dispatchers = "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)"

    #mts_dispatchers = "(protocol=TCP)"

    db_files = 254
    compatible = "8.1.0"
    sort_area_size = 2048000
    sort_area_retained_size = 20480

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Are your controlfiles on raw devices? And are the raw devices in same Volume Group or Disk? if not I would fire the one who setup the database

    Also I can see your company need to contract urgently one experienced Oracle dba

    in the meanwhile read

    http://download-west.oracle.com/docs...a96533/toc.htm

    and go to metalink and read some notes
    Last edited by pando; 01-22-2003 at 02:59 AM.

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    What are the objectives for this hardware upgrade? Based on that only you can make use of extra available processing power and memory.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  4. #4
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    phonee, I think your dba needs some training on oracle.

    As far as settings are concerned, it all depends on your goals and objectives. Since you have spent so much money on the hardware and software, why dont you take an expert DBA's help to setup your database. That will do a lot good for your company's future.
    Agasimani
    OCP(10g/9i/8i/8)

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    You seem to be doing OLTP without being in ARCHIVELOG mode.
    In which case, I think you should forget tuning until that's sorted.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #6
    Join Date
    Jan 2003
    Location
    China
    Posts
    3
    hi ,pando .
    Our controlfiles on raw devices. And the raw devices are in same Volume Group ,but different Disks.

    hi ,SANJAY_G.
    The objectives for this hardware upgrade is just because we get
    these hardare from other servers .

    Hi,DaPi.
    We really do OLTP without being in ARCHIVELOG mode.
    But we have a backup way of EMC SRDF .


    Here is the status of our databse.
    We have two Oracle 8174 databases running on COMPAQ cluster .
    The storage is EMC Symmetrix 8430.
    And the databases are installed on share disks.
    Two GS160: node A 8CPU 11GB Mem;node B 8CPU 8GB Mem.
    Tonight We will upgrade Node B to 16 CPU 16GB Mem.
    Next night We will upgrade Node A to 16 CPU 16GB Mem.
    Node A runs business service.
    Node B runs billing service.

    I made a mistake just now . We upgrade the hardware not because of low performance.The pfile of first article is belong to Node B.
    And the Node B have few active session .(less than 20) .



    1.Node A:
    It is an OLTP server which has 400--500 sessions .
    It has 50--70 active sessions .
    orabus@Ahyz1> vmstat 1 30
    Virtual Memory Statistics: (pagesize = 8192)
    procs memory pages intr cpu
    r w u act free wire fault cow zero react pin pout in sy cs us sy id
    34 697 201 1M 253K 137K 1G 66M 356M 1M 173M 46K 4K 15K 15K 42 19 39
    33 693 203 1M 254K 137K 1412 140 607 0 426 0 8K 20K 18K 71 28 1
    28 695 204 1M 254K 137K 1282 92 438 0 324 0 8K 19K 18K 71 28 2
    43 686 201 1M 254K 137K 1767 114 467 0 353 0 7K 19K 17K 71 29 0
    37 689 203 1M 253K 137K 2474 172 689 0 374 0 8K 19K 18K 70 29 0
    35 695 203 1M 254K 137K 3336 193 918 0 667 0 3K 10K 8K 73 27 0
    39 694 198 1M 254K 137K 1600 91 488 0 341 0 8K 20K 18K 72 27 1
    26 698 208 1M 254K 137K 1921 98 511 0 315 0 6K 20K 17K 72 27 1
    33 691 205 1M 255K 137K 1206 57 302 0 170 0 8K 20K 18K 71 27 2
    26 700 202 1M 256K 137K 2024 127 565 0 411 0 8K 22K 18K 70 29 1
    41 688 201 1M 255K 137K 2636 122 614 0 367 0 11K 23K 18K 69 30 1
    35 691 204 1M 255K 137K 1905 100 722 0 392 0 11K 22K 20K 70 30 1

    the following is the pfile of Node A.



    #
    # Copyright (c) 1991, 2000 by Oracle Corporation
    #
    ##############################################################################
    # Example INIT.ORA file
    #
    # This file is provided by Oracle Corporation to help you customize
    # your RDBMS installation for your site. Important system parameters
    # are discussed, and example settings given.
    #
    # Some parameter settings are generic to any size installation.
    # For parameters that require different values in different size
    # installations, three scenarios have been provided: SMALL, MEDIUM
    # and LARGE. Any parameter that needs to be tuned according to
    # installation size will have three settings, each one commented
    # according to installation size.
    #
    # Use the following table to approximate the SGA size needed for the
    # three scenarious provided in this file:
    #
    # -------Installation/Database Size------
    # SMALL MEDIUM LARGE
    # Block 2K 4500K 6800K 17000K
    # Size 4K 5500K 8800K 21000K
    #
    # To set up a database that multiple instances will be using, place
    # all instance-specific parameters in one file, and then have all
    # of these files point to a master file using the IFILE command.
    # This way, when you change a public
    # parameter, it will automatically change on all instances. This is
    # necessary, since all instances must run with the same value for many
    # parameters. For example, if you choose to use private rollback' segments,
    # these must be specified in different files, but since all gc_*
    # parameters must be the same on all instances, they should be in one file.
    #
    # INSTRUCTIONS: Edit this file and the other INIT files it calls for
    # your site, either by using the values provided here or by providing
    # your own. Then place an IFILE= line into each instance-specific
    # INIT file that points at this file.
    #
    # NOTE: Parameter values suggested in this file are based on conservative
    # estimates for computer memory availability. You should adjust values upward
    # for modern machines.
    #
    ###############################################################################

    db_name = "ahbus"
    instance_name = ahbus

    service_names = ahbus


    control_files = ("/dev/rvol/busvg01/v1_control1", "/dev/rvol/busvg01/v1_control2", "/dev/rvol/busvg01/v1_control3")

    open_cursors = 1500
    max_enabled_roles = 30

    db_block_buffers = 307200

    shared_pool_size = 828375040

    large_pool_size = 15728640
    #java_pool_size = 20971520
    java_pool_size = 1048576

    #Modified by Liang Hao at 20021206
    #log_checkpoint_interval = 20000
    #log_checkpoint_timeout = 18000

    log_checkpoint_interval = 409600
    log_checkpoint_timeout = 1800

    processes = 600
    #log_buffer=10485760

    # audit_trail = false # if you want auditing
    # timed_statistics = false # if you want timed statistics
    # max_dump_file_size = 10000 # limit trace file size to 5M each
    max_dump_file_size = 5M # Modified by Xie Nan to limit trace file size to 5M each

    # Uncommenting the lines below will cause automatic archiving if archiving has
    # been enabled using ALTER DATABASE ARCHIVELOG.
    # log_archive_start = true
    # log_archive_dest_1 = "location=/array/orabus/app/oracle/admin/ahbus/arch"
    # log_archive_format = arch_%t_%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,R BS18,RBS19,RBS20,RBS21,RBS22,RBS23,RBS24,RBS25,RBS26,RBS27,RBS28,RBS29)

    rollback_segments = (RBS0,RBS1,RBS2,RBS3,RBS4,RBS5,RBS6,RBS7,RBS8,RBS9,RBS10,RBS11,RBS12,RBS13,RBS14,RBS15,RBS16,RBS17,R BS18,RBS19,RBS20,RBS23,RBS24,RBS25,RBS26,RBS27,RBS28,RBS29)

    # 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

    # define directories to store trace and alert files
    background_dump_dest = /array/orabus/app/oracle/admin/ahbus/bdump
    core_dump_dest = /array/orabus/app/oracle/admin/ahbus/cdump
    #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 = /array/orabus/app/oracle/admin/ahbus/udump
    db_block_size = 8192
    remote_login_passwordfile = none

    os_authent_prefix = ""

    # The following parameters are needed for the Advanced Replication Option
    #job_queue_processes = 4
    #job_queue_interval = 60
    distributed_transactions = 200
    open_links = 4

    #mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
    # Uncomment the following line when your listener is configured for SSL
    # (listener.ora and sqlnet.ora)
    # mts_dispatchers = "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)"

    #compatible = "8.1.0"
    sort_area_size = 2097152
    sort_area_retained_size = 2097152

    session_cached_cursors = 400

    parallel_max_servers = 0
    fast_start_parallel_rollback = false

    #db_writer_processes = 4
    #add by fuxg
    db_files = 400
    distributed_transactions = 200
    #db_block_lru_latches = 24
    enqueue_resources = 8192
    #session_cached_cursors = 30

    #added 29 by Oracle OCT
    #cursor_space_for_time=true
    _sqlexec_progression_cost=0
    #CURSOR_SHARING = FORCE
    hash_area_size = 10240000
    resource_limit = true

    #change 20021106 by LuGH NeuSoftTS
    cursor_sharing = exact
    #change 20021107 by LuGH NeuSoftTS
    cursor_space_for_time=FALSE
    db_block_lru_latches=48
    db_writer_processes=8
    log_buffer=1048576
    compatible=8.1.7.4

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally Posted by Phonee
    hi ,SANJAY_G.
    The objectives for this hardware upgrade is just because we get
    these hardare from other servers .
    Lucky, you. Here I have to justify every dollar and cent.

    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  8. #8
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Envy, envy ... Upgrade without a harsh justification... Well, surely, this db config appears to be ** questionable ** at least. Some points :

    a) sort_area_size of just 2 Mb, with SO MUCH RAM available ???? ANy special desire for disk sorts , here ?

    b) hash_area_size around 10 Mb, BUT without hash_multiblock_io_count ? MAYBE this could be correct, but many times, not. And talkink about blocked I/O, how about db_file_multiblock_read_count, sort_multiblock_read_count, this WAS considered / analyzed ??

    c) with SO MUCH ram, how about a keep pool ?? And a bigger large_pool_size , maybe ?

    d) you REALLY want a timeout for logs, sometimes this will NOT be the best approach, maybe log files of BIG sizes, NO timeout and a even bigger log_checkpoint_interval would be better

    e) NO timed_statistics ??? the performance impact of this parameters is REALLY small in recent versions of Oracle dbs, and the info is SO EXTREMELY useful and important what I leave it activated in ALL my dbs

    f) cursor_sharing ??? This is just a BIG and UGLY "crutch" to a ILL-CONFORMED app, and it CAN bring collateral bad effects, SPECIALLY in internal db SQLs, see http://asktom.oracle.com/pls/ask/f?p...:3696883368520 for some examples. IF this is a PROD system, DEMAND corrections from the system producer.


    In resume, BRING URGENTLY a DBA here, many possible improvements are showing.

    Regards,

    Chiappa

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Well if the database is OLTP sort area size doesnt need to be large and there must be few hash joins so hash area size shouldnt be large as well.

    Large pool, I am not sure why is used, MTS used? RMAN used? PQ used? If none is used I dont see the point of setting large pool or even higher value

    db_file_multiblock_read_count is just, TOO LARGE

    JChiappa have you seen cursor_sharing is commented? They are not using it

  10. #10
    Join Date
    Feb 2001
    Posts
    295
    hi ,SANJAY_G.
    The objectives for this hardware upgrade is just because we get
    these hardare from other servers .
    If it ain't broke, don't fix it yet.
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

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