TKPROF: Release 11.1.0.7.0 - Production on Wed Sep 16 04:25:42 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: /usr/oracle_su/diag/rdbms/sudr/SUDR/trace/SUDR_ora_26362.trc
Sort options: execpu  exeela  fchcpu  
********************************************************************************
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
********************************************************************************

Error in CREATE TABLE of EXPLAIN PLAN table: STR_RO_RTP_NETAPP.prof$plan_table
ORA-01031: insufficient privileges

EXPLAIN PLAN option disabled.
********************************************************************************

SELECT
  :"SYS_B_00" AS source_system,
  customer_name,
  whs_inbound_order_no,
  source_ib_order_level,
  request_number,
  source_order_type,
  whs_order_line_no,
  whs_segment,
  quantity_requested,
  source_service_type,
  shipment_no,
  receipt_number,
  receipt_line_number,
  lpn_number,
  lot_number,
  batch_number,
  revision_number,
  country_of_origin,
  actual_part_serial_number,
  disposition_code,
  quantity_received,
  quantity_uom,
  weight,
  weight_uom,
  :"SYS_B_01" AS volume,
  volume_uom,
  source_whs_name,
  part_number,
  product_class,
  source_awb_mcr,
  cource_carrier_name,
  NULL AS delivery_record_number,
  batch_task_number,
  putaway_task_number,
  whs_zone,
  to_bin_location,
  NULL AS delivery_record_creat_date_loc,
  NULL AS delivery_record_creat_date_gmt,
  NULL AS label_printtimestamp_loc,
  NULL AS label_printtimestamp_gmt,
  sib_whs_order_status,
  receipt_status,
  source_system_part_status,
  timeconvertor_oracle (truck_arrival_time_gmt, :"SYS_B_02", local_time) AS
  truck_arrival_time_loc,
  truck_arrival_time_gmt,
  timeconvertor_oracle (system_part_receipt_date_gmt, :"SYS_B_03", local_time) AS
  system_part_receipt_date_loc,
  system_part_receipt_date_gmt,
  timeconvertor_oracle (source_part_putaway_date_gmt, :"SYS_B_04", local_time) AS
  source_part_putaway_date_loc,
  source_part_putaway_date_gmt,
  timeconvertor_oracle (dts_start_time_gmt, :"SYS_B_05", local_time) AS
  dts_start_time_loc,
  dts_start_time_gmt,
  timeconvertor_oracle (dts_completion_date_gmt, :"SYS_B_06", local_time) AS
  dts_completion_date_loc,
  dts_completion_date_gmt,
  timeconvertor_oracle (expiry_date_gmt, :"SYS_B_07", local_time) AS expiry_date_loc,
  expiry_date_gmt,
  timeconvertor_oracle (reporting_date_gmt, :"SYS_B_08", local_time) AS
  reporting_date_loc,
  reporting_date_gmt,
  timeconvertor_oracle (billing_date_gmt, :"SYS_B_09", local_time) AS
  billing_date_loc,
  billing_date_gmt,
  NULL AS product_class_2,
  NULL AS truck_license_plate,
  NULL AS source_dts_reason_code,
  NULL AS dock_to_stock_rc_comments,
  oh_modifyts,
  ol_modifyts,
  sh_modifyts,
  sl_modifyts,
  oh_createts
