On Feb 28, 6:59 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -
The top query is the 45 minute query. You see the explain plan right
below it........
Received on Thu Mar 01 2007 - 09:57:53 CST
