Re: please help me understand what this tkprof output is saying

A MERGE JOIN CARTESIAN can be the result of a couple of factors:

Outdated or missing statistics on one or more tables in the query. Default behaviour of the CBO with good statistics.

Doing cartesian products is normal expected behavior from the CBO as it sometimes is cheaper (in CBO terms and also the real world) to place two (or more) non-joined row sources early in the join sequnce - thus giving the merge join cartesian operation. However, if your statistics are stale or missing on one or more tables in the query this could lead the CBO into a false sense that the MERGE JOIN CARTESIAN is the most efficient way to handle this when, in fact, it isn't. I'll surmise that you do have statistics on several of the tables in the query (possibly all of them) as the CBO won't function without them however some of those statistics are not current.

I would generate current statistics on U_SECURITYCUSTOMER, THIS_DATE, CUSTOMER_PARENT, CUSTOMER_LEVEL, TIME_DAYS, CUSTOMER and FACT_EPISODE and run this query again. You may see dramatic improvement in the response time.

David Fitzjarrell

oofoofoof_at_ureach.com (oofoof) wrote in message news:<894b11eb.0212111434.39132a05_at_posting.google.com>...
> We are using a tool that generates sql to run against
> an Oracle DB. Some of the queries are taking a long time to process.
> I searched on deja and figured out how to capture usage.
> The problem is that I can't figure out from the
> tkprof output, what the problem is or how to fix it. I'm a newbie at
> this,
> I will buy a Oracle tuning book which hopefully will help me with
> this, but
> its going to take me a while to come up to speed on this, so any
> pointers will be greatly appreciated. Here is the output from tkprof
>
> ********************************************************************************
>
> SELECT
> TIME_DAYS_BACKLOG.day_date,
> CUSTOMER.U_ACCOUNT_NO,
> THIS_DATE.day_date,
> CUSTOMER.customer_name,
> CUSTOMER_PARENT1.DESCRIPTION,
> count(distinct FACT_EPISODE.episode_key)
> FROM
> U_SECURITYCUSTOMER,
> THIS_DATE,
> CUSTOMER_PARENT CUSTOMER_PARENT1,
> CUSTOMER_LEVEL,
> TIME_DAYS TIME_DAYS_BACKLOG,
> CUSTOMER,
> FACT_EPISODE
> WHERE
> ( CUSTOMER.customer_key=FACT_EPISODE.customer_key )
> AND ( THIS_DATE.day_date >= FACT_EPISODE.episode_date )
> AND ( CUSTOMER_LEVEL.CUSTOMER_KEY=CUSTOMER.CUSTOMER_KEY )
> AND ( FACT_EPISODE.episode_date <= TIME_DAYS_BACKLOG.day_date
> AND (FACT_EPISODE.u_closed_date IS NULL OR
> nvl(FACT_EPISODE.U_CLOSED_DATE,to_date('01/01/1901','mm/dd/yyyy'))
> >= TIME_DAYS_BACKLOG.day_date)
> )
> AND ( U_SECURITYCUSTOMER.U_CUSTOMER_USERNAME='USER1' )
> AND ( CUSTOMER_PARENT1.DESCRIPTION=U_SECURITYCUSTOMER.U_CUSTOMER )
> AND ( CUSTOMER_LEVEL.CUSTOMER_NODE=CUSTOMER_PARENT1.CUSTOMER_NODE
> )
> AND ( CUSTOMER_PARENT1.customer_level=1 )
> AND (
> ( FACT_EPISODE.u_episodefunction_key = 1 )
> AND ( TIME_DAYS_BACKLOG.day_date BETWEEN (select
> THIS_DATE.day_date -1 from THIS_DATE) AND
> (select THIS_DATE.day_date from THIS_DATE) )
> )
> GROUP BY
> TIME_DAYS_BACKLOG.day_date,
> CUSTOMER.U_ACCOUNT_NO,
> THIS_DATE.day_date,
> CUSTOMER.customer_name,
> CUSTOMER_PARENT1.DESCRIPTION
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.01 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 2 71.02 74.60 0 1417073 19572
> 6
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 4 71.02 74.61 0 1417073 19572
> 6
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: 21 (BIZWATCH)
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 6 SORT GROUP BY
> 3412 FILTER
> 1323863 NESTED LOOPS
> 4891 NESTED LOOPS
> 4181 NESTED LOOPS
> 4181 NESTED LOOPS
> 3 NESTED LOOPS
> 3 MERGE JOIN CARTESIAN
> 2 TABLE ACCESS FULL THIS_DATE
> 3 SORT JOIN
> 2 TABLE ACCESS BY INDEX ROWID U_SECURITYCUSTOMER
> 3 INDEX RANGE SCAN (object id 7282)
> 4 TABLE ACCESS BY INDEX ROWID CUSTOMER_PARENT
> 94 INDEX RANGE SCAN (object id 6379)
> 4182 TABLE ACCESS BY INDEX ROWID CUSTOMER_LEVEL
> 4182 INDEX RANGE SCAN (object id 6378)
> 8360 TABLE ACCESS BY INDEX ROWID CUSTOMER
> 8360 INDEX UNIQUE SCAN (object id 4161)
> 9070 TABLE ACCESS BY INDEX ROWID FACT_EPISODE
> 9236 INDEX RANGE SCAN (object id 6423)
> 1328752 INDEX FAST FULL SCAN (object id 4668)
> 2 TABLE ACCESS FULL THIS_DATE
> 2 TABLE ACCESS FULL THIS_DATE
>
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 6 SORT (GROUP BY)
> 3412 FILTER
> 1323863 NESTED LOOPS
> 4891 NESTED LOOPS
> 4181 NESTED LOOPS
> 4181 NESTED LOOPS
> 3 NESTED LOOPS
> 3 MERGE JOIN (CARTESIAN)
> 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF
> 'THIS_DATE'
> 3 SORT (JOIN)
> 2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
> OF 'U_SECURITYCUSTOMER'
> 3 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'U_CUSTOMERUSERNAME' (NON-UNIQUE)
> 4 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'CUSTOMER_PARENT'
> 94 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'U_CUSTOMER' (NON-UNIQUE)
> 4182 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'CUSTOMER_LEVEL'
> 4182 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'U_CUSTOMERLEVELNODE' (NON-UNIQUE)
> 8360 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'CUSTOMER'
> 8360 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> 'SYS_C002436' (UNIQUE)
> 9070 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'FACT_EPISODE'
> 9236 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'U_FACTEPISODECUSTOMER' (NON-UNIQUE)
> 1328752 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF 'SYS_C003543'
> (UNIQUE)
> 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'THIS_DATE'
> 2 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'THIS_DATE'
Received on Thu Dec 12 2002 - 08:43:01 CST