-
15. Re: How to know which process is causing high memory usage on DB?
1 person found this helpfulHi,
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
-
16. Re: How to know which process is causing high memory usage on DB?
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?
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?
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?
@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?
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?
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?
1 person found this helpfulUse 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.
-
24. Re: How to know which process is causing high memory usage on DB?
1 person found this helpfulReviewing 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
-
25. Re: How to know which process is causing high memory usage on DB?
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?
1 person found this helpfulPerhaps adding the non-default init.ora parameters might be informative too.
-
27. Re: How to know which process is causing high memory usage on DB?
1 person found this helpfulHi,
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.
-
28. Re: How to know which process is causing high memory usage on DB?
@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?
@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.
Join the world’s largest interactive community dedicated to Oracle technologies.
- Learn from thousands of community experts
- Get answers to your technical questions
- Share your knowledge with peers
1,060,000
Active Users1,100,000
Answered Questions