1 2 3 Previous Next 35 Replies Latest reply on Jun 9, 2014 6:03 AM by Warrior25 Go to original post
      • 15. Re: How to know which process is causing high memory usage on DB?
        Suntrupth

        Hi,

         

        Run the following queries connected to your database and check how much memory your instance and each process is using.

         

         

        Total memory occupied by Instance (SGA + PGA)

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

        select sum(bytes)/1024/1024 mb from (select bytes from v$sgastat union select value bytes from v$sesstat s, v$statname n where n.STATISTIC# = s.STATISTIC# and  n.name = 'session pga memory' );

         

         

        Process memory (individual process memory)

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

        select a.sid,value/(1024*1024),program from v$session a, v$sesstat b where a.sid=b.sid and b.statistic#=(select statistic# from v$statname where name='session pga memory');

         

         

        If the output of "Total memory" doesn't match up to the used memory that you observe on your Server, you don't have to worry too much.

         

         

        Regards,

        Suntrupth

        1 person found this helpful
        • 16. Re: How to know which process is causing high memory usage on DB?
          jgarry

          You might also consider seeing how much memory the OMS java uses, stopping and starting it, and seeing if it uses less.  Some version/configuration/usages have memory leaks.

          • 17. Re: How to know which process is causing high memory usage on DB?
            Thierry H.

            Hi Joel,

            Isn't the OS/kernel analog enough to converge to the same answer ? ... especially when your link covers the topic for Solaris, Linux, HP-UX and AIX.

            When the concept of "the real available RAM is the free + buffer + cached" is wrong, then i would expect negative criticism from you and all other experts.

             

            Regards,

            Thierry

            • 18. Re: How to know which process is causing high memory usage on DB?
              smit_sign-Oracle

              I generally use-

               

              col name format a30

              select

                 sid,name,value

              from

                 v$statname n,v$sesstat s

              where

                 n.STATISTIC# = s.STATISTIC# and

                 name like 'session%memory%'

              order by 3 asc;   

               

              Smit

              • 20. Re: How to know which process is causing high memory usage on DB?
                sweetritz

                @Suntrupth hi thanks for the reply.

                 

                here is the output of the queries mentioned by you:

                Total memory occupied by Instance (SGA + PGA)

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

                select sum(bytes)/1024/1024 mb from (select bytes from v$sgastat union select value bytes from v$sesstat s, v$statname n where n.STATISTIC# = s.STATISTIC# and  n.name = 'session pga memory' );

                 

                        MB

                ----------

                50151.3262

                 

                Process memory (individual process memory)

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

                select a.sid,value/(1024*1024),program from v$session a, v$sesstat b where a.sid=b.sid and b.statistic#=(select statistic# from v$statname where name='session pga memory');

                 

                       SID VALUE/(1024*1024) PROGRAM

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

                      2276        2.76815796 oracle@apollo1 (TNS V1-V3)

                      2278        .643157959 JDBC Thin Client

                      2279        1.89315796

                      2280        4.08065796 SQL Developer

                      2289        2.14315796 JDBC Thin Client

                      2291        .580657959

                      2297        .580657959

                      2301        .893157959 JDBC Thin Client

                      2303        4.33065796

                      2305        .580657959

                      2307        .580657959

                      2317        3.08065796 oracle@apollo1 (TNS V1-V3)

                      2324        1.83065796

                      2331        1.83065796

                      2339        3.08065796 oracle@apollo1 (TNS V1-V3)

                      2340        .705657959 oracle@apollo1 (TNS V1-V3)

                      2348        2.20565796 OSDC.exe

                      2352        1.83065796

                      2353        3.20565796 sqlplus@zeus04 (TNS V1-V3)

                      2354        1.95565796 oracle@apollo1 (TNS V1-V3)

                      2355        2.58866882 sqlplus@zeus04 (TNS V1-V3)

                      2366        4.26815796 SQL Developer

                      2371        2.08065796 OSDC.exe

                      2378        .580657959

                      2379        2.58065796 oracle@apollo1 (TNS V1-V3)

                      2381        .580657959

                      2383        2.95565796 oracle@apollo1 (TNS V1-V3)

                      2384        .580657959

                      2385        .580657959

                      2386        4.33065796

                      2387        5.81439209

                      2389        .580657959

                      2390        .580657959

                      2391        .580657959

                      2393        1.83065796

                      2394        .580657959

                      2395        .580657959

                      2396        .580657959

                      2397        2383.93939

                      2398        2930.18939

                      2399        .580657959

                      2401        .580657959

                      2402        .580657959

                      2403        .580657959

                      2404        .580657959

                      2405        1.83065796

                      2406        .580657959

                      2407        .580657959

                      2408        6.37689209

                      2410        .580657959

                      2413        .580657959

                      2415        4.45565796

                      2416        .580657959

                      2417        .580657959

                      2420        .580657959

                      2421        2.64315796 oracle@zeus01 (TNS V1-V3)

                      2422        4.14315796

                      2423        .580657959

                      2425        .580657959

                      2428        .580657959

                      2429        .580657959

                      2430        .580657959

                      2431        3.95565796 SQL Developer

                      2432        .580657959

                      2433        .580657959

                      2435        .580657959

                      2438        2350.37689

                      2439        .580657959

                      2440        4.08065796 extract@zeus04 (TNS V1-V3)

                      2441        3.39315796 oracle@apollo1 (TNS V1-V3)

                      2443        2.51815796 OMS

                      2444        .643157959

                      2445        .580657959

                      2446        .580657959

                      2447        .705657959

                      2448        4.58065796

                      2449        .580657959

                      2450        4.12689209

                      2452        .580657959

                      2453        .580657959

                      2454        .580657959

                      2455        .580657959

                      2456        .580657959

                      2457        .580657959

                      2458        .580657959

                      2460        .643157959 JDBC Thin Client

                      2461        .580657959

                      2462        .580657959

                      2463        .580657959

                      2464        .580657959

                      2465        4.33065796

                      2467        .643157959 OSDC.exe

                      2468        .580657959

                      2469        .580657959

                      2470        .580657959

                      2471        .580657959

                 

                 

                       SID VALUE/(1024*1024) PROGRAM

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

                      2472        2.51815796 OMS

                      2473        2.26815796 oracle@apollo1 (TNS V1-V3)

                      2474        .580657959

                      2475        .580657959

                      2476        .643157959

                      2477        6.12689209

                      2478        3.45565796 oracle@apollo1 (TNS V1-V3)

                      2479        .580657959

                      2480        .580657959

                      2481        2.70565796

                      2483        .580657959

                      2484        .580657959

                      2485        3.33065796

                      2486        .580657959

                      2487        .580657959

                      2488        .580657959

                      2489        .580657959

                      2490        .580657959

                      2491        .580657959

                      2492        2.14315796 oracle@apollo1 (TNS V1-V3)

                      2493        1.83065796

                      2494        .580657959

                      2496        .580657959

                      2497        4.31439209

                      2498        .580657959

                      2499        4.31439209

                      2500        .580657959

                      2501        .580657959

                      2502        4.39315796

                      2503        .580657959

                      2504        .580657959

                      2505        .580657959

                      2506        4.01815796

                      2507        .580657959

                      2508        4.00189209

                      2509        7.68939209

                      2510        3.20565796 oracle@apollo1 (TNS V1-V3)

                      2511        .580657959

                      2512        .580657959

                      2513        .580657959

                      2514        6.25189209

                      2515         32.955658 extract@zeus04 (TNS V1-V3)

                      2516        .580657959

                      2518        .580657959

                      2519        4.62689209

                      2520        .580657959

                      2521        .580657959

                      2522        .580657959

                      2523        2.14315796 JDBC Thin Client

                      2524        .580657959

                      2525        7.56439209

                      2526        3.08065796 oracle@zeus04 (q001)

                      2527        1.83065796

                      2528        4.33065796

                      2529        2.58065796 oracle@apollo1 (TNS V1-V3)

                      2530        6.12689209

                      2531        .643157959 OSDC.exe

                      2532        .580657959

                      2534        .580657959

                      2535        .580657959

                      2536        .580657959

                      2537        .580657959

                      2538        2.70565796 OSDC.exe

                      2539        .580657959

                      2541        4.51815796

                      2542        .580657959

                      2543        6.25189209

                      2544        .580657959

                      2545        6.00189209 SQL Developer

                      2547        .580657959

                      2548        3.33065796 oracle@apollo1 (TNS V1-V3)

                      2549        .643157959 oracle@zeus04 (q004)

                      2550        .580657959

                      2551        .580657959

                      2552        5.87689209

                      2554        4.06439209

                      2555        .580657959

                      2556        1.26815796 oracle@apollo1 (TNS V1-V3)

                      2557        .588668823 zeusgimon@zeus04 (TNS V1-V3)

                      2558        .580657959

                      2559        .580657959

                      2560        .580657959

                      2561        5.87689209

                      2562        .580657959

                      2563        .580657959

                      2564        .643157959

                      2565        .580657959

                      2566        .580657959

                      2568        .580657959

                      2569        .580657959

                      2570        1.83065796

                      2571        .580657959

                      2572        4.26815796

                      2573        3.26815796

                      2574        .580657959

                      2575        .580657959

                 

                 

                       SID VALUE/(1024*1024) PROGRAM

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

                      2576        .580657959

                      2577        .580657959

                      2578        .580657959

                      2579        .580657959

                      2580        .580657959

                      2581        .580657959

                      2582        5.01815796 SQL Developer

                      2583        1.83065796

                      2584        4.70565796 oracle@zeus04 (P001)

                      2587        4.25189209

                      2588        .580657959

                      2589        1.26815796 oracle@apollo1 (TNS V1-V3)

                      2590        .580657959

                      2591        3.14315796 oracle@apollo1 (TNS V1-V3)

                      2592        .580657959

                      2593        .580657959

                      2594        .580657959

                      2595        .580657959

                      2596        .580657959

                      2597        .580657959

                      2598        .580657959

                      2599        2.95565796 OSDC.exe

                      2600        .580657959

                      2601        .580657959

                      2602        .580657959

                      2603        .580657959

                      2604        .580657959

                      2605        4.62689209

                      2606        .580657959

                      2607        .580657959

                      2608        1.83065796

                      2609        .580657959

                      2610        .580657959

                      2611        .580657959

                      2612         32.705658 extract@zeus04 (TNS V1-V3)

                      2613        .893157959 JDBC Thin Client

                      2614        .580657959

                      2616        .580657959

                      2617        .580657959

                      2618        .580657959

                      2620        .643157959

                      2621        3.08065796 oracle@apollo1 (TNS V1-V3)

                      2622        .518157959 oracle@zeus04 (O000)

                      2623        5.58065796 SQL Developer

                      2624        4.33065796 oracle@venus01 (TNS V1-V3)

                      2625        .580657959

                      2627        2.39315796 oracle@apollo1 (TNS V1-V3)

                      2628        2.95565796

                      2629        3.51815796 SQL Developer

                      2631        6.25189209

                      2632        4.14315796

                      2633        2.58065796 oracle@apollo1 (TNS V1-V3)

                      2634        1.83065796

                      2636        5.31439209

                      2637        .643157959 OSDC.exe

                      2638        .580657959

                      2639        .580657959

                      2641        3.76815796

                      2645        .580657959

                      2647        1171.87689

                      2648        .580657959

                      2649        .580657959

                      2651        .580657959

                      2652        5.62689209

                      2654        5.93939209

                      2656        .580657959

                      2657        .580657959

                      2659        .580657959

                      2660        4.26815796

                      2662        .580657959

                      2663        2.70565796

                      2664        .580657959

                      2665        .580657959

                      2666        .580657959

                      2667        1.89315796 OSDC.exe

                      2668        10.3143921 replicat@zeus04 (TNS V1-V3)

                      2669        .580657959

                      2670        .580657959

                      2671        .580657959

                      2673        3.83065796

                      2674        4.14315796

                      2675        .580657959

                      2676        .580657959

                      2678        3.89315796 toad.exe

                      2679        3.14315796

                      2680        .580657959

                      2681        .580657959

                      2682        .580657959

                      2683        4.51815796 OSDC.exe

                      2684        .580657959

                      2686        .580657959

                      2687        .580657959

                      2688        .580657959

                      2689        .580657959

                      2690        .580657959

                      2692        .580657959

                 

                 

                       SID VALUE/(1024*1024) PROGRAM

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

                      2693        1435.62689

                      2694        3432.06439

                      2695        4.12689209

                      2696        .580657959

                      2697        .580657959

                      2698        .580657959

                      2699        3.08065796 oracle@apollo1 (TNS V1-V3)

                      2701        3.70565796

                      2703        4.06439209

                      2704        4.14315796

                      2705        4.08065796

                      2706        2.83065796

                      2707        3.76815796

                      2708        3.89315796

                      2709        2.76815796

                      2710        4.01815796

                      2711        2.83065796

                      2712        .580657959

                      2713        .580657959

                      2714        .580657959

                      2715        .580657959

                      2716        .580657959

                      2717        .580657959

                      2718        .580657959

                      2719        .580657959

                      2720        .580657959

                      2721        .580657959

                      2722        .580657959

                      2723        .580657959

                      2724        .580657959

                      2725        .580657959

                      2726        .580657959

                      2727        .580657959

                      2728        .580657959

                      2729        .580657959

                      2730        .580657959

                      2731        1.83065796

                      2732        .580657959

                      2733        .580657959

                      2734        .580657959

                      2735        .580657959

                      2736        .580657959

                      2737        .580657959

                      2738        .580657959

                      2739        .580657959

                      2740        .580657959

                      2741        4.26815796 oracle@venus01 (TNS V1-V3)

                      2742        .580657959

                      2743        3.08065796

                      2744        4.20565796

                      2745        .580657959

                      2746        4.75189209

                      2747        4.37689209

                      2748        .580657959

                      2749        .580657959

                      2750        .580657959

                      2751        4.56439209

                      2752        .580657959

                      2753        4.50189209

                      2754        4.18939209

                      2755        .580657959

                      2756        .580657959

                      2757        .580657959

                      2758        4.56439209

                      2759        4.62689209

                      2760        .580657959

                      2761        .580657959

                      2762        4.50189209

                      2763        .580657959

                      2764        .580657959

                      2765        .580657959

                      2766        .580657959

                      2767        4.50189209

                      2768        .580657959

                      2769        .580657959

                      2770        5.18939209

                      2771        .580657959

                      2772        .580657959

                      2773        1.83065796

                      2774        5.26815796

                      2775        4.68939209

                      2776        4.37689209

                      2777        4.50189209

                      2778        5.58065796 JDBC Thin Client

                      2780        .580657959

                      2781        .580657959

                      2782        .580657959

                      2783        4.62689209

                      2784        .580657959

                      2785        .643157959 OSDC.exe

                      2786        4.39315796

                      2787        2.14315796 JDBC Thin Client

                      2788        3.95565796

                      2789        .580657959

                      2790        3.51815796

                      2791        4.39315796

                 

                 

                       SID VALUE/(1024*1024) PROGRAM

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

                      2792        3.95565796

                      2793        3.89315796

                      2794        3.95565796

                      2795        3.20565796

                      2796        3.33065796

                      2797        2.83065796

                      2798        .580657959

                      2799        .580657959

                      2800        .580657959

                      2801        .580657959

                      2802        .580657959

                      2803        1.83065796

                      2804        .580657959

                      2805        .580657959

                      2806        .580657959

                      2807        1517.81439

                      2808        .580657959

                      2809        .580657959

                      2810        .643157959

                      2811        .580657959

                      2812        .580657959

                      2813        .580657959

                      2814        .580657959

                      2815        .580657959

                      2816        .580657959

                      2817        .580657959

                      2818        .580657959

                      2819        .580657959

                      2820        .580657959

                      2821        .580657959

                      2822        .580657959

                      2823        .580657959

                      2824        .580657959

                      2825        .580657959

                      2826        .580657959

                      2827        .580657959

                      2828        .580657959

                      2829        3.89315796

                      2830        6.43939209

                      2831        .580657959

                      2832        2.64315796

                      2833        .580657959

                      2834        .580657959

                      2835        .580657959

                      2836        .580657959

                      2837        1.83065796

                      2838        .643157959

                      2839        .580657959

                      2840        .580657959

                      2841        .580657959

                      2842        .580657959

                      2843        .580657959

                      2844        .580657959

                      2845        4.08065796

                      2846        .580657959

                      2847        4.26815796

                      2848        .580657959

                      2849        .580657959

                      2850        .580657959

                      2851        5.87689209

                      2852        .580657959

                      2853        4.08065796 oracle@venus01 (TNS V1-V3)

                      2854        .580657959

                      2855        3.26815796 oracle@apollo1 (TNS V1-V3)

                      2856        3.89315796

                      2857        .580657959

                      2858        3.14315796 oracle@apollo1 (TNS V1-V3)

                      2859        .580657959

                      2860        .580657959

                      2861        4.87689209

                      2862        7.51815796 SQL Developer

                      2863        .580657959

                      2864        1.83065796

                      2865        .580657959

                      2866        6.37689209

                      2867        .580657959

                      2868        .580657959

                      2869        4.33065796

                      2870        .580657959

                      2871        .580657959

                      2872        .580657959

                      2873        .580657959

                      2874        .580657959

                      2875        .580657959

                      2876        4.33065796

                      2877        .580657959

                      2878        4.45565796

                      2879        6.62689209

                      2881        .580657959

                      2882        4.33065796

                      2883        .580657959

                      2884        4.45565796

                      2885        .580657959

                      2886        .580657959

                      2888        .580657959

                      2889        .580657959

                 

                 

                       SID VALUE/(1024*1024) PROGRAM

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

                      2890        .580657959

                      2891        .580657959

                      2892        4.08065796

                      2893        .580657959

                      2894        .580657959

                      2896        1.83065796

                      2897        .580657959

                      2898        .580657959

                      2899        3.89315796

                      2900        .580657959

                      2902        .580657959

                      2903        .580657959

                      2904        .580657959

                      2905        .580657959

                      2906        2.51815796 oracle@apollo1 (TNS V1-V3)

                      2907        .580657959

                      2908        .580657959

                      2909        .580657959

                      2911        .893157959 JDBC Thin Client

                      2912        .580657959

                      2913        .580657959

                      2914        .580657959

                      2915        4.58065796

                      2916        .580657959

                      2917        .580657959

                      2918        .580657959

                      2919        4.33065796

                      2921        .580657959

                      2922        .643157959

                      2923        2411.37689

                      2924        .580657959

                      2925        4.08065796

                      2926        .580657959

                      2928        .580657959

                      2929        .580657959

                      2930        .580657959

                      2931        1.83065796

                      2932        .580657959

                      2933        .580657959

                      2934        .580657959

                      2935        2.14315796 JDBC Thin Client

                      2936        .580657959

                      2937        .580657959

                      2938        6.18939209

                      2939        .643157959

                      2940        4.01815796

                      2941        .580657959

                      2943        .580657959

                      2945        .580657959

                      2946        5.00189209

                      2947        .580657959

                      2948        .580657959

                      2949        .580657959

                      2950        .580657959

                      2951        .580657959

                      2952        .580657959

                      2953        .580657959

                      2954        .580657959

                      2956        .580657959

                      2957        .580657959

                      2958        .580657959

                      2959        .580657959

                      2960        3.20565796 oracle@apollo1 (TNS V1-V3)

                      2961        .580657959

                      2962        .580657959

                      2963        3.01815796 oracle@apollo1 (TNS V1-V3)

                      2964        .580657959

                      2966        4.08065796

                      2967        .580657959

                      2968        1.83065796

                      2969        .580657959

                      2970        6.12689209

                      2971        .580657959

                      2972        .580657959

                      2974        4.37689209

                      2976        .580657959

                      2977        2.14315796 JDBC Thin Client

                      2978        .580657959

                      2980        .580657959

                      2981        3.95565796

                      2983        .580657959

                      2984        .580657959

                      2985        .580657959

                      2986        .580657959

                      2987        5.56439209

                      2988        .580657959

                      2989        .705657959

                      2990        .580657959

                      2991        .580657959

                      2992        4.39315796

                      2993        4.26815796

                      2994        3.89315796

                      2995        .580657959

                      2997        2.70565796

                      2998        .580657959

                      2999        .580657959

                 

                 

                       SID VALUE/(1024*1024) PROGRAM

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

                      3000        .580657959

                      3002        6.43939209

                      3003        .643157959 OSDC.exe

                      3004        6.18939209

                      3005        4.33065796 extract@zeus04 (TNS V1-V3)

                      3006        .580657959

                      3007        .580657959

                      3008        1.89315796 OSDC.exe

                      3009        1.83065796

                      3010        .580657959

                      3011        .580657959

                      3012        1.83065796

                      3013        .580657959

                      3014        .580657959

                      3015        .893157959 JDBC Thin Client

                      3016        .580657959

                      3017        .580657959

                      3018        .830657959

                      3019        .580657959

                      3020        1.83065796

                      3021        4.58065796

                      3022        .580657959

                      3023        .580657959

                      3024        .580657959

                      3025        .580657959

                      3026        2.58065796 oracle@apollo1 (TNS V1-V3)

                      3027        .705657959 oracle@apollo1 (TNS V1-V3)

                      3028        .580657959

                      3029        .580657959

                      3030        .580657959

                      3031        .643157959 OSDC.exe

                      3032        6.31439209

                      3033        2.64315796

                      3034        1.08065796 JDBC Thin Client

                      3035        .580657959

                      3037        5.87689209

                      3038        1.83065796

                      3039        4.08065796

                      3040        3.26815796 oracle@apollo1 (TNS V1-V3)

                      3041        .580657959

                      3042        4.12689209

                      3043        .580657959

                      3044        .705657959

                      3045        1.89315796 OSDC.exe

                      3046        .580657959

                      3048        5.37689209

                      3049        2.14315796 JDBC Thin Client

                      3050        4.20565796

                      3052        .580657959

                      3053        4.01815796

                      3054        .643157959

                      3057        4.50189209

                      3058        3.01815796 oracle@apollo1 (TNS V1-V3)

                      3059        1.95565796 oracle@apollo1 (TNS V1-V3)

                      3061        .580657959

                      3063        .580657959

                      3064        .580657959

                      3065        1.83065796

                      3067        1.83065796

                      3068        .580657959

                      3069        .580657959

                      3071        .580657959

                      3073        .518157959 oracle@zeus04 (O002)

                      3074        3.08065796 SQL Developer

                      3075        .580657959

                      3076        4.70565796 extract@zeus04 (TNS V1-V3)

                      3077        .580657959

                      3078        6.31439209

                      3079        .580657959

                      3080        .893157959 JDBC Thin Client

                      3081        3.14315796

                      3082        3.58065796 oracle@apollo1 (TNS V1-V3)

                      3083        1.26815796 oracle@apollo1 (TNS V1-V3)

                      3084        .580657959

                      3085        5.68939209

                      3086        4.51815796

                      3087        .580657959

                      3088        .580657959

                      3089        .580657959

                      3090        4.08065796

                      3091        .580657959

                      3092        .580657959

                      3093        4.08065796

                      3094        4.39315796

                      3095        3.08065796 oracle@apollo1 (TNS V1-V3)

                      3096        1.95565796

                      3097        .580657959

                      3098        .580657959 oracle@zeus04 (O001)

                      3099        .705657959 oracle@apollo1 (TNS V1-V3)

                      3100        .580657959

                      3101        .893157959 JDBC Thin Client

                      3102        .580657959

                      3103        .580657959

                      3104        3.39315796 OSDC.exe

                      3105        .580657959

                      3106        .580657959

                 

                 

                       SID VALUE/(1024*1024) PROGRAM

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

                      3107        .580657959

                      3108        6.18939209

                      3109        .580657959

                      3110        .580657959

                      3111        .580657959

                      3112        .580657959

                      3113        1.95565796 oracle@apollo1 (TNS V1-V3)

                      3114        .580657959

                      3115        2.70565796

                      3116        .580657959

                      3117        .580657959

                      3118        .580657959

                      3119        .580657959

                      3120        .580657959

                      3121        .580657959

                      3122        6.18939209

                      3123        .580657959

                      3124        .580657959

                      3125        8.75189209

                      3126        .580657959

                      3127        .580657959

                      3128        2.14315796 JDBC Thin Client

                      3129        4.33065796

                      3130        3.26815796 oracle@apollo1 (TNS V1-V3)

                      3132        .580657959

                      3133        .580657959

                      3134        .580657959

                      3135        4.39315796

                      3136        .580657959

                      3137        2.45565796 oracle@apollo1 (TNS V1-V3)

                      3138        12.7518921

                      3139        1.89315796 OSDC.exe

                      3140        .580657959

                      3143        4.39315796

                      3144        .580657959

                      3145        .580657959

                      3146        .580657959

                      3147        4.33065796

                      3148        2.89315796 oracle@apollo1 (TNS V1-V3)

                      3149        .580657959

                      3150        3.89315796 JDBC Thin Client

                      3151        .705657959 oracle@apollo1 (TNS V1-V3)

                      3152        .580657959

                      3153        9.75189209

                      3154        .580657959

                      3155        .580657959

                      3156        4.26815796

                      3157        1581.62689

                      3159        .580657959

                      3160        .580657959

                      3161        8.75189209

                      3162        4.50189209

                      3163        2.89315796 oracle@apollo1 (TNS V1-V3)

                      3164        4.62689209

                      3165        4.56439209

                      3167        .580657959

                      3168        4.01815796

                      3170        4.08866882 zeusgimon@zeus04 (TNS V1-V3)

                      3171        .580657959

                      3172        5.50189209

                      3174        4.33065796 SQL Developer

                      3175        .643157959 OSDC.exe

                      3176        3.58065796

                      3178        2.64315796 oracle@apollo1 (TNS V1-V3)

                      3179        .580657959

                      3180        .580657959

                      3182        .580657959

                      3183        2.76815796

                      3184        6.12689209

                      3185        .580657959

                      3186        .580657959

                      3187        .580657959

                      3188        4.08065796

                      3189        .580657959

                      3191        .580657959

                      3192        .580657959

                      3194        .580657959

                      3195        .580657959

                      3197        .580657959

                      3198        4.20565796

                      3199        .580657959

                      3200        .580657959

                      3201        6.75189209

                      3202        .580657959

                      3203        2.08065796 oracle@apollo1 (TNS V1-V3)

                      3204        2.95565796 oracle@apollo1 (TNS V1-V3)

                      3205        .580657959

                      3206        .580657959

                      3207        7.50189209

                      3209        .580657959

                      3210        .580657959

                      3211        4.50189209

                      3212        4.18939209

                      3213        .580657959

                      3214        3.89315796

                      3215        .580657959

                 

                 

                       SID VALUE/(1024*1024) PROGRAM

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

                      3216        .580657959

                      3217        4.70565796 oracle@zeus04 (P000)

                      3218        4.25189209

                      3219        .580657959

                      3220        2.14315796 JDBC Thin Client

                      3221        .893157959 JDBC Thin Client

                      3222        .580657959

                      3223        .580657959

                      3224        .580657959

                      3225        1.83065796

                      3226        .580657959

                      3227        .893157959 JDBC Thin Client

                      3228        .580657959

                      3229        3.08065796 oracle@apollo1 (TNS V1-V3)

                      3230        1.83065796

                      3231        4.01815796

                      3232        .580657959

                      3233        .580657959

                      3234        1331.43939

                      3235        3.45565796 SQL Developer

                      3236        .580657959

                      3238        .580657959

                      3239        .580657959

                      3240        .580657959

                      3242        3.45565796 oracle@zeus04 (EMN0)

                      3243        .580657959

                      3244        5.31439209 oracle@zeus04 (q000)

                      3246        2.14315796 JDBC Thin Client

                      3248        .643157959

                      3249        4.51815796

                      3250        .580657959

                      3251        .643157959 OSDC.exe

                      3252        .580657959

                      3253        .580657959

                      3254        .643157959

                      3255        .580657959

                      3256        5.76815796 SQL Developer

                      3257        .580657959

                      3259        .580657959

                      3260        3.83065796

                      3261        4.01815796 SQL Developer

                      3262        1.83065796

                      3263        3.14315796

                      3265        .518157959 oracle@zeus04 (QMNC)

                      3267        2.95565796 oracle@apollo1 (TNS V1-V3)

                      3268        24.1511688 oracle@zeus04 (ARC5)

                      3269        24.1511688 oracle@zeus04 (ARC4)

                      3270        24.1511688 oracle@zeus04 (ARC3)

                      3271        24.1511688 oracle@zeus04 (ARC2)

                      3272        13.0886688 oracle@zeus04 (ARC1)

                      3273        24.1511688 oracle@zeus04 (ARC0)

                      3274        4.26815796 oracle@venus01 (TNS V1-V3)

                      3276        4.14315796 SQL Developer

                      3277        1.26815796 oracle@zeus04 (RBAL)

                      3278        .643157959 oracle@zeus04 (ASMB)

                      3279        4.46366882 zeusgimon@zeus04 (TNS V1-V3)

                      3281        2.89315796 oracle@zeus04 (LCK0)

                      3287        2.64315796 oracle@zeus04 (MMNL)

                      3288        139.564392 oracle@zeus04 (MMON)

                      3289        3.95565796 oracle@zeus04 (CJQ0)

                      3290        4.26815796 oracle@zeus04 (RECO)

                      3291        5.50189209 oracle@zeus04 (SMON)

                      3292        8.14341736 oracle@zeus04 (CKPT)

                      3293        18.9393921 oracle@zeus04 (LGWR)

                      3294         13.072525 oracle@zeus04 (DBW1)

                      3295         15.572525 oracle@zeus04 (DBW0)

                      3296        .580657959 oracle@zeus04 (MMAN)

                      3297         14.143158 oracle@zeus04 (LMS3)

                      3298         12.330658 oracle@zeus04 (LMS2)

                      3299         14.393158 oracle@zeus04 (LMS1)

                      3300         14.268158 oracle@zeus04 (LMS0)

                      3301         11.018158 oracle@zeus04 (LMD0)

                      3302        5.06445313 oracle@zeus04 (LMON)

                      3303        .518157959 oracle@zeus04 (PSP0)

                      3304        .955657959 oracle@zeus04 (DIAG)

                      3305        2.70565796 oracle@zeus04 (PMON)

                 

                 

                844 rows selected.

                • 21. Re: How to know which process is causing high memory usage on DB?
                  sweetritz

                  how do i check how much memory the OMS java uses?

                  • 22. Re: How to know which process is causing high memory usage on DB?
                    jgarry

                    Not necessarily, as even within some of those OS's some Oracle versions have different ways of allocating shared memory dynamically.  pmap crosses the OS's, but that link had scripts specific for Solaris.  Of course, they're just scripts and could be adapted to anything with pmap.  Unfortunately, not everything listed has pmap, I believe on hp-ux it started on 11iv3.

                     

                    Sorry, didn't really mean to sound so chiding towards you.  There is some cross-purpose in this thread, free memory is one thing (and not really relevant as some have pointed out), high memory usage of a process is another.  Oracle java is not the database, and has been known to be a hog on many platforms.

                    • 23. Re: How to know which process is causing high memory usage on DB?
                      jgarry

                      Use any of the tools to look at any of the processes.  The non-db processes are simpler since they don't have all the ipc memory confusion.  Find the non-db process id's with an extended ps search, perhaps grepping for, surprise, java.  On my system, plain old top command usually shows java.  In the database, the program column of v$session is OMS, and from there you can get whatever Oracle knows about sessions and processes, or just drill down to the session detail statistics from the dbconsole search sessions screen.  session_pga_memory_max and the similar uga may be informative.

                      1 person found this helpful
                      • 24. Re: How to know which process is causing high memory usage on DB?
                        Suntrupth

                        Reviewing the Pga Memory consumed by processes I could see the following SID's consuming quite a bit of memory.

                        3234        1331.43939

                        3157        1581.62689

                        2923        2411.37689

                        2694        3432.06439

                        2397        2383.93939

                        2398        2930.18939

                         

                        Did you check what these processes are executing and how they are connected?

                         

                        Regards,

                        Suntrupth

                        1 person found this helpful
                        • 25. Re: How to know which process is causing high memory usage on DB?
                          sb92075

                          select SUM(value/(1024*1024))  from v$session a, v$sesstat b where a.sid=b.sid and b.statistic#=(select statistic# from v$statname where name='session pga memory');


                          post results from SQL above

                          • 26. Re: How to know which process is causing high memory usage on DB?
                            jgarry

                            Perhaps adding the non-default init.ora parameters might be informative too.

                            1 person found this helpful
                            • 27. Re: How to know which process is causing high memory usage on DB?
                              Franck Pachot

                              Hi,

                              if you already identified that it's a java process with many threads, you can explore the threads with jstack.

                              If the process is related with OEM agent, there are so many bugs about too much cpu usage, too many threads, too much memory usage...

                              Regards,

                              Franck.

                              1 person found this helpful
                              • 28. Re: How to know which process is causing high memory usage on DB?
                                sweetritz

                                @sb92075 here is the result of the query you posted:

                                select SUM(value/(1024*1024))  from v$session a, v$sesstat b where a.sid=b.sid and b.statistic#=(select statistic# from v$statname where name='session pga memory');

                                 

                                SUM(VALUE/(1024*1024))

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

                                            17558.1274

                                • 29. Re: How to know which process is causing high memory usage on DB?
                                  sweetritz

                                  @Suntrupth i am a newbie DBA and learning so many things. To be frank I dont know what these processes are executing and how they are connected. Any help would be great.

                                  If you can help me then i can:


                                  I can post the output of the queries you ask to drill down the issue.