-
Performance Issue Particular query
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))
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
-
Re-boot, it's the WinDoze way.
Originally Posted by 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.
Here is the problem:
"We were on Unix ... we went to WinDoze"
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Look at this part
"INDEX RANGE SCAN CASHALLOCATIONAUD3" It's somehow screwed up in Windows.
-
Do you still have the old environment available?... compare explain plans.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks Guys.. I removed the index being used and it sped up by using a different one.. strange..
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
-
Originally Posted by Sureshy
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.
Last edited by PAVB; 06-03-2009 at 09:46 AM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|