Home » RDBMS Server » Server Administration » pgs allocated issue (11.1.0.7 windows xp)
pgs allocated issue [message #529038] Fri, 28 October 2011 07:35 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
how to know how much pag has allocated a session?
Re: pgs allocated issue [message #529040 is a reply to message #529038] Fri, 28 October 2011 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
V$SESSTAT.

Regards
Michel
Re: pgs allocated issue [message #529296 is a reply to message #529040] Sun, 30 October 2011 20:40 Go to previous messageGo to next message
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
Re: pgs allocated issue [message #529297 is a reply to message #529296] Sun, 30 October 2011 20:45 Go to previous messageGo to next message
BlackSwan
Messages: 22495
Registered: January 2009
Senior Member
>how to know how much pag has allocated a session?
>Select Pga_Used_Mem

does "used" equal "allocated"?
Re: pgs allocated issue [message #529298 is a reply to message #529297] Sun, 30 October 2011 21:07 Go to previous messageGo to next message
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
Re: pgs allocated issue [message #529901 is a reply to message #529298] Thu, 03 November 2011 09:42 Go to previous messageGo to next message
LNossov
Messages: 284
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?
Re: pgs allocated issue [message #529904 is a reply to message #529901] Thu, 03 November 2011 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You approach is more accurate then select from v$sesstat

Based on what this claim?

Regards
Michel
Re: pgs allocated issue [message #529922 is a reply to message #529904] Thu, 03 November 2011 11:03 Go to previous messageGo to next message
LNossov
Messages: 284
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.
Re: pgs allocated issue [message #529924 is a reply to message #529922] Thu, 03 November 2011 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58545
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: pgs allocated issue [message #529933 is a reply to message #529924] Thu, 03 November 2011 11:25 Go to previous messageGo to next message
LNossov
Messages: 284
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.
Re: pgs allocated issue [message #529956 is a reply to message #529933] Thu, 03 November 2011 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58545
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
LNossov
Messages: 284
Registered: July 2011
Location: Germany
Senior Member
The reality can differ from the theory Sad .
Re: pgs allocated issue [message #529967 is a reply to message #529966] Thu, 03 November 2011 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58545
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: pgs allocated issue [message #529969 is a reply to message #529967] Thu, 03 November 2011 13:31 Go to previous message
LNossov
Messages: 284
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.
Previous Topic: dbms job not created
Next Topic: space and clob's
Goto Forum:
  


Current Time: Thu Jul 24 22:49:37 CDT 2014

Total time taken to generate the page: 0.09922 seconds