Home » RDBMS Server » Server Administration » PGA required for optimal and one-pass execution (DB12.x)
PGA required for optimal and one-pass execution [message #642065] Tue, 01 September 2015 11:33
John Watson
Messages: 8803
Registered: January 2010
Location: Global Village
Senior Member
I'm trying to calculate the PGA needed to run a statement. If I actually run it, I can look at v$sql_workarea and see the estimates for optimal execution and for one-pass execution, but is there an algorithm I can apply to calculate it without actually running the statement?

It sounds easy. Sorting is in the docs: to sort 10GB of data needs 10GB plus a bit for optimal, or 40MB for one-pass. A serial hash join is simple, too: the size of the inner row source plus a bit.

EXPLAIN PLAN will tell me the PLAN_TABLE.TEMP_SPACE estimated for any one sort or join, is it reasonable to take the highest value for that, and assume that if I have that much PGA it will run optimally? What about queries that join many tables, where multiple interim result sets may exist concurrently? And there is the degree of parallelism to consider, too.

Thankyou for any insight.
Previous Topic: ORA-01652 error
Next Topic: Quota Grant
Goto Forum:

Current Time: Wed Mar 22 02:08:17 CDT 2023