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

Now that I think about it, it might very well be so :) DBA_ views vs. base tables would be a different story (uet$ & fet$ case with LMT for example...)

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, September 26, 2003 2:14 PM

> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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).