This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Jan 15, 2013 1:19 AM by Jonathan Lewis RSS

db file sequential read and direct path read

914789 Newbie
Currently Being Moderated
Hi,

Could someone please clear my doubts on 'db file sequential read' and 'direct path read'. And help me understand tkprof report correctly.
Kindly suggest whether my understanding for below scenario is correct.

We have a '11.2.0.1 version 2-node rac cluster + asm' production environment and its test environment which is a stand alone database.
The query is performing good in production compared to test database.
The table is having 254+ columns ( 264) with many lobs coulumns however lobs are not being selected in the query.
I read in metalink that 254+ column table has intra-row-chaining, thus causing 'db file sequential read' in full table scan.

Below is some detail about table which is similar in prod and test, block size is 8k:
TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK
------------------------------  ---------------  ---------------  ---------------
PROBSUMMARYM1                               0          17408          17407
What I understand from below tkprof in production environment for a particular session is:
1- The query resulted in 19378 disk reads and 145164 consistent reads.
2- Out of 19378 disk reads, 2425 disk reads resulted in 'db file sequential read' wait event.
Is this statement correct that remaining disk reads were 'db file sequential reads' but were real quick so didn't have wait event associated to it ?
3- There are 183 'direct path read' also. Is this because of order by clause in the query ?

SQL ID: 72tvt5h4402c9
Plan Hash: 1127048874
select "NUMBER" num 
from
 smprd.probsummarym1 where flag ='f' and affected_item = 'PAUSRWVP39486' 
  order by num asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.53       4.88      19378     145164          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.53       4.88      19378     145164          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=145164 pr=19378 pw=0 time=0 us cost=4411 size=24 card=2)
      0   TABLE ACCESS FULL PROBSUMMARYM1 (cr=145164 pr=19378 pw=0 time=0 us cost=4410 size=24 card=2)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  ges message buffer allocation                   3        0.00          0.00
  enq: KO - fast object checkpoint                2        0.00          0.00
  reliable message                                1        0.00          0.00
  KJC: Wait for msg sends to complete             1        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  kfk: async disk IO                            274        0.00          0.00
  direct path read                              183        0.01          0.72
  db file sequential read                      2425        0.05          3.71
  SQL*Net message from client                     1        2.45          2.45
