Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Actual Buffer Pool size

Re: Actual Buffer Pool size

From: Antoine BRUNEL <antoinebrunel/yahoo.fr>
Date: Wed, 28 May 2003 04:09:41 +0200
Message-ID: <3ed41a65$0$26874$79c14f64@nan-newsreader-01.noos.net> 





Hi from Paris




"Linda Lee" <goforticket_at_yahoo.com> a écrit dans le message de
news:f901fb80.0305271341.2decaf40_at_posting.google.com...


> On my 8.1.7 database on Windows 2000, I used following parameters to

> set up buffer pools:

> db_block_lru_latches = 8

> db_block_buffers = 210000

> buffer_pool_keep = (50000,2)

> buffer_pool_recycle = (30000,2)

> 1.

> But when I query V$BUFFER_POOL, the number of latches for KEEP and

> RECYCLE is 1. Why they take only one (instead of 2)? Is there a better

> way to get the numbers of latches?

> SQL> select name, set_count "latches", buffers

>   2    from v$buffer_pool

>   3   where id != 0;

> NAME                    latches    BUFFERS

> -------------------- ---------- ----------

> KEEP                          1      50000


> RECYCLE                       1      30000


> DEFAULT                       6     130000


>




Strange behaviour. maybe a bug .... ??



Be more precise concerning exact RDBMS level, hardware architecture
(notably, CPUs) .







> 2.

> I query dba_segments to get the actual sizes of pools. Is there a

> beeter way to get the actuall/real size of each pool? Why the size of

> DEFAULT pool is much larger than what I allocated by init parameters?

> Do I need to increase the size for DEFAULT pool?

> SQL> select sum(blocks) Blocks, sum(bytes)/1024 KBytes, buffer_pool

>   2    from dba_segments

>   3   group by buffer_pool;

>

>      BLOCKS     KBYTES BUFFER_


> ----------- ---------- -------

>     4303299   34426392 DEFAULT


>       20085     160680 KEEP


>       23697     189576 RECYCLE


>

> Thanks a lot.










you cannot refer to the segment size of a schema object to determine the
real number of blocks wich will be read...



RTFM: Oracle8i Designing and Tuning for Performance
....


Use the following steps to determine the percentage of the cache used by an
individual object at a given point in time:



  1.. Find the Oracle internal object number of the segment by entering the
following:



SELECT DATA_OBJECT_ID, OBJECT_TYPE



FROM USER_OBJECTS



WHERE OBJECT_NAME = '<SEGMENT_NAME>';



Because two objects can have the same name (if they are different types of
objects), you can use the OBJECT_TYPE column to identify the object of
interest. If the object is owned by another user, then use the view
DBA_OBJECTS or ALL_OBJECTS instead of USER_OBJECTS.



  2.. Find the number of buffers in the buffer cache for SEGMENT_NAME:



SELECT COUNT(*) BUFFERS



FROM V$BH



WHERE OBJD = <DATA_OBJECT_ID>;



where DATA_OBJECT_ID is from Step 1.



c.. Find the total number of buffers in the instance:



SELECT VALUE "TOTAL BUFFERS"



FROM  V$PARAMETER



WHERE NAME = 'DB_BLOCK_BUFFERS';


d.. Calculate the ratio of buffers to total buffers, to obtain the
percentage of the cache currently used by SEGMENT_NAME.








      Note:
      This technique works only for a single segment. You must run the query


for each partition for a partitioned object.









If the number of local block gets equals the number of physical reads for
statements involving such objects, then consider using a RECYCLE cache,
because of the limited usefulness of the buffer cache for the objects.



........





Identifying Segments to Put into the KEEP and RECYCLE Buffer Pools
A good candidate for a segment to put into the RECYCLE buffer pool is a
segment that is at least twice the size of the DEFAULT buffer pool and has
incurred at least a few percent of the total I/Os in the system.



A good candidate for a segment to put into the KEEP pool is a segment that
is smaller than 10% of the size of the DEFAULT buffer pool and has incurred
at least 1% of the total I/Os in the system.



