Oracle-L: Incorrect SQL execution plan in Star Schema TEMP table creation.

From: <Prasada.Gunda_at_hartfordlife.com>
Date: Wed, 09 Apr 2003 13:43:43 -0800
Message-ID: <F001.0057E8AC.20030409134343@fatcity.com>

Hi All,

We are having a problem with one of the SQL query, which is giving incorrect execution plan.
This is a Data warehouse environment and the star_transformation_enabled set to true. The following query uses star transformation and it takes long time to execute. In fact it used up all temp space and failed eventually. As part of investigation, we came to know that one of the temp table generation SQL is doing Cartesian product and it is using enormous amount of temp space.

The main sql :
SELECT
MNTHLY_DURING_DATE.MONTH,

  CLAIM_PROCESSING_EXAMINER.PROCESSORS_OFFICE,
  CLAIM_PROCESSING_EXAMINER.TEAM,
  CLAIM_PROCESSING_EXAMINER.PROCESSOR_ID,
  CLAIM_PROCESSING_EXAMINER.PROCESSORS_FULL_NAME,

  SUM(MONTHLY_SNAPSHOT_DURING.NO_OF_DECISION_TRX) FROM
  EXAMINER_DIM CLAIM_PROCESSING_EXAMINER,   DAY_DIM MNTHLY_DURING_DATE,
  V_CLAIM_MONTHLY_SNAPSHOT_FACT MONTHLY_SNAPSHOT_DURING,   COVERAGE_PLAN_DIM
WHERE
  (
COVERAGE_PLAN_DIM.COVERAGE_PLAN_SKEY=MONTHLY_SNAPSHOT_DURING.COVERAGE_PLAN_SKEY )
  AND (
MONTHLY_SNAPSHOT_DURING.PROCESSING_EXMNR_SKEY=CLAIM_PROCESSING_EXAMINER.EXAMINER_SKEY )
  AND (
MNTHLY_DURING_DATE.DAY_KEY=MONTHLY_SNAPSHOT_DURING.CLAIM_DAILY_SNAPSHOT_DATE )
  AND ( MONTHLY_SNAPSHOT_DURING.CLAIM_DAILY_SNAPSHOT_DATE BETWEEN   20020331 AND 20020430 )
  AND (

  COVERAGE_PLAN_DIM.COVERAGE_CATEGORY_CODE  =  'STD'
  AND  COVERAGE_PLAN_DIM.COVERAGE_TYPE_CODE  IN  ('NST', 'STAT', 'SUPP')
  AND  COVERAGE_PLAN_DIM.MARKET_GROUP  =  'Employer Market'
  AND  COVERAGE_PLAN_DIM.MARKET_SEGMENT  IN  ('National Accounts',

'Priority Accounts', 'Private Label Direct', 'Private Label Turnkey', 'Regional Accounts')
  AND CLAIM_PROCESSING_EXAMINER.BUSINESS_ROLE_DESC IN ('TELEPHONIC ADJUDICATE EXAMINER', 'TELEPHONIC ADJUDICATE SPECIALIST', 'TELEPHONIC ADJUDICATE TEAM LEADER')
  AND ( MONTHLY_SNAPSHOT_DURING.CLAIM_DAILY_SNAPSHOT_DATE BETWEEN 20020331 AND 20020430 )
  AND ( CLAIM_PROCESSING_EXAMINER.PROCESSORS_OFFICE IN ('SYRACUSE DISABILITY CLAIM OFFICE','SYRACUSE'))
  AND ( ( (MONTHLY_SNAPSHOT_DURING.THRD_STMNT_TO_FST_DCSN_BDTAT) ) <= 1 )   )
GROUP BY
  MNTHLY_DURING_DATE.MONTH,

  CLAIM_PROCESSING_EXAMINER.PROCESSORS_OFFICE,
  CLAIM_PROCESSING_EXAMINER.TEAM,
  CLAIM_PROCESSING_EXAMINER.PROCESSOR_ID,
  CLAIM_PROCESSING_EXAMINER.PROCESSORS_FULL_NAME

================================================================================

The problem SQL (This is the sql generated by one of the TEMP table creation):

INSERT INTO "SYS"."ORA_TEMP_1_13FB"
SELECT
  /*+ NO_EXPAND SEMIJOIN_DRIVER */ "CMSF"."CLAIM_DAILY_SNAPSHOT_KEY" "C0",
