Horrible Query Performance, Simple Query -- Performance Tuning Help Needed

From: WhiteDog <cchenoweth56_at_msn.com>
Date: 19 Apr 2006 08:46:04 -0700
Message-ID: <1145461564.739608.136270@i39g2000cwa.googlegroups.com>

Hello all. I'm writing to this group in hopes of a way to fix my problem. So far, no one has been able to fix this issue, they all keep telling me to "deal with it, that is the way it is." I just won't accept that oracle could be this crummy.

Anyway, here is my problem: I have a very simple query that does three table joins, a simple filter, and a sort. The query takes 27 seconds to run. From the TKProf output, it looks like most (almost all) of the time is taken doing IO to get the data from disk. I'm unsure of how to make it faster... any and all ideas would be appreciated.

Thanks for your help,

Chad

Oracle version 10.2.0.1.0. Brand new hardware running on Unix.

Query:
SELECT

            E.EVENT_ID EventId,
            hdd_tran_2000(D.REPORT_TYPE_NCID)  DocumentTitle,
            D.EVENT_GMTIME DocumentUniversalTime,

decode(d.dictating_clinician_name,null,tran_clinician(d.dictating_clinician_ncid,e.event_id),d.dictating_clinician_name) Clinician

            FROM
                  DOCUMENT D,
                  EVENT E,
                 ebiz_application_document x
            WHERE
                 E.UNIT_NUMBER= 232411
                  AND
                 E.EVENT_ID=D.EVENT_ID
                  AND
                 D.REPORT_TYPE_NCID = x.report_type_ncid
                 and x.application_id=1
                 and x.document_category_id=5
            ORDER BY
                 DocumentUniversalTime desc




The sizes of each table are:
     EVENT: 15,690,395 rows
     DOCUMENT: 5,661,104 rows
     ebiz_application_document: 384 rows

The total rows returned by the query are around 850.

Here is the TKProf output:

call count cpu elapsed disk query current

rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.03       0.03          1          1          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch       62      0.57      27.17       3856       8166          0
     907

------- ------ -------- ---------- ---------- ---------- ----------

total       64      0.60      27.20       3857       8167          0
     907

Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 108

Rows Row Source Operation

-------  ---------------------------------------------------

907 SORT ORDER BY (cr=38786 pr=5695 pw=0 time=33005420 us)

907 NESTED LOOPS (cr=8166 pr=3856 pw=0 time=21419454 us)

1088 NESTED LOOPS (cr=7076 pr=3853 pw=0 time=12322744 us)

2013 TABLE ACCESS BY INDEX ROWID OBJ#(3135) (cr=1943 pr=1827 pw=0 time=15699436 us)

2013 INDEX RANGE SCAN OBJ#(4015) (cr=13 pr=12 pw=0 time=16765 us)(object id 4015)

1088 TABLE ACCESS BY INDEX ROWID OBJ#(3459) (cr=5133 pr=2026 pw=0 time=13354221 us)

1088 INDEX RANGE SCAN OBJ#(4531) (cr=4042 pr=1001 pw=0 time=4485629 us)(object id 4531)

907 INDEX UNIQUE SCAN OBJ#(63333) (cr=1090 pr=3 pw=0 time=8256 us)(object id 63333)

Received on Wed Apr 19 2006 - 10:46:04 CDT