Oracle memory questions....

[supermega]

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

[tamilselvan]

In Oracle 7.2 KEEP and RECYCLE options are not available.
I thought you use 8/8i.