Re: "SQL AREA" and "LIBARARY CACHE" size?

Was it not Dave Ensor that once concluded that x$ tables actually change less than v$ tables?

Tanel Poder wrote:

>I'd suggest, when possible, not to use any x$ views, but stich with plain
>old documented ways. That way you'll probably avoid a lot of confusion,
>especially when database versions might change..
>
>Tanel.
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Friday, September 26, 2003 1:19 PM
>
>
>
>
>>Hi Prem,
>>
>>I Think you are also doing the same mistake that I have done yesterday,
>>that was causing so much confusion.
>>I had earlier calculated the library cache and sql area size from X$ksmsp.
>>Please check the size of these component in x$ksmss.
>>WHat is difference in both x$ view is little bit confusing.
>>
>>ORACLE try to calcluate SGASTAT from V$SGASTAT based on below mentioned
>>query:
>>===========
>>select inst_id,
>> '',
>> ksmssnam,
>> ksmsslen
>>from x$ksmfs
>>where ksmsslen>1
>>union all
>>select inst_id,
>> 'shared pool',
>> ksmssnam,
>> sum(ksmsslen)
>>from x$ksmss
>>where ksmsslen>1
>> group by inst_id, 'shared pool', ksmssnam
>>union all
>>select inst_id,
>> 'large pool',
>> ksmssnam,
>> sum(ksmsslen)
>>from x$ksmls
>>where ksmsslen>1
>> group by inst_id, 'large pool', ksmssnam
>>union all
>>select inst_id,
>> 'java pool',
>> ksmssnam,
>> ksmsslen
>>from x$ksmjs
>>where ksmsslen>1
>>=====================
>>You can see the library cache and sql area size from :
>>select inst_id,
>> 'shared pool',
>> ksmssnam,
>> sum(ksmsslen)
>>from x$ksmss
>>where ksmsslen>1
>> group by inst_id, 'shared pool', ksmssnam
>>
>>
>>===============
>>However at same time when when you you calculate these component from the
>>x$ksmsp using below mentioned SQL:
>>get different result:
>>
>>
>>
>>SQL> l
>> select
>> ksmchcom contents,
>> sum(ksmchsiz) total
>> from
>> sys.x$ksmsp
>> where
>> inst_id = userenv('Instance')
>> group by ksmchcom
>>
>>
>>
>>With Regards,
>>Manoj Kumar Jha
>>C-56 , Phase-2
>>NOIDA -201305, UP(INDIA)
>>Tata Consultancy Services
>>Ph No: (+91-120) 4461001 ext : 1037 (Off.)
>>Mobile No : 9810090974
>>
>>--------------------------------------------------------------------------
>>
>>
>--------------------------
>
>
>>A transcendentalist engaged in auspicious activities does not meet with
>>destruction either in this world or in the spiritual world; one who does
>>good,
>>is never overcome by evil.
>>--------------------------------------------------------------------------
>>
>>
>--------------------------
>
>
>>
>> Prem Khanna J
>> <jprem_at_kssnet To: Multiple recipients of
>>
>>
>list ORACLE-L <ORACLE-L_at_fatcity.com>
>
>
>> .co.jp> cc:
>> Sent by: Subject: Re: RE: "SQL AREA"
>>
>>
>and "LIBARARY CACHE" size?
>
>
>> ml-errors_at_fat
>> city.com
>>
>>
>> 09/26/03
>> 01:39 PM
>> Please
>> respond to
>> ORACLE-L
>>
>>
>>
>>
>>
>>
>>Hi Manoj,
>>
>>this is the statistics i got from one of my instance.
>> sql area 225 MB
>> library cache 112 MB
>>
>>I was also confused as u were [ i didn't notice this so far ].
>>but reading the replies of Tanel & Steve once again:
>>
>><tanel>
>>But what Steve meant (please correct me if I'm wrong), is that operations
>>on
>>sql areas are managed by library cache structures (library cache latch,
>>
>>
>KGL
>
>
>>manager, lib cache hash table, etc), so sql area is dependent on library
>>cache, despite the fact they both have memory allocated directly from
>>shared pool.
>></tanel>
>>
>>from this (and as per the docs) i understand that sql area is a subset of
>>library cache.
>>perhaps,what "subset" means is:
>> in terms of the control library cache has over sql area and not in terms
>>of memory allocated.
>>is my understanding correct, Tanel ?
>>
>><steve>
>>My best guess at the moment is that when new recreatable chunks are first
>>unpinned,
>>they go onto the transient list, and then when they have been reused, they
>>go back
>>onto the recurrent list.
>></steve>
>>
>>..and reading this post by Steve,i feel there is something more(a lot)
>>
>>
>that
>
>
>>we need to
>>know/understand to discuss about this.
>>
>>Can Steve/Tanel/List explain me what is transient chunk & recurrent chunk
>>
>>
>?
>
>
>>Regards,
>>Jp.
>>
>>P.S.Thanx Naveen.
>>
>>
>>
>>
>>
>>
>>--
>>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>>--
>>Author: Prem Khanna J
>> INET: jprem_at_kssnet.co.jp
>>
>>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>>San Diego, California -- Mailing list and web hosting services
>>---------------------------------------------------------------------
>>To REMOVE yourself from this mailing list, send an E-Mail message
>>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>>the message BODY, include a line containing: UNSUB ORACLE-L
>>(or the name of mailing list you want to be removed from). You may
>>also send the HELP command for other information (like subscribing).
>>
>>
>>
>>
>>DISCLAIMER: The information contained in this message is intended only and
>>solely for the addressed individual or entity indicated in this message
>>
>>
>and
>
>
>>for the exclusive use of the said addressed individual or entity indicated
>>in this message (or responsible for delivery of the message to such
>>
>>
>person)
>
>
>>and may contain legally privileged and confidential information belonging
>>to Tata Consultancy Services. It must not be printed, read, copied,
>>disclosed, forwarded, distributed or used (in whatsoever manner) by any
>>person other than the addressee. Unauthorized use, disclosure or copying
>>
>>
>is
>
>
>>strictly prohibited and may constitute unlawful act and can possibly
>>attract legal action, civil and/or criminal. The contents of this message
>>need not necessarily reflect or endorse the views of Tata Consultancy
>>Services on any subject matter. Any action taken or omitted to be taken
>>based on this message is entirely at your risk and neither the originator
>>of this message nor Tata Consultancy Services takes any responsibility or
>>liability towards the same. Opinions, conclusions and any other
>>
>>
>information
>
>
>>contained in this message that do not relate to the official business of
>>Tata Consultancy Services shall be understood as neither given nor
>>
>>
>endorsed
>
>
>>by Tata Consultancy Services or any affiliate of Tata Consultancy
>>
>>
>Services.
>
>
>>If you have received this message in error, you should destroy this
>>
>>
>message
>
>
>>and may please notify the sender by e-mail. Thank you.
>>
>>--
>>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>>--
>>Author: ManojKr Jha
>> INET: jmanoj_at_delhi.tcs.co.in
>>
>>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>>San Diego, California -- Mailing list and web hosting services
>>---------------------------------------------------------------------
>>To REMOVE yourself from this mailing list, send an E-Mail message
>>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>>the message BODY, include a line containing: UNSUB ORACLE-L
>>(or the name of mailing list you want to be removed from). You may
>>also send the HELP command for other information (like subscribing).
>>
>>
>>
>
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: mln_at_miracleas.dk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).