|
|
|
Forum Guru
 
Group: Forum Members
Last Login: Yesterday @ 12:00:05 PM
Posts: 72,
Visits: 60
|
|
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
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: 2 days ago @ 5:38:40 PM
Posts: 280,
Visits: 297
|
|
|
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.
|
|
|
|