Re: Why is this simple query taking forever?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 1 Mar 2007 09:06:29 -0800
Message-ID: <1172768789.311617.208850@t69g2000cwt.googlegroups.com>

On Mar 1, 10:57 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> On Feb 28, 6:59 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> > 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
>
> The top query is the 45 minute query. You see the explain plan right
> below it........

I had trouble following the original post also. Let's see if we can unwind the logic of the main SQL query that takes 45 minutes to complete:
* Main SQL calls LAST_SUBSCR_CODE PL/SQL function (context switch) for every row returned (dep=0 in a 10046 trace) * LAST_SUBSCR_CODE calls LAST_SUBSCR_DATE potentially for every row in CUSTOMER_ORDER, LAST_SUBSCR_CODE hits the same tables as the main SQL statement (dep=1 in a 10046 trace)
* LAST_SUBSCR_DATE hits three of the same tables as the main SQL statement (dep=2 in a 10046 trace)
Main SQL calls LAST_SUBSCR_STATUS_TMP PL/SQL function (context switch) for every row returned (dep=0 in a 10046 trace) * LAST_SUBSCR_STATUS_TMP calls LAST_SUBSCR_DATE_TMP potentially for every row in CUSTOMER_ORDER, LAST_SUBSCR_STATUS_TMP hits three of the same tables used by the main SQL statement (dep=1 in a 10046 trace) * LAST_SUBSCR_DATE_TMP hits three of the same tables as the main SQL statement (dep=2 in a 10046 trace)

(dep=0 in a 10046 trace)
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; LAST_SUBSCR_CODE (dep=1 in a 10046 trace)

---

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);

LAST_SUBSCR_DATE (dep=2 in a 10046 trace)

---

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; LAST_SUBSCR_STATUS_TMP (dep=1 in a 10046 trace)

---

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); LAST_SUBSCR_DATE_TMP (dep=2 in a 10046 trace)

---

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';

So, where might the problems be? You cannot look at the explain plan for each SQL statement in isolation. How many times are you scanning the tables (or indexes for the tables): CUSTOMER_ORDER, ORDER_LINE, PRODUCT, NEWSLETTER_SUBSCRIPTION? How much CPU time are you losing due to excessive context switches?

Suggestions:
* See if you can accomplish some or all of the above using analytical functions.
* Unwind the logic to accomplish everything in a single SQL statement.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Mar 01 2007 - 11:06:29 CST