| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Incorrect SQL execution plan in Star Schema TEMP table creation.
Hi Gaja,
Thanks for your response and suggestions.
In fact, we know why this query is running slow and taking all the temp space. It is taking all it's time in one of the TEMP table generation (SQL text is available at the end of the original email) because it is missing one of the join condition(cmsf.coverage_plan_skey with coverage_plan_dim.coverage_plan_skey) and causing cartesian join. I also opened a TAR to find out if there are any known bugs.
As you suggested (and I need this info for TAR as well), I am going to turn on trace and run the trace file using tkprof. I will update the list if I hear anything from Oracle tech support.
Thanks for your help again.
Best Regards,
Prasad
860 843 8377
Gaja Krishna
Vaidyanatha To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
<oraperfman_at_ya cc:
hoo.com> Subject: Re: Incorrect SQL execution plan in Star Schema TEMP table
Sent by: creation.
root_at_fatcity.c
om
04/09/2003
07:28 PM
Please respond
to ORACLE-L
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
> 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 ===
-- 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). -- 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 Thu Apr 10 2003 - 10:33:36 CDT
 |
 |