"CMSF"."NO_OF_DECISION_TRX" "C1",
"CLAIM_PROCESSING_EXAMINER"."PROCESSORS_FULL_NAME" "C2",
"CLAIM_PROCESSING_EXAMINER"."PROCESSOR_ID" "C3",
"CLAIM_PROCESSING_EXAMINER"."TEAM" "C4",
"CLAIM_PROCESSING_EXAMINER"."PROCESSORS_OFFICE" "C5"
FROM
"DISCLM"."EXAMINER_DIM" "CLAIM_PROCESSING_EXAMINER",
"DISCLM"."CLAIM_MONTHLY_SNAPSHOT_FACT" "CMSF",
"DISCLM"."COVERAGE_PLAN_DIM" "COVERAGE_PLAN_DIM"
WHERE
  ("CLAIM_PROCESSING_EXAMINER"."PROCESSORS_OFFICE"='SYRACUSE' OR "CLAIM_PROCESSING_EXAMINER"."PROCESSORS_OFFICE"='SYRACUSE DISABILITY CLAIM OFFICE') AND
  ("CLAIM_PROCESSING_EXAMINER"."BUSINESS_ROLE_DESC"='TELEPHONIC ADJUDICATE EXAMINER' OR "CLAIM_PROCESSING_EXAMINER"."BUSINESS_ROLE_DESC"='TELEPHONIC ADJUDICATE SPECIALIST' OR "CLAIM_PROCESSING_EXAMINER"."BUSINESS_ROLE_DESC" ='TELEPHONIC ADJUDICATE TEAM LEADER') AND
"CMSF"."PROCESSING_EXMNR_SKEY"

="CLAIM_PROCESSING_EXAMINER"."EXAMINER_SKEY" AND
"CMSF"."PROCESSING_EXMNR_SKEY"=ANY (SELECT /*+ SEMIJOIN_DRIVER */
"CLAIM_PROCESSING_EXAMINER"."EXAMINER_SKEY" FROM "DISCLM"."EXAMINER_DIM" "CLAIM_PROCESSING_EXAMINER" WHERE
("CLAIM_PROCESSING_EXAMINER"."PROCESSORS_OFFICE"='SYRACUSE' OR "CLAIM_PROCESSING_EXAMINER"."PROCESSORS_OFFICE"='SYRACUSE DISABILITY CLAIM OFFICE') AND
  ("CLAIM_PROCESSING_EXAMINER"."BUSINESS_ROLE_DESC"='TELEPHONIC ADJUDICATE EXAMINER' OR "CLAIM_PROCESSING_EXAMINER"."BUSINESS_ROLE_DESC"='TELEPHONIC ADJUDICATE SPECIALIST' OR "CLAIM_PROCESSING_EXAMINER"."BUSINESS_ROLE_DESC" ='TELEPHONIC ADJUDICATE TEAM LEADER')) AND
"CMSF"."THRD_STMNT_TO_FST_DCSN_BDTAT"<=1 AND
"CMSF"."COVERAGE_PLAN_SKEY"=ANY (SELECT /*+ SEMIJOIN_DRIVER */
"COVERAGE_PLAN_DIM"."COVERAGE_PLAN_SKEY" FROM "DISCLM"."COVERAGE_PLAN_DIM" "COVERAGE_PLAN_DIM" WHERE "COVERAGE_PLAN_DIM"."MARKET_GROUP"='Employer Market' AND
"COVERAGE_PLAN_DIM"."COVERAGE_CATEGORY_CODE"='STD' AND
  ("COVERAGE_PLAN_DIM"."MARKET_SEGMENT"='National Accounts' OR

"COVERAGE_PLAN_DIM"."MARKET_SEGMENT"='Priority Accounts' OR
"COVERAGE_PLAN_DIM"."MARKET_SEGMENT"='Private Label Direct' OR
"COVERAGE_PLAN_DIM"."MARKET_SEGMENT"='Private Label Turnkey' OR
"COVERAGE_PLAN_DIM"."MARKET_SEGMENT"='Regional Accounts') AND

("COVERAGE_PLAN_DIM"."COVERAGE_TYPE_CODE"='NST' OR "COVERAGE_PLAN_DIM"."COVERAGE_TYPE_CODE"='STAT' OR "COVERAGE_PLAN_DIM"."COVERAGE_TYPE_CODE"='SUPP')) In this sql, 4th line from the bottom is doing a join on cmsf.coverage_plan_skey with the subselect. It is not doing a join on cmsf.coverage_plan_skey with coverage_plan_dim.coverage_plan_skey as an additional condition but it is using coverage_plan_dim as part of "FROM" clause. Due to this, it is doing cartesian product with coverage plan dim.

It is doing correctly for other table (CLAIM_PROCESSING_EXAMINER). One thing to note here is that it is doing a key join between CMSF and CLAIM_PROCESSING_EXAMINER because (I think) we are selecting some of the columns from this table where as none of the columns being selected from COVERAGE_PLAN_DIM. The db version is 8.1.7.4 and it is on HP v11. All the tables and indexes were analyzed.

I appreciate your suggestions/comments.

Thanks.

Best Regards,
Prasad
860 843 8377

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Prasada.Gunda_at_hartfordlife.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Apr 09 2003 - 16:43:43 CDT