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

Take a look on Maxym Kharchenko blog:

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@xxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx>
> To:        <oracle-l@xxxxxxxxxxxxx>
> Date:        08/23/2011 03:07 PM
> Subject:        Re: Memory used by a single oracle server process in
> Solaris
> Sent by:        oracle-l-bounce@xxxxxxxxxxxxx
> ------------------------------
>
>
>
>
> 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@xxxxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> 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."
>
>
>
>
>
>

Other related posts: