Oracle Scratchpad

January 11, 2007


Filed under: CBO,Parallel Execution,trace files — Jonathan Lewis @ 8:56 pm BST Jan 11,2007

If you look at a 10053 trace file, you will find references to Resc and Resp (sometimes rsc and rsp) lurking in the background whenever the trace file mentions Cost. When you get to the 10gR2 trace file, you see these described in the “Legend” section as:

  • Resp – response cost
  • Resc – resource cost

Effectively, these can be viewed as the serial and parallel execution costs respectively (although there are various fiddles, tweaks, adjustments, and version-dependent variations that make this statement rather inaccurate).

Essentially, the resource cost (Resc) is a measure of the resources needed to complete (that part of) the query; and in the serial case the resource you need is (by definition) the measure of the cost of the query.

But when you run parallel, you calculate the resource requirement for the query then share the resource load across the parallel execution slaves. Assuming the slaves don’t interfere with each other the resources handled by each of N slaves will, in the simplest cases, be 1/N’th of the total resource requirement – which means the response time should be 1/N’th of the serial response time. Hence for parallel execution, you need to show the cost implied by the resource requirement (Resc) and the cost implied by the response time (Resp) of a single slave as it handles its shared of the resources.  (Remember: cost is time).

Of course there are variations. The notional “simplest case” above is the pure tablescan. As soon as you start playing with hash joins, or sorts, and so on, you find that the 1/N need not apply; for example, the resources needed to sort 200,000 rows could be much less than one fifth of the resources needed to sort 1,000,000 rows.

As a final complicating detail – 10g doesn’t assume that parallel execution slaves act completely independently. There is a ‘fudge factor’ of 90% that gets thrown into the calculation – in effect saying that you only get 90% effectiveness from parallel execution.

Leave a Comment »

No comments yet.

RSS feed for comments on this post.

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by