Direct Path Reads

I was reading from Oracle product documentation about direct path reads and here it is.

<quote>
   Direct reads read directly from disk into the PGA, again bypassing the buffer cache
</quote>

So, what is this Direct path read is about? How can we instruct optimizer to go for Direct path read to get data for queries?

In Oracle we dont need to 'define' a direct read in sql statement they just 'happen'. Parallel query for example - very often does direct reads.

rajesh@ORA10GR2> select count(*) from big_table;

  COUNT(*)
----------
  12773888

Elapsed: 00:00:32.35
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec show_space(user,'BIG_TABLE','TABLE');
l_total_blocks****************  188416
l_total_bytes*****************  1543503872
l_unused_blocks***************  1665
l_unused_bytes****************  13639680
l_last_used_extent_file_id****  6
l_last_used_extent_block_id***  91657
l_last_used_block*************  6527
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  0
l_fs2_bytes*******************  0
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  0
l_fs4_bytes*******************  0
l_full_blocks*****************  186083
l_full_bytes******************  1524391936

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.42
rajesh@ORA10GR2>
Then ran the below script from sql*plus
exec dbms_workload_repository.create_snapshot();

select /*+ parallel(big_table,8) */ owner,object_type,object_name
from big_table;

AWR Show's me this.

                                                                   Avg
                                             %Time  Total Wait    wait     Waits
Event                                 Waits  -outs    Time (s)    (ms)      /txn
---------------------------- -------------- ------ ----------- ------- ---------
PX Deq Credit: send blkd            277,868     .0      17,822      64     624.4
direct path read                     23,911     .0         176       7      53.7db file scattered read               12,656     .0          32       3      28.4
db file sequential read              11,105     .0          21       2      25.0
control file parallel write           3,286     .0           9       3       7.4
control file sequential read         11,338     .0           8       1      25.5
db file parallel write                1,578     .0           5       3       3.5
SQL*Net more data to client         166,611     .0           1       0     374.4
os thread startup                        57     .0           1      24       0.1
log file parallel write               2,492     .0           1       1       5.6
enq: KO - fast object checkp              1     .0           1     813       0.0
Data file init write                     16     .0           0      26       0.0
rdbms ipc reply                          76     .0           0       4       0.2
latch free                               19     .0           0      16       0.0
PX Deq: Signal ACK                        6   66.7           0      35       0.0
SQL*Net message to client            85,410     .0           0       0     191.9
log file sync                           115     .0           0       1       0.3
log file switch completion                2     .0           0      34       0.0
kksfbc child completion                   1  100.0           0      59       0.0
cursor: pin S wait on X                   3  100.0           0      15       0.0
log buffer space                         74     .0           0       0       0.2
read by other session                     4     .0           0       4       0.0
direct path write                        22     .0           0       0       0.0
log file sequential read                  2     .0           0       5       0.0
PX qref latch                         1,658   99.9           0       0       3.7
PX Deq: Table Q Get Keys                 20     .0           0       0       0.0
direct path write temp                   10     .0           0       1       0.0
enq: RO - fast object reuse              73     .0           0       0       0.2
reliable message                         75     .0           0       0       0.2
log file single write                     2     .0           0       1       0.0
LGWR wait for redo copy                 104     .0           0       0       0.2
library cache load lock                   2     .0           0       1       0.0
row cache lock                            8     .0           0       0       0.0
db file single write                      1     .0           0       1       0.0
PX Deq: Table Q qref                      6     .0           0       0       0.0
enq: PS - contention                      3     .0           0       0       0.0
cursor: mutex S                           1     .0           0       0       0.0
Streams AQ: qmn slave idle w            345     .0       9,611   27858       0.8
Streams AQ: qmn coordinator             691   50.1       9,611   13909       1.6
virtual circuit status                  321  100.0       9,583   29853       0.7
PX Idle Wait                          3,595   99.5       7,121    1981       8.1
SQL*Net message from client          85,410     .0       6,756      79     191.9
Streams AQ: waiting for time              4  100.0       2,405  601321       0.0
PX Deq: Execution Msg                   539   34.7         407     756       1.2
jobq slave wait                          40  100.0         120    3000       0.1
PX Deq: Execute Reply                   139     .0          27     196       0.3
PX Deq Credit: need buffer              159     .0           7      45       0.4
PL/SQL lock timer                         2   50.0           1     495       0.0
PX Deq: Parse Reply                      22     .0           0       1       0.0
PX Deq: Table Q Sample                   21     .0           0       0       0.0
PX Deq: Msg Fragment                      7     .0           0       0       0.0
PX Deq: Join ACK                         17     .0           0       0       0.0
class slave wait                         30     .0           0       0       0.1
PX Deq: Table Q Normal                   10     .0           0       0       0.0
          -------------------------------------------------------------
Then ran the below script from sql*plus

exec dbms_workload_repository.create_snapshot();

select owner,object_type,object_name
from big_table;

AWR Show's me this.

                                                                   Avg
                                             %Time  Total Wait    wait     Waits
Event                                 Waits  -outs    Time (s)    (ms)      /txn
---------------------------- -------------- ------ ----------- ------- ---------
db file scattered read               12,446     .0         176      14     177.8
db file sequential read               2,261     .0           6       3      32.3
control file parallel write           1,391     .0           6       4      19.9
control file sequential read          4,872     .0           3       1      69.6
SQL*Net more data to client         160,000     .0           2       0   2,285.7
db file parallel write                  468     .0           2       3       6.7
log file parallel write                 360     .0           0       1       5.1
os thread startup                        13     .0           0      22       0.2
SQL*Net message to client            85,164     .0           0       0   1,216.6
latch free                                5     .0           0      19       0.1
log file sync                            14     .0           0       3       0.2
direct path write                         4     .0           0       6       0.1
LGWR wait for redo copy                  32     .0           0       0       0.5
virtual circuit status                  139  100.0       4,164   29957       2.0
Streams AQ: qmn slave idle w            149     .0       4,152   27866       2.1
Streams AQ: qmn coordinator             298   50.0       4,152   13933       4.3
SQL*Net message from client          85,164     .0       3,352      39   1,216.6
Streams AQ: waiting for time              1  100.0       1,202 #######       0.0
class slave wait                         13     .0           0       0       0.2
          -------------------------------------------------------------

You we can see that only with parallel execution, direct path reads comes in picture.