Oracle memory questions.... - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Oracle memory questions....

  1. #11
    Join Date
    Oct 2000
    Posts
    103

    Cool

    Hi Joe,
    Thanks for your response,
    The DB is Oracle 7.2.2.4 running on a Novell server with 1 gig of memory.
    This is an oltp DB but it does have a few dw tables that will be moving to a seperate dw DB at a next year.

    I am not sure what you mean by "You might want to consider using recycle and keep pools as well. "???

    here is the results of :select (sum(reloads)/sum(pins)) * 100 "Number of Library Cache Miss %"
    from v$librarycache;

    Number of Library Cache Miss %
    ----------------------------------------
    .184300816703898311411642425812387627439

    Here are some of the results from your question
    "Also run something like:

    select table_name, num_rows, blocks
    from dba_tables
    where owner not in ('SYS','SYSTEM') "



    ---------------------------------------------------------
    TABLE_NAME NUM_ROWS BLOCKS OWNER
    ------------------------------ ---------- ---------- ------------------------------
    CMS_BUNDLE_ENTRIES 1849650 11754 PRODUCTION
    CMS_ENTRY_JOURNAL 1326779 31664 PRODUCTION
    CMS_BILLING_ENTRIES 1113330 22929 PRODUCTION
    CMS_BUNDLE 757030 24792 PRODUCTION
    CMS_PAYOUTS 439889 9224 PRODUCTION
    CMS_CHANGE 327723 2979 PRODUCTION
    CMS_BUNDLE_CHECK 140536 1252 PRODUCTION
    CMS_STATEMENT 121873 1814 PRODUCTION
    CMS_COMPUTER_ENTRIES 116306 2229 PRODUCTION
    CMS_CLAIMS_ENTRIES 96879 2504 PRODUCTION
    CLM_TRANSACTION 80673 679 PRODUCTION
    TEMP3 65187 174 PRODUCTION
    CMS_CONTRACT_LINE_OF_BUSINESS 43796 434 PRODUCTION
    CMS_PAYMENT 38379 954 PRODUCTION
    CMS_ALLOWANCE 35380 584 PRODUCTION
    CMS_PLACEMENT 34937 339 PRODUCTION
    CMS_CASH_APP_SESSION 34090 272 PRODUCTION






    Below is the init.ora parameters you asked to see.

    # db_file_multiblock_read_count = 8 # SMALL
    db_file_multiblock_read_count = 16 # MEDIUM
    # db_file_multiblock_read_count = 32 # LARGE

    # db_block_buffers = 60 # SMALL
    #db_block_buffers = 2000 # MEDIUM
    #db_block_buffers = 3200 # LARGE
    db_block_buffers = 6400 # 11/19/97 Changed from 3200

    #shared_pool_size = 2000000 # SMALL
    #shared_pool_size = 5000000 # MEDIUM
    #shared_pool_size = 18000000 # LARGE
    #shared_pool_size = 24000000 # REALLY LARGE!
    #shared_pool_size = 32000000 # SUPER-DUPER LARGE!
    shared_pool_size = 72000000 # SUPER-DUPER LARGE!

    db_block_size=8192

    log_checkpoint_interval = 10000

    #processes = 50 # SMALL
    processes = 100 # MEDIUM
    # processes = 200 # LARGE

    enqueue_resources = 400
    open_cursors = 255

    # dml_locks = 100 # SMALL
    #dml_locks = 200 # MEDIUM
    dml_locks = 300
    # dml_locks = 500 # LARGE

    # log_buffer = 8192 # SMALL
    #log_buffer = 32768 # MEDIUM
    #log_buffer = 65536 # 4/8/98 Changed from 32768
    log_buffer = 163840 # 10/03/00 Changed from 65536 # LARGE

    # sequence_cache_entries = 10 # SMALL
    sequence_cache_entries = 30 # MEDIUM
    # sequence_cache_entries = 100 # LARGE

    # sequence_cache_hash_buckets = 10 # SMALL
    sequence_cache_hash_buckets = 23 # MEDIUM
    # sequence_cache_hash_buckets = 89 # LARGE

    # audit_trail = true # if you want auditing
    # timed_statistics = true # if you want timed statistics
    max_dump_file_size = 10240 # limit trace file size to 5 Meg each

    # log_archive_start = true # if you want automatic archiving
    # log_archive_dest = %RDBMS72%\arcs\
    # log_archive_format = "ARC%S.%T"

    # Archive logging set 11/17/98 - MH & JN
    log_archive_start = true # if you want automatic archiving
    log_archive_dest = VOL1:ARCHIVE\ #
    log_archive_format = "ARC%S.%T"

    # If using private rollback segments, place lines of the following
    # form in each of your instance-specific init.ora files:
    # rollback_segments = (name1, name2)

    # If using public rollback segments, define how many
    # rollback segments each instance will pick up, using the formula
    # # of rollback segments = transactions / transactions_per_rollback_segment
    # In this example each instance will grab 40/10 = 4:
    # transactions = 40
    # transactions_per_rollback_segment = 10

    # Global Naming -- enforce that a dblink has same name as the db it connects to
    global_names = TRUE

    # Edit and uncomment the following line to provide the suffix that will be
    # appended to the db_name parameter (separated with a dot) and stored as the
    # global database name when a database is created. If your site uses
    # Internet Domain names for e-mail, then the part of your e-mail address after
    # the '@' is a good candidate for this parameter value.

    db_domain = ioare.com # global database name is db_name.db_domain

    # FOR DEVELOPMENT ONLY, DEFAULT TO SINGLE-PROCESS
    # single_process = TRUE

    # FOR BETA RELEASE ONLY. Enable debugging modes. Note that these can
    # adversely affect performance. On some non-VMS ports the db_block_cache_*
    # debugging modes have a severe effect on performance.

    # _db_block_cache_protect = true # memory protect buffers
    # event = "10210 trace name context forever, level 2" # data block checking
    # event = "10211 trace name context forever, level 2" # index block checking
    # event = "10235 trace name context forever, level 1" # memory heap checking
    # event = "10049 trace name context forever, level 2" # memory protect cursors

    # define directories to store trace and alert files
    background_dump_dest=%RDBMS72%\trace
    user_dump_dest=%RDBMS72%\trace

    control_files=sys:database\control1.ora,vol1:database\control1.ora

    db_name = philly

    # user_dump_dest=sys:ioaredb
    # background_dump_dest=sys:ioaredb

    # 11/19/97 Dropped RBSSYS1. This segment was in the SYSTEM tablespace.
    # rbs_1 = public system rollback segment
    # rollback_segments= (rbssys1, rbs1, rbs2, rbs3, rbs4, rbs5, rbs6, rbs7, rbs8)
    rollback_segments= (rbs1, rbs2, rbs3, rbs4, rbs5, rbs6, rbs7, rbs8)

    #SQL_TRACE=TRUE

    job_queue_processes = 1 # added 10/15/97
    sort_area_size = 1024000 # 11/19/97 added
    optimizer_mode = RULE # 12/17/97 added.
    checkpoint_process = TRUE # 1/9/98 added.
    nls_date_format = "DD-MON-RR" # 1/6/00 JLC to handle Y2K by jaga
    # distributed_transactions = 0 # 4/8/98 added. 10/7 commented out to allow use of db links


    Thanks,
    SM

  2. #12
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    In Oracle 7.2 KEEP and RECYCLE options are not available.
    I thought you use 8/8i.

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