Re: hash join waits on cpu 100% time

From: GG <grzegorzof_at_interia.pl>
Date: Sun, 04 Jan 2015 14:16:55 +0100
Message-ID: <54A93D47.7050901_at_interia.pl>

W dniu 2015-01-04 o 13:50, Jonathan Lewis pisze:
>
> A couple of quick questions:
>
> You said the query should run in 6 minutes - is this the design target, or a previous best ?
> There is a "hint" to set the optimizer features back to 10.2.0.4 - is this really supposed to be a hint, or is it intended as a comment to point out that the session or system parameter has been set ? As it stands it's not the correct syntax for the hint.
>
>
>

The 6 minutes is the usual run time (got this from developers) I'm not able to confirm this from awr but it was not hours for sure . Otherwise our DW load would take ages .
We may ignore the hint .
I've got the view definition and it is pure evil with first_rows(1) :) The part with cl.cunit_id = 2 is run :
and I'm suspecting :
NVL(

     (SELECT
       /*+first_rows(1)*/
       cl.per_type_doc
     FROM ccpa_bc cl
     WHERE cl.time   = lt.time
     AND cl.cunit_id = 2
     AND cl.cus_no   = TO_CHAR(lt.cust_id)
     )

is the problem here . No proof thoug :) .

VIEW_NAME TEXT

V_SA_CUS_PERSONAL_ADD
WITH ccpa_bc AS

(SELECT

     /*+dynamic_sampling(4)*/
     cl.time,
     cl.cunit_id,
     cl.cus_no,
     cl.per_type_doc

   FROM stage_4b.sa_cus_crm_prim_add cl
   WHERE cl.cunit_id != 1
   ),
   scpa AS
   (SELECT pl.time,

     TO_NUMBER(1)        AS cunit_id,
     TO_CHAR(pl.cust_id) AS cus_no,
     pl.title,
     pl.gender,
     pl.birth_date,
     pl.birth_place,
     pl.type_doc,
     pl.id_number,
     pl.nr_id,
     pl.nip,
     pl.fathers_name,
     pl.maiden_name,
     pl.mother_maiden,
     pl.marital_stat,
     pl.spec_sign,
     pl.inst_death,
     pl.add_info,
     pl.b_dep_no,
     pl.numochildren,
     pl.record_status,
     pl.curr_no,
     pl.inputter,
     pl.input_date_time,
     pl.authoriser,
     pl.co_code,
     pl.dept_code,
     pl.auditor_code,
     pl.audit_date_time,
     pl.data_country,
     pl.data_source,
     pl.last_rev,
     pl.exported,
     pl.cont_pref,
     pl.cont_type,
     pl.work_place,
     pl.income,
     pl.act_size,
     pl.perm_no,
     pl.perm_valid,
     pl.profession

   FROM stagep.sa_cus_personal_add pl
   UNION ALL
   SELECT lt.time,

     TO_NUMBER(2)        AS cunit_id,
     TO_CHAR(lt.cust_id) AS cus_no,
     lt.title,
     lt.gender,
     lt.birth_date,
     lt.birth_place,
     NVL(
     (SELECT
       /*+first_rows(1)*/
       cl.per_type_doc
     FROM ccpa_bc cl
     WHERE cl.time   = lt.time
     AND cl.cunit_id = 2
     AND cl.cus_no   = TO_CHAR(lt.cust_id)
     ), lt.type_doc) AS type_doc,
     lt.id_number,
     lt.nr_id,
     lt.nip,
     lt.fathers_name,
     lt.maiden_name,
     lt.mother_maiden,
     lt.marital_stat,
     lt.spec_sign,
     lt.inst_death,
     lt.add_info,
     lt.b_dep_no,
     lt.numochildren,
     lt.record_status,
     lt.curr_no,
     lt.inputter,
     lt.input_date_time,
     lt.authoriser,
     lt.co_code,
     lt.dept_code,
     lt.auditor_code,
     lt.audit_date_time,
     lt.data_country,
     lt.data_source,
     lt.last_rev,
     lt.exported,
     lt.cont_pref,
     lt.cont_type,
     lt.work_place,
     lt.income,
     lt.act_size,
     lt.perm_no,
     lt.perm_valid,
     lt.profession

   FROM staget.sa_cus_personal_add lt
   UNION ALL
   SELECT lv.time,

     TO_NUMBER(3)        AS cunit_id,
     TO_CHAR(lv.cust_id) AS cus_no,
     lv.title,
     lv.gender,
     lv.birth_date,
     lv.birth_place,
     NVL(
     (SELECT
       /*+first_rows(1)*/
       cl.per_type_doc
     FROM ccpa_bc cl
     WHERE cl.time   = lv.time
     AND cl.cunit_id = 3
     AND cl.cus_no   = TO_CHAR(lv.cust_id)
     ), lv.type_doc) AS type_doc,
     lv.id_number,
     lv.nr_id,
     lv.nip,
     lv.fathers_name,
     lv.maiden_name,
     lv.mother_maiden,
     lv.marital_stat,
     lv.spec_sign,
     lv.inst_death,
     lv.add_info,
     lv.b_dep_no,
     lv.numochildren,
     lv.record_status,
     lv.curr_no,
     lv.inputter,
     lv.input_date_time,
     lv.authoriser,
     lv.co_code,
     lv.dept_code,
     lv.auditor_code,
     lv.audit_date_time,
     lv.data_country,
     lv.data_source,
     lv.last_rev,
     lv.exported,
     lv.cont_pref,
     lv.cont_type,
     lv.work_place,
     lv.income,
     lv.act_size,
     lv.perm_no,
     lv.perm_valid,
     lv.profession

   FROM stagev.sa_cus_personal_add lv
   UNION ALL
   SELECT ee.time,

     TO_NUMBER(4)        AS cunit_id,
     TO_CHAR(ee.cust_id) AS cus_no,
     ee.title,
     ee.gender,
     ee.birth_date,
     ee.birth_place,
     NVL(
     (SELECT
       /*+first_rows(1)*/
       cl.per_type_doc
     FROM ccpa_bc cl
     WHERE cl.time   = ee.time
     AND cl.cunit_id = 4
     AND cl.cus_no   = TO_CHAR(ee.cust_id)
     ), ee.type_doc) AS type_doc,
     ee.id_number,
     ee.nr_id,
     ee.nip,
     ee.fathers_name,
     ee.maiden_name,
     ee.mother_maiden,
     ee.marital_stat,
     ee.spec_sign,
     ee.inst_death,
     ee.add_info,
     ee.b_dep_no,
     ee.numochildren,
     ee.record_status,
     ee.curr_no,
     ee.inputter,
     ee.input_date_time,
     ee.authoriser,
     ee.co_code,
     ee.dept_code,
     ee.auditor_code,
     ee.audit_date_time,
     ee.data_country,
     ee.data_source,
     ee.last_rev,
     ee.exported,
     ee.cont_pref,
     ee.cont_type,
     ee.work_place,
     ee.income,
     ee.act_size,
     ee.perm_no,
     ee.perm_valid,
     ee.profession

FROM stagee.sa_cus_personal_add ee
)
SELECT s."TIME",

   s."CUNIT_ID",
   s."CUS_NO",
   s."TITLE",
   s."GENDER",
   s."BIRTH_DATE",
   s."BIRTH_PLACE",
   s."TYPE_DOC",
   s. "ID_NUMBER",
   s."NR_ID",
   s."NIP",
   s."FATHERS_NAME",
   s."MAIDEN_NAME",
   s."MOTHER_MAIDEN",
   s."MARITAL_STAT",
   s. "SPEC_SIGN",
   s."INST_DEATH",
   s."ADD_INFO",
   s."B_DEP_NO",
   s."NUMOCHILDREN",
   s."RECORD_STATUS",
   s."CURR_NO",
   s."INPUTTER",
   s."INPUT_DATE_TIME",
   s."AUTHORISER",
   s."CO_CODE",
   s."DEPT_CODE",
   s."AUDITOR_CODE",
   s."AUDIT_                               DATE_TIME",
   s."DATA_COUNTRY",
   s."DATA_SOURCE",
   s."LAST_REV",
   s."EXPORTED",
   s."CONT_PREF",
   s."CONT_TYPE",
   s. "WORK_PLACE",
   s."INCOME",
   s."ACT_SIZE",
   s."PERM_NO",
   s."PERM_VALID",
   s."PROFESSION",

