Thanks for the question, Y.
Asked: June 26, 2018 - 5:55 pm UTC
Last updated: June 27, 2018 - 2:05 am UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
In direct path read, blocks are read into PGA directly from disk. Where in PGA hold the blocks? Is it work area? How long will PGA hold them? If I have a full table scan and after a while the same session does another full table scan on the same table, suppose both are using direct path read, will the second FTS read from Disk or PGA already has all the blocks and no need for any IO? Let's assume table is unchanged between the two FTS. Thanks.
and Connor said...
No, that memory is "transient" in the sense that blocks simply flow "through" it. For example, I have a nice big 700million row table
SQL> set autotrace on stat
SQL> select max(object_name) from t;
MAX(OBJECT_NAME)
----------------------------------------------------------
sun/util/xml/PlatformXmlPropertiesProvider$Resolver
Statistics
----------------------------------------------------------
0 recursive calls
9 db block gets
1420029 consistent gets
1420000 physical reads
0 redo size
599 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
MAX(OBJECT_NAME)
----------------------------------------------------------
sun/util/xml/PlatformXmlPropertiesProvider$Resolver
Statistics
----------------------------------------------------------
0 recursive calls
9 db block gets
1420029 consistent gets
1420000 physical reads
0 redo size
599 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
You can see that I got no benefit from my pga - I simply did 1420000 physical reads each time.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment