Re: Optimizing inline view

From: Mike C <michaeljc70_at_hotmail.com>
Date: 7 Nov 2006 13:14:30 -0800
Message-ID: <1162934069.945535.3790@m73g2000cwd.googlegroups.com>

Charles Hooper wrote:
> 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.

Here you go. Hopefully you are viewing with a font that makes it look readable.

OBJECT_NAME

                                                         OBJECT_TYPE

--------------------------------------------------------------------------------------------------------------------------------
------------------
NT_CONDITION_MODIFIER
                                                         TABLE

NT_LINK
                                                         TABLE

NT_DATE_TIME_MODIFIER
                                                         TABLE

3 rows selected

TABLE_NAME                     NUM_ROWS               BLOCKS
     LAST_ANALYZED
------------------------------ ----------------------
---------------------- -------------------------
NT_CONDITION_MODIFIER          3161690                9616
     12-SEP-06
NT_DATE_TIME_MODIFIER          90634                  700
     28-OCT-06
NT_LINK                        27836040               310309
     13-SEP-06

3 rows selected

NAME                                                             VALUE








----------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

db_file_multiblock_read_count                                    128








optimizer_features_enable                                        9.2.0








sort_area_size                                                   65536








sort_area_retained_size                                          0








optimizer_index_cost_adj                                         100








optimizer_index_caching                                          0

pga_aggregate_target
8589934592          

7 rows selected Received on Tue Nov 07 2006 - 15:14:30 CST