Solved

SGA limited to 2GB

Posted on 2004-08-09
20
4,038 Views
Last Modified: 2008-01-09
Hi everybody,

I'm configuring a production server with Oracle 8.1.7. My server is a Dell equiped with 8gb and 4 Cpu's Xeon running @ 800 Mhz. The operating system is Windows 2000 Advance Server(Updated). My real problem is that after configuring the server with the /PAE flag at the boot.ini, I'm still getting low performance on my Oracle database and my SGA is limited to 2GB no matter what configuration I set at the ora.ini file. I think I'm doing something wrong.

My ora.ini file looks like and my database is approx 20gb in size:
*********************************************************************
background_dump_dest = E:\oracle\admin\DCSI\bdump
compatible = 8.1.0
control_files = "E:\oracle\oradata\DCSI\control02.ctl"
control_files = "E:\oracle\oradata\DCSI\control01.ctl"
control_files = "E:\oracle\oradata\DCSI\control03.ctl"
db_block_buffers = 100000
db_block_size = 8192
db_file_multiblock_read_count = 32
db_files = 1024
db_name = DCSI
distributed_transactions = 500
global_names = FALSE
instance_name = DCSI
instance_number = 1
java_pool_size = 20971520
job_queue_interval = 10
job_queue_processes = 5
large_pool_size = 614400
log_archive_dest = E:\oracle\oradata\DCSI\archive
log_archive_format = "%%ORACLE_SID%%T%TS%S.ARC"
log_archive_start = TRUE
log_buffer = 2457600
log_checkpoint_interval = 100000
log_checkpoint_timeout = 3200
max_dump_file_size = 10240
max_enabled_roles = 30
mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
open_cursors = 1500
open_links = 4
oracle_trace_collection_name = ""
os_authent_prefix = ""
parallel_max_servers = 16
processes = 400
remote_login_passwordfile = EXCLUSIVE
service_names = DCSI
shared_pool_size = 80000000
sort_area_retained_size = 65536
sort_area_size = 65536
use_indirect_data_buffers = TRUE
user_dump_dest = E:\oracle\admin\DCSI\udump
utl_file_dir = *

Right now I'm using both /3GB and /PAE looking for answers, but the response was the same, just 2GB for SGA and when monitoring the processors performance they get up to 100% utilization.

Does anybody has any idea what could be wrong in my configuration?
0
Comment
Question by:scirocco1
  • 11
  • 4
  • 3
  • +1
20 Comments
 
LVL 1

Expert Comment

by:Ajay_Gupta
ID: 11756953
Hi

Please run this command to check
imagecfg oracle.exe

check registery parameter - AWE_WINDOW_MEMORY  ? what is the value for this ..... It should be by default 1GB .

It should work fine.
0
 

Author Comment

by:scirocco1
ID: 11759163
Hi Ajay!
I do not have this entry in my registry. I was trying to look for information on how this entry needs to be configured, like for example how am I supposed to do the entry. It is a string, binary, WORD etc...? I supposed that if this entry is not explicitly exposed in the registry, the default anyway is 1GB.
Right now my SGA memory is showing 1.5 GB. I'm concerned about this because my computer is reading the whole 8 GB of RAM (/PAE) and paging size is 12 GB distributed in three logical drives. Every time I tried to increase the SGA values beyond 1 GB in the buffers field and share pool, the total SGA shows a value of negative memory and the database will not startup. Any ideas or suggestions? Is it possible to configure AWE_WINDOW_MEMORY with for example 5 GB value? How?
0
 
LVL 47

Expert Comment

by:schwertner
ID: 11760023
It is not so easy as you can see below.

The answer is to take advantage of Physical Address Extensions (PAE), or  
    Address Windowing Extensions (AWE)(These two terms are used interchangeably,  
    so the rest of this document will refer to this simply as AWE).  
    AWE support is available if you are running on a machine with more than 4GB    
    of physical RAM which is running any of the below Windows operating systems:
 
    * Windows 2000 Datacenter Server
    * Windows 2000 Advanced Server
    * Windows 2003 Data Center Edition (32-Bit)
    * Windows 2003 Enterprise Edition (32-Bit)  
 
    On the above operating systems, AWE support is built into the OS.  No
    special drivers are needed to take advantage of the additional memory.
 
  AWE CANNOT be used on the following Operating Systems:
 
    * Windows 2000 Server (Standard)
    * Windows 2000 Professional
    * Windows XP Home Edition
    * Windows XP Professional
    * Windows 2003 Standard Edition
    * Windows 2003 Web Edition
 
   NOTE Also that on 64-Bit Windows operating systems, there is no need for AWE
   implementation support, because the directly addressable memory for a single
   process on 64-Bit Windows is 8 Terabytes.
 
