SQL statement help

From: Ed Lufker <elufker_at_inago.swcp.com>
Date: 24 Sep 1999 21:30:35 GMT
Message-ID: <7sgqhr$8h8$1@sloth.swcp.com>

Hi all:

Here's the whole trace file, thanks for any help with this. Could someone help me get a more efficent statement, this one runs too long

Thanks in advance for any help here.
eddie lufker

TKPROF: Release 8.0.5.0.0 - Production on Thu Sep 23 11:42:15 1999

Trace file: ././hbf_tst_ora_23456.trc
Sort options: default

count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

ALTER SESSION
                         SET SQL_TRACE = TRUE

call     count       cpu    elapsed       disk      query    current

rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer goal: CHOOSE
Parsing user id: 37 (HBFADMIN)

begin DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

total 4 0.00 0.00 0 0 0 2

Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Parsing user id: 37 (HBFADMIN)

select couponorder.cporid from HBFADMIN.COUPONORDER
                             , HBFADMIN.COUPONORDERDETAIL
                             , HBFADMIN.SPONSOR
                             , HBFADMIN.SITE
                             , HBFADMIN.STATE
                             , HBFADMIN.COUNTY
                             , HBFADMIN.CAMPAIGN

where couponorder.cpordate BETWEEN '16-SEP-99' AND '17-SEP-99' AND couponorder.cporstateid = state.id AND couponorder.cporsiteid = site.siteid AND state.state_id = county.state_id
AND couponorder.cporcountyid = county.county_id AND couponorderdetail.cpodsponid = sponsor.sponid AND couponorderdetail.cpodcampid = campaign.campid ORDER BY cporid

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 7 0 7 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 57 16221 3 0
------- ------ -------- ---------- ---------- ---------- ----------

total 3 0.00 0.00 64 16221 10 0

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 37 (HBFADMIN)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (ORDER BY)
      0    MERGE JOIN
      0     SORT (JOIN)
      0      MERGE JOIN
      0       SORT (JOIN)
      0        NESTED LOOPS
      0         NESTED LOOPS
   2488          NESTED LOOPS
   2488           NESTED LOOPS
    622            TABLE ACCESS (FULL) OF 'CAMPAIGN'
   2488            TABLE ACCESS (BY INDEX ROWID) OF
                       'COUPONORDER'
   3110             INDEX (RANGE SCAN) OF
                        'NDX_COUPORDER_CPORDATE' (NON-UNIQUE)
   2488           TABLE ACCESS (BY INDEX ROWID) OF 'STATE'
   4976            INDEX (RANGE SCAN) OF 'PK_STATE' (NON-UNIQUE)
   2488          INDEX (RANGE SCAN) OF 'PK_COUNTY' (NON-UNIQUE)
      0         INDEX (RANGE SCAN) OF 'NDX_SITE_SITEID' (NON-UNIQUE)

      0       SORT (JOIN)
      0        TABLE ACCESS (FULL) OF 'COUPONORDERDETAIL'
      0     SORT (JOIN)
      0      TABLE ACCESS (FULL) OF 'SPONSOR'

********************************************************************************

ALTER SESSION
                         SET SQL_TRACE = false

call     count       cpu    elapsed       disk      query    current

rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 37 (HBFADMIN)

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 4 0.00 0.00 7 0 7 0
Execute 5 0.00 0.00 0 0 0 2
Fetch 1 0.00 0.00 57 16221 3 0
------- ------ -------- ---------- ---------- ---------- ----------

total 10 0.00 0.00 64 16221 10 2

Misses in library cache during parse: 2 Misses in library cache during execute: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 25 0.00 0.00 0 0 0 0
Execute 55 0.00 0.00 0 0 0 0
Fetch 163 0.00 0.00 47 289 0 145
------- ------ -------- ---------- ---------- ---------- ----------

total 243 0.00 0.00 47 289 0 145

Misses in library cache during parse: 0

5 user SQL statements in session. 25 internal SQL statements in session. 30 SQL statements in session.
1 statement EXPLAINed in this session.

Trace file: ././hbf_tst_ora_23456.trc
Trace file compatibility: 7.03.02
Sort options: default

       1  session in tracefile.
       5  user  SQL statements in trace file.
      25  internal SQL statements in trace file.
      30  SQL statements in trace file.
      12  unique SQL statements in trace file.
       1  SQL statements EXPLAINed using schema:
           HBFADMIN.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
     468  lines in trace file.

Received on Fri Sep 24 1999 - 16:30:35 CDT