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!

Tuning sql query reg.

Discussion in 'SQL PL/SQL' started by laxman, Mar 18, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear all,
    can you provide me some information related to basic steps to tune this query below,since i am new to the tuning concept.i have an urgent requirement to tune this query.
    need ur valuable support kindly.

    Thanks n regards
    Laxman
    Code (SQL):
    SELECT DECODE(r.PriorityCode,2,1,2) priority,
                                    NVL(p.CUSTOMERPRIORITYCODE,3) CUSTOMERPRIORITYCODE,
                                    qpp.managerID managerID,
                                    qpp.priority queuePriority,
                                    r.lastModDate lastModDate,
                                    r.requestID requestID,
                                    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.miniTeamID IN (
                                                        SELECT childMiniTeamID
                                                        FROM MiniTeamTree
                                                        WHERE childMiniTeamID = r.miniTeamID
                                                        AND parentMiniTeamID = qpp.MiniteamID
                                                        AND levelFromParent = 1
                                                     )
                                 AND r.lastModDate < (l_sysDate - 8/24)
                                 AND NVL(r.customerPersonID,24497) = p.personID (+)
                                 AND am.miniTeamID = qpp.miniTeamID
                                 AND qpp.personID = SonarSession.sPersonID
                                 AND EXISTS ( SELECT 1 FROM ERPerson er
                                                WHERE er.personID = SonarSession.spersonID
                                                AND er.miniTeamID = r.miniTeamID
                                                AND er.securityLevelCode IN (5, 7, 8)
                                            )
                                AND r.requestID NOT IN (SELECT requestID FROM QueueRequest_TMP)
                               
                              UNION
                             
                                -- Second set
                                SELECT DECODE(r.PriorityCode,2,1,2) priority,
                                        NVL(p.CUSTOMERPRIORITYCODE,3) CUSTOMERPRIORITYCODE,
                                        qpp.managerID managerID,
                                        qpp.priority queuePriority,
                                        r.lastModDate lastModDate,
                                        r.requestID requestID,
                                        r.miniTeamID miniTeamID
                                 FROM SRequest r, Person P, QueuePersonPref qpp, Auto_MiniTeam am
                                 WHERE r.requestTypeCode = 1
                                 AND r.statusCode IN (2, 3)
                                 AND r.assigned_PersonID IS NULL
                                 AND r.miniTeamID IN (
                                                        SELECT childMiniTeamID
                                                        FROM MiniTeamTree
                                                        WHERE childMiniTeamID = r.miniTeamID
                                                        AND parentMiniTeamID = qpp.MiniteamID
                                                        AND levelFromParent = 1
                                                     )
                                 AND r.lastModDate < (l_sysDate - 1)
                                 AND NVL(r.customerPersonID,24497) = p.personID (+)
                                 AND am.miniTeamID = qpp.miniTeamID
                                 AND qpp.personID = SonarSession.sPersonID
                                 AND EXISTS ( SELECT 1 FROM ERPerson er
                                                WHERE er.personID = SonarSession.spersonID
                                                AND er.miniTeamID = r.miniTeamID
                                                AND er.securityLevelCode IN (5, 7, 8)
                                            )
                                 AND r.requestID NOT IN (SELECT requestID FROM QueueRequest_TMP)
                                 
                               UNION
                               
                                -- Third Set
                                SELECT DECODE(r.PriorityCode,2,1,2) priority,
                                        NVL(p.CUSTOMERPRIORITYCODE,3) CUSTOMERPRIORITYCODE,
                                        qpp.managerID managerID,
                                        qpp.priority queuePriority,
                                        r.lastModDate lastModDate,
                                        r.requestID requestID,
                                        r.miniTeamID miniTeamID
                                 FROM SRequest r, Person P, QueuePersonPref qpp, Auto_MiniTeam am
                                 WHERE r.requestTypeCode = 1
                                 AND r.statusCode IN (2, 3)
                                 AND r.miniTeamID IN (
                                                        SELECT childMiniTeamID
                                                        FROM MiniTeamTree
                                                        WHERE childMiniTeamID = r.miniTeamID
                                                        AND parentMiniTeamID = qpp.MiniteamID
                                                        AND levelFromParent = 1
                                                     )
                                 AND r.lastModDate < (l_sysDate - 14)
                                 AND NVL(r.customerPersonID,24497) = p.personID (+)
                                 AND am.miniTeamID = qpp.miniTeamID
                                 AND qpp.personID = SonarSession.sPersonID
                                 AND EXISTS ( SELECT 1 FROM ERPerson er
                                                WHERE er.personID = SonarSession.spersonID
                                                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
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: tunning sql query reg.

    The first step is knowing WHY you 'need' to tune it. You haven't posted the current explain plan nor any query statistics nor any table/index DDL nor any sample data so it's difficult to say what needs to be tuned. Since we have no relevant information we can't help you much, if at all.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear Sir,
    I am not sure whether tunning is needed .Basically this query is inside one procedure which is being called from client application where a round trip call to this procedure is happening i.e at the same instance the procedure is being called n times.In this type of scenario can i expect any performance issue!!! .also kindly find the statistics of this query below.
    overall what could be the initial step i need to follow to verify whether tunning is needed or not.i am doing first time this kind of activity that is the confusion.
    waiting for your valuable suggestion ,I beg pardon if information provided are too huge for analyze.

    Thanks n regards
    Laxman

    Code (SQL):
    --- For srequest table
    SQL> SELECT dbms_metadata.get_ddl('TABLE','SREQUEST','SONAR') FROM DUAL;

    DBMS_METADATA.GET_DDL('TABLE','SREQUEST','SONAR')
    --------------------------------------------------------------------------------

      CREATE TABLE "SONAR"."SREQUEST"
       (    "REQUESTID" NUMBER(10,0) NOT NULL ENABLE,
        "REQUESTNAME" VARCHAR2(192) NOT NULL ENABLE,
        "REQUESTSUBJECT" VARCHAR2(4000) NOT NULL ENABLE,
        "FIRSTRESPONSEDATE" DATE,
        "ISFOLLOWUP" NUMBER(3,0) NOT NULL ENABLE,
        "CLOSEDDATE" DATE,
        "DUEDATE" DATE,
        "RECEIVEDDATE" DATE NOT NULL ENABLE,
        "ASSOCIATEDENGRIND" NUMBER(3,0) NOT NULL ENABLE,

    DBMS_METADATA.GET_DDL('TABLE','SREQUEST','SONAR')
    --------------------------------------------------------------------------------
        "CCLISTIND" NUMBER(3,0) NOT NULL ENABLE,
        "LASTMESSAGEDATE" DATE,
        "ENGRACTIONREQUESTEDIND" NUMBER(3,0) NOT NULL ENABLE,
        "RELATEDPRODUCTSIND" NUMBER(3,0) NOT NULL ENABLE,
        "RELATEDREQUESTSIND" NUMBER(3,0) NOT NULL ENABLE,
        "KNOWLEDGEBASEIND" NUMBER(3,0) NOT NULL ENABLE,
        "LASTMODUSERID" NUMBER(20,0),
        "LASTMODDATE" DATE,
        "RESPONSESENTIND" NUMBER(3,0) NOT NULL ENABLE,
        "REQUESTSUBJECTLOWER" VARCHAR2(765) NOT NULL ENABLE,
        "REQUESTNAMELOWER" VARCHAR2(192) NOT NULL ENABLE,

    DBMS_METADATA.GET_DDL('TABLE','SREQUEST','SONAR')
    --------------------------------------------------------------------------------
        "WORKTIMETORESOLVE" FLOAT(126),
        "CONTRACTLEVELCODE" NUMBER(10,0) NOT NULL ENABLE,
        "REPORTINGLEVELCODE" NUMBER(10,0) NOT NULL ENABLE,
        "CHANNELID" NUMBER(10,0) NOT NULL ENABLE,
        "MINITEAMID" NUMBER(10,0) NOT NULL ENABLE,
        "SECURITYLEVELCODE" NUMBER(6,0) NOT NULL ENABLE,
        "STATUSCODE" NUMBER(6,0) NOT NULL ENABLE,
        "REQUESTTYPECODE" NUMBER(6,0) NOT NULL ENABLE,
        "REQUESTCATEGORYCODE" NUMBER(6,0) NOT NULL ENABLE,
        "ASSIGNED_PERSONID" NUMBER(20,0),
        "ORIGINATOR_PERSONID" NUMBER(20,0) NOT NULL ENABLE,

    DBMS_METADATA.GET_DDL('TABLE','SREQUEST','SONAR')
    --------------------------------------------------------------------------------
        "PRIORITYCODE" NUMBER(6,0) NOT NULL ENABLE,
        "CSDTOPPRODISSUECODE" NUMBER(6,0) NOT NULL ENABLE,
        "SEVERITYCODE" NUMBER(10,0) NOT NULL ENABLE,
        "ATTACHEDFILESIND" NUMBER(3,0) NOT NULL ENABLE,
        "ATTACHEDFILESPATH" VARCHAR2(240) NOT NULL ENABLE,
        "CONTRACT" VARCHAR2(60) NOT NULL ENABLE,
        "WORKTIMETOCLOSE" FLOAT(126),
        "ER_PRIORITYCODE" NUMBER(6,0) NOT NULL ENABLE,
        "ATTACHEDFILENAME" VARCHAR2(4000),
        "SECUREMINITEAMID" NUMBER(10,0),
        "ESCALATION_CONTACTPERSONID" NUMBER(20,0),

    DBMS_METADATA.GET_DDL('TABLE','SREQUEST','SONAR')
    --------------------------------------------------------------------------------
        "ISBILLABLE" NUMBER(3,0),
        "CUSTOMERPERSONID" NUMBER(20,0),
        "SERVERID" NUMBER(10,0),
        "SUBTOPICID" NUMBER(10,0),
        "ORIGINATOREMAILADDRESSID" NUMBER(10,0),
        "CUSTOMEREMAILADDRESSID" NUMBER(10,0),
        "SENDEREMAILADDRESSID" NUMBER(10,0),
        "HASBEENREAD" NUMBER(1,0) NOT NULL ENABLE,
        "ACKSENTDATE" DATE,
        "DOLLARIMPACT" NUMBER(12,2),
        "RFANUMBER" VARCHAR2(90),

    DBMS_METADATA.GET_DDL('TABLE','SREQUEST','SONAR')
    --------------------------------------------------------------------------------
        "BUNDLEID" VARCHAR2(96),
        "ASSETID" NUMBER DEFAULT 0 NOT NULL ENABLE,
        "ENGINEERING_COST" NUMBER,
        "OPS_COST" NUMBER,
        "AA_COST" NUMBER,
        "OTHER_COST" NUMBER,
        "RECOVERED_COST" NUMBER,
        "GLOBALISSUE" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
        "ENC_COUNT" NUMBER(4,0) DEFAULT 0,
        "ROOTCAUSEID" NUMBER,
        "ROOTCAUSENM" VARCHAR2(1000),

    DBMS_METADATA.GET_DDL('TABLE','SREQUEST','SONAR')
    --------------------------------------------------------------------------------
        "SUBROOTCAUSEID" NUMBER,
        "SUBROOTCAUSENM" VARCHAR2(1000),
        "OWNER_PERSONID" NUMBER(20,0),
        "RATINGS" NUMBER(2,0),
        "SENDANDCLOSEIND" NUMBER(1,0),
        "ISCONVERTED_REQUEST" NUMBER(1,0),
        "CONVERTED_REQUESTID" NUMBER(10,0),
        "TECHNICALRATINGS" NUMBER(2,0),
        "NONTECHNICALRATINGS" NUMBER(2,0),
        "PRODUCTNAME" VARCHAR2(2048),
        "PRODUCTCOMPONENT" VARCHAR2(2048),

    DBMS_METADATA.GET_DDL('TABLE','SREQUEST','SONAR')
    --------------------------------------------------------------------------------
        "PRODUCTISSUE" VARCHAR2(2048),
        "PRODUCTSUBISSUE" VARCHAR2(2048),
        "BULKRESPONDEDIND" NUMBER(1,0),
        "ENCFIESIZE" NUMBER,
        "AGING_NOTIFICATION_SENT_DATE" DATE,
         CONSTRAINT "SREQUEST_K8" UNIQUE ("ASSIGNED_PERSONID", "STATUSCODE", "REQUESTID
    "
    )
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 10485760 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "INDEX_TS"  ENABLE,

    DBMS_METADATA.GET_DDL('TABLE','SREQUEST','SONAR')
    --------------------------------------------------------------------------------
         CONSTRAINT "SREQUEST_PK" UNIQUE ("REQUESTID")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 5242880 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "INDEX_TS"  ENABLE,
         CONSTRAINT "SREQUEST_K3" UNIQUE ("MINITEAMID", "STATUSCODE", "REQUESTID")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 6291456 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "INDEX_TS"  ENABLE
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

    DBMS_METADATA.GET_DDL('TABLE','SREQUEST','SONAR')
    --------------------------------------------------------------------------------
      STORAGE(INITIAL 125829120 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "DATA_TS"

    --  FOR PERSON TABLE
    SQL> SELECT dbms_metadata.get_ddl('TABLE','PERSON','SONAR') FROM dual;


    DBMS_METADATA.GET_DDL('TABLE','PERSON','SONAR')
    --------------------------------------------------------------------------------

      CREATE TABLE "SONAR"."PERSON"
       (    "PERSONID" NUMBER(20,0) NOT NULL ENABLE,
        "FIRSTNAME" VARCHAR2(180) NOT NULL ENABLE,
        "LASTNAME" VARCHAR2(180) NOT NULL ENABLE,
        "PHONENUM" VARCHAR2(765),
        "USERACCOUNT" NUMBER(10,0),
        "EMPLOYEENUM" NUMBER(10,0),
        "CONTRACTORNUM" NUMBER(10,0),
        "COMPANYNAME" VARCHAR2(735),
        "ZIP" VARCHAR2(30),

    DBMS_METADATA.GET_DDL('TABLE','PERSON','SONAR')
    --------------------------------------------------------------------------------
        "STREET" VARCHAR2(210),
        "STATE" VARCHAR2(54),
        "MAILSTOP" VARCHAR2(21),
        "CITY" VARCHAR2(75),
        "ISCONTRACTOR" NUMBER(3,0),
        "ISEXTERNAL" NUMBER(3,0),
        "PROVINCE" VARCHAR2(150),
        "TITLE" VARCHAR2(90),
        "COUNTRYCODE" NUMBER(10,0),
        "FIRSTNAMELOWER" VARCHAR2(180) NOT NULL ENABLE,
        "LASTNAMELOWER" VARCHAR2(180) NOT NULL ENABLE,

    DBMS_METADATA.GET_DDL('TABLE','PERSON','SONAR')
    --------------------------------------------------------------------------------
        "EMAILADDRESS" VARCHAR2(735),
        "DATASOURCEKEY" NUMBER(10,0),
        "FAXNUMBER" VARCHAR2(90),
        "RENEWDATE" DATE,
        "PROGRAM" VARCHAR2(60),
        "PROGRAMSTATUS" VARCHAR2(75),
        "STREET2" VARCHAR2(210),
        "CONTACT_PERSONID" NUMBER(20,0),
        "MANAGER_PERSONID" NUMBER(20,0),
        "DEPTID" NUMBER(10,0),
        "DATASOURCEID" NUMBER(10,0) NOT NULL ENABLE,

    DBMS_METADATA.GET_DDL('TABLE','PERSON','SONAR')
    --------------------------------------------------------------------------------
        "COMPANYID" NUMBER(10,0) NOT NULL ENABLE,
        "BUSINESSRELATIONSHIPID" NUMBER(10,0),
        "ERENGINEERNUMBER" NUMBER(10,0),
        "TIMEZONE" NUMBER(4,2),
        "CPERSONID" NUMBER(20,0),
        "PTYPE" CHAR(1),
        "LASTMODDATE" DATE DEFAULT sysdate,
        "CUSTOMERPRIORITYCODE" NUMBER(1,0),
        "PEMAILADDRESSID" NUMBER(10,0),
        "SEMAILADDRESSID" NUMBER(10,0),
         CONSTRAINT "PERSON_K10" UNIQUE ("CONTRACTORNUM", "PERSONID")

    DBMS_METADATA.GET_DDL('TABLE','PERSON','SONAR')
    --------------------------------------------------------------------------------
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 5242880 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "INDEX_TS"  ENABLE,
         CONSTRAINT "PERSON_PK" PRIMARY KEY ("PERSONID")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "DATA_TS"  ENABLE
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 31457280 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

    DBMS_METADATA.GET_DDL('TABLE','PERSON','SONAR')
    --------------------------------------------------------------------------------
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "DATA_TS"

    --FOR QUEUEPERSONPREF TABLE
    SQL> SELECT dbms_metadata.get_ddl('TABLE','QUEUEPERSONPREF','SONAR') FROM dual;

    DBMS_METADATA.GET_DDL('TABLE','QUEUEPERSONPREF','SONAR')
    --------------------------------------------------------------------------------

      CREATE TABLE "SONAR"."QUEUEPERSONPREF"
       (    "MANAGERID" NUMBER(10,0) NOT NULL ENABLE,
        "PERSONID" NUMBER(20,0) NOT NULL ENABLE,
        "MINITEAMID" NUMBER(10,0) NOT NULL ENABLE,
        "QUEUENAME" VARCHAR2(90) NOT NULL ENABLE,
        "ACTIVE" NUMBER(1,0) NOT NULL ENABLE,
        "PRIORITY" NUMBER(2,0) NOT NULL ENABLE,
        "LASTMODPERSONID" NUMBER(20,0),
        "LASTMODDATE" DATE,
         CONSTRAINT "PK_QUEUEPERSONPREF" PRIMARY KEY ("MANAGERID", "PERSONID", "MINITEA

    DBMS_METADATA.GET_DDL('TABLE','QUEUEPERSONPREF','SONAR')
    --------------------------------------------------------------------------------
    MID"
    )
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "INDEX_TS"  ENABLE
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "DATA_TS"


    Code (SQL):
    SQL>  SELECT * FROM TABLE(dbms_xplan.display())
      2  /

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    --------------------------------------------------------------------------------------------------
    | Id  | Operation                | Name          | ROWS  | Bytes | Cost  |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |               |     7 |   719 |    69 |
    |   1 |  SORT UNIQUE             |               |     7 |   719 |    68 |
    |   2 |   UNION-ALL              |               |   |   |               |
    |   3 TABLE ACCESS BY INDEX ROWI |QUEUEPERSONPREF|     1 |    20 |     2 |
    |   4 |     NESTED LOOPS         |               |     2 |   208 |    21 |
    |   5 |      MERGE JOIN CARTESIAN        |               |     5 |   420 |    11 |
    |   6 |       NESTED LOOPS OUTER         |               |     1 |    71 |    10 |
    |   7 |        NESTED LOOPS ANTI         |               |     1 |    63 |     8 |
    |   8 |     NESTED LOOPS             |               |     1 |    57 |     8 |
    |   9 |      NESTED LOOPS            |               |     1 |    27 |     3 |
    |  10 |       INDEX UNIQUE SCAN      | MINITEAMTREE_PK       |     1 |    13 |     1 |
    |  11 |   TABLE ACCESS BY INDEX ROWID| ERPERSON          |     1 |    14 |     2 |
    |  12 |        INDEX UNIQUE SCAN     | ERPERSON_PK       |     1 |   |     1 |
    |  13 |  TABLE ACCESS BY INDEX ROWID | SREQUEST          |     1 |    30 |     5 |
    |  14 |       INDEX RANGE SCAN       | SREQUEST_K26      |     1 |   |     4 |
    |  15 |     INDEX UNIQUE SCAN        | QUEUEREQUESTTMP_REQUESTID |     1 |     6 |     0 |
    |  16 |        TABLE ACCESS BY INDEX ROWID   | PERSON            |     1 |     8 |     2 |
    |  17 |     INDEX UNIQUE SCAN        | PERSONID_UNIQUE_IDX   |     1 |   |     1 |
    |  18 |       BUFFER SORT            |               |     5 |    65 |     9 |
    |  19 |        INDEX FULL SCAN           | AMINITEAMID_PK        |     5 |    65 |     1 |
    |  20 |      INDEX RANGE SCAN            | QUEUEPERSONPREF_X1    |     1 |   |     1 |
    |  21 |    TABLE ACCESS BY INDEX ROWID       | QUEUEPERSONPREF       |     1 |    20 |     2 |
    |  22 |     NESTED LOOPS             |               |     2 |   208 |    21 |
    |  23 |      MERGE JOIN CARTESIAN        |               |     5 |   420 |    11 |
    |  24 |       NESTED LOOPS OUTER         |               |     1 |    71 |    10 |
    |  25 |        NESTED LOOPS ANTI         |               |     1 |    63 |     8 |
    |  26 |     NESTED LOOPS             |               |     1 |    57 |     8 |
    |  27 |      NESTED LOOPS            |               |     1 |    27 |     3 |
    |  28 |       INDEX UNIQUE SCAN      | MINITEAMTREE_PK       |     1 |    13 |     1 |
    |  29 |       TABLE ACCESS BY INDEX ROWID| ERPERSON          |     1 |    14 |     2 |
    |  30 |        INDEX UNIQUE SCAN         | ERPERSON_PK       |     1 |   |     1 |
    |  31 |      INLIST ITERATOR         |               |   |   |   |
    |  32 |       TABLE ACCESS BY INDEX ROWID| SREQUEST          |     1 |    30 |     5 |
    |  33 |        INDEX RANGE SCAN      | SREQUEST_K4       |     1 |   |     4 |
    |  34 |     INDEX UNIQUE SCAN        | QUEUEREQUESTTMP_REQUESTID |     1 |     6 |     0 |
    |  35 |        TABLE ACCESS BY INDEX ROWID   | PERSON            |     1 |     8 |     2 |
    |  36 |     INDEX UNIQUE SCAN        | PERSONID_UNIQUE_IDX   |     1 |   |     1 |
    |  37 |       BUFFER SORT            |               |     5 |    65 |     9 |
    |  38 |        INDEX FULL SCAN           | AMINITEAMID_PK        |     5 |    65 |     1 |
    |  39 |      INDEX RANGE SCAN            | QUEUEPERSONPREF_X1    |     1 |   |     1 |
    |  40 |    TABLE ACCESS BY INDEX ROWID       | QUEUEPERSONPREF       |     1 |    20 |     2 |
    |  41 |     NESTED LOOPS             |               |     3 |   303 |    23 |
    |  42 |      MERGE JOIN CARTESIAN        |               |     6 |   486 |    11 |
    |  43 |       NESTED LOOPS OUTER         |               |     1 |    68 |    10 |
    |  44 |        NESTED LOOPS ANTI         |               |     1 |    60 |     8 |
    |  45 |     NESTED LOOPS             |               |     1 |    54 |     8 |
    |  46 |      NESTED LOOPS            |               |     1 |    27 |     3 |
    |  47 |       INDEX UNIQUE SCAN      | MINITEAMTREE_PK       |     1 |    13 |     1 |
    |  48 |       TABLE ACCESS BY INDEX ROWID| ERPERSON          |     1 |    14 |     2 |
    |  49 |        INDEX UNIQUE SCAN         | ERPERSON_PK       |     1 |   |     1 |
    |  50 |      INLIST ITERATOR         |               |   |   |   |
    |  51 |       TABLE ACCESS BY INDEX ROWID| SREQUEST          |     1 |    27 |     5 |
    |  52 |        INDEX RANGE SCAN      | SREQUEST_K4       |     1 |   |     4 |
    |  53 |     INDEX UNIQUE SCAN        | QUEUEREQUESTTMP_REQUESTID |     1 |     6 |     0 |
    |  54 |        TABLE ACCESS BY INDEX ROWID   | PERSON            |     1 |     8 |     2 |
    |  55 |     INDEX UNIQUE SCAN        | PERSONID_UNIQUE_IDX   |     1 |   |     1 |
    |  56 |       BUFFER SORT            |               |     5 |    65 |     9 |
    |  57 |        INDEX FULL SCAN           | AMINITEAMID_PK        |     5 |    65 |     1 |
    |  58 |      INDEX RANGE SCAN            | QUEUEPERSONPREF_X1    |     1 |   |     1 |
    --------------------------------------------------------------------------------------------------

    Note
    -----
       - 'PLAN_TABLE' IS OLD version

    68 ROWS selected.

    Elapsed: 00:00:03.46