can someone explain me the concept of Synchronized scanning which often shows up in the Explain plan.
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.
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"
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.
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..
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?
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?