Performance Issue Particular query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Performance Issue Particular query

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Talking Re-boot, it's the WinDoze way.

    Quote Originally Posted by Sureshy View Post
    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

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Look at this part
    "INDEX RANGE SCAN CASHALLOCATIONAUD3" It's somehow screwed up in Windows.
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    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.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Sureshy View Post
    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 10: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.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width