FROM
  (
  WITH
    order_h AS
    (
      SELECT
        *
      FROM
        yfs_order_header oh
      WHERE
        EXISTS
        (
          SELECT
            :"SYS_B_10"
          FROM
            ea_mv_refresh.fact05_order_header ea
          WHERE
            ea.order_header_key = oh.order_header_key
        )
    )
    ,
    order_line AS
    (
      SELECT
        ol.*
      FROM
        yfs_order_line ol
      INNER JOIN order_h oh
      ON
        (
          oh.order_header_key = ol.order_header_key
        )
    )
    ,
    itm AS
    (
      SELECT
        item.unit_length_uom
        || :"SYS_B_11"
        || item.unit_width_uom
        || :"SYS_B_12"
        || item.unit_height_uom AS volume_uom,
        item.item_id,
        item.organization_code,
        item.uom
      FROM
        yfs_item item,
        order_line ol,
        order_h oh
      WHERE
        item.item_id             = ol.item_id
      AND item.organization_code = oh.enterprise_key
      AND item.uom               = ol.uom
      AND ol.order_header_key    = oh.order_header_key
    )
  SELECT
    TRIM (oh.enterprise_key)        AS customer_name,
    TRIM (oh.order_no)              AS whs_inbound_order_no,
    TRIM (oh.extn_dhl_service_type) AS source_ib_order_level,
    TRIM (oh.extn_reference_1)      AS request_number,
    CASE oh.document_type
      WHEN :"SYS_B_13"
      THEN :"SYS_B_14"
      WHEN :"SYS_B_15"
      THEN :"SYS_B_16"
      ELSE oh.document_type
    END                     AS source_order_type,
    TRIM (ol.prime_line_no) AS whs_order_line_no,
    TRIM (ol.segment)       AS whs_segment,
    ol.ordered_qty          AS quantity_requested,
    --TRIM(cc.code_long_description)  AS source_service_type,
    (
      SELECT
        code_long_description
      FROM
        yfs_common_code
      WHERE
        TRIM (code_type)    = :"SYS_B_17"
      AND organization_code = :"SYS_B_18"
      AND code_value        = oh.extn_dhl_service_type
    )                           AS source_service_type,
    TRIM (sh.shipment_no)       AS shipment_no,
    TRIM (rh.receipt_no)        AS receipt_number,
    TRIM (rl.receipt_line_no)   AS receipt_line_number,
    TRIM (rl.lpn_no)            AS lpn_number,
    TRIM (rl.lot_number)        AS lot_number,
    TRIM (rl.batch_no)          AS batch_number,
    TRIM (rl.revision_no)       AS revision_number,
    TRIM (rl.country_of_origin) AS country_of_origin,
    TRIM (rl.serial_no)         AS actual_part_serial_number,
    TRIM (rl.disposition_code)  AS disposition_code,
    rl.quantity                 AS quantity_received,
    rl.uom                      AS quantity_uom,
    rl.net_weight               AS weight,
    rl.net_weight_uom           AS weight_uom,
    /*item.unit_length || ' '
    || item.unit_width || ' '
    || item.unit_height         AS volume,*/
    --0                                AS volume,
    item.volume_uom AS volume_uom,
    TRIM ( COALESCE (rh.receivingnode_key, oh.receiving_node, ol.receiving_node
    ))                                                     AS source_whs_name,
    NVL (TRIM (rl.item_id), TRIM (ol.item_id))             AS part_number,
    NVL (TRIM (rl.product_class), TRIM (ol.product_class)) AS product_class,
    NVL (TRIM (rh.extn_airwaybill_no), TRIM (sh.bol_no))   AS source_awb_mcr,
    NVL (TRIM (rh.extn_carrier_service), TRIM (sh.scac))   AS
    cource_carrier_name,
    TRIM (ts.batch_no)           AS batch_task_number,
    TRIM (ts.task_id)            AS putaway_task_number,
    TRIM (ts.target_zone_id)     AS whs_zone,
    TRIM (ts.target_location_id) AS to_bin_location,
    DECODE (
    (
      SELECT
        SUM (ol.ordered_qty) FROM yfs_order_release_status sts WHERE
        sts.order_header_key  = oh.order_header_key
      AND sts.order_line_key  = ol.order_line_key
      AND sts.status_quantity > :"SYS_B_19"
    )
    , :"SYS_B_20", :"SYS_B_21", DECODE (
    (
      SELECT
        SUM (ol.ordered_qty) FROM yfs_order_release_status sts WHERE
        sts.order_header_key  = oh.order_header_key
      AND sts.order_line_key  = ol.order_line_key
      AND sts.status_quantity > :"SYS_B_22"
    )
    ,
    (
      SELECT
        SUM (sts.status_quantity)
      FROM
        yfs_order_release_status sts
      WHERE
        sts.order_header_key  = oh.order_header_key
      AND sts.order_line_key  = ol.order_line_key
      AND sts.status_quantity > :"SYS_B_23"
    )
    ,
    (
      SELECT
        st.description
      FROM
        yfs_status st,
        yfs_pipeline p
      WHERE
        st.status =
        (
          SELECT
            MAX ( status)
          FROM
            yfs_order_release_status sts
          WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_24"
        )
      AND p.process_type_key = st.process_type_key
      AND p.pipeline_key     =
        (
          SELECT DISTINCT
            ors.pipeline_key
          FROM
            yfs_order_release_status ors
          WHERE
            ors.order_header_key  = oh.order_header_key
          AND ors.order_line_key  = ol.order_line_key
          AND ors.status_quantity > :"SYS_B_25"
        )
    )
    , DECODE (
    (
      SELECT
        MIN (status) FROM yfs_order_release_status sts WHERE
        sts.order_header_key  = oh.order_header_key
      AND sts.order_line_key  = ol.order_line_key
      AND sts.status_quantity > :"SYS_B_26"
    )
    ,
    (
      SELECT
        MAX (status)
      FROM
        yfs_order_release_status sts
      WHERE
        sts.order_header_key  = oh.order_header_key
      AND sts.order_line_key  = ol.order_line_key
      AND sts.status_quantity > :"SYS_B_27"
    )
    ,
    (
      SELECT
        st.description
      FROM
        yfs_status st,
        yfs_pipeline p
      WHERE
        st.status =
        (
          SELECT
            MIN ( status)
          FROM
            yfs_order_release_status sts
          WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_28"
        )
      AND p.process_type_key = st.process_type_key
      AND p.pipeline_key     =
        (
          SELECT DISTINCT
            ors.pipeline_key
          FROM
            yfs_order_release_status ors
          WHERE
            ors.order_header_key  = oh.order_header_key
          AND ors.order_line_key  = ol.order_line_key
          AND ors.status_quantity > :"SYS_B_29"
        )
    )
    , DECODE (
    (
      SELECT
        MIN (status) FROM yfs_order_release_status sts WHERE
        sts.order_header_key  = oh.order_header_key
      AND sts.order_line_key  = ol.order_line_key
      AND sts.status_quantity > :"SYS_B_30"
    )
    ,
    (
      SELECT
        MAX (status)
      FROM
        yfs_order_release_status sts
      WHERE
        sts.order_header_key  = oh.order_header_key
      AND sts.order_line_key  = ol.order_line_key
      AND sts.status_quantity > :"SYS_B_31"
      AND sts.status          < :"SYS_B_32"
    )
    ,
    (
      SELECT
        st.description
      FROM
        yfs_status st,
        yfs_pipeline p
      WHERE
        st.status =
        (
          SELECT
            MIN ( status)
          FROM
            yfs_order_release_status sts
          WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_33"
        )
      AND p.process_type_key = st.process_type_key
      AND p.pipeline_key     =
        (
          SELECT DISTINCT
            ors.pipeline_key
          FROM
            yfs_order_release_status ors
          WHERE
            ors.order_header_key  = oh.order_header_key
          AND ors.order_line_key  = ol.order_line_key
          AND ors.status_quantity > :"SYS_B_34"
        )
    )
    ,
    (
      SELECT
        :"SYS_B_35"
        || st.description
      FROM
        yfs_status st,
        yfs_pipeline p
      WHERE
        st.status =
        (
          SELECT
            MAX (status)
          FROM
            yfs_order_release_status sts
          WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_36"
          AND sts.status          < :"SYS_B_37"
        )
      AND p.process_type_key = st.process_type_key
      AND p.pipeline_key     =
        (
          SELECT DISTINCT
            ors.pipeline_key
          FROM
            yfs_order_release_status ors
          WHERE
            ors.order_header_key  = oh.order_header_key
          AND ors.order_line_key  = ol.order_line_key
          AND ors.status_quantity > :"SYS_B_38"
        )
    )
    ))))                       AS sib_whs_order_status,
    TRIM (rhst.description)    AS receipt_status,
    TRIM (rhst.description)    AS source_system_part_status,
    rh.extn_truck_arrival_time AS truck_arrival_time_gmt,
    CASE
      WHEN TRIM (rh.status) = :"SYS_B_39"
      THEN rh.status_date
      ELSE NULL
    END AS system_part_receipt_date_gmt,
    /* CR108-d */
    --rcpt_status.new_status_date     AS source_part_putaway_date_gmt,
    (
      SELECT
        MAX (rcpt_status.new_status_date)
      FROM
        yfs_receipt_status_audit rcpt_status
      WHERE
        rcpt_status.receipt_header_key = rh.receipt_header_key
      AND rcpt_status.new_status       = :"SYS_B_40"
    )                          AS source_part_putaway_date_gmt,
    rh.extn_truck_arrival_time AS dts_start_time_gmt,
    /* CR108-d */
    --rcpt_status.new_status_date     AS dts_completion_date_gmt,
    (
      SELECT
        MAX (rcpt_status.new_status_date)
      FROM
        yfs_receipt_status_audit rcpt_status
      WHERE
        rcpt_status.receipt_header_key = rh.receipt_header_key
      AND rcpt_status.new_status       = :"SYS_B_41"
    )                                                              AS dts_completion_date_gmt,
    NULLIF (rl.ship_by_date, TO_DATE (:"SYS_B_42", :"SYS_B_43")) AS
    expiry_date_gmt,
    CASE
      WHEN DECODE (
        (
          SELECT
            SUM (ol.ordered_qty) FROM yfs_order_release_status sts WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_44"
        )
        , :"SYS_B_45", :"SYS_B_46", DECODE (
        (
          SELECT
            SUM (ol.ordered_qty) FROM yfs_order_release_status sts WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_47"
        )
        ,
        (
          SELECT
            SUM (sts.status_quantity)
          FROM
            yfs_order_release_status sts
          WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_48"
        )
        ,
        (
          SELECT
            st.description
          FROM
            yfs_status st,
            yfs_pipeline p
          WHERE
            st.status =
            (
              SELECT
                MAX ( status)
              FROM
                yfs_order_release_status sts
              WHERE
                sts.order_header_key  = oh.order_header_key
              AND sts.order_line_key  = ol.order_line_key
              AND sts.status_quantity > :"SYS_B_49"
            )
          AND p.process_type_key = st.process_type_key
          AND p.pipeline_key     =
            (
              SELECT DISTINCT
                ors.pipeline_key
              FROM
                yfs_order_release_status ors
              WHERE
                ors.order_header_key  = oh.order_header_key
              AND ors.order_line_key  = ol.order_line_key
              AND ors.status_quantity > :"SYS_B_50"
            )
        )
        , DECODE (
        (
          SELECT
            MIN (status) FROM yfs_order_release_status sts WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_51"
        )
        ,
        (
          SELECT
            MAX (status)
          FROM
            yfs_order_release_status sts
          WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_52"
        )
        ,
        (
          SELECT
            st.description
          FROM
            yfs_status st,
            yfs_pipeline p
          WHERE
            st.status =
            (
              SELECT
                MIN ( status)
              FROM
                yfs_order_release_status sts
              WHERE
                sts.order_header_key  = oh.order_header_key
              AND sts.order_line_key  = ol.order_line_key
              AND sts.status_quantity > :"SYS_B_53"
            )
          AND p.process_type_key = st.process_type_key
          AND p.pipeline_key     =
            (
              SELECT DISTINCT
                ors.pipeline_key
              FROM
                yfs_order_release_status ors
              WHERE
                ors.order_header_key  = oh.order_header_key
              AND ors.order_line_key  = ol.order_line_key
              AND ors.status_quantity > :"SYS_B_54"
            )
        )
        , DECODE (
        (
          SELECT
            MIN (status) FROM yfs_order_release_status sts WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_55"
        )
        ,
        (
          SELECT
            MAX (status)
          FROM
            yfs_order_release_status sts
          WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_56"
          AND sts.status          < :"SYS_B_57"
        )
        ,
        (
          SELECT
            st.description
          FROM
            yfs_status st,
            yfs_pipeline p
          WHERE
            st.status =
            (
              SELECT
                MIN ( status)
              FROM
                yfs_order_release_status sts
              WHERE
                sts.order_header_key  = oh.order_header_key
              AND sts.order_line_key  = ol.order_line_key
              AND sts.status_quantity > :"SYS_B_58"
            )
          AND p.process_type_key = st.process_type_key
          AND p.pipeline_key     =
            (
              SELECT DISTINCT
                ors.pipeline_key
              FROM
                yfs_order_release_status ors
              WHERE
                ors.order_header_key  = oh.order_header_key
              AND ors.order_line_key  = ol.order_line_key
              AND ors.status_quantity > :"SYS_B_59"
            )
        )
        ,
        (
          SELECT
            :"SYS_B_60"
            || st.description
          FROM
            yfs_status st,
            yfs_pipeline p
          WHERE
            st.status =
            (
              SELECT
                MAX (status)
              FROM
                yfs_order_release_status sts
              WHERE
                sts.order_header_key  = oh.order_header_key
              AND sts.order_line_key  = ol.order_line_key
              AND sts.status_quantity > :"SYS_B_61"
              AND sts.status          < :"SYS_B_62"
            )
          AND p.process_type_key = st.process_type_key
          AND p.pipeline_key     =
            (
              SELECT DISTINCT
                ors.pipeline_key
              FROM
                yfs_order_release_status ors
              WHERE
                ors.order_header_key  = oh.order_header_key
              AND ors.order_line_key  = ol.order_line_key
              AND ors.status_quantity > :"SYS_B_63"
            )
        )
        )))) = :"SYS_B_64"
      THEN cancelled_date.status_date --cancelled_date.status_date
      ELSE
        (
          SELECT
            MAX (rcpt_status.new_status_date)
          FROM
            yfs_receipt_status_audit rcpt_status
          WHERE
            rcpt_status.receipt_header_key = rh.receipt_header_key
          AND rcpt_status.new_status       = :"SYS_B_65"
        ) --rcpt_status.new_status_date-- rcpt_status.new_status_date
    END AS reporting_date_gmt,
    CASE
      WHEN DECODE (
        (
          SELECT
            SUM (ol.ordered_qty) FROM yfs_order_release_status sts WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_66"
        )
        , :"SYS_B_67", :"SYS_B_68", DECODE (
        (
          SELECT
            SUM (ol.ordered_qty) FROM yfs_order_release_status sts WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_69"
        )
        ,
        (
          SELECT
            SUM (sts.status_quantity)
          FROM
            yfs_order_release_status sts
          WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_70"
        )
        ,
        (
          SELECT
            st.description
          FROM
            yfs_status st,
            yfs_pipeline p
          WHERE
            st.status =
            (
              SELECT
                MAX ( status)
              FROM
                yfs_order_release_status sts
              WHERE
                sts.order_header_key  = oh.order_header_key
              AND sts.order_line_key  = ol.order_line_key
              AND sts.status_quantity > :"SYS_B_71"
            )
          AND p.process_type_key = st.process_type_key
          AND p.pipeline_key     =
            (
              SELECT DISTINCT
                ors.pipeline_key
              FROM
                yfs_order_release_status ors
              WHERE
                ors.order_header_key  = oh.order_header_key
              AND ors.order_line_key  = ol.order_line_key
              AND ors.status_quantity > :"SYS_B_72"
            )
        )
        , DECODE (
        (
          SELECT
            MIN (status) FROM yfs_order_release_status sts WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_73"
        )
        ,
        (
          SELECT
            MAX (status)
          FROM
            yfs_order_release_status sts
          WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_74"
        )
        ,
        (
          SELECT
            st.description
          FROM
            yfs_status st,
            yfs_pipeline p
          WHERE
            st.status =
            (
              SELECT
                MIN ( status)
              FROM
                yfs_order_release_status sts
              WHERE
                sts.order_header_key  = oh.order_header_key
              AND sts.order_line_key  = ol.order_line_key
              AND sts.status_quantity > :"SYS_B_75"
            )
          AND p.process_type_key = st.process_type_key
          AND p.pipeline_key     =
            (
              SELECT DISTINCT
                ors.pipeline_key
              FROM
                yfs_order_release_status ors
              WHERE
                ors.order_header_key  = oh.order_header_key
              AND ors.order_line_key  = ol.order_line_key
              AND ors.status_quantity > :"SYS_B_76"
            )
        )
        , DECODE (
        (
          SELECT
            MIN (status) FROM yfs_order_release_status sts WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_77"
        )
        ,
        (
          SELECT
            MAX (status)
          FROM
            yfs_order_release_status sts
          WHERE
            sts.order_header_key  = oh.order_header_key
          AND sts.order_line_key  = ol.order_line_key
          AND sts.status_quantity > :"SYS_B_78"
          AND sts.status          < :"SYS_B_79"
        )
        ,
        (
          SELECT
            st.description
          FROM
            yfs_status st,
            yfs_pipeline p
          WHERE
            st.status =
            (
              SELECT
                MIN ( status)
              FROM
                yfs_order_release_status sts
              WHERE
                sts.order_header_key  = oh.order_header_key
              AND sts.order_line_key  = ol.order_line_key
              AND sts.status_quantity > :"SYS_B_80"
            )
          AND p.process_type_key = st.process_type_key
          AND p.pipeline_key     =
            (
              SELECT DISTINCT
                ors.pipeline_key
              FROM
                yfs_order_release_status ors
              WHERE
                ors.order_header_key  = oh.order_header_key
              AND ors.order_line_key  = ol.order_line_key
              AND ors.status_quantity > :"SYS_B_81"
            )
        )
        ,
        (
          SELECT
            :"SYS_B_82"
            || st.description
          FROM
            yfs_status st,
            yfs_pipeline p
          WHERE
            st.status =
            (
              SELECT
                MAX (status)
              FROM
                yfs_order_release_status sts
              WHERE
                sts.order_header_key  = oh.order_header_key
              AND sts.order_line_key  = ol.order_line_key
              AND sts.status_quantity > :"SYS_B_83"
              AND sts.status          < :"SYS_B_84"
            )
          AND p.process_type_key = st.process_type_key
          AND p.pipeline_key     =
            (
              SELECT DISTINCT
                ors.pipeline_key
              FROM
                yfs_order_release_status ors
              WHERE
                ors.order_header_key  = oh.order_header_key
              AND ors.order_line_key  = ol.order_line_key
              AND ors.status_quantity > :"SYS_B_85"
            )
        )
        )))) = :"SYS_B_86"
      THEN cancelled_date.status_date --cancelled_date.status_date
      ELSE
        (
          SELECT
            MAX (rcpt_status.new_status_date)
          FROM
            yfs_receipt_status_audit rcpt_status
          WHERE
            rcpt_status.receipt_header_key = rh.receipt_header_key
          AND rcpt_status.new_status       = :"SYS_B_87"
        ) --rcpt_status.new_status_date --rcpt_status.new_status_date
    END AS billing_date_gmt,
    -- QC2443: For cancelled order, map cancelled date
    --lo.timezone,
    --(SELECT lo.timezone FROM  yfs_locale lo INNER JOIN  yfs_organization og
    -- ON (og.locale_code = lo.localecode) WHERE og.is_node = 'Y' AND
    -- og.organization_key = ol.receiving_node)     AS timezone,
    --cancelled_date.status_date rs_status_date,
    -- technical columns
    oh.modifyts   AS oh_modifyts,
    ol.modifyts   AS ol_modifyts,
    sh.modifyts   AS sh_modifyts,
    sl.modifyts   AS sl_modifyts,
    oh.order_date AS oh_createts,
    (
      SELECT
        lo.timezone
      FROM
        yfs_locale lo
      INNER JOIN yfs_organization og
      ON
        (
          og.locale_code = lo.localecode
        )
      WHERE
        og.is_node            = :"SYS_B_88"
      AND og.organization_key = ol.receiving_node
    ) AS local_time
  FROM
    order_h oh
  INNER JOIN order_line ol
  ON
    (
      oh.order_header_key = ol.order_header_key
    )
    --INNER JOIN dhl_service_type cc ON cc.code_value =
    -- oh.extn_dhl_service_type
  INNER JOIN itm item
  ON
    (
      item.item_id             = ol.item_id
    AND item.organization_code = oh.enterprise_key
    AND item.uom               = ol.uom
    )
    --INNER JOIN locale lo ON (lo.organization_key = ol.receiving_node)
  LEFT JOIN yfs_order_release_status cancelled_date
  ON
    (
      cancelled_date.order_line_key    = ol.order_line_key
    AND cancelled_date.status_quantity > :"SYS_B_89"
    AND cancelled_date.status          = :"SYS_B_90"
    )
  LEFT JOIN yfs_receipt_line rl
  ON
    (
      ol.order_line_key = rl.order_line_key
    )
  LEFT JOIN ( yfs_receipt_header rh
  INNER JOIN yfs_status rhst
  ON
    (
      rhst.status = rh.status
    )
  INNER JOIN yfs_pipeline rhp
  ON
    (
      rhp.process_type_key = rhst.process_type_key
    AND rhp.pipeline_key   = rh.pipeline_key
    )
    )
  ON
    (
      rh.receipt_header_key = rl.receipt_header_key
    )
  LEFT JOIN yfs_shipment sh
  ON
    (
      sh.shipment_key = rh.shipment_key
    )
  LEFT JOIN yfs_task ts
  ON
    (
      ts.receipt_header_key = rh.receipt_header_key
    AND ts.item_id          = rl.item_id
    AND ts.source_lpn_no    = rl.lpn_no
    AND ts.task_type        = :"SYS_B_91"
    AND ts.task_status     <> :"SYS_B_92"
    )
    --LEFT JOIN  yfs_task_status_audit tsa ON (tsa.task_key = ts.task_key AND
    -- tsa.new_status = '2000')
    /* CR108-d: Received timestamps */
    /*LEFT JOIN  yfs_receipt_status_audit rcpt_status ON (
    rcpt_status.receipt_header_key = rh.receipt_header_key
    AND rcpt_status.new_status = '1500           '  -- "Received"
    )        */
  LEFT JOIN yfs_shipment_line sl
  ON
    sl.shipment_key     = sh.shipment_key
  AND ol.order_line_key = sl.order_line_key
  )
