OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » pgs allocated issue (11.1.0.7 windows xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

pgs allocated issue [message #529038]

Fri, 28 October 2011 07:35

andy huang
Messages: 498
Registered: July 2011

Senior Member

Dear all,
how to know how much pag has allocated a session?

Report message to a moderator

Re: pgs allocated issue [message #529040 is a reply to message #529038]

Fri, 28 October 2011 07:50

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

V$SESSTAT.

Regards
Michel

Report message to a moderator

Re: pgs allocated issue [message #529296 is a reply to message #529040]

Sun, 30 October 2011 20:40

andy huang
Messages: 498
Registered: July 2011

Senior Member

hi,
What about the v$process.Pga_Used_Mem?

SQL> Select Sid From v$mystat Where Rownum<=1;

       SID
----------
       150

SQL> Select Pga_Used_Mem
  2    From V$process Aa, V$session Bb
  3   Where Aa.Addr = Bb.Paddr
  4     And Bb.Sid = 150
  5  /

PGA_USED_MEM
------------
     1043314

Report message to a moderator

Re: pgs allocated issue [message #529297 is a reply to message #529296]

Sun, 30 October 2011 20:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>how to know how much pag has allocated a session?
>Select Pga_Used_Mem

does "used" equal "allocated"?

Report message to a moderator

Re: pgs allocated issue [message #529298 is a reply to message #529297]

Sun, 30 October 2011 21:07

andy huang
Messages: 498
Registered: July 2011

Senior Member

hi,
The "USED" is not equal to "allocated" by flowing test:

SQL> Select Sid From v$mystat Where Rownum<=1;

       SID
----------
       150

SQL> Select Pga_Used_Mem,PGA_ALLOC_MEM
  2    From V$process Aa, V$session Bb
  3   Where Aa.Addr = Bb.Paddr
  4     And Bb.Sid = 150;

PGA_USED_MEM PGA_ALLOC_MEM
------------ -------------
     1049566       1214486

Report message to a moderator

Re: pgs allocated issue [message #529901 is a reply to message #529298]

Thu, 03 November 2011 09:42

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

You approach is more accurate then select from v$sesstat. My only question, why did you ask, if you knew the answer?

Report message to a moderator

Re: pgs allocated issue [message #529904 is a reply to message #529901]

Thu, 03 November 2011 09:49

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

You approach is more accurate then select from v$sesstat

Based on what this claim?

Regards
Michel

Report message to a moderator

Re: pgs allocated issue [message #529922 is a reply to message #529904]

Thu, 03 November 2011 11:03

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

I don't know anymore, where I learned this (perhaps from my colleagues). Some years ago I checked that with dumping of pga.

Report message to a moderator

Re: pgs allocated issue [message #529924 is a reply to message #529922]

Thu, 03 November 2011 11:13

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I said that because I don't see any reason Oracle didn't put in the same code the settings of (object underlying) v$sesstat and v$session.
Base on my experience, I'm pretty sure they added some code lines to the previous ones to set the values in v$session at the same place they set it in v$sesstat.
Why would they make it complex when there is a simple solution?

Regards
Michel

Report message to a moderator

Re: pgs allocated issue [message #529933 is a reply to message #529924]

Thu, 03 November 2011 11:25

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Yes, it would be better so. But I don't know, if it is possible to reflect exactly the real pga allocation in any dynamic view.

Report message to a moderator

Re: pgs allocated issue [message #529956 is a reply to message #529933]

Thu, 03 November 2011 12:20

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

For Oracle it is possible, it just have to sum each time it calls "malloc" and decrease each time it calls "free" and both are embedded into their function which means only a single point to modify and not spread all over the code.

Regards
Michel

[Updated on: Thu, 03 November 2011 12:20]

Report message to a moderator

Re: pgs allocated issue [message #529966 is a reply to message #529956]

Thu, 03 November 2011 12:58

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

The reality can differ from the theory Sad

Report message to a moderator

Re: pgs allocated issue [message #529967 is a reply to message #529966]

Thu, 03 November 2011 13:16

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is not theory, I saw Oracle code (and even worked on part of it), I can tell you, for core rdbms, it is very well designed, it had 10 years lead when it has been designed.

Regards
Michel

Report message to a moderator

Re: pgs allocated issue [message #529969 is a reply to message #529967]

Thu, 03 November 2011 13:31

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Ok, perhaps you are right with Oracle code. I prefer to investigate Oracle as a nature event Smile

Report message to a moderator

Previous Topic:	dbms job not created
Next Topic:	space and clob's

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 04:37:55 CST 2024