Re: SQL statement help

Ed,

Oracle chooses the RULE to execute your query - hence the number of NESTED LOOPS operations, which tends to be inefficient as the number of joined rows increases. This is because there are no statistics on the tables. Analyze tables and let the COST to execute it - this should be faster.

If you do not want the CBO, rework your FROM clause, trying to minimize the amount of rows received from inner operations.

Regards,
Karen Abgarian.

Ed Lufker wrote:

> 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
>
> (c) Copyright 1998 Oracle Corporation. All rights reserved.
>
> 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 Mon Sep 27 1999 - 11:20:26 CDT