Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2006
    Posts
    3

    Unanswered: Oracle 9I, HASH_JOIN_ENABLED

    Can someone tell me, if there is a way to determine if a HASH_JOIN_ENABLED parameter should be set to TRUE or FALSE in a certain environment

    In our environment, we currently switched from TRUE to FALSE, based on the prior-experience of our DBA. He stated, that in his previous experience, switching this to FALSE, has improved over-all database performance. However, when we swiched, we have noticed (at this point), at least a few processes, that are now taking 1.5 hours instead of 4 minutes. Also, based on Oracle documentation, they recommend keping setting to TRUE, and let the optimizer choose the most cost-efficient path. However, in some PeopleSoft documents, they argue that switching to FALSE, also produces better performance.
    We are seeing the performance "hit" while doing an INSERT statement. SELECT part of the insert, comes back in about 4-5 seconds, however, when running INSERT using that particular SELECT it takes 1.5 hours. How does HASH_JOIN_ENABLED, affect INSERT statement?


    thank you.
    Last edited by kishinevetz; 12-27-06 at 13:09.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If one of the answers was ALWAYS the best, then there would be NO need for the toggle. Think about it.
    The better answer is application AND data dependent.
    While FALSE would provide faster results in one application & data collection;
    TRUE provides faster results for a different application or different data collection.
    The only accurate answer for ANY Oracle tuning question is "It depends".

    >if there is a way to determine if a HASH_JOIN_ENABLED parameter should be set to TRUE or FALSE in a certain environment
    Benchmark your application & data with it set to TRUE & then change it to FALSE & run the same benchmark.
    Use the value that gave the best results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You should leave it to TRUE in most circunstances, Oracle will just take into account hash joins when evaluating the plans for a given query.

    So, your SELECT runs in 4-5 seconds, and your INSERT in 1.5 hours, and you believe is hash_join_enabled the culript..

    I would run a TKPROF on this and see what the real waits are.

  4. #4
    Join Date
    Dec 2006
    Posts
    3
    Yes, I believe the culprit is HASH_JOIN_ENABLED. I was able to replicate this issue in our TEST environment. While having HASH_JOIN_ENABLED =TRUE, I ran INSERT statement, itcompleted in about 1 minute or so, however, when I set HASH_JOIN_ENABLED =FALSE, it came back in about 1.5 hours.
    What does not make any sense to me, is why the "select" part of the insert comes back in 5-10 seconds, but the entire INSERT statement takes over 1 hour. I thought that HASH_JOIN_ENABLED, would only relate to the select portion.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Please, post an small example on the forums so we can all see what you're really doing. It is not clear to me wether you're using something like select * from table and/or insert into table select * from table. An example would be great and please.. also use TKPROF to see where the real job is being done.

  6. #6
    Join Date
    Dec 2006
    Posts
    3

    Hash_join_enabled

    Sorry, for not doing this earlier:
    Basically my INSERT looks like this:

    INSERT INTO PS_EMPLOYEES (EMPLID
    , EMPL_RCD
    , BIRTHDATE
    , BIRTHPLACE
    , DT_OF_DEATH
    , COUNTRY_NM_FORMAT
    , NAME
    , NAME_INITIALS
    , NAME_PREFIX
    , NAME_SUFFIX
    , NAME_ROYAL_PREFIX
    , NAME_ROYAL_SUFFIX
    , NAME_TITLE
    , LAST_NAME_SRCH
    , FIRST_NAME_SRCH
    , LAST_NAME
    , FIRST_NAME
    , MIDDLE_NAME
    , SECOND_LAST_SRCH
    , SECOND_LAST_NAME
    , NAME_AC
    , PREF_FIRST_NAME
    , LAST_NAME_PREF_NLD
    , COUNTRY
    , ADDRESS1
    , ADDRESS2
    , ADDRESS3
    , ADDRESS4
    , CITY
    , NUM1
    , NUM2
    , HOUSE_TYPE
    , ADDR_FIELD1
    , ADDR_FIELD2
    , ADDR_FIELD3
    , COUNTY
    , STATE
    , POSTAL
    , GEO_CODE
    , IN_CITY_LIMIT
    , HOME_PHONE
    , NATIONAL_ID_TYPE
    , NATIONAL_ID
    , SEX
    , MAR_STATUS
    , HIGHEST_EDUC_LVL
    , FT_STUDENT
    , MILITARY_STATUS
    , US_WORK_ELIGIBILTY
    , MILIT_SITUATN_FRA
    , DISABLED
    , DISABLED_VET
    , ETHNIC_GROUP
    , CITIZENSHIP_STATUS
    , ORIG_HIRE_DT
    , PER_ORG
    , BENEFIT_RCD_NBR
    , CMPNY_SENIORITY_DT
    , SERVICE_DT
    , HOME_HOST_CLASS
    , LAST_INCREASE_DT
    , OWN_5PERCENT_CO
    , BUSINESS_TITLE
    , PROBATION_DT
    , SECURITY_CLEARANCE
    , EFFDT
    , EFFSEQ
    , HIRE_DT
    , EXPECTED_RETURN_DT
    , TERMINATION_DT
    , LAST_DATE_WORKED
    , REPORTS_TO
    , SUPERVISOR_ID
    , BUSINESS_UNIT
    , DEPTID
    , JOBCODE
    , POSITION_NBR
    , ACTION
    , ACTION_DT
    , ACTION_REASON
    , LOCATION
    , JOB_ENTRY_DT
    , DEPT_ENTRY_DT
    , POSITION_ENTRY_DT
    , SHIFT
    , REG_TEMP
    , FULL_PART_TIME
    , FLSA_STATUS
    , OFFICER_CD
    , COMPANY
    , PAYGROUP
    , EMPL_TYPE
    , HOLIDAY_SCHEDULE
    , STD_HOURS
    , STD_HRS_FREQUENCY
    , REG_REGION
    , PAID_HOURS
    , PAID_FTE
    , PAID_HRS_FREQUENCY
    , FTE
    , EEO_CLASS
    , SAL_ADMIN_PLAN
    , GRADE
    , GRADE_ENTRY_DT
    , STEP
    , STEP_ENTRY_DT
    , GL_PAY_TYPE
    , COMP_FREQUENCY
    , COMPRATE
    , CHANGE_AMT
    , CHANGE_PCT
    , ANNUAL_RT
    , MONTHLY_RT
    , DAILY_RT
    , HOURLY_RT
    , ANNL_BENEF_BASE_RT
    , SHIFT_RT
    , SHIFT_FACTOR
    , CURRENCY_CD
    , DIRECTLY_TIPPED
    , PAY_SYSTEM_FLG
    , SETID_DEPT
    , SETID_JOBCODE
    , SETID_LOCATION
    , SETID_SALARY
    , GP_PAYGROUP
    , GP_ELIG_GRP
    , CUR_RT_TYPE
    , GP_ASOF_DT_EXG_RT
    , JOB_INDICATOR
    , PAY_UNION_FEE
    , UNION_CD
    , BARG_UNIT
    , UNION_SENIORITY_DT
    , ENTRY_DATE
    , LABOR_AGREEMENT
    , EMPL_CTG
    , EMPL_CTG_L1
    , EMPL_CTG_L2
    , SETID_LBR_AGRMNT
    , WPP_STOP_FLAG
    , LABOR_FACILITY_ID
    , LBR_FAC_ENTRY_DT
    , LAYOFF_EXEMPT_FLAG
    , LAYOFF_EXEMPT_RSN
    , VALUE_1_FRA
    , VALUE_2_FRA
    , VALUE_3_FRA
    , VALUE_4_FRA
    , VALUE_5_FRA
    , GVT_SCD_RETIRE
    , GVT_MAND_RET_DT
    , GVT_SCD_TSP
    , GVT_SCD_SEVPAY
    , GVT_DT_LEI
    , GVT_PAY_BASIS
    , GVT_WGI_STATUS
    , GVT_WGI_DUE_DATE
    , GVT_INTRM_DAYS_WGI
    , GVT_LOCALITY_ADJ
    , GVT_WORK_SCHED
    , GVT_SEVPAY_PRV_WKS
    , GVT_BIWEEKLY_RT
    , GVT_STEP
    , GVT_RTND_PAY_PLAN
    , GVT_RTND_SAL_PLAN
    , GVT_RTND_GRADE
    , GVT_RTND_STEP
    , GVT_RTND_GVT_STEP
    , GVT_RTND_GRADE_BEG
    , GVT_RTND_GRADE_EXP
    , GVT_TEMP_PRO_EXPIR
    , GVT_TEMP_PSN_EXPIR
    , GVT_DETAIL_EXPIRES
    , GVT_SABBATIC_EXPIR
    , GVT_TYPE_OF_APPT
    , GVT_APPT_EXPIR_DT
    , GVT_CAREER_CNV_DUE
    , GVT_SUPV_PROB_DT
    , GVT_SES_PROB_DT
    , GVT_SEC_CLR_STATUS
    , GVT_CLRNCE_STAT_DT
    , EEO1CODE
    , EEO4CODE
    , EEO5CODE
    , EEO6CODE
    , EEO_JOB_GROUP
    , JOB_FAMILY
    , JOB_KNOWHOW_POINTS
    , JOB_ACCNTAB_POINTS
    , JOB_PROBSLV_POINTS
    , JOB_POINTS_TOTAL
    , JOB_KNOWHOW_PCT
    , JOB_ACCNTAB_PCT
    , JOB_PROBSLV_PCT
    , IPEDSSCODE
    , GVT_ORG_TTL_DESCR
    , MANAGER_ID
    , EEO4_FUNCTION
    , ASOFDATE
    , FROMDATE
    , JOBTITLE
    , JOBTITLE_ABBRV
    , DEPTNAME
    , DEPTNAME_ABBRV
    , REHIRE_DT
    , WORK_PHONE
    , NID_COUNTRY
    , GVT_OVERTIME_RT
    , GVT_RTND_PAY_BASIS
    , HR_STATUS
    , EMPL_STATUS
    , SALARY_MATRIX_CD
    , RATING_SCALE
    , REVIEW_RATING
    , REVIEW_DT)
    SELECT A.EMPLID
    , B.EMPL_RCD
    , A.BIRTHDATE
    , A.BIRTHPLACE
    , A.DT_OF_DEATH
    , NM.COUNTRY_NM_FORMAT
    , NM.NAME
    , NM.NAME_INITIALS
    , NM.NAME_PREFIX
    , NM.NAME_SUFFIX
    , NM.NAME_ROYAL_PREFIX
    , NM.NAME_ROYAL_SUFFIX
    , NM.NAME_TITLE
    , NM.LAST_NAME_SRCH
    , NM.FIRST_NAME_SRCH
    , NM.LAST_NAME
    , NM.FIRST_NAME
    , NM.MIDDLE_NAME
    , NM.SECOND_LAST_SRCH
    , NM.SECOND_LAST_NAME
    , NM.NAME_AC
    , NM.PREF_FIRST_NAME
    , NM.LAST_NAME_PREF_NLD
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , A2.SEX
    , A2.MAR_STATUS
    , A2.HIGHEST_EDUC_LVL
    , A2.FT_STUDENT
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , ' '
    , PI.ORIG_HIRE_DT
    , B.PER_ORG
    , B.BENEFIT_RCD_NBR
    , B.CMPNY_SENIORITY_DT
    , B.SERVICE_DT
    , B.HOME_HOST_CLASS
    , B.LAST_INCREASE_DT
    , B.OWN_5PERCENT_CO
    , B.BUSINESS_TITLE
    , B.PROBATION_DT
    , B.SECURITY_CLEARANCE
    , C.EFFDT
    , C.EFFSEQ
    , C.HIRE_DT
    , C.EXPECTED_RETURN_DT
    , C.TERMINATION_DT
    , C.LAST_DATE_WORKED
    , C.REPORTS_TO
    , C.SUPERVISOR_ID
    , C.BUSINESS_UNIT
    , C.DEPTID
    , C.JOBCODE
    , C.POSITION_NBR
    , C.ACTION
    , C.ACTION_DT
    , C.ACTION_REASON
    , C.LOCATION
    , C.JOB_ENTRY_DT
    , C.DEPT_ENTRY_DT
    , C.POSITION_ENTRY_DT
    , C.SHIFT
    , C.REG_TEMP
    , C.FULL_PART_TIME
    , C.FLSA_STATUS
    , C.OFFICER_CD
    , C.COMPANY
    , C.PAYGROUP
    , C.EMPL_TYPE
    , C.HOLIDAY_SCHEDULE
    , C.STD_HOURS
    , C.STD_HRS_FREQUENCY
    , C.REG_REGION
    , C.PAID_HOURS
    , C.PAID_FTE
    , C.PAID_HRS_FREQUENCY
    , C.FTE
    , C.EEO_CLASS
    , C.SAL_ADMIN_PLAN
    , C.GRADE
    , C.GRADE_ENTRY_DT
    , C.STEP
    , C.STEP_ENTRY_DT
    , C.GL_PAY_TYPE
    , C.COMP_FREQUENCY
    , C.COMPRATE
    , C.CHANGE_AMT
    , C.CHANGE_PCT
    , C.ANNUAL_RT
    , C.MONTHLY_RT
    , C.DAILY_RT
    , C.HOURLY_RT
    , C.ANNL_BENEF_BASE_RT
    , C.SHIFT_RT
    , C.SHIFT_FACTOR
    , C.CURRENCY_CD
    , C.DIRECTLY_TIPPED
    , C.PAY_SYSTEM_FLG
    , C.SETID_DEPT
    , C.SETID_JOBCODE
    , C.SETID_LOCATION
    , C.SETID_SALARY
    , C.GP_PAYGROUP
    , C.GP_ELIG_GRP
    , C.CUR_RT_TYPE
    , C.GP_ASOF_DT_EXG_RT
    , C.JOB_INDICATOR
    , C.PAY_UNION_FEE
    , C.UNION_CD
    , C.BARG_UNIT
    , C.UNION_SENIORITY_DT
    , C.ENTRY_DATE
    , C.LABOR_AGREEMENT
    , C.EMPL_CTG
    , C.EMPL_CTG_L1
    , C.EMPL_CTG_L2
    , C.SETID_LBR_AGRMNT
    , C.WPP_STOP_FLAG
    , C.LABOR_FACILITY_ID
    , C.LBR_FAC_ENTRY_DT
    , C.LAYOFF_EXEMPT_FLAG
    , C.LAYOFF_EXEMPT_RSN
    , C.VALUE_1_FRA
    , C.VALUE_2_FRA
    , C.VALUE_3_FRA
    , C.VALUE_4_FRA
    , C.VALUE_5_FRA
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ' '
    , ' '
    , NULL
    , 0
    , 0
    , ' '
    , 0
    , 0
    , ' '
    , ' '
    , ' '
    , ' '
    , 0
    , ' '
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ' '
    , NULL
    , NULL
    , NULL
    , NULL
    , ' '
    , NULL
    , D.EEO1CODE
    , D.EEO4CODE
    , D.EEO5CODE
    , D.EEO6CODE
    , D.EEO_JOB_GROUP
    , D.JOB_FAMILY
    , D.JOB_KNOWHOW_POINTS
    , D.JOB_ACCNTAB_POINTS
    , D.JOB_PROBSLV_POINTS
    , D.JOB_POINTS_TOTAL
    , D.JOB_KNOWHOW_PCT
    , D.JOB_ACCNTAB_PCT
    , D.JOB_PROBSLV_PCT
    , D.IPEDSSCODE
    , D.GVT_ORG_TTL_DESCR
    , E.MANAGER_ID
    , E.EEO4_FUNCTION
    , %Bind(asofdate)
    , %Bind(asofdate)
    , D.DESCR
    , D.DESCRSHORT
    , E.DESCR
    , E.DESCRSHORT
    , C.LAST_HIRE_DT
    , B.POSITION_PHONE
    , ' '
    , 0
    , ' '
    , C.HR_STATUS
    , C.EMPL_STATUS
    , ' '
    , ' '
    , ' '
    , NULL
    FROM PS_PERSON A , PS_PERS_DATA_EFFDT A2 , PS_PERSON_NAME NM
    , PS_PER_ORG_ASGN B , PS_PER_ORG_INST PI , PS_JOB C
    , PS_JOBCODE_TBL D , PS_DEPT_TBL E
    WHERE B.PER_ORG IN ('EMP','CWR')
    AND NM.EMPLID = A.EMPLID
    AND A2.EMPLID = A.EMPLID
    AND A2.EFFDT = (
    SELECT MAX(A3.EFFDT)
    FROM PS_PERS_DATA_EFFDT A3
    WHERE A3.EMPLID = A2.EMPLID
    AND A3.EFFDT <= SYSDATE)
    AND B.EMPLID = A.EMPLID
    AND PI.EMPLID = B.EMPLID
    AND PI.ORG_INSTANCE_ERN = B.ORG_INSTANCE_ERN
    AND C.EMPLID = B.EMPLID
    AND C.EMPL_RCD = B.EMPL_RCD
    AND C.EFFDT = (
    SELECT MAX(H.EFFDT)
    FROM PS_JOB H
    WHERE H.EMPLID = C.EMPLID
    AND H.EMPL_RCD = C.EMPL_RCD
    AND H.EFFDT <= SYSDATE)
    AND C.EFFSEQ = (
    SELECT MAX(I.EFFSEQ)
    FROM PS_JOB I
    WHERE I.EMPLID = C.EMPLID
    AND I.EMPL_RCD = C.EMPL_RCD
    AND I.EFFDT = C.EFFDT)
    AND D.JOBCODE = C.JOBCODE
    AND D.SETID = C.SETID_JOBCODE
    AND D.EFFDT = (
    SELECT MAX(J.EFFDT)
    FROM PS_JOBCODE_TBL J
    WHERE J.JOBCODE = D.JOBCODE
    AND J.SETID = D.SETID
    AND J.EFFDT <= C.EFFDT)
    AND E.DEPTID = C.DEPTID
    AND E.SETID = C.SETID_DEPT
    AND E.EFFDT = (
    SELECT MAX(K.EFFDT)
    FROM PS_DEPT_TBL K
    WHERE K.DEPTID = E.DEPTID
    AND K.SETID = E.SETID
    AND K.EFFDT <= C.EFFDT)

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I'm a little surprised that it would ever be a good idea to disable a core feature like hash joins (I've never seen anyone do this). I'm frankly amazed that based on one system appearing to work better with hash joins disabled, anyone would assume it's some sort of "fast=true" setting.

    Leave it at the default unless you have identified a specific problem with hash joins being overused by the optimizer and can't solve it any other way.

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    What surprises me more is that, according to the poster, the select alone (with the hash_join_enabled feature on) runs in 4-5 seconds, whereas the insert + select takes an hour (with hash_join_enabled on as well). This is *strange*.

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Maybe the actual work of loading the rows into the target table is slow due to indexes, triggers, constraints etc. Or maybe for some reason the CBO is choosing a different plan for the INSERT version - I've seen this happen. In 9i you can check the actual plan with Tom Kyte's dynamic_plan_table approach.

  10. #10
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    So, back to where I want him to be: It is probably that hash_join_enabled is NOT the culprits.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •