Printable View
1) What tables/columns can I look at to find out how much memory each user connection is using and also to sum them.
2) Is memory used for client connections taken from the shared_pool?
3) What does boosting the FIXED_SGA affect?
4) In the following query does "free memory" refer to what's available in the shared pool???
select NAME "PARAMETER",
to_char(BYTES, '999,999,999,999') "VALUE"
from v$sgastat
where name = 'free memory';
For checking the memory you could use the views that I had mentioned in the following thread. http://ora.dbasupport.com/forums/sho...?threadid=4324
Sam
well the reason I am asking these questions is... several weeks ago a few users were getting oracle errors stating that thier client could not access somenumber of bytes to create an oracle connection( it was an ora-###) . I looked it up and it said to increase the shared pool size( which was set at 18000000) , after monitoring the free memory for about a week( with the query "select NAME "PARAMETER",
to_char(BYTES, '999,999,999,999') "VALUE"
from v$sgastat
where name = 'free memory';") I found that free memory was usualy between 3 MB and as low as 100K I increased it to shared_pool_size = 72000000. But a couple clients have gotten the error again and as I check free memory again I have found that that it is varying from 19mb free to less then 1 mb free. I would have thought that increasing the HARED POOL_SIZE by such a great amount would have kept me from seeing this error again for a loooong time. From looking at the other post I am wondering if I need to also increase my db_block_buffers which is currently set at 6400 .
Also the app that is getting the error is written in Access, and we also have several VB apps and a 2 C++ apps.
Thanks TONS,
SM
check the code that runs in the SGA. It looks loke the query had been poorly written. Other things are that you could pin the freq. used objects in the sga and reduce the reloads. Are the tables they are using is analyzed, if not analyze then and check for chained rows and etc. Run an audit on the perticular schema and check the audit trail for more details.
Good luck,
sam
What do you think about increasing my db_block_buffers which is currently set at 6400 to 12800. What is the ratio of shared_pool_size to db_block_buffers, or are they not related?
There is no relationship between db_block_buffers and shared_pool other than they both live in the SGA.
db_block_Buffers controls the amount of memory you will use for data caching. For example, an update statement. In order to evaluate whether you need to increase your db_block_buffers, you should examine your hit ratio:
select (1-(sum(decode(name,'physical reads',value,0)) /
(sum(decode(name,'db block gets',value,0))+
sum(decode(name,'consistent gets',value,0))))) * 100 "buff hit"
from v$sysstat
The hit ratio should be above 98%.
shared_pool controls the amount of memory you will use for other operations; library cache, object cache, etc.
You could also try with
SQL>alter system flush shared_pool;
It will reclaim some memory back for you.
thanks for your help, how about these questions?
1) Is memory used for client connections taken from the shared_pool?
2) In the following query does "free memory" refer to what's available in the shared pool???
select NAME "PARAMETER",
to_char(BYTES, '999,999,999,999') "VALUE"
from v$sgastat
where name = 'free memory';
Thanks tons
SM
The answer to your question NO2 is Yes. Here the 'free memory' refers to shared_pool.
I have similar problem with my database too. I have to do alter system flush shared_pool almost everyday. Alternatives are to increase shared_pool, but it constrains by the server memory. Since we don't have enough memory on server, I have to flush memory.Of course, you have to tune your sql statements make it as optimal and efficient as possible.
What is the Oracle error you are getting?
Could you please post your initialization file and
how much memory your machine has?
Also run something like:
select table_name, num_rows, blocks
from dba_tables
where owner not in ('SYS','SYSTEM')
You might want to consider using recycle and keep
pools as well. Is this an OLTP database or a DSS
system?
What is your db_block_size? The total size of your
SGA is db_block_size * db_block_buffers. What is
your current hit rate for your caches?
You may want to run a bstat/estat during times of high
activity for a couple of hours and examine the results.
There are also some Oracle parameters you can specifiy
that will give you some extended statistics to determine
the best size for db_block_buffers (DB_BLOCK_LRU_STATISTICS and DB_BLOCK_LRU_EXTENDED_STATISTICS). Make sure that you have the parameter TIMED_STATISTICS = TRUE.
Joe
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
In Oracle 7.2 KEEP and RECYCLE options are not available.
I thought you use 8/8i.