-
Help on slow query
Hi, I have a query from an application runs slow intermittently, I run the 10046 level 12 trace on the application when the query is fast and slow, I got the following result in tkprof:
When the query is slow:
Code:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.76 45.24 5408 21617 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.76 45.24 5408 21617 0 0
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 21 (DBUSER)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 TABLE ACCESS BY INDEX ROWID tab1
7306 AND-EQUAL
14612 INDEX RANGE SCAN FK_tab1_TO_tab2 (object id 11936)
7617 INDEX RANGE SCAN FK_tab1_TO_tab3 (object id 11935)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
0 SORT (ORDER BY)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'tab1'
7306 AND-EQUAL
14612 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'FK_tab1_TO_tab2' (NON-UNIQUE)
7617 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'FK_tab1_TO_tab3' (NON-UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
global cache cr request 3770 1.22 33.86
db file sequential read 5408 0.31 10.87
global cache quiesce wait 1 0.00 0.00
cr request retry 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
When the query is fast:
Code:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.02 0.67 199 683 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.67 199 683 0 0
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 21 (DBUSER)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 TABLE ACCESS BY INDEX ROWID tab1
202 AND-EQUAL
404 INDEX RANGE SCAN FK_tab1_TO_tab2 (object id 11936)
239 INDEX RANGE SCAN FK_tab1_TO_tab3 (object id 11935)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: FIRST_ROWS
0 SORT (ORDER BY)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'tab1'
202 AND-EQUAL
404 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'FK_tab1_TO_tab2' (NON-UNIQUE)
239 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'FK_tab1_TO_tab3' (NON-UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
global cache cr request 105 0.00 0.03
db file sequential read 199 0.03 0.61
SQL*Net message from client 1 0.00 0.00
********************************************************************************
-
show us the query
but this is perfectly understandable if for example yuu have a table with 10000 males and 10 females, when you search for females it runs fast and males slow
I noticed you are using First_Rows as your optimizer_mode, is it a requirement? Have you tried with All_Rows with this query?
-
see the wait in the first plan.. Which might be slowing down..!
Code:
global cache cr request 3770 1.22 33.86
Reason could be poor Cluster Interconnect (I assume you are using RAC), high load on the systems, using a public interconnect instead of a private network or other network issues. Investigate your cluster interconnect network issues/performances after seeing the general behaviour of the systems.
Last edited by Thomasps; 08-16-2006 at 02:45 AM.
-
Yes, we use RAC, but why other queries works fine except this one?
The query is
Code:
SELECT tab1.col1, tab1.col2, tab1.col3, tab1.col4, tab1.col5
FROM tab1
WHERE ((((tab1.col9 = :1 AND tab1.col6 = :2 AND tab1.col7 < :6) AND
(tab1.col8 = :7 OR tab1.col8 = :8)) AND
((tab1.col3 = :3 OR tab1.col3 = :4) OR tab1.col3 = :5)) AND
((tab1.col4 - tab1.col5) > :9))
ORDER BY (tab1.col4 - tab1.col5) ASC, tab1.col1 ASC, tab1.col2 ASC
FK_tab1_TO_tab2 index is on tab1.col6 column
FK_tab1_TO_tab3 index is on tab1.col9 column
Should I create a cover index on the following columns:
(col9, col6, col7, col8, col3)
I don't think I should include col4 and col5 since it will not use it unless it is a functional-based index.
-
See, you have the same query running some times fast and some times slow.. Think yourself.. Do you require to create indexes to resolve this issue? If this is an index problem, why the query is running some times fast, as you shown in the second plan?
-
I think the reason why sometimes it is fast, sometimes it is slow is that the values of the bind variables are different , as you can see in the original post, when it is fast, query = 683, when it is slow, query = 21617. I am not saying RAC is not factor, but is it possible the missing index is the major factor in this case?
-
Then... See the answer from Pando.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|