1 2 3 4 5 Previous Next 61 Replies Latest reply: Sep 17, 2010 4:49 PM by user503699 Go to original post RSS
      • 45. Re: explaining the explain plan
        695836
        Great!!
        I shall also test.
        • 46. Re: explaining the explain plan
          Jonathan Lewis
          user503699 wrote:
          ----------------------------------------------------------------------------------------------------
          | Id  | Operation                     | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
          ----------------------------------------------------------------------------------------------------
          |*  1 |  TABLE ACCESS BY INDEX ROWID  | T1          |      1 |      1 |      7 |00:00:00.01 |      22 |
          |   2 |   NESTED LOOPS                |             |      1 |      1 |     15 |00:00:00.01 |      18 |
          |   3 |    TABLE ACCESS BY INDEX ROWID| T           |      1 |      1 |      7 |00:00:00.01 |       8 |
          |*  4 |     INDEX RANGE SCAN          | T_OWNER_IDX |      1 |      1 |      7 |00:00:00.01 |       4 |
          |*  5 |    INDEX RANGE SCAN           | T1_IDX      |      7 |      1 |      7 |00:00:00.01 |      10 |
          ----------------------------------------------------------------------------------------------------
          Thank you for producing a sample of starts/A-rows. This makes it easy to explain the mechanism.
          The sequence of activity is as follows:

          Line 4 - operates an index range scan once to generate a list of 7 rowids, and passes rowsource to line 3
          Line 3 - operates table prefetching once to fetch all the relevant table blocks in the minimum number of I/O requests to produce a list of rows
          at run time this could be a single "db file scattered read" if the table blocks are seen to be consecutive blocks in the table
          it might be a single "db file parallel read" if the table blocks are scattered widely across the table
          it might be a collection of scattered, paralllel and sequential reads.
          Line 2 takes the rowsource for line 3 and calls line 5 for each row from line 3
          Line 5 does an index range scan for each incoming row, and returns a list of related rowids
          Line 2 takes each list of rowids from line 5 and replicates the input row that many times, adding one rowid to the end of each copy
          when all the rows have been generated the entire rowset is passed up to line 1
          Line 1 now has an input that is a list of rows with some columns from table T and a rowid from T1, so it visits table T1 by rowid to add columns from t1 to the end of each row. (addendum) This operation is also table pre-fetching - and could use a mixture of sequential, scattered and parallel reads because at this point line 1 has all the rowids from line 2 that it will need to collect all the relevant data from table T.

          I am assuming that the number of STARTS is recorded accurately - but I suspect that the number of A-Rows in line 2 (nested loop) is wrong; I've written a note about this somewhere (possibly in my book) to the effect that line 2 is reporting (7 + 7 + 1 == rows from first child + rows from second child + 1) as its number of rows.


          In earlier versions of Oracle it was possible for the execution plan to supply this plan, but a run-time decision to use the old-style plan, based on a calculation of cache-efficiency run by CKPT every few seconds. I've also seen a note to the effect that the mechanism was only used when a block operation (e.g. a sort order by) called the nested loop. It looks as if things have become much more relaxed by 10.2.0.4

          Regards
          Jonathan Lewis

          Edited by: Jonathan Lewis on Sep 17, 2010 12:46 AM
          Added comment to end of description of line 1 processing.
          • 47. Re: explaining the explain plan
            user503699
            Jonathan,

            Thanks for the crystal clear explaination, something that I struggled to provide in this thread in one place.
            • 48. Re: explaining the explain plan
              695836
              Thankyou Sir for this explanation.

              This is what i was thinking but could not understand the concept of cartesian join happening here and how rows and rowid be cartesian joined.

              So this is not the case. Right?

              Every thing is clear now,the only question that is arising is :

              The input of line 1 is 15 ,7 rows from line 3 and the nested loop effect 7 rowids from line 5 +1 row?

              Line 1 processes only 7 rowids from line 3 and does not account for those 7 rows. RIght?

              Also,why can't oracle take the advantage of block prefetching using the same plan as it was prior to 9i?

              Many thanks.
              • 49. Re: explaining the explain plan
                user503699
                Jonathan Lewis wrote:
                Line 3 - operates table prefetching once to fetch all the relevant table blocks in the minimum number of I/O requests to produce a list of rows
                at run time this could be a single "db file scattered read" if the table blocks are seen to be consecutive blocks in the table
                it might be a single "db file parallel read" if the table blocks are scattered widely across the table
                it might be a collection of scattered, paralllel and sequential reads.
                Line 1 now has an input that is a list of rows with some columns from table T and a rowid from T1, so it visits table T1 by rowid to add columns from t1 to the end of each row. (addendum) This operation is also table pre-fetching - and could use a mixture of sequential, scattered and parallel reads because at this point line 1 has all the rowids from line 2 that it will need to collect all the relevant data from table T.
                Jonathan,

                I am trying to see how oracle manages to do scattered or parallel reads for Line 3 or Line 1, as you described above. But somehow I am always ending up with only "DB File Sequential Read" waits in the trace (I am flushing buffer cache before my test to ensure no table blocks are cached). I also tried Timur's test case which OP suggested in db file scatter read but no luck.
                Can you give any pointers that will help me to build a test case to see this?
                I am using 10.2.0.1 (or 10.2.0.4).
                • 50. Re: explaining the explain plan
                  Jonathan Lewis
                  user503699 wrote:

                  I am trying to see how oracle manages to do scattered or parallel reads for Line 3 or Line 1, as you described above. But somehow I am always ending up with only "DB File Sequential Read" waits in the trace (I am flushing buffer cache before my test to ensure no table blocks are cached). I also tried Timur's test case which OP suggested in db file scatter read but no luck.
                  Can you give any pointers that will help me to build a test case to see this?
                  I am using 10.2.0.1 (or 10.2.0.4).
                  I've just run up a test case in 11.1 and find the same - when using the nlj_prefetch nested loop I can only see "db file sequential read" waits on the first table - with "db file parallel read" waits on the second.

                  It's only when I switch to (the default) nlj_batching nested loop that I get "db file parallel read" waits on the first table (and then it's all "db file sequential read" waits on the second table).

                  I think my comment about prefetch at line 3 was probably wrong.
                  Of course, there's plenty of scope for increasingly complicated tests with more than two tables.


                  Regards
                  Jonathan Lewis
                  • 51. Re: explaining the explain plan
                    Timur Akhmadeev
                    Hi,
                    user503699 wrote:
                    I am trying to see how oracle manages to do scattered or parallel reads for Line 3 or Line 1, as you described above. But somehow I am always ending up with only "DB File Sequential Read" waits in the trace (I am flushing buffer cache before my test to ensure no table blocks are cached). I also tried Timur's test case which OP suggested in db file scatter read but no luck.
                    I tried to reproduce it on 10.2.0.5 - and wasn't able too. Instead of cache pre-warmup (indicated as 'db file scattered read' on what seems to be ROWID access), I'm seeing index pre-fetch via 'db file parallel read's:
                    select /*+ index(t t_indx) */ * 
                    from
                     t where id between 1000 and 10000
                    
                    
                    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      902      9.51      75.30      15831     902884          0      900100
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    total      904      9.52      75.31      15831     902884          0      900100
                    
                    Misses in library cache during parse: 1
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 41  
                    
                    Rows     Row Source Operation
                    -------  ---------------------------------------------------
                     900100  TABLE ACCESS BY INDEX ROWID T (cr=902884 pr=15831 pw=0 time=19993368 us)
                     900100   INDEX RANGE SCAN T_INDX (cr=2784 pr=1885 pw=0 time=9180703 us)(object id 82223)
                    
                    
                    Elapsed times include waiting on following events:
                      Event waited on                             Times   Max. Wait  Total Waited
                      ----------------------------------------   Waited  ----------  ------------
                      SQL*Net message to client                     902        0.00          0.00
                      db file sequential read                     13937        0.70         61.16
                      SQL*Net message from client                   902        1.20         76.39
                      db file parallel read                         902        0.24          7.95
                      SQL*Net more data to client                  1800        0.00          0.78
                    ********************************************************************************
                     
                    It's just an indication that the test case is highly dependent on many factors, probably on the buffer cache size/segment size/filesystemio_options/etc.
                    See also Doc ID 790721.1 for a small description of dbcache_pre_warm parameter.
                    • 52. Re: explaining the explain plan
                      user503699
                      Timur Akhmadeev wrote:
                      I tried to reproduce it on 10.2.0.5 - and wasn't able too. Instead of cache pre-warmup (indicated as 'db file scattered read' on what seems to be ROWID access), I'm seeing index pre-fetch via 'db file parallel read's:
                      Timur,

                      Many thanks for the followup. I understood the point that the behaviour is influenced by many factors as you suggested. But if I understand it correctly, the pre-fetch theroy is different to the separation of index and table access (introduced in 9i), right?
                      • 53. Re: explaining the explain plan
                        user503699
                        Jonathan Lewis wrote:
                        Of course, there's plenty of scope for increasingly complicated tests with more than two tables.
                        Jonathan,

                        Apologies if this is not relevant/correct but are you talking about "NLJ Batching", which was introduced in 11g, and produces a different version of the plan than the corresponding 10g version ?
                        • 54. Re: explaining the explain plan
                          695836
                          May be a index_ffs() hint would show db file scattered read.
                          • 55. Re: explaining the explain plan
                            user503699
                            Hashmi wrote:
                            May be a index_ffs() hint would show db file scattered read.
                            But Index Fast Full Scan is known to be ALWAYS doing multi-block reads (like full table scan). It is the case of index range scan, which is known to do a single-block reads as a standard, doing multi-block reads.
                            • 56. Re: explaining the explain plan
                              Jonathan Lewis
                              user503699 wrote:
                              Jonathan Lewis wrote:
                              Of course, there's plenty of scope for increasingly complicated tests with more than two tables.
                              Jonathan,

                              Apologies if this is not relevant/correct but are you talking about "NLJ Batching", which was introduced in 11g, and produces a different version of the plan than the corresponding 10g version ?
                              The example where I got pre-fetch on the first table in the nested loop join was when the plan was the 11g "nlj_batch" plan.

                              The comment about scope for increasingly complicated tests wasn't related to any specific path - rather to the fact that pre-fetching through indexed access may have many different variations that might only show up in certain circumstances. For example, the "db file parallel read" on the first table may be something that always happens on the NLJ_BATCHING path, but perhaps it also happens on nlj_prefetching if CKPT decides that the rolling average cache hit ratio over the last 30 seconds hits a certain target.

                              I know that I have at least two test scripts from my testing in 9i where I comment on the difficulty (and apparent randomness) of getting prefetching to work at all.

                              With reference to your comment about pre-fetch and separating the table access from the index access. Pre-fetching appears to describe the action of reading a block (from disc) before it is actually needed for a buffer access (so the simplest example is a tablescan "db file scattered read", where Oracle may read 128 blocks with a single disc request, but then count 127 of them as pre-fetched). Access paths that "separate" table and index access are simply mechanisms that may give Oracle the opportunity to implement prefetching if it seems to make sense - they may also give Oracle the opportunity to avoid work that should not have been necessary (though I don't think Oracle has any such paths in the current version).


                              Regards
                              Jonathan Lewis
                              • 57. Re: explaining the explain plan
                                user503699
                                Jonathan,

                                Not sure how this fits, but following is the description of "db file parallel read" wait event from 10gR2 documentation
                                http://www.oracle.com/pls/db102/to_URL?remark=ranked&urlname=http:%2F%2Fdownload.oracle.com%2Fdocs%2Fcd%2FB19306_01%2Fserver.102%2Fb14237%2Fwaitevents003.htm%23sthref3022
                                Parameter      Description
                                files              This indicates the number of files to which the session is reading
                                blocks              This indicates the total number of blocks to be read
                                requests      This indicates the total number of I/O requests, which will be the same as blocks
                                While it states that the wait can happen during "buffer prefetching" (as experienced by you and Timur above), the description of "requests" parameter appears (to me) to suggest that the I/O request will still be processing single-block, but multiple requests are carried out in parallel, which is the optimization.
                                Is my undersranding correct (or is completely off-the-track) ?
                                • 58. Re: explaining the explain plan
                                  Taral
                                  I may be wrong but it's slight difference

                                  You Said
                                  "single-block, but multiple requests are carried out in parallel"

                                  I think

                                  "Multiple block request are carried out in one single call". So, if you are not using async i/o then this is slow. But Jonathan can answer better
                                  • 59. Re: explaining the explain plan
                                    user503699
                                    Taral wrote:
                                    I may be wrong but it's slight difference

                                    You Said
                                    "single-block, but multiple requests are carried out in parallel"

                                    I think

                                    "Multiple block request are carried out in one single call". So, if you are not using async i/o then this is slow. But Jonathan can answer better
                                    Taral,

                                    I am sure I am becoming/have become pain in the neck now for OP and other posters by now ;)
                                    Coming back to the single vs. multiple blocks requests issue, I believed in the documentation which stated that "total no. of I/O requests will be same as blocks". By which, I derived that an I/O request could only be for a block and not multiple blocks. But instead of doing single block I/O requests, one at a time, oracle is able to initiate many such requests, in parallel, and wait for all of them to complete. Any waits incurred in reading blocks from disk (when needed) during this mechanism (which is called block prefetching), will be recorded as "DB File Parallel Read". Although, I admit that I don't know whether oracle will report such block reads under "physical read total multi block requests" or "physical reads prefetch warmup" or some other statistic.
                                    The only reason to mention this was that I was not able to witness the scattered reads that Jonathan described earlier.
                                    Line 1 now has an input that is a list of rows with some columns from table T and a rowid from T1, so it visits table T1 by rowid to add columns from t1 to the end of each row. (addendum) This operation is also table pre-fetching - and could use a mixture of sequential, scattered and parallel reads because at this point line 1 has all the rowids from line 2 that it will need to collect all the relevant data from table T
                                    p.s. I guess I am pretty much running the risk of people on this thread starting to ignore me now ;)