The trouble with these rules is that it can sometimes be difficult to
determine the number of I/Os per segment if a tablespace has more than one
segment. One way to solve this problem is to sample the I/Os that occur over
a period of time by selecting from V$SESSION_WAIT to determine a statistical
distribution of I/Os per segment.



.....



Obviously, you should have a look at your instance during peak load, to see
which objects are "hot spots", and give an approximate size for pools.





Cdlt


















begin 666 ch19_me4.gif

M1TE&.#EAL (M`/<`````````/P``?P``P ``_P`J```J/P`J?P`JP `J_P!5
M``!5/P!5?P!5P !5_P!_``!_/P!_?P!_P !__P"J``"J/P"J?P"JP "J_P#5
M``#5/P#5?P#5P #5_P#_``#_/P#_?P#_P #__RH``"H`/RH`?RH`P"H`_RHJ
M`"HJ/RHJ?RHJP"HJ_RI5`"I5/RI5?RI5P"I5_RI_`"I_/RI_?RI_P"I__RJJ
M`"JJ/RJJ?RJJP"JJ_RK5`"K5/RK5?RK5P"K5_RK_`"K_/RK_?RK_P"K__U4`
M`%4`/U4`?U4`P%4`_U4J`%4J/U4J?U4JP%4J_U55`%55/U55?U55P%55_U5_
M`%5_/U5_?U5_P%5__U6J`%6J/U6J?U6JP%6J_U75`%75/U75?U75P%75_U7_
M`%7_/U7_?U7_P%7__W\``'\`/W\`?W\`P'\`_W\J`'\J/W\J?W\JP'\J_W]5
M`']5/W]5?W]5P']5_W]_`']_/W]_?W]_P']__W^J`'^J/W^J?W^JP'^J_W_5
M`'_5/W_5?W_5P'_5_W__`'__/W__?W__P'___ZH``*H`/ZH`?ZH`P*H`_ZHJ
M`*HJ/ZHJ?ZHJP*HJ_ZI5`*I5/ZI5?ZI5P*I5_ZI_`*I_/ZI_?ZI_P*I__ZJJ
M`*JJ/ZJJ?ZJJP*JJ_ZK5`*K5/ZK5?ZK5P*K5_ZK_`*K_/ZK_?ZK_P*K__]4`
M`-4`/]4`?]4`P-4`_]4J`-4J/]4J?]4JP-4J_]55`-55/]55?]55P-55_]5_
M`-5_/]5_?]5_P-5__]6J`-6J/]6J?]6JP-6J_]75`-75/]75?]75P-75_]7_
M`-7_/]7_?]7_P-7___\``/\`/_\`?_\`P/\`__\J`/\J/_\J?_\JP/\J__]5
M`/]5/_]5?_]5P/]5__]_`/]_/_]_?_]_P/]___^J`/^J/_^J?_^JP/^J___5
M`/_5/__5?__5P/_5____`/__/___?___P/____\``/\`/_\`?_\`P ```"HJ


M*E5557]_?ZJJJM75U?___R'Y! $``/\`+ ````"P`BT```C_`/\)'$BPH,&#

