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 @ SF
I have been asked for estimating RAM size for an Oracle database on top of Sun Fire server.
This database, according to our customer, should support upto 2000 CONCURRENT database connections.
Can u guide how to estimate the required RAM and SGA and PGA sizes.
Thanks,
Wael
No, I cannot, sorry.
This kind of specification should be provided by the application vendor, and in case you are the developer, you need to do some test to check memory consumtion and expected performance.
Regards
Pingback: Undocumented secrets for super-sizing your PGA « Think Big, Start Small…