Home » RDBMS Server » Server Administration » PGA_TARGET (Linux)
PGA_TARGET [message #339119] Wed, 06 August 2008 17:37 Go to next message
bubbly
Messages: 5
Registered: August 2008
Junior Member

Hi...I am working with 10g R2 oracle (10.2.0.3)

My PGA_TARGET=500M but while sorting it uses 200M and if sorting size is bigger than 200M it move it to temp tablespace to do sorting.

can someone tell me why its not doing sorting in PGA though its value is 500M?
Re: PGA_TARGET [message #339121 is a reply to message #339119] Wed, 06 August 2008 17:46 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


using CUT & PASTE post SELECT & results which prove the following:
>if sorting size is bigger than 200M it move it to temp tablespace to do sorting.

using sqlplus do the following:
SQL> set lines 150
SQL> select * from V_$PGA_TARGET_ADVICE;

then using CUT & PASTE along with <code tags> post results back here like below

  1* select * from V_$PGA_TARGET_ADVICE
SQL> /

PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ------------------- ----------------------------- --------------------
               74711040              .125 ON       2.2355E+12          7.2340E+11                            76                46729
              149422080               .25 ON       2.2355E+12          3.8732E+11                            85                 4593
              298844160                .5 ON       2.2355E+12          8.1343E+10                            96                    0
              448266240               .75 ON       2.2355E+12          6.6714E+10                            97                    0
              597688320                 1 ON       2.2355E+12          5.5317E+10                            98                    0
              717225984               1.2 ON       2.2355E+12          4.4193E+10                            98                    0
              836763648               1.4 ON       2.2355E+12          4.3978E+10                            98                    0
              956301312               1.6 ON       2.2355E+12          4.3978E+10                            98                    0
             1075838976               1.8 ON       2.2355E+12          4.3709E+10                            98                    0
             1195376640                 2 ON       2.2355E+12          4.3709E+10                            98                    0
             1793064960                 3 ON       2.2355E+12          4.3709E+10                            98                    0
             2390753280                 4 ON       2.2355E+12          4.3709E+10                            98                    0
             3586129920                 6 ON       2.2355E+12          4.3709E+10                            98                    0
             4781506560                 8 ON       2.2355E+12          4.3709E+10                            98                    0

14 rows selected.

[Updated on: Wed, 06 August 2008 18:28] by Moderator

Report message to a moderator

Re: PGA_TARGET [message #339155 is a reply to message #339119] Wed, 06 August 2008 23:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because you are not allowed to use the whole PGA target for you alone, it is a global limit for all sessions and there are sublimits for each one.

Regards
Michel
Re: PGA_TARGET [message #339274 is a reply to message #339155] Thu, 07 August 2008 03:42 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
There is a hidden parameter, but without recommendation of oracle don't change it.



SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 6 03:51:02 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter pga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 500M
SQL> show parameter _pga

SQL> select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx and substr(ksppinm,1,1)='_'
and ksppinm like '_pga%' ;
2 3 4
Parameter
--------------------------------------------------------------------------------
Session Value
--------------------------------------------------------------------------------
Instance Value
--------------------------------------------------------------------------------
_pga_large_extent_size
1048576
1048576

_pga_max_size
209715200
209715200

Parameter
--------------------------------------------------------------------------------
Session Value
--------------------------------------------------------------------------------
Instance Value
--------------------------------------------------------------------------------


SQL> select 209715200/1024/1024 from v$dual




SQL> select 209715200/1024/1024 from dual;

209715200/1024/1024
-------------------
200

_pga_max_size <--- equal to the value of this hidden parameter sorting is done in PGA than it goes to temp.
Re: PGA_TARGET [message #339316 is a reply to message #339274] Thu, 07 August 2008 04:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you are proud to show hidden parameters to newbies and lead them to unsupported environment?
In addition, your answer is not complete, there are other limits.

Regards
Michel
Re: PGA_TARGET [message #339378 is a reply to message #339316] Thu, 07 August 2008 07:00 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
Michel already said
>>Without recommendation of oracle don't change it(_pga_max_size).

Michel please give link to bubbly so that he/she can read it to know on which factor it depends.

-------------------------------------------------------
"Big Tree never allows small tree to grow under them"
Re: PGA_TARGET [message #339382 is a reply to message #339378] Thu, 07 August 2008 07:03 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

"Big Tree never allows small tree to grow under them"

Yes, if small tree in a wrong way Smile i hope you get my point.

Re: PGA_TARGET [message #339388 is a reply to message #339382] Thu, 07 August 2008 07:17 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
No Big tree never allows small tree of same type to grow under them its nature..., Even Big tree never followed any rule to grow they just grow but they try to dictate terms to small tree..Very Happy
Re: PGA_TARGET [message #339396 is a reply to message #339378] Thu, 07 August 2008 07:31 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel already said

But not emphasized as you perfectly know to do it.
Anyway, do not post unsupported stuff to newbies

Quote:
Michel please give link to bubbly so that he/she can read it to know on which factor it depends.

I will surely not do this, the only main point is that there are limits and the behaviour he saw is expected. If for one process this does not fit his requirements he can switch to manual mode.

Regards
Michel
Previous Topic: size
Next Topic: ORA-1115 error
Goto Forum:
  


Current Time: Tue Dec 06 06:23:55 CST 2016

Total time taken to generate the page: 0.09582 seconds