Re: PDML and PQ tunning
Date: Wed, 23 Nov 2011 16:52:06 +0100
Message-ID: <4ECD16A6.5080006_at_gmail.com>
Hi Dave,
thanks again for your valuable input!
Comments in-line.
On 23/11/2011 15:45, Herring Dave - dherri wrote:
> Increasing the right memory parameters may be your best bet, as it seems you've already planned to do so. With parallel operations, you typically need more memory as each PX slave needs memory. Parallel operations work best when you can throw resources at it, pretty much a brute force method. It'd be interesting to see what the PGA advisor says in a statspack report, as in how much of an increase would help, if any.
Requested 2 weeks ago, I'm still waiting for the statspack reports ...
> Sqlplus ARRAYSIZE usually doesn't help much past a value of 300, at least in my testing. And that's only for dealing with results returned to your session. In the production setting, are they really going to be running this query with the expectation of returning 400 MB or so of data (500,000+ rows) to their PC?
No, the real statements do insert ... select. In production I can only test the select part and not the complete insert ... select ... statement.
> If not then try to rewrite the query in tests to return no rows yet leave the plan the same. For example, make the main query just an inline view with the outer-query performing some aggregation so only a few rows are returned. Obviously you'll need to run a number of EXPLAIN PLAN FOR ... attempts to make sure the plans haven't changed, as Oracle may optimize the query because of the aggregation.
I'll try to simulate more closely these insert ... select statements.
Best regards
Dimitre
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 23 2011 - 09:52:06 CST