Re: Library Caches Reports

Hi Ton

You may consider a total reload figure of 165 that represents less than 0.0009% of your pins as high and worthy of a sleepless night.

Me, I would go to bed with a warm glowing feeling, dreaming of Pink Floyd reuniting again with a kick-off concert in sunny Canberra ;)

My main concern with your shared pool is that it's probably too big.

Cheers

Richard
"Ton" <76434.1353_at_compuserve.com> wrote in message news:4477604.0209110443.572c9c0b_at_posting.google.com...
> 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
Received on Wed Sep 11 2002 - 08:18:22 CDT