8 Replies Latest reply: Dec 24, 2013 2:53 AM by GeetaM RSS

    Tuning SQL Query

    GeetaM

      Hi Guru

       

      A user wants me to fine tune the below query, when i have executed the below query it has  executed in 2 mins for me.

      when i looked into the code i feel that using CAST , CASE is causing slowness inthe query ...kindly correct me if I am wrong?

      Also how do i generate better sql plan for the below query in 12c.

       

      Thanks in advance.

       

       

      /*SELECT

        CAST(cm.SITE_ID AS varchar(3)) ICOMS_SITE_ID

      , cm.ACCOUNT_NUMBER

      , ccea.n_SCHEME_ID

      , ccea.n_EVENT_ID

      , UPPER(REPLACE(cm.FIRST_NAME,',',' ')) FIRST_NAME

      , UPPER(REPLACE(cm.LAST_NAME,',',' ')) LAST_NAME

      , CAST((cm.HOME_AREA_CODE || cm.HOME_EXCHANGE_NUMBER || LPAD(cm.HOME_TELEPHONE_NUMBER,4,0)) AS INTEGER) HOME_NUM

      , CAST((cm.OTHER_AREA_CODE || cm.OTHER_EXCHANGE_NUMBER || LPAD(cm.OTHER_PHONE,4,0)) AS INTEGER) OTHER_NUM

      , CAST((cm.BUSINESS_AREA_CODE || cm.BUSINESS_EXCHANGE_NUMBER || LPAD(cm.BUSINESS_EXCHANGE_NUMBER,4,0)) AS INTEGER) BUS_NUM

      , CAST(NVL((ct.NPASTD_NUMBER || ct.NXXEXCHANGE_NUMBER || LPAD(ct.THOUSAND_NUMBER,4,0)),0) AS INTEGER) SERVICE_NUM

      , REPLACE(UPPER(LTRIM(hm.ADDR_LOCATION) || ' ' || hm.PRE_DIRECTIONAL || ' ' || hm.STREET),'   ',' ') HM_ADDRESS1

      , hm.ADDR_CITY

      , hm.ADDR_STATE

      , hm.ADDR_ZIP_5

      , cm.COMPANY_NUMBER

      , cm.DIVISION_NUMBER

      , cm.FRANCHISE_NUMBER

      , r.CURRENT_AR_BALANCE

      , r.AR_1_TO_30

      , r.AR_31_TO_60

      , r.AR_61_TO_90

      , r.AR_91_TO_120

      , r.AR_121_TO_150

      , r.AR_OVER_150_DAYS

      , r.AR_BALANCE

      , TO_DATE(TO_CHAR(ccea.n_ACTION_DATE+19000000),'YYYY/MM/DD') n_ACTION_DATE

      , cm.EXTERNAL_CREDIT_SCORE

      , cm.CUSTOMER_CATEGORY

      , cm.CUSTOMER_TYPE_CODE

      , cm.LANGUAGE_CODE || ccea.n_EVENT_ID REPORT_NAME

      , UPPER(REPLACE(cd.SPOUSE_OR_ROOM_MATE,',',' ')) SPOUSE_OR_ROOM_MATE

      , UPPER(REPLACE(cd.RELATIVES_NAME,',',' ')) RELATIVES_NAME

      , cs.CUST_SRV_STMT_STATUS

      , ccea.STATEMENT_CODE

      , ccea.n_EVENT_STATUS

      , r.SERVICE_CATEGORY_CODE

      , DATE_FORMAT(cm.CONNECT_DATE)  CONNECT_DATE

      , cm.LANGUAGE_CODE

      , cd.VCR_DATA_CODE PAYMENT_BLOCK

      , NULL CUSTOM_A_50

      , NULL CUSTOM_B_50

      , NULL CUSTOM_C_50

      , NULL CUSTOMER_A_10

      , NULL CUSTOMER_B_10

      , NULL CUSTOMER_C_10

      , NULL COLL_ACT_CODE

      , cm.PIN_NUMBER

      , cs.INTERNAL_CREDIT_SCORE

      , NULL CUSTOM_A_1

      , NULL CUSTOM_B_1

      , CASE WHEN hm.BILL_TYPE_CODE != 'S' THEN 1 ELSE 2 END LOB

      FROM

        PSTAGE.PHX_CUSTOMER_MASTER cm

      LEFT OUTER JOIN PSTAGE.PHX_CUSTOMER_TELEPHONE ct

        ON cm.ACCOUNT_NUMBER = ct.ACCOUNT_NUMBER

        AND ct.PRIMADL = 'P'

        AND ct.CUST_TELEPHONE_STATUS = 'AC'

        AND ct.SERVICE_OCCURRENCE = '1'

        AND ct.SERVICE_CATEGORY_CODE = 'T'

      INNER JOIN

        PSTAGE.PHX_n_CST_EVENT_ACT ccea       

        ON cm.ACCOUNT_NUMBER = ccea.ACCOUNT_NUMBER

      INNER JOIN PSTAGE.PHX_HOUSE_MASTER hm

        ON cm.HOUSE_NUMBER = hm.HOUSE_NUMBER

      INNER JOIN PSTAGE.PHX_CUSTOMER_STATEMENTS cs

        ON ccea.STATEMENT_CODE = cs.STATEMENT_CODE

        AND cm.ACCOUNT_NUMBER = cs.ACCOUNT_NUMBER

      LEFT OUTER JOIN PSTAGE.PHX_CUSTOMER_DEMOGRAPHICS cd

        ON  cm.ACCOUNT_NUMBER = cd.ACCOUNT_NUMBER

      INNER JOIN

        (

          SELECT

            r.ACCOUNT_NUMBER,

            SUM(r.AR_BALANCE) AR_BALANCE,

            SUM(r.CURRENT_AR_BALANCE) CURRENT_AR_BALANCE,

            SUM(r.AR_1_TO_30) AR_1_TO_30,

            SUM(r.AR_31_TO_60) AR_31_TO_60,

            SUM(r.AR_61_TO_90) AR_61_TO_90,

            SUM(r.AR_91_TO_120) AR_91_TO_120,

            SUM(r.AR_121_TO_150) AR_121_TO_150,

            SUM(r.AR_OVER_150_DAYS) AR_OVER_150_DAYS,

            MAX(CASE WHEN r.SERVICE_CATEGORY_CODE = 'C' THEN 'C' ELSE NULL END) ||

              MAX(CASE WHEN r.SERVICE_CATEGORY_CODE = 'D' THEN 'D' ELSE NULL END) ||

              MAX(CASE WHEN r.SERVICE_CATEGORY_CODE = 'T' THEN 'T' ELSE NULL END) ||

              MAX(CASE WHEN r.SERVICE_CATEGORY_CODE = 'H' THEN 'H' ELSE NULL END) SERVICE_CATEGORY_CODE

          FROM PSTAGE.PHX_CUSTOMER_AR_AGING r

      GROUP BY

            r.ACCOUNT_NUMBER

        )  r

        ON cm.ACCOUNT_NUMBER = r.ACCOUNT_NUMBER

      WHERE

        ccea.n_ACTION_DATE = TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) - 19000000

      AND ccea.n_EVENT_STATUS = 'P';  */

        • 1. Re: Tuning SQL Query
          ranit B

          Hi Geeta,

           

          Couple of things to note before you start any tuning work:

           

          1. Understanding of the business objective behind the query.

          (Because if you know the business obj, it is very easy to write/tune the query by tightening the exact nuts-and-bolts i.e. query parts)

           

          2. Database version on which query is to be executed. ( select * from v$version )

          i feel that using CAST , CASE is causing slowness inthe query

          3. Why do you feel so?

          how do i generate better sql plan for the below query in 12c.

          4. What steps you have taken to help optimizer generate a better plan?

          (Oracle optimizer will generate a plan based on how well we instruct him to work and the inputs we give)

           

          5. Finally, before you post a query tuning request here, please read these -

          HOW TO: Post a SQL statement tuning request - template posting

          When your query takes too long ...

           

          Keep patience and read every line carefully. It's written in a very easy and understandable manner.

           

          Hope you understand what my words are trying to convey.

           

          -- Ranit

          • 2. Re: Tuning SQL Query
            GeetaM

            Hi Ranit...

             

            Thanks for guidance,i have generated the explain plan,tkprof ...

             

            {noformat}

             

            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------------------------------------------------------------------------------
            Plan hash value: 1243846765

            -----------------------------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                                  | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
            -----------------------------------------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT                           |             |   133K|    43M|       | 13612   (3)| 00:00:03 |        |      |            |
            |   1 |  PX COORDINATOR                            |             |       |       |       |            |          |        |      |            |
            |   2 |   PX SEND QC (RANDOM)                      | :TQ10009    |   133K|    43M|       | 13612   (3)| 00:00:03 |  Q1,09 | P->S | QC (RAND)  |
            |   3 |    HASH GROUP BY                           |             |   133K|    43M|    47M| 13612   (3)| 00:00:03 |  Q1,09 | PCWP |            |
            |   4 |     PX RECEIVE                             |             |   133K|    43M|       | 13612   (3)| 00:00:03 |  Q1,09 | PCWP |            |
            |   5 |      PX SEND HASH                          | :TQ10008    |   133K|    43M|       | 13612   (3)| 00:00:03 |  Q1,08 | P->P | HASH       |
            |   6 |       HASH GROUP BY                        |             |   133K|    43M|    47M| 13612   (3)| 00:00:03 |  Q1,08 | PCWP |            |
            |*  7 |        HASH JOIN                           |             |   133K|    43M|       | 12610   (3)| 00:00:03 |  Q1,08 | PCWP |            |
            |   8 |         PX RECEIVE                         |             | 81376 |    23M|       | 10319   (3)| 00:00:03 |  Q1,08 | PCWP |            |
            |   9 |          PX SEND BROADCAST                 | :TQ10007    | 81376 |    23M|       | 10319   (3)| 00:00:03 |  Q1,07 | P->P | BROADCAST  |
            |* 10 |           HASH JOIN OUTER BUFFERED         |             | 81376 |    23M|       | 10319   (3)| 00:00:03 |  Q1,07 | PCWP |            |
            |  11 |            PX RECEIVE                      |             | 81376 |    21M|       |  9791   (3)| 00:00:03 |  Q1,07 | PCWP |            |
            |  12 |             PX SEND HASH                   | :TQ10005    | 81376 |    21M|       |  9791   (3)| 00:00:03 |  Q1,05 | P->P | HASH       |
            |* 13 |              HASH JOIN                     |             | 81376 |    21M|       |  9791   (3)| 00:00:03 |  Q1,05 | PCWP |            |
            |  14 |               PX RECEIVE                   |             | 81376 |    16M|       |  8194   (4)| 00:00:02 |  Q1,05 | PCWP |            |
            |  15 |                PX SEND BROADCAST           | :TQ10004    | 81376 |    16M|       |  8194   (4)| 00:00:02 |  Q1,04 | P->P | BROADCAST  |
            |* 16 |                 HASH JOIN OUTER BUFFERED   |             | 81376 |    16M|       |  8194   (4)| 00:00:02 |  Q1,04 | PCWP |            |
            |  17 |                  PX RECEIVE                |             | 81376 |    13M|       |  6958   (4)| 00:00:02 |  Q1,04 | PCWP |            |
            |  18 |                   PX SEND HASH             | :TQ10002    | 81376 |    13M|       |  6958   (4)| 00:00:02 |  Q1,02 | P->P | HASH       |
            |* 19 |                    HASH JOIN               |             | 81376 |    13M|       |  6958   (4)| 00:00:02 |  Q1,02 | PCWP |            |
            |  20 |                     PX RECEIVE             |             | 86021 |    11M|       |  3646   (4)| 00:00:01 |  Q1,02 | PCWP |            |
            |  21 |                      PX SEND BROADCAST     | :TQ10001    | 86021 |    11M|       |  3646   (4)| 00:00:01 |  Q1,01 | P->P | BROADCAST  |
            |* 22 |                       HASH JOIN            |             | 86021 |    11M|       |  3646   (4)| 00:00:01 |  Q1,01 | PCWP |            |
            |  23 |                        PX RECEIVE          |             | 84068 |  3366K|       |   322   (3)| 00:00:01 |  Q1,01 | PCWP |            |
            |  24 |                         PX SEND BROADCAST  | :TQ10000    | 84068 |  3366K|       |   322   (3)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
            |  25 |                          PX BLOCK ITERATOR |             | 84068 |  3366K|       |   322   (3)| 00:00:01 |  Q1,00 | PCWC |            |
            |* 26 |                           TABLE ACCESS FULL| PHX_CBLKREP | 84068 |  3366K|       |   322   (3)| 00:00:01 |  Q1,00 | PCWP |            |
            |  27 |                        PX BLOCK ITERATOR   |             |  8643K|   849M|       |  3316   (3)| 00:00:01 |  Q1,01 | PCWC |            |
            |* 28 |                         TABLE ACCESS FULL  | PHX_CUMSTPF |  8643K|   849M|       |  3316   (3)| 00:00:01 |  Q1,01 | PCWP |            |
            |  29 |                     PX BLOCK ITERATOR      |             |  9261K|   256M|       |  3304   (4)| 00:00:01 |  Q1,02 | PCWC |            |
            |  30 |                      TABLE ACCESS FULL     | PHX_CBIZREP |  9261K|   256M|       |  3304   (4)| 00:00:01 |  Q1,02 | PCWP |            |
            |  31 |                  PX RECEIVE                |             |   544K|    21M|       |  1235   (2)| 00:00:01 |  Q1,04 | PCWP |            |
            |  32 |                   PX SEND HASH             | :TQ10003    |   544K|    21M|       |  1235   (2)| 00:00:01 |  Q1,03 | P->P | HASH       |
            |  33 |                    PX BLOCK ITERATOR       |             |   544K|    21M|       |  1235   (2)| 00:00:01 |  Q1,03 | PCWC |            |
            |* 34 |                     TABLE ACCESS FULL      | PHX_CMGTREP |   544K|    21M|       |  1235   (2)| 00:00:01 |  Q1,03 | PCWP |            |
            |  35 |               PX BLOCK ITERATOR            |             |  2388K|   136M|       |  1595   (2)| 00:00:01 |  Q1,05 | PCWC |            |
            |  36 |                TABLE ACCESS FULL           | PHX_HOSTPF  |  2388K|   136M|       |  1595   (2)| 00:00:01 |  Q1,05 | PCWP |            |
            |  37 |            PX RECEIVE                      |             |  3161K|    99M|       |   525   (3)| 00:00:01 |  Q1,07 | PCWP |            |
            |  38 |             PX SEND HASH                   | :TQ10006    |  3161K|    99M|       |   525   (3)| 00:00:01 |  Q1,06 | P->P | HASH       |
            |  39 |              PX BLOCK ITERATOR             |             |  3161K|    99M|       |   525   (3)| 00:00:01 |  Q1,06 | PCWC |            |
            |  40 |               TABLE ACCESS FULL            | PHX_DEMOGPF |  3161K|    99M|       |   525   (3)| 00:00:01 |  Q1,06 | PCWP |            |
            |  41 |         PX BLOCK ITERATOR                  |             |    13M|   436M|       |  2279   (3)| 00:00:01 |  Q1,08 | PCWC |            |
            |  42 |          TABLE ACCESS FULL                 | PHX_CBI0REP |    13M|   436M|       |  2279   (3)| 00:00:01 |  Q1,08 | PCWP |            |
            -----------------------------------------------------------------------------------------------------------------------------------------------

            Predicate Information (identified by operation id):
            ---------------------------------------------------

               7 - access("CUCNO"="I0CNBR")
              10 - access("CUCNO"="DSCNBR"(+))
              13 - access("CUHO#"="HONUM")
              16 - access("CUCNO"="GTCNBR"(+))
              19 - access("LKNUO9"="IZNUO9" AND "CUCNO"="IZCNBR")
              22 - access("CUCNO"="LKCNBR")
              26 - filter("LKSIGA"='P' AND "LKDDAB"=TO_NUMBER(TO_CHAR(SYSDATE@!,'YYYYMMDD'))-19000000)
              28 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"CUCNO"))
              34 - filter("GTSUFJ"(+)='AC' AND "GTSUFV"(+)='P' AND "GTSYP8"(+)=1 AND "GTCEK6"(+)='T')

            Note
            -----
               - dynamic sampling used for this statement (level=5)
               - automatic DOP: Computed Degree of Parallelism is 5 because of degree limit

            67 rows selected.

            {noformat}

            {code}

            SQL> show parameter user_dump_dest

            NAME                                 TYPE        VALUE
            ------------------------------------ ----------- ------------------------------
            user_dump_dest                       string      /u00/app/oracle/diag/rdbms/***/****/trace

             

            SQL> show parameter optimizer

            NAME                                 TYPE        VALUE
            ------------------------------------ ----------- ------------------------------
            _optimizer_cost_based_transformation string      ON
            _optimizer_push_pred_cost_based      boolean     FALSE
            optimizer_capture_sql_plan_baselines boolean     FALSE
            optimizer_dynamic_sampling           integer     2
            optimizer_features_enable            string      11.2.0.3
            optimizer_index_caching              integer     0
            optimizer_index_cost_adj             integer     100
            optimizer_mode                       string      ALL_ROWS
            optimizer_secure_view_merging        boolean     TRUE
            optimizer_use_invisible_indexes      boolean     FALSE
            optimizer_use_pending_statistics     boolean     FALSE

             

            SQL> show parameter db_file_multi

            NAME                                 TYPE        VALUE
            ------------------------------------ ----------- ------------------------------
            db_file_multiblock_read_count        integer     64

             

            SQL> show parameter db_block_size

            NAME                                 TYPE        VALUE
            ------------------------------------ ----------- ------------------------------
            db_block_size                        integer     16384

             

            SQL> show parameter cursor_sharing

            NAME                                 TYPE        VALUE
            ------------------------------------ ----------- ------------------------------
            cursor_sharing                       string      EXACT

             

            SQL> column sname format a20
            column pname format a20
            column pval2 format a20
            select sname,pname,pval1,pval2 from sys.aux_stats$;SQL> SQL> SQL>

            SNAME                PNAME                     PVAL1 PVAL2
            -------------------- -------------------- ---------- --------------------
            SYSSTATS_INFO        STATUS                          COMPLETED
            SYSSTATS_INFO        DSTART                          12-28-2012 23:00
            SYSSTATS_INFO        DSTOP                           12-29-2012 01:00
            SYSSTATS_INFO        FLAGS                         0
            SYSSTATS_MAIN        CPUSPEEDNW                 2869
            SYSSTATS_MAIN        IOSEEKTIM                    10
            SYSSTATS_MAIN        IOTFRSPEED                 4096
            SYSSTATS_MAIN        SREADTIM                  8.632
            SYSSTATS_MAIN        MREADTIM                 16.857
            SYSSTATS_MAIN        CPUSPEED                   2907
            SYSSTATS_MAIN        MBRC                         19
            SYSSTATS_MAIN        MAXTHR                598526976
            SYSSTATS_MAIN        SLAVETHR                7931904

            13 rows selected.

             

            {code}

             

             

            {noformat}

             

            TKPROF: Release 11.2.0.3.0 - Development on Tue Dec 24 01:14:06 2013

             

            Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

             

            Trace file: PSTAGE2_ora_6210.trc

             

            Sort options: default

             

            ********************************************************************************

             

            count = number of times OCI procedure was executed

             

            cpu = cpu time in seconds executing

             

            elapsed = elapsed time in seconds executing

             

            disk = number of physical reads of buffers from disk

             

            query = number of buffers gotten for consistent read

             

            current = number of buffers gotten in current mode (usually for update)

             

            rows = number of rows processed by the fetch or execute call

             

            ********************************************************************************

             

            SQL ID: 1a2g3z4rj1uq2 Plan Hash: 0

             

            alter session set sql_trace true

             

              

             

            call count cpu elapsed disk query current rows

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            Parse 0 0.00 0.00 0 0 0 0

             

            Execute 1 0.00 0.00 0 0 0 0

             

            Fetch 0 0.00 0.00 0 0 0 0

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            total 1 0.00 0.00 0 0 0 0

             

            Misses in library cache during parse: 0

             

            Misses in library cache during execute: 1

             

            Optimizer mode: ALL_ROWS

             

            Parsing user id: SYS

             

            ********************************************************************************

             

            SQL ID: grwydz59pu6mc Plan Hash: 3684871272

             

            select text

             

            from

             

            view$ where rowid=:1

             

              

             

            call count cpu elapsed disk query current rows

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            Parse 14 0.00 0.00 0 0 0 0

             

            Execute 14 0.00 0.00 0 0 0 0

             

            Fetch 14 0.00 0.00 2 28 0 14

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            total 42 0.00 0.01 2 28 0 14

             

            Misses in library cache during parse: 1

             

            Misses in library cache during execute: 1

             

            Optimizer mode: CHOOSE

             

            Parsing user id: SYS (recursive depth: 1)

             

            Number of plan statistics captured: 3

             

            Rows (1st) Rows (avg) Rows (max) Row Source Operation

             

            ---------- ---------- ---------- ---------------------------------------------------

             

            1 1 1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=1174 us cost=1 size=15 card=1)

             

            ********************************************************************************

             

            SQL ID: 5mq7qz8r3dcvg Plan Hash: 902152095

             

            SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE

             

            NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')

             

            NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)

             

            FROM

             

            (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("PHX_CMGTREP")

             

            FULL("PHX_CMGTREP") NO_PARALLEL_INDEX("PHX_CMGTREP") */ 1 AS C1, CASE WHEN

             

            "PHX_CMGTREP"."GTCEK6"='T' AND "PHX_CMGTREP"."GTSYP8"=1 AND

             

            "PHX_CMGTREP"."GTSUFJ"='AC' AND "PHX_CMGTREP"."GTSUFV"='P' THEN 1 ELSE 0

             

            END AS C2 FROM "PSTAGE"."PHX_CMGTREP" SAMPLE BLOCK (0.118794 , 1) SEED (1)

             

            "PHX_CMGTREP") SAMPLESUB

             

              

             

            call count cpu elapsed disk query current rows

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            Parse 3 0.00 0.00 0 0 0 0

             

            Execute 3 0.00 0.00 0 0 0 0

             

            Fetch 3 0.01 0.33 81 456 0 3

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            total 9 0.01 0.33 81 456 0 3

             

            Misses in library cache during parse: 1

             

            Optimizer mode: ALL_ROWS

             

            Parsing user id: SYS (recursive depth: 1)

             

            Number of plan statistics captured: 3

             

            Rows (1st) Rows (avg) Rows (max) Row Source Operation

             

            ---------- ---------- ---------- ---------------------------------------------------

             

            1 1 1 SORT AGGREGATE (cr=152 pr=27 pw=0 time=110359 us)

             

            4043 4043 4043 TABLE ACCESS SAMPLE PHX_CMGTREP (cr=152 pr=27 pw=0 time=2401 us cost=8 size=86963 card=3781)

             

            ********************************************************************************

             

            SQL ID: cp3jws7wccyuj Plan Hash: 4040798679

             

            SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE

             

            NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')

             

            NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)

             

            FROM

             

            (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("PHX_CBLKREP")

             

            FULL("PHX_CBLKREP") NO_PARALLEL_INDEX("PHX_CBLKREP") */ 1 AS C1, CASE WHEN

             

            "PHX_CBLKREP"."LKDDAB"=TO_NUMBER(TO_CHAR(SYSDATE@!,'YYYYMMDD'))-19000000

             

            AND "PHX_CBLKREP"."LKSIGA"='P' THEN 1 ELSE 0 END AS C2 FROM

             

            "PSTAGE"."PHX_CBLKREP" SAMPLE BLOCK (0.458449 , 1) SEED (1) "PHX_CBLKREP")

             

            SAMPLESUB

             

              

             

            call count cpu elapsed disk query current rows

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            Parse 3 0.00 0.00 0 0 0 0

             

            Execute 3 0.00 0.00 0 0 0 0

             

            Fetch 3 0.01 0.32 122 264 3 3

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            total 9 0.01 0.32 122 264 3 3

             

            Misses in library cache during parse: 1

             

            Optimizer mode: ALL_ROWS

             

            Parsing user id: SYS (recursive depth: 1)

             

            Number of plan statistics captured: 3

             

            Rows (1st) Rows (avg) Rows (max) Row Source Operation

             

            ---------- ---------- ---------- ---------------------------------------------------

             

            1 1 1 SORT AGGREGATE (cr=88 pr=41 pw=0 time=107173 us)

             

            7052 7052 7052 TABLE ACCESS SAMPLE PHX_CBLKREP (cr=88 pr=41 pw=0 time=3573 us cost=8 size=151000 card=7550)

             

            ********************************************************************************

             

            SELECT

             

            CAST(cm.SITE_ID AS varchar(3)) ICOMS_SITE_ID

             

            , cm.ACCOUNT_NUMBER

             

            , ccea.COLLECTION_SCHEME_ID

             

            , ccea.COLLECTION_EVENT_ID

             

            , UPPER(REPLACE(cm.FIRST_NAME,',',' ')) FIRST_NAME

             

            , UPPER(REPLACE(cm.LAST_NAME,',',' ')) LAST_NAME

             

            , CAST((cm.HOME_AREA_CODE || cm.HOME_EXCHANGE_NUMBER || LPAD(cm.HOME_TELEPHONE_NUMBER,4,0)) AS INTEGER) HOME_NUM

             

            , CAST((cm.OTHER_AREA_CODE || cm.OTHER_EXCHANGE_NUMBER || LPAD(cm.OTHER_PHONE,4,0)) AS INTEGER) OTHER_NUM

             

            , CAST((cm.BUSINESS_AREA_CODE || cm.BUSINESS_EXCHANGE_NUMBER || LPAD(cm.BUSINESS_EXCHANGE_NUMBER,4,0)) AS INTEGER) BUS_NUM

             

            , CAST(NVL((ct.NPASTD_NUMBER || ct.NXXEXCHANGE_NUMBER || LPAD(ct.THOUSAND_NUMBER,4,0)),0) AS INTEGER) SERVICE_NUM

             

            , REPLACE(UPPER(LTRIM(hm.ADDR_LOCATION) || ' ' || hm.PRE_DIRECTIONAL || ' ' || hm.STREET),' ',' ') HM_ADDRESS1

             

            , hm.ADDR_CITY

             

            , hm.ADDR_STATE

             

            , hm.ADDR_ZIP_5

             

            , cm.COMPANY_NUMBER

             

            , cm.DIVISION_NUMBER

             

            , cm.FRANCHISE_NUMBER

             

            , car.CURRENT_AR_BALANCE

             

            , car.AR_1_TO_30

             

            , car.AR_31_TO_60

             

            , car.AR_61_TO_90

             

            , car.AR_91_TO_120

             

            , car.AR_121_TO_150

             

            , car.AR_OVER_150_DAYS

             

            , car.AR_BALANCE

             

            , TO_DATE(TO_CHAR(ccea.COLLECTION_ACTION_DATE+19000000),'YYYY/MM/DD') COLLECTION_ACTION_DATE

             

            , cm.EXTERNAL_CREDIT_SCORE

             

            , cm.CUSTOMER_CATEGORY

             

            , cm.CUSTOMER_TYPE_CODE

             

            , cm.LANGUAGE_CODE || ccea.COLLECTION_EVENT_ID REPORT_NAME

             

            , UPPER(REPLACE(cd.SPOUSE_OR_ROOM_MATE,',',' ')) SPOUSE_OR_ROOM_MATE

             

            , UPPER(REPLACE(cd.RELATIVES_NAME,',',' ')) RELATIVES_NAME

             

            , cs.CUST_SRV_STMT_STATUS

             

            , ccea.STATEMENT_CODE

             

            , ccea.COLLECTION_EVENT_STATUS

             

            , car.SERVICE_CATEGORY_CODE

             

            , DATE_FORMAT(cm.CONNECT_DATE) CONNECT_DATE

             

            , cm.LANGUAGE_CODE

             

            , cd.VCR_DATA_CODE PAYMENT_BLOCK

             

            , NULL CUSTOM_A_50

             

            , NULL CUSTOM_B_50

             

            , NULL CUSTOM_C_50

             

            , NULL CUSTOMER_A_10

             

            , NULL CUSTOMER_B_10

             

            , NULL CUSTOMER_C_10

             

            , NULL COLL_ACT_CODE

             

            , cm.PIN_NUMBER

             

            , cs.INTERNAL_CREDIT_SCORE

             

            , NULL CUSTOM_A_1

             

            , NULL CUSTOM_B_1

             

            , CASE WHEN hm.BILL_TYPE_CODE != 'S' THEN 1 ELSE 2 END LOB

             

            FROM

             

            PSTAGE.PHX_CUSTOMER_MASTER cm

             

            LEFT OUTER JOIN PSTAGE.PHX_CUSTOMER_TELEPHONE ct

             

            ON cm.ACCOUNT_NUMBER = ct.ACCOUNT_NUMBER

             

            AND ct.PRIMADL = 'P'

             

            AND ct.CUST_TELEPHONE_STATUS = 'AC'

             

            AND ct.SERVICE_OCCURRENCE = '1'

             

            AND ct.SERVICE_CATEGORY_CODE = 'T'

             

            INNER JOIN

             

            PSTAGE.PHX_COLLECTION_CST_EVENT_ACT ccea

             

            ON cm.ACCOUNT_NUMBER = ccea.ACCOUNT_NUMBER

             

            INNER JOIN PSTAGE.PHX_HOUSE_MASTER hm

             

            ON cm.HOUSE_NUMBER = hm.HOUSE_NUMBER

             

            INNER JOIN PSTAGE.PHX_CUSTOMER_STATEMENTS cs

             

            ON ccea.STATEMENT_CODE = cs.STATEMENT_CODE

             

            AND cm.ACCOUNT_NUMBER = cs.ACCOUNT_NUMBER

             

            LEFT OUTER JOIN PSTAGE.PHX_CUSTOMER_DEMOGRAPHICS cd

             

            ON cm.ACCOUNT_NUMBER = cd.ACCOUNT_NUMBER

             

            INNER JOIN

             

            (

             

            SELECT

             

            car.ACCOUNT_NUMBER,

             

            SUM(car.AR_BALANCE) AR_BALANCE,

             

            SUM(car.CURRENT_AR_BALANCE) CURRENT_AR_BALANCE,

             

            SUM(car.AR_1_TO_30) AR_1_TO_30,

             

            SUM(car.AR_31_TO_60) AR_31_TO_60,

             

            SUM(car.AR_61_TO_90) AR_61_TO_90,

             

            SUM(car.AR_91_TO_120) AR_91_TO_120,

             

            SUM(car.AR_121_TO_150) AR_121_TO_150,

             

            SUM(car.AR_OVER_150_DAYS) AR_OVER_150_DAYS,

             

            MAX(CASE WHEN car.SERVICE_CATEGORY_CODE = 'C' THEN 'C' ELSE NULL END) ||

             

            MAX(CASE WHEN car.SERVICE_CATEGORY_CODE = 'D' THEN 'D' ELSE NULL END) ||

             

            MAX(CASE WHEN car.SERVICE_CATEGORY_CODE = 'T' THEN 'T' ELSE NULL END) ||

             

            MAX(CASE WHEN car.SERVICE_CATEGORY_CODE = 'H' THEN 'H' ELSE NULL END) SERVICE_CATEGORY_CODE

             

            FROM PSTAGE.PHX_CUSTOMER_AR_AGING car

             

            GROUP BY

             

            car.ACCOUNT_NUMBER

             

            ) car

             

            ON cm.ACCOUNT_NUMBER = car.ACCOUNT_NUMBER

             

            WHERE

             

            ccea.COLLECTION_ACTION_DATE = TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) - 19000000

             

            AND ccea.COLLECTION_EVENT_STATUS = 'P'

             

            call count cpu elapsed disk query current rows

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            Parse 1 1.36 1.37 2 6 0 0

             

            Execute 1 0.01 1.39 15 99 12 0

             

            Fetch 3568 0.65 39.78 0 0 0 53499

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            total 3570 2.04 42.55 17 105 12 53499

             

            Misses in library cache during parse: 1

             

            Optimizer mode: ALL_ROWS

             

            Parsing user id: SYS

             

            Number of plan statistics captured: 1

             

            Rows (1st) Rows (avg) Rows (max) Row Source Operation

             

            ---------- ---------- ---------- ---------------------------------------------------

             

            53499 53499 53499 PX COORDINATOR (cr=99 pr=15 pw=0 time=40628107 us)

             

            0 0 0 PX SEND QC (RANDOM) :TQ10009 (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

             

            0 0 0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

             

            0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

             

            0 0 0 PX SEND HASH :TQ10008 (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

             

            0 0 0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

             

            0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=12610 size=45535094 card=133534)

             

            0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=10319 size=25063808 card=81376)

             

            0 0 0 PX SEND BROADCAST :TQ10007 (cr=0 pr=0 pw=0 time=0 us cost=10319 size=25063808 card=81376)

             

            0 0 0 HASH JOIN OUTER BUFFERED (cr=0 pr=0 pw=0 time=0 us cost=10319 size=25063808 card=81376)

             

            0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=9791 size=22378400 card=81376)

             

            0 0 0 PX SEND HASH :TQ10005 (cr=0 pr=0 pw=0 time=0 us cost=9791 size=22378400 card=81376)

             

            0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=9791 size=22378400 card=81376)

             

            0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=8194 size=17495840 card=81376)

             

            0 0 0 PX SEND BROADCAST :TQ10004 (cr=0 pr=0 pw=0 time=0 us cost=8194 size=17495840 card=81376)

             

            0 0 0 HASH JOIN OUTER BUFFERED (cr=0 pr=0 pw=0 time=0 us cost=8194 size=17495840 card=81376)

             

            0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=6958 size=14078048 card=81376)

             

            0 0 0 PX SEND HASH :TQ10002 (cr=0 pr=0 pw=0 time=0 us cost=6958 size=14078048 card=81376)

             

            0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=6958 size=14078048 card=81376)

             

            0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=3646 size=12387024 card=86021)

             

            0 0 0 PX SEND BROADCAST :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=3646 size=12387024 card=86021)

             

            0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=3646 size=12387024 card=86021)

             

            0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=322 size=3446788 card=84068)

             

            0 0 0 PX SEND BROADCAST :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=322 size=3446788 card=84068)

             

            0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=322 size=3446788 card=84068)

             

            0 0 0 TABLE ACCESS FULL PHX_CBLKREP (cr=0 pr=0 pw=0 time=0 us cost=322 size=3446788 card=84068)

             

            0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=3316 size=890258561 card=8643287)

             

            0 0 0 TABLE ACCESS FULL PHX_CUMSTPF (cr=0 pr=0 pw=0 time=0 us cost=3316 size=890258561 card=8643287)

             

            0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=3304 size=268569203 card=9261007)

             

            0 0 0 TABLE ACCESS FULL PHX_CBIZREP (cr=0 pr=0 pw=0 time=0 us cost=3304 size=268569203 card=9261007)

             

            0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=1235 size=22879122 card=544741)

             

            0 0 0 PX SEND HASH :TQ10003 (cr=0 pr=0 pw=0 time=0 us cost=1235 size=22879122 card=544741)

             

            0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=1235 size=22879122 card=544741)

             

            0 0 0 TABLE ACCESS FULL PHX_CMGTREP (cr=0 pr=0 pw=0 time=0 us cost=1235 size=22879122 card=544741)

             

            0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=1595 size=143327880 card=2388798)

             

            0 0 0 TABLE ACCESS FULL PHX_HOSTPF (cr=0 pr=0 pw=0 time=0 us cost=1595 size=143327880 card=2388798)

             

            0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=525 size=104338014 card=3161758)

             

            0 0 0 PX SEND HASH :TQ10006 (cr=0 pr=0 pw=0 time=0 us cost=525 size=104338014 card=3161758)

             

            0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=525 size=104338014 card=3161758)

             

            0 0 0 TABLE ACCESS FULL PHX_DEMOGPF (cr=0 pr=0 pw=0 time=0 us cost=525 size=104338014 card=3161758)

             

            0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=2279 size=457415673 card=13861081)

             

            0 0 0 TABLE ACCESS FULL PHX_CBI0REP (cr=0 pr=0 pw=0 time=0 us cost=2279 size=457415673 card=13861081)

             

            ********************************************************************************

             

            SQL ID: 7cfz5wy9caaf4 Plan Hash: 4015672053

             

            SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,

             

            'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,

             

            DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM

             

            FROM

             

            V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY

             

            NAME_COL_PLUS_SHOW_PARAM,ROWNUM

             

              

             

            call count cpu elapsed disk query current rows

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            Parse 2 0.00 0.00 0 0 0 0

             

            Execute 2 0.00 0.00 0 0 0 0

             

            Fetch 3 0.00 0.00 0 0 0 6

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            total 7 0.00 0.00 0 0 0 6

             

            Misses in library cache during parse: 1

             

            Misses in library cache during execute: 1

             

            Optimizer mode: ALL_ROWS

             

            Parsing user id: SYS

             

            Number of plan statistics captured: 2

             

            Rows (1st) Rows (avg) Rows (max) Row Source Operation

             

            ---------- ---------- ---------- ---------------------------------------------------

             

            6 3 6 SORT ORDER BY (cr=0 pr=0 pw=0 time=2350 us cost=2 size=57 card=1)

             

            6 3 6 COUNT (cr=0 pr=0 pw=0 time=2142 us)

             

            6 3 6 HASH JOIN (cr=0 pr=0 pw=0 time=2140 us cost=1 size=57 card=1)

             

            51 26 51 FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=1107 us cost=0 size=273 card=7)

             

            2752 1376 2752 FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=440 us cost=0 size=49536 card=2752)

             

              

             

              

             

              

             

            ********************************************************************************

             

            OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

             

            call count cpu elapsed disk query current rows

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            Parse 3 1.37 1.37 2 6 0 0

             

            Execute 4 0.01 1.39 15 99 12 0

             

            Fetch 3571 0.66 39.78 0 0 0 53505

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            total 3578 2.05 42.55 17 105 12 53505

             

            Misses in library cache during parse: 2

             

            Misses in library cache during execute: 2

             

              

             

            OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

             

            call count cpu elapsed disk query current rows

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            Parse 20 0.00 0.00 0 0 0 0

             

            Execute 20 0.00 0.00 0 0 0 0

             

            Fetch 20 0.03 0.65 205 748 3 20

             

            ------- ------ -------- ---------- ---------- ---------- ---------- ----------

             

            total 60 0.03 0.66 205 748 3 20

             

            Misses in library cache during parse: 3

             

            Misses in library cache during execute: 1

             

            4 user SQL statements in session.

             

            9 internal SQL statements in session.

             

            13 SQL statements in session.

             

            ********************************************************************************

             

            Trace file: PSTAGE2_ora_6210.trc

             

            Trace file compatibility: 11.1.0.7

             

            Sort options: default

             

            1 session in tracefile.

             

            4 user SQL statements in trace file.

             

            9 internal SQL statements in trace file.

             

            13 SQL statements in trace file.

             

            6 unique SQL statements in trace file.

             

            4514 lines in trace file.

             

            731 elapsed seconds in trace file.

             

            {noformat}

             

              

             

            • 3. Re: Tuning SQL Query
              ranit B

              Thanks for the info (although completely unreadable & needs lot of formatting).

               

              Few more questions -

              1. Why default DOP is set to 5 ?

              AFAIK, it should be serial execution i.e. DOP = 1

               

              2. Why do you feel the query is slow? What is your expectation?

               

              3. Are all the statistics up-to-date?

               

              4. Are you checking the Explain Plan (using EXPLAIN PLAN FOR . . . and DBMS_XPLAN.display) or Execution Plan (using Hint /*+ gather_plan_statistics */ and DBMS_XPLAN.display_cursor) ?

               

               

               

              Sorry, I can't read TKPROF output so can't comment on that. Let's wait for some expert to comment on this. (Learning for me too)

              • 4. Re: Tuning SQL Query
                GeetaM

                How can i attach a file to this fourm of a TKPROF ?

                 

                Regards

                • 5. Re: Tuning SQL Query
                  ranit B

                  Also, can you please check these:

                   

                  - I see all access paths go for FTS and seems there are no indexes present on the base tables.

                   

                  - There are many places in code I feel which can be tightened (though not sure... you have access to the code... play with it )

                  ccea.n_ACTION_DATE = TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) - 19000000

                  What is this?

                  Seems like you want to go back to some previous date/time in past, but for that you need not convert date into number and subtract operation.

                   

                  Check if you can use Analytical functions to meet your query objective.

                  • 6. Re: Tuning SQL Query
                    GeetaM

                    Hi Ranit

                     

                    I am an Oracle DBA and  the user wants me to look into query as its running slow for him. I am going to check with him on the business objective?

                    Also,Why default DOP is set to 5 ?

                    Why did he feel the query is slow? What is his expectation?

                     

                    Answering few of your questions 

                     

                    4. Are you checking the Explain Plan (using EXPLAIN PLAN FOR . . . and DBMS_XPLAN.display) or Execution Plan (using Hint /*+ gather_plan_statistics */ and DBMS_XPLAN.display_cursor) ?

                     

                    I have used - Explain Plan (using EXPLAIN PLAN FOR . . . and DBMS_XPLAN.display)

                     

                    I am repasting the TKPROF...please see if its readable

                     

                    {code}

                     

                     

                    TKPROF: Release 11.2.0.3.0 - Development on Tue Dec 24 01:14:06 2013

                     

                    Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

                     

                    Trace file: PSTAGE2_ora_6210.trc

                     

                    Sort options: default

                     

                    ********************************************************************************

                     

                    count = number of times OCI procedure was executed

                     

                    cpu = cpu time in seconds executing

                     

                    elapsed = elapsed time in seconds executing

                     

                    disk = number of physical reads of buffers from disk

                     

                    query = number of buffers gotten for consistent read

                     

                    current = number of buffers gotten in current mode (usually for update)

                     

                    rows = number of rows processed by the fetch or execute call

                     

                    ********************************************************************************

                     

                    SQL ID: 1a2g3z4rj1uq2 Plan Hash: 0

                     

                    alter session set sql_trace true

                     

                      

                     

                    call count cpu elapsed disk query current rows

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    Parse 0 0.00 0.00 0 0 0 0

                     

                    Execute 1 0.00 0.00 0 0 0 0

                     

                    Fetch 0 0.00 0.00 0 0 0 0

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    total 1 0.00 0.00 0 0 0 0

                     

                    Misses in library cache during parse: 0

                     

                    Misses in library cache during execute: 1

                     

                    Optimizer mode: ALL_ROWS

                     

                    Parsing user id: SYS

                     

                    ********************************************************************************

                     

                    SQL ID: grwydz59pu6mc Plan Hash: 3684871272

                     

                    select text

                     

                    from

                     

                    view$ where rowid=:1

                     

                      

                     

                    call count cpu elapsed disk query current rows

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    Parse 14 0.00 0.00 0 0 0 0

                     

                    Execute 14 0.00 0.00 0 0 0 0

                     

                    Fetch 14 0.00 0.00 2 28 0 14

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    total 42 0.00 0.01 2 28 0 14

                     

                    Misses in library cache during parse: 1

                     

                    Misses in library cache during execute: 1

                     

                    Optimizer mode: CHOOSE

                     

                    Parsing user id: SYS (recursive depth: 1)

                     

                    Number of plan statistics captured: 3

                     

                    Rows (1st) Rows (avg) Rows (max) Row Source Operation

                     

                    ---------- ---------- ---------- ---------------------------------------------------

                     

                    1 1 1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=1174 us cost=1 size=15 card=1)

                     

                    ********************************************************************************

                     

                    SQL ID: 5mq7qz8r3dcvg Plan Hash: 902152095

                     

                    SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE

                     

                    NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')

                     

                    NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)

                     

                    FROM

                     

                    (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("PHX_CMGTREP")

                     

                    FULL("PHX_CMGTREP") NO_PARALLEL_INDEX("PHX_CMGTREP") */ 1 AS C1, CASE WHEN

                     

                    "PHX_CMGTREP"."GTCEK6"='T' AND "PHX_CMGTREP"."GTSYP8"=1 AND

                     

                    "PHX_CMGTREP"."GTSUFJ"='AC' AND "PHX_CMGTREP"."GTSUFV"='P' THEN 1 ELSE 0

                     

                    END AS C2 FROM "PSTAGE"."PHX_CMGTREP" SAMPLE BLOCK (0.118794 , 1) SEED (1)

                     

                    "PHX_CMGTREP") SAMPLESUB

                     

                      

                     

                    call count cpu elapsed disk query current rows

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    Parse 3 0.00 0.00 0 0 0 0

                     

                    Execute 3 0.00 0.00 0 0 0 0

                     

                    Fetch 3 0.01 0.33 81 456 0 3

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    total 9 0.01 0.33 81 456 0 3

                     

                    Misses in library cache during parse: 1

                     

                    Optimizer mode: ALL_ROWS

                     

                    Parsing user id: SYS (recursive depth: 1)

                     

                    Number of plan statistics captured: 3

                     

                    Rows (1st) Rows (avg) Rows (max) Row Source Operation

                     

                    ---------- ---------- ---------- ---------------------------------------------------

                     

                    1 1 1 SORT AGGREGATE (cr=152 pr=27 pw=0 time=110359 us)

                     

                    4043 4043 4043 TABLE ACCESS SAMPLE PHX_CMGTREP (cr=152 pr=27 pw=0 time=2401 us cost=8 size=86963 card=3781)

                     

                    ********************************************************************************

                     

                    SQL ID: cp3jws7wccyuj Plan Hash: 4040798679

                     

                    SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE

                     

                    NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')

                     

                    NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)

                     

                    FROM

                     

                    (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("PHX_CBLKREP")

                     

                    FULL("PHX_CBLKREP") NO_PARALLEL_INDEX("PHX_CBLKREP") */ 1 AS C1, CASE WHEN

                     

                    "PHX_CBLKREP"."LKDDAB"=TO_NUMBER(TO_CHAR(SYSDATE@!,'YYYYMMDD'))-19000000

                     

                    AND "PHX_CBLKREP"."LKSIGA"='P' THEN 1 ELSE 0 END AS C2 FROM

                     

                    "PSTAGE"."PHX_CBLKREP" SAMPLE BLOCK (0.458449 , 1) SEED (1) "PHX_CBLKREP")

                     

                    SAMPLESUB

                     

                      

                     

                    call count cpu elapsed disk query current rows

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    Parse 3 0.00 0.00 0 0 0 0

                     

                    Execute 3 0.00 0.00 0 0 0 0

                     

                    Fetch 3 0.01 0.32 122 264 3 3

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    total 9 0.01 0.32 122 264 3 3

                     

                    Misses in library cache during parse: 1

                     

                    Optimizer mode: ALL_ROWS

                     

                    Parsing user id: SYS (recursive depth: 1)

                     

                    Number of plan statistics captured: 3

                     

                    Rows (1st) Rows (avg) Rows (max) Row Source Operation

                     

                    ---------- ---------- ---------- ---------------------------------------------------

                     

                    1 1 1 SORT AGGREGATE (cr=88 pr=41 pw=0 time=107173 us)

                     

                    7052 7052 7052 TABLE ACCESS SAMPLE PHX_CBLKREP (cr=88 pr=41 pw=0 time=3573 us cost=8 size=151000 card=7550)

                     

                    ********************************************************************************

                     

                    SELECT

                     

                    CAST(cm.SITE_ID AS varchar(3)) ICOMS_SITE_ID

                     

                    , cm.ACCOUNT_NUMBER

                     

                    , ccea.COLLECTION_SCHEME_ID

                     

                    , ccea.COLLECTION_EVENT_ID

                     

                    , UPPER(REPLACE(cm.FIRST_NAME,',',' ')) FIRST_NAME

                     

                    , UPPER(REPLACE(cm.LAST_NAME,',',' ')) LAST_NAME

                     

                    , CAST((cm.HOME_AREA_CODE || cm.HOME_EXCHANGE_NUMBER || LPAD(cm.HOME_TELEPHONE_NUMBER,4,0)) AS INTEGER) HOME_NUM

                     

                    , CAST((cm.OTHER_AREA_CODE || cm.OTHER_EXCHANGE_NUMBER || LPAD(cm.OTHER_PHONE,4,0)) AS INTEGER) OTHER_NUM

                     

                    , CAST((cm.BUSINESS_AREA_CODE || cm.BUSINESS_EXCHANGE_NUMBER || LPAD(cm.BUSINESS_EXCHANGE_NUMBER,4,0)) AS INTEGER) BUS_NUM

                     

                    , CAST(NVL((ct.NPASTD_NUMBER || ct.NXXEXCHANGE_NUMBER || LPAD(ct.THOUSAND_NUMBER,4,0)),0) AS INTEGER) SERVICE_NUM

                     

                    , REPLACE(UPPER(LTRIM(hm.ADDR_LOCATION) || ' ' || hm.PRE_DIRECTIONAL || ' ' || hm.STREET),' ',' ') HM_ADDRESS1

                     

                    , hm.ADDR_CITY

                     

                    , hm.ADDR_STATE

                     

                    , hm.ADDR_ZIP_5

                     

                    , cm.COMPANY_NUMBER

                     

                    , cm.DIVISION_NUMBER

                     

                    , cm.FRANCHISE_NUMBER

                     

                    , car.CURRENT_AR_BALANCE

                     

                    , car.AR_1_TO_30

                     

                    , car.AR_31_TO_60

                     

                    , car.AR_61_TO_90

                     

                    , car.AR_91_TO_120

                     

                    , car.AR_121_TO_150

                     

                    , car.AR_OVER_150_DAYS

                     

                    , car.AR_BALANCE

                     

                    , TO_DATE(TO_CHAR(ccea.COLLECTION_ACTION_DATE+19000000),'YYYY/MM/DD') COLLECTION_ACTION_DATE

                     

                    , cm.EXTERNAL_CREDIT_SCORE

                     

                    , cm.CUSTOMER_CATEGORY

                     

                    , cm.CUSTOMER_TYPE_CODE

                     

                    , cm.LANGUAGE_CODE || ccea.COLLECTION_EVENT_ID REPORT_NAME

                     

                    , UPPER(REPLACE(cd.SPOUSE_OR_ROOM_MATE,',',' ')) SPOUSE_OR_ROOM_MATE

                     

                    , UPPER(REPLACE(cd.RELATIVES_NAME,',',' ')) RELATIVES_NAME

                     

                    , cs.CUST_SRV_STMT_STATUS

                     

                    , ccea.STATEMENT_CODE

                     

                    , ccea.COLLECTION_EVENT_STATUS

                     

                    , car.SERVICE_CATEGORY_CODE

                     

                    , DATE_FORMAT(cm.CONNECT_DATE) CONNECT_DATE

                     

                    , cm.LANGUAGE_CODE

                     

                    , cd.VCR_DATA_CODE PAYMENT_BLOCK

                     

                    , NULL CUSTOM_A_50

                     

                    , NULL CUSTOM_B_50

                     

                    , NULL CUSTOM_C_50

                     

                    , NULL CUSTOMER_A_10

                     

                    , NULL CUSTOMER_B_10

                     

                    , NULL CUSTOMER_C_10

                     

                    , NULL COLL_ACT_CODE

                     

                    , cm.PIN_NUMBER

                     

                    , cs.INTERNAL_CREDIT_SCORE

                     

                    , NULL CUSTOM_A_1

                     

                    , NULL CUSTOM_B_1

                     

                    , CASE WHEN hm.BILL_TYPE_CODE != 'S' THEN 1 ELSE 2 END LOB

                     

                    FROM

                     

                    PSTAGE.PHX_CUSTOMER_MASTER cm

                     

                    LEFT OUTER JOIN PSTAGE.PHX_CUSTOMER_TELEPHONE ct

                     

                    ON cm.ACCOUNT_NUMBER = ct.ACCOUNT_NUMBER

                     

                    AND ct.PRIMADL = 'P'

                     

                    AND ct.CUST_TELEPHONE_STATUS = 'AC'

                     

                    AND ct.SERVICE_OCCURRENCE = '1'

                     

                    AND ct.SERVICE_CATEGORY_CODE = 'T'

                     

                    INNER JOIN

                     

                    PSTAGE.PHX_COLLECTION_CST_EVENT_ACT ccea

                     

                    ON cm.ACCOUNT_NUMBER = ccea.ACCOUNT_NUMBER

                     

                    INNER JOIN PSTAGE.PHX_HOUSE_MASTER hm

                     

                    ON cm.HOUSE_NUMBER = hm.HOUSE_NUMBER

                     

                    INNER JOIN PSTAGE.PHX_CUSTOMER_STATEMENTS cs

                     

                    ON ccea.STATEMENT_CODE = cs.STATEMENT_CODE

                     

                    AND cm.ACCOUNT_NUMBER = cs.ACCOUNT_NUMBER

                     

                    LEFT OUTER JOIN PSTAGE.PHX_CUSTOMER_DEMOGRAPHICS cd

                     

                    ON cm.ACCOUNT_NUMBER = cd.ACCOUNT_NUMBER

                     

                    INNER JOIN

                     

                    (

                     

                    SELECT

                     

                    car.ACCOUNT_NUMBER,

                     

                    SUM(car.AR_BALANCE) AR_BALANCE,

                     

                    SUM(car.CURRENT_AR_BALANCE) CURRENT_AR_BALANCE,

                     

                    SUM(car.AR_1_TO_30) AR_1_TO_30,

                     

                    SUM(car.AR_31_TO_60) AR_31_TO_60,

                     

                    SUM(car.AR_61_TO_90) AR_61_TO_90,

                     

                    SUM(car.AR_91_TO_120) AR_91_TO_120,

                     

                    SUM(car.AR_121_TO_150) AR_121_TO_150,

                     

                    SUM(car.AR_OVER_150_DAYS) AR_OVER_150_DAYS,

                     

                    MAX(CASE WHEN car.SERVICE_CATEGORY_CODE = 'C' THEN 'C' ELSE NULL END) ||

                     

                    MAX(CASE WHEN car.SERVICE_CATEGORY_CODE = 'D' THEN 'D' ELSE NULL END) ||

                     

                    MAX(CASE WHEN car.SERVICE_CATEGORY_CODE = 'T' THEN 'T' ELSE NULL END) ||

                     

                    MAX(CASE WHEN car.SERVICE_CATEGORY_CODE = 'H' THEN 'H' ELSE NULL END) SERVICE_CATEGORY_CODE

                     

                    FROM PSTAGE.PHX_CUSTOMER_AR_AGING car

                     

                    GROUP BY

                     

                    car.ACCOUNT_NUMBER

                     

                    ) car

                     

                    ON cm.ACCOUNT_NUMBER = car.ACCOUNT_NUMBER

                     

                    WHERE

                     

                    ccea.COLLECTION_ACTION_DATE = TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) - 19000000

                     

                    AND ccea.COLLECTION_EVENT_STATUS = 'P'

                     

                    call count cpu elapsed disk query current rows

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    Parse 1 1.36 1.37 2 6 0 0

                     

                    Execute 1 0.01 1.39 15 99 12 0

                     

                    Fetch 3568 0.65 39.78 0 0 0 53499

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    total 3570 2.04 42.55 17 105 12 53499

                     

                    Misses in library cache during parse: 1

                     

                    Optimizer mode: ALL_ROWS

                     

                    Parsing user id: SYS

                     

                    Number of plan statistics captured: 1

                     

                    Rows (1st) Rows (avg) Rows (max) Row Source Operation

                     

                    ---------- ---------- ---------- ---------------------------------------------------

                     

                    53499 53499 53499 PX COORDINATOR (cr=99 pr=15 pw=0 time=40628107 us)

                     

                    0 0 0 PX SEND QC (RANDOM) :TQ10009 (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

                     

                    0 0 0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

                     

                    0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

                     

                    0 0 0 PX SEND HASH :TQ10008 (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

                     

                    0 0 0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

                     

                    0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=12610 size=45535094 card=133534)

                     

                    0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=10319 size=25063808 card=81376)

                     

                    0 0 0 PX SEND BROADCAST :TQ10007 (cr=0 pr=0 pw=0 time=0 us cost=10319 size=25063808 card=81376)

                     

                    0 0 0 HASH JOIN OUTER BUFFERED (cr=0 pr=0 pw=0 time=0 us cost=10319 size=25063808 card=81376)

                     

                    0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=9791 size=22378400 card=81376)

                     

                    0 0 0 PX SEND HASH :TQ10005 (cr=0 pr=0 pw=0 time=0 us cost=9791 size=22378400 card=81376)

                     

                    0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=9791 size=22378400 card=81376)

                     

                    0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=8194 size=17495840 card=81376)

                     

                    0 0 0 PX SEND BROADCAST :TQ10004 (cr=0 pr=0 pw=0 time=0 us cost=8194 size=17495840 card=81376)

                     

                    0 0 0 HASH JOIN OUTER BUFFERED (cr=0 pr=0 pw=0 time=0 us cost=8194 size=17495840 card=81376)

                     

                    0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=6958 size=14078048 card=81376)

                     

                    0 0 0 PX SEND HASH :TQ10002 (cr=0 pr=0 pw=0 time=0 us cost=6958 size=14078048 card=81376)

                     

                    0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=6958 size=14078048 card=81376)

                     

                    0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=3646 size=12387024 card=86021)

                     

                    0 0 0 PX SEND BROADCAST :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=3646 size=12387024 card=86021)

                     

                    0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=3646 size=12387024 card=86021)

                     

                    0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=322 size=3446788 card=84068)

                     

                    0 0 0 PX SEND BROADCAST :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=322 size=3446788 card=84068)

                     

                    0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=322 size=3446788 card=84068)

                     

                    0 0 0 TABLE ACCESS FULL PHX_CBLKREP (cr=0 pr=0 pw=0 time=0 us cost=322 size=3446788 card=84068)

                     

                    0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=3316 size=890258561 card=8643287)

                     

                    0 0 0 TABLE ACCESS FULL PHX_CUMSTPF (cr=0 pr=0 pw=0 time=0 us cost=3316 size=890258561 card=8643287)

                     

                    0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=3304 size=268569203 card=9261007)

                     

                    0 0 0 TABLE ACCESS FULL PHX_CBIZREP (cr=0 pr=0 pw=0 time=0 us cost=3304 size=268569203 card=9261007)

                     

                    0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=1235 size=22879122 card=544741)

                     

                    0 0 0 PX SEND HASH :TQ10003 (cr=0 pr=0 pw=0 time=0 us cost=1235 size=22879122 card=544741)

                     

                    0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=1235 size=22879122 card=544741)

                     

                    0 0 0 TABLE ACCESS FULL PHX_CMGTREP (cr=0 pr=0 pw=0 time=0 us cost=1235 size=22879122 card=544741)

                     

                    0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=1595 size=143327880 card=2388798)

                     

                    0 0 0 TABLE ACCESS FULL PHX_HOSTPF (cr=0 pr=0 pw=0 time=0 us cost=1595 size=143327880 card=2388798)

                     

                    0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=525 size=104338014 card=3161758)

                     

                    0 0 0 PX SEND HASH :TQ10006 (cr=0 pr=0 pw=0 time=0 us cost=525 size=104338014 card=3161758)

                     

                    0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=525 size=104338014 card=3161758)

                     

                    0 0 0 TABLE ACCESS FULL PHX_DEMOGPF (cr=0 pr=0 pw=0 time=0 us cost=525 size=104338014 card=3161758)

                     

                    0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=2279 size=457415673 card=13861081)

                     

                    0 0 0 TABLE ACCESS FULL PHX_CBI0REP (cr=0 pr=0 pw=0 time=0 us cost=2279 size=457415673 card=13861081)

                     

                    ********************************************************************************

                     

                    SQL ID: 7cfz5wy9caaf4 Plan Hash: 4015672053

                     

                    SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,

                     

                    'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,

                     

                    DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM

                     

                    FROM

                     

                    V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY

                     

                    NAME_COL_PLUS_SHOW_PARAM,ROWNUM

                     

                      

                     

                    call count cpu elapsed disk query current rows

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    Parse 2 0.00 0.00 0 0 0 0

                     

                    Execute 2 0.00 0.00 0 0 0 0

                     

                    Fetch 3 0.00 0.00 0 0 0 6

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    total 7 0.00 0.00 0 0 0 6

                     

                    Misses in library cache during parse: 1

                     

                    Misses in library cache during execute: 1

                     

                    Optimizer mode: ALL_ROWS

                     

                    Parsing user id: SYS

                     

                    Number of plan statistics captured: 2

                     

                    Rows (1st) Rows (avg) Rows (max) Row Source Operation

                     

                    ---------- ---------- ---------- ---------------------------------------------------

                     

                    6 3 6 SORT ORDER BY (cr=0 pr=0 pw=0 time=2350 us cost=2 size=57 card=1)

                     

                    6 3 6 COUNT (cr=0 pr=0 pw=0 time=2142 us)

                     

                    6 3 6 HASH JOIN (cr=0 pr=0 pw=0 time=2140 us cost=1 size=57 card=1)

                     

                    51 26 51 FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=1107 us cost=0 size=273 card=7)

                     

                    2752 1376 2752 FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=440 us cost=0 size=49536 card=2752)

                     

                      

                     

                      

                     

                      

                     

                    ********************************************************************************

                     

                    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

                     

                    call count cpu elapsed disk query current rows

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    Parse 3 1.37 1.37 2 6 0 0

                     

                    Execute 4 0.01 1.39 15 99 12 0

                     

                    Fetch 3571 0.66 39.78 0 0 0 53505

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    total 3578 2.05 42.55 17 105 12 53505

                     

                    Misses in library cache during parse: 2

                     

                    Misses in library cache during execute: 2

                     

                      

                     

                    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

                     

                    call count cpu elapsed disk query current rows

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    Parse 20 0.00 0.00 0 0 0 0

                     

                    Execute 20 0.00 0.00 0 0 0 0

                     

                    Fetch 20 0.03 0.65 205 748 3 20

                     

                    ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                     

                    total 60 0.03 0.66 205 748 3 20

                     

                    Misses in library cache during parse: 3

                     

                    Misses in library cache during execute: 1

                     

                    4 user SQL statements in session.

                     

                    9 internal SQL statements in session.

                     

                    13 SQL statements in session.

                     

                    ********************************************************************************

                     

                    Trace file: PSTAGE2_ora_6210.trc

                     

                    Trace file compatibility: 11.1.0.7

                     

                    Sort options: default

                     

                    1 session in tracefile.

                     

                    4 user SQL statements in trace file.

                     

                    9 internal SQL statements in trace file.

                     

                    13 SQL statements in trace file.

                     

                    6 unique SQL statements in trace file.

                     

                    4514 lines in trace file.

                     

                    731 elapsed seconds in trace file.

                     

                      

                     

                      

                     

                    {code}

                    • 7. Re: Tuning SQL Query
                      Hoek

                      If you use the 'advanced editor' (upper right in screen), and change the font to Courier New, then your output should be much more readable.

                       

                      One thing caught my eye when looking at the tkprof output: you're parsing for every execute. Rule is: parse once, execute many.

                      Parsing is an expensive operation, so find out what causes the excessive parsing (for example: are you using bind variables?).

                      Another thing: does anything change if you run this test as another user? SYS is a special user, things work different when you're connected as SYS.

                      • 8. Re: Tuning SQL Query
                        GeetaM

                        I will check with the user regarding bind variables....and will try executing with another user too...

                         

                        {code}

                         

                        TKPROF: Release 11.2.0.3.0 - Development on Tue Dec 24 01:14:06 2013

                         

                        Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

                         

                        Trace file: PSTAGE2_ora_6210.trc

                         

                        Sort options: default

                         

                        ********************************************************************************

                         

                        count = number of times OCI procedure was executed

                         

                        cpu = cpu time in seconds executing

                         

                        elapsed = elapsed time in seconds executing

                         

                        disk = number of physical reads of buffers from disk

                         

                        query = number of buffers gotten for consistent read

                         

                        current = number of buffers gotten in current mode (usually for update)

                         

                        rows = number of rows processed by the fetch or execute call

                         

                        ********************************************************************************

                         

                        SQL ID: 1a2g3z4rj1uq2 Plan Hash: 0

                         

                        alter session set sql_trace true

                         

                          

                         

                        call count cpu elapsed disk query current rows

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        Parse 0 0.00 0.00 0 0 0 0

                         

                        Execute 1 0.00 0.00 0 0 0 0

                         

                        Fetch 0 0.00 0.00 0 0 0 0

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        total 1 0.00 0.00 0 0 0 0

                         

                        Misses in library cache during parse: 0

                         

                        Misses in library cache during execute: 1

                         

                        Optimizer mode: ALL_ROWS

                         

                        Parsing user id: SYS

                         

                        ********************************************************************************

                         

                        SQL ID: grwydz59pu6mc Plan Hash: 3684871272

                         

                        select text

                         

                        from

                         

                        view$ where rowid=:1

                         

                          

                         

                        call count cpu elapsed disk query current rows

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        Parse 14 0.00 0.00 0 0 0 0

                         

                        Execute 14 0.00 0.00 0 0 0 0

                         

                        Fetch 14 0.00 0.00 2 28 0 14

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        total 42 0.00 0.01 2 28 0 14

                         

                        Misses in library cache during parse: 1

                         

                        Misses in library cache during execute: 1

                         

                        Optimizer mode: CHOOSE

                         

                        Parsing user id: SYS (recursive depth: 1)

                         

                        Number of plan statistics captured: 3

                         

                        Rows (1st) Rows (avg) Rows (max) Row Source Operation

                         

                        ---------- ---------- ---------- ---------------------------------------------------

                         

                        1 1 1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=1174 us cost=1 size=15 card=1)

                         

                        ********************************************************************************

                         

                        SQL ID: 5mq7qz8r3dcvg Plan Hash: 902152095

                         

                        SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE

                         

                        NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')

                         

                        NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)

                         

                        FROM

                         

                        (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("PHX_CMGTREP")

                         

                        FULL("PHX_CMGTREP") NO_PARALLEL_INDEX("PHX_CMGTREP") */ 1 AS C1, CASE WHEN

                         

                        "PHX_CMGTREP"."GTCEK6"='T' AND "PHX_CMGTREP"."GTSYP8"=1 AND

                         

                        "PHX_CMGTREP"."GTSUFJ"='AC' AND "PHX_CMGTREP"."GTSUFV"='P' THEN 1 ELSE 0

                         

                        END AS C2 FROM "PSTAGE"."PHX_CMGTREP" SAMPLE BLOCK (0.118794 , 1) SEED (1)

                         

                        "PHX_CMGTREP") SAMPLESUB

                         

                          

                         

                        call count cpu elapsed disk query current rows

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        Parse 3 0.00 0.00 0 0 0 0

                         

                        Execute 3 0.00 0.00 0 0 0 0

                         

                        Fetch 3 0.01 0.33 81 456 0 3

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        total 9 0.01 0.33 81 456 0 3

                         

                        Misses in library cache during parse: 1

                         

                        Optimizer mode: ALL_ROWS

                         

                        Parsing user id: SYS (recursive depth: 1)

                         

                        Number of plan statistics captured: 3

                         

                        Rows (1st) Rows (avg) Rows (max) Row Source Operation

                         

                        ---------- ---------- ---------- ---------------------------------------------------

                         

                        1 1 1 SORT AGGREGATE (cr=152 pr=27 pw=0 time=110359 us)

                         

                        4043 4043 4043 TABLE ACCESS SAMPLE PHX_CMGTREP (cr=152 pr=27 pw=0 time=2401 us cost=8 size=86963 card=3781)

                         

                        ********************************************************************************

                         

                        SQL ID: cp3jws7wccyuj Plan Hash: 4040798679

                         

                        SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE

                         

                        NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')

                         

                        NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0)

                         

                        FROM

                         

                        (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("PHX_CBLKREP")

                         

                        FULL("PHX_CBLKREP") NO_PARALLEL_INDEX("PHX_CBLKREP") */ 1 AS C1, CASE WHEN

                         

                        "PHX_CBLKREP"."LKDDAB"=TO_NUMBER(TO_CHAR(SYSDATE@!,'YYYYMMDD'))-19000000

                         

                        AND "PHX_CBLKREP"."LKSIGA"='P' THEN 1 ELSE 0 END AS C2 FROM

                         

                        "PSTAGE"."PHX_CBLKREP" SAMPLE BLOCK (0.458449 , 1) SEED (1) "PHX_CBLKREP")

                         

                        SAMPLESUB

                         

                          

                         

                        call count cpu elapsed disk query current rows

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        Parse 3 0.00 0.00 0 0 0 0

                         

                        Execute 3 0.00 0.00 0 0 0 0

                         

                        Fetch 3 0.01 0.32 122 264 3 3

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        total 9 0.01 0.32 122 264 3 3

                         

                        Misses in library cache during parse: 1

                         

                        Optimizer mode: ALL_ROWS

                         

                        Parsing user id: SYS (recursive depth: 1)

                         

                        Number of plan statistics captured: 3

                         

                        Rows (1st) Rows (avg) Rows (max) Row Source Operation

                         

                        ---------- ---------- ---------- ---------------------------------------------------

                         

                        1 1 1 SORT AGGREGATE (cr=88 pr=41 pw=0 time=107173 us)

                         

                        7052 7052 7052 TABLE ACCESS SAMPLE PHX_CBLKREP (cr=88 pr=41 pw=0 time=3573 us cost=8 size=151000 card=7550)

                         

                        ********************************************************************************

                         

                        SELECT

                         

                        CAST(cm.SITE_ID AS varchar(3)) ICOMS_SITE_ID

                         

                        , cm.ACCOUNT_NUMBER

                         

                        , ccea.COLLECTION_SCHEME_ID

                         

                        , ccea.COLLECTION_EVENT_ID

                         

                        , UPPER(REPLACE(cm.FIRST_NAME,',',' ')) FIRST_NAME

                         

                        , UPPER(REPLACE(cm.LAST_NAME,',',' ')) LAST_NAME

                         

                        , CAST((cm.HOME_AREA_CODE || cm.HOME_EXCHANGE_NUMBER || LPAD(cm.HOME_TELEPHONE_NUMBER,4,0)) AS INTEGER) HOME_NUM

                         

                        , CAST((cm.OTHER_AREA_CODE || cm.OTHER_EXCHANGE_NUMBER || LPAD(cm.OTHER_PHONE,4,0)) AS INTEGER) OTHER_NUM

                         

                        , CAST((cm.BUSINESS_AREA_CODE || cm.BUSINESS_EXCHANGE_NUMBER || LPAD(cm.BUSINESS_EXCHANGE_NUMBER,4,0)) AS INTEGER) BUS_NUM

                         

                        , CAST(NVL((ct.NPASTD_NUMBER || ct.NXXEXCHANGE_NUMBER || LPAD(ct.THOUSAND_NUMBER,4,0)),0) AS INTEGER) SERVICE_NUM

                         

                        , REPLACE(UPPER(LTRIM(hm.ADDR_LOCATION) || ' ' || hm.PRE_DIRECTIONAL || ' ' || hm.STREET),' ',' ') HM_ADDRESS1

                         

                        , hm.ADDR_CITY

                         

                        , hm.ADDR_STATE

                         

                        , hm.ADDR_ZIP_5

                         

                        , cm.COMPANY_NUMBER

                         

                        , cm.DIVISION_NUMBER

                         

                        , cm.FRANCHISE_NUMBER

                         

                        , car.CURRENT_AR_BALANCE

                         

                        , car.AR_1_TO_30

                         

                        , car.AR_31_TO_60

                         

                        , car.AR_61_TO_90

                         

                        , car.AR_91_TO_120

                         

                        , car.AR_121_TO_150

                         

                        , car.AR_OVER_150_DAYS

                         

                        , car.AR_BALANCE

                         

                        , TO_DATE(TO_CHAR(ccea.COLLECTION_ACTION_DATE+19000000),'YYYY/MM/DD') COLLECTION_ACTION_DATE

                         

                        , cm.EXTERNAL_CREDIT_SCORE

                         

                        , cm.CUSTOMER_CATEGORY

                         

                        , cm.CUSTOMER_TYPE_CODE

                         

                        , cm.LANGUAGE_CODE || ccea.COLLECTION_EVENT_ID REPORT_NAME

                         

                        , UPPER(REPLACE(cd.SPOUSE_OR_ROOM_MATE,',',' ')) SPOUSE_OR_ROOM_MATE

                         

                        , UPPER(REPLACE(cd.RELATIVES_NAME,',',' ')) RELATIVES_NAME

                         

                        , cs.CUST_SRV_STMT_STATUS

                         

                        , ccea.STATEMENT_CODE

                         

                        , ccea.COLLECTION_EVENT_STATUS

                         

                        , car.SERVICE_CATEGORY_CODE

                         

                        , DATE_FORMAT(cm.CONNECT_DATE) CONNECT_DATE

                         

                        , cm.LANGUAGE_CODE

                         

                        , cd.VCR_DATA_CODE PAYMENT_BLOCK

                         

                        , NULL CUSTOM_A_50

                         

                        , NULL CUSTOM_B_50

                         

                        , NULL CUSTOM_C_50

                         

                        , NULL CUSTOMER_A_10

                         

                        , NULL CUSTOMER_B_10

                         

                        , NULL CUSTOMER_C_10

                         

                        , NULL COLL_ACT_CODE

                         

                        , cm.PIN_NUMBER

                         

                        , cs.INTERNAL_CREDIT_SCORE

                         

                        , NULL CUSTOM_A_1

                         

                        , NULL CUSTOM_B_1

                         

                        , CASE WHEN hm.BILL_TYPE_CODE != 'S' THEN 1 ELSE 2 END LOB

                         

                        FROM

                         

                        PSTAGE.PHX_CUSTOMER_MASTER cm

                         

                        LEFT OUTER JOIN PSTAGE.PHX_CUSTOMER_TELEPHONE ct

                         

                        ON cm.ACCOUNT_NUMBER = ct.ACCOUNT_NUMBER

                         

                        AND ct.PRIMADL = 'P'

                         

                        AND ct.CUST_TELEPHONE_STATUS = 'AC'

                         

                        AND ct.SERVICE_OCCURRENCE = '1'

                         

                        AND ct.SERVICE_CATEGORY_CODE = 'T'

                         

                        INNER JOIN

                         

                        PSTAGE.PHX_COLLECTION_CST_EVENT_ACT ccea

                         

                        ON cm.ACCOUNT_NUMBER = ccea.ACCOUNT_NUMBER

                         

                        INNER JOIN PSTAGE.PHX_HOUSE_MASTER hm

                         

                        ON cm.HOUSE_NUMBER = hm.HOUSE_NUMBER

                         

                        INNER JOIN PSTAGE.PHX_CUSTOMER_STATEMENTS cs

                         

                        ON ccea.STATEMENT_CODE = cs.STATEMENT_CODE

                         

                        AND cm.ACCOUNT_NUMBER = cs.ACCOUNT_NUMBER

                         

                        LEFT OUTER JOIN PSTAGE.PHX_CUSTOMER_DEMOGRAPHICS cd

                         

                        ON cm.ACCOUNT_NUMBER = cd.ACCOUNT_NUMBER

                         

                        INNER JOIN

                         

                        (

                         

                        SELECT

                         

                        car.ACCOUNT_NUMBER,

                         

                        SUM(car.AR_BALANCE) AR_BALANCE,

                         

                        SUM(car.CURRENT_AR_BALANCE) CURRENT_AR_BALANCE,

                         

                        SUM(car.AR_1_TO_30) AR_1_TO_30,

                         

                        SUM(car.AR_31_TO_60) AR_31_TO_60,

                         

                        SUM(car.AR_61_TO_90) AR_61_TO_90,

                         

                        SUM(car.AR_91_TO_120) AR_91_TO_120,

                         

                        SUM(car.AR_121_TO_150) AR_121_TO_150,

                         

                        SUM(car.AR_OVER_150_DAYS) AR_OVER_150_DAYS,

                         

                        MAX(CASE WHEN car.SERVICE_CATEGORY_CODE = 'C' THEN 'C' ELSE NULL END) ||

                         

                        MAX(CASE WHEN car.SERVICE_CATEGORY_CODE = 'D' THEN 'D' ELSE NULL END) ||

                         

                        MAX(CASE WHEN car.SERVICE_CATEGORY_CODE = 'T' THEN 'T' ELSE NULL END) ||

                         

                        MAX(CASE WHEN car.SERVICE_CATEGORY_CODE = 'H' THEN 'H' ELSE NULL END) SERVICE_CATEGORY_CODE

                         

                        FROM PSTAGE.PHX_CUSTOMER_AR_AGING car

                         

                        GROUP BY

                         

                        car.ACCOUNT_NUMBER

                         

                        ) car

                         

                        ON cm.ACCOUNT_NUMBER = car.ACCOUNT_NUMBER

                         

                        WHERE

                         

                        ccea.COLLECTION_ACTION_DATE = TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) - 19000000

                         

                        AND ccea.COLLECTION_EVENT_STATUS = 'P'

                         

                        call count cpu elapsed disk query current rows

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        Parse 1 1.36 1.37 2 6 0 0

                         

                        Execute 1 0.01 1.39 15 99 12 0

                         

                        Fetch 3568 0.65 39.78 0 0 0 53499

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        total 3570 2.04 42.55 17 105 12 53499

                         

                        Misses in library cache during parse: 1

                         

                        Optimizer mode: ALL_ROWS

                         

                        Parsing user id: SYS

                         

                        Number of plan statistics captured: 1

                         

                        Rows (1st) Rows (avg) Rows (max) Row Source Operation

                         

                        ---------- ---------- ---------- ---------------------------------------------------

                         

                        53499 53499 53499 PX COORDINATOR (cr=99 pr=15 pw=0 time=40628107 us)

                         

                        0 0 0 PX SEND QC (RANDOM) :TQ10009 (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

                         

                        0 0 0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

                         

                        0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

                         

                        0 0 0 PX SEND HASH :TQ10008 (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

                         

                        0 0 0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=13612 size=45535094 card=133534)

                         

                        0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=12610 size=45535094 card=133534)

                         

                        0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=10319 size=25063808 card=81376)

                         

                        0 0 0 PX SEND BROADCAST :TQ10007 (cr=0 pr=0 pw=0 time=0 us cost=10319 size=25063808 card=81376)

                         

                        0 0 0 HASH JOIN OUTER BUFFERED (cr=0 pr=0 pw=0 time=0 us cost=10319 size=25063808 card=81376)

                         

                        0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=9791 size=22378400 card=81376)

                         

                        0 0 0 PX SEND HASH :TQ10005 (cr=0 pr=0 pw=0 time=0 us cost=9791 size=22378400 card=81376)

                         

                        0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=9791 size=22378400 card=81376)

                         

                        0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=8194 size=17495840 card=81376)

                         

                        0 0 0 PX SEND BROADCAST :TQ10004 (cr=0 pr=0 pw=0 time=0 us cost=8194 size=17495840 card=81376)

                         

                        0 0 0 HASH JOIN OUTER BUFFERED (cr=0 pr=0 pw=0 time=0 us cost=8194 size=17495840 card=81376)

                         

                        0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=6958 size=14078048 card=81376)

                         

                        0 0 0 PX SEND HASH :TQ10002 (cr=0 pr=0 pw=0 time=0 us cost=6958 size=14078048 card=81376)

                         

                        0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=6958 size=14078048 card=81376)

                         

                        0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=3646 size=12387024 card=86021)

                         

                        0 0 0 PX SEND BROADCAST :TQ10001 (cr=0 pr=0 pw=0 time=0 us cost=3646 size=12387024 card=86021)

                         

                        0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=3646 size=12387024 card=86021)

                         

                        0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=322 size=3446788 card=84068)

                         

                        0 0 0 PX SEND BROADCAST :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=322 size=3446788 card=84068)

                         

                        0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=322 size=3446788 card=84068)

                         

                        0 0 0 TABLE ACCESS FULL PHX_CBLKREP (cr=0 pr=0 pw=0 time=0 us cost=322 size=3446788 card=84068)

                         

                        0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=3316 size=890258561 card=8643287)

                         

                        0 0 0 TABLE ACCESS FULL PHX_CUMSTPF (cr=0 pr=0 pw=0 time=0 us cost=3316 size=890258561 card=8643287)

                         

                        0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=3304 size=268569203 card=9261007)

                         

                        0 0 0 TABLE ACCESS FULL PHX_CBIZREP (cr=0 pr=0 pw=0 time=0 us cost=3304 size=268569203 card=9261007)

                         

                        0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=1235 size=22879122 card=544741)

                         

                        0 0 0 PX SEND HASH :TQ10003 (cr=0 pr=0 pw=0 time=0 us cost=1235 size=22879122 card=544741)

                         

                        0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=1235 size=22879122 card=544741)

                         

                        0 0 0 TABLE ACCESS FULL PHX_CMGTREP (cr=0 pr=0 pw=0 time=0 us cost=1235 size=22879122 card=544741)

                         

                        0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=1595 size=143327880 card=2388798)

                         

                        0 0 0 TABLE ACCESS FULL PHX_HOSTPF (cr=0 pr=0 pw=0 time=0 us cost=1595 size=143327880 card=2388798)

                         

                        0 0 0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=525 size=104338014 card=3161758)

                         

                        0 0 0 PX SEND HASH :TQ10006 (cr=0 pr=0 pw=0 time=0 us cost=525 size=104338014 card=3161758)

                         

                        0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=525 size=104338014 card=3161758)

                         

                        0 0 0 TABLE ACCESS FULL PHX_DEMOGPF (cr=0 pr=0 pw=0 time=0 us cost=525 size=104338014 card=3161758)

                         

                        0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=2279 size=457415673 card=13861081)

                         

                        0 0 0 TABLE ACCESS FULL PHX_CBI0REP (cr=0 pr=0 pw=0 time=0 us cost=2279 size=457415673 card=13861081)

                         

                        ********************************************************************************

                         

                        SQL ID: 7cfz5wy9caaf4 Plan Hash: 4015672053

                         

                        SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,

                         

                        'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,

                         

                        DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM

                         

                        FROM

                         

                        V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY

                         

                        NAME_COL_PLUS_SHOW_PARAM,ROWNUM

                         

                          

                         

                        call count cpu elapsed disk query current rows

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        Parse 2 0.00 0.00 0 0 0 0

                         

                        Execute 2 0.00 0.00 0 0 0 0

                         

                        Fetch 3 0.00 0.00 0 0 0 6

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        total 7 0.00 0.00 0 0 0 6

                         

                        Misses in library cache during parse: 1

                         

                        Misses in library cache during execute: 1

                         

                        Optimizer mode: ALL_ROWS

                         

                        Parsing user id: SYS

                         

                        Number of plan statistics captured: 2

                         

                        Rows (1st) Rows (avg) Rows (max) Row Source Operation

                         

                        ---------- ---------- ---------- ---------------------------------------------------

                         

                        6 3 6 SORT ORDER BY (cr=0 pr=0 pw=0 time=2350 us cost=2 size=57 card=1)

                         

                        6 3 6 COUNT (cr=0 pr=0 pw=0 time=2142 us)

                         

                        6 3 6 HASH JOIN (cr=0 pr=0 pw=0 time=2140 us cost=1 size=57 card=1)

                         

                        51 26 51 FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=1107 us cost=0 size=273 card=7)

                         

                        2752 1376 2752 FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=440 us cost=0 size=49536 card=2752)

                         

                          

                         

                          

                         

                          

                         

                        ********************************************************************************

                         

                        OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

                         

                        call count cpu elapsed disk query current rows

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        Parse 3 1.37 1.37 2 6 0 0

                         

                        Execute 4 0.01 1.39 15 99 12 0

                         

                        Fetch 3571 0.66 39.78 0 0 0 53505

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        total 3578 2.05 42.55 17 105 12 53505

                         

                        Misses in library cache during parse: 2

                         

                        Misses in library cache during execute: 2

                         

                          

                         

                        OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

                         

                        call count cpu elapsed disk query current rows

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        Parse 20 0.00 0.00 0 0 0 0

                         

                        Execute 20 0.00 0.00 0 0 0 0

                         

                        Fetch 20 0.03 0.65 205 748 3 20

                         

                        ------- ------ -------- ---------- ---------- ---------- ---------- ----------

                         

                        total 60 0.03 0.66 205 748 3 20

                         

                        Misses in library cache during parse: 3

                         

                        Misses in library cache during execute: 1

                         

                        4 user SQL statements in session.

                         

                        9 internal SQL statements in session.

                         

                        13 SQL statements in session.

                         

                        ********************************************************************************

                         

                        Trace file: PSTAGE2_ora_6210.trc

                         

                        Trace file compatibility: 11.1.0.7

                         

                        Sort options: default

                         

                        1 session in tracefile.

                         

                        4 user SQL statements in trace file.

                         

                        9 internal SQL statements in trace file.

                         

                        13 SQL statements in trace file.

                         

                        6 unique SQL statements in trace file.

                         

                        4514 lines in trace file.

                         

                        731 elapsed seconds in trace file.

                         

                          

                         

                        {code}