__________________________________________________________________
 
Oracle versions that can use AWE:
=================================
 
    Oracle can take advantage of AWE in the following RDBMS releases:
 
    * Oracle 8.1.6.x
    * Oracle 8.1.7.x
    * Oracle 9.2.x
    * Oracle 10.1.x
 
   Oracle does NOT implement AWE support in release 9.0.1.x
 
 
   AWE support is available on both the Enterprise Edition of Oracle and
   the Standard Edition of Oracle.  However, on Standard Edition of 9.2.0.1,  
   you may receive the following error if trying to start the database with
   USE_INDIRECT_DATA_BUFFERS=TRUE:
 
   ORA-439 - FEATURE NOT ENABLED: VERY LARGE MEMORY
 
   In Standard Edition 9.2.0.2 and 9.2.0.3, you will not receive the above errors,  
   but VLM functionality is still not enabled.  Refer to BUG#2945011 for more detail.
   This BUG is fixed in 9.2.0.3 Patch 2, and will be fixed in 9.2.0.4 as well.
 
__________________________________________________________________
 
Enabling support at the OS level:
==================================
 
    AWE can be enabled at the OS by adding the /PAE switch to the boot.ini  
    as such:
 
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Advanced Server" /PAE
 
    It IS possible to have BOTH the /PAE and /3GB switch in place on the same
    machine, as such:
 
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Advanced Server" /3GB /PAE
 
    However, be aware that if BOTH switches are in place, the server will only
    be able to recognize up to 16GB of RAM.  If you are working with a server  
    with more than 16GB of RAM, you will need to choose between the two.
 
    It is important to note that once either or both of these switches are in
    place in the boot.ini, ALL processes running can take advantage of these
    switches.  Thus, in a case where multiple Oracle instances are running on
    the same server, ALL instances can take advantage of the additional memory                
    afforded by these switches, up to the amount of physical memory on the box.
 
 
Operating System Privileges Needed at the OS Level:
====================================================
 
   In order to take advantage of the additional memory afforded through PAE,
   the operating system user account which is used to start the OracleService<SID>
   must be granted the 'Lock Pages in Memory' system privilege at the operating system
   level.   By default, the OracleService<SID> starts as the LocalSystem account.
   The LocalSystem account has the privilege to Lock Pages in Memory granted to  
   it by default.
 
   However, if you change the OracleService<SID> to logon as a user OTHER than
   LocalSystem, you may see the following errors when attempting to start the
   database with USE_INDIRECT_DATA_BUFFERS set to TRUE :
 
 
   SQL> startup pfile=c:\temp\initscott.ora
   ORA-27102: out of memory
   OSD-00010: Message 10 not found;  product=RDBMS; facility=SOSD
   
   O/S-Error: (OS 1300) Not all privileges referenced are assigned to the caller.
 
 
   To rectify this, you must grant the 'Lock pages in memory' privilege to the user
   that the OracleService<SID> starts as.  To do this, click on:
   Start ->  Programs -> Administrative Tools -> Local Security Policy
   (on a Domain Controller, click on 'Domain Security Policy' instead of 'Local Security Policy')
   Double-click on the 'Lock Pages in memory' policy.
   Add the appropriate user and click 'Ok'.
   Restart the OracleService<SID>
 
 
__________________________________________________________________
 
