| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Performance Question
Try to use another optimizer.
I suggest your are using the rule base.
Try to use the cost based optimizer.
Your can do it in three ways:
put a line
"optimizer_mode=first_rows"
in your initSID.ora
and restart the instance.
Put the statement
alter session set optimizer_mode=first_rows;
before your select statement
Work with optimizer_hints:
select /*+ FIRST_ROWS */ ...................................................
Best regards.
Martin Hepworth wrote:
> HI
>
> Well two tools I used are
>
> 1) the Oracle Preformance tuning book by Mark Gurry & Peter Corrigan
> (published by O'Reilly).
> 2) TOAD (www.toadsoft.com for the freeware version) and use it to to
> explain plans on the fly. This means I can make chnages to SQL and see
> the cost differences due to recommendations from 1) without actually
> running a long running SQL.
>
> Looks like sort is taking the time so I'd concentrate on reducing that
> with 'exists' to reduce sort time.
>
> Martin
>
> CGS wrote:
> >
> > Hi All,
> >
> > I created a SQL trace for a query that was taking hours to run. Can someone
> > please help me to interpret the output? What I do not understand is that why
> > is
> > it taking that long to fetch 22 rows? The time taken to execute the SQL was
> > almost negligible.
> >
> > Any help is appreciated
> >
> > CGS
> > tcgs_at_hotmail.com
> >
> > SELECT DISTINCT A.RT_TYPE, A.TXN_CURRENCY_CD, A.BASE_CURRENCY, A.INVOICE_DT,
> > B.RATE_MULT, B.RATE_DIV
> > FROM
> > EDT_VCHR_WRK A, RT_DFLT_VW B WHERE A.BUSINESS_UNIT = :1 AND
> > A.PROCESS_INSTANCE = :2 AND A.CUR_RT_SOURCE = 'T' AND B.FROM_CUR =
> > A.TXN_CURRENCY_CD AND B.TO_CUR = A.BASE_CURRENCY AND B.RT_TYPE = A.RT_TYPE
> > AND B.EFFDT = (SELECT MAX(I.EFFDT) FROM RT_DFLT_VW I WHERE I.FROM_CUR =
> > B.FROM_CUR AND I.TO_CUR = B.TO_CUR AND I.RT_TYPE = B.RT_TYPE AND I.EFFDT
> > <=
> > A.INVOICE_DT)
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ---------- ------
> > ----
> > Parse 8 0.07 0.09 0 0 0
> > 0
> > Execute 8 0.05 0.05 0 0 0
> > 0
> > Fetch 8 12287.62 12603.65 73 530467835 0
> > 22
> > ------- ------ -------- ---------- ---------- ---------- ---------- ------
> > ----
> > total 24 12287.74 12603.79 73 530467835 0
> > 22
> >
> > Misses in library cache during parse: 2
> > Optimizer goal: ALL_ROWS
> > Parsing user id: 22 (TEST)
> >
> > Rows Execution Plan
> > ------- ---------------------------------------------------
> > 0 SELECT STATEMENT GOAL: ALL_ROWS
> > 246 SORT (UNIQUE)
> > 246 NESTED LOOPS
> > 246 NESTED LOOPS
> > 4408 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> > 'RT_RATE_TBL'
> > 4409 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RT_RATE_TBL_IDX01'
> > (NON-UNIQUE)
> > 281178 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> > 'EDT_VCHR_WRK'
> > 1088776 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'EDT_VCHR_WRK_IDX01'
> > (NON-UNIQUE)
> > 2152377 SORT (AGGREGATE)
> > 2152377 NESTED LOOPS
> > 174874176 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> > 'RT_RATE_TBL'
> > 174913848 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> > 'RT_RATE_TBL_IDX01' (NON-UNIQUE)
> > 2160396 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> > 'RT_INDEX_TBL'
> > 2160396 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> > 'RT_INDEX_TBL' (UNIQUE)
> > 246 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> > 'RT_INDEX_TBL'
> > 246 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'RT_INDEX_TBL'
> > (UNIQUE)
--
 |
 |