M"!,J7,BPH<.'$"-*G$BQHL6+&#-JW,BQH\>/($.*'$FRI,F3*%.J7,FRI<N7
M,&/*G$FSILV;.'/JW,FSI\^?0(,*'4JTJ-&C2),J7<JTJ=.>_OCU<^@OG[]_

M_O+YZ^C/WTE__IX^??KTZ=.G)_WE\]?0'[]__OSQ^^>/HS]^`OOIRY<OWSZ-
M_/+ET^=/X#Y_3Y\^??KTZ=.3^1SJ&ZCOHSZ!_/@1Y/>/WSZ+_O8)W*=OH+ZG
M3Y\^??KTJ4E_^ASF&YC/(S]^`O,=Y.?/8C^"^0;RZ_?TZ=.G3Y\^+<F/GS]]
M^?[URS<PWT!^^OKYXZ>OG[]__?+E__O7+]\_?OS^^=.7+U]#??X$YMMG<%^^
M?/P$[LN7C]\_@/WXZ?.G+Y\^@/\$#B3X+]] ?_H`_A,XD&!!@P<1)E2XD&%#
MAP\A1I0XD6)%BQ<Q9M2XD6-'CQ]!ANRX;Q^_?_G\_<LGT)\^@?[V[?/G;]\^
M?__V\?N7SY^_?/W^^?N73V"_AOD&]LN73Y\^?O_\Y2.HKY_ ??W^]<O'3Z"^


M?0G[[2.83Z1(D2)%BA0I4J1(D2)%BA0I4B1#??P$YOOG3Y] ?OP&\N,GD!^_

M?_[T_>/7[Y\_??X&YH.8KV _?OSRZ?N7;V"_? 3W"<Q',%_"? 7SB10I4J1(
MD2)%BA0I4O^D2)$B18IDF$^@OWW_^/$3F*_?0'W^!.KS]V_?OGX#^>DCR"]?


M/GX-\R'TE^]?OH'\\NG3QX]?/X'Y".9#J,]?P7PB18H4*5*D2)$B18H4*5*D
M2)$B%P+(G[Y___;Y^[>OW[]^^O[]^_=/W[]___3]^Y?/W[]___[MZP?PG\"!
M_O(-'#APX+]]__at_3V&R_at_PW[]\`_GE&SCP7[Z!__0-'/AOG[^!`OWM`_A/X$""


M!0T>1)A0X4*&#1T^A!A1XD2*%2U>Q)A1XT:.'3U^!!F28S]]__SM$YC/'[]_
M^0CF&YA/8#Y_`OO]RT=PG[]__O_at_U[,=/X#Z"_/C]R^=O8+Y]!O/_^1.HKY]!


M?OX,\NLG4J1(D2)%BA0I4J1(D2)%BA0I<J&_?/GX#<S'[Y^_?/T&ZANH3R"_
M?/GV"<Q'<%^^?/P>ZA/(+U^^?/KZ">27+Y^_?_WTY<NGSY_ ?/GRZ?-GD!_ 
M? (%ZOOW;Q_ ?P('$BQH\"#"A H7,FSH\"'$B!(G4JQH\2+&C!HW<NSH\2/(
MD"(5^N,GT!\_D/[V6<PG<=_(D2-'CAPY<N3(D2,%^N,W$J$_?@;]]1LY<N1(


MC/WZ">S7+Z0_?Q7S1?0W<N3(D2-'CAPY<N1 ?OGR^1.X;Z._??I&(N27SU]!

M?OE&>O0W<B1'?@/YC;28;^3(D2-'CAPY_W+D2(K\]/WSER^?/GX=]8U,F.]@
MOI$=]?4;.7*COGX"]?4;25%?OGSY1HX<.7+DR)$C1XZ<F,^?P'S_]GG4-S)A
MOH/Y_(WDF,_?R)$C1XX<.7+DR)$C1XX<.=)@/H']]O7KI[!?OGSZ_OG3ER^?
M/X+]\N73)U"?OWSY^A'<ER\?/X+]^/$3R __at_OG___O7+ET_?OW__]N7+Q^_?
MOW_^].7;EP_@/X$#_^7SQR]?/G[_].7+)S!?OGP$__G3ER_?P'[Y\@GDER\?


MOX'\\OWSIV_?OW[Z^ GLER^?P'[Y!/++)]!?/G[^"!+LET\@OWP#]^7+MT\@


MOWS__.G;]Z^?/O]^`OOERP?PG\"!! L:/(@PH<*%#!LZ? @QHL2)%"M:O(@Q
MH\:-'#MZW+B/7[]\__8IY,=/X+Y__03RTS>0'S^!^P3FZ_>/7[Z!^OH)W->/
M8#]]`OWE&YA/H#]___3U$[BOWS]^^P3RRW<P'S^!_?+Q^Z>/W\!\!O,)["=P


M'[]_^?[QXR<P7[Y__OKIZ^>/'S]^__3U^[>/W[]\`_/]Z_=/GT!_^OPIS/>O
MWS]]`O7Y^\>/WS]__?3U\\>/'[]_^OK]V\?O7[Z"_@#F$S_at_PGS^ _P0.)#@0

MH#^!`PD6-'@084*%"QDV=/@0HD" _P0.)%C0X$&$"14N9-C0X4.($27_3J18


MT>)%C!D'[N/WCY^_?_OR\3.8;Z"__at_OD&YAOH3V"^@?D$]LM'<%_!? /S#<S7
M;V"_? 3W_<M',-_!? 3]Y?O7+Y_ ?OT,YB/H3]\_?OW^Y1NH;V _??[^Z>,G
M,-\_?_K^\>LGT)^^?O_\[1/(CY]"?_KZ_?.W[Y^_?0+U^1/83Y^_?_KX"<SW


MSY^^?_SZ:?P'D)_ @00+&CR(,*'"A0P;.GP(D5\_@/\$#B18T.!!A D5+F38

MT.%#B!$E3J18T>)%C!D-\OO'3]^_? ;S%>RWCU^_? /S&<PW,)] ?OGTZ>/'
MKU_!? /S#>27+Y^^?OSRZ=/'CU^_?_D(YCN8_Z]@/H'Y^/W;=Y!?OGS\_NW;

MUV]@OH'Z!O+;)S"?OW_^\OW;MZ\?07[Z!/+K)W!?/X7\] GDU^\?OW[__.4;

MR&^?P'S^_OG+]V_?OGX:-6K4J%&C1HT:-6K4J%&C1HT:-6H<R$]@/G__\AG,

MYX]@/G\"\PW,YZ]@OH'Y!/++IS#?P'P%^^W+QR^?P7P$\QW,5Y"?0'[Y_O%+
MZ"_?OWS^"/+3QX\?07W^!.83R&_?OWS^"N[K)U#?0'W^%.[K)U"?0'T"]^D;
MJ,\?P'__\@GDM^]?/G\"!0H4Z ]@/H$#\_D#^$__at_0((%#1Y$F%#A0H8-'3Z$
M&%'B1(H5+5[$F%'C1O^.'3T^],=/8+Y__/89W*>/8#Z!_?(-W*>O8+Z!^0;F
MVY<PG[]__/()Y,=OH+Y_^?85W+=/(+]\!_/Y$^AO'\%\^@[N\_?/'[]_^?P)
M[">P7[^"^03VVR=P7S]_^?P)["<PW\!\`_/]ZY<PW\!\`O/]\[>/GS^!^03V
MVR=P7S]_^?P)[/?QX\>/'S]^_/CQX\>/'S]^[+AO(+]\^A#NRY>/WS]]^_CU


MRY=OX+Y\^?CIRY<OG\!\^?S]ZZ<O7SY]__at_KNRY>O7[Y]_?CERY=OG\!^^O+E
MT^?OGS]]^?+YR[>O7\%\^?+EXU=PW[Z#^_+EXR>07[Y\^P;JT^=OH#]]`O?_


M`>3W[U\^?O_XY<NW[]^_?_G^_?.7K]^_?_GZ`?PG<"#!? +]Y>OW+Q^_?_GV
M"?2G3^ ^?@+S\?O'+U^^?0#_"1Q(L*#!@P_at_3(O3'CU]!?_SZ*23H+Y^_?_[R
M^5.H,*$_?PH5*DSHSQ_ ?P('$BQH\"#"A H7,FSH\"'$B!(G4JQH\2+&C!H1
M^MNXT2*_B?[V_>.7S]_&C1LI^E/H+Z$_??P*^LOGKZ$_?O_\^>/WS]_&?_[X


M">RG+U^^?/LT\LN7+Y\_@?O\;=RX<>/&C1LW;MRX<>/&C1LW#MPW,=_ ?ODV

M;MPX45^_A/KZ*<SGSV ^A_H&ZMM(4)] ?OP(\OO'_V_?Q7T"^>4;J&_CQHT;
M-V[<N''CQHT;-V[<N%$@/W\3\_D;J&]C08#Y! [,YP_@/X$#"18T>!!A0H4%
M\_E+F,^?PGP&_>E;.##?P'P+%R+DQT]@OH/\_"U<6##?0'[]`/X3.)!@08,'
M$294N)!A0X</(4:4.)%B18L7,6;4N)%C1G[Y^%'LER]?/GX=.W9LZ"\?/W\"
M]^7+U^^?OWS\_ G<ER_?/H'^\AGDQ\^?OGS_^N4;F&\@/WW]_/'3U\_?OW[Y
M\OWKE^\?/W[__.G+EZ^A/G\"\^TSN"]?/GX"]^7+Q^]?/W[Z_.G+IT\A/WX#
M_>GKV+%CQXX=.W;LV+%CQ_^.'3MV[-BQ(T)_^OP)U.?O7[]\__SI\R=0G[]_


M_/@)Y+?/X+Y]_/[E\_<OGT!_^@3ZV[?/G[]]^_S]V\?O7SY__O+U^^?O7SZ!
M_1KF&]@O7SY]^OC]\Y>/H+Y^`O?U^]<O'S^!^O8=Y)<OGS^"^3IV[-BQ8\>.
M'3MV[-BQ8\>.'3OJJ".$`.+'[]\_@?_^_<OW3^"_?_[V_?NGS]^_?_OX`?PG
M<* ^?@+S_?.G3R _?@/Y\1/(C]\_?_K^\>OWSY\^?P/S#1PX<.# ?_D&"NS'
MCU\^??_R#>R7;^"_?0+S#?R7;^# @?[R]1N8#^ __at_0,)%C1X$&%"A0L9-G3X

M$&+_1(D3*5:T>!%C1HT;.7;T^!'DQWW]!.HCF._?OGX"^?7[YR_?P'S^#.83


MZ&_?/W[\!.;K-U"?/X'Z_/W;MZ_?0'[Z"/++EX]?PWP(_>7[EV\@OWSZ]/'C
MUT]@/H+Y%/+3-S!?R) A0X8,&3)DR) A0X8,&3+D1WW^!.8;V(_?/WW^!.H3
MN$_?P'P&_>D3N,_?OWW]_O731U#?0'T"\_DCN*^?07_Y&N[S)[!?P7S_\@WD
ME^]@/H+Z"OHCR&^?0'_[0H8,&3)DR) A0X8,&3)DR) A/^;[U^]?/G[__.D3
MF.]?OW_Y_OG;Q\_?/W_Y#/;3]\_?/H'Y_/'[EX]@OH'Y_P3F\R>PW[]\!/?Y


M^P?0'S^ _P0.)#BP'S^!^PCRX_<OG[^!^?85_)?/GT!]_0KRXR?0G[Z!_/H!
M_"=P(,&"!@\B3*AP(<.&#A]"C"AQ(L6*%B]BS*AQ(\>.'C^"_)BOG\!^^?+M

M&YBOG\!\_/[EVR>PGSY_!?WER\=O8#Y^__SEZS=0WT!]`OGER[=/8#Z"^_+E
MX_=0GT!^^?+ET]=/(+]\^?S]ZZ<O7SY]__at_3FRY=/GS^#_?+ER[>/X+Z0(4.&
M#!DR9,B0(4.&#!DR9,B0(1?ZXR?0'S^0_O99S"=Q7\B0(4.&#!DR9,B0(4.&
M#!DR9,B0#/OU$]BO7TA__BKFBYGH+V3(D"%#A_at_P9,F3(D"%#A_at_P9,F3(AOP&
M\@N),5_(D"%#A_at_P9,F3(D"%#A_at_P9,F3(D![U]1.HKU](B_KRY<L'\)_ @00+


M&CR(,*'"A0P;.GP(,:+$B10K6KR(,:/&C1P[>OP(,J3(D21+FCR),J7*E2Q;

INGP),Z;,F31KVKR),Z?.G3Q[^OP)-*C0H42+&CV*-*G2I4P_!@0`.SL`

`


end
Received on Tue May 27 2003 - 21:09:41 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US