Re: Optimizing inline view

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 7 Nov 2006 13:04:48 -0800
Message-ID: <1162933487.882352.78170@b28g2000cwb.googlegroups.com>

Mike C wrote:
> Charles Hooper wrote:
> > I don't see anything really odd in the above explain plan, you might
> > try a DBMS_XPLAN to see if it helps - Daniel Morgan recently posted how
> > to work with this feature in another thread.
> >
> > Looking at the SQL statement (reformatted so that I can follow it):
> > SELECT
> > L1.LINK_PVID LINK_PVID_1,
> > FC_1,
> > L2.LINK_PVID LINK_PVID_2,
> > FC_2,
> > QT_UTIL.GET_SUBREGION(L1.LEFT_ADMIN_PLACE_ID) REGION,
> > CM.VALUE COND_MOD,
> > NVL(
> > (SELECT
> > 'Y'
> > FROM
> > NT_DATE_TIME_MODIFIER DTM
> > WHERE
> > ROWNUM<2
> > AND DTM.CONDITION_ID=X.CONDITION_ID),'N') DATE_MODIFIERS
> > FROM
> > (SELECT
> > L1.CONDITION_ID,
> > L1.LINK_ID L1_LINK_ID,
> > L2.LINK_ID L2_LINK_ID,
> > L1.FUNCTIONAL_CLASS FC_1,
> > L2.FUNCTIONAL_CLASS FC_2
> > FROM
> > TMP_LINKS L1,
> > TMP_LINKS L2
> > WHERE
> > L1.CNT=2
> > AND L2.FUNCTIONAL_CLASS <= L1.FUNCTIONAL_CLASS
> > AND L1.NAV_STRAND_ID=L2.NAV_STRAND_ID
> > AND L1.SEQUENCE_NUMBER=0
> > AND L2.SEQUENCE_NUMBER=1
> > AND L1.FUNCTIONAL_CLASS<5) X,
> > NT_LINK L1,
> > NT_LINK L2,
> > NT_CONDITION_MODIFIER CM
> > WHERE
> > X.L1_LINK_ID=L1.LINK_ID
> > AND X.L2_LINK_ID=L2.LINK_ID
> > AND CM.CONDITION_ID(+)=X.CONDITION_ID
> > AND CM.TYPE(+)=1;
> >
> > QT_UTIL.GET_SUBREGION(L1.LEFT_ADMIN_PLACE_ID) REGION - this seems to
> > be a PL/SQL function, is it optimized?
> >
> > NVL(
> > (SELECT
> > 'Y'
> > FROM
> > NT_DATE_TIME_MODIFIER DTM
> > WHERE
> > ROWNUM<2
> > AND DTM.CONDITION_ID=X.CONDITION_ID),'N') DATE_MODIFIERS
> > - might be better to throw this into an inline view. I see that you
> > are stopping after the first row is retrieved, there may be better
> > ways.
> >
> > FROM
> > TMP_LINKS L1,
> > TMP_LINKS L2
> > WHERE
> > L1.CNT=2
> > AND L2.FUNCTIONAL_CLASS <= L1.FUNCTIONAL_CLASS
> > AND L1.NAV_STRAND_ID=L2.NAV_STRAND_ID
> > AND L1.SEQUENCE_NUMBER=0
> > AND L2.SEQUENCE_NUMBER=1
> > AND L1.FUNCTIONAL_CLASS<5
> >
> > Looks like an intentional Cartesian join. It might also help to
> > specify AND L2.FUNCTIONAL_CLASS<5. Maybe if you prejoin each of these
> > copies of TMP_LINKS with NT_LINK, and then join those results with each
> > other it would help.
> >
> > With these changes, your SQL statement _might_ look similar to this:
> > SELECT
> > L1.LINK_PVID LINK_PVID_1,
> > FC_1,
> > L2.LINK_PVID LINK_PVID_2,
> > FC_2,
> > QT_UTIL.GET_SUBREGION(L1.LEFT_ADMIN_PLACE_ID) REGION,
> > CM.VALUE COND_MOD,
> > NVL2(DTM.MATCHES,'Y','N') DATE_MODIFIERS
> > FROM
> > (SELECT
> > L1.LINK_PVID_1,
> > L1.CONDITION_ID,
> > L1.FC_1,
> > L1.NAV_STRAND_ID,
> > L2.LINK_PVID_2,
> > L2.FC_2
> > FROM
> > (SELECT
> > L1.LINK_PVID LINK_PVID_1,
> > TL1.CONDITION_ID,
> > TL1.FUNCTIONAL_CLASS FC_1,
> > TL1.NAV_STRAND_ID
> > FROM
> > TMP_LINKS TL1,
> > NT_LINK L1
> > WHERE
> > TL1.SEQUENCE_NUMBER=0
> > AND TL1.CNT=2
> > AND TL1.FUNCTIONAL_CLASS<5
> > AND TL1.LINK_ID=L1.LINK_ID) L1,
> > (SELECT
> > L2.LINK_PVID LINK_PVID_2,
> > TL2.FUNCTIONAL_CLASS FC_2,
> > TL2.NAV_STRAND_ID
> > FROM
> > TMP_LINKS TL2,
> > NT_LINK L2
> > WHERE
> > TL2.SEQUENCE_NUMBER=1
> > AND TL2.FUNCTIONAL_CLASS<5
> > AND TL2.LINK_ID=L2.LINK_ID) L2
> > WHERE
> > L2.NAV_STRAND_ID=L1.NAV_STRAND_ID
> > AND L2.FC_2<=L1.FC_1) X,
> > (SELECT
> > CONDITION_ID,
> > COUNT(*) MATCHES
> > FROM
> > NT_DATE_TIME_MODIFIER
> > GROUP BY
> > CONDITION_ID) DTM,
> > NT_CONDITION_MODIFIER CM
> > WHERE
> > X.CONDITION_ID=DTM.CONDITION_ID(+)
> > AND X.CONDITION_ID=CM.CONDITION_ID(+)
> > AND CM.TYPE(+)=1;
> >
> > Without understanding your data model, the above could execute faster,
> > the same, or slower than the SQL that you posted. I am not sure that
> > this exercise will help you resolve the problem, but it may be another
> > way to look at the SQL statement.
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.

