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

Tuning SQL Query

GeetaM Newbie
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

     

    Regards

  • 5. Re: Tuning SQL Query
    ranit B Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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} 

     


Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points