db file sequential read for table access
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 16

Thread: db file sequential read for table access

Threaded View

  1. #1
    Join Date
    Aug 2002
    Posts
    56

    db file sequential read for table access

    Hi,
    I had a user complain about a query which was hanging(they had to cancel it after 8hours). The database is 1og R2 on a Solaris box with 32G mem and 8CPUs. The query uses one view which I figured was taking up most of the time

    Below is the query for the view:
    Code:
    SELECT   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,
                per_all_assignments_f ass,
                (SELECT *
                   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 peev.screen_entry_value IS NOT NULL
            AND pee.assignment_id = ass.assignment_id
            AND ass.person_id = peop.person_id
            AND ass.effective_end_date =
                   (SELECT MAX (sass.effective_end_date)
                      FROM hr.per_all_assignments_f sass
                     WHERE sass.person_id = peop.person_id
                       AND sass.assignment_id = ass.assignment_id)
       GROUP BY peop.person_id, ass.assignment_id, pee.element_entry_id;
    And here is the explain plan

    Code:
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation                              | Name                          | Rows  | Bytes | Cost  |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                       |                               |     1 |   212 |   713 |
    |   1 |  HASH GROUP BY                         |                               |     1 |   212 |   713 |
    |   2 |   VIEW                                 |                               |     1 |   212 |   713 |
    |   3 |    FILTER                              |                               |       |       |       |
    |   4 |     HASH GROUP BY                      |                               |     1 |   223 |   713 |
    |   5 |      TABLE ACCESS BY INDEX ROWID       | PAY_ELEMENT_ENTRIES_F         |     1 |    23 |     3 |
    |   6 |       NESTED LOOPS                     |                               |     1 |   223 |   712 |
    |   7 |        NESTED LOOPS                    |                               |     7 |  1400 |   691 |
    |   8 |         MERGE JOIN CARTESIAN           |                               |     1 |   172 |   618 |
    |   9 |          TABLE ACCESS BY INDEX ROWID   | PER_ALL_ASSIGNMENTS_F         |     1 |    30 |     1 |
    |  10 |           NESTED LOOPS                 |                               |     1 |   142 |   615 |
    |  11 |            NESTED LOOPS                |                               |   135 | 15120 |   479 |
    |  12 |             TABLE ACCESS FULL          | PER_ALL_PEOPLE_F              |    28 |  2352 |   346 |
    |  13 |             TABLE ACCESS BY INDEX ROWID| PER_ALL_ASSIGNMENTS_F         |     5 |   140 |     6 |
    |  14 |              INDEX RANGE SCAN          | PER_ASSIGNMENTS_F_N12         |     5 |       |     1 |
    |  15 |            INDEX RANGE SCAN            | PER_ASSIGNMENTS_F_N12         |     5 |       |     1 |
    |  16 |          BUFFER SORT                   |                               |     4 |   120 |   617 |
    |  17 |           INLIST ITERATOR              |                               |       |       |       |
    |  18 |            TABLE ACCESS BY INDEX ROWID | PAY_INPUT_VALUES_F_TL         |     4 |   120 |     4 |
    |  19 |             INDEX RANGE SCAN           | PAY_INPUT_VALUES_F_TL_PK      |     4 |       |     3 |
    |  20 |         TABLE ACCESS BY INDEX ROWID    | PAY_ELEMENT_ENTRY_VALUES_F    |    29 |   812 |    73 |
    |  21 |          INDEX RANGE SCAN              | PAY_ELEMENT_ENTRY_VALUES_F_N1 |    40 |       |    62 |
    |  22 |        INDEX RANGE SCAN                | PAY_ELEMENT_ENTRIES_F_PK      |     1 |       |     2 |
    --------------------------------------------------------------------------------------------------------
    I figured creating an index on PER_ALL_PEOPLE_F table will help. Here is the explain plan after the index creation:

    Code:
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation                              | Name                          | Rows  | Bytes | Cost  |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                       |                               |     1 |   212 |   377 |
    |   1 |  HASH GROUP BY                         |                               |     1 |   212 |   377 |
    |   2 |   VIEW                                 |                               |     1 |   212 |   377 |
    |   3 |    FILTER                              |                               |       |       |       |
    |   4 |     HASH GROUP BY                      |                               |     1 |   223 |   377 |
    |   5 |      TABLE ACCESS BY INDEX ROWID       | PAY_ELEMENT_ENTRIES_F         |     1 |    23 |     3 |
    |   6 |       NESTED LOOPS                     |                               |     1 |   223 |   376 |
    |   7 |        NESTED LOOPS                    |                               |     7 |  1400 |   355 |
    |   8 |         MERGE JOIN CARTESIAN           |                               |     1 |   172 |   282 |
    |   9 |          TABLE ACCESS BY INDEX ROWID   | PER_ALL_ASSIGNMENTS_F         |     1 |    30 |     1 |
    |  10 |           NESTED LOOPS                 |                               |     1 |   142 |   278 |
    |  11 |            NESTED LOOPS                |                               |   135 | 15120 |   142 |
    |  12 |             TABLE ACCESS BY INDEX ROWID| PER_ALL_PEOPLE_F              |    28 |  2352 |     9 |
    |  13 |              INDEX RANGE SCAN          | TEST                          |    28 |       |     1 |
    |  14 |             TABLE ACCESS BY INDEX ROWID| PER_ALL_ASSIGNMENTS_F         |     5 |   140 |     6 |
    |  15 |              INDEX RANGE SCAN          | PER_ASSIGNMENTS_F_N12         |     5 |       |     1 |
    |  16 |            INDEX RANGE SCAN            | PER_ASSIGNMENTS_F_N12         |     5 |       |     1 |
    |  17 |          BUFFER SORT                   |                               |     4 |   120 |   281 |
    |  18 |           INLIST ITERATOR              |                               |       |       |       |
    |  19 |            TABLE ACCESS BY INDEX ROWID | PAY_INPUT_VALUES_F_TL         |     4 |   120 |     4 |
    |  20 |             INDEX RANGE SCAN           | PAY_INPUT_VALUES_F_TL_PK      |     4 |       |     3 |
    |  21 |         TABLE ACCESS BY INDEX ROWID    | PAY_ELEMENT_ENTRY_VALUES_F    |    29 |   812 |    73 |
    |  22 |          INDEX RANGE SCAN              | PAY_ELEMENT_ENTRY_VALUES_F_N1 |    40 |       |    62 |
    |  23 |        INDEX RANGE SCAN                | PAY_ELEMENT_ENTRIES_F_PK      |     1 |       |     2 |
    --------------------------------------------------------------------------------------------------------
    But there was something else I noticed when I ran the level 12 trace for the query. There were a lot of db file sequential read event :
    db file sequential read 90213 0.18 92.71


    On checking the raw trace file the object# for the wait was pointing to a table:


    OWNER OBJECT_ID OBJECT_NAME OBJECT_TYP
    ----- ---------- ----------------------------------- ----------
    HR 42003 PAY_ELEMENT_ENTRIES_F TABLE
    HR 42061 PAY_ELEMENT_ENTRIES_F_PK INDEX
    HR 42063 PAY_ELEMENT_ENTRY_VALUES_F TABLE
    HR 42081 PAY_ELEMENT_ENTRY_VALUES_F_N1 INDEX


    Can someone explain why there is db file sequential read event for table access and what else can be done to improve the query performance.

    Any pointers is appreciated.

    Thanks
    Last edited by gandolf989; 12-12-2006 at 02:26 PM.
    When in doubt ...go to the basics!!

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