Spooled Data - Sharing


Spooled Data - Sharing


User A, Query has spooled Table A ( say whole table FTS).

Can User B utilize the Spooled data for the queries (on Table A)?

Assumption : No DDLs and User A is still logged in.


Re: Spooled Data - Sharing

No, spool is not shared even with in the same user ids different sessions or with in the sessions different requests.

There might be some performance help from SYNC SCAN though.

Re: Spooled Data - Sharing

Thanks Joe.

One more mile on this question .....

The text goes....

......Secondary Index Usage

Full Table Scans – Sync Scans
In the case of multiple users that access the same table at the same time, the system can do a synchronized scan (sync scan) on the table.

• Multiple simultaneous scans share reads.
• New query joins scan at the current scan point

Some questions>
1) ... multiple users.. In the same session ?
2) Is Secondary Index mandatory for Sync Scan ?
3) What is "current scan point" ?


Re: Spooled Data - Sharing

>>Some questions>
>>1) ... multiple users.. In the same session ?
Nope, multiple users implies different sessions as different users can't share the same session. Sync scan is available across sessions.

2) Is Secondary Index mandatory for Sync Scan ?
As far as I know the two are not related. Sync Scan is projected as a TD mechanism to reduce physical IO by sharing the read operations on the Disk

3) What is "current scan point" ?
my understanding is that it means that if Query 1 which is in progress has already gone past a few blocks, when Query 2 started, Q2 can start sharing disk reads from that point on (where Q1 is now).

It's like joining some one reading a book somewhere after a few chapters (Not my idea of reading a book though :-) )

Probably someone familiar with FSG intrinsics can throw more light on the topic.