WHERE
  :"SYS_B_93" = :"SYS_B_94"

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1     38.37     270.33     816013    1791542       4338           0
Fetch      373     20.48     182.36      40243    1092500          0        5581
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      375     58.88     452.72     856256    2884042       4338        5581

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 138  

Rows     Row Source Operation
-------  ---------------------------------------------------
      4  TABLE ACCESS BY INDEX ROWID YFS_COMMON_CODE (cr=52 pr=0 pw=0 time=0 us cost=3 size=75 card=1)
      4   INDEX RANGE SCAN YFS_COMMON_CODE_I1 (cr=48 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 216322)
     46  SORT AGGREGATE (cr=11511 pr=283 pw=0 time=0 us)
     51   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=11511 pr=283 pw=0 time=112 us cost=5 size=53 card=1)
    315    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=136 pr=44 pw=0 time=109 us cost=4 size=0 card=2)(object id 216361)
     24  SORT AGGREGATE (cr=205 pr=0 pw=0 time=0 us)
     29   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=205 pr=0 pw=0 time=73 us cost=5 size=53 card=1)
    148    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=85 pr=0 pw=0 time=68 us cost=4 size=0 card=2)(object id 216361)
     24  SORT AGGREGATE (cr=205 pr=0 pw=0 time=0 us)
     29   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=205 pr=0 pw=0 time=67 us cost=5 size=53 card=1)
    148    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=85 pr=0 pw=0 time=67 us cost=4 size=0 card=2)(object id 216361)
     21  NESTED LOOPS  (cr=459 pr=0 pw=0 time=0 us cost=2 size=109 card=1)
     21   TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=223 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
     21    INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=202 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
     21     HASH UNIQUE (cr=186 pr=0 pw=0 time=0 us cost=6 size=78 card=1)
     21      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=186 pr=0 pw=0 time=0 us cost=5 size=78 card=1)
    122       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=86 pr=0 pw=0 time=55 us cost=4 size=0 card=2)(object id 216361)
     21   TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=236 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
     21    INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=215 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
     21     SORT AGGREGATE (cr=179 pr=0 pw=0 time=0 us)
     21      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=179 pr=0 pw=0 time=0 us cost=5 size=69 card=1)
    122       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=81 pr=0 pw=0 time=49 us cost=4 size=0 card=2)(object id 216361)
      3  SORT AGGREGATE (cr=34 pr=0 pw=0 time=0 us)
      8   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=34 pr=0 pw=0 time=69 us cost=5 size=69 card=1)
     26    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=12 pr=0 pw=0 time=16 us cost=4 size=0 card=2)(object id 216361)
      3  SORT AGGREGATE (cr=34 pr=0 pw=0 time=0 us)
      8   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=34 pr=0 pw=0 time=68 us cost=5 size=69 card=1)
     26    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=12 pr=0 pw=0 time=16 us cost=4 size=0 card=2)(object id 216361)
      2  NESTED LOOPS  (cr=66 pr=0 pw=0 time=0 us cost=2 size=109 card=1)
      2   TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=32 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
      2    INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=30 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
      2     HASH UNIQUE (cr=28 pr=0 pw=0 time=0 us cost=6 size=78 card=1)
      6      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=28 pr=0 pw=0 time=83 us cost=5 size=78 card=1)
     24       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=8 pr=0 pw=0 time=15 us cost=4 size=0 card=2)(object id 216361)
      2   TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=34 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
      2    INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=32 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
      2     SORT AGGREGATE (cr=28 pr=0 pw=0 time=0 us)
      6      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=28 pr=0 pw=0 time=70 us cost=5 size=69 card=1)
     24       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=8 pr=0 pw=0 time=16 us cost=4 size=0 card=2)(object id 216361)
      1  SORT AGGREGATE (cr=6 pr=0 pw=0 time=0 us)
      2   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=6 pr=0 pw=0 time=8 us cost=5 size=69 card=1)
      2    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=4 pr=0 pw=0 time=1 us cost=4 size=0 card=2)(object id 216361)
      1  SORT AGGREGATE (cr=5 pr=0 pw=0 time=0 us)
      1   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=5 pr=0 pw=0 time=0 us cost=5 size=69 card=1)
      1    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=4 pr=0 pw=0 time=0 us cost=4 size=0 card=2)(object id 216361)
      1  NESTED LOOPS  (cr=17 pr=0 pw=0 time=0 us cost=2 size=109 card=1)
      1   TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=8 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
      1    INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=7 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
      1     HASH UNIQUE (cr=6 pr=0 pw=0 time=0 us cost=6 size=78 card=1)
      2      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=6 pr=0 pw=0 time=18 us cost=5 size=78 card=1)
      2       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=4 pr=0 pw=0 time=3 us cost=4 size=0 card=2)(object id 216361)
      1   TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=9 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
      1    INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=8 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
      1     SORT AGGREGATE (cr=6 pr=0 pw=0 time=0 us)
      2      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=6 pr=0 pw=0 time=8 us cost=5 size=69 card=1)
      2       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=4 pr=0 pw=0 time=2 us cost=4 size=0 card=2)(object id 216361)
      0  NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=109 card=1)
      0   TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=0 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
      0    INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
      0     HASH UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=6 size=78 card=1)
      0      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=0 pr=0 pw=0 time=0 us cost=5 size=78 card=1)
      0       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=2)(object id 216361)
      0   TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=0 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
      0    INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
      0     SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
      0      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=0 pr=0 pw=0 time=0 us cost=5 size=69 card=1)
      0       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=2)(object id 216361)
     10  NESTED LOOPS  (cr=44 pr=25 pw=0 time=0 us cost=3 size=56 card=1)
     10   TABLE ACCESS BY INDEX ROWID YFS_ORGANIZATION (cr=27 pr=19 pw=0 time=0 us cost=2 size=34 card=1)
     10    INDEX UNIQUE SCAN YFS_ORGANIZATION_PK (cr=17 pr=9 pw=0 time=0 us cost=1 size=0 card=1)(object id 95935)
     10   TABLE ACCESS BY INDEX ROWID YFS_LOCALE (cr=17 pr=6 pw=0 time=0 us cost=1 size=4136 card=188)
     10    INDEX UNIQUE SCAN YFS_LOCALE_I1 (cr=7 pr=2 pw=0 time=0 us cost=0 size=0 card=1)(object id 96352)
     22  SORT AGGREGATE (cr=3768 pr=137 pw=0 time=0 us)
     21   TABLE ACCESS BY INDEX ROWID YFS_RECEIPT_STATUS_AUDIT (cr=3768 pr=137 pw=0 time=0 us cost=6 size=49 card=1)
     65    INDEX RANGE SCAN YFS_RECEIPT_STATUS_AUDIT_I1 (cr=3711 pr=121 pw=0 time=47 us cost=3 size=0 card=3)(object id 96840)
     22  SORT AGGREGATE (cr=114 pr=0 pw=0 time=0 us)
     21   TABLE ACCESS BY INDEX ROWID YFS_RECEIPT_STATUS_AUDIT (cr=114 pr=0 pw=0 time=0 us cost=6 size=49 card=1)
     65    INDEX RANGE SCAN YFS_RECEIPT_STATUS_AUDIT_I1 (cr=58 pr=0 pw=0 time=29 us cost=3 size=0 card=3)(object id 96840)
     46  SORT AGGREGATE (cr=285 pr=0 pw=0 time=0 us)
     51   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=285 pr=0 pw=0 time=75 us cost=5 size=53 card=1)
    315    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=133 pr=0 pw=0 time=85 us cost=4 size=0 card=2)(object id 216361)
     24  SORT AGGREGATE (cr=205 pr=0 pw=0 time=0 us)
     29   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=205 pr=0 pw=0 time=74 us cost=5 size=53 card=1)
    148    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=85 pr=0 pw=0 time=67 us cost=4 size=0 card=2)(object id 216361)
     24  SORT AGGREGATE (cr=205 pr=0 pw=0 time=0 us)
     29   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=205 pr=0 pw=0 time=73 us cost=5 size=53 card=1)
    148    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=85 pr=0 pw=0 time=65 us cost=4 size=0 card=2)(object id 216361)
     21  NESTED LOOPS  (cr=459 pr=0 pw=0 time=0 us cost=2 size=109 card=1)
     21   TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=223 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
     21    INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=202 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
     21     HASH UNIQUE (cr=186 pr=0 pw=0 time=0 us cost=6 size=78 card=1)
     21      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=186 pr=0 pw=0 time=0 us cost=5 size=78 card=1)
    122       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=86 pr=0 pw=0 time=51 us cost=4 size=0 card=2)(object id 216361)
     21   TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=236 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
     21    INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=215 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
     21     SORT AGGREGATE (cr=179 pr=0 pw=0 time=0 us)
     21      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=179 pr=0 pw=0 time=0 us cost=5 size=69 card=1)
    122       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=81 pr=0 pw=0 time=49 us cost=4 size=0 card=2)(object id 216361)
      3  SORT AGGREGATE (cr=34 pr=0 pw=0 time=0 us)
      8   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=34 pr=0 pw=0 time=72 us cost=5 size=69 card=1)
     26    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=12 pr=0 pw=0 time=17 us cost=4 size=0 card=2)(object id 216361)
      3  SORT AGGREGATE (cr=34 pr=0 pw=0 time=0 us)
      8   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=34 pr=0 pw=0 time=70 us cost=5 size=69 card=1)
     26    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=12 pr=0 pw=0 time=18 us cost=4 size=0 card=2)(object id 216361)
      2  NESTED LOOPS  (cr=66 pr=0 pw=0 time=0 us cost=2 size=109 card=1)
      2   TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=32 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
      2    INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=30 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
      2     HASH UNIQUE (cr=28 pr=0 pw=0 time=0 us cost=6 size=78 card=1)
      6      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=28 pr=0 pw=0 time=81 us cost=5 size=78 card=1)
     24       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=8 pr=0 pw=0 time=16 us cost=4 size=0 card=2)(object id 216361)
      2   TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=34 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
      2    INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=32 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
      2     SORT AGGREGATE (cr=28 pr=0 pw=0 time=0 us)
      6      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=28 pr=0 pw=0 time=48 us cost=5 size=69 card=1)
     24       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=8 pr=0 pw=0 time=14 us cost=4 size=0 card=2)(object id 216361)
      1  SORT AGGREGATE (cr=6 pr=0 pw=0 time=0 us)
      2   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=6 pr=0 pw=0 time=10 us cost=5 size=69 card=1)
      2    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=4 pr=0 pw=0 time=2 us cost=4 size=0 card=2)(object id 216361)
      1  SORT AGGREGATE (cr=5 pr=0 pw=0 time=0 us)
      1   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=5 pr=0 pw=0 time=0 us cost=5 size=69 card=1)
      1    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=4 pr=0 pw=0 time=0 us cost=4 size=0 card=2)(object id 216361)
      1  NESTED LOOPS  (cr=17 pr=0 pw=0 time=0 us cost=2 size=109 card=1)
      1   TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=8 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
      1    INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=7 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
      1     HASH UNIQUE (cr=6 pr=0 pw=0 time=0 us cost=6 size=78 card=1)
      2      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=6 pr=0 pw=0 time=23 us cost=5 size=78 card=1)
      2       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=4 pr=0 pw=0 time=4 us cost=4 size=0 card=2)(object id 216361)
      1   TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=9 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
      1    INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=8 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
      1     SORT AGGREGATE (cr=6 pr=0 pw=0 time=0 us)
      2      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=6 pr=0 pw=0 time=11 us cost=5 size=69 card=1)
      2       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=4 pr=0 pw=0 time=2 us cost=4 size=0 card=2)(object id 216361)
      0  NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=109 card=1)
      0   TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=0 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
      0    INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
      0     HASH UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=6 size=78 card=1)
      0      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=0 pr=0 pw=0 time=0 us cost=5 size=78 card=1)
      0       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=2)(object id 216361)
      0   TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=0 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
      0    INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
      0     SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
      0      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=0 pr=0 pw=0 time=0 us cost=5 size=69 card=1)
      0       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=2)(object id 216361)
     22  SORT AGGREGATE (cr=115 pr=0 pw=0 time=0 us)
     21   TABLE ACCESS BY INDEX ROWID YFS_RECEIPT_STATUS_AUDIT (cr=115 pr=0 pw=0 time=0 us cost=6 size=49 card=1)
     65    INDEX RANGE SCAN YFS_RECEIPT_STATUS_AUDIT_I1 (cr=59 pr=0 pw=0 time=30 us cost=3 size=0 card=3)(object id 96840)
     46  SORT AGGREGATE (cr=285 pr=0 pw=0 time=0 us)
     51   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=285 pr=0 pw=0 time=55 us cost=5 size=53 card=1)
    315    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=133 pr=0 pw=0 time=76 us cost=4 size=0 card=2)(object id 216361)
     24  SORT AGGREGATE (cr=205 pr=0 pw=0 time=0 us)
     29   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=205 pr=0 pw=0 time=55 us cost=5 size=53 card=1)
    148    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=85 pr=0 pw=0 time=67 us cost=4 size=0 card=2)(object id 216361)
     24  SORT AGGREGATE (cr=205 pr=0 pw=0 time=0 us)
     29   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=205 pr=0 pw=0 time=55 us cost=5 size=53 card=1)
    148    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=85 pr=0 pw=0 time=65 us cost=4 size=0 card=2)(object id 216361)
     21  NESTED LOOPS  (cr=459 pr=0 pw=0 time=0 us cost=2 size=109 card=1)
     21   TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=223 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
     21    INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=202 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
     21     HASH UNIQUE (cr=186 pr=0 pw=0 time=0 us cost=6 size=78 card=1)
     21      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=186 pr=0 pw=0 time=0 us cost=5 size=78 card=1)
    122       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=86 pr=0 pw=0 time=62 us cost=4 size=0 card=2)(object id 216361)
     21   TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=236 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
     21    INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=215 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
     21     SORT AGGREGATE (cr=179 pr=0 pw=0 time=0 us)
     21      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=179 pr=0 pw=0 time=0 us cost=5 size=69 card=1)
    122       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=81 pr=0 pw=0 time=46 us cost=4 size=0 card=2)(object id 216361)
      3  SORT AGGREGATE (cr=34 pr=0 pw=0 time=0 us)
      8   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=34 pr=0 pw=0 time=56 us cost=5 size=69 card=1)
     26    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=12 pr=0 pw=0 time=16 us cost=4 size=0 card=2)(object id 216361)
      3  SORT AGGREGATE (cr=34 pr=0 pw=0 time=0 us)
      8   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=34 pr=0 pw=0 time=55 us cost=5 size=69 card=1)
     26    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=12 pr=0 pw=0 time=16 us cost=4 size=0 card=2)(object id 216361)
      2  NESTED LOOPS  (cr=66 pr=0 pw=0 time=0 us cost=2 size=109 card=1)
      2   TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=32 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
      2    INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=30 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
      2     HASH UNIQUE (cr=28 pr=0 pw=0 time=0 us cost=6 size=78 card=1)
      6      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=28 pr=0 pw=0 time=62 us cost=5 size=78 card=1)
     24       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=8 pr=0 pw=0 time=13 us cost=4 size=0 card=2)(object id 216361)
      2   TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=34 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
      2    INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=32 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
      2     SORT AGGREGATE (cr=28 pr=0 pw=0 time=0 us)
      6      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=28 pr=0 pw=0 time=50 us cost=5 size=69 card=1)
     24       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=8 pr=0 pw=0 time=14 us cost=4 size=0 card=2)(object id 216361)
      1  SORT AGGREGATE (cr=6 pr=0 pw=0 time=0 us)
      2   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=6 pr=0 pw=0 time=9 us cost=5 size=69 card=1)
      2    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=4 pr=0 pw=0 time=2 us cost=4 size=0 card=2)(object id 216361)
      1  SORT AGGREGATE (cr=5 pr=0 pw=0 time=0 us)
      1   TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=5 pr=0 pw=0 time=0 us cost=5 size=69 card=1)
      1    INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=4 pr=0 pw=0 time=0 us cost=4 size=0 card=2)(object id 216361)
      1  NESTED LOOPS  (cr=17 pr=0 pw=0 time=0 us cost=2 size=109 card=1)
      1   TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=8 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
      1    INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=7 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
      1     HASH UNIQUE (cr=6 pr=0 pw=0 time=0 us cost=6 size=78 card=1)
      2      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=6 pr=0 pw=0 time=21 us cost=5 size=78 card=1)
      2       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=4 pr=0 pw=0 time=4 us cost=4 size=0 card=2)(object id 216361)
      1   TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=9 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
      1    INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=8 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
      1     SORT AGGREGATE (cr=6 pr=0 pw=0 time=0 us)
      2      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=6 pr=0 pw=0 time=10 us cost=5 size=69 card=1)
      2       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=4 pr=0 pw=0 time=2 us cost=4 size=0 card=2)(object id 216361)
      0  NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=2 size=109 card=1)
      0   TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=0 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
      0    INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
      0     HASH UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=6 size=78 card=1)
      0      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=0 pr=0 pw=0 time=0 us cost=5 size=78 card=1)
      0       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=2)(object id 216361)
      0   TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=0 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
      0    INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
      0     SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
      0      TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=0 pr=0 pw=0 time=0 us cost=5 size=69 card=1)
      0       INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I7 (cr=0 pr=0 pw=0 time=0 us cost=4 size=0 card=2)(object id 216361)
     22  SORT AGGREGATE (cr=115 pr=0 pw=0 time=0 us)
     21   TABLE ACCESS BY INDEX ROWID YFS_RECEIPT_STATUS_AUDIT (cr=115 pr=0 pw=0 time=0 us cost=6 size=49 card=1)
     65    INDEX RANGE SCAN YFS_RECEIPT_STATUS_AUDIT_I1 (cr=59 pr=0 pw=0 time=28 us cost=3 size=0 card=3)(object id 96840)
   5581  VIEW  (cr=2884042 pr=856256 pw=4268 time=1163635 us cost=71051 size=1151 card=1)
   5581   TEMP TABLE TRANSFORMATION  (cr=2864660 pr=855811 pw=4268 time=1161117 us)
      1    LOAD AS SELECT  (cr=24941 pr=1243 pw=1591 time=0 us)
  15539     NESTED LOOPS  (cr=24941 pr=1243 pw=0 time=29064 us)
  15539      NESTED LOOPS  (cr=9286 pr=1241 pw=0 time=26603 us cost=16356 size=12242250 card=16323)
  15539       SORT UNIQUE (cr=206 pr=104 pw=0 time=133 us cost=6 size=408075 card=16323)
  15539        TABLE ACCESS FULL FACT05_ORDER_HEADER (cr=206 pr=104 pw=0 time=114 us cost=6 size=408075 card=16323)
  15539       INDEX UNIQUE SCAN YFS_ORDER_HEADER_PK (cr=9080 pr=1137 pw=0 time=0 us cost=1 size=0 card=1)(object id 96308)
  15539      TABLE ACCESS BY INDEX ROWID YFS_ORDER_HEADER (cr=15655 pr=2 pw=0 time=0 us cost=2 size=725 card=1)
      1    LOAD AS SELECT  (cr=1766601 pr=814770 pw=2677 time=0 us)
  26052     HASH JOIN  (cr=1766601 pr=814770 pw=0 time=486464 us cost=35865 size=18900000 card=26250)
  15539      VIEW  (cr=1599 pr=1591 pw=0 time=560 us cost=75 size=408075 card=16323)
  15539       TABLE ACCESS FULL SYS_TEMP_0FD9D8774_39197D (cr=1599 pr=1591 pw=0 time=480 us cost=75 size=12242250 card=16323)
