Re: Library Caches Reports

roobaron <member_at_dbforums.com> wrote in message news:<1795219.1031618536_at_dbforums.com>...
> hmmm ratio tuning :)
>
> Stuff could be getting aged out of the library cache due to a high
> turnover of different SQL over a period of time.
> i.e.period 1: Same SQL run over and over
> period 2: Different SQL run, requires reload, then run over and
> over.
> period 3: SQL from period 1 is required again
> etc.
>
> This might be what is happening, high get% and also high reload, the
> library cache is churning, but the database is finding the SQL required
> once it is loaded.
>
> You might want to check the size of the library cache (maybe publish the
> size here, along with version)
>
> Is the database waiting on Library cache latches? or is the main wait
> something else?
>
> You will get more mileage tuning the chronic waits first. Ratio tuning
> is hit and miss.
>
> have a look at v$session_wait, join by the sid from v$session.
>
> Have Fun
 

Thanks four your response. I have here some system values output

Oracle 8.1.7.0

POOL	        NAME	                    BYTES
                fixed_sga	             75804
                db_block_buffers             65536000
                log_buffer	             66560
shared pool	free memory	             168902980
shared pool	miscellaneous	             1155368
shared pool	VIRTUAL CIRCUITS             364632
shared pool	DML locks	             118560
shared pool	joxs heap init	             4248
shared pool	fixed allocation callback    960
shared pool	PLS non-lib hp	             2096
shared pool	table columns 	             214108
shared pool	trigger defini	             2464
shared pool	enqueue_resources	     146304
shared pool	KGK heap	             17532
shared pool	sessions	             485100
shared pool	trigger source	             88
shared pool	State objects	             303920
shared pool	message pool freequeue	     124552
shared pool	Checkpoint queue	     147528
shared pool	ktlbk state objects	     105716
shared pool	table definiti	             5224
shared pool	transactions	             220324
shared pool	db_files	             370988
shared pool	KGFF heap	             5900
shared pool	KQLS heap	             186915996
shared pool	dictionary cache	     33764752
shared pool	joxlod: in phe	             114560
shared pool	PL/SQL MPCODE	             835468
shared pool	trigger inform	             724
shared pool	db_block_hash_buckets	     259080
shared pool	library cache	             47329060
shared pool	db_block_buffers	     1088000
shared pool	sql area	             95695424
shared pool	PL/SQL DIANA	             1987872
shared pool	processes	             161600
shared pool	joxlod: in ehe	             107204
shared pool	event statistics per sess    774000
large pool	free memory	             33554432
java pool	free memory	             28688384
java pool	memory in use	             4866048

SUBSTR(NAME,1,25)	    GETS	MISSES	IMMEDIATE_GETS	IMMEDIATE_MISSES
messages	            547294	62	0	        0
enqueues	            1212394	1	0       	0
channel operations parent   413	        1	149	        0
cache buffers lru chain	    2239995	109	2251743	        90
checkpoint queue latch	    230488	3	0	        0
cache buffers chains	    798064311	32786	4113085	        12
multiblock read objects	    437108	1	0	        0
redo allocation	            2628023	88      0       	0
redo writing	            256252	60	0       	0
undo global data	    70512	1	0	        0
row cache objects	    5252822	35	0	        0
shared pool	            4088141	52	0       	0
library cache	            66091451	285	0       	0


NAMESPACEGETS	 GETHITRATIO	 PINS	  PINHITRATIO      RELOADS INVALIDS
SQL AREA 4030598 99.645437227925 17087945 99.8326832161503 153	   5462
TABLE/PR 51457	 92.212915638299 96539	  83.9422409596122 12	   0
BODY	 31	 64.516129032258 29	  62.0689655172414 0	   0
TRIGGER	 1339	 99.850634802091 1339	  99.8506348020911 0	   0
INDEX	 107	 33.644859813084 107	  33.6448598130841 0	   0
CLUSTER	 4502	 99.800088849400 4960	  99.7379032258065 0	   0
OBJECT	 0	 100	         0	  100	           0	   0
PIPE	 0	 100	         0	  100	           0	   0