> I appreciate the help. I did run the above query and it seemed to chug
> along pretty slowly (no results after 40 min). Since this problem is
> basically solved with using the temp table, I am wondering more in
> general. I mean, yes I can create temp table(s) and break things up to
> get them to execute faster. Maybe that is the route to go, maybe it
> looks amateurish. I've noticed if you get beyond around 6 tables,
> Oracle is more iffy with picking the best join order . That is why I
> was wondering if there is a hint or more elegant way to tell Oracle "do
> this first" without creating temp tables. On less than 6 tables, I can
> throw in a hint and usually get it to do what I want and it isn't
> usually an issue.

Something does not look right in the plan - the cost is very low considering the length of time. Can you post the results of these queries?
SELECT
  OBJECT_NAME,
  OBJECT_TYPE
FROM
  DBA_OBJECTS
WHERE
  OBJECT_NAME IN
('TMP_LINKS','NT_DATE_TIME_MODIFIER','NT_LINK','NT_CONDITION_MODIFIER'); SELECT
  TABLE_NAME,
  NUM_ROWS,
  BLOCKS,
  LAST_ANALYZED
FROM
  DBA_TABLES
WHERE
  TABLE_NAME IN
('TMP_LINKS','NT_DATE_TIME_MODIFIER','NT_LINK','NT_CONDITION_MODIFIER'); SELECT
  NAME,
  VALUE
FROM
  V$PARAMETER
WHERE
  NAME IN
('sort_area_size','sort_area_retained_size','optimizer_features_enable',

'db_file_multiblock_read_count',
'pga_aggregate_target','optimizer_index_caching',
'optimizer_index_cost_adj');

A 10046 trace at level 8 would be very helpful.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Nov 07 2006 - 15:04:48 CST