Performance Issue Particular query

[Sureshy]

Hi All,

My database is running perfectly apart from a particular report which started to slow down as soon as we went to Oracle 10G on Windows.

We were Oracle 8i on SCO UNix.

I have added the results of the trace file from dbms_system

I am not a developer and cannot see why there is an issue when indexes are being used.. any help would be very much appreciated.


SELECT /*+FIRST_ROWS*/ A.CompanyCode, A.LedgerType, A.AccountNo,
A.TransactionNo, A.CurrencyCode, A.MatchedAt, A.MatchedAtTime,
A.AllocatedValue, A.SessionNumber, B.CompanyCode, B.LedgerType, B.AccountNo,
B.TransactionNo, B.Status, B.TransactionType, B.TransactionDate, B.DueDate,
B.Year, B.PeriodNumber, B.OurReference, B.TheirReference, C.CompanyCode,
C.LedgerType, C.TransactionNo, C.CurrencyCode, C.TransactionValue,
C.AllocatedValue,A.rowid,B.rowid,C.rowid
FROM
CashAllocationAudt A, LedgerTransactions B, TransactionValues C WHERE
A.CompanyCode = :1 AND A.LedgerType = :2 AND A.AccountNo = :3
AND A.CurrencyCode = :4 AND A.MatchedAt >= :5 AND B.CompanyCode =
:6 AND B.LedgerType = :7 AND B.AccountNo = :8 AND C.CompanyCode =
:9 AND C.LedgerType = :10 AND C.CurrencyCode = :11 AND
(B.TransactionNo=A.TransactionNo AND C.TransactionNo=B.TransactionNo) ORDER
BY A.CompanyCode ASC, A.LedgerType ASC, A.AccountNo ASC, A.SessionNumber
ASC, A.CurrencyCode ASC, A.TransactionNo ASC


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4663 0.09 0.06 0 0 0 0
Execute 4663 0.17 0.10 0 0 0 0
Fetch 4686 2331.12 2576.27 79872 49658332 0 557
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14012 2331.39 2576.44 79872 49658332 0 557

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 48 (MAGIC8)

Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS (cr=10608 pr=10417 pw=0 time=3643570 us)
0 NESTED LOOPS (cr=10608 pr=10417 pw=0 time=3643567 us)
0 TABLE ACCESS BY INDEX ROWID CASHALLOCATIONAUDT (cr=10608 pr=10417 pw=0 time=3643565 us)
42 INDEX RANGE SCAN CASHALLOCATIONAUD3 (cr=10595 pr=10404 pw=0 time=656926 us)(object id 55895)
0 TABLE ACCESS BY INDEX ROWID LEDGERTRANSACTIONS (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN LEDGERTRANSACTION4 (cr=0 pr=0 pw=0 time=0 us)(object id 56729)
0 TABLE ACCESS BY INDEX ROWID TRANSACTIONVALUES (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN TRANSACTIONVALUES1 (cr=0 pr=0 pw=0 time=0 us)(object id 55887)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: HINT: FIRST_ROWS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'CASHALLOCATIONAUDT' (TABLE)
42 INDEX MODE: ANALYZED (RANGE SCAN) OF
'CASHALLOCATIONAUD3' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'LEDGERTRANSACTIONS' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'LEDGERTRANSACTION4' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TRANSACTIONVALUES' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'TRANSACTIONVALUES1'
(INDEX (UNIQUE))

[LKBrwn_DBA]

Hi All,

My database is running perfectly apart from a particular report which started to slow down as soon as we went to Oracle 10G on Windows.

We were Oracle 8i on SCO UNix.

Here is the problem:

"We were on Unix ... we went to WinDoze"

[malay_biswal]

Look at this part
"INDEX RANGE SCAN CASHALLOCATIONAUD3" It's somehow screwed up in Windows.

[Sureshy]

Thanks Guys.. I removed the index being used and it sped up by using a different one.. strange..

[PAVB]

Thanks Guys.. I removed the index being used and it sped up by using a different one.. strange..

Did you check explain plan with no first_rows hint?

Either way, chances are removed index would be benefitial to other queries.

Instead of removing it I would check explain plan with no first_rows hint and, if necessary add and index hint to direct Oracle optimizer in the right direction.

[PAVB]

Do you still have the old environment available?... compare explain plans.