Re: Memory used by a single oracle server process in Solaris

http://intermediatesql.com/aix/how-oracle-uses-memory-on-aix-part-1-processes/

He gives insight on AIX, but you could translate to your enviroment.

On Tue, Aug 23, 2011 at 7:19 AM, Sreejith S Nair < Sreejith.Sreekantan_at_ibsplc.com> wrote:

> Hi Jonathan,
>
> Thank you so much for the hint.
>
> Checking the views you suggested shows me this
>
> SQL> select PGA_USED_MEM/1024/1024 used_mb,PGA_ALLOC_MEM/1024/1024
> alloc_mb,PGA_FREEABLE_MEM/1024/1024 free_mb,PGA_MAX_MEM/1024/1024 max_mb
> from v$process where spid=27430;
>
> USED_MB ALLOC_MB FREE_MB MAX_MB
> ---------- ---------- ---------- ----------
> 71.9989109 87.611393 12.625 220.923893
>
> SQL> select CATEGORY,ALLOCATED/1024/1024 allocated_mb,USED/1024/1024
> USED_MB,MAX_ALLOCATED/1024/1024 max_mb from v$process_memory where pid=114;
>
> CATEGORY ALLOCATED_MB USED_MB MAX_MB
> --------------- ------------ ---------- ----------
> SQL .136795044 .004920959 153.053238
> PL/SQL 60.8475571 .006774902 66.7705688
> JAVA 4.70097351 4.69393921 6.37259674
> Freeable 12.625 0
> Other 9.30106735 9.30106735
>
> This comes close to what I got from 'session_pga_memory' which is close to
> 71 MB.
>
> Still, I did not get the memory I derived from this calculation .
>
> shared memory= 4710400 ( shmid from pmap )
> RSS = 4901764 from pmap and ps .
>
> So RSS - shared memory = PGA used by this process + (code + data ) ?
>
> If Yes then PGA + (code + data ) = 4901764 - 4710400 = 191364 kb = 186 MB.
>
> I am getting PGA as 71 MB, so rest ( 186 - 71 ) is the memory for code +
> data ?
>
> I suspect, I might be missing something here ?
>
> Kind Regards,
> Sreejith
>
>
>
>
>
>
>
> From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
> To: <oracle-l_at_freelists.org>
> Date: 08/23/2011 03:07 PM
> Subject: Re: Memory used by a single oracle server process in
> Solaris
> Sent by: oracle-l-bounce_at_freelists.org
> ------------------------------
>
>
>
>
> You're probably better off looking at v$process, which reports allocated,
> max
> allocated, and used memory. (The various views telling you about process
> memory
> are not completely consistent, so a second opinion may help.)
>
> Example of use at http://jonathanlewis.wordpress.com/2009/06/07/pga-leaks/(The
> bug mentioned in the note does not apply to your version of Oracle.)
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
>
> ----- Original Message -----
> From: "Sreejith S Nair" <Sreejith.Sreekantan_at_ibsplc.com>
> To: <oracle-l_at_freelists.org>
> Sent: Tuesday, August 23, 2011 10:21 AM
> Subject: Memory used by a single oracle server process in Solaris
>
> > SQL> SELECT
> > 2 s.sid sid
> > 3 , lpad(s.username,12) oracle_username
> > 4 , lpad(s.osuser,9) os_username
> > 5 , s.program session_program
> > 6 , lpad(s.machine,8) session_machine
> > 7 , (select ss.value from v$sesstat ss, v$statname sn
> > 8 where ss.sid = s.sid and
> > 9 sn.statistic# = ss.statistic# and
> > 10 sn.name = 'session pga memory') session_pga_memory
> > 11 , (select ss.value from v$sesstat ss, v$statname sn
> > 12 where ss.sid = s.sid and
> > 13 sn.statistic# = ss.statistic# and
> > 14 sn.name = 'session pga memory max')
> session_pga_memory_max
> > 15 , (select ss.value from v$sesstat ss, v$statname sn
> > 16 where ss.sid = s.sid and
> > 17 sn.statistic# = ss.statistic# and
> > sn.name = 'session uga memory') session_uga_memory
> > 18 19 , (select ss.value from v$sesstat ss, v$statname sn
> > 20 where ss.sid = s.sid and
> > 21 sn.statistic# = ss.statistic# and
> > 22 sn.name = 'session uga memory max')
> session_uga_memory_max
> > 23 FROM
> > 24 v$session s
> > 25 WHERE s.sid=&1
> > 26 ORDER BY session_pga_memory DESC
> > 27 /
> > Enter value for 1: 520
> > old 25: WHERE s.sid=&1
> > new 25: WHERE s.sid=520
> >
> > SID Oracle User O/S User Session Program Machine PGA Memory PGA
> > Memory Max UGA Memory UGA Memory MAX
> > ---- ------------ --------- ------------------ -------- --------------
> > -------------- -------------- --------------
> > 520 APP_USER_MB appuser JDBC Thin Client zone2 76,082,400
> > 229,895,392 68,541,416 215,665,464
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>
>
>
>
> DISCLAIMER:
>
> "The information in this e-mail and any attachment is intended only for the
> person to whom it is addressed and may contain confidential and/or
> privileged material. If you have received this e-mail in error, kindly
> contact the sender and destroy all copies of the original communication. IBS
> makes no warranty, express or implied, nor guarantees the accuracy, adequacy
> or completeness of the information contained in this email or any attachment
> and is not liable for any errors, defects, omissions, viruses or for
> resultant loss or damage, if any, direct or indirect."
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l