- Check in the where clause to make sure that you have the correct number
of joins.
- You are doing a FTS on THIS_DATE. It *may* help you to index the day_date
column. How many rows are in this_date?
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"oofoof" <oofoofoof_at_ureach.com> 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 - 09:49:10 CST