5809384      TABLE ACCESS FULL YFS_ORDER_LINE (cr=1765002 pr=813179 pw=0 time=1619436 us cost=35705 size=4027420055 card=5794849)
   5581    NESTED LOOPS OUTER (cr=1073118 pr=39798 pw=0 time=1161075 us cost=18830 size=1448 card=1)
   5581     NESTED LOOPS OUTER (cr=416296 pr=4088 pw=0 time=68094 us cost=18785 size=1286 card=1)
   5581      VIEW  (cr=415767 pr=4054 pw=0 time=55975 us cost=18782 size=1228 card=1)
   5581       FILTER  (cr=415767 pr=4054 pw=0 time=55602 us)
   5581        NESTED LOOPS OUTER (cr=415767 pr=4054 pw=0 time=10563 us cost=18782 size=1106 card=1)
   5581         NESTED LOOPS OUTER (cr=403922 pr=3995 pw=0 time=54869 us cost=18780 size=999 card=1)
   5581          NESTED LOOPS OUTER (cr=384328 pr=3993 pw=0 time=52437 us cost=18778 size=821 card=1)
    294           NESTED LOOPS OUTER (cr=373822 pr=3883 pw=0 time=32515 us cost=18774 size=661 card=1)
    294            HASH JOIN  (cr=355547 pr=2803 pw=0 time=97 us cost=18767 size=609 card=1)
 360409             HASH JOIN  (cr=355533 pr=2677 pw=0 time=3394 us cost=18691 size=392 card=1)
  26051              HASH JOIN  (cr=352845 pr=2677 pw=0 time=54579 us cost=18571 size=402 card=2)
  15539               VIEW  (cr=1599 pr=0 pw=0 time=472 us cost=75 size=816150 card=16323)
  15539                TABLE ACCESS FULL SYS_TEMP_0FD9D8774_39197D (cr=1599 pr=0 pw=0 time=430 us cost=75 size=12242250 card=16323)
  29334               HASH JOIN  (cr=351246 pr=2677 pw=0 time=117398 us cost=18495 size=3963750 card=26250)
  26052                VIEW  (cr=2688 pr=2677 pw=0 time=572 us cost=119 size=1863750 card=26250)
  26052                 TABLE ACCESS FULL SYS_TEMP_0FD9D8775_39197D (cr=2688 pr=2677 pw=0 time=474 us cost=119 size=18900000 card=26250)
