Re: Why is this simple query taking forever?

On Feb 28, 4:54 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> Hi,
>
> Actually this query may not be so simple. But, in short, it is a
> query which has 2 inline functions. The inline functions themselves
> have inline functions. When each query is run by itself, it is rather
> fast. However, if you run the top level query, it takes over 45
> minutes.
>
> I've pasted the queries below, starting with the top level query, it's
> explain plan, and a timing of how long it took. If the lower level
> queries run quickly, qhy is the top level query not running quickly???
>
> Any assistance would be helpful. Thanks in advance.
>
> SELECT p.SUBPRODUCT_ID, ns.NEWSLETTER_NAME, ns.FREQUENCY, ns.FORMAT,
> last_subscr_code (1068733494, p.SUBPRODUCT_ID) last_code,
> last_subscr_status_tmp(1068733494, p.SUBPRODUCT_ID) last_type
> FROM customer_order co, order_line ol, .product p,
> newsletter_subscription ns
> WHERE co.ORDER_ID = ol.ORDER_ID
> AND ol.PRODUCT_ID = p.PRODUCT_ID
> AND p.CODE = ns.CODE
> AND co.CUSTOMER_ID = 1068733494
> AND ns.newsletter_id = 208
> GROUP BY ns.NEWSLETTER_NAME, p.SUBPRODUCT_ID, ns.FREQUENCY, ns.FORMAT;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=84 Card=1 Bytes=82)
> 1 0 SORT (GROUP BY) (Cost=84 Card=1 Bytes=82)
> 2 1 NESTED LOOPS (Cost=57 Card=1 Bytes=82)
> 3 2 NESTED LOOPS (Cost=48 Card=9 Bytes=351)
> 4 3 NESTED LOOPS (Cost=39 Card=9 Bytes=198)
> 5 4 TABLE ACCESS (BY INDEX ROWID) OF
> 'CUSTOMER_ORDER' (Cost=12 Card=9 Bytes=108)
> 6 5 INDEX (RANGE SCAN) OF 'ORDER_CUSTOMER_IDX' (NON-
> UNIQUE) (Cost=3 Card=9)
> 7 4 INDEX (RANGE SCAN) OF 'ORDER_PRODUCT_IDX' (UNIQUE)
> (Cost=3 Card=431090 Bytes=4310900)
> 8 3 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT' (Cost=1
> Card=29640 Bytes=503880)
> 9 8 INDEX (UNIQUE SCAN) OF 'SYS_C005663' (UNIQUE)
> 10 2 TABLE ACCESS (BY INDEX ROWID) OF
> 'NEWSLETTER_SUBSCRIPTION' (Cost=1 Card=435 Bytes=18705)
>
> LAST_SUBSCR_CODE
> ----------------
> SELECT ns.CODE
> FROM CUSTOMER_ORDER co, ORDER_LINE ol, PRODUCT p,
> newsletter_subscription ns
> WHERE co.ORDER_ID = ol.ORDER_ID
> AND ol.PRODUCT_ID = p.PRODUCT_ID
> AND p.CODE = ns.CODE
> AND ns.NEWSLETTER_ID = 208
> AND co.CUSTOMER_ID = 1068733494
> AND co.ORDER_DATE = last_subscr_date(1068733494, 208);
>
> CODE
> ----------
> 864
>
> Elapsed: 00:00:21.77
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=1 Bytes=53)
> 1 0 NESTED LOOPS (Cost=17 Card=1 Bytes=53)
> 2 1 NESTED LOOPS (Cost=16 Card=1 Bytes=45)
> 3 2 NESTED LOOPS (Cost=15 Card=1 Bytes=30)
> 4 3 TABLE ACCESS (BY INDEX ROWID) OF
> 'CUSTOMER_ORDER' (Cost=12 Card=1 Bytes=20)
> 5 4 INDEX (RANGE SCAN) OF 'ORDER_CUSTOMER_IDX' (NON-
> UNIQUE) (Cost=3 Card=1)
> 6 3 INDEX (RANGE SCAN) OF 'ORDER_PRODUCT_IDX' (UNIQUE)
> (Cost=3 Card=431090 Bytes=4310900)
> 7 2 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT' (Cost=1
> Card=29640 Bytes=444600)
> 8 7 INDEX (UNIQUE SCAN) OF 'SYS_C005663' (UNIQUE)
> 9 1 TABLE ACCESS (BY INDEX ROWID) OF
> 'NEWSLETTER_SUBSCRIPTION' (Cost=1 Card=4 Bytes=32)
> 10 9 INDEX (UNIQUE SCAN) OF 'PK_NEWS_SUBCR_CODE' (UNIQUE)
>
> LAST_SUBSCR_DATE
> ----------------
> SELECT MAX(co.ORDER_DATE)
> FROM CUSTOMER_ORDER co, ORDER_LINE ol, PRODUCT p
> WHERE co.ORDER_ID = ol.ORDER_ID
> AND ol.PRODUCT_ID = p.PRODUCT_ID
> AND p.SUBPRODUCT_ID = 208
> AND co.CUSTOMER_ID = 1068733494;
>
> MAX(CO.OR
> ---------
> 11-DEC-06
>
> Elapsed: 00:00:00.06
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=45 Card=1 Bytes=37)
> 1 0 SORT (AGGREGATE)
> 2 1 HASH JOIN (Cost=45 Card=1 Bytes=37)
> 3 2 TABLE ACCESS (BY INDEX ROWID) OF
> 'CUSTOMER_ORDER' (Cost=12 Card=9 Bytes=180)
> 4 3 INDEX (RANGE SCAN) OF 'ORDER_CUSTOMER_IDX' (NON-
> UNIQUE) (Cost=3 Card=9)
> 5 2 NESTED LOOPS (Cost=32 Card=224 Bytes=3808)
> 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT' (Cost=2
> Card=5 Bytes=35)
> 7 6 INDEX (RANGE SCAN) OF 'PROD_SUBPRODUCT_IDX' (NON-
> UNIQUE) (Cost=1 Card=5)
> 8 5 TABLE ACCESS (BY INDEX ROWID) OF
> 'ORDER_LINE' (Cost=6 Card=431090 Bytes=4310900)
> 9 8 INDEX (RANGE SCAN) OF 'OL_PRODUCT_IDX' (NON-
> UNIQUE) (Cost=2 Card=431090)
>
> LAST_SUBSCR_STATUS_TMP
> ----------------------
> SELECT co.STATUS
> FROM CUSTOMER_ORDER co, ORDER_LINE ol, PRODUCT p
> WHERE co.ORDER_ID = ol.ORDER_ID
> AND ol.PRODUCT_ID = p.PRODUCT_ID
> AND co.CUSTOMER_ID = 1068733494
> AND p.SUBPRODUCT_ID = 208
> AND co.ORDER_DATE = last_subscr_date_tmp(1068733494, 208);
>
> STATUS
> ----------
> Active
>
> Elapsed: 00:00:03.75
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1 Bytes=47)
> 1 0 NESTED LOOPS (Cost=16 Card=1 Bytes=47)
> 2 1 NESTED LOOPS (Cost=15 Card=1 Bytes=40)
> 3 2 TABLE ACCESS (BY INDEX ROWID) OF
> 'CUSTOMER_ORDER' (Cost=12 Card=1 Bytes=30)
> 4 3 INDEX (RANGE SCAN) OF 'ORDER_CUSTOMER_IDX' (NON-
> UNIQUE) (Cost=3 Card=1)
> 5 2 INDEX (RANGE SCAN) OF 'ORDER_PRODUCT_IDX' (UNIQUE)
> (Cost=3 Card=431090 Bytes=4310900)
> 6 1 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT' (Cost=1
> Card=5 Bytes=35)
> 7 6 INDEX (UNIQUE SCAN) OF 'SYS_C005663' (UNIQUE)
>
> LAST_SUBSCR_DATE_TMP
> --------------------
> SELECT MAX(co.ORDER_DATE)
> FROM CUSTOMER_ORDER co, ORDER_LINE ol, PRODUCT p
> WHERE co.ORDER_ID = ol.ORDER_ID
> AND ol.PRODUCT_ID = p.PRODUCT_ID
> AND p.SUBPRODUCT_ID = 208
> AND co.CUSTOMER_ID = 1068733494
> and co.status = 'Active';
>
> MAX(CO.OR
> ---------
> 11-DEC-06
>
> Elapsed: 00:00:00.02

Where is the SQL and explain plan for the 45 minute query? When was the last time statistics were collected on each object? You might want to update the statistics and then recheck the plan for the poorly performing query?

HTH -- Mark D Powell -- Received on Wed Feb 28 2007 - 18:59:09 CST