db file sequential read for table access - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: db file sequential read for table access

  1. #11
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Did you do this in the query:

    (SELECT rownum rn, a.*
    FROM per_all_people_f a
    WHERE effective_end_date =
    TO_DATE ('31-DEC-4712', 'DD-MON-YYYY'))

    With the rownum, oracle will materialize the inline view, and probably come out with different plan.

    Tamil

  2. #12
    Join Date
    Aug 2002
    Posts
    56
    Tamil,
    I do have the explain plan output for the above query posted on 12/12 based on your earlier request. It seems that the query performance goes down as per the explain plan.

    bhatia333
    When in doubt ...go to the basics!!

  3. #13
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Try this:
    Code:
    SELECT   /*+ LEADING(PIV) */
        peop.person_id, 
        ass.assignment_id, 
        pee.element_entry_id,
        MAX (peop.full_name) AS full_name,
        MAX (DECODE (piv.NAME, 'Rank', 
                     peev.screen_entry_value, NULL)
            ) AS RANK,
        MAX (DECODE (piv.NAME, 'Vessel', 
                     peev.screen_entry_value, NULL)
            ) AS vessel,
        MAX (DECODE (piv.NAME, 'Days', 
                     peev.screen_entry_value, NULL)
            ) AS days_worked,
        MAX (DECODE (piv.NAME,
                     'Date', TO_DATE (peev.screen_entry_value,
                                    'YYYY/MM/DD HH24:MI:SS'),
                             NULL
                            )
            ) AS date_worked
    FROM pay_element_entries_f      pee,
         pay_element_entry_values_f peev,
         pay_input_values_f_tl      piv,
         ( select /*+ NO_MERGE */              
                  person_id, assignment_id, 
                  max(effective_end_date) effective_end_date
            from per_all_assignments_f 
            group by person_id, assignment_id)     ass,
         (SELECT /*+ NO_MERGE */
                    person_id
            FROM per_all_people_f
           WHERE effective_end_date =
                 TO_DATE ('31-DEC-4712', 'DD-MON-YYYY')) peop
    WHERE pee.element_entry_id = peev.element_entry_id
            AND peev.input_value_id = piv.input_value_id
            AND piv.input_value_id IN (3220, 3221, 3222, 3223)
            AND pee.assignment_id = ass.assignment_id
            AND ass.person_id = peop.person_id        
    GROUP BY peop.person_id, 
          ass.assignment_id, 
          pee.element_entry_id;
    Trace the SQL statement, and attach the trace file and tkprof output here.

    Tamil

  4. #14
    Join Date
    Aug 2002
    Posts
    56
    The query ran for sometime without returning any rows. The trace file started increasing to its max value. So I had to cancel the query. The result is similar to what I have stated in my original post. Could not attach the trace file because of size limit.

    Attached is the tkprof output. I saw the raw trace files and all the db file sequential read event happen on the following:

    SQL> select object_name, object_type from dba_objects where object_id in (42063,42061);

    OBJECT_NAME
    --------------------------------------------------------------------------------
    OBJECT_TYPE
    -------------------
    PAY_ELEMENT_ENTRIES_F_PK
    INDEX

    PAY_ELEMENT_ENTRY_VALUES_F
    TABLE

    These are the same objects that I had mentioned in my original post. The raw trace file is full of:


    WAIT #1: nam='db file sequential read' ela= 1863 file#=367 block#=155196 blocks=1 obj#=42063 tim=2356739681387
    WAIT #1: nam='db file sequential read' ela= 965 file#=374 block#=135647 blocks=1 obj#=42061 tim=2356739682766
    WAIT #1: nam='db file sequential read' ela= 59 file#=367 block#=155197 blocks=1 obj#=42063 tim=2356739683074
    WAIT #1: nam='db file sequential read' ela= 877 file#=374 block#=135649 blocks=1 obj#=42061 tim=2356739684280
    WAIT #1: nam='db file sequential read' ela= 57 file#=367 block#=155198 blocks=1 obj#=42063 tim=2356739684584
    WAIT #1: nam='db file sequential read' ela= 659 file#=374 block#=135597 blocks=1 obj#=42061 tim=2356739685556
    WAIT #1: nam='db file sequential read' ela= 58 file#=367 block#=155199 blocks=1 obj#=42063 tim=2356739685863
    WAIT #1: nam='db file sequential read' ela= 77 file#=367 block#=155200 blocks=1 obj#=42063 tim=2356739686357
    WAIT #1: nam='db file sequential read' ela= 57 file#=367 block#=155201 blocks=1 obj#=42063 tim=2356739686834
    WAIT #1: nam='db file sequential read' ela= 58 file#=367 block#=155202 blocks=1 obj#=42063 tim=2356739687402
    WAIT #1: nam='db file sequential read' ela= 161 file#=367 block#=155203 blocks=1 obj#=42063 tim=2356739687954
    WAIT #1: nam='db file sequential read' ela= 78 file#=367 block#=155204 blocks=1 obj#=42063 tim=2356739688461
    WAIT #1: nam='db file sequential read' ela= 58 file#=367 block#=155205 blocks=1 obj#=42063 tim=2356739688936
    WAIT #1: nam='db file sequential read' ela= 58 file#=367 block#=155206 blocks=1 obj#=42063 tim=2356739689416
    WAIT #1: nam='db file sequential read' ela= 59 file#=367 block#=155207 blocks=1 obj#=42063 tim=2356739689911
    WAIT #1: nam='db file sequential read' ela= 425 file#=367 block#=155208 blocks=1 obj#=42063 tim=2356739690721
    WAIT #1: nam='db file sequential read' ela= 53 file#=367 block#=155209 blocks=1 obj#=42063 tim=2356739691202
    WAIT #1: nam='db file sequential read' ela= 66 file#=367 block#=155210 blocks=1 obj#=42063 tim=2356739691701
    WAIT #1: nam='db file sequential read' ela= 62 file#=367 block#=155211 blocks=1 obj#=42063 tim=2356739692156
    WAIT #1: nam='db file sequential read' ela= 8632 file#=367 block#=150122 blocks=1 obj#=42003 tim=2356739700998
    WAIT #1: nam='db file sequential read' ela= 24616 file#=374 block#=135246 blocks=1 obj#=42081 tim=2356739726032
    WAIT #1: nam='db file sequential read' ela= 67 file#=367 block#=155212 blocks=1 obj#=42063 tim=2356739726261
    WAIT #1: nam='db file sequential read' ela= 94 file#=367 block#=155213 blocks=1 obj#=42063 tim=2356739727041
    WAIT #1: nam='db file sequential read' ela= 67 file#=367 block#=155214 blocks=1 obj#=42063 tim=2356739727760
    WAIT #1: nam='db file sequential read' ela= 61 file#=367 block#=155215 blocks=1 obj#=42063 tim=2356739728256
    WAIT #1: nam='db file sequential read' ela= 1032 file#=367 block#=155216 blocks=1 obj#=42063 tim=2356739729722
    WAIT #1: nam='db file sequential read' ela= 1220 file#=367 block#=155217 blocks=1 obj#=42063 tim=2356739731374
    WAIT #1: nam='db file sequential read' ela= 62 file#=367 block#=155218 blocks=1 obj#=42063 tim=2356739732033
    WAIT #1: nam='db file sequential read' ela= 54 file#=367 block#=155219 blocks=1 obj#=42063 tim=2356739732432
    WAIT #1: nam='db file sequential read' ela= 1860 file#=367 block#=155220 blocks=1 obj#=42063 tim=2356739734640
    WAIT #1: nam='db file sequential read' ela= 52 file#=367 block#=155221 blocks=1 obj#=42063 tim=2356739735133
    WAIT #1: nam='db file sequential read' ela= 60 file#=367 block#=155222 blocks=1 obj#=42063 tim=2356739735533
    WAIT #1: nam='db file sequential read' ela= 53 file#=367 block#=155223 blocks=1 obj#=42063 tim=2356739736010
    Attached Files Attached Files
    When in doubt ...go to the basics!!

  5. #15
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    How long did you run the query?

    For tuning a query row source is important:
    Code:
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  SELECT STATEMENT   MODE: ALL_ROWS
          0   HASH (GROUP BY)
          0    HASH JOIN
          0     VIEW
          0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'PER_ALL_PEOPLE_F' 
                     (TABLE)
          0     HASH JOIN
          0      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                     'PAY_ELEMENT_ENTRIES_F' (TABLE)
          0       NESTED LOOPS
          0        NESTED LOOPS
          0         INLIST ITERATOR
          0          TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                         'PAY_INPUT_VALUES_F_TL' (TABLE)
          0           INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                          'PAY_INPUT_VALUES_F_TL_PK' (INDEX (UNIQUE))
          0         TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 
                        'PAY_ELEMENT_ENTRY_VALUES_F' (TABLE)
          0          INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                         'PAY_ELEMENT_ENTRY_VALUES_F_N1' (INDEX)
          0        INDEX   MODE: ANALYZED (RANGE SCAN) OF 
                       'PAY_ELEMENT_ENTRIES_F_PK' (INDEX (UNIQUE))
          0      VIEW
          0       HASH (GROUP BY)
          0        TABLE ACCESS   MODE: ANALYZED (FULL) OF 
                       'PER_ALL_ASSIGNMENTS_F' (TABLE)
    Do you have any valid info for the row source?

    You do not need set level 12 trace. First try with level 1.

    Tamil

  6. #16
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    Let me guess, you have cursor_sharing set to force in the database, do you?

    You statistics lies to CBO. This magic 31-DEC-4712 valie needs to be null instead. With 9000 real values most likely with in year 2000-2006 range and 18000 values 2700 years away I am surprised Oracle expects to get 28 rows back. I would expect less than that for a single date. Change this magic value to null, analyze PER_ALL_PEOPLE_F table again (make sure you collect column level stats for effective_end_date column, no histograms).

    If you cannot change this value to null, then collect histograms on effective_end_date column and run explain plan on this small query.


    I spoted this problem based on explain plan cardinality estimate and real number of rows returned by the query. You can use this technique to segment your query and make sure optimizer get the correct picture.

    If you have cursor_sharing parameter set to force then you need to change it to exact in your session and run the explain plan. Make sure you either use sql*plus 10.2 to get autotrace or dbms_xplan.display to get the explain plan. This way you will get predicates as well that is really important.

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