RE: Manual mem management in 10g
- From: "Schultz, Charles" <sac@xxxxxxxxxxxxx>
- To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>, <topshot.rhit@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Jun 2006 12:55:35 -0500
It was mostly pre-emptive, but came with a lot of advice from folks here
and other smart folks I chat with. We were never to directly correlate
our problems (waits due to lib cache latch) directly to this KGH: NO
ACCESS memory. However, with sga_target = 0, we do not see this pool at
all any more. Coincidence? Oh, one caveat is that we upgraded to
10.2.0.2 recently, but even before that, we did not see KGH: NO ACCESS
with sga_target = 0.
-charles schultz
-----Original Message-----
From: Allen, Brandon [mailto:Brandon.Allen@xxxxxxxxxxx]
Sent: Friday, June 30, 2006 12:45 PM
To: Schultz, Charles; topshot.rhit@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Manual mem management in 10g
Looking back at the history (see below), I can see the "KGH: NO ACCESS"
does grow and shrink regularly, so it doesn't seem to be a constantly
worsening problem, but the name is certainly discomforting. I've opened
an SR on this and a few other sga_target related questions. I'll come
back and post the results if I get anything good.
Did you eventually run into errors as KGH: NO ACCESS grew, or did you
just turn off sga_target as a preemptive move?
SQL> select a.begin_interval_time, b.bytes from wrm$_snapshot a,
dba_hist_sgastat b where a.snap_id = b.snap_id and a.begin_interval_time
> sysdate -2 and b.pool = 'shared pool' and b.name = 'KGH: NO ACCESS'
order by 1;
BEGIN_INTERVAL_TIME
BYTES
------------------------------------------------------------------------
--- ----------
28-JUN-06 10.45.38.375 AM
887200864
28-JUN-06 11.00.41.727 AM
853998464
28-JUN-06 11.15.45.079 AM
753949088
28-JUN-06 11.30.45.662 AM
938777632
28-JUN-06 11.45.49.325 AM
843320288
28-JUN-06 12.00.50.669 PM
1008570976
28-JUN-06 12.15.54.447 PM
1090017440
28-JUN-06 12.30.55.068 PM
942639936
28-JUN-06 12.45.55.728 PM
926280160
28-JUN-06 01.00.56.297 PM
959998688
28-JUN-06 01.15.57.695 PM
927434144
28-JUN-06 01.30.58.531 PM
1007619136
28-JUN-06 01.45.58.972 PM
892675360
28-JUN-06 02.00.59.692 PM
877158560
28-JUN-06 02.16.00.498 PM
877526720
28-JUN-06 02.31.00.837 PM
861641696
28-JUN-06 02.45.07.068 PM
911449248
28-JUN-06 03.00.07.437 PM
977543616
28-JUN-06 03.15.11.089 PM
911523392
28-JUN-06 03.30.12.945 PM
895663072
28-JUN-06 03.45.13.535 PM
845953760
28-JUN-06 04.00.14.114 PM
829724928
28-JUN-06 04.15.17.510 PM
729512192
28-JUN-06 04.30.18.457 PM
679974592
28-JUN-06 04.45.18.716 PM
762935648
28-JUN-06 05.00.19.491 PM
710329792
28-JUN-06 05.15.20.123 PM
727763104
28-JUN-06 05.30.20.897 PM
628760000
28-JUN-06 05.45.21.471 PM
645177056
28-JUN-06 06.00.21.893 PM
612662688
28-JUN-06 06.15.22.765 PM
529832480
28-JUN-06 06.30.26.128 PM
464221568
28-JUN-06 06.45.26.893 PM
447477056
28-JUN-06 07.00.30.216 PM
447477056
28-JUN-06 07.15.30.621 PM
447477056
28-JUN-06 07.30.31.093 PM
447477056
28-JUN-06 07.45.31.325 PM
447477056
28-JUN-06 08.00.37.667 PM
447477056
28-JUN-06 08.15.37.951 PM
447477056
28-JUN-06 08.30.38.720 PM
447477056
28-JUN-06 08.45.42.183 PM
447477056
28-JUN-06 09.00.45.453 PM
579664000
28-JUN-06 09.15.46.816 PM
496342784
28-JUN-06 09.30.47.217 PM
496342784
28-JUN-06 09.45.50.929 PM
496342784
28-JUN-06 10.00.54.184 PM
496391904
28-JUN-06 10.15.54.468 PM
497350144
28-JUN-06 10.30.58.030 PM
514045568
28-JUN-06 10.45.04.562 PM
514045568
28-JUN-06 11.00.08.043 PM
628965504
28-JUN-06 11.15.08.492 PM
579952192
28-JUN-06 11.30.09.313 PM
563477984
28-JUN-06 11.45.12.709 PM
546930048
29-JUN-06 12.00.13.155 AM
481524416
29-JUN-06 12.15.19.531 AM
398513312
29-JUN-06 12.30.20.696 AM
398513312
29-JUN-06 12.45.21.071 AM
398800064
29-JUN-06 01.00.21.654 AM
597122336
29-JUN-06 01.15.22.149 AM
563657824
29-JUN-06 01.30.29.051 AM
564034560
29-JUN-06 01.45.29.394 AM
564034560
29-JUN-06 02.00.29.794 AM
564231104
29-JUN-06 02.15.30.179 AM
547625792
29-JUN-06 02.30.31.195 AM
514808352
29-JUN-06 02.45.34.564 AM
498088384
29-JUN-06 03.00.37.901 AM
695714400
29-JUN-06 03.15.38.310 AM
663904032
29-JUN-06 03.30.38.790 AM
1007495872
29-JUN-06 03.45.40.224 AM
892068608
29-JUN-06 04.00.46.821 AM
876175360
29-JUN-06 04.15.47.086 AM
842931968
29-JUN-06 04.30.47.287 AM
794883776
29-JUN-06 04.45.51.178 AM
794957312
29-JUN-06 05.00.51.632 AM
811669056
29-JUN-06 05.15.51.892 AM
876967936
29-JUN-06 05.30.52.665 AM
827569856
29-JUN-06 05.45.55.939 AM
827659904
29-JUN-06 06.00.59.489 AM
645907776
29-JUN-06 06.15.59.931 AM
745424928
29-JUN-06 06.31.00.256 AM
777587424
29-JUN-06 06.46.00.512 AM
744819168
29-JUN-06 07.00.01.172 AM
711641376
29-JUN-06 07.15.01.557 AM
778046080
29-JUN-06 07.30.02.791 AM
745260896
29-JUN-06 07.45.03.097 AM
712451392
29-JUN-06 08.00.06.670 AM
712132000
29-JUN-06 08.15.10.229 AM
809904640
29-JUN-06 08.30.11.105 AM
793733216
29-JUN-06 08.45.11.414 AM
777012992
29-JUN-06 09.00.11.855 AM
728532096
29-JUN-06 09.15.15.344 AM
859220832
29-JUN-06 09.30.16.044 AM
842869856
29-JUN-06 09.45.16.520 AM
827344512
29-JUN-06 10.00.25.887 AM
761774784
29-JUN-06 10.15.26.296 AM
745636352
29-JUN-06 10.30.27.325 AM
827999200
29-JUN-06 10.45.31.213 AM
843739232
29-JUN-06 11.00.34.773 AM
678160448
29-JUN-06 11.15.35.063 AM
645195072
29-JUN-06 11.30.35.349 AM
678830400
29-JUN-06 11.45.36.215 AM
662470880
29-JUN-06 12.00.36.611 PM
662569088
29-JUN-06 12.15.39.910 PM
727622912
29-JUN-06 12.30.40.935 PM
743785888
29-JUN-06 12.45.41.349 PM
694346592
29-JUN-06 01.00.44.612 PM
760285696
29-JUN-06 01.15.48.297 PM
744654272
29-JUN-06 01.30.48.924 PM
728212608
29-JUN-06 01.45.55.130 PM
662143488
29-JUN-06 02.00.56.208 PM
662299104
29-JUN-06 02.15.59.422 PM
662421952
29-JUN-06 02.30.59.656 PM
662421952
29-JUN-06 02.45.08.838 PM
629268736
29-JUN-06 03.00.09.183 PM
630316928
29-JUN-06 03.15.15.609 PM
613703488
29-JUN-06 03.30.15.836 PM
629923744
29-JUN-06 03.45.16.316 PM
563477920
29-JUN-06 04.00.16.622 PM
497392416
29-JUN-06 04.15.22.942 PM
497392416
29-JUN-06 04.30.23.267 PM
480967264
29-JUN-06 04.45.23.494 PM
480983648
29-JUN-06 05.00.26.802 PM
710133792
29-JUN-06 05.15.27.870 PM
611655936
29-JUN-06 05.30.29.304 PM
728826336
29-JUN-06 05.45.29.858 PM
680074656
29-JUN-06 06.00.30.456 PM
630357056
29-JUN-06 06.15.30.942 PM
531175616
29-JUN-06 06.30.34.783 PM
498430816
29-JUN-06 06.45.38.073 PM
465703232
29-JUN-06 07.00.44.342 PM
465703232
29-JUN-06 07.15.44.564 PM
465703232
29-JUN-06 07.30.44.814 PM
465703232
29-JUN-06 07.45.45.193 PM
465703232
29-JUN-06 08.00.45.801 PM
465916192
29-JUN-06 08.15.49.025 PM
432689312
29-JUN-06 08.30.49.579 PM
416263840
29-JUN-06 08.45.49.969 PM
432320352
29-JUN-06 09.00.53.222 PM
465711008
29-JUN-06 09.15.53.661 PM
449548064
29-JUN-06 09.30.54.639 PM
449761024
29-JUN-06 09.45.57.921 PM
449761024
29-JUN-06 10.00.01.242 PM
449761024
29-JUN-06 10.15.01.541 PM
449761024
29-JUN-06 10.30.04.860 PM
449761024
29-JUN-06 10.45.05.048 PM
433262144
29-JUN-06 11.00.11.277 PM
632461440
29-JUN-06 11.15.11.988 PM
583267936
29-JUN-06 11.30.15.592 PM
399216928
29-JUN-06 11.45.16.079 PM
399216928
30-JUN-06 12.00.19.796 AM
333057952
30-JUN-06 12.15.20.008 AM
415445536
30-JUN-06 12.30.21.221 AM
399511776
30-JUN-06 12.45.21.473 AM
382808224
30-JUN-06 01.00.24.816 AM
366186624
30-JUN-06 01.15.31.006 AM
349687840
30-JUN-06 01.30.34.429 AM
349687840
30-JUN-06 01.45.35.580 AM
349687840
30-JUN-06 02.00.36.063 AM
349687840
30-JUN-06 02.15.45.269 AM
349687840
30-JUN-06 02.30.46.346 AM
349818880
30-JUN-06 02.45.46.818 AM
415029888
30-JUN-06 03.00.56.083 AM
870720544
30-JUN-06 03.15.56.445 AM
854646688
30-JUN-06 03.30.56.913 AM
905812704
30-JUN-06 03.45.58.337 AM
922696128
30-JUN-06 04.00.02.105 AM
906842048
30-JUN-06 04.15.02.676 AM
890850560
30-JUN-06 04.30.08.963 AM
874753024
30-JUN-06 04.45.09.183 AM
875113184
30-JUN-06 05.00.10.130 AM
860595328
30-JUN-06 05.15.10.414 AM
860595328
30-JUN-06 05.30.13.956 AM
861160064
30-JUN-06 05.45.14.199 AM
910689344
30-JUN-06 06.00.14.662 AM
877967776
30-JUN-06 06.15.17.983 AM
861189984
30-JUN-06 06.30.21.241 AM
861607552
30-JUN-06 06.45.21.487 AM
895153920
30-JUN-06 07.00.22.064 AM
895522400
30-JUN-06 07.15.22.994 AM
895522400
30-JUN-06 07.30.26.751 AM
895522400
30-JUN-06 07.45.29.977 AM
878786080
30-JUN-06 08.00.30.164 AM
879449344
30-JUN-06 08.15.33.407 AM
846615328
30-JUN-06 08.30.33.852 AM
846966752
30-JUN-06 08.45.37.229 AM
863694880
30-JUN-06 09.00.37.684 AM
864439936
30-JUN-06 09.15.40.958 AM
831007680
30-JUN-06 09.30.41.667 AM
748406368
30-JUN-06 09.45.41.964 AM
682786272
30-JUN-06 10.00.42.267 AM
749240096
30-JUN-06 10.15.42.784 AM
863167584
191 rows selected.
-----Original Message-----
From: Schultz, Charles [mailto:sac@xxxxxxxxxxxxx]
Sent: Friday, June 30, 2006 10:28 AM
To: Allen, Brandon; topshot.rhit@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Manual mem management in 10g
Before we set sga_target = 0, we were seeing "KGH: NO ACCESS" grow to
over 50% of the shared_pool. Support would only say that this was used
to temporarily transfer data from the buffer cache to the shared_pool,
but they never did disclose why so much, or how, or when, or....
Would you really want most of your memory tied up in something called
"NO ACCESS"? *grin*
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Allen, Brandon
Sent: Friday, June 30, 2006 11:25 AM
To: topshot.rhit@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Manual mem management in 10g
I'm growing concerned about the same thing after seeing some of the
comments on this list. I just implemented my first 10g system (10.2.0.2
on AIX 5.3) about a month ago and I decided to go out on a limb and use
the new gather_stats_job and sga_target. So far, our performance has
been excellent and we haven't had any problems with memory errors, but
after all the problems I've seen on this list I'm increasingly worried
that I may be sitting on a time bomb and maybe I should change back to
manual memory management before it explodes. I've just recently started
keeping a closer eye on v$sgastat, hoping that I can catch any problems
before they get out of hand. A couple odd things I've noticed - a lot
of free memory in the shared pool, even though ADDM keeps telling me I
need to increase my SGA, and a lot of memory allocated to "KGH: NO
ACCESS":
SQL> select * from v$sgastat where bytes > 10000000 order by 3;
POOL NAME BYTES
------------ -------------------------- ----------
shared pool private strands 11624448
shared pool KQR M PO 13381408
log_buffer 14700544
shared pool obj stat memo 16219416
shared pool ASH buffers 16252928
java pool free memory 16777216
shared pool Cursor Stats 17322216
shared pool kglsim heap 17627904
shared pool PCursor 23200608
shared pool CCursor 29468216
shared pool kglsim object batch 31205664
shared pool library cache 31840456
shared pool KGLS heap 50571752
large pool free memory 62080224
shared pool sql area 176615248
shared pool KGH: NO ACCESS 480967264
shared pool free memory 655483792
buffer_cache 1241513984
Anyone else seeing the same pattern? Have an explanation?
Thanks,
Brandon
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do
not consent to Internet email for messages of this kind. Opinions,
conclusions and other information in this message that do not relate to
the official business of this company shall be understood as neither
given nor endorsed by it.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