2781262                TABLE ACCESS FULL YFS_ITEM (cr=348558 pr=0 pw=0 time=39637 us cost=14338 size=222482800 card=2781035)
  26052              VIEW  (cr=2688 pr=0 pw=0 time=768 us cost=119 size=5013750 card=26250)
  26052               TABLE ACCESS FULL SYS_TEMP_0FD9D8775_39197D (cr=2688 pr=0 pw=0 time=641 us cost=119 size=18900000 card=26250)
     19             VIEW  (cr=14 pr=126 pw=0 time=0 us cost=75 size=3542091 card=16323)
     19              TABLE ACCESS FULL SYS_TEMP_0FD9D8774_39197D (cr=14 pr=126 pw=0 time=0 us cost=75 size=12242250 card=16323)
     80            TABLE ACCESS BY INDEX ROWID YFS_ORDER_RELEASE_STATUS (cr=18275 pr=1080 pw=0 time=0 us cost=7 size=52 card=1)
   1046             INDEX RANGE SCAN YFS_ORDER_RELEASE_STATUS_I2 (cr=376 pr=43 pw=0 time=24269 us cost=3 size=0 card=4)(object id 96775)
   5499           TABLE ACCESS BY INDEX ROWID YFS_RECEIPT_LINE (cr=10506 pr=110 pw=0 time=6941 us cost=4 size=160 card=1)
   5499            INDEX RANGE SCAN EXTN_YFS_RECEIPT_LINE_I4 (cr=697 pr=17 pw=0 time=46281 us cost=2 size=0 card=1)(object id 153892)
   5499          VIEW PUSHED PREDICATE  (cr=19594 pr=2 pw=0 time=0 us cost=2 size=178 card=1)
   5499           NESTED LOOPS  (cr=19594 pr=2 pw=0 time=0 us cost=5 size=266 card=1)
   5499            NESTED LOOPS  (cr=13717 pr=2 pw=0 time=0 us cost=4 size=207 card=1)
   5499             TABLE ACCESS BY INDEX ROWID YFS_RECEIPT_HEADER (cr=7845 pr=2 pw=0 time=0 us cost=3 size=157 card=1)
   5499              INDEX UNIQUE SCAN YFS_RECEIPT_HEADER_PK (cr=2345 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 95941)
   5499             TABLE ACCESS BY INDEX ROWID YFS_PIPELINE (cr=5872 pr=0 pw=0 time=0 us cost=1 size=50 card=1)
   5499              INDEX UNIQUE SCAN YFS_PIPELINE_PK (cr=373 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 95708)
   5499            TABLE ACCESS BY INDEX ROWID YFS_STATUS (cr=5877 pr=0 pw=0 time=0 us cost=1 size=59 card=1)
   5499             INDEX UNIQUE SCAN YFS_STATUS_I1 (cr=378 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 216323)
   5499         TABLE ACCESS BY INDEX ROWID YFS_SHIPMENT (cr=11845 pr=59 pw=0 time=0 us cost=2 size=107 card=1)
   5499          INDEX UNIQUE SCAN YFS_SHIPMENT_PK (cr=6346 pr=59 pw=0 time=0 us cost=1 size=0 card=1)(object id 95757)
      0      TABLE ACCESS BY INDEX ROWID YFS_SHIPMENT_LINE (cr=529 pr=34 pw=0 time=0 us cost=3 size=58 card=1)
      0       INDEX RANGE SCAN YFS_SHIPMENT_LINE_I4 (cr=529 pr=34 pw=0 time=0 us cost=2 size=0 card=1)(object id 96795)
      0     TABLE ACCESS BY INDEX ROWID YFS_TASK (cr=656822 pr=35710 pw=0 time=0 us cost=45 size=162 card=1)
  22875      INDEX RANGE SCAN YFS_TASK_I22 (cr=31427 pr=673 pw=0 time=17394 us cost=2 size=0 card=56)(object id 97014)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  control file sequential read                    8        0.04          0.07
  db file sequential read                     38580        0.17        167.58
  db file scattered read                         49        0.00          0.00
  direct path write temp                        134        0.05          0.50
  latch: row cache objects                     1043        0.00          0.02
  reliable message                                2        0.00          0.00
  enq: KO - fast object checkpoint                1        0.00          0.00
  direct path read                            12744        0.74        234.42
  latch: enqueue hash chains                     17        0.00          0.00
  latch: undo global data                         2        0.00          0.00
  latch: cache buffers chains                    15        0.00          0.00
  SQL*Net message to client                     373        0.00          0.00
  SQL*Net message from client                   373       87.86      13867.65
  read by other session                          52        0.02          0.30
