explain plan question

[maswien]

Following query is complained by our user that it is too slow:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 735290276

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |     1 |   147 |    82   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                     |                       |     1 |   147 |    82   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                    |                       |     1 |   107 |    82   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                   |                       |     1 |    90 |    81   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                  |                       |     1 |    73 |    81   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                 |                       |     1 |    56 |    80   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                |                       |    24 |   936 |    69   (0)| 00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID| TESTINFO            |    24 |   408 |    21   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN          | TESTINFOTEMPKEY     |    24 |       |     4   (0)| 00:00:01 |
|   9 |        TABLE ACCESS BY INDEX ROWID| TEST                |     1 |    22 |     2   (0)| 00:00:01 |
|* 10 |         INDEX UNIQUE SCAN         | TESTPRIMARYKEY      |     1 |       |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS BY INDEX ROWID | TESTINFO            |     1 |    17 |     2   (0)| 00:00:01 |
|* 12 |        INDEX UNIQUE SCAN          | PERMINFOPRIKEY        |     1 |       |     1   (0)| 00:00:01 |
|  13 |      TABLE ACCESS BY INDEX ROWID  | TESTINFO            |     1 |    17 |     2   (0)| 00:00:01 |
|* 14 |       INDEX UNIQUE SCAN           | PERMINFOPRIKEY        |     1 |       |     1   (0)| 00:00:01 |
|  15 |     TABLE ACCESS BY INDEX ROWID   | TESTINFO            |     1 |    17 |     2   (0)| 00:00:01 |
|* 16 |      INDEX UNIQUE SCAN            | PERMINFOPRIKEY        |     1 |       |     1   (0)| 00:00:01 |
|  17 |    TABLE ACCESS BY INDEX ROWID    | TESTINFO            |     1 |    17 |     2   (0)| 00:00:01 |
|* 18 |     INDEX UNIQUE SCAN             | PERMINFOPRIKEY        |     1 |       |     1   (0)| 00:00:01 |
|  19 |   INLIST ITERATOR                 |                       |       |       |            |          |
|* 20 |    TABLE ACCESS BY INDEX ROWID    | VALIDDEFICIENCY       |     1 |    40 |     2   (0)| 00:00:01 |
|* 21 |     INDEX RANGE SCAN              | VALIDDEFICIENCYPRIKEY |     2 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("TESTINFO_A"."INFOVALUE"=:B3 AND "TESTINFO_A"."INFONUM"=2227)
  10 - access("TESTINFO_A"."TESTRSN"="TEST"."TESTRSN")
  12 - access("TEST"."TESTRSN"="TESTINFO_B"."TESTRSN" AND "TESTINFO_B"."INFONUM"=2212)
  14 - access("TESTINFO_B"."TESTRSN"="TESTINFO_C"."TESTRSN" AND
              "TESTINFO_C"."INFONUM"=2213)
  16 - access("TESTINFO_C"."TESTRSN"="TESTINFO_D"."TESTRSN" AND
              "TESTINFO_D"."INFONUM"=2214)
  18 - access("TESTINFO_A"."TESTRSN"="TESTINFO_A2"."TESTRSN" AND
              "TESTINFO_A2"."INFONUM"=2223)
  20 - filter(TRIM("TESTINFO_A2"."INFOVALUE")=:B2 OR TRIM("TESTINFO_A2"."INFOVALUE")=:B1 OR
              TRIM("TESTINFO_A2"."INFOVALUE")=TRIM("VALIDDEFICIENCY"."DEFICIENCYDESC"))
  21 - access("VALIDDEFICIENCY"."DEFICIENCYNUM"=TO_NUMBER(:B2) OR
              "VALIDDEFICIENCY"."DEFICIENCYNUM"=TO_NUMBER(:B1))

Anything can be changed for this query to run faster?

Thanks

 

[carp]

It's rather hard to tell without seeing the query!

 

[maswien]

The query:

      SELECT /*+  FIRST_ROWS */ TEST.TESTrsn prosecution_TESTrsn, 
       TEST.stampdate, TEST.stampuser, 
       TESTinfo_a.infovalue prosecution_processrsn, to_date(
       TESTinfo_c.infovalue, 'MM DD, YYYY') AS outcome_date, 
       TESTinfo_b.infovalue outcome_description, 
       TESTinfo_d.infovalue fine_amount
    FROM TESTinfo TESTinfo_d, TESTinfo TESTinfo_c, 
         TESTinfo TESTinfo_b, validdeficiency, TEST, 
         TESTinfo TESTinfo_a2, TESTinfo TESTinfo_a
    WHERE TESTinfo_a.TESTrsn = TEST.TESTrsn
      AND TESTinfo_a.TESTrsn = TESTinfo_a2.TESTrsn
      AND TEST.TESTrsn = TESTinfo_b.TESTrsn
      AND TESTinfo_b.TESTrsn = TESTinfo_c.TESTrsn
      AND TESTinfo_c.TESTrsn = TESTinfo_d.TESTrsn
      AND TESTinfo_a.infoNUM = 2227
      AND TESTinfo_a.infovalue = :b3
      AND validdeficiency.deficiencyNUM IN (:b2, :b1)
      AND TESTinfo_a2.infoNUM = 2223
      AND (trim(TESTinfo_a2.infovalue) = :b2
      OR  trim(TESTinfo_a2.infovalue) = :b1
      OR  trim(TESTinfo_a2.infovalue) = trim(validdeficiency.deficiencydesc))
      AND TESTinfo_b.infoNUM = 2212
      AND TESTinfo_c.infoNUM = 2213
      AND TESTinfo_d.infoNUM = 2214

 

[maswien]

it seems the indexes are used, in this case rebuilding index helps?

 

[carp]

As maswien suggests - rebuilding the indexes might help. If there is a lot of "swiss cheese" in the tables, rebuilding the tables might help. And finally, making sure your statistics are up to date might help. But I don't see anywhere to improve the SQL.

 

[maswien]

I just ran statspack on the database, the top 5 waits is like following:

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
direct path read                            11,561,270       8,167      1   44.2
db file sequential read                        617,031       4,689      8   25.4
CPU time                                                     3,346          18.1
PX qref latch                              311,176,813         996      0    5.4
PX Deq: Msg Fragment                            69,952         451      6    2.4

Rebuilding index will reduce the wait?