Re: Oracle 9.2 PGA_Aggregate_target and a statistics question

In article <VGiX8.1475$zX3.1030_at_news.indigo.ie>, "Telemachus" says...
>
>Pete, Jonathan, thanks for the swift and excellent answers !
>
>Followups :
>
>
>1: The 5/30 rule : Is that a hardcode or is it tunable ?
>2: Can I therefore assume that 5% (or 30 %) of PAT will be used as the
>relevant inputs into the query optimization process and would this be PAT
>or delta-PAT ?
>
>trying some numbers assume 4GB or memory and 1G SGA
>Give the OS 512MB
>
>-> PAT = 2.5G
>
>-> 5% PAT = 125M
>
>Which is not bad - a lot of stuff could fit in there ..
>
>However, for a large sort > 125M there does not seem to be a facility to
>fast...
>
>Whoops. just found WORKAREA_SIZE_POLICY ....
>
>knock it to manual when we need a huge chunk to do something fast like a
>big index ?
>
>
>One presumes even if in WAP is in manual and PAT is set then we still cannot
>exceed PGA_AGGREGATE_TARGET.
>
>Food for thought all right....
>
>
>Thanks again
>
>T|
>
>

And the not so swift followup answer. Sorry, I marked this to go back to then completely forgot about it. First thing to be aware of is if you set WORKAREA_SIZE_POLICY to MANUAL (which is not the default in 9.2), the PGA_AGGREGATE_TARGET is ignored. Here's what happens on my 9.2 database when I try and change WORKAREA_SIZE_POLICY to AUTO without PGA_AGGREGATE_TARGET being set:

SQL> l
  1 select name, value
  2 from v$parameter
  3* where name in ('workarea_size_policy', 'pga_aggregate_target') SQL> /

NAME                           VALUE
------------------------------ ----------
pga_aggregate_target           0
workarea_size_policy           MANUAL

SQL> ALTER SYSTEM SET workarea_size_policy='AUTO' SCOPE=BOTH; ALTER SYSTEM SET workarea_size_policy='AUTO' SCOPE=BOTH *
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid ORA-04032: pga_aggregate_target must be set before switching to auto mode

As for the 5/30 rule, AFAIK it's hard-coded.

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Mon Jul 15 2002 - 13:40:16 CDT