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