You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




14 September 2005

undocumented parameter

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

0 Comments:

Post a Comment

<< Home