The same query when ran in non-rac non-asm stand alone test database gave below tkprof.
Does this mean that:
1- Here also the reads happen via ' db file sequential read', but they were so quick that didn't resulted in wait event ?
2- 'Direct path read' is because of order by clause in the query.
SQL ID: 72tvt5h4402c9
Plan Hash: 1127048874
select "NUMBER" num 
from
 smprd.probsummarym1 where flag ='f' and affected_item = 'PAUSRWVP39486' 
  order by num asc


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.06          0          0          0           0
Fetch        1      0.10       0.11      17154      17298          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.10       0.18      17154      17298          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=17298 pr=17154 pw=0 time=0 us cost=4694 size=12 card=1)
      0   TABLE ACCESS FULL PROBSUMMARYM1 (cr=17298 pr=17154 pw=0 time=0 us cost=4693 size=12 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         3        0.00          0.00
  direct path read                              149        0.00          0.03
  SQL*Net message from client                     1        2.29          2.29
For the trace files in Production and Test database, I'm seeing that 'direct path read' is against the same datafile which has table stored.
Then how come this 'direct path read' is because of order by clause in the query and would have been in sort area size or pga ?
Or this direct path read fetched the actual data from disk to PGA , and 'db file sequential read' did not fetched the data ?
What I know is 'direct path read' is wait event when data is put from disk to PGA or when sort segment or temp tablespace is used.

Below is sample trace file in Production database:
*** 2013-01-04 13:49:15.109
WAIT #1: nam='SQL*Net message from client' ela= 11258483 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1357278555109496
CLOSE #1:c=0,e=9,dep=0,type=1,tim=1357278555109622
=====================
PARSING IN CURSOR #1 len=113 dep=0 uid=0 oct=3 lid=0 tim=1357278555109766 hv=138414473 ad='cfc02ab8' sqlid='72tvt5h4402c9'
select "NUMBER" num from smprd.probsummarym1 where flag ='f' and affected_item = 'PAUSRWVP39486' order by num asc
END OF STMT
PARSE #1:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1127048874,tim=1357278555109765
EXEC #1:c=0,e=135,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1127048874,tim=1357278555109994
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1357278555110053
WAIT #1: nam='ges message buffer allocation' ela= 3 pool=0 request=1 allocated=0 obj#=-1 tim=1357278555111630
WAIT #1: nam='enq: KO - fast object checkpoint' ela= 370 name|mode=1263468550 2=65610 0=1 obj#=-1 tim=1357278555112098
WAIT #1: nam='reliable message' ela= 1509 channel context=3691837552 channel handle=3724365720 broadcast message=3692890960 obj#=-1 tim=1357278555113975
WAIT #1: nam='ges message buffer allocation' ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1357278555114051
WAIT #1: nam='enq: KO - fast object checkpoint' ela= 364 name|mode=1263468550 2=65610 0=1 obj#=-1 tim=1357278555114464
WAIT #1: nam='KJC: Wait for msg sends to complete' ela= 9 msg=3686348728 dest|rcvr=65536 mtype=8 obj#=-1 tim=1357278555114516
WAIT #1: nam='ges message buffer allocation' ela= 2 pool=0 request=1 allocated=0 obj#=-1 tim=1357278555114680
WAIT #1: nam='Disk file operations I/O' ela= 562 FileOperation=2 fileno=6 filetype=2 obj#=85520 tim=1357278555115710
WAIT #1: nam='kfk: async disk IO' ela= 5 count=1 intr=0 timeout=4294967295 obj#=85520 tim=1357278555117332

*** 2013-01-04 13:49:15.123
WAIT #1: nam='direct path read' ela= 6243 file number=6 first dba=11051 block cnt=5 obj#=85520 tim=1357278555123628
WAIT #1: nam='db file sequential read' ela= 195 file#=6 block#=156863 blocks=1 obj#=85520 tim=1357278555123968
WAIT #1: nam='db file sequential read' ela= 149 file#=6 block#=156804 blocks=1 obj#=85520 tim=1357278555124216
WAIT #1: nam='db file sequential read' ela= 155 file#=6 block#=156816 blocks=1 obj#=85520 tim=1357278555124430
WAIT #1: nam='db file sequential read' ela= 4826 file#=6 block#=156816 blocks=1 obj#=85520 tim=1357278555129317
WAIT #1: nam='db file sequential read' ela= 987 file#=6 block#=156888 blocks=1 obj#=85520 tim=1357278555130427
WAIT #1: nam='db file sequential read' ela= 3891 file#=6 block#=156888 blocks=1 obj#=85520 tim=1357278555134394
WAIT #1: nam='db file sequential read' ela= 155 file#=6 block#=156912 blocks=1 obj#=85520 tim=1357278555134645
WAIT #1: nam='db file sequential read' ela= 145 file#=6 block#=156920 blocks=1 obj#=85520 tim=1357278555134866
WAIT #1: nam='db file sequential read' ela= 234 file#=6 block#=156898 blocks=1 obj#=85520 tim=1357278555135332
WAIT #1: nam='db file sequential read' ela= 204 file#=6 block#=156907 blocks=1 obj#=85520 tim=1357278555135666
WAIT #1: nam='kfk: async disk IO' ela= 4 count=1 intr=0 timeout=4294967295 obj#=85520 tim=1357278555135850
WAIT #1: nam='direct path read' ela= 6894 file number=6 first dba=72073 block cnt=15 obj#=85520 tim=1357278555142774
WAIT #1: nam='db file sequential read' ela= 4642 file#=6 block#=156840 blocks=1 obj#=85520 tim=1357278555147574
WAIT #1: nam='db file sequential read' ela= 162 file#=6 block#=156853 blocks=1 obj#=85520 tim=1357278555147859
WAIT #1: nam='db file sequential read' ela= 6469 file#=6 block#=156806 blocks=1 obj#=85520 tim=1357278555154407
WAIT #1: nam='db file sequential read' ela= 182 file#=6 block#=156826 blocks=1 obj#=85520 tim=1357278555154660
WAIT #1: nam='db file sequential read' ela= 147 file#=6 block#=156830 blocks=1 obj#=85520 tim=1357278555154873
WAIT #1: nam='db file sequential read' ela= 145 file#=6 block#=156878 blocks=1 obj#=85520 tim=135727855515
Below is the trace file for test database:
*** 2013-01-04 13:46:11.354
WAIT #1: nam='SQL*Net message from client' ela= 10384792 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1357278371354075
CLOSE #1:c=0,e=3,dep=0,type=3,tim=1357278371354152
=====================
PARSING IN CURSOR #1 len=113 dep=0 uid=0 oct=3 lid=0 tim=1357278371363427 hv=138414473 ad='c7bd8d00' sqlid='72tvt5h4402c9'
select "NUMBER" num from smprd.probsummarym1 where flag ='f' and affected_item = 'PAUSRWVP39486' order by num asc
END OF STMT
PARSE #1:c=0,e=9251,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1127048874,tim=1357278371363426
EXEC #1:c=0,e=63178,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1127048874,tim=1357278371426691
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1357278371426766
WAIT #1: nam='Disk file operations I/O' ela= 1133 FileOperation=2 fileno=55 filetype=2 obj#=93574 tim=1357278371428069
WAIT #1: nam='db file sequential read' ela= 51 file#=55 block#=460234 blocks=1 obj#=93574 tim=1357278371428158
WAIT #1: nam='direct path read' ela= 31 file number=55 first dba=460235 block cnt=5 obj#=93574 tim=1357278371428956
WAIT #1: nam='direct path read' ela= 47 file number=55 first dba=136288 block cnt=8 obj#=93574 tim=1357278371429099
WAIT #1: nam='direct path read' ela= 80 file number=55 first dba=136297 block cnt=15 obj#=93574 tim=1357278371438529
WAIT #1: nam='direct path read' ela= 62 file number=55 first dba=136849 block cnt=15 obj#=93574 tim=1357278371438653
WAIT #1: nam='direct path read' ela= 17 file number=55 first dba=136881 block cnt=7 obj#=93574 tim=1357278371438750
WAIT #1: nam='direct path read' ela= 35 file number=55 first dba=136896 block cnt=8 obj#=93574 tim=1357278371438855
WAIT #1: nam='direct path read' ela= 22 file number=55 first dba=136913 block cnt=7 obj#=93574 tim=1357278371438936
WAIT #1: nam='direct path read' ela= 19 file number=55 first dba=137120 block cnt=8 obj#=93574 tim=1357278371439029
WAIT #1: nam='direct path read' ela= 36 file number=55 first dba=137145 block cnt=7 obj#=93574 tim=1357278371439114
WAIT #1: nam='direct path read' ela= 18 file number=55 first dba=137192 block cnt=8 obj#=93574 tim=1357278371439193
WAIT #1: nam='direct path read' ela= 16 file number=55 first dba=137201 block cnt=7 obj#=93574 tim=1357278371439252
WAIT #1: nam='direct path read' ela= 17 file number=55 first dba=137600 block cnt=8 obj#=93574 tim=1357278371439313
WAIT #1: nam='direct path read' ela= 15 file number=55 first dba=137625 block cnt=7 obj#=93574 tim=1357278371439369
WAIT #1: nam='direct path read' ela= 22 file number=55 first dba=137640 block cnt=8 obj#=93574 tim=1357278371439435
WAIT #1: nam='direct path read' ela= 702 file number=55 first dba=801026 block cnt=126 obj#=93574 tim=1357278371440188
WAIT #1: nam='direct path read' ela= 1511 file number=55 first dba=801154 block cnt=126 obj#=93574 tim=1357278371441763
WAIT #1: nam='direct path read' ela= 263 file number=55 first dba=801282 block cnt=126 obj#=93574 tim=1357278371442547
WAIT #1: nam='direct path read' ela= 259 file number=55 first dba=801410 block cnt=126 obj#=93574 tim=1357278371443315
WAIT #1: nam='direct path read' ela= 294 file number=55 first dba=801538 block cnt=126 obj#=93574 tim=1357278371444099
WAIT #1: nam='direct path read' ela= 247 file number=55 first dba=801666 block cnt=126 obj#=93574 tim=1357278371444843
WAIT #1: nam='direct path read' ela= 266 file number=55 first dba=801794 block cnt=126 obj#=93574 tim=1357278371445619
Thanks & Rgds,
Vijay
  • 1. Re: db file sequential read and direct path read
    damorgan Oracle ACE Director
    Currently Being Moderated
    I started to read what you posted ... and stopped ... because there are a couple of things seriously wrong that should be addressed long before you start worrying about sequential vs direct path reads.

    1. 11.2.0.1? Why? There are numerous patches that have been released and the current version is 11.2.0.3. Unless this system is a toy, and that appears to not be the case, you should maintain a rigorous patching schedule.

    2. 254+ columns? A complete non-starter. I don't care what Oracle says about supporting large number of columns in a table any table with more than 50 columns is suspect ... any table with more than 100 columns is almost impossible to justify except in a DSS/DW environment. Any table with more than 254 columns needs to be fixed.

    Attend to the important issues and then deal with how you read it after that.
  • 2. Re: db file sequential read and direct path read
    914789 Newbie
    Currently Being Moderated
    Hi Damorgan,

    Thanks for your reply.
    1- The application is a HP product and vendor has yet to certify it for version higher that 11.2.0.1. Hence not been upgraded yet.
    2- Since it's a product, application team has no role in creating the tables, it comes built in.

    Rgds,
    Vijay
  • 3. Re: db file sequential read and direct path read
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    3- There are 183 'direct path read' also. Is this because of order by clause in the query ?
    No, direcy path read is not different then DB FILE SCATTERED READ (Full table scan). only difference is that while doing a full table scan, oracle decides whether it should "flood" the buffer cache by putting the result in buffer cache, or should it bypass the buffer cache and read data directly to PGA. If it decieds to bypass the buffer cache (for the sake of overall performance of the database), you will see DIRECT PATH READ wait event (still it is doing db file scattered read/full table scan).

    db file sequential read is actually because of your order by caluse. If you paster here execution plan of queries on both databases, you will see that.

    Salman

    Edited by: Salman Qureshi on Jan 7, 2013 9:44 AM
  • 4. Re: db file sequential read and direct path read
    damorgan Oracle ACE Director
    Currently Being Moderated
    Any system not certified for patching to stay current is a system you should dump as fast as you can.

    You don't name the system but if it is sold by HP then your management needs to give them an ultimatum ... either support 11.2.0.3 ... or lose any future revenue. But, if you don't mind, please tell us what system/software so others can avoid it like the plague.

    I would need to know the name of the application to advise further as it appears you can not restructure the tables. But if the vendor designed these tables count it as one more reason to look for a replacement.
  • 5. Re: db file sequential read and direct path read
    871018 Explorer
    Currently Being Moderated
    911786 wrote:
    Hi,
    Hi
    2- Out of 19378 disk reads, 2425 disk reads resulted in 'db file sequential read' wait event.
    Is this statement correct that remaining disk reads were 'db file sequential reads' but were real quick so didn't have wait event associated to it ?
    Not, it is not correct. Remaining disk reads were done by 'direct path read'. Every 'direct path read' can read N blocks. Look at your excerpt from trace file, "block cnt" (this is p3 from wait event) in the 'direct path read' event.
    3- There are 183 'direct path read' also. Is this because of order by clause in the query ?
    "Order by clause" does not lead "direct path read" events. Even if sort area size is not enough, it would be "direct path write *temp*"/"direct path read *temp*". Also notice that all of physical reads (pr=) were done on Full Table Scan step:
    Rows     Row Source Operation
    -------  ---------------------------------------------------
    0  SORT ORDER BY (cr=145164 pr=19378 pw=0 time=0 us cost=4411 size=24 card=2)
    0   TABLE ACCESS FULL PROBSUMMARYM1 (cr=145164 pr=19378 pw=0 time=0 us cost=4410 size=24 card=2)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
    0  SORT ORDER BY (cr=17298 pr=17154 pw=0 time=0 us cost=4694 size=12 card=1)
    0   TABLE ACCESS FULL PROBSUMMARYM1 (cr=17298 pr=17154 pw=0 time=0 us cost=4693 size=12 card=1)
    The nature of these "direct path read" events is serial direct path read - from 11g Oracle decides to do serial full scan by "cached" "db file scattered read" or bypassing cache by "direct path read" automatically (_serial_direct_read=auto), based on size of segment, number of dirty buffers, etc.
  • 6. Re: db file sequential read and direct path read
    Mohamed Houri Pro
    Currently Being Moderated
    Hi,

    You wrote
    Below is the trace file for test database:
    
    WAIT #1: nam='db file sequential read' ela= 51 file#=55 block#=460234 blocks=1 obj#=93574 tim=1357278371428158
    WAIT #1: nam='direct path read' ela= 31 file number=55 first dba=460235 block cnt=5 obj#=93574 tim=1357278371428956
    WAIT #1: nam='direct path read' ela= 47 file number=55 first dba=136288 block cnt=8 obj#=93574 tim=1357278371429099
    WAIT #1: nam='direct path read' ela= 80 file number=55 first dba=136297 block cnt=15 obj#=93574 tim=1357278371438529
    WAIT #1: nam='direct path read' ela= 62 file number=55 first dba=136849 block cnt=15 obj#=93574 tim=1357278371438653
    WAIT #1: nam='direct path read' ela= 17 file number=55 first dba=136881 block cnt=7 obj#=93574 tim=1357278371438750
    WAIT #1: nam='direct path read' ela= 35 file number=55 first dba=136896 block cnt=8 obj#=93574 tim=1357278371438855
    WAIT #1: nam='direct path read' ela= 22 file number=55 first dba=136913 block cnt=7 obj#=93574 tim=1357278371438936
    WAIT #1: nam='direct path read' ela= 19 file number=55 first dba=137120 block cnt=8 obj#=93574 tim=1357278371439029
    WAIT #1: nam='direct path read' ela= 36 file number=55 first dba=137145 block cnt=7 obj#=93574 tim=1357278371439114
    WAIT #1: nam='direct path read' ela= 18 file number=55 first dba=137192 block cnt=8 obj#=93574 tim=1357278371439193
    WAIT #1: nam='direct path read' ela= 16 file number=55 first dba=137201 block cnt=7 obj#=93574 tim=1357278371439252
    WAIT #1: nam='direct path read' ela= 17 file number=55 first dba=137600 block cnt=8 obj#=93574 tim=1357278371439313
    WAIT #1: nam='direct path read' ela= 15 file number=55 first dba=137625 block cnt=7 obj#=93574 tim=1357278371439369
    Normally before making a decision wether to direct path read or db file scatter read a segment (table, partition of a table, or index), Oracle engine (not the CBO) will first read the table segment header to see how many blocks are below the high water mark. This read is done via a db file sequential read
    WAIT #1: nam='db file sequential read' ela= 51 file#=55 block#=460234 blocks=1 obj#=93574 tim=1357278371428158
    Once this information is put in the cache, Oracle will not re-read again the segment header to decide wether is has to direct path read the table or not.

    The question however here is why in the production trace file is showing several db file sequential read on different blocks before direct path reading?
    WAIT #1: nam='db file sequential read' ela= 195 file#=6 block#=156863 blocks=1 obj#=85520 tim=1357278555123968
    WAIT #1: nam='db file sequential read' ela= 149 file#=6 block#=156804 blocks=1 obj#=85520 tim=1357278555124216
    WAIT #1: nam='db file sequential read' ela= 155 file#=6 block#=156816 blocks=1 obj#=85520 tim=1357278555124430
    WAIT #1: nam='db file sequential read' ela= 4826 file#=6 block#=156816 blocks=1 obj#=85520 tim=1357278555129317
    WAIT #1: nam='db file sequential read' ela= 987 file#=6 block#=156888 blocks=1 obj#=85520 tim=1357278555130427
    WAIT #1: nam='db file sequential read' ela= 3891 file#=6 block#=156888 blocks=1 obj#=85520 tim=1357278555134394
    WAIT #1: nam='db file sequential read' ela= 155 file#=6 block#=156912 blocks=1 obj#=85520 tim=1357278555134645
    WAIT #1: nam='db file sequential read' ela= 145 file#=6 block#=156920 blocks=1 obj#=85520 tim=1357278555134866
    WAIT #1: nam='db file sequential read' ela= 234 file#=6 block#=156898 blocks=1 obj#=85520 tim=1357278555135332
    WAIT #1: nam='db file sequential read' ela= 204 file#=6 block#=156907 blocks=1 obj#=85520 tim=1357278555135666
    WAIT #1: nam='kfk: async disk IO' ela= 4 count=1 intr=0 timeout=4294967295 obj#=85520 tim=1357278555135850
    WAIT #1: nam='direct path read' ela= 6894 file number=6 first dba=72073 block cnt=15 obj#=85520 tim=1357278555142774
    This is the question one has to clarify first.

    By the way, as far as performance is concerned you should understand that for 5 seconds of response time, you spent 2.45 seconds waiting for the client to ask for it ( SQL*Net message from client) !!!
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       1        0.00          0.00
      ges message buffer allocation                   3        0.00          0.00
      enq: KO - fast object checkpoint                2        0.00          0.00
      reliable message                                1        0.00          0.00
      KJC: Wait for msg sends to complete             1        0.00          0.00
      Disk file operations I/O                        1        0.00          0.00
      kfk: async disk IO                            274        0.00          0.00
      direct path read                              183        0.01          0.72
      db file sequential read                      2425        0.05          3.71
      SQL*Net message from client                     1        2.45          2.45
    http://hourim.wordpress.com/2012/09/14/tuning-by-tkprof-a-case-study/

    Best regards

    Mohamed Houri
    www.hourim.wordpress.com
  • 7. Re: db file sequential read and direct path read
    871018 Explorer
    Currently Being Moderated
    >
    Not, it is not correct. Remaining disk reads were done by 'direct path read'. Every 'direct path read' can read N blocks. Look at your excerpt from trace file, "block cnt" (this is p3 from wait event) in the 'direct path read' event.
    I should add: every 'direct path read' request or mechanic can read N blocks, but not all of them can be accounted as wait events 'direct path read' if it works in asynchronous mode.
    I am preparing some stuff about it.

    Alexander Anokhin
    http://alexanderanokhin.wordpress.com/
  • 8. Re: db file sequential read and direct path read
    Mich Talebzadeh Explorer
    Currently Being Moderated
    I agree with that statement. Any multi-block read whether it is "db file scattered read" or "Direct path read" is effectively pre-fetching. IMO, this implies that before the execution engine gets there, data is already cached. In other words, it is more like Logical I/O than Physical reads.

    If we look at the output from trace we see
    WAIT #1: nam='direct path read' ela= 1511 file number=55 first dba=801154 block cnt=126 obj#=93574 tim=1357278371441763
    WAIT #1: nam='direct path read' ela= 263 file number=55 first dba=801282 block cnt=126 obj#=93574 tim=1357278371442547
    WAIT #1: nam='direct path read' ela= 259 file number=55 first dba=801410 block cnt=126 obj#=93574 tim=1357278371443315
    Well block cnt are different for different calls. However, the aggregate shows that in production environment there were 2,425 waits for direct path reads that took 3.71 sec to complete. I gather the server wide parameter settings for db_file_multiblock_read_count was 128 so on average each direct path physical I/O took 3.71 sec/2425 or 1529 microseconds. It is nott clear to me why the first 126 blocks took 1511 microseconds to complete whereas the next two 126 blocks took 260 microseconds to complete unless there is some form of pre-fetching here.

    Regards,

    Mich Talebzadeh
  • 9. Re: db file sequential read and direct path read
    914789 Newbie
    Currently Being Moderated
    Hi Damorgan,

    Below are the components of application, each one has its individual datatase.
    The problem database belongs to "Service Manager", it shares the same binary with other databases.
    Unless all these components are certified for 11.2.0.3, individual database cann't be patched.
    Application team says that any changes done to database version or its objects might breach support contract with vendor.


    Universal Configuration Management Database
    Unified Topology Manager
    Service Manager
    Portal
    Identity Management , Application Integration


    Rgds,
    Sanjay
  • 10. Re: db file sequential read and direct path read
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    911786 wrote:
    Direct path reads can take place on serial tablescans in your version of Oracle, but if you have chained rows in the table then Oracle can read the start of the row in the direct path read, but has to do a single block read to cache (db file sequential read) to get the next part of the row.

    It's possible that your production system has a lot of chained rows while your test system doesn't. As a corroborative (though not convincing) indicator of this you might note that if you take (disk reads - db file sequential reads) - which could get you close to the total of blocks by direct path read - the numbers are very similar.

    I'm not 100% convinced that this is the answer for the difference in behaviour, but it's worth checking. If you can force and indexed access path into the table doing something like "select /*+ index({use the pk}) */ max(last_column_in_table) from table" and check the number of "table fetch continued rows" this might be close to the number of db file sequential reads you've got. (There are other options for counting chained rows that might be quicker).

    Regards
    Jonathan Lewis
  • 11. Re: db file sequential read and direct path read
    871018 Explorer
    Currently Being Moderated
    911786 wrote:
    The problem database belongs to "Service Manager", it shares the same binary with other databases.
    What exactly is the problem? The statement ran in ~5 seconds?
    Almost all of this time is from 2425 of "db file sequential reads".
    Notice huge difference in number of logical reads in both cases 145164 vs 17298.
    It looks like that the table in the first case is actively modified during the full scan. It would explain why the number of logical reads much more that physical reads and explain high number of "db file sequential reads".
  • 12. Re: db file sequential read and direct path read
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Alexander Anokhin wrote:
    It looks like that the table in the first case is actively modified during the full scan. It would explain why the number of logical reads much more that physical reads and explain high number of "db file sequential reads".
    Alexander,

    I think that was my first thought, which prompted me to check if the db file sequential reads likely to be undo blocks being read - but the file number for both types of read is the same, so the answer is no.

    Regards
    Jonathan Lewis
  • 13. Re: db file sequential read and direct path read
    871018 Explorer
    Currently Being Moderated
    Jonathan Lewis wrote:
    Alexander Anokhin wrote:
    It looks like that the table in the first case is actively modified during the full scan. It would explain why the number of logical reads much more that physical reads and explain high number of "db file sequential reads".
    Alexander,

    I think that was my first thought, which prompted me to check if the db file sequential reads likely to be undo blocks being read - but the file number for both types of read is the same, so the answer is no.
    Hi Jonathan,
    Yes, exactly, you are absolutely right. I have missed it.
    I agree that these reads are chained rows.
  • 14. Re: db file sequential read and direct path read
    Mohamed Houri Pro
    Currently Being Moderated
    Jonathan,
    I think that was my first thought, which prompted me to check if the db file sequential reads likely to be undo blocks being read - but the file number for both types 
    of read is the same, so the answer is no
    That's very nice. I learned something new today: undo blocks can also be read via db file sequential read.

    And does the db file sequential read on undo blocks mean an access via index too? If yes then what kind of indexes are they????

    Best Regards
    Mohamed Houri
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points