All Forums Analytics
j355ga 100 posts Joined 12/05
02 Nov 2007
spool variation question

I am comparing two queries for performance. Version "A" is the old one and version "B" is new and improved.I've run version "A" 10 times and version "B" ten times. All the runs from version "A" are identical sql and all of version "B" are identical. When I look in the DBQL spool usage I see that version "A" always has exactly the same spool use but version "B" has a varying spool use. It varies between 317,575,168 and 317,705,728 whereas version "A" is always 1,194,763,776.Does anybody know what might account for the varying spool use?Thanks

Jeff
Fred 1096 posts Joined 08/04
03 Nov 2007

My guess: Query B benefits from use of the aggregate cache. The degree of benefit can depend on how much memory is available for this query and in what order the rows are aggregated. Other queries may be using part of the cache. Rows could be processed in a different order due to use of SyncScan, contention, or just tiny variations in I/O timing between runs.

You must sign in to leave a comment.