DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 11

Hybrid View

  1. #1
    Join Date
    Dec 2001

    Shared server memory usage

    Hi all,

    We have Oracle intalled on a Redhat linux box. The server has 4GB of physical memory. Oracle has been configured to shared servers (wasnt configured by me, i would used dedicated servers). We have 25 shared servers. Now when I do a top i see:

    195 processes: 190 sleeping, 3 running, 2 zombie, 0 stopped
    CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
               total    4.1%    0.0%   65.2%   0.0%     0.7%   29.0%    0.7%
               cpu00    3.7%    0.0%   64.6%   0.0%     1.5%   28.5%    1.5%
               cpu01    4.5%    0.0%   65.9%   0.0%     0.0%   29.5%    0.0%
    Mem:  3921888k av, 3899804k used,   22084k free,       0k shrd,    7960k buff
                       2963208k actv,  566312k in_d,   55244k in_cK
    Swap: 2044072k av,    5168k used, 2038904k free                 3408592k cached
    29453 oracle    16   0 1110M 1.1G 1107M R    28.6 28.9  12:38   0 oracle
        8 root      16   0     0    0     0 SW   28.2  0.0 218:07   0 kscand
        7 root      16   0     0    0     0 SW    3.0  0.0 212:15   1 kswapd
    29455 oracle    15   0 1041M 1.0G 1030M R     2.2 27.0   4:19   0 oracle
    29449 oracle    15   0 1136M 1.1G 1124M D     1.8 29.5  33:03   0 oracle
    25029 root      20   0  1244 1244   880 R     1.5  0.0   0:00   0 top
    29451 oracle    15   0 1124M 1.1G 1112M D     1.1 29.2  35:58   1 oracle
    29457 oracle    15   0 1022M 1.0G 1019M D     0.7 26.6   4:06   0 oracle
    As can be seen, most of the memory is been used by oracle processes, these were traced to shared processes (some using 1.1G!). Besides changing to dedicated mode, what areas need to looked in order to reduce this overhead?
    SGA_TARGET is set to 1.4GB, and PGA_AGGREGATE=486MB.

    Any advice will be much appreciated,

    Thanks in advance,
    Last edited by davey23uk; 05-02-2007 at 12:01 PM.

  2. #2
    Join Date
    Nov 2005
    The process size that an utility like top shows is not the actual size of heap (data). It is a combination of (a) SGA size + (b) oracle executable being used by the process + (c) the actual heap (data). On sun boxes, you can verify the actual size of the heap portion of the process through the use of pmap command. For example :

    On a solaris10 box, here's the output from top:

    9177 ora9i 1 1 0 1597M 1569M sleep 45:04 0.03% oracle

    As you can see, the process size is listed by top at almost 1.6GB.

    Doing pmap :

    $ pmap -x 9177

    9177: ora_pmon_QADB
    Address Kbytes RSS Anon Locked Mode Mapped File
    00010000 49744 48896 - - r-x-- oracle <<- Executable
    030B2000 496 344 56 - rwx-- oracle <<- Executable
    0312E000 16 8 8 - rwx-- oracle <<- Executable
    03132000 1512 632 632 - rwx-- [ heap ] <<- Private Data
    20000000 1568768 1568768 - 1568768 rwxsR [ ism shmid=0x55 ] <<- SGA
    FE720000 240 216 - - r-x-- libresolv.so.2
    FE76C000 16 16 - - rwx-- libresolv.so.2
    FE780000 2392 2096 - - r-x-- libvas.so.4.2.0

    From PMAP, the actual private heap / data size is only 1.4 MB.

    Translating the SGA address from the pmap output (ism shmid=0x55 ): 0x55 translates to 85

    $ ipcs -m

    Shared Memory:
    m 85 0xdc126148 --rw-r----- ora9i software

    ipcs -a should tell you as to how much oracle's allocated for SGA.

    As you can see, the process size of top is not a good indication of the heap portion of the process.

    You are better off relying on the UGA and PGA statistics from v$sesstat to see if you are using excessive sql work area memory.

    Until 9i the automatic tuning of work areas was disabled when you use shared servers since most of the work area allocations that are part of the run time memory were handled within SGA for shared servers. Starting 10G work area allocations that are part of the run time memory is handled within PGA and so large sort operation or hash joins or windowing operation on resultsets can potentially cause your private heap size to grow.

    Good luck.......


  3. #3
    Join Date
    Dec 2001

    Thank you very much for the very informative feedback. I can understand that because of the changes in 10g, large sorts etc can take place within the PGA however can point me in the direction of what exactly to look for when i check v$sessat?

    I also noticed that there are 25 shared servers allocated and all of these start up with the instance, is there a gain in reducing the number to shared servers? Along with this there is only one dispatcher allocated. Surely this needs to increase if shared servers were to remain at 25?

    Am new to shared servers so any advice will be greatly apprecaited.


  4. #4
    Join Date
    Nov 2005
    UGA and PGA checks:

    (1) You can do this to check UGA and PGA allocations for a given session:

    select a.name,b.value,b.sid,username
    from v$statname a,v$sesstat b,v$session
    where a.statistic#=b.statistic# and b.sid = v$session.sid
    and b.sid=&sidvalue and a.name like '%ga%memory%'


    (2) To just check PGA allocations for a given session:

    select PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM from v$process,v$session where sid = &sidvalue and addr=paddr

    Also to keep in mind is the fact that database sessions that do direct path reads due to Sort IO (when a sort does not fit in memory) or uses parallel Query slaves or performs I/O to LOB segments (which are not cached in the Buffer Cache), add to the PGA's growth which in turn can affect the process size.

    "I also noticed that there are 25 shared servers allocated and all of these start up with the instance, is there a gain in reducing the number to shared servers?"

    I guess it depends on the load / concurrent connections to your database. You could always control initial shared server process startup Vs. the total number through the use of shared_servers and max_shared_servers parameters.

    This is oracle's recommendation for the number of dispatchers:

    "The value of MAX_DISPATCHERS should at least equal the maximum number of concurrent sessions divided by the number of connections for each dispatcher. For most systems, a value of 250 connections for each dispatcher provides good performance. "

    You might also want to take a look at 10g's Automatic Shared Server Configuration feature (metalink note 265931.1).

    Good luck......


  5. #5
    Join Date
    Dec 2001
    Hi there,

    Thank you very much for the feedback. I have gone deeper into the database to investigate this high CPU usage. I ran several AWR reports and that the execute to parse ratio is only 3%. I checked out several queries that were being run (and that are consistently run throughout the day) that most likely sap all the CPU. One such query had a cost of 141913! It was performing 4 full table scans (out of which one table is 37 million rows!). Altough the cost was higher on the hash joins it the query was trying to perform. Any advice on reducing this (generally)?

    I also noticed that stats were not update to on any of the tables (e.g the stats for the table which had 37 mill rows dated back 3 weeks). As a first point of action i have suggest an analyze on the schema.

    It seems like that this ineffiecient SQL code is the reason for this high CPU usage.

    I also have a high number of waits on db file sequential read:

    Total Wait wait Waits
    Event Waits Timeouts Time (s) (ms) /txn
    db file sequential read 3,292,788 8,545 37.10 User I/O

    Is this due to ineff sql as well due to the high amounts of i/o produced by these queries?

    Thanks in advance,

  6. #6
    Join Date
    Nov 2005
    Having a low execute to parse ratio depends on the type of application being run against the database. If you have OLTP type of an applications that parses a statement and re-executes it numerous times within the session then this ratio will tend to be higher. On the other hand if you have a batch processing system that parses, executes and does not re-execute the same statement then this ratio will likely be low. I would concentrate on soft parse % (which will give us an idea of the amount of hard parse) and Non-parse CPU% which could tell you as to how much CPU time was actually spent on parsing.

    High CPU usage might be an indicative of a different problem where in some cases, the process might be actually waiting on the IO (full scan) to complete.

    Something must be causing oracle to favor hash joins. Could be that all of your tables that the optimizer's doing full scans on (incl. the 37 mill rows table) might have parallelism enabled and not have large number of blocks or the outer cardinality of the join might be high.

    Nested Loop Join cost = outer access cost + (inner access cost * outer cardinality)


    Hash join cost = (outer access cost * # of hash partitions) + inner access cost

    You could always enable the 10053 event for the session to see as to why oracle arrived at the plan it did.

    Hope you've enabled monitoring on the tables so that stats are collected by oracle only if a certain percentage of data has changed. Frequent collection of stats can cause plan instability.

    Narrow down on the actual objects where the sequential reads are happening. You could query active session history (ASH) to get the specific details. 99% of the time this will point to tweaking the execution plans with having the optimizer pick the right indexes for the sql statement.

    Good Luck......


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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.