1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Performance issue reg

Discussion in 'SQL PL/SQL' started by laxman, Apr 15, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi all,
    i did explain plan for the below mentioned query ,the status of which is also here.if anybody can help me out to tune this query please.

    Code (SQL):
    sql>DESC srequest
    Name NULL TYPE
    --------

    REQUESTID NOT NULL NUMBER(10)
    LASTMODDATE DATE
    MINITEAMID NOT NULL NUMBER(10)
    STATUSCODE NOT NULL NUMBER(6)
    REQUESTTYPECODE NOT NULL NUMBER(6)
    ASSIGNED_PERSONID NUMBER(20)
    PRIORITYCODE NOT NULL NUMBER(6)

    sql>DESC person
    Name NULL TYPE
    --------

    PERSONID NOT NULL NUMBER(20)

    sql>DESC QueuePersonPref
    Name NULL TYPE
    --------

    PERSONID NOT NULL NUMBER(20)
    MINITEAMID NOT NULL NUMBER(10)
    sql>DESC Auto_MiniTeam
    Name NULL TYPE

    --------

    MINITEAMID NOT NULL NUMBER

    EXPLAIN plan FOR
    SELECT r.requestID requestID, DECODE(r.PriorityCode,2,1,2) priority,
    NVL(p.CUSTOMERPRIORITYCODE,3) CUSTOMERPRIORITYCODE,
    qpp.managerID managerID,
    qpp.priority queuePriority,
    r.lastModDate lastModDate,
    r.miniTeamID miniTeamID
    FROM SRequest r, Person P, QueuePersonPref qpp, Auto_MiniTeam am
    WHERE r.requestTypeCode = 1
    AND
    (
    (
    r.statusCode = 1
    AND r.assigned_PersonID IS NOT NULL
    AND r.lastModDate < (sysdate - 8/24)
    )
    OR
    (
    r.statusCode IN (2, 3)
    AND r.lastModDate < (sysdate - 1)
    AND r.assigned_PersonID IS NULL
    )
    OR
    (
    r.statusCode IN (2, 3)
    AND r.lastModDate < (sysdate - 14)
    )
    )
    AND r.miniTeamID IN (
    SELECT childMiniTeamID
    FROM MiniTeamTree
    WHERE childMiniTeamID = r.miniTeamID
    AND parentMiniTeamID = qpp.MiniteamID
    AND levelFromParent = 1
    )
    AND NVL(r.customerPersonID,24497) = p.personID
    AND am.miniTeamID = qpp.miniTeamID
    AND qpp.personID = 38530953
    AND EXISTS ( SELECT 1 FROM ERPerson er
    WHERE er.personID = 38530953
    AND er.miniTeamID = r.miniTeamID
    AND er.securityLevelCode IN (5, 7, 8)
    )
    AND r.requestID NOT IN (SELECT requestID FROM QueueRequest_TMP)
    ORDER BY 2, 3, managerID DESC, queuePriority, lastModDate

    Id   Operation                     Name  ROWS    Bytes   Cost

    0    SELECT STATEMENT               42736   4340K    96925
    1    SORT ORDER BY                      42736   4340K    96925
    2    NESTED LOOPS OUTER             42736   4340K    95905
    3    HASH JOIN                              42736   4006K    23756
    4    INDEX FULL SCAN     AUTO_MINITEAM_PK    14  70      1
    5    HASH JOIN RIGHT ANTI               15263    1356K   23754
    6    INDEX FAST FULL SCAN    PK_QUEUEREQUEST_TMP     1   7   2
    7    HASH JOIN RIGHT SEMI              15263     1252K   23751
    8    VIEW    INDEX$_join$_006             175    2450    11
    9    HASH JOIN               
    10   INDEX RANGE SCAN    ERPERSON_PK     175     2450    5
    11   INLIST ITERATOR                 
    12   INDEX RANGE SCAN    ERPERSON_K1     175     2450    10503
    13   TABLE ACCESS BY INDEX ROWID     SREQUEST    6312    228K    21058
    14   NESTED LOOPS                                             30718  2099K   23740
    15   NESTED LOOPS                                                   5               165   23
    16   TABLE ACCESS FULL   QUEUEPERSONPREF        2                40   21
    17   INDEX RANGE SCAN    MINITEAMTREE_PK        2                 26        1
    18   INDEX RANGE SCAN    SREQUEST_K4            40962    665
    19   TABLE ACCESS BY INDEX ROWID     PERSON            1               8                2
    20   INDEX UNIQUE SCAN   PERSONID_UNIQUE_IDX   1           1
    regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Explain WHY this query needs to be 'tuned'.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear Sir,
    Basically this query taking too much time to send the requestid data to client side and hence effecting the performance. sometimes i am observing a message like "Transaction didn't completed in time.i am finding little bit difficulties to tune this query,earlier i have implemented this query with union clause to check lastmoddate data,then i removed union and modified it but still i am not seeing any improvement in this query.
    Sir, Kindly provide me with valuable information so that i can tune this query effectively.


    Regards
    Laxman
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    How do you know it's the query and not the network transport causing these problems? Using explain plan doesn't always provide the correct execution path and it doesn't provide statistics for the query in question. Run the query using autotrace and post the results; someone may get a better idea of what this query is doing, if it really needs tuning and, if so, what the best course of action might be.
     
  5. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Ok sir,
    let me try with autotrace and get back to you with complete details.

    Thanks n regards
    Laxman