1 2 Previous Next 21 Replies Latest reply: Jan 15, 2013 3:19 AM by Jonathan Lewis RSS

    db file sequential read and direct path read

    914789
      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
          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
            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
              Salman Qureshi
              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
                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
                  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
                    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
                      >
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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