DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: db file sequential read

  1. #1
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174

    db file sequential read

    I'm looking into a select statement to see how I can improve it. I traced the execution of the single select statement using 10046 trace and used tkprof on it. Here's the output:

    Code:
    SELECT  *
    FROM NGSDMS60.NGTAGS_VIEW T1
    WHERE NGPROJGUID='6A018CB9-C15B-443b-8487-5C016EB8251B'
    AND  (((UPPER(NGUSRFLD2)   =  ('ROARK'))
    AND  (UPPER(NGUSRFLD19)   =  ('BUILTIN'))))
    ORDER BY NGID DESC
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.04       0.03          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        4     81.82     402.27     258094     505635          0          31
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        6     81.86     402.31     258094     505635          0          31
    
    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 55
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
         31  TABLE ACCESS BY INDEX ROWID NGTAGS
    1726509   INDEX RANGE SCAN DESCENDING NGTAGS_PK (object id 31331)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       4        0.00          0.00
      SQL*Net more data to client                     5        0.00          0.00
      db file sequential read                    258094        0.31        352.49
      latch free                                     87        0.05          0.12
      SQL*Net message from client                     4        9.26         25.61
    What I notice here is that there were alot of blocks read (either from disk and/or from cache) to return only 31 rows (see fetch)? I'm no expert in performance diagnosing, but does this strike anyone as odd? Why would Oracle read so many blocks just to find 31 rows? It's obviously using the index.

    I'm hoping this is a classic symptom that a novice like me doesn't recognize...

  2. #2
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    1726509 INDEX RANGE SCAN DESCENDING NGTAGS_PK (object id 31331)
    that's a lot of scan for 31 rows. A case of improper index?
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Please run an explain plan and see what the estimated cardinalities are. I'm guessing that the query is a lot more selective than the optimizer thinks it is. Are the contents of the columns NGUSRFLD2 and NGUSRFLD19 definitely mixed case? How selective are those two predicates? It might be worth creating a function-based index on UPPER() of one or both of them.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Guys,
    correct me if I am wrong but it seems to me that the index is scanned to avoid sorting, not for selectivity.
    The index seems to be primary key index on NGID and has nothing to deal with the predicates.
    Please buid an index on NGPROJGUID and I belive it will improve drastically

    Regards
    Boris

  5. #5
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    I looked at the base table of the view and found that it had never any stats for it, so I did:

    Code:
    exec dbms_stats.gather_index_stats (ownname => 'NGSDMS60', indname => 'NGTAGS_PK', estimate_percent => null);
    It improved response time dramatically:

    Code:
    SELECT  *
    FROM NGSDMS60.NGTAGS_VIEW T1
    WHERE NGPROJGUID='6A018CB9-C15B-443b-8487-5C016EB8251B'
    AND  (((UPPER(NGUSRFLD2)   =  ('ROARK'))
    AND  (UPPER(NGUSRFLD19)   =  ('BUILTIN'))))
    ORDER BY NGID DESC
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.09       0.17          0          0          0           0
    Execute      1      0.00       0.01          0          0          0           0
    Fetch        4      0.59       5.89        908        931          0          31
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        6      0.68       6.08        908        931          0          31
    
    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 55
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
         31  SORT ORDER BY
         31   TABLE ACCESS BY INDEX ROWID NGTAGS
       1494    INDEX RANGE SCAN NGUSRFLD2_IND (object id 31663)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       4        0.00          0.00
      SQL*Net more data to client                     5        0.00          0.00
      db file sequential read                       908        0.02          5.69
      SQL*Net message from client                     4       32.67         61.10
    So it was a case of no statistics for the index.

    Now I have another problem....

    After seeing that there were no stats for this index, I also saw that a lot of other indexes (on the same table) had no stats. So, I went ahead and got stats for the other indexes.

    After I did that, the performance this same qeury went reverted back to the previous poor response time:

    Code:
    SELECT  *
    FROM NGSDMS60.NGTAGS_VIEW T1
    WHERE NGPROJGUID='6A018CB9-C15B-443b-8487-5C016EB8251B'
    AND  (((UPPER(NGUSRFLD2)   =  ('ROARK'))
    AND  (UPPER(NGUSRFLD19)   =  ('BUILTIN'))))
    ORDER BY NGID DESC
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.11       0.11          0          0          0           0
    Execute      1      0.01       0.01          0          0          0           0
    Fetch        4    112.94     268.23     248319     250121          0          31
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        6    113.06     268.35     248319     250121          0          31
    
    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 55
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
         31  SORT ORDER BY
         31   TABLE ACCESS BY INDEX ROWID NGTAGS
    1680125    INDEX RANGE SCAN NGUSRFLD6_IND (object id 31665)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                       4        0.00          0.00
      SQL*Net more data to client                     5        0.00          0.00
      db file sequential read                    248319        0.54        175.31
      latch free                                      2        0.00          0.00
      SQL*Net message from client                     4        8.04         11.77
    I've tried deleting all the stats (dbms_stats.delete_index_stats) I just got for all the other indexes, but I can't get it to perform anymore.

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    I still think you miss an index on NGPROJGUID. The easyest way to bring back the fast plan is to hint the query.
    Are the indexes NGUSRFLD6_IND and NGUSRFLD2_IND function based? If not, they still cannot be used as appropriate

  7. #7
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Are the indexes NGUSRFLD6_IND and NGUSRFLD2_IND function based?
    Yes, there are, but they are also compound (correct my term usage) indexes. The indexes are based on the NGPROJGUID field and uses UPPER function on the NGUSRFLD6 and NGUSRFLD2 field, respectively.

    After looking over this whole thread, I realized I was over looking something... something I should have asked about cuz I don't understand why its happening....

    In my first post, I said response time is poor of this one query. Trace shows that there's a lot of db file seq read waits. Looking back now, I see it's using the NGTAG_PK index (as shown in the row execution) and it is having to read many many blocks to return just 31 rows.

    After that, I said I realized that the indexes on the (base) table being queried did not have stats, so I got stats for the NGTAGS_PK index. I reran the query and it had significant improvement. WHAT I DIDN'T REALIZE was that it NOW using a different index - NGUSRFLD2_IND.

    Question 1: When I analyzed only the NGTAGS_PK index, which improved response time, did the stats tell Oracle that NGTAGS_PK was not the best index to use, so instead it used NGUSRFLD2_IND?

    Then I went ahead and got stats for the other indexes (of the same table) missing stats. When I reran the query, it is slow again. And again, WHAT I DIDN'T REALIZE was that it NOW using a different index - NGUSRFLD6_IND.

    Question 2: Why did it even bother using NGUSRFLD6_IND when it was not even part of the query?? NGUSRFLD6_IND does have the NGPROJGUID indexed (and so did NGTAGS_PK), is this why Oracle would even remotely think to use this index?

    It seems that for this qeury, the best index to use is NGUSRFLD2_IND and it makes sense (now) - It indexes by NGPROJGUID and UPPER("NGUSRFLD2").

    Question 3: Why doesn't it use NGUSRFLD2_IND all the time, even after I got stats for all the indexes of the table??
    Last edited by dbbyleo; 11-20-2006 at 05:44 PM.

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    It is hard to explain the behaviour of the optimizer :-) You can only hope it's right :-)
    Quote Originally Posted by dbbyleo
    Question 1: When I analyzed only the NGTAGS_PK index, which improved response time, did the stats tell Oracle that NGTAGS_PK was not the best index to use, so instead it used NGUSRFLD2_IND?
    Cannot be sure about that BUT it could be. Other possibility is that gathering statistics , you unlocled the cost based aproach of the optimizer and it ignored the ..PK index. I see the optimizer_goal is CHOOSE what means RULE in case of statistics lack and COST in case if statistics for at least one object are presented.
    Q 2 and Q3 - keep in mind that the order in which the fields are present into the query mather. The index cannot be used for selectivity if the predicate field is not leading column into the index. Hence if NGPROJGUID is leading column in NGUSRFLD6_IND but is not leading in NGUSRFLD2_IND (supposing that NGPROJGUID='6A018CB9-C15B-443b-8487-5C016EB8251B' is most selective predicarte) that could be the reason.
    Again, it is really hard to say, this is just a guess. One can hardly explain the CBO even when all the data are available, and here when we do not know the exact index setup, stats etc. it can be really obly a guess

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    along with stats collect histograms with proper size.. ur problems will solve in no time.


    abhay
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Quote Originally Posted by Bore
    Guys,
    correct me if I am wrong but it seems to me that the index is scanned to avoid sorting, not for selectivity.
    The index seems to be primary key index on NGID and has nothing to deal with the predicates.
    Please buid an index on NGPROJGUID and I belive it will improve drastically

    Regards
    Boris

    how will index scan avoid scanning??

    as far as ur point on selectivity is concerned.. we need to check if any of the columns involved in the where clause is a part of PK?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width