********************************************************************************

SQL ID: 7fy9bn6vck9dd
Plan Hash: 892072905
SELECT COUNT (*) 
FROM
 V$TIMEZONE_NAMES WHERE TZNAME = :B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute   6366      0.94       1.02          0          0          0           0
Fetch     6366     17.03      17.30          0          0          0        6366
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    12734     17.97      18.33          0          0          0        6366

Misses in library cache during parse: 1
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 138     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
      1   FIXED TABLE FULL X$TIMEZONE_NAMES (cr=0 pr=0 pw=0 time=0 us cost=0 size=34 card=1)




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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.02          0          0          0           0
Execute      1     38.37     270.33     816013    1791542       4338           0
Fetch      373     20.48     182.36      40243    1092500          0        5581
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      375     58.88     452.72     856256    2884042       4338        5581

Misses in library cache during parse: 1
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message from client                   374      158.89      14026.55
  SQL*Net more data from client                   3        0.00          0.00
  db file sequential read                     38580        0.17        167.58
  control file sequential read                    8        0.04          0.07
  db file scattered read                         49        0.00          0.00
  direct path write temp                        134        0.05          0.50
  latch: row cache objects                     1043        0.00          0.02
  reliable message                                2        0.00          0.00
  enq: KO - fast object checkpoint                1        0.00          0.00
  direct path read                            12744        0.74        234.42
  latch: enqueue hash chains                     17        0.00          0.00
  latch: undo global data                         2        0.00          0.00
  latch: cache buffers chains                    15        0.00          0.00
  SQL*Net message to client                     373        0.00          0.00
  read by other session                          52        0.02          0.30


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      145      0.05       0.04          0          0          0           0
Execute   7560      1.20       1.31          0          0          0           0
Fetch     8343     17.11      17.97         53       3620          0        8523
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    16048     18.36      19.33         53       3620          0        8523

Misses in library cache during parse: 26
Misses in library cache during execute: 21

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        53        0.02          0.35

 6367  user  SQL statements in session.
 1194  internal SQL statements in session.
 7561  SQL statements in session.
    0  statements EXPLAINed in this session.
********************************************************************************
Trace file: /usr/oracle_su/diag/rdbms/sudr/SUDR/trace/SUDR_ora_26362.trc
Trace file compatibility: 11.1.0.7
Sort options: execpu  exeela  fchcpu  
       1  session in tracefile.
    6367  user  SQL statements in trace file.
    1194  internal SQL statements in trace file.
    7561  SQL statements in trace file.
      26  unique SQL statements in trace file.
  161071  lines in trace file.
   14005  elapsed seconds in trace file.