1 2 Previous Next 16 Replies Latest reply: Apr 24, 2013 8:08 AM by Paul Horth RSS

    db file sequential read

    00125
      Hi,


      What is db file sequential read? In which case it happens and how can we resolve it in developing and also in DBA aspects.

      version:-10.2.0.1.0

      Thanks in advance.

      Edited by: 00125 on Apr 24, 2013 1:10 AM
        • 1. Re: db file sequential read
          Manik
          http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::%3AP11_QUESTION_ID:6265095774206

          Cheers,
          Manik.
          • 2. Re: db file sequential read
            00125
            Hi,

            How can we avoid such issue while developing.
            • 3. Re: db file sequential read
              Paul  Horth
              00125 wrote:
              Hi,

              How can we avoid such issue while developing.
              You make it sound as if it is a problem to avoid, why do you think it is a problem?

              It usually indicates indexed access to your data why would you want to avoid that?

              What issues are you talking about?
              • 4. Re: db file sequential read
                00125
                Hi,

                While data loading,such issue is happening "DB file sequential read". If this happens due to indexing then what shall we want to do.It takes too CPU cost and also

                waits.

                Edited by: 00125 on Apr 24, 2013 2:08 AM
                • 5. Re: db file sequential read
                  Paul  Horth
                  00125 wrote:
                  Hi,

                  While data loading,such issue is happening "DB file sequential read". If this happens due to indexing then what shall we want to do.It takes too CPU cost and also

                  waits.

                  Edited by: 00125 on Apr 24, 2013 2:08 AM
                  I think you are saying you are loading a large amount of data into a table and it is slow?

                  If it is a batch load and no one else wants to use that table at the time, you can remove the indexes, load the data and then add the indexes again.

                  Alternatively, you could look to see whether all the indexes on your table are actually needed and remove those that aren't.

                  How many indexes does the table have?
                  • 6. Re: db file sequential read
                    00125
                    Hi,

                    While executing query in respect of index column db file sequential read shows larger value . when it going with full table scan it's fine..
                    • 7. Re: db file sequential read
                      Paul  Horth
                      00125 wrote:
                      Hi,

                      While executing query in respect of index column db file sequential read shows larger value . when it going with full table scan it's fine..
                      What query? you mentioned loading data. Please read {message:id=9360002} and give the required information.

                      Detail the actual problem you have. Why do you think db file sequential read is the cause of that problem? Give details.

                      What about the answers to my previous questions.

                      The more information you can give, the more we can help.
                      • 8. Re: db file sequential read
                        Dom Brooks
                        While executing query in respect of index column db file sequential read shows larger value . when it going with full table scan it's fine.
                        This is normal.

                        Physical IO is normal.

                        "db file sequential read" is a single block physical io read, often associated with an index lookup.
                        A full table scan would normally do multi block physical reads via the wait "db file scattered read" or "direct path read".

                        To a certain extent, physical IO is beyond your control.
                        So if in your SQL tuning attempts you want to reduce IO, you should focus on logical IO.
                        [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]SQL Tuning Thread

                        In general, SQL tuning should target accurate optimizer estimates.
                        If the estimates are accurate, then generally the plan is good.
                        If the estimates are accurate but you're still not happy with the work being done, then you need to look at optimising physical access to the data, e.g indexes, MVs, table redesign, partitioning, etc.
                        • 9. Re: db file sequential read
                          00125
                          Hi Dom Brooks ,

                          Thanks for your help.Still am having lot of doubt.The problem is:- In load test ,db file sequential cost is in top ,it's wait looking so huge.

                          I need to know what are the steps to carry out from developing and also in DBA to reduce it. By reducing I/O size shall we solve this problem???

                          Thanks in advance.

                          Edited by: 00125 on Apr 24, 2013 3:23 AM
                          • 10. Re: db file sequential read
                            Paul  Horth
                            00125 wrote:
                            Hi Dom Brooks ,

                            Thanks for your help.Still am having lot of doubt.The problem is:- In load test ,db file sequential cost is in top ,it's wait looking so huge.

                            I need to know what are the steps to carry out from developing and also in DBA to reduce it.

                            Thanks in advance.
                            You keep asking the same question, ignoring the answers we are giving and not answering the questions we are asking, so as to help you.

                            Please make the effort to give details as you have been asked.
                            • 11. Re: db file sequential read
                              00125
                              Hi,

                              For your question I have don't have clear answer for this I won't reply for it and so.In testbed,test team run load test for 3 times.We use lot of queries for fetching

                              data by using index column.Here db file sequential read wait is large.question whether it is good ?? if not then how to resolve it from developing side

                              and DBA side?? Why it happening ??Whether it happens it only due to DB or application side??? Whether there is any need of query tuning for such issue or to leave it???


                              Thanks in advance.

                              Edited by: 00125 on Apr 24, 2013 4:26 AM

                              Edited by: 00125 on Apr 24, 2013 4:27 AM
                              • 12. Re: db file sequential read
                                Paul  Horth
                                00125 wrote:
                                Hi,

                                For your question I have don't have clear answer for this I won't reply for it and so.In testbed,test team run load test for 3 times.We use lot of queries for fetching

                                data by using index column.Here db file sequential read wait is large.question whether it is good ?? if not then how to resolve it from developing side

                                and DBA side?? Why it happening ??Whether it happens it only due to DB or application side??? Whether there is any need of query tuning for such issue or to leave it???


                                Thanks in advance.

                                Edited by: 00125 on Apr 24, 2013 4:26 AM

                                Edited by: 00125 on Apr 24, 2013 4:27 AM
                                Just sounds like you are doing indexed access to the table: hence you are getting a lot of db file sequential reads. That is normal.

                                The question is, should you use indexes or full table scan? This depends on various factors including percentage of data you are retrieving out of the table.

                                In general if you gather system statistics and statistics for your tables/indexes, Oracle will choose the best path as long as you don't override it with hints.

                                Again what issue do you actually have? What is the business complaining about? Is the query too slow?

                                If it is, post query and explain plan (see {message:id=9360003}
                                • 13. Re: db file sequential read
                                  00125
                                  Hi paul,


                                  Thanks for your help..I read some blogs that says that by improving I/O cost by tuning query will reduce db file sequential read waits low.

                                  In testbed,db file sequential read waits occurs in range scan query..By this waits query becomes slow.How to reduce I/O cost in query????

                                  select x,y,z from sample where x=:a
                                  In this table x and y are composite primary key.It goes Index range scan.
                                  Execution Plan
                                  ----------------------------------------------------------
                                  Plan hash value: 2394910998
                                  
                                  -------------------------------------------------------------------------
                                  | Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                                  -------------------------------------------------------------------------
                                  |   0 | SELECT STATEMENT |      |     1 |    26 |     3233  (1)| 00:00:46 |
                                  |*  1 |  INDEX RANGE SCAN| A    |     1 |    26 |     3233   (1)| 00:00:46 |
                                  -------------------------------------------------------------------------
                                  Thanks in advance
                                  • 14. Re: db file sequential read
                                    Paul  Horth
                                    00125 wrote:
                                    Hi paul,


                                    Thanks for your help..I read some blogs that says that by improving I/O cost by tuning query will reduce db file sequential read waits low.

                                    In testbed,db file sequential read waits occurs in range scan query..By this waits query becomes slow.How to reduce I/O cost in query????

                                    select x,y,z from sample where x=:a
                                    In this table x and y are composite primary key.It goes Index range scan.
                                    Execution Plan
                                    ----------------------------------------------------------
                                    Plan hash value: 2394910998
                                    
                                    -------------------------------------------------------------------------
                                    | Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                                    -------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT |      |     1 |    26 |     3233  (1)| 00:00:46 |
                                    |*  1 |  INDEX RANGE SCAN| A    |     1 |    26 |     3233   (1)| 00:00:46 |
                                    -------------------------------------------------------------------------
                                    Thanks in advance
                                    You say your key is composite, therefore as you are only using x=:a (and x is first part of key), a range scan will be done.

                                    You execution plan says only one row is retrieved. If that is correct, then it is the best plan and the query should not take any time at all.

                                    How long does it take? How many rows are actually retrieved? How many rows are in the table?
                                    1 2 Previous Next