Home » RDBMS Server » Performance Tuning » can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s
can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s [message #118700] Sat, 07 May 2005 02:16 Go to next message
reyazan
Messages: 53
Registered: February 2005
Member
can i use 2 GB as SGA(4gb ram) oracle 8i windows 2000s
Re: can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s [message #118746 is a reply to message #118700] Sat, 07 May 2005 14:58 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Yes. In general SGA size can be 60-65% of total RAM available.
Re: can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s [message #119081 is a reply to message #118746] Tue, 10 May 2005 09:28 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi,

If you dont specify any thing while creating database (i.e creating typical Database) then nealry 65-70% RAM is used for SGA.

from :- sunil
icon5.gif  Re: can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s [message #121784 is a reply to message #118746] Wed, 01 June 2005 00:37 Go to previous messageGo to next message
aviananta
Messages: 1
Registered: May 2005
Junior Member
To take advantages of 4GB RAM on my machine, Do I need to set /3GB at boot.ini? It seems 8i takes only 2 Gb of available RAM and ignoring the rest.
Re: can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s [message #121797 is a reply to message #121784] Wed, 01 June 2005 01:50 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Not sure.

Do try to create an instance with all its components combined size of >3GB and then check for SGA size.
Also do monitor the pagefile size as well during this whole exercise. Becaues if what u r saying is true, it may be due to the fact that windows doesn't support pagefile size of >2gb.
Re: can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s [message #121805 is a reply to message #121797] Wed, 01 June 2005 02:43 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi

Check init.ora file you can open it in text mode in notepad which contain information like :

--SGA size.
--Database name and instance default.
--Defines physical attribute such as BLOCK_SIZE.
--Name & location of control file, archived log file, alert file and trace file location.
--Log minor
--Dictionary file
--Performance Tunning related parameters

--Contents of this file are not case sensitive, but oracle checks for spelling errors

===================================================
SGA is made up of 5 part
---Suppose if we consider SGA of 1 GB than
1. Shared pool is 48% of SGA
2. Database buffer is 48% of SGA.
3. Log Buffer is 4 % of SGA.
4. Java pool 20 MB
5. Large pool 20 MB.

So you have to change parameter value in init.ora related to part of SGA in file and then save it and again start the database so that database can take new setting of SGA.

Note You dont have to change any thing in Boot.ini
Remeber When oracle start it search for init.ora file and according to the parameter value specified in it, it sets the sizes of SGA. so only by adding RAM to the system will not increase the performance ... you have to change the size of parameter.

----------------------------------------------------------------
eg:- paramete value that you have to change will be as below
java_pool_size=33554432
shared_pool_size=33554432
--->Please find out other paramenter releated to log buffer database buffer and large pool and change its value as per your requirement.

Note :-[1] JAVA POOL and Large Pool make it 20 MB.
[2] DB_BLOCK_SIZE = ___ bytes
once database is created you cant change the size of DB_BLOCK_SIZE parameter, if you change it database gets corrupted.
__________________________________________________________
eg.
###########################################
# SGA Memory (in oracle 10g)
###########################################
sga_target=167772160


###########################################
# Sort, Hash Joins, Bitmap Indexes (in Oracle 10g)
###########################################
pga_aggregate_target=16777216
___________________________________________________________

----------------------------------------------------------------

I hope this much explanation is more than enough and now i think you will be able to change the size of SGA

Regard
Sunilkumar Vishwanath Mishra

Re: can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s [message #121984 is a reply to message #118700] Thu, 02 June 2005 04:41 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

A Sample Initialization File
Listed here is a sample of an Oracle supplied initialization parameter file that has been edited as the parameter file that can be used with the RBDB1 database. You will note that, within the script, Oracle has provided guidance for the settings of the initialization parameters.

##############################################################################
# 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 = RBDB1

db_files = 1024 # INITIAL
# db_files = 80 # SMALL
# db_files = 400 # MEDIUM
# db_files = 1500 # LARGE

control_files = ("/u01/oracle/rbdb1/control01.ctl",
"/u01/oracle/rbdb1/control02.ctl")

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

db_block_buffers = 8192 # INITIAL
# db_block_buffers = 100 # SMALL
# db_block_buffers = 550 # MEDIUM
# db_block_buffers = 3200 # LARGE

shared_pool_size = 15728640 # INITIAL
# shared_pool_size = 3500000 # SMALL
# shared_pool_size = 5000000 # MEDIUM
# shared_pool_size = 9000000 # LARGE

log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800

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

parallel_max_servers = 5 # SMALL
# parallel_max_servers = 4 x (number of CPUs) # MEDIUM
# parallel_max_servers = 4 x (number of CPUs) # LARGE

log_buffer = 32768 # INITIAL
# log_buffer = 32768 # SMALL
# log_buffer = 32768 # MEDIUM
# log_buffer = 163840 # 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 5M each

# Uncommenting the line below will cause automatic archiving if archiving has
# been enabled using ALTER DATABASE ARCHIVELOG.
# log_archive_start = true
# log_archive_dest_1 = "location=/u01/oracle/rbdb1/archive"
# log_archive_format = "%%RBDB1%%T%TS%S.ARC"

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

# 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/5 = 8
# transactions = 40
# transactions_per_rollback_segment = 5

# 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 = us.acme.com
#global database name is db_name.db_domain
compatible = 8.1.0

Re: can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s [message #122023 is a reply to message #118700] Thu, 02 June 2005 07:55 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

The SGA is also called the shared global area. It is used to store database information that is shared by database processes. It contains data and control information for the Oracle server and is allocated in the virtual memory of the computer where Oracle resides.
The following statement can be used to view SGA memory allocations:
SHOW SGA;
Total System Global Area 2735708800 bytes
Fixed Size 735872 bytes
Variable Size 1090519040 bytes
Database Buffers 1543503872 bytes
Redo Buffers 100950016 bytes

Dynamic SGA
A dynamic SGA implements an infrastructure that allows the SGA configuration to change without shutting down the instance. This then allows the sizes of the database buffer cache, shared pool, and large pool to be changed without shutting down the instance. Conceivably, the database buffer cache, shared pool, and large pool could be initially under configured and would grow and shrink depending upon their respective work loads, up to a maximum of SGA_MAX_SIZE.

Sizing the SGA
The size of the SGA is determined by several initialization parameters. The parameters that most affect SGA size are:
DB_CACHE_SIZE: The size of the cache of standard blocks.

LOG_BUFFER: The number of bytes allocated for the redo log buffer cache.

SHARED_POOL_SIZE: The size in bytes of the area devoted to shared SQL and PL/SQL.

LARGE_POOL_SIZE: The size of the large pool; the default is zero.

Unit of Allocation
A granule is a unit of contiguous virtual memory allocation. The size of a granule depends on the estimated total SGA size whose calculation is based on the value of the parameter SGA_MAX_SIZE.
4 MB if estimated SGA size is < 128 MB, 16 MB otherwise

The components (buffer cache, shared pool, and large pool) are allowed to grow and shrink based on granule boundaries. For each component which owns granules, the number of granules allocated to the component, any pending operations against the component (e.g., allocation of granules via ALTER SYSTEM, freeing of granules via ALTER SYSTEM, corresponding self-tuning), and target size in granules will be tracked and displayed by the V$BUFFER_POOL view. At instance startup, the Oracle server allocates granule entries, one for each granule to support SGA_MAX_SIZE bytes of address space. As startup continues, each component acquires as many granules as it requires. The minimum SGA configuration is three granules (one granule for fixed SGA (includes redo buffers; one granule for buffer cache; one granule for shared pool).
Re: can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s [message #122027 is a reply to message #118700] Thu, 02 June 2005 08:03 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

Check init.ora parameter definations

http://download-uk.oracle.com/docs/cd/A81042_01/DOC/index.htm
Re: can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s [message #122072 is a reply to message #122027] Thu, 02 June 2005 13:15 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
The SGA is also called the shared global area. It is used to store database information that is shared by database processes. It contains data and control information for the Oracle server and is allocated in the virtual memory of the computer where Oracle resides.

SGA is System Global area & not shared global area.
icon14.gif  Re: can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s [message #122290 is a reply to message #122072] Sun, 05 June 2005 01:15 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

girish.rohini wrote on Thu, 02 June 2005 21:15

The SGA is also called the shared global area. It is ......

SGA is System Global area & not shared global area.


My Dear Friend, notice that The SGA is also called the shared global area, I didn't said that its ONLY called shared global area Smile By the way this line was taken from ORACLE UNIVERSITY course material for oracle 9i DBA, First chapter of DBA Fundamentals I, Oracle Architectural Components slide # 12..

If you would like to see it for ya self i could send you a copy of slide 12 in pdf format Smile

Cheers,
NK
Re: can use 2 GB as SGA(4gb ram) oracle 8i windows 2000s [message #122302 is a reply to message #122290] Sun, 05 June 2005 09:00 Go to previous message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
Ok sir... checked the same and thanks for the clarification.
Previous Topic: Reg. out put of DBMS_XPLAN.DISPLAY
Next Topic: Composite bitmap indexes
Goto Forum:
  


Current Time: Tue Mar 19 00:41:34 CDT 2024