DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: *** Very Strange Problem ***

  1. #1
    Join Date
    Mar 2002
    Posts
    171

    Red face

    When I execute this Query, its pretty fast. It takes just a second.

    SELECT
    count(*)
    FROM TSW_CODES CD1,
    TSW_CODES CD2,
    TSW_CODES CD3,
    TSW_CODES CD4,
    TSW_CODES CD5,
    TSW_CODES CD6,
    TSW_CODES CD7,
    TSW_PERSONNEL EMP,
    EAM_COMPONENTS CMP,
    EWM_WO_AUDITS WOA
    WHERE ( CD1.CODE_ID (+) = WOA.WO_STATUS_ID ) and
    ( CD2.CODE_ID (+) = WOA.ACTION_ID ) and
    ( CD3.CODE_ID (+) = WOA.CATEGORY_ID ) and
    ( CD4.CODE_ID (+) = WOA.AREA_ID ) and
    ( CD5.CODE_ID (+) = WOA.ASSET_STATUS_ID ) and
    ( CD6.CODE_ID (+) = WOA.PRIORITY_ID ) and
    ( CD7.CODE_ID (+) = WOA.SHUTDOWN_ID ) and
    ( EMP.EMP_NO = WOA.EMP_NO ) and
    ( CMP.SERIAL_ID (+) = WOA.COMPONENT_SERIAL_ID) and
    WOA.WORK_ORDER_NO = '02-000695-000'
    ORDER BY WOA.DATE_TIME DESC ;





    But the same Command if I choose columns instead of count(*), it takes 5-6 minutes. Heres the SQL:

    SELECT
    CD1.USER_CODE WO_STATUS_CODE,
    CD2.USER_CODE ACTION_CODE,
    CD3.USER_CODE CATEGORY_CODE ,
    CD4.USER_CODE AREA_CODE,
    CD5.USER_CODE ASSET_STATUS_CODE ,
    CD6.USER_CODE WO_PRIORITY_CODE,
    CD7.USER_CODE SHUTDOWN_CODE
    FROM TSW_CODES CD1,
    TSW_CODES CD2,
    TSW_CODES CD3,
    TSW_CODES CD4,
    TSW_CODES CD5,
    TSW_CODES CD6,
    TSW_CODES CD7,
    TSW_PERSONNEL EMP,
    EAM_COMPONENTS CMP,
    EWM_WO_AUDITS WOA
    WHERE ( CD1.CODE_ID (+) = WOA.WO_STATUS_ID ) and
    ( CD2.CODE_ID (+) = WOA.ACTION_ID ) and
    ( CD3.CODE_ID (+) = WOA.CATEGORY_ID ) and
    ( CD4.CODE_ID (+) = WOA.AREA_ID ) and
    ( CD5.CODE_ID (+) = WOA.ASSET_STATUS_ID ) and
    ( CD6.CODE_ID (+) = WOA.PRIORITY_ID ) and
    ( CD7.CODE_ID (+) = WOA.SHUTDOWN_ID ) and
    ( EMP.EMP_NO = WOA.EMP_NO ) and
    ( CMP.SERIAL_ID (+) = WOA.COMPONENT_SERIAL_ID) and
    WOA.WORK_ORDER_NO = '02-000695-000'
    ORDER BY WOA.DATE_TIME DESC ;







    I checked for Chained Rows. No chained rows. I checked for block fragments. No block fragments. What could be the problem. Please advise as it is very urgent for the next release we have here.


  2. #2
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    Did you check explain plan?
    If you check it you will see that in first case you access only index blocks in second case you access index & data blocks to return result.


    [Edited by kgb on 04-08-2002 at 06:33 AM]
    Best wishes!
    Dmitri

  3. #3
    Join Date
    Mar 2002
    Posts
    171
    Okay. But how does this help me now? I even re-built all the indexes. Nothing seems to help. How to trouble shoot this problem.

    Another thing: The same query performs very fast on another server with similar data. Infact, the other database fetches more rows than this one. Its all very confusing. I see the same indexes created on the tables on both the databases. Please help.

    [Edited by dbafreak on 04-08-2002 at 06:39 AM]

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    do you run it in local?

  5. #5
    Join Date
    Mar 2002
    Posts
    171
    Originally posted by pando
    do you run it in local?
    I am running this from the client. From SQL * PLUS.

  6. #6
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    Does your servers hace the same configurations?
    Did you try to review indexes, I mean WOA.WORK_ORDER_NO = '02-000695-000' looks like a number, you could decrease index size around in 3,5 times, if you use number instead of char.
    Best wishes!
    Dmitri

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    how about try run it in local with both query, count(*) and columns and compare

  8. #8
    Join Date
    Mar 2002
    Posts
    171
    Originally posted by pando
    how about try run it in local with both query, count(*) and columns and compare
    Okay. Let me try that. Meanwhile here's what EXPLAIN PLAN outputted into the PLAN_TABLE:

    ID PARENT_ID OPERATION SUBSTR(OBJECT_NAME,1,25)
    ---------- ---------- ------------------------------------------------------------ -----------------
    0 SELECT STATEMENT
    1 0 NESTED LOOPS OUTER
    2 1 NESTED LOOPS OUTER
    3 2 NESTED LOOPS OUTER
    4 3 NESTED LOOPS OUTER
    5 4 NESTED LOOPS OUTER
    6 5 NESTED LOOPS OUTER
    7 6 NESTED LOOPS OUTER
    8 7 NESTED LOOPS
    9 8 NESTED LOOPS OUTER
    10 9 TABLE ACCESS BY INDEX ROWID EWM_WO_AUDITS
    11 10 INDEX RANGE SCAN DESCENDING EWM_CON_WOU_PK
    12 9 TABLE ACCESS BY INDEX ROWID TSW_CODES
    13 12 INDEX UNIQUE SCAN TSW_CON_COD_PK
    14 8 TABLE ACCESS BY INDEX ROWID TSW_PERSONNEL
    15 14 INDEX UNIQUE SCAN TSW_CON_EMP_PK
    16 7 TABLE ACCESS BY INDEX ROWID TSW_CODES
    17 16 INDEX UNIQUE SCAN TSW_CON_COD_PK
    18 6 TABLE ACCESS BY INDEX ROWID TSW_CODES
    19 18 INDEX UNIQUE SCAN TSW_CON_COD_PK
    20 5 TABLE ACCESS BY INDEX ROWID EAM_COMPONENTS
    21 20 INDEX UNIQUE SCAN EAM_CON_CMP_PK
    22 4 TABLE ACCESS BY INDEX ROWID TSW_CODES
    23 22 INDEX UNIQUE SCAN TSW_CON_COD_PK
    24 3 TABLE ACCESS BY INDEX ROWID TSW_CODES
    25 24 INDEX UNIQUE SCAN TSW_CON_COD_PK
    26 2 TABLE ACCESS BY INDEX ROWID TSW_CODES
    27 26 INDEX UNIQUE SCAN TSW_CON_COD_PK
    28 1 TABLE ACCESS BY INDEX ROWID TSW_CODES
    29 28 INDEX UNIQUE SCAN TSW_CON_COD_PK

  9. #9
    Join Date
    Mar 2002
    Posts
    171
    Pando: I tried from the Server. Same problem. COunt takes a second and colums fetch takes about 4-5 minutes.

  10. #10
    Join Date
    Mar 2002
    Posts
    171
    Originally posted by kgb
    Does your servers hace the same configurations?
    Did you try to review indexes, I mean WOA.WORK_ORDER_NO = '02-000695-000' looks like a number, you could decrease index size around in 3,5 times, if you use number instead of char.
    It is supposed to be a VARCHAR unfortunately.

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