Home » SQL & PL/SQL » SQL & PL/SQL » Not much memory left for Oracle Process (Oracle 9i, 9.2.0.1.0, RHEL 2.1 AS)
Not much memory left for Oracle Process [message #443760] Wed, 17 February 2010 04:34 Go to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends

Here i am posting snap of 'top' command and also a snap of v$session view run on my database server (on which my database server running). We continuously got the call for application users that system is running very slow. We checked the v$session view, and surprisingly i found that not many ACTIVE process going on server. Then i checked my os memory status through 'top' command and i found that only 41164K memory is available out of 4114420K. My main question how do i overcome this situation. Is
there problem with my database or my physical memory or os.

*Snap of 'top' Command
3:22pm  up  8:03,  3 users,  load average: 4.02, 2.47, 2.33
208 processes: 196 sleeping, 12 running, 0 zombie, 0 stopped
CPU0 states: 69.2% user, 29.2% system,  0.0% nice,  0.3% idle
CPU1 states: 85.3% user, 13.2% system,  0.0% nice,  0.2% idle
Mem:  4114420K av, 4073256K used,   41164K free,  715628K shrd,  125972K buff
Swap: 2096220K av,   72648K used, 2023572K free                 2916668K cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
15494 oracle1   15   0  386M 356M  353M S     0.0  8.8   0:01 oracle
15164 oracle1   16   0  350M 319M  319M S     0.0  7.9   0:03 oracle
 1259 oracle1   15   0  195M 161M  161M S     0.0  4.0   0:09 oracle
 1271 oracle1   15   0  155M 124M  123M S     0.0  3.0   0:19 oracle
11638 oracle1   15   0  140M 109M  108M S     0.0  2.7   0:18 oracle
13563 oracle1   15   0  205M 108M  108M S     0.0  2.7   0:49 oracle
 1265 oracle1   16   0  121M  89M 91268 S     0.0  2.2   0:04 oracle
32572 oracle1   20   0 97.9M  67M 66760 R    17.4  1.6   0:00 oracle
32137 oracle1   15   0 94580  62M 61124 S     0.0  1.5   0:01 oracle
27301 oracle1   21   0 91720  58M 58212 S     0.0  1.4   0:04 oracle
28584 oracle1   15   0 78364  45M 44892 S     0.0  1.1   0:00 oracle
17429 oracle1   15   0 66052  35M 32588 S     0.0  0.8   0:07 oracle
15160 oracle1   17   0 66976  33M 33520 S     0.0  0.8   0:00 oracle
32550 oracle1   24   0 65084  31M 31624 R    38.4  0.7   0:01 oracle
32502 oracle1   22   0 64968  31M 31512 R    28.8  0.7   0:01 oracle
32556 oracle1   18   0 62244  28M 28784 R    11.8  0.7   0:00 oracle
15606 oracle1   16   0 54336  24M 20872 S     0.0  0.5   0:00 oracle
14916 oracle1   15   0 54116  22M 20652 S     0.0  0.5   0:00 oracle
32596 oracle1   17   0 51596  18M 18140 R     1.2  0.4   0:00 oracle
24472 oracle1   15   0 49812  18M 16356 S     0.0  0.4   0:00 oracle
15000 oracle1   15   0 49372  17M 15916 S     0.0  0.4   0:00 oracle
23178 oracle1   15   0 48884  17M 15428 S     0.0  0.4   0:00 oracle
32332 oracle1   15   0 49500  16M 16044 S     0.0  0.4   0:00 oracle
 1263 oracle1   15   0 47232  13M 13472 S     0.0  0.3   0:02 oracle
17541 oracle1   15   0 46600  13M 13144 S     0.0  0.3   0:00 oracle
17075 oracle1   15   0 47464  13M 14008 S     0.0  0.3   0:00 oracle
21520 oracle1   15   0 46612  12M 13160 S     0.0  0.3   0:00 oracle
23580 oracle1   15   0 45248  12M 11792 S     0.0  0.3   0:00 oracle
 1269 oracle1   15   0 45564  12M 12308 S     0.0  0.3   0:00 oracle
15162 oracle1   16   0 45080  12M 11624 S     0.0  0.3   0:00 oracle
13989 oracle1   17   0 44884  12M 11424 S     0.0  0.2   0:00 oracle
31407 oracle1   15   0 44856  11M 11396 S     0.0  0.2   0:00 oracle
14523 oracle1   16   0 44844  11M 11388 S     0.0  0.2   0:00 oracle
 1042 oracle1   16   0 24092  11M 10060 S     0.0  0.2   0:00 java
 1136 oracle1   15   0 24092  11M 10060 S     0.0  0.2   0:00 java
 1138 oracle1   15   0 24092  11M 10060 S     0.0  0.2   0:00 java
 1140 oracle1   15   0 24092  11M 10060 S     0.0  0.2   0:00 java
 1142 oracle1   15   0 24092  11M 10060 S     0.0  0.2   0:00 java
 1148 oracle1   15   0 24092  11M 10060 R     0.0  0.2   0:00 java
 1149 oracle1   20   0 24092  11M 10060 S     0.0  0.2   0:00 java
 1150 oracle1   20   0 24092  11M 10060 S     0.0  0.2   0:00 java
 1151 oracle1   15   0 24092  11M 10060 S     0.0  0.2   0:00 java
 1239 oracle1   16   0 24092  11M 10060 S     0.0  0.2   0:00 java
 1244 oracle1   15   0 24092  11M 10060 S     0.0  0.2   0:00 java
 1245 oracle1   15   0 24092  11M 10060 S     0.0  0.2   0:00 java
 1246 oracle1   15   0 24092  11M 10060 S     0.0  0.2   0:00 java
32598 oracle1   17   0 44528  11M 11072 R     1.0  0.2   0:00 oracle


*information taken from v$session query
SQL> select * from v$session order by status;

SADDR           SID    SERIAL#     AUDSID PADDR         USER# USERNAME                          COMMAND    OWNERID TADDR    LOCKWAIT STATUS   SERVER       SCHEMA# SCHEMANAME                     OSUSER                         PROCESS      MACHINE                                                          TERMINAL                       PROGRAM                                          TYPE       SQL_ADDR SQL_HASH_VALUE PREV_SQL PREV_HASH_VALUE MODULE                                           MODULE_HASH ACTION                           ACTION_HASH CLIENT_INFO                                                      FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET PDM FAILOVER_TYPE FAILOVER_M FAI RESOURCE_CONSUMER_GROUP          PDML_STA PDDL_STA PQ_STATU CURRENT_QUEUE_DURATION
-------- ---------- ---------- ---------- -------- ---------- ------------------------------ ---------- ---------- -------- -------- -------- --------- ---------- ------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- ------------------------------ ------------------------------------------------ ---------- -------- -------------- -------- --------------- ------------------------------------------------ ----------- -------------------------------- ----------- ---------------------------------------------------------------- -------------------- ------------- -------------- --------------- ------------- --------- ------------ --- ------------- ---------- --- -------------------------------- -------- -------- -------- ----------------------
6B2B14E0          1          1          0 6B28ACDC          0                                         0 2147483644                   ACTIVE   DEDICATED          0 SYS                            oracle1                        1257         wbh_as1.wbtea.com                                                UNKNOWN                        oracle@wbh_as1.wbtea.com (PMON)                  BACKGROUND 00                    0 00                     0                                                            0                                            0                                                                                     0            -1              0               0             0 17-FEB-10        29378 NO  NONE          NONE       NO                                   DISABLED ENABLED  ENABLED                       0
6B2B1E48          2          1          0 6B28B08C          0                                         0 2147483644                   ACTIVE   DEDICATED          0 SYS                            oracle1                        1259         wbh_as1.wbtea.com                                                UNKNOWN                        oracle@wbh_as1.wbtea.com (DBW0)                  BACKGROUND 00                    0 00                     0                                                            0                                            0                                                                                     0            -1              0               0             0 17-FEB-10        29378 NO  NONE          NONE       NO                                   DISABLED ENABLED  ENABLED                       0
6B2B27B0          3          1          0 6B28B43C          0                                         0 2147483644                   ACTIVE   DEDICATED          0 SYS                            oracle1                        1261         wbh_as1.wbtea.com                                                UNKNOWN                        oracle@wbh_as1.wbtea.com (LGWR)                  BACKGROUND 00                    0 00                     0                                                            0                                            0                                                                                     0            -1              0               0             0 17-FEB-10        29378 NO  NONE          NONE       NO                                   DISABLED ENABLED  ENABLED                       0
6B2B3118          4          1          0 6B28B7EC          0                                         0 2147483644                   ACTIVE   DEDICATED          0 SYS                            oracle1                        1263         wbh_as1.wbtea.com                                                UNKNOWN                        oracle@wbh_as1.wbtea.com (CKPT)                  BACKGROUND 00                    0 00                     0                                                            0                                            0                                                                                     0            -1              0               0             0 17-FEB-10        29378 NO  NONE          NONE       NO                                   DISABLED ENABLED  ENABLED                       0
6B2B3A80          5          1          0 6B28BB9C          0                                         0 2147483644                   ACTIVE   DEDICATED          0 SYS                            oracle1                        1265         wbh_as1.wbtea.com                                                UNKNOWN                        oracle@wbh_as1.wbtea.com (SMON)                  BACKGROUND 6AE45D7C     3634611010 00                     0                                                            0                                            0                                                                                     0            -1              2              73             0 17-FEB-10        29378 NO  NONE          NONE       NO                                   DISABLED ENABLED  ENABLED                       0
6B2B43E8          6          1          0 6B28BF4C          0                                         0 2147483644                   ACTIVE   DEDICATED          0 SYS                            oracle1                        1267         wbh_as1.wbtea.com                                                UNKNOWN                        oracle@wbh_as1.wbtea.com (RECO)                  BACKGROUND 6AB51180     3625995331 6AB51180      3625995331                                                            0                                            0                                                                                     0            -1              0               0             0 17-FEB-10        29378 NO  NONE          NONE       NO                                   DISABLED ENABLED  ENABLED                       0
6B2B85C0         13          1          0 6B28DCCC          0                                         0 2147483644                   ACTIVE   DEDICATED          0 SYS                            oracle1                        1285         wbh_as1.wbtea.com                                                UNKNOWN                        oracle@wbh_as1.wbtea.com (LNS0)                  BACKGROUND 00                    0 00                     0                                                            0                                            0                                                                                     2            -1              0               0             0 17-FEB-10        29369 NO  NONE          NONE       NO                                   DISABLED ENABLED  ENABLED                       0
6B2B4D50          7          1          0 6B28C2FC          0                                         0 2147483644                   ACTIVE   DEDICATED          0 SYS                            oracle1                        1269         wbh_as1.wbtea.com                                                UNKNOWN                        oracle@wbh_as1.wbtea.com (CJQ0)                  USER       00                    0 6AC62540      2964743345                                                            0                                            0                                                                                     0            -1              0               0             0 17-FEB-10        29378 NO  NONE          NONE       NO                                   DISABLED ENABLED  ENABLED                       0
6B2B56B8          8          1          0 6B28C6AC          0                                         0 2147483644                   ACTIVE   DEDICATED          0 SYS                            oracle1                        1271         wbh_as1.wbtea.com                                                UNKNOWN                        oracle@wbh_as1.wbtea.com (QMN0)                  USER       6AB65730     3151937050 6AB7FB18       546288790                                                            0                                            0                                                                                     0            -1              0               0             0 17-FEB-10        29378 NO  NONE          NONE       NO                                   DISABLED ENABLED  ENABLED                       0
6B2CE1C8         50      14390  255666125 6B292DEC         64 RMS                                     3 2147483644                   ACTIVE   DEDICATED         64 RMS                            Administrator                  3964:1100    WORKGROUP\WBH-MIS-1524                                           WBH-MIS-1524                   sqlplusw.exe                                     USER       5170D7B4      704557925 5170D7B4       704557925 SQL*Plus                                          3669949024                                   4029777240                                                                                453866            -1              0               0             0 17-FEB-10            0 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2B6020          9          1          0 6B28D1BC          0                                         0 2147483644                   ACTIVE   DEDICATED          0 SYS                            oracle1                        1277         wbh_as1.wbtea.com                                                UNKNOWN                        oracle@wbh_as1.wbtea.com (ARC0)                  BACKGROUND 00                    0 00                     0                                                            0                                            0                                                                                     0            -1              0               0             0 17-FEB-10        29378 NO  NONE          NONE       NO                                   DISABLED ENABLED  ENABLED                       0
6B2B6988         10          1          0 6B28D56C          0                                         0 2147483644                   ACTIVE   DEDICATED          0 SYS                            oracle1                        1279         wbh_as1.wbtea.com                                                UNKNOWN                        oracle@wbh_as1.wbtea.com (ARC1)                  BACKGROUND 00                    0 00                     0                                                            0                                            0                                                                                     0            -1              0               0             0 17-FEB-10        29378 NO  NONE          NONE       NO                                   DISABLED ENABLED  ENABLED                       0
6B2B7C58         12      31149  255685392 6B29106C         65 BLD                                     0 2147483644                   INACTIVE DEDICATED         65 BLD                            oracle2                        17485        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       00                    0 5A5856E8      3974905779                                                            0                                            0                                                                                453721            -1              0               0             0 17-FEB-10            3 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2BA1F8         16       5255  255681217 6B28D91C         75 DEV                                     0 2147483644                   INACTIVE DEDICATED         75 DEV                            oracle2                        22878        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       00                    0 69F9416C      2746322519                                                            0                                            0                                                                                453815            -1              0               0             0 17-FEB-10            1 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2BAB60         17       5300  255682413 6B29090C         78 POSTMST                                 3 2147483644                   INACTIVE DEDICATED         78 POSTMST                        oracle2                        22895        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       6AAF7F90       89466228 6AAF7F90        89466228                                                            0                                            0                                                                                419279            -1              0               0             0 17-FEB-10         1073 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2BC798         20      12637  255665420 6B29405C         66 PPK                                     0 2147483644                   INACTIVE DEDICATED         66 PPK                            oracle2                        2199         dhl_as1.wbtea.com                                                                                  ?  @dhl_as1.wbtea.com (TNS V1-V3)             USER       00                    0 5A525944      1559756890                                                            0                                            0                                                                                368291            -1              0               0             0 17-FEB-10         3198 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2BD100         21       6965  255681216 6B28F69C         75 DEV                                     3 2147483644                   INACTIVE DEDICATED         75 DEV                            oracle2                        22878        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       6AAF7F90       89466228 6AAF7F90        89466228                                                            0                                            0                                                                                415683            -1              0               0             0 17-FEB-10         1197 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2BE3D0         23      10970  255681334 6B2901AC         69 SAM                                     3 2147483644                   INACTIVE DEDICATED         69 SAM                            oracle2                        17180        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       6AAF7F90       89466228 6AAF7F90        89466228                                                            0                                            0                                                                                416040            -1              0               0             0 17-FEB-10         1177 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2D9480         69      24579  255681257 6B28F2EC         75 DEV                                     3 2147483644                   INACTIVE DEDICATED         75 DEV                            oracle2                        17126        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       6AAF7F90       89466228 6AAF7F90        89466228                                                            0                                            0                                                                                415809            -1              0               0             0 17-FEB-10         1195 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2D81B0         67      47258  255686350 6B29440C         72 FAS                                     3 2147483644                   INACTIVE DEDICATED         72 FAS                            oracle2                        23781        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       6AAF7F90       89466228 6AAF7F90        89466228                                                            0                                            0                                                                                431093            -1              0               0             0 17-FEB-10          764 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2D6EE0         65      12924  255618522 6B2973FC         66 PPK                                     0 2147483644                   INACTIVE DEDICATED         66 PPK                            oracle2                        10660        dhl_as1.wbtea.com                                                                                  ?  @dhl_as1.wbtea.com (TNS V1-V3)             USER       00                    0 5A525944      1559756890                                                            0                                            0                                                                                223625            -1              0               0             0 17-FEB-10        10380 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2D6578         64      29854  255665681 6B2947BC         66 PPK                                     0 2147483644                   INACTIVE DEDICATED         66 PPK                            oracle2                        2067         dhl_as1.wbtea.com                                                                                  ?  @dhl_as1.wbtea.com (TNS V1-V3)             USER       00                    0 5A525944      1559756890                                                            0                                            0                                                                                369074            -1              0               0             0 17-FEB-10         3179 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2D5C10         63       8386  255575350 6B29952C         75 DEV                                     0 2147483644                   INACTIVE DEDICATED         75 DEV                            oracle2                        16838        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       00                    0 51930BE8       214637085                                                            0                                            0                                                                                445778            -1              2              57             0 17-FEB-10          291 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2D3670         59      15413  255682469 6B291B7C         82 POST09                                  3 2147483644                   INACTIVE DEDICATED         82 POST09                         oracle2                        22895        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       6AAF7F90       89466228 6AAF7F90        89466228                                                            0                                            0                                                                                419445            -1              0               0             0 17-FEB-10         1068 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2D2D08         58      28570  255685197 6B29055C         75 DEV                                     0 2147483644                   INACTIVE DEDICATED         75 DEV                            oracle2                        16835        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       00                    0 68906554      1193751854                                                            0                                            0                                                                                453679            -1              0               0             0 17-FEB-10            4 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2D23A0         57      15117  255685806 6B291F2C         72 FAS                                     3 2147483644                   INACTIVE DEDICATED         72 FAS                            oracle2                        22878        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       6AAF7F90       89466228 6AAF7F90        89466228                                                            0                                            0                                                                                429458            -1              0               0             0 17-FEB-10          816 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2D1A38         56       5408  255560106 6B29618C         64 RMS                                     0 2147483644                   INACTIVE DEDICATED         64 RMS                            Administrator                  1040:624     DHOLKA\DHL-FACT-2006                                             DHL-FACT-2006                  sqlplusw.exe                                     USER       00                    0 644D014C      2734722537 SQL*Plus                                          3669949024                                   4029777240                                                                                 36530            -1              0               0             0 17-FEB-10        25014 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2DB0B8         72      16154  255693919 6B28FA4C         75 DEV                                     0 2147483644                   INACTIVE DEDICATED         75 DEV                            oracle2                        17064        dhl_as1.wbtea.com                                                                                  ?  @dhl_as1.wbtea.com (TNS V1-V3)             USER       00                    0 68906554      1193751854                                                            0                                            0                                                                                453865            -1              0               0             0 17-FEB-10            0 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2D0768         54      24372  255681528 6B28E07C         69 SAM                                     3 2147483644                   INACTIVE DEDICATED         69 SAM                            oracle2                        22901        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       6AAF7F90       89466228 6AAF7F90        89466228                                                            0                                            0                                                                                416623            -1              0               0             0 17-FEB-10         1150 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2CF498         52      26583  255618521 6B29704C         66 PPK                                     0 2147483644                   INACTIVE DEDICATED         66 PPK                            Administrator                  1720:1588    DHOLKA\DHL-FACT-2006                                             DHL-FACT-2006                  sqlplusw.exe                                     USER       530FD7F4      808948547 530FD7F4       808948547 SQL*Plus                                          3669949024                                   4029777240                                                                                341729            -1              0               0             0 17-FEB-10         4812 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2CEB30         51      29091  255682412 6B28EB8C         78 POSTMST                                 0 2147483644                   INACTIVE DEDICATED         78 POSTMST                        oracle2                        22895        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       00                    0 58B93C0C      2324981603                                                            0                                            0                                                                                441788            -1              0               0             0 17-FEB-10          440 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2BED38         24      19147  255690712 6B294F1C         68 DSP                                     0 2147483644                   INACTIVE DEDICATED         68 DSP                            oracle2                        22896        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       6393DAF4     3528304182 6393DAF4      3528304182                                                            0                                            0                                                                                444260            -1              0               0             0 17-FEB-10          354 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2C0970         27      20452  255682467 6B29141C         82 POST09                                  0 2147483644                   INACTIVE DEDICATED         82 POST09                         oracle2                        22895        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       58734108     4031754657 58734108      4031754657                                                            0                                            0                                                                                439697            -1              0               0             0 17-FEB-10          497 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2C12D8         28      10710  255590980 6B29A03C         66 PPK                                     0 2147483644                   INACTIVE DEDICATED         66 PPK                            oracle2                        10660        dhl_as1.wbtea.com                                                                                  ?  @dhl_as1.wbtea.com (TNS V1-V3)             USER       00                    0 5A525944      1559756890                                                            0                                            0                                                                                140400            -1              0               0             0 17-FEB-10        16212 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2C25A8         30      13676  255685198 6B290CBC         75 DEV                                     3 2147483644                   INACTIVE DEDICATED         75 DEV                            oracle2                        16835        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       6AAF7F90       89466228 6AAF7F90        89466228                                                            0                                            0                                                                                427634            -1              0               0             0 17-FEB-10          865 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2C41E0         33      12334  255617288 6B296C9C         66 PPK                                     0 2147483644                   INACTIVE DEDICATED         66 PPK                            oracle2                        2086         dhl_as1.wbtea.com                                                                                  ?  @dhl_as1.wbtea.com (TNS V1-V3)             USER       00                    0 5A525944      1559756890                                                            0                                            0                                                                                219925            -1              0               0             0 17-FEB-10        10709 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2C4B48         34       6221  255690714 6B293CAC         68 DSP                                     3 2147483644                   INACTIVE DEDICATED         68 DSP                            oracle2                        22896        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       6AAF7F90       89466228 6AAF7F90        89466228                                                            0                                            0                                                                                444182            -1              0               0             0 17-FEB-10          355 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2C7A50         39      10844  255685395 6B29319C         65 BLD                                     3 2147483644                   INACTIVE DEDICATED         65 BLD                            oracle2                        17485        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       6AAF7F90       89466228 6AAF7F90        89466228                                                            0                                            0                                                                                428223            -1              0               0             0 17-FEB-10          846 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2C83B8         40      37662  255681333 6B28FDFC         69 SAM                                     0 2147483644                   INACTIVE DEDICATED         69 SAM                            oracle2                        17180        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       00                    0 675CE5CC       499808472                                                            0                                            0                                                                                439817            -1              0               0             0 17-FEB-10          490 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2C9FF0         43      15147  255681529 6B28E7DC         69 SAM                                     0 2147483644                   INACTIVE DEDICATED         69 SAM                            oracle2                        22901        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       00                    0 53731F64      2637166844                                                            0                                            0                                                                                427787            -1              0               0             0 17-FEB-10          851 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2CA958         44      16562  255578232 6B2917CC         64 RMS                                     0 2147483644                   INACTIVE DEDICATED         64 RMS                            Administrator                  5156:4220    WORKGROUP\WBH-MIS-1525                                           WBH-MIS-1525                   sqlplusw.exe                                     USER       00                    0 675F615C      3694317878 SQL*Plus                                          3669949024                                   4029777240                                                                                 97668            -1              0               0             0 17-FEB-10        19546 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2CBC28         46      12682  255681259 6B28E42C         75 DEV                                     0 2147483644                   INACTIVE DEDICATED         75 DEV                            oracle2                        17126        wbhouse.wbtea.com                                                                                  ?  @wbhouse.wbtea.com (TNS V1-V3)             USER       00                    0 68906554      1193751854                                                            0                                            0                                                                                451652            -1              0               0             0 17-FEB-10          138 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
6B2CD860         49       3927  255693920 6B2922DC         75 DEV                                     3 2147483644                   INACTIVE DEDICATED         75 DEV                            oracle2                        17064        dhl_as1.wbtea.com                                                                                  ?  @dhl_as1.wbtea.com (TNS V1-V3)             USER       6AAF7F90       89466228 6AAF7F90        89466228                                                            0                                            0                                                                                453799            -1              0               0             0 17-FEB-10            1 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0


47 rows selected.



Additional information
RAM - 4 GB
SQL> show sga

Total System Global Area  823202612 bytes
Fixed Size                   451380 bytes
Variable Size             570425344 bytes
Database Buffers          251658240 bytes
Redo Buffers                 667648 bytes

Regards

Jimit
Re: Not much memory left for Oracle Process [message #443768 is a reply to message #443760] Wed, 17 February 2010 04:54 Go to previous messageGo to next message
John Watson
Messages: 6563
Registered: January 2010
Location: Global Village
Senior Member
Hi - you have plenty of memory! Linux uses any unused memory as a read-ahead disc cache (nearly 3G in your example), so there will never be much "free" memory. AIX is the same.
Re: Not much memory left for Oracle Process [message #443776 is a reply to message #443760] Wed, 17 February 2010 05:07 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear John Watson

Here as you say is right, but my questions is why don't my users do not get enough memory and processes become very slow. Is there any way to find out which of the processes occupied by OS memory. If i restart my server than it will working well for quite a few hours.

Regards

Jimit
Re: Not much memory left for Oracle Process [message #443785 is a reply to message #443776] Wed, 17 February 2010 05:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What makes you conclude that
a) your users do not get enough memory
b) the application is slow due to memory issues?
Re: Not much memory left for Oracle Process [message #443789 is a reply to message #443760] Wed, 17 February 2010 05:46 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Frank

As soon as i restart the whole setup,my memory is set to it's original value, which is near to my allocated 4114420K value(rely on information from top command) and used memory is very few. After running for few hours, i got the calls of poor performance. Is that any problem with physically memory leakage or poor garbage collection from OS or oracle server is not able to free the memory which is occupied by oracle inactive sessions. please suggest me on this issues.

Regards

Jimit
Re: Not much memory left for Oracle Process [message #443839 is a reply to message #443760] Wed, 17 February 2010 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
When performance gets slow then run following command & post results

vmstat 6 10

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Not much memory left for Oracle Process [message #443903 is a reply to message #443760] Thu, 18 February 2010 00:04 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear BlackSwan

herewith i'm posting results as per your guideline,which may helpful to you.
[oracle1@wbh_as1 odb]$ vmstat 6 10
   procs                      memory    swap          io     system         cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy  id
 2  0  0   3524  16400  71420 2974456   0   0   131   234  157   279  57   7  36
 4  0  0   3524   7216  71436 2961376   0   0    15   143  188   334  74  24   1
 3  1  0   3524   7428  71448 2960136   0   0   153    42  921  1097  60  23  17
 2  0  0   3524  30840  71464 2960176   0   0     2    51  548   688  71  18  11
 4  0  0   3524  28932  71484 2960212   0   0     1    83  286   452  60  21  19
 2  0  0   3524  28300  71496 2960280   0   0     1    52  202   356  39  20  41
 3  0  0   3524  27536  71516 2960332   0   0     1    78  233   402  89  10   1
 4  0  0   3524   7460  71532 2967588   0   0  1203    73  497  1183  87  13   0
 3  1  0   3524   7332  71568 2967640   0   0     7    61  189   391  82  16   2
 4  0  0   3524   6152  71588 2966452   0   0     2    43  163   332  88  11   1


Regards

Jimit
Re: Not much memory left for Oracle Process [message #443905 is a reply to message #443903] Thu, 18 February 2010 00:07 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
si - swap in rate
so - swap out rate

When no swapping occurs, then no lack of RAM exists!
Re: Not much memory left for Oracle Process [message #443912 is a reply to message #443760] Thu, 18 February 2010 00:22 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear BlackSwan

As i understand that my physical memory leakage is not the problem here,so which are the other areas that i have to looking for pin point my problem.Can you suggest me any other statistic which i have to gathered for during the period or anything else.

Regards

Jimit
Re: Not much memory left for Oracle Process [message #443916 is a reply to message #443912] Thu, 18 February 2010 00:32 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You claim to have a problem, but I don't see it.
Re: Not much memory left for Oracle Process [message #443924 is a reply to message #443760] Thu, 18 February 2010 00:44 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear BlackSwan

Yes you are right,i know i have a problem but i'm not able to pin point the problem. whether it is from OS side or from database side or memory configuration of oracle DB. In which direction i look forward.please suggest what should be it on your experience.

Regards

Jimit
Re: Not much memory left for Oracle Process [message #443929 is a reply to message #443924] Thu, 18 February 2010 00:50 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>i know i have a problem
post proof!
Re: Not much memory left for Oracle Process [message #443930 is a reply to message #443924] Thu, 18 February 2010 00:50 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check the Performance Tuning section of the forum. (It is under the RDBMS Server heading). It has a sticky that explains what you can investigate and how you can best ask questions in that forum.
Previous Topic: FORALL with EXECUTE IMMEDIATE
Next Topic: problen in ref cursor
Goto Forum:
  


Current Time: Sat Dec 03 01:16:49 CST 2016

Total time taken to generate the page: 0.10398 seconds