| PGA_TARGET [message #339119] |
Wed, 06 August 2008 17:37  |
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  |
 |
BlackSwan
Messages: 26766
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 #339274 is a reply to message #339155] |
Thu, 07 August 2008 03:42 |
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 #339378 is a reply to message #339316] |
Thu, 07 August 2008 07:00 |
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 #339396 is a reply to message #339378] |
Thu, 07 August 2008 07:31 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
i hope you get my point.