Re: hash join waits on cpu 100% time
- From: GG <grzegorzof@xxxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 04 Jan 2015 14:16:55 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
