All Forums Database
Cvinodh 32 posts Joined 10/11
31 Jul 2012
Synchronized scanning

Hi

can someone explain me the concept of Synchronized scanning which often shows up in the Explain plan.

Thanks

kathuriamukesh 1 post Joined 07/12
31 Jul 2012

Synchronized scanning is basically a concept of saving time and resources when multiple users are trying to scan the same table at one time. For example, lets assume table A has 10 rows and user u1 is accessing the table and the processor is still reading the 5th row of the table. If user U2, also requests to access the same table then rather than reading the table from the start it can join U1 request and start reading the table from 6 th row, taking advantage of the reading U1 has done and hence saving sometime.

 

This gives a great advantage as the table size becomes bigger and number of user increases. Hope it answers your question.

 

dnoeth 4628 posts Joined 11/04
01 Aug 2012

Just one remark:

Sync scan is only done for tables which are too large to be cached, so in explain it's always:

"The input table will not be cached in memory, but it is eligible for synchronized scanning"

Dieter

Dieter

Cvinodh 32 posts Joined 10/11
01 Aug 2012

Thanks mukesh and Dieter!!

so you mean the spool file will be shared between two requests?

 

dnoeth 4628 posts Joined 11/04
02 Aug 2012

When a table is to large to be fully cached the syncscan will still cache it, but only partially (the maximum size used for this is based on some settings in dbscontrol).

So it's not a typical "last recently used" cache, it just removes the oldest block before reading a new one.

When there's any other session requesting a full table scan on that table it might piggyback the running scan, thus avoiding IOs.

Both session share acces to those cashed blocks, but still create their own spool.

Dieter

Dieter

Cvinodh 32 posts Joined 10/11
02 Aug 2012

Thanks Dieter !!!

one last question.. can you clarify my understanding..

when a large table is being scanned for request 1 and then request 2 is submitted which also requires the same large table to be scanned will piggyback on request 1 only when the predicates of request 2 matches that of request 1 or if request 2 has all the predicates of request 1 and additional predicates ANDed to it..

Thanks..

dnoeth 4628 posts Joined 11/04
03 Aug 2012

The predicates don't have to match, the 2nd session just has do request a full table scan, too.

Dieter

Dieter

AdamL 5 posts Joined 01/13
10 Jun 2013

So, does this mean both the tables will be undergo a Full table Scan?
Also, will the scanning of rows will be Row Hash scan or All rows scan?

Shelley 28 posts Joined 09/10
11 Jun 2013

the answer to your question will be in the explain..what does the explain say.
--Shelley
 

AdamL 5 posts Joined 01/13
12 Jun 2013

All AMP join step, by way of row hash match scan,using merger join .After that, explain says the input tables for joins will not be cached in memory, but they are eligible for synchronized scanning. Then we sort to order Spool1 by rowhash.
>> Just curious whats the role of rowhash match scan here?
Does it help in doing piggyback, under synchronized scanning?

Shelley 28 posts Joined 09/10
09 Jul 2013

the synchronized scanning is for pulling the data for building the hash join subtable, it is not part of the join itself.
--Shelley--

Shelley 28 posts Joined 09/10
10 Jul 2013

sorry, i did not read your comment correctly. for the row hash match join , the scan is going on as the join is happening
--Shelley--
 

vasudev 24 posts Joined 12/12
18 Nov 2013

Hi,
Whether the synchronized scan work for PPI tables also?

You must sign in to leave a comment.