Change PGA for session?
XenofonOct 20 2010 — edited Oct 20 2010Hi there,
we are developing an OLTP application on 11gR2. As a consequence of having a lot of small transactions, we have configured a relatively small PGA and a lot of SGA memory and have configured the optimizer relevant parameters for choosing b-tree index paths.
Still we are going to have some batch Jobs, running during the non-office hours. Now my problem is, how to change the DB configuration session-wise, to be more DWH-like. While I can change the optimizer settings session wise, I have problems changing the PGA for a session.
How would you solve the problem? One solution could be to change from automatic PGA memory management to manual, that is to setting the %_AREA_SIZE parameters. These parameters can be changed session wise. But this would very much affect the behaviour of the overall system.
Any idea? What is the standard solution for such issues?
Many thanks in advance.