Just in case you read my success story on Don Burleson webpage about undocumented parameters.
out of metalink thread 460157.996 :
“I set appropriate values for pga_aggregate_target and _pga_max_size…
alter system set pga_aggregate_target=6G;
alter system set “_pga_max_size”=2000000000;
…and I gave the query some hints “NOREWRITE FULL USE_HASH ORDERED”. As a result, it boosted my query performance from 12 hours to 1.5 hour.”
a few lines below I mentioned :
this parameter often leads to an ORA-4030, even when plenty of memory available, for some obscure reasons
I think last sentence is quite interresting, too.
Well, I must say that I finally opted for a more maintenable solution :
no more hints, no more undocumented parameter, but parallel processing up to 16 threads on a 4 cpus server.
As discussed in the iTar, a supported way to increased the maximum pga memory per single sql query is to increase the degree of parallelism.
As a rule of dumb, if you can avoid hidden parameters, avoid them!
see you soon @ SFGoogle+