please help me understand what this tkprof output is saying

From: oofoof <oofoofoof_at_ureach.com>
Date: 11 Dec 2002 14:34:51 -0800
Message-ID: <894b11eb.0212111434.39132a05@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 Wed Dec 11 2002 - 16:34:51 CST