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

"WhiteDog" <cchenoweth56_at_msn.com> wrote in message news:1145461564.739608.136270_at_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)
> ********************************************************************************
>

Combining the details from several posts - and assuming that the object numbers that appear in the tkprof output match the object names in the explain plan you also supplied:

    You are collecting 2,000 events from 16,000,000     Is there any reason why you think the events     for unit_number 232411 should be tightly clustered     rather than being scattered randomly through the     16,000,000. You did 1,943 physical reads on     the EVENT table, so they appear to be randomly     scattered.

    You are collecting 1,100 documents from 5,600,000.     Again, is there any reason why you think the 1,100     you want should be tightly clustered rather than     randomly scattered ? You did 2,023 physical reads     on the DOCUMENT table - which is a little odd as     you only visited it 1,088 times (according to the count     of the indexed rowsource) - so perhaps you have a lot     chained/migrated rows in that table as well as a     problem with extremely randomly scattered data.

    You have two "selective" access paths into the data -     from event or from ebiz_application_document.     Either path needs to pick up at least 900 documents.     Unless you find a way of clustering the documents, or     getting the whole table in memory, to minimise the number     of physical reads, then your query is likely to take around     9 seconds to complete (at an optimistic 100 I/Os per second).

    You may be able to reduce the I/O cost of the scan on     the EVENT table by recreating it as an IOT, or single     table cluster.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html