Hi Prasada,
Why don't you try turning on 10046 for the session in
question (go to Hotsos.com for a paper in case you
don't know how to use this), which will collect the
required trace data in a file in USER_DUMP_DEST, run
tkprof on the trace file (please use a 9i tkprof) and
then take a look at the output that tkprof provides.
That will be your best guide in determining what
portion of the execution time is spent waiting and on
"which wait events". That will be our best bet to even
craft a "theory", as to why things are slow. Let us
know.
Cheers,
Gaja
- Prasada.Gunda_at_hartfordlife.com wrote:
>
> 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
> --
>
=== message truncated ===
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gaja Krishna Vaidyanatha
INET: oraperfman_at_yahoo.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 - 18:28:41 CDT
