Help on slow query

[zxmgh]

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:

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:

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
********************************************************************************

[pando]

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?

[Thomasps]

see the wait in the first plan.. Which might be slowing down..!

 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.

[zxmgh]

Yes, we use RAC, but why other queries works fine except this one?

The query is

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.

[Thomasps]

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?

[zxmgh]

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?

[Thomasps]

Then... See the answer from Pando.