ORA_HASH( s.cus_no

   ||s.title
   ||s.gender
   ||TO_CHAR(s.birth_date,'yyyymmdd')
   ||s.birth_place
   ||s.type_doc
   ||s.id_numbe r
   ||s.nr_id
   ||s.nip
   ||s.fathers_name
   ||s.maiden_name
   || s.mother_maiden
   ||s.marital_stat
   ||s.spec_sign
   ||s.inst_death
   ||s.add_info
   ||s.b_dep_no
   ||TO_CHAR(s.numoch ildren)
   ||s.record_status
   ||TO_CHAR(s.curr_no)
   || s.inputter
   ||TO_CHAR(s.input_date_time)
   ||s.authoriser
   ||s.co_code
   ||TO_CHAR(s.dept_code)
   ||s.auditor_cod e
   ||TO_CHAR(s.audit_date_time)
   ||s.data_country
   || s.data_source
   ||TO_CHAR(s.last_rev,'yyyymmdd')
   ||s.cont_pref
   ||TO_CHAR(s.cont_type)
   ||s.work_place
   ||s.in come
   ||s.act_size
   ||s.perm_no
   ||TO_CHAR(s.perm_valid,'yyyymmdd')
   || s.profession,4294967295,20 ) AS rec_checksum

FROM scpa s

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jan 04 2015 - 14:16:55 CET

This message: [ Message body ]
Next message: Niall Litchfield: "Re: Data Masking - auto generate scripts"
Previous message: Jonathan Lewis: "RE: hash join waits on cpu 100% time"
In reply to: Jonathan Lewis: "RE: hash join waits on cpu 100% time"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message