Understanding the Oracle implementation of AWE support:
=======================================================
 
    What the PAE switch allows you to do from the Oracle perspective is to  
    increase the amount of memory that can be used for the Oracle Database  
    Block Buffer Cache.  It is important to note that this additional memory  
    can ONLY be used by Oracle in the form of an increased value for  
    DB_BLOCK_BUFFERS.  
 
    There is still confusion on the old style of VLM versus AWE on Windows 2000.  
    With VLM on Windows NT 4.0, there was the concept of pointers pointing to  
    the extended memory area, but that is no longer the case on Windows 2000.
    Instead, the windowing technology as described in these articles is being  
    used.  For more information on AWE/PAE implementation on the Windows  
    platform, refer to Microsoft's website.
 
    As mentioned previously, with AWE enabled, this allows the process(es)  
    (in this case ORACLE.EXE) to use memory above and beyond the 4GB  
    mark defined by a 32-Bit Process Address space.  The physical location of  
    these blocks does not matter.  However, the database blocks must still be  
    accessed from within a ?window?, which exists (logically) in that regular  
    3GB process address space.  
    The size of this window is defined by a registry setting in the HOME key for  
    Oracle (HKLM\Software\Oracle\Homex) called AWE_WINDOW_MEMORY.  By default,  
    this value is 1GB, so if this value is not set in the registry,  
    AWE_WINDOW_MEMORY will be 1GB.  
 
    It is important to realize that any database blocks accessed by Oracle  
    (or any user/background thread within Oracle.exe) must first be mapped into  
    the 'window' defined by AWE_WINDOW_MEMORY.  In this scenario, it does not
    matter where the blocks are physically located - there is no need to be  
    concerned with where the blocks are physically residing.  The window will be  
    drawn around the block (i.e. the block will be mapped) wherever it is located  
    in memory.  If the block is in memory but has not been mapped into the  
    ?window?, then it may be necessary to unmapped another block that IS in the  
    window, in order to accommodate the new block.  While this mapping and  
    unmapping of blocks does add some cost, it is still faster than incurring  
    an I/O operation to read the block from disk.  This will be discussed  
    further down in the section on troubleshooting.
     
    Note:    
 
    Keep in mind that if there are multiple instances on a machine with  
    the /PAE switch enabled, ALL instances can take advantage of the additional  
    memory.  However, AWE_WINDOW_MEMORY cannot be set on a per-instance basis,
    so all databases that are running out of the HOMEx key where  
    AWE_WINDOW_MEMORY is set will inherit the same value.
 
 
__________________________________________________________________
 
Enabling AWE Support at the Database/Instance Level:
====================================================
 
    To enable the AWE implementation on Oracle, you must set the following  
    parameter in the init file (or spfile) used to start the instance:
 
      USE_INDIRECT_DATA_BUFFERS=TRUE
 
    Note again that the buffer cache MUST be defined using the parameter  
    DB_BLOCK_BUFFERS, no matter what version of the RDBMS you are running.  
    The 9.2 feature allowing for Multiple block sizes in a database will be  
    disabled if you set USE_INDIRECT_DATA_BUFFERS=TRUE, and you cannot specify  
    the DB_CACHE_SIZE parameter to define the size of the buffer cache.
     
 
    On 9.2, if you attempt to startup a database with this combination of  
    parameters:
 
      USE_INDIRECT_DATA_BUFFERS=TRUE
      DB_CACHE_SIZE=xxxxx (Any number)
 
    The startup will fail with the following error:
 
 
      SQL> startup
      ORA-00385: cannot enable Very Large Memory with new buffer cache  
      parameters
 
    You must change DB_CACHE_SIZE to use DB_BLOCK_BUFFERS instead, as was the  
    syntax under Oracle8i and earlier.
 
 
 
__________________________________________________________________
 
AWE_WINDOW_MEMORY Within the 3GB Process Address Space:
=======================================================
 
    If you are using /PAE and the /3GB switch together, the address space for  
    ORACLE.EXE will be 3GB.  The value for AWE_WINDOW_MEMORY must come from the  
    normal address space used by the ORACLE.EXE process.  Memory that comes  
    from that 3GB address space addressable by the oracle.exe process includes
    the following:
 
 
     ?The Value for AWE_WINDOW_MEMORY
     ?The rest of the SGA (shared_pool, large_pool, java_pool, log_buffers, etc)
     ?Overhead for Oracle.exe and DLL?s (65-100M depends on version & options)
     ?Stack space for all threads (Defaults to 1MB/thread, unless orastack  
         is used)
     ?PGA and UGA memory for all user sessions
 
    Therefore, the value for AWE_WINDOW_MEMORY should be tuned such that mapping
    and unmapping operations are avoided as much as possible, while still  
    allowing enough memory within the 3GB address space for the rest of the  
    process memory that MUST fit within the 3GB (i.e. overhead, remaining SGA
    components and all user connection memory (stack + uga + pga) noted above).
 
    The total size of the buffer cache can then be set to the amount of  
    physical memory remaining above the 4GB barrier, plus AWE_WINDOW_MEMORY.
    On a machine with 12GB of RAM, using the default value of 1GB for  
    AWE_WINDOW_MEMORY, your total buffer cache could theoretically be as high  
    as 9GB:
 
     (Total RAM - 4GB + AWE_WINDOW_MEMORY) = 12GB - 4GB + 1GB = 9GB
 
    In reality, your maximum buffer cache size will be somewhat less than  
    this, allowing for some overhead and additional processes running on the  
    system.  
 
    Attempting to startup the database with a buffer cache larger than the  
    maximum value as calculated above may result in the following errors:
 
      ORA-27102 out of memory  
      OSD-00034 Message 34 not found;  Product=RDBMS;facility =SOSD  
      O/S Error: (OS 8) Not enough storage is available to process this command
 
    (Note - If you are on Release 9.2, another possible cause for these errors  
    is  noted further down, in the troubleshooting section)
 
    As mentioned above, the buffer cache must be specified using  
    DB_BLOCK_BUFFERS rather than DB_CACHE_SIZE, so assuming an 8K block  
    size (8192), to get a 9GB buffer cache, you would set the following init  
    parameters:
 
      DB_BLOCK_BUFFERS = 1179648
      DB_BLOCK_SIZE = 8192
 
 
