If I have 10 queries to run in sql assistant. From spool utilization perspective, which approach will be more efficient , is it running all 10 queries in one go or 1 query at a time ?
Define "one go".
When submitted sequentially (=F5) each query's spool will be released after finishing it.
When they're submitted as a single Multi Statement Request (= F9) it might be more efficient overall, but will probably need (way) more spool (all answer sets are kept in spool until all queries finished).
Thanks Dieter !!
When you say it "might be more efficient", in what scenario you feel that this will be efficient ?
There are cases when the multiple statements will operate on the same tables or subqueries in the same way. In that case, the multi-statement request will allow the optimizer to recognize and re-use the spool file rather than computing it again. This can be observed in the Explain by looking for spools that are used but not marked last use, then are used again later in the plan. If this is the case and if these spool files are large while the result set is small, then it is possible it will be more efficient in spool space usage as well.