__________________________________________________________________
 
Troubleshooting AWE_WINDOW_MEMORY implementation:
 
=========================
=========================
 
Minimum Value Required for AWE_WINDOW_MEMORY in 9.2:
====================================================
 
    Here are key points to understand when using AWE_WINDOW_MEMORY:
 
     1.  Under Oracle 8.1.7 we do NOT enforce a minimum value for  
         AWE_WINDOW_MEMORY to be able to start the database.
     2.  This was changed under Oracle9i Release 2, such that we DO  
         enforce a minimum value for AWE_WINDOW_MEMORY. This change was  
         done to help improve performance by enforcing a larger window size.
     3.  You can alter the minimum required value for AWE_WINDOW_MEMORY  
         under 9.2 by changing/setting the value of the parameter  
         _DB_BLOCK_LRU_LATCHES.  Under 8.1.7, this parameter was named  
         DB_BLOCK_LRU_LATCHES.  However, under 9.x, this parameter was  
         changed to be a hidden parameter.
 
    The minimum value for AWE_WINDOW_MEMORY on 9.2 is calculated as such:
 
    MIN(AWE_WINDOW_MEMORY)=(4096 * DB_BLOCK_SIZE * _DB_BLOCK_LRU_LATCHES)/8
 
    Under 9.2, to calculate the value for _DB_BLOCK_LRU_LATCHES, we need  
    this formula:
 
    _DB_BLOCK_LRU_LATCHES = (Max buffer pools * SETS_PER_POOL)  
 
    Max Buffer Pools is a constant = 8
    SETS_PER_POOL is variable, and depends on whether or not VLM is enabled.
 
    SETS_PER_POOL = 2* CPU_COUNT   (if VLM is enabled)
    SETS_PER_POOL= CPU Count /2  (If VLM is NOT enabled)
 
    /* Recall that VLM is enabled by setting USE_INDIRECT_DATA_BUFFERS=TRUE
 
    So, as you can see, the value for _DB_BLOCK_LRU_LATCHES in 9.2 is  
    dependent on the number of CPU's in the box, and therefore  
    MIN(AWE_WINDOW_MEMORY) is dependent on the # of CPU's as well as the  
    DB_BLOCK_SIZE.  The larger the Block Size, and the more CPU's in a system,
    the higher the value for MIN(AWE_WINDOW_MEMORY). Here are a couple of  
    example configurations and caclulations showing MIN(AWE_WINDOW_MEMORY).

0
 

Author Comment

by:scirocco1
ID: 11761572
Hi schwertner,
I've read this article so many times that I lost the count. This is article 225349.1. After reading it I calcualted some configuration like :
db_block_buffers=655360 (before it was 100000)
hash_area_size=4b*2
sort_area_size=4mb (before it was 64k)
log_buffer=4mb (before it was 2457600 bytes)
share_pool_reserve_size=16777216 (when it was 40000000 bytes)
shared_poolsize=33554432 (before 800000000 bytes) // I think the last person that configured this value commit an error and added an additional 0 to the value.

Look I have done everything that is in the article, with these mentioned settings I got less performance and lowered the size of the SGA. I think I'm doing something wrong with the shared pool and buffer cache values. But it has to be done from the ora.inin file, just don't know how the combination has to be done.
0
 

Author Comment

by:scirocco1
ID: 11761584
Hi Ajay_Gupta,
I was trying to run the command imagecfg oracle.exe and it is not valid to the OS.
0
 

Author Comment

by:scirocco1
ID: 11761686
Ajay_Gupta,
I ran the command succesfully now and definately the address space for oracle.exe indicates 2gb.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 11763449
I've run Oracle on Windows for years, sometimes with more than 2GB of RAM in the server, but I've never had Advanced Server so I've never had an SGA larger than 2GB.  One thing I've done to make use of the some of the extra RAM is to create a RAM disk and put the temporary tablespaces in the RAM disk.  That can greatly speed up large sorts, etc.  I use a product called RamDisk2003 for our WindowsServer2003 from: www.superspeed.com
0
 
LVL 47

Accepted Solution

by:
schwertner earned 250 total points
ID: 11763869
Big SGA is not the best choice.
There are many warnings about using big SGA.
For example if you use big db_buffer_cache you will run into delay - there are many latches.
If you experience dificulties in using Oracle you should figure out what is the cause.
The easy way is to run some scripts periodically.
The complex way is to use BSTAT and ESTAT packages and STATPACK.

Also the performance is not only function of the big SGA.
The first thing from which you have to begin is to collect statistics:

CREATE OR REPLACE PROCEDURE compute_statistics IS
BEGIN
   dbms_stats.gather_database_stats(cascade=>true);
END compute_statistics;

execute compute_statistics;

set serveroutput on
set linesize 10000
variable x number;
begin
   DBMS_OUTPUT.enable(100000);  
   dbms_job.submit(:x,'compute_statistics;',trunc(sysdate),'trunc(sysdate+7)');
   commit;
   dbms_output.put_line(TO_char(:x));
end;


Some other  considerations:

1. Avoiding I/O disk Contention  
-------------------------------
 
The fastest way that Oracle can read or modify a block is when the  
information is already loaded in memory in the buffer cache. When  
this do not occur, Oracle will need to read the information directly  
from the datafile incurring I/O operations, which usually are much  
more expensive.
 
When data is retrieved from the physical disk you must takes steps  
to reduce disk contention. Disk contention occurs when multiple  
processes try to access the same disk simultaneously. Disk contention  
can be reduced, thereby increasing performance, by distributing the  
disk I/O more evenly over the available disks. Decreasing disk I/O  
can also reduce disk contention.  

Tuning I/O and physical structure involves:  

    - Distributing data so that I/O is distributed to avoid disk contention.  
   - Storing data in data blocks for best access: setting an adequate number  
     of free lists and using proper values for PCTFREE and PCTUSED.  
   - Creating extents large enough for your data to avoid dynamic extension  
     of tables. This adversely affects the performance of high-volume  
     OLTP applications.  

This article discuss how to distribute effectively the I/O to take advantage of
the I/O system.  

 
2. Monitoring disk I/O Contention
---------------------------------
 
Using the FILE I/O Monitor in the Enterprise Manager is one way to monitor
disk contention and identify disk imbalances. It will help to determine the  
I/O taking place on each database file. If the reads and writes are not
distributed evenly between disks, the tablespaces may need to be restructured
for better performance.
One can also determine file I/O problems by running the following query:
 
col PHYRDS   format  999,999,999
col PHYWRTS  formay  999,999,999
ttitle "Disk Balancing Report"
col READTIM  format  999,999,999
col WRITETIM format  999,999,999
col name format a40
spool fiol.out
 
select    name, phyrds, phywrts, readtim, writetim
from      v$filestat a, v$datafile b
where     a.file# = b.file#
order by  readtim desc
/
spool off
 
A large difference in the number of physical writes and reads among disks
will show which disk is being burdened the most. Allowing data to span
across multiple disks will help balance disk contention when accessing a  
table or a index. This script should be executed any time a configuration  
modification is made, on a periodic basis to ensure the disk balancing  
that is expected is truly happening on the system, when there is large  
amount of data growth (interactive or batch loading), or when the tables  
or indexes are added to the system.
 
3. Avoiding Disk Contention by Distributing Datafiles
-----------------------------------------------------
 
- Separating Datafiles and Redo Log Files  

   Redolog files maintain a high writing rate since every single commited  
  transaction requires an redolog entry record to support recovery processes.
   When data is inserted or modified, both datafiles and redologfiles will  
  require a write operation.
   Keeping the redo log files on separate disks from datafiles will avoid  
  impacting other write operations.
 
  The best recommendation when the server has sufficient resources is to  
  place each set of redo log files on a separate disk with no other activity.  
  Redo log files are written by the Log Writer process (LGWR) when a transaction
   is committed. Information in a redo log file is written sequentially.  
  This sequential writing can take place much faster if there is no concurrent activity on the same disk.
 

- Separating Tables and Indexes  

  Tables that are joined ( simultaneously accessed during a query) often should
  have their data and index separated. The following example shows a table join
  and one possible solution for managing the data:
 
 Select COL1, COL2 ....
  from CUST_HEADER, CUST_DETAIL
  where ...;
 
 Data management solution:
 
 Disk1: CUST_HEADER Table
  Disk2: CUST_HEADER Index
  Disk3: CUST_DETAIL Table
  Disk4: CUST_DETAIL Index  

  The preceding solution allows the table join to be done while accessing
  four different disks and controllers. Separate data and index files onto  
 different physical disks and controllers; consequently, when tables and
  indexes are accessed at the same time, they will not be accessing the  
 same physical disks.  
 
4. Avoiding Disk Contention by Using Partitions
-----------------------------------------------
 
Partitioning is a way to increase efficiency by accessing smaller pieces
of a table or index instead of accessing the full table or index. This can
be particularly useful when one or more users are accessing multiple parts
of the same table. If these partitions (pieces) of the table reside on  
different disks, the throughput is greatly increased. Partitions can also
be backed up and recovered independently of each other, eliminating potential
disk I/O issues during backup times. Only when partitions are properly  
implemented are the best performance-enhancing features of Oracle realized.
Remember, the tablespace is the logical holder of information where the
data file is the physical disk. You can have one tablespace that contains
multiple data files, but a data file can only relate to a single tablespace.
The key to partitioning to improve disk I/O is to ensure that the partitions
that will be accessed simultaneously are located on different physical
disks.
Partitioning indexes and using the parallel option along with partitions
make this option even more powerful.
 
5. Striping Disks
-----------------
 
Striping divides a large table's data into small portions and stores these  
portions in separate datafiles on separate disks. This permits multiple  
processes to access different portions of the table concurrently without  
disk contention. Striping is particularly helpful in optimizing random  
access to tables with many rows. Striping can either be done manually  
(described below), or through operating system striping utilities.  

Although this can be done manually when creating the tables, you can  
achieve the same result using a RAID 0 implementation.


The simple scripts I mentioned are as follow:

Rem Scripts to be run periodically
Rem as user sys from SQL*Plus
Set linesize 10000
Set pagesize 500
set trimspool on
Set heading on
Set echo on
set underline '-'

Rem DB_CACHE_SIZE

Rem   Script 1
select round(((1-(sum(decode(name,
'physical reads', value,0))/
(sum(decode(name, 'db block gets', value,0))+
(sum(decode(name, 'consistent gets', value, 0))))))*100),2)  
|| '%' "Buffer Cache Hit Ratio"
from v$sysstat;

Rem Buffer Cache Hit Ratio > 90.00%

Rem SHARED_POOL_SIZE

Rem   Script 2

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"  
from v$rowcache;

rem should be value above 90%

Rem The unused part of the SHARED_POOL_SIZE

Rem   Script 3
         select name, bytes/1024/1024 "Size in MB"
         from v$sgastat
         where name='free memory';

Rem ROLLBACK_SEGMENTS

Rem   Script 4
      Select a. name, b.extents, b.rssize, b.xacts, b.waits,  
        b. gets, optsize, status
      From v$rollname a, v$rollstat b
      Where a.usn = b.usn;

Rem column "xacts" (which are active transactions) are continually above 1 for the RBS's, you will probably need to
Rem increase the number of rollback segments to avoid the possibility of contention.
Rem If the waits are greater than zero, and this is normal processing, there may also be a need to increase
Rem the number of rollback segments.

Rem From Performance Tuning - Now You are the V8 Expert
Rem Richard J. Niemiec, TUSC

rem Finding the values of ‘KEY’ INIT.ORA parameters
Rem   Script 5

show sga;
select      name, value
from      v$parameter
where       name in ('db_cache_size', 'db_block_size', 'shared_pool_size', 'sort_area_size',
                 'large_pool_size', 'java_pool_size', 'log_buffer', 'dbwr_io_slaves',
                 'rollback_segments', 'sort_area_retained_size',
                 'shared_pool_reserved_size');


 

Rem Determine dictionary cache miss ratio:

Rem   Script 6

select       sum(gets) "Gets", sum(getmisses) "Misses",
             (1 - (sum(getmisses) / (sum(gets) +           
      sum(getmisses))))*100  "HitRate"
from        v$rowcache;

Rem should be > 90%





Rem Determine library cache hit ratio
Rem   Script 7

select       sum(pins) Executions, sum(pinhits) "Execution Hits",
             ((sum(pinhits) / sum(pins)) * 100) phitrat,
             sum(reloads) Misses,
             ((sum(pins) / (sum(pins) + sum(reloads))) * 100)  hitrat
from      v$librarycache;
Rem  If the hit ratio or reloads is high, increase the shared_pool_size INIT.ora parameter.
Rem  Reloads indicate that statements that were once in memory now had to be reloaded because they were pushed
Rem out, whereas misses include statements that are loaded for the first time.






Rem How much memory is left for SHARED_POOL_SIZE:

Rem   Script 8

col value for 999,999,999,999 heading 'Shared Pool Size'
col bytes for 999,999,999,999 heading 'Free Bytes'
select         to_number(v$parameter.value) value, v$sgastat.bytes,
(v$sgastat.bytes/v$parameter.value)*100 "Percent Free"
from       v$sgastat, v$parameter
where      v$sgastat.name = 'free memory'
and      v$parameter.name = 'shared_pool_size';




rem A better query

Rem   Script 9

select       sum(ksmchsiz) Bytes, ksmchcls Status
from       x$ksmsp
group by ksmchcls;








Rem  To see how fast the SGA gets using x$bh

Rem   Script 10
select       state, count(*)
from       x$bh
group by state;

Rem  STATE        COUNT(*)                                                                                
Rem  ---------       -----------------
 Rem       0             371                                                                                
 Rem      1             429                                                                                

Rem  In the above result:
Rem Total DB_BLOCK_BUFFERS = 800
Rem Total that have been used = 429
Rem Total that have NOT been used = 371

Rem A better query

Rem   Script 11

select       decode(state,0, 'FREE', 1, decode(lrba_seq,0,'AVAILABLE','BEING USED'),
         3, 'BEING USED', state) "BLOCK STATUS", count(*)
from       x$bh
group by       decode(state,0,'FREE',1,decode(lrba_seq,0,
         'AVAILABLE','BEING USED'),3, 'BEING USED', state);


Rem BLOCK STATUS            COUNT(*)
Rem AVAILABLE                  779
Rem BEING USED              154
Rem FREE               167




Rem Finding the largest amount of physical reads by query

Rem   Script 12

select       disk_reads, sql_text
from         v$sqlarea
where        disk_reads > 10000
order       by disk_reads desc;



Rem Finding the largest amount of logical reads by query

Rem   Script 13

select       buffer_gets, sql_text
from         v$sqlarea
where        buffer_gets > 200000
order by buffer_gets desc;

Rem   Script 14

select * from v$version;

Rem   Script 15

select * from v$option;



Rem   Script 16

Rem List the logswitches of the 4 last days

select sequence#,to_char(first_time,'DD/MM/YYYY HH24:MI') TIME,FIRST_CHANGE#,SWITCH_CHANGE# from v$loghist where FIRST_TIME > sysdate-4;


I understand that Performance Tunning is a very complex problem and there are many other considerations.

0
 

Author Comment

by:scirocco1
ID: 11765943
Hi schwertner,
The article could help me a lot to study the performance of my database, but right now I'm focus in why Oracle is not taking at least 3gb of RAM space when everything is configured to do so. I want to know exactly how could I increase my SGA size to at least 3gb.

My application is a critical one. I work for a power company and we are requested to read our customer power utilization in a daily basis. That means that we need to invoke every single client power meter every day. If we have near 1000000 customers, maybe 25% is the amount of queries that could be running per batch. Right now one batch could take maybe 8 hours, but in the middle of a batch, another batch begins to work. There is when my database and CPU's went down.
My only doubt is, why if my server is registering 8gb and everything in my database is configured to use more RAM space, I can not get more memory space for my oracle.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 11770070
Yes, I see.
The main reason is Windows, origimnally made for IA32. I know you use IA64, but what is your OS and Oracle.
Are they 64 bit versions or they are 32 bit versions?
Nevertheles you underestimate my recommendations I will kindly insist on running the statistics.
If it is not run the indexes are not used!!! Strange? Unexpected? But true! This is the FIRST step in performance tunning.

Also I think you have an OS problem, not Oracle problem.
If I were you I will try to post a message in the Windows tread and also read Windows docs.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 11771514
http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx


Windows?XP Professional and Windows Server?2003 Memory Support. The maximum amount of memory that can be supported on Windows?XP Professional and Windows Server?2003 is also 4 GB. However, Windows Server?2003, Enterprise Edition supports 32 GB of physical RAM and Windows Server?2003, Datacenter Edition supports 64 GB of physical RAM using the PAE feature.
The virtual address space of processes and applications is still limited to 2 GB unless the /3GB switch is used in the Boot.ini file. When the physical RAM in the system exceeds 16 GB and the /3GB switch is used, the operating system will ignore the additional RAM until the /3GB switch is removed. This is because of the increased size of the kernel required to support more Page Table Entries. The assumption is made that the administrator would rather not lose the /3GB functionality silently and automatically; therefore, this requires the administrator to explicitly change this setting.
The /3GB switch allocates 3 GB of virtual address space to an application that uses IMAGE_FILE_LARGE_ADDRESS_AWARE in the process header. This switch allows applications to address 1 GB of additional virtual address space above 2 GB.
The virtual address space of processes and applications is still limited to 2 GB, unless the /3GB switch is used in the Boot.ini file. The following example shows how to add the /3GB parameter in the Boot.ini file to enable application memory tuning:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="????" /3GB
0
 

Author Comment

by:scirocco1
ID: 11771615
Hi schwertner,
Sorry if it looks that I underestimate your recommendations. My operating system is Windows 2000 Advance Server SP4 with all MS updates. I have an article where it says that this OS version will support more than 4GB of memory and therefore will give Oralce sufficient space amount. I already tried the /3GB and the /PAE flags. At first I tried the /PAE alone, now I'm using both to see if at least I can get more RAM to Oralce.exe. The OS recognized the whole 8gb of RAM but Oracle is not using more than 2gb. In both configurations Oralce only gets 2GB and when my application starts the CPU performance gets a 100% utilization and my mem usage just take a peak value of 2gb. What I'm looking for is just the right combination of parameters to get at least 3gb of SGA. It seems to me that there is no way that I could get that.

Maybe I'm missing a parameter to specify to Windows to assign more GB's to oracle. I'm really out of focus because I have everything set to enable the VLM support.
0
 

Author Comment

by:scirocco1
ID: 11771621

How do I use the IMAGE_FILE_LARGE_ADDRESS_AWARE?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 11771992
Another possibility: you may be able to run a second instance of Oracle on the server connected to the same database, and run reports or queries through this second instance.  Either way, with Oracle on Windows, I don't think that you can get an SGA larger than 3GB and obviously everything has to be exactly right to get the SGA above 2GB.  A second instance may be a way to use some of the RAM efficiently.
0
 

Author Comment

by:scirocco1
ID: 11772391
markgeer,
One thing I'm trying to do is to get at least 3GB but it is impossible with the parameters I have. for Example:
My Boot.ini already have the /PAE and /3GB and my operating system has recognized my 8gb. I enabled the VLM support with USE_INDIRECT_DATA_BUFFERS=TRUE but it seems that maybe a parameter is limiting my SGA to 2gb.
0
 

Author Comment

by:scirocco1
ID: 11772974
Let me make the question more specific. What configuration will you guys run for Oracle 8.1.7 for a machine with 4 processors (800mhz Xeon) and 8gb RAM with a paging size of 12gb? ... the machine is only used for database purposes and I want to assign more than 2gb to Oralce.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 11773150
I've never had the luxury of that much RAM or that many processors in a server running Windows.  I would try two instances (especially if the application can direct some large queries to one instance, and all inserts, updates and deletes to the other).  I would also use a RAM disk for the temporary tablespace(s).  Last choice: switch to Linux or Unix since they can support more RAM and a larger SGA than Windows can.
0
 

Author Comment

by:scirocco1
ID: 11773477
Right now I'm waiting an IBM server with AIX but the process can take several weeks to get the purchase order. So I need to maximize my Windows server by this week.
0
 

Author Comment

by:scirocco1
ID: 12110975
Thank you all!!!!!!!!!!!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now