SQL – Reformatting to Improve Performance 11

December 4, 2009

(Back to the Previous Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/1194b893e71e2e15

After analyzing I have found that this query is taking a long time to
execute

    (
       select count(*) as cntFixed
         from mdl_parameter_tree u
         where u.prmid not in
        (
          select t.prmid
          from mdl_parameter_tree t
         where t.rootnode in
          (
           select b.rootnode
           from subjective_prm_trans a, mdl_parameter_tree b
           where
                a.mdlid = b.mdlid and a.prmid = b.prmid and
                a.endnodeflag = 'N' and a.value between 0.0001 and 1
and
                a.prmid not in (select r.prmid from mdl_parameter_tree
r where trim(lower(r.prmname)) = 'project risk' and r.mdlid=
&var_mdlid ) and
                a.borrid= &var_borrId and a.mdlid= &var_mdlid and
a.user_id= &var_user_id
               ) and t.endnodeflag = 'E' and parametertype = 'S' and
mdlid= &var_mdlid
)         and
        ( u.endnodeflag ='E' and
         u.parametertype = 'S' and
         u.mdlid= &var_mdlid

   )
)
This query is taking 58 seconds to execute.
Below is the explain plan of the above query

--------------------------------------------------------------------------­-------
| Id  | Operation                |  Name                 | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------­-------
|   0 | SELECT STATEMENT         |                       |     1 |   284 |   349 |
|   1 |  SORT AGGREGATE          |                       |     1 |   284 |       |
|   2 |   FILTER                 |                       |       |       |       |
|   3 |    TABLE ACCESS FULL     | MDL_PARAMETER_TREE    |     1 |   284 |     3 |
|   4 |    FILTER                |                       |       |       |       |
|   5 |     NESTED LOOPS         |                       |     1 |   636 |   343 |
|   6 |      MERGE JOIN CARTESIAN|                       |     1 |   481 |   340 |
|   7 |       TABLE ACCESS FULL  | SUBJECTIVE_PRM_TRANS  |     1 |    68 |   337 |
|   8 |       BUFFER SORT        |                       |     1 |   413 |     3 |
|   9 |        TABLE ACCESS FULL | MDL_PARAMETER_TREE    |     1 |   413 |     3 |
|  10 |      TABLE ACCESS FULL   | MDL_PARAMETER_TREE    |    12 |  1860 |     3 |
|  11 |     TABLE ACCESS FULL    | MDL_PARAMETER_TREE    |     1 |   155 |     3 |
--------------------------------------------------------------------------­-------

Note: cpu costing is off, 'PLAN_TABLE' is old version

Please assist me sir to tune the below query

—-
It is a good idea to simplify the SQL statement, if possible.  The following may not be 100% accurate, so test the results.

Your original SQL statement, slightly reformatted:

select
  count(*) as cntFixed
from
  mdl_parameter_tree u
where
  u.prmid not in (
    select
      t.prmid
    from
      mdl_parameter_tree t
    where
      t.rootnode in (
        select
          b.rootnode
        from
          subjective_prm_trans a,
          mdl_parameter_tree b
        where
          a.mdlid = b.mdlid
          and a.prmid = b.prmid
          and a.endnodeflag = 'N'
          and a.value between 0.0001 and 1
          and a.prmid not in (
            select
              r.prmid
            from
              mdl_parameter_tree r
           where
             trim(lower(r.prmname)) = 'project risk'
             and r.mdlid= &var_mdlid )
          and a.borrid= &var_borrId
          and a.mdlid= &var_mdlid
          and a.user_id= &var_user_id)
      and t.endnodeflag = 'E'
      and parametertype = 'S'
      and mdlid= &var_mdlid)
  and u.endnodeflag ='E'
  and u.parametertype = 'S'
  and u.mdlid= &var_mdlid;

Now, removing one of the subqueries, transforming it to <> and OR:

select
  count(*) as cntFixed
from
  mdl_parameter_tree u
where
  u.prmid not in (
    select
      t.prmid
    from
      mdl_parameter_tree t
    where
      t.rootnode in (
        select
          b.rootnode
        from
          subjective_prm_trans a,
          mdl_parameter_tree b
        where
          a.mdlid = b.mdlid
          and a.prmid = b.prmid
          and a.endnodeflag = 'N'
          and a.value between 0.0001 and 1
          and (trim(lower(r.prmname)) <> 'project risk' or r.mdlid <> &var_mdlid)
          and a.borrid= &var_borrId
          and a.mdlid= &var_mdlid
          and a.user_id= &var_user_id)
      and t.endnodeflag = 'E'
      and parametertype = 'S'
      and mdlid= &var_mdlid)
  and u.endnodeflag ='E'
  and u.parametertype = 'S'
  and u.mdlid= &var_mdlid;

Let’s look at what this SQL statement is attempting to accomplish: Count of rows in mdl_parameter_tree that do not have a rootnode in the inner-most subquery.
Let’s rewrite per the specification to use an inline view, rather than a subquery:

select
  count(*) as cntFixed
from
  mdl_parameter_tree u,
  (select
    b.prmid,
    b.rootnode
  from
    subjective_prm_trans a,
    mdl_parameter_tree b
  where
    a.mdlid = b.mdlid
    and a.prmid = b.prmid
    and a.endnodeflag = 'N'
    and a.value between 0.0001 and 1
    and (trim(lower(b.prmname)) <> 'project risk' or b.mdlid <> &var_mdlid)
    and a.borrid= &var_borrId
    and a.mdlid= &var_mdlid
    and a.user_id= &var_user_id) n
where
  u.prmid=n.prmid(+)
  and n.prmid is null;

Once again, verify that this is showing the same number of rows as the original.

If you are still experiencing a problem, create a 10046 trace at level 8 for the query.  On this page I show how to enable a 10046 trace at level 12, which will work just as well as a level 8 trace for you:
http://forums.oracle.com/forums/thread.jspa?messageID=2384639&

This link shows how to decode the contents of a 10046 trace file:
http://forums.oracle.com/forums/thread.jspa?threadID=661124

SQL – Reformatting to Improve Performance 10

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/8176f2571da1da3d

I wanted to tune the below query.Any help would be of great help. Below is the query

--Find Year to Generate Risk Score Starts here
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as year
 from statement
 where borrid= &var_borrId and user_id= &var_user_id and coaid = &var_mdlid
 and to_char(stmtdt, 'yyyy') in
 (
   select os.year from
   (
     select borrid, year, count(*) as cntActuals
     from subjective_prm_trans
     where borrid= &var_borrId and user_id= &var_user_id and mdlid = &var_mdlid and endnodeflag = 'E'
     group by year, borrid
   ) os,
   (
     select j.borrid, j.year, j.cntVariable + k.cntFixed as cntMdlTotals
     from
     (
       select a.borrid, a.year , count(*) as cntVariable
       from subjective_prm_trans a, mdl_Parameter_Tree m
       where
       a.prmid = m.parentid and a.mdlid = m.mdlid and
       a.endnodeflag = 'N' and a.value between 0.0001 and 1 and
       a.prmid not in (select r.prmid from mdl_parameter_tree r
    where
       trim(lower(r.prmname)) = 'project risk' and r.mdlid= &var_mdlid ) and
       a.borrid= &var_borrId and a.mdlid= &var_mdlid and a.user_id= &var_user_id
       group by a.borrid , a.year
     ) j,
     (
       select count(*) as cntFixed
         from mdl_parameter_tree u
         where u.prmid not in
        (
          select t.prmid
          from mdl_parameter_tree t
          where t.rootnode in
          (
           select b.rootnode
           from subjective_prm_trans a, mdl_parameter_tree b
           where
                a.mdlid = b.mdlid and a.prmid = b.prmid and
                a.endnodeflag = 'N' and a.value between 0.0001 and 1
                and a.prmid not in (select r.prmid from mdl_parameter_tree r
                  where trim(lower(r.prmname)) = 'project risk' and r.mdlid= &var_mdlid ) and
                a.borrid= &var_borrId and a.mdlid= &var_mdlid and
                a.user_id= &var_user_id ) and
          t.endnodeflag = 'E' and parametertype = 'S' and mdlid= &var_mdlid
        ) and
         u.endnodeflag ='E' and
         u.parametertype = 'S' and
         u.mdlid= &var_mdlid
     ) k
   ) om
   where
   os.borrid = om.borrid and
   os.year = om.year and
   os.cntActuals  = om.cntMdlTotals
 )
 order by year desc

Well below is the explain plan:

Execution Plan
----------------------------------------------------------
0     SELECT STATEMENT Optimizer=ALL_ROWS (Cost=28 Card=1 Bytes=61 )
1 0    SORT (ORDER BY) (Cost=28 Card=1 Bytes=61)
2 1     HASH JOIN (SEMI) (Cost=26 Card=1 Bytes=61)
3 2      TABLE ACCESS (FULL) OF 'STATEMENT' (Cost=2 Card=1 Bytes=48)
4 2      VIEW OF 'VW_NSO_1' (Cost=23 Card=1 Bytes=13)
5 4       NESTED LOOPS (Cost=23 Card=1 Bytes=91)
6 5        HASH JOIN (Cost=13 Card=1 Bytes=78)
7 6         VIEW (Cost=4 Card=1 Bytes=39)
8 7          SORT (GROUP BY) (Cost=4 Card=1 Bytes=55)
9 8           TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2 Card=1 Bytes=55)
10 6        VIEW (Cost=9 Card=1 Bytes=39)
11 10        SORT (GROUP BY) (Cost=7 Card=1 Bytes=107)
12 11         FILTER
13 12          HASH JOIN (Cost=5 Card=1 Bytes=107)
14 13           TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2 Card=1 Bytes=81)
15 13           TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=142 Bytes=3692)
16 12          TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=1 Bytes=155)
17 5       VIEW
18 17       SORT (AGGREGATE)
19 18        FILTER
20 19         TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=1 Bytes=284)
21 19         FILTER
22 21          NESTED LOOPS (Cost=6 Card=1 Bytes=636)
23 22           MERGE JOIN (CARTESIAN) (Cost=4 Card=1 By tes=481)
24 23            TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2 Card=1 Bytes=68)
25 23            BUFFER (SORT) (Cost=2 Card=1 Bytes=413)
26 25             TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=1 Bytes=413)
27 22           TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=18 Bytes=2790)
28 21          TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=1 Bytes=155)

 

Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
387229 consistent gets
306954 physical reads
0 redo size
432 bytes sent via SQL*Net to client
584 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
114 sorts (memory)
0 sorts (disk)
1 rows processed

Any help would help to resolve the issue

———-
David has already mentioned that your use of “and to_char(stmtdt, ‘yyyy’)” makes it impossible to use a regular index on the stmtdt column, if one exists.

Looking at the plan, you may notice that there is not a single index access, the cardinality numbers are low (possibily indicating that it has been a while since statistics were gathered), there is a merge join Cartesian which can kill performance with large numbers of rows, the formatting without spaces makes it impossible to see how the data was retrieved in order, and it appears that you are using a version of Oracle prior to 10g R1 based on the presence of “SORT (GROUP BY)”. That may be an indication that Oracle is converting or is not converting some/all of the NOT IN subqueries as necessary.

Looking at the query, slightly reformatted:

SELECT
  TO_CHAR(STMTDT, 'YYYY') AS ID, TO_CHAR(STMTDT, 'YYYY') AS YEAR
FROM
  STATEMENT
WHERE
  BORRID= &VAR_BORRID
  AND USER_ID= &VAR_USER_ID
  AND COAID = &VAR_MDLID
  AND TO_CHAR(STMTDT, 'YYYY') IN
  (
   SELECT
     OS.YEAR
   FROM
   (
     SELECT
       BORRID,
       YEAR,
       COUNT(*) AS CNTACTUALS
     FROM
       SUBJECTIVE_PRM_TRANS
     WHERE
       BORRID= &VAR_BORRID
       AND USER_ID= &VAR_USER_ID
       AND MDLID = &VAR_MDLID
       AND ENDNODEFLAG = 'E'
     GROUP BY
       YEAR,
       BORRID) OS,
   (
     SELECT
       J.BORRID,
       J.YEAR,
       J.CNTVARIABLE + K.CNTFIXED AS CNTMDLTOTALS
     FROM
     (
       SELECT
         A.BORRID,
         A.YEAR ,
         COUNT(*) AS CNTVARIABLE
       FROM
         SUBJECTIVE_PRM_TRANS A,
         MDL_PARAMETER_TREE M
       WHERE
         A.PRMID = M.PARENTID
         AND A.MDLID = M.MDLID
         AND A.ENDNODEFLAG = 'N'
         AND A.VALUE BETWEEN 0.0001 AND 1
         AND A.PRMID NOT IN
           (SELECT
             R.PRMID
           FROM
             MDL_PARAMETER_TREE R
           WHERE
             TRIM(LOWER(R.PRMNAME)) = 'PROJECT RISK'
             AND R.MDLID= &VAR_MDLID )
         AND A.BORRID= &VAR_BORRID
         AND A.MDLID= &VAR_MDLID
         AND A.USER_ID= &VAR_USER_ID
       GROUP BY
         A.BORRID ,
         A.YEAR ) J,
     (
       SELECT
         COUNT(*) AS CNTFIXED
       FROM
         MDL_PARAMETER_TREE U
       WHERE
         U.PRMID NOT IN
           (SELECT
             T.PRMID
           FROM
             MDL_PARAMETER_TREE T
           WHERE
             T.ROOTNODE IN
               (SELECT
                 B.ROOTNODE
               FROM
                 SUBJECTIVE_PRM_TRANS A,
                 MDL_PARAMETER_TREE B
               WHERE
                 A.MDLID = B.MDLID
                 AND A.PRMID = B.PRMID
                 AND A.ENDNODEFLAG = 'N' AND A.VALUE BETWEEN 0.0001 AND 1
                 AND A.PRMID NOT IN
                   (SELECT
                     R.PRMID
                   FROM
                     MDL_PARAMETER_TREE R
                   WHERE
                     TRIM(LOWER(R.PRMNAME)) = 'PROJECT RISK'
                    AND R.MDLID= &VAR_MDLID )
             AND A.BORRID= &VAR_BORRID
             AND A.MDLID= &VAR_MDLID
             AND A.USER_ID= &VAR_USER_ID
          )
         AND T.ENDNODEFLAG = 'E'
         AND PARAMETERTYPE = 'S'
         AND MDLID= &VAR_MDLID
        )
       AND U.ENDNODEFLAG ='E'
       AND U.PARAMETERTYPE = 'S'
       AND U.MDLID= &VAR_MDLID
     ) K
   ) OM
   WHERE
     OS.BORRID = OM.BORRID
     AND OS.YEAR = OM.YEAR
     AND OS.CNTACTUALS  = OM.CNTMDLTOTALS
 )
ORDER BY
  YEAR DESC

You seem to be repeating the same inline view SQL in a couple places, you seem to be performing a COUNT in two inline views so that you can perform “AND OS.CNTACTUALS  = OM.CNTMDLTOTALS” – there might be a better way.  Where you have “SELECT OS.YEAR” – you may want to convert this to 2 date columns with Jan 1 of the year in the first date column and Dec 31 of the year in the second – doing that would allow you to change “TO_CHAR(STMTDT, ‘YYYY’) IN” into a statement that does not require TO_CHAR.

It will take someone familiar with the data to tell you if some of the extra work in the inline views may be eliminated.

A follow-up email to the OP:
There are some basic problems that I found in the query and the explain plan that should be fixed before trying David’s suggestions for a materialed view.  I suggested the following:
* There are no indexes used in the query, which may be part or most of the performance problem – verify that indexes exist and statistics are gathered for those indexes.
* The cardinality numbers are low (possibily indicating that it has been a while since statistics were gathered) – make certain that statistics are gathered with DBMS_STATS.
* There is a merge join Cartesian in the plan which can severely hurt performance with large numbers of rows – you may need a hint to fix this.
* The formatting of the plan without spaces makes it impossible to see how the data was retrieved by the plan.
* You seem to be using a version of Oracle prior to 10g R1, and those versions may not convert/change/transform some/all of the NOT IN subqueries as necessary for best performance.
* You seem to be repeating the same inline view SQL in a couple places.
* You seem to be performing a COUNT in two inline views so that you can perform “AND OS.CNTACTUALS  = OM.CNTMDLTOTALS” – there might be a better way.
* Where you have “SELECT OS.YEAR” – you may want to convert this to 2 date columns with Jan 1 of the year in the first date column and Dec 31 of the year in the second – doing that would allow you to change “TO_CHAR(STMTDT, ‘YYYY’) IN” into a statement that does not require TO_CHAR.
* It will take someone familiar with the data to tell you if some of the extra work in the inline views may be eliminated.

SQL – Reformatting to Improve Performance 9

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

A couple months ago  an interesting/huge/confusing SQL statement appeared on the OTN forums that was experiencing performance problems when running on Oracle 8i.  The SQL statement contained several very complex inline view, with inline views nested inside other inline views:

http://forums.oracle.com/forums/thread.jspa?messageID=3802738

I offered the following suggestions to the person:

That is a long SQL statement. Splitting the SQL statement up into multiple views likely will not help performance, but could hurt performance.

A couple comments about the SQL statement:
* “SELECT DISTINCT” repeated in many places, including in an inline view using a GROUP BY clause. Are all of the DISTINCT clauses necessary?

* ORDER BY is frequently used in the inline views: “order by group_name” in the T11 inline view, “order by groupname” in the T6 inline view, “order by groupname” in the T3 inline view, “order by linecode” in the T5 inline view. Remove those ORDER BY clauses.

* “UNION” is used in T3 inline view – is it possible to replace that with a “UNION ALL”?

* IN clauses may be inefficient in Oracle 8i, consider switching to an EXISTS clause or transforming the IN clause to an inline view.

* Odd LIKE syntax in several places: where “groupname like ‘%’ and hub like ‘%’”, “lr.groupname like ‘%’” in T1 inline view, “lr.groupname like ‘%’” in T2 inline view.

* The table autoloadflow_lineresults is accessed many times – is that a large table? Is it possible to consolidate the queries accessing that table?

* The WHERE clauses seem to limit the usefulness of any indexes which may exist.

* It appears that you want the results from the T6 inline view (which join the T1, T2, T3, T4, and T5 inline views) to drive into the T7, T8, T9, T10, and T11 inline views – does the execution plan show that is happening?

SQL – Reformatting to Improve Performance 8

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/deb2e3f18be836c0

I am using Oracle 9i.
I have some reports developed in Reports 6i which run very slow.
When I run the query in TOAD, it’s taking too much time for displaying
result.
Can anybody suggest me how to tune this query ?

SELECT DISTINCT c.AccName, SUBSTR (a.VouNo, 3) VouNo,
                a.VouDate, a.TranType, b.ChequeNo,
                b.ChequeDate,
                DECODE (BalType, 'Debit', Amount, 0) Debit,
                DECODE (BalType, 'Credit', Amount, 0) Credit,
                d.DocNo, d.DocDate, a.Remark
           FROM TranDetails a,
                ChequeDetails b,
                AccMaster c,
                TranDocDetails d
          WHERE c.AccCode <> :p_BankAccCode
            AND a.VouDate BETWEEN :st_date AND :end_date
            AND a.CompanyNo = c.CompanyNo
            AND a.CompanyNo = b.CompanyNo(+)
            AND a.VouNo = b.VouNo(+)
            AND a.VouDate = b.VouDate(+)
            AND a.nu_serial_no = b.nu_serial_no(+)
            AND a.nu_serial_no = d.nu_serial_no(+)
            AND a.AccCode = c.AccCode
            AND a.TranType IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
            AND NVL (a.SecFlag, 'N') = 'Y'
            AND d.VouNo(+) = a.VouNo
            AND d.VouDate(+) = a.VouDate
            AND c.CompanyNo = :p_CompanyNo
            AND d.CompanyNo(+) = :p_CompanyNo
            AND    a.CompanyNo
                || a.VouNo
                || TO_CHAR (a.VouDate, 'dd-mm-yyyy') IN (
                   SELECT    d.CompanyNo
                          || d.VouNo
                          || TO_CHAR (d.VouDate, 'dd-mm-yyyy')
                     FROM TranDetails d
                    WHERE d.AccCode = :p_BankAccCode
                      AND NVL (d.SecFlag, 'N') = 'Y'
                      AND d.CompanyNo = :p_CompanyNo)
       ORDER BY 3, 2

The Oracle version number (for example 9.2.0.4) is important, as it determines what types of transformations Oracle may use to help improve the query performance.  First, a slight change in the formatting to make it easier for me to read (you will need to fix the DECODE statements):

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND TD.COMPANYNO || TD.VOUNO || TO_CHAR (TD.VOUDATE, 'DD-MM-YYYY') IN
    (SELECT
      TD2.COMPANYNO || TD2.VOUNO || TO_CHAR (TD2.VOUDATE, 'DD-MM-YYYY')
    FROM
      TRANDETAILS TD2
    WHERE
      TD2.ACCCODE = :V_BANKACCCODE
      AND NVL (TD2.SECFLAG, 'N') = 'Y'
      AND TD2.COMPANYNO = :V_COMPANYNO)
ORDER BY
  3,
  2;

In the above, you are concatenating three columns into a single value, and then trying to determine if the same concatenated value exists in a table.  A DBMS_XPLAN would probably show many, many full table scans of the TRANSDETAILS table.

If we just list the three columns, and use TRUNC rather than TO_CHAR, we may be able to take advantage of an index on those columns, and avoid the CPU overhead of a data type conversion of a DATE column to a character data type.  You could use an EXISTS syntax instead, which might be more efficient (not shown):

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND (TD.COMPANYNO,TD.VOUNO,TRUNC(TD.VOUDATE)) IN
    (SELECT
      TD2.COMPANYNO,
      TD2.VOUNO,
      TRUNC(TD2.VOUDATE)
    FROM
      TRANDETAILS TD2
    WHERE
      TD2.ACCCODE = :V_BANKACCCODE
      AND NVL (TD2.SECFLAG, 'N') = 'Y'
      AND TD2.COMPANYNO = :V_COMPANYNO)
ORDER BY
  3,
  2;

Let’s transform the subquery into an inline view, which will sometimes help improve performance (usually a very noticeable on Oracle 8i).  Such a transformation may be performed by Oracle automatically:

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD,
  (SELECT DISTINCT
    TD2.COMPANYNO,
    TD2.VOUNO,
    TRUNC(TD2.VOUDATE) VOUDATE
  FROM
    TRANDETAILS TD2
  WHERE
    TD2.ACCCODE = :V_BANKACCCODE
    AND NVL (TD2.SECFLAG, 'N') = 'Y'
    AND TD2.COMPANYNO = :V_COMPANYNO) TD2
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND TD.COMPANYNO=TD2.COMPANYNO
  AND TD.VOUNO=TD2.VOUNO
  AND TRUNC(TD.VOUDATE)=TD2.VOUDATE
ORDER BY
  3,
  2;

Is it really necessary to reference the TRANDETAILS table twice?  Will the following work?:

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND TD.ACCCODE = :V_BANKACCCODE
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TD.COMPANYNO = :V_COMPANYNO
ORDER BY
  3,
  2;

Why do you need to use the DISTINCT clause – is that a sign that you do not have sufficient joins between the tables?  It might be helpful to post a DBMS_XPLAN for the query.

SQL – Reformatting to Improve Performance 7

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/7780f1fd94c03ced

I have been challenged to optimize a stored procedure on Oracle 9i that will return a ref cursor with the counts of each status for each batch from the tables defined below.  I started with a View to join the tables together on the OrderNo field.  Then I wrote a query in the stored procdure that grouped the Batch values together, and then did a count for each Status value in each Batch like this:

SELECT Batch,
COUNT(Batch) Total,
(SELECT  COUNT(Batch)
        FROM MYVIEW
        WHERE Status = 1
                AND Batch = V1.Batch
(SELECT  COUNT(Batch)
        FROM MYVIEW
        WHERE Status = 2
                AND Batch = V1.Batch
(SELECT  COUNT(Batch)
        FROM MYVIEW
        WHERE Status = 3
                AND Batch = V1.Batch
FROM MYVIEW V1
WHERE Status < 4
        GROUP BY Batch;

With 42 unique Batch values and 26,000 OrderNo values my dev server takes over 5 seconds.  I built the query without the view and added the joins in the query and took the same amount of time.  I can’t change the table structures but the view is wide open. This is a conversion project from MS SQL and this same data returns from MS SQL in 0.09 seconds.  I hope Oracle can beat MS

Suporting Data and table structures.

TableOne Definition:

 
Batch - VarChar
OrderNo - VarChar
Various other Columns of Data...

TableTwo Definition:

OrderNo - VarChar
Status - Number
Various other Columns of Data...

View Definition:

Select o.Batch, o.OrderNo, t.Status, ...
FROM TableOne o Left Outer Join TableTwo t
                on o.OrderNo = t.OrderNo;

Sample Data TableOne:

ABC1    123     ...
ABC1  234       ...
ABC1    345     ...
ABC1  456       ...
ABC2    567     ...
ABC2  678       ...
ABC2    789     ...
ABC2  321       ...
ABC3    432     ...
ABC4  543       ...

Sample Data TableTwo:

123     1       ...
234     1       ...
345     2       ...
456     3       ...
567     2       ...
678     1       ...
789     2       ...
321     2       ...
432     3       ...
543     3       ...

Expected Return Ref Cursor:

ABC1    4       2       1       3
ABC2    4       1       3       null
ABC3    1       null    null    1
ABC4    1       null    1       null

Reduce it to a simple SQL statement using DECODE.  If the STATUS is the expected value (1, 2, 3) for the column, return 1, otherwise return NULL.  Then count the non-null return values.  COUNT will only count non-null values:

SELECT
  BATCH,
  COUNT(BATCH) TOTAL,
  COUNT(DECODE(STATUS,1,1,NULL)) TOTAL_1,
  COUNT(DECODE(STATUS,2,1,NULL)) TOTAL_2,
  COUNT(DECODE(STATUS,3,1,NULL)) TOTAL_3
FROM
  MYVIEW V1
WHERE
  STATUS < 4
GROUP BY
  BATCH;

It appears that if COUNT returns 0, that you want NULL to be returned rather than 0.  Once again, use DECODE to convert 0 to NULL and all other values to the original formula:

SELECT
  BATCH,
  COUNT(BATCH) TOTAL,
  DECODE(COUNT(DECODE(STATUS,1,1,NULL)),0,NULL,COUNT(DECODE(STATUS,1,1,NULL))) TOTAL_1,
  DECODE(COUNT(DECODE(STATUS,2,1,NULL)),0,NULL,COUNT(DECODE(STATUS,2,1,NULL))) TOTAL_2,
  DECODE(COUNT(DECODE(STATUS,3,1,NULL)),0,COUNT(DECODE(STATUS,3,1,NULL))) TOTAL_3
FROM
  MYVIEW V1
WHERE
  STATUS < 4
GROUP BY
  BATCH;

The inclusion of STATUS < 4 leads me to believe that you are attempting to reuse a view definition for a different task.  Doing so may lead to performance issues.  It may be a good idea to eliminate the view and directly reference the exact SQL statement of interest.

SQL – Reformatting to Improve Performance 6

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/c28103fe283b7551

I am learning SQL (trial by fire) and have a question regarding a query optimization. Lets say I have three tables ‘project’,  ‘notes’ & ‘reminder’. Key on ‘project’ is ‘proj_id’. The other two tables reference this key as ‘notes’ contains note entries on a given project and ‘reminder’ tracks todo type and due dates.

select count(a.proj_id)
from project a
where a.stat_cd = 'CLOSED'
and  exists
      (
      select b.proj_id
      from reminder b
      where b.rem_type = 'LAST'
      and a.proj_id = b.proj_id
      )
and exists
      (
      select c.proj_id
      from notes c
      where c.note_type = 'QA'
      and a.proj_id = c.proj_id
      )

I am trying to determine the number of projects that have a ‘CLOSED’ status, contain a reminder type of ‘LAST’ and has a note type of ‘QA’

I get the result I am looking for but these are very large tables and it takes over 30 minutes for this to run. Is there a better way to write this query? I’m betting there is. Thank you in advance for your advice.

Give this re-write a try to see if it improves performance – I have essentially moved the exists statements into two inline views (some versions of Oracle may automatically perform such transformations):

SELECT
  COUNT(A.PROJ_ID)
FROM
  PROJECT A,
  (SELECT DISTINCT
    B.PROJ_ID
  FROM
    REMINDER B
  WHERE
    B.REM_TYPE = 'LAST') B,
  (SELECT DISTINCT
    C.PROJ_ID
  FROM
    NOTES C
  WHERE
    C.NOTE_TYPE = 'QA') C
WHERE
  A.STAT_CD = 'CLOSED'
  AND A.PROJ_ID = B.PROJ_ID
  AND A.PROJ_ID = C.PROJ_ID;

Very likely, you will need an index on the PROJ_ID column for each table, especially if the REMINDER and NOTES tables contain columns that are wide.  An index on REMINDER.REM_TYPE might help.  An index on NOTES.NOTE_TYPE also might help.  Take a look at the explain plan (preferrably DBMS_XPLAN) for your query, and compare it to the explain plan for the one above.  Also, make certain that the tables and indexes are analyzed (use DBMS_STATS for Oracle 8i and above).

It is important to keep in mind that the two solutions provided may or may not yield the same results.  This will be evident in those cases where there is more than one row in REMINDER for a PROJ_ID WHERE REM_TYPE = ‘LAST’, and in cases where there is more than one row in NOTES for a PROJ_ID WHERE NOTE_TYPE = ‘QA’.

To work around the above issue, if it may occur, modify AlterEgo’s solution:

select count(a.projectid)

Can be modified as (assuming that PROJECTID is the primary key of the
PROJECT table):

SELECT
  COUNT(DISTINCT A.PROJECTID)

With the above change, you may find that AlterEgo’s SQL statement executes slightly faster than the solution that I provided (this may be Oracle version dependent).

One final note.  When building SQL statements with aliases, it is easier to troubleshoot problems with the SQL statements if the alias name is somehow related to the object name that it represents:
Rather than using:

  PROJECT A,
  REMINDER B,
  NOTES C

I would use:

  PROJECT P,
  REMINDER R,
  NOTES N

SQL – Reformatting to Improve Performance 5

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/97995c4a0e697539

Suppose I have a database table with 20 fields which are lookups to a single table.

configtable(configtable_id, a_field, something_lookup_id,
another_lookup_id, ...)
lookup(lookup_id, value, description, ...)

what is going to be faster to map the rows to an Object which needs the ‘value’ for every field ending in lookup_id

a) View

select c.configtable_id, l1.value as something_lookup, l2.value as
another_lookup
from configtable c,
       lookup l1,
       lookup l2
where c.something_lookup_id = l1.lookup_id
     and c.another_lookup_id = l2.lookup_id

foreach row
   map values to object
end

b) Cache all lookup values and populate

select c.* from configtable
foreach row
   map values to object
   if lookup_id
       find value from hashtable and map value to object
   endif
end

It seems that the latter *might* be better to scale outward better, as you could add application servers to do the caching/mapping and you only select from a single table?

——————————–
It might be a good idea to perform some testing with artifical data to see what happens when the size of the data set increases.  Performance wise, it is generally best to perform as much processing of data as possible on the database server, and return few result rows, compared to returning a large result set and performing the analysis outside the database.

To help you set up a test environment (T1 is your configtable):

CREATE TABLE T1(
  CONFIGTABLE_ID NUMBER(12),
  A_FIELD VARCHAR2(15),
  LOOKUP1 VARCHAR2(15),
  LOOKUP2 VARCHAR2(15),
  LOOKUP3 VARCHAR2(15),
  LOOKUP4 VARCHAR2(15),
  LOOKUP5 VARCHAR2(15),
  LOOKUP6 VARCHAR2(15),
  LOOKUP7 VARCHAR2(15),
  LOOKUP8 VARCHAR2(15),
  LOOKUP9 VARCHAR2(15),
  LOOKUP10 VARCHAR2(15),
  LOOKUP11 VARCHAR2(15),
  LOOKUP12 VARCHAR2(15),
  LOOKUP13 VARCHAR2(15),
  LOOKUP14 VARCHAR2(15),
  LOOKUP15 VARCHAR2(15),
  LOOKUP16 VARCHAR2(15),
  LOOKUP17 VARCHAR2(15),
  LOOKUP18 VARCHAR2(15),
  LOOKUP19 VARCHAR2(15),
  LOOKUP20 VARCHAR2(15),
  PRIMARY KEY(CONFIGTABLE_ID));

T2 is your lookup table:

CREATE TABLE T2(
  LOOKUP_ID VARCHAR2(15),
  VALUE NUMBER(12),
  DESCRIPTION VARCHAR2(30));

Note that I _forgot_ to declare a primary key on T2, so there is no index.

Now, let’s generate 10,000 rows of data in the T1 table, but don’t make the lookup columns too random – only three of the 15 characters in the lookup columns is permitted to be random:

INSERT INTO
  T1
SELECT
  ROWNUM CONFIGTABLE_ID,
  DBMS_RANDOM.STRING('A',15) A_FIELD,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP1,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP2,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP3,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP4,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP5,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP6,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP7,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP8,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP9,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP10,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP11,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP12,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP13,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP14,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP15,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP16,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP17,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP18,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP19,
  DBMS_RANDOM.STRING('A',3)||'ABCDEFGHIJKL' LOOKUP20
FROM
  DUAL
CONNECT BY
  LEVEL<=100000;

Let’s generate the data for the T2 table (your lookup table) using the distinct values in the 20 lookup columns of T1:

INSERT INTO
  T2
SELECT
  L LOOKUP_ID,
  TRUNC(DBMS_RANDOM.VALUE(1,100000)) VALUE,
  DBMS_RANDOM.STRING('A',25) DESCRIPTION
FROM
(SELECT
  LOOKUP1 L
FROM
  T1
UNION
SELECT
  LOOKUP2 L
FROM
  T1
UNION
SELECT
  LOOKUP3 L
FROM
  T1
UNION
SELECT
  LOOKUP4 L
FROM
  T1
UNION
SELECT
  LOOKUP5 L
FROM
  T1
UNION
SELECT
  LOOKUP6 L
FROM
  T1
UNION
SELECT
  LOOKUP7 L
FROM
  T1
UNION
SELECT
  LOOKUP8 L
FROM
  T1
UNION
SELECT
  LOOKUP9 L
FROM
  T1
UNION
SELECT
  LOOKUP10 L
FROM
  T1
UNION
SELECT
  LOOKUP11 L
FROM
  T1
UNION
SELECT
  LOOKUP12 L
FROM
  T1
UNION
SELECT
  LOOKUP13 L
FROM
  T1
UNION
SELECT
  LOOKUP14 L
FROM
  T1
UNION
SELECT
  LOOKUP15 L
FROM
  T1
UNION
SELECT
  LOOKUP16 L
FROM
  T1
UNION
SELECT
  LOOKUP17 L
FROM
  T1
UNION
SELECT
  LOOKUP18 L
FROM
  T1
UNION
SELECT
  LOOKUP19 L
FROM
  T1
UNION
SELECT
  LOOKUP20 L
FROM
  T1);
COMMIT;

Gather statistics on the two tables:

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER, TABNAME=>'T1',CASCADE=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER, TABNAME=>'T2',CASCADE=>TRUE);

SQL statement test 1, with 14 instances of the T2 lookup table:

SELECT
  C.CONFIGTABLE_ID,
  L1.VALUE L1_VALUE,
  L2.VALUE L2_VALUE,
  L3.VALUE L3_VALUE,
  L4.VALUE L4_VALUE,
  L5.VALUE L5_VALUE,
  L6.VALUE L6_VALUE,
  L7.VALUE L7_VALUE,
  L8.VALUE L8_VALUE,
  L9.VALUE L9_VALUE,
  L10.VALUE L10_VALUE,
  L11.VALUE L11_VALUE,
  L12.VALUE L12_VALUE,
  L13.VALUE L13_VALUE,
  L14.VALUE L14_VALUE
FROM
  T1 C,
  T2 L1,
  T2 L2,
  T2 L3,
  T2 L4,
  T2 L5,
  T2 L6,
  T2 L7,
  T2 L8,
  T2 L9,
  T2 L10,
  T2 L11,
  T2 L12,
  T2 L13,
  T2 L14
WHERE
  C.LOOKUP1=L1.LOOKUP_ID
  AND C.LOOKUP2=L2.LOOKUP_ID
  AND C.LOOKUP3=L3.LOOKUP_ID
  AND C.LOOKUP4=L4.LOOKUP_ID
  AND C.LOOKUP5=L5.LOOKUP_ID
  AND C.LOOKUP6=L6.LOOKUP_ID
  AND C.LOOKUP7=L7.LOOKUP_ID
  AND C.LOOKUP8=L8.LOOKUP_ID
  AND C.LOOKUP9=L9.LOOKUP_ID
  AND C.LOOKUP10=L10.LOOKUP_ID
  AND C.LOOKUP11=L11.LOOKUP_ID
  AND C.LOOKUP12=L12.LOOKUP_ID
  AND C.LOOKUP13=L13.LOOKUP_ID
  AND C.LOOKUP14=L14.LOOKUP_ID;

The DBMS Xplan for the above – took about 11.7 seconds:

---------------------------------------------------------------------------­-----------------------------------------------------------
| Id  | Operation                       | Name | Starts | E-Rows | A- Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------­-----------------------------------------------------------
|*  1 |  HASH JOIN                      |      |      1 |    100K|     100K|00:00:11.68 |   20806 |   5141 |  7744K|  2666K|     1/0/0|
|   2 |   TABLE ACCESS FULL             | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |    149 |       |       |          |
|*  3 |   HASH JOIN                     |      |      1 |    100K|     100K|00:00:10.95 |   19750 |   4992 |  7744K|  2666K|     1/0/0|
|   4 |    TABLE ACCESS FULL            | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|*  5 |    HASH JOIN                    |      |      1 |    100K|     100K|00:00:10.21 |   18694 |   4992 |  7744K|  2666K|     1/0/0|
|   6 |     TABLE ACCESS FULL           | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|*  7 |     HASH JOIN                   |      |      1 |    100K|     100K|00:00:09.57 |   17638 |   4992 |  7744K|  2666K|     1/0/0|
|   8 |      TABLE ACCESS FULL          | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|*  9 |      HASH JOIN                  |      |      1 |    100K|     100K|00:00:08.83 |   16582 |   4992 |  7744K|  2666K|     1/0/0|
|  10 |       TABLE ACCESS FULL         | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 11 |       HASH JOIN                 |      |      1 |    100K|     100K|00:00:08.16 |   15526 |   4992 |  7744K|  2666K|     1/0/0|
|  12 |        TABLE ACCESS FULL        | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 13 |        HASH JOIN                |      |      1 |    100K|     100K|00:00:07.43 |   14470 |   4992 |  7744K|  2666K|     1/0/0|
|  14 |         TABLE ACCESS FULL       | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 15 |         HASH JOIN               |      |      1 |    100K|     100K|00:00:06.78 |   13414 |   4992 |  7744K|  2666K|     1/0/0|
|  16 |          TABLE ACCESS FULL      | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 17 |          HASH JOIN              |      |      1 |    100K|     100K|00:00:06.05 |   12358 |   4992 |  7744K|  2666K|     1/0/0|
|  18 |           TABLE ACCESS FULL     | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 19 |           HASH JOIN             |      |      1 |    100K|     100K|00:00:05.40 |   11302 |   4992 |  7744K|  2666K|     1/0/0|
|  20 |            TABLE ACCESS FULL    | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 21 |            HASH JOIN            |      |      1 |    100K|     100K|00:00:04.65 |   10246 |   4992 |  7744K|  2666K|     1/0/0|
|  22 |             TABLE ACCESS FULL   | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 23 |             HASH JOIN           |      |      1 |    100K|     100K|00:00:04.01 |    9190 |   4992 |  7744K|  2666K|     1/0/0|
|  24 |              TABLE ACCESS FULL  | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 25 |              HASH JOIN          |      |      1 |    100K|     100K|00:00:03.28 |    8134 |   4992 |  7744K|  2666K|     1/0/0|
|  26 |               TABLE ACCESS FULL | T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|* 27 |               HASH JOIN         |      |      1 |    100K|     100K|00:00:02.64 |    7078 |   4992 |  7744K|  2666K|     1/0/0|
|  28 |                TABLE ACCESS FULL| T2   |      1 |    140K|     140K|00:00:00.01 |    1056 |      0 |       |       |          |
|  29 |                TABLE ACCESS FULL| T1   |      1 |    100K|     100K|00:00:01.90 |    6022 |   4992 |       |       |          |
---------------------------------------------------------------------------­-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."LOOKUP14"="L14"."LOOKUP_ID")
   3 - access("C"."LOOKUP13"="L13"."LOOKUP_ID")
   5 - access("C"."LOOKUP12"="L12"."LOOKUP_ID")
   7 - access("C"."LOOKUP11"="L11"."LOOKUP_ID")
   9 - access("C"."LOOKUP10"="L10"."LOOKUP_ID")
  11 - access("C"."LOOKUP9"="L9"."LOOKUP_ID")
  13 - access("C"."LOOKUP8"="L8"."LOOKUP_ID")
  15 - access("C"."LOOKUP7"="L7"."LOOKUP_ID")
  17 - access("C"."LOOKUP6"="L6"."LOOKUP_ID")
  19 - access("C"."LOOKUP5"="L5"."LOOKUP_ID")
  21 - access("C"."LOOKUP4"="L4"."LOOKUP_ID")
  23 - access("C"."LOOKUP3"="L3"."LOOKUP_ID")
  25 - access("C"."LOOKUP2"="L2"."LOOKUP_ID")
  27 - access("C"."LOOKUP1"="L1"."LOOKUP_ID")

Let’s try again, this time with one instance of the T2 lookup table
listed in the SQL statement:

SELECT
  C.CONFIGTABLE_ID,
  MAX(DECODE(C.LOOKUP1,L1.LOOKUP_ID,L1.VALUE,NULL)) L1_VALUE,
  MAX(DECODE(C.LOOKUP2,L1.LOOKUP_ID,L1.VALUE,NULL)) L2_VALUE,
  MAX(DECODE(C.LOOKUP3,L1.LOOKUP_ID,L1.VALUE,NULL)) L3_VALUE,
  MAX(DECODE(C.LOOKUP4,L1.LOOKUP_ID,L1.VALUE,NULL)) L4_VALUE,
  MAX(DECODE(C.LOOKUP5,L1.LOOKUP_ID,L1.VALUE,NULL)) L5_VALUE,
  MAX(DECODE(C.LOOKUP6,L1.LOOKUP_ID,L1.VALUE,NULL)) L6_VALUE,
  MAX(DECODE(C.LOOKUP7,L1.LOOKUP_ID,L1.VALUE,NULL)) L7_VALUE,
  MAX(DECODE(C.LOOKUP8,L1.LOOKUP_ID,L1.VALUE,NULL)) L8_VALUE,
  MAX(DECODE(C.LOOKUP9,L1.LOOKUP_ID,L1.VALUE,NULL)) L9_VALUE,
  MAX(DECODE(C.LOOKUP10,L1.LOOKUP_ID,L1.VALUE,NULL)) L10_VALUE,
  MAX(DECODE(C.LOOKUP11,L1.LOOKUP_ID,L1.VALUE,NULL)) L11_VALUE,
  MAX(DECODE(C.LOOKUP12,L1.LOOKUP_ID,L1.VALUE,NULL)) L12_VALUE,
  MAX(DECODE(C.LOOKUP13,L1.LOOKUP_ID,L1.VALUE,NULL)) L13_VALUE,
  MAX(DECODE(C.LOOKUP14,L1.LOOKUP_ID,L1.VALUE,NULL)) L14_VALUE
FROM
  T1 C,
  T2 L1
WHERE
  C.LOOKUP1=L1.LOOKUP_ID
  OR C.LOOKUP2=L1.LOOKUP_ID
  OR C.LOOKUP3=L1.LOOKUP_ID
  OR C.LOOKUP4=L1.LOOKUP_ID
  OR C.LOOKUP5=L1.LOOKUP_ID
  OR C.LOOKUP6=L1.LOOKUP_ID
  OR C.LOOKUP7=L1.LOOKUP_ID
  OR C.LOOKUP8=L1.LOOKUP_ID
  OR C.LOOKUP9=L1.LOOKUP_ID
  OR C.LOOKUP10=L1.LOOKUP_ID
  OR C.LOOKUP11=L1.LOOKUP_ID
  OR C.LOOKUP12=L1.LOOKUP_ID
  OR C.LOOKUP13=L1.LOOKUP_ID
  OR C.LOOKUP14=L1.LOOKUP_ID
GROUP BY
  C.CONFIGTABLE_ID;

The DBMS Xplan, 21.3 seconds once the GROUP BY completed:

---------------------------------------------------------------------------­---------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A- Time   | Buffers | Reads  | Writes |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------­---------------------------------------------------------
|   1 |  HASH GROUP BY       |      |      1 |  98476 |    100K| 00:00:21.31 |   85162 |  74613 |   4402 |       |       |          |
|   2 |   CONCATENATION      |      |      1 |        |   1399K| 00:00:14.33 |   85162 |  70211 |      0 |       |       |          |
|*  3 |    HASH JOIN         |      |      1 |    100K|    100K| 00:00:00.84 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|   4 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|   5 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|*  6 |    HASH JOIN         |      |      1 |   5000 |  99998 | 00:00:01.00 |    6083 |   5015 |      0 |  7744K|  2666K|     1/0/0|
|   7 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|   8 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5015 |      0 |       |       |          |
|*  9 |    HASH JOIN         |      |      1 |    250 |  99998 | 00:00:00.93 |    6083 |   5015 |      0 |  7744K|  2666K|     1/0/0|
|  10 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  11 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.30 |    5027 |   5015 |      0 |       |       |          |
|* 12 |    HASH JOIN         |      |      1 |     13 |  99997 | 00:00:00.93 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  13 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  14 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|* 15 |    HASH JOIN         |      |      1 |      1 |  99999 | 00:00:00.94 |    6083 |   5013 |      0 |  7744K|  2666K|     1/0/0|
|  16 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  17 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5013 |      0 |       |       |          |
|* 18 |    HASH JOIN         |      |      1 |      1 |  99994 | 00:00:00.97 |    6083 |   5015 |      0 |  7744K|  2666K|     1/0/0|
|  19 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  20 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5015 |      0 |       |       |          |
|* 21 |    HASH JOIN         |      |      1 |      1 |  99993 | 00:00:00.99 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  22 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  23 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|* 24 |    HASH JOIN         |      |      1 |      1 |  99995 | 00:00:00.95 |    6083 |   5014 |      0 |  7744K|  2666K|     1/0/0|
|  25 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  26 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5014 |      0 |       |       |          |
|* 27 |    HASH JOIN         |      |      1 |      1 |  99988 | 00:00:00.97 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  28 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  29 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|* 30 |    HASH JOIN         |      |      1 |      1 |  99994 | 00:00:00.97 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  31 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  32 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.30 |    5027 |   5016 |      0 |       |       |          |
|* 33 |    HASH JOIN         |      |      1 |      1 |  99988 | 00:00:01.09 |    6083 |   5015 |      0 |  7744K|  2666K|     1/0/0|
|  34 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  35 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.30 |    5027 |   5015 |      0 |       |       |          |
|* 36 |    HASH JOIN         |      |      1 |      1 |  99991 | 00:00:00.96 |    6083 |   5016 |      0 |  7744K|  2666K|     1/0/0|
|  37 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  38 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5016 |      0 |       |       |          |
|* 39 |    HASH JOIN         |      |      1 |      1 |  99988 | 00:00:00.93 |    6083 |   5014 |      0 |  7744K|  2666K|     1/0/0|
|  40 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  41 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5014 |      0 |       |       |          |
|* 42 |    HASH JOIN         |      |      1 |      1 |  99997 | 00:00:00.98 |    6083 |   5014 |      0 |  7744K|  2666K|     1/0/0|
|  43 |     TABLE ACCESS FULL| T2   |      1 |    140K|    140K| 00:00:00.01 |    1056 |      0 |      0 |       |       |          |
|  44 |     TABLE ACCESS FULL| T1   |      1 |    100K|    100K| 00:00:00.20 |    5027 |   5014 |      0 |       |       |          |
---------------------------------------------------------------------------­---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("C"."LOOKUP14"="L1"."LOOKUP_ID")
   6 - access("C"."LOOKUP13"="L1"."LOOKUP_ID")
       filter(LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID"))
   9 - access("C"."LOOKUP12"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  12 - access("C"."LOOKUP11"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  15 - access("C"."LOOKUP10"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  18 - access("C"."LOOKUP9"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  21 - access("C"."LOOKUP8"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  24 - access("C"."LOOKUP7"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  27 - access("C"."LOOKUP6"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  30 - access("C"."LOOKUP5"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  33 - access("C"."LOOKUP4"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP5"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  36 - access("C"."LOOKUP3"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP4"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP5"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  39 - access("C"."LOOKUP2"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP3"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP4"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP5"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))
  42 - access("C"."LOOKUP1"="L1"."LOOKUP_ID")
       filter((LNNVL("C"."LOOKUP2"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP3"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP4"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP5"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP6"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP7"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP8"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP9"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP10"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP11"="L1"."LOOKUP_ID")
              AND LNNVL("C"."LOOKUP12"="L1"."LOOKUP_ID") AND LNNVL("C"."LOOKUP13"="L1"."LOOKUP_ID") AND
              LNNVL("C"."LOOKUP14"="L1"."LOOKUP_ID")))

OK, so we tried to be clever and it took twice as long.

Third try:

SELECT
  C.CONFIGTABLE_ID,
  MAX(DECODE(C.LOOKUP1,L1.LOOKUP_ID,L1.VALUE,NULL)) L1_VALUE,
  MAX(DECODE(C.LOOKUP2,L1.LOOKUP_ID,L1.VALUE,NULL)) L2_VALUE,
  MAX(DECODE(C.LOOKUP3,L1.LOOKUP_ID,L1.VALUE,NULL)) L3_VALUE,
  MAX(DECODE(C.LOOKUP4,L1.LOOKUP_ID,L1.VALUE,NULL)) L4_VALUE,
  MAX(DECODE(C.LOOKUP5,L1.LOOKUP_ID,L1.VALUE,NULL)) L5_VALUE,
  MAX(DECODE(C.LOOKUP6,L1.LOOKUP_ID,L1.VALUE,NULL)) L6_VALUE,
  MAX(DECODE(C.LOOKUP7,L1.LOOKUP_ID,L1.VALUE,NULL)) L7_VALUE,
  MAX(DECODE(C.LOOKUP8,L1.LOOKUP_ID,L1.VALUE,NULL)) L8_VALUE,
  MAX(DECODE(C.LOOKUP9,L1.LOOKUP_ID,L1.VALUE,NULL)) L9_VALUE,
  MAX(DECODE(C.LOOKUP10,L1.LOOKUP_ID,L1.VALUE,NULL)) L10_VALUE,
  MAX(DECODE(C.LOOKUP11,L1.LOOKUP_ID,L1.VALUE,NULL)) L11_VALUE,
  MAX(DECODE(C.LOOKUP12,L1.LOOKUP_ID,L1.VALUE,NULL)) L12_VALUE,
  MAX(DECODE(C.LOOKUP13,L1.LOOKUP_ID,L1.VALUE,NULL)) L13_VALUE,
  MAX(DECODE(C.LOOKUP14,L1.LOOKUP_ID,L1.VALUE,NULL)) L14_VALUE
FROM
  T1 C,
  T2 L1
WHERE
  DECODE(L1.LOOKUP_ID,C.LOOKUP1,1,
    C.LOOKUP2,1,
    C.LOOKUP3,1,
    C.LOOKUP4,1,
    C.LOOKUP5,1,
    C.LOOKUP6,1,
    C.LOOKUP7,1,
    C.LOOKUP8,1,
    C.LOOKUP9,1,
    C.LOOKUP10,1,
    C.LOOKUP11,1,
    C.LOOKUP12,1,
    C.LOOKUP13,1,
    C.LOOKUP14,1,0)=1
GROUP BY
  C.CONFIGTABLE_ID;

This one took more than two minutes (I killed it at that point).

Let’s try a full Cartesian join just to kill the database server:

SELECT /*+ ORDERED */
  C.CONFIGTABLE_ID,
  MAX(DECODE(C.LOOKUP1,L1.LOOKUP_ID,L1.VALUE,NULL)) L1_VALUE,
  MAX(DECODE(C.LOOKUP2,L1.LOOKUP_ID,L1.VALUE,NULL)) L2_VALUE,
  MAX(DECODE(C.LOOKUP3,L1.LOOKUP_ID,L1.VALUE,NULL)) L3_VALUE,
  MAX(DECODE(C.LOOKUP4,L1.LOOKUP_ID,L1.VALUE,NULL)) L4_VALUE,
  MAX(DECODE(C.LOOKUP5,L1.LOOKUP_ID,L1.VALUE,NULL)) L5_VALUE,
  MAX(DECODE(C.LOOKUP6,L1.LOOKUP_ID,L1.VALUE,NULL)) L6_VALUE,
  MAX(DECODE(C.LOOKUP7,L1.LOOKUP_ID,L1.VALUE,NULL)) L7_VALUE,
  MAX(DECODE(C.LOOKUP8,L1.LOOKUP_ID,L1.VALUE,NULL)) L8_VALUE,
  MAX(DECODE(C.LOOKUP9,L1.LOOKUP_ID,L1.VALUE,NULL)) L9_VALUE,
  MAX(DECODE(C.LOOKUP10,L1.LOOKUP_ID,L1.VALUE,NULL)) L10_VALUE,
  MAX(DECODE(C.LOOKUP11,L1.LOOKUP_ID,L1.VALUE,NULL)) L11_VALUE,
  MAX(DECODE(C.LOOKUP12,L1.LOOKUP_ID,L1.VALUE,NULL)) L12_VALUE,
  MAX(DECODE(C.LOOKUP13,L1.LOOKUP_ID,L1.VALUE,NULL)) L13_VALUE,
  MAX(DECODE(C.LOOKUP14,L1.LOOKUP_ID,L1.VALUE,NULL)) L14_VALUE
FROM
  (SELECT
    C.CONFIGTABLE_ID,
    C.LOOKUP1,
    C.LOOKUP2,
    C.LOOKUP3,
    C.LOOKUP4,
    C.LOOKUP5,
    C.LOOKUP6,
    C.LOOKUP7,
    C.LOOKUP8,
    C.LOOKUP9,
    C.LOOKUP10,
    C.LOOKUP11,
    C.LOOKUP12,
    C.LOOKUP13,
    C.LOOKUP14
  FROM
    T1 C) C,
  T2 L1
GROUP BY
  C.CONFIGTABLE_ID;

This one took more than two minutes (I killed it at that point).

Let’s try the second method again, hiding optimizations from Oracle, prohibiting the CONCAT/UNION ALL optimization and a couple other optimization possibilies:

SELECT /*+ NO_QUERY_TRANSFORMATION */
  C.CONFIGTABLE_ID,
  MAX(DECODE(C.LOOKUP1,L1.LOOKUP_ID,L1.VALUE,NULL)) L1_VALUE,
  MAX(DECODE(C.LOOKUP2,L1.LOOKUP_ID,L1.VALUE,NULL)) L2_VALUE,
  MAX(DECODE(C.LOOKUP3,L1.LOOKUP_ID,L1.VALUE,NULL)) L3_VALUE,
  MAX(DECODE(C.LOOKUP4,L1.LOOKUP_ID,L1.VALUE,NULL)) L4_VALUE,
  MAX(DECODE(C.LOOKUP5,L1.LOOKUP_ID,L1.VALUE,NULL)) L5_VALUE,
  MAX(DECODE(C.LOOKUP6,L1.LOOKUP_ID,L1.VALUE,NULL)) L6_VALUE,
  MAX(DECODE(C.LOOKUP7,L1.LOOKUP_ID,L1.VALUE,NULL)) L7_VALUE,
  MAX(DECODE(C.LOOKUP8,L1.LOOKUP_ID,L1.VALUE,NULL)) L8_VALUE,
  MAX(DECODE(C.LOOKUP9,L1.LOOKUP_ID,L1.VALUE,NULL)) L9_VALUE,
  MAX(DECODE(C.LOOKUP10,L1.LOOKUP_ID,L1.VALUE,NULL)) L10_VALUE,
  MAX(DECODE(C.LOOKUP11,L1.LOOKUP_ID,L1.VALUE,NULL)) L11_VALUE,
  MAX(DECODE(C.LOOKUP12,L1.LOOKUP_ID,L1.VALUE,NULL)) L12_VALUE,
  MAX(DECODE(C.LOOKUP13,L1.LOOKUP_ID,L1.VALUE,NULL)) L13_VALUE,
  MAX(DECODE(C.LOOKUP14,L1.LOOKUP_ID,L1.VALUE,NULL)) L14_VALUE
FROM
  T1 C,
  T2 L1
WHERE
  C.LOOKUP1=L1.LOOKUP_ID
  OR C.LOOKUP2=L1.LOOKUP_ID
  OR C.LOOKUP3=L1.LOOKUP_ID
  OR C.LOOKUP4=L1.LOOKUP_ID
  OR C.LOOKUP5=L1.LOOKUP_ID
  OR C.LOOKUP6=L1.LOOKUP_ID
  OR C.LOOKUP7=L1.LOOKUP_ID
  OR C.LOOKUP8=L1.LOOKUP_ID
  OR C.LOOKUP9=L1.LOOKUP_ID
  OR C.LOOKUP10=L1.LOOKUP_ID
  OR C.LOOKUP11=L1.LOOKUP_ID
  OR C.LOOKUP12=L1.LOOKUP_ID
  OR C.LOOKUP13=L1.LOOKUP_ID
  OR C.LOOKUP14=L1.LOOKUP_ID
GROUP BY
  C.CONFIGTABLE_ID;

This one took more than five minutes (I killed it at that point).

Maybe the fact that we forgot to put an index on the T2 table is causing the performance problem – all of those full tablescans can’t be good:

ALTER TABLE T2 ADD
  PRIMARY KEY (LOOKUP_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T2';

Now we have an index named SYS_C0022342.

Analyze table T2 and its index again:

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER, TABNAME=>'T2', CASCADE=>TRUE);

Execute the first query again, and the same plan was generated.

Force the use of the index on five of the joins, and try a nested loop on one of the joins – took about the same amount of time as the first attempt:

SELECT /*+ INDEX(L1 SYS_C0022342) INDEX(L2 SYS_C0022342) INDEX(L3 SYS_C0022342) INDEX(L4 SYS_C0022342) INDEX(L5 SYS_C0022342) USE_NL(C L1) */
  C.CONFIGTABLE_ID,
  L1.VALUE L1_VALUE,
  L2.VALUE L2_VALUE,
  L3.VALUE L3_VALUE,
  L4.VALUE L4_VALUE,
  L5.VALUE L5_VALUE,
  L6.VALUE L6_VALUE,
  L7.VALUE L7_VALUE,
  L8.VALUE L8_VALUE,
  L9.VALUE L9_VALUE,
  L10.VALUE L10_VALUE,
  L11.VALUE L11_VALUE,
  L12.VALUE L12_VALUE,
  L13.VALUE L13_VALUE,
  L14.VALUE L14_VALUE
FROM
  T1 C,
  T2 L1,
  T2 L2,
  T2 L3,
  T2 L4,
  T2 L5,
  T2 L6,
  T2 L7,
  T2 L8,
  T2 L9,
  T2 L10,
  T2 L11,
  T2 L12,
  T2 L13,
  T2 L14
WHERE
  C.LOOKUP1=L1.LOOKUP_ID
  AND C.LOOKUP2=L2.LOOKUP_ID
  AND C.LOOKUP3=L3.LOOKUP_ID
  AND C.LOOKUP4=L4.LOOKUP_ID
  AND C.LOOKUP5=L5.LOOKUP_ID
  AND C.LOOKUP6=L6.LOOKUP_ID
  AND C.LOOKUP7=L7.LOOKUP_ID
  AND C.LOOKUP8=L8.LOOKUP_ID
  AND C.LOOKUP9=L9.LOOKUP_ID
  AND C.LOOKUP10=L10.LOOKUP_ID
  AND C.LOOKUP11=L11.LOOKUP_ID
  AND C.LOOKUP12=L12.LOOKUP_ID
  AND C.LOOKUP13=L13.LOOKUP_ID
  AND C.LOOKUP14=L14.LOOKUP_ID;

------------------------------------------------------------------------­---------------------------------------------------------------------------­--
| Id  | Operation                                 | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem | 1Mem |  O/1/M   |
---------------------------------------------------- -----------------------­-----------------------------------------------------------------------­--
|*  1 |  HASH JOIN                                |              |      1 |    131K|    100K|00:00:12.98 |     222K|   4967 |  7744K| 2666K|     1/0/0|
|   2 |   TABLE ACCESS FULL                       | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|*  3 |   HASH JOIN                               |              |      1 |    129K|    100K|00:00:12.25 |     221K|   4967 |  7744K| 2666K|     1/0/0|
|   4 |    TABLE ACCESS FULL                      | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|*  5 |    HASH JOIN                              |              |      1 |    126K|    100K|00:00:11.62 |     220K|   4967 |  7744K| 2666K|     1/0/0|
|   6 |     TABLE ACCESS FULL                     | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|*  7 |     HASH JOIN                             |              |      1 |    124K|    100K|00:00:10.99 |     219K|   4967 |  7744K| 2666K|     1/0/0|
|   8 |      TABLE ACCESS FULL                    | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|*  9 |      HASH JOIN                            |              |      1 |    121K|    100K|00:00:10.27 |     218K|   4967 |  7744K| 2666K|     1/0/0|
|  10 |       TABLE ACCESS FULL                   | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 11 |       HASH JOIN                           |              |      1 |    119K|    100K|00:00:09.64 |     217K|   4967 |  7744K| 2666K|     1/0/0|
|  12 |        TABLE ACCESS FULL                  | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 13 |        HASH JOIN                          |              |      1 |    117K|    100K|00:00:09.01 |     216K|   4967 |  7744K| 2666K|     1/0/0|
|  14 |         TABLE ACCESS FULL                 | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 15 |         HASH JOIN                         |              |      1 |    114K|    100K|00:00:08.29 |     215K|   4967 |  7744K| 2666K|     1/0/0|
|  16 |          TABLE ACCESS FULL                | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 17 |          HASH JOIN                        |              |      1 |    112K|    100K|00:00:07.66 |     214K|   4967 |  7744K| 2666K|     1/0/0|
|  18 |           TABLE ACCESS FULL               | T2           |      1 |    140K|    140K|00:00:00.01 |    1056 |      0 |       |      |          |
|* 19 |           HASH JOIN                       |              |      1 |    110K|    100K|00:00:07.03 |     213K|   4967 |  7744K| 2666K|     1/0/0|
|  20 |            TABLE ACCESS BY INDEX ROWID    | T2           |      1 |    140K|    140K|00:00:00.42 |    1520 |      0 |       |      |          |
|  21 |             INDEX FULL SCAN               | SYS_C0022342 |      1 |    140K|    140K|00:00:00.08 |     511 |      0 |       |      |          |
|* 22 |            HASH JOIN                      |              |      1 |    108K|    100K|00:00:05.87 |     211K|   4967 |  7744K| 2666K|     1/0/0|
|  23 |             TABLE ACCESS BY INDEX ROWID   | T2           |      1 |    140K|    140K|00:00:00.42 |    1520 |      0 |       |      |          |
|  24 |              INDEX FULL SCAN              | SYS_C0022342 |      1 |    140K|    140K|00:00:00.08 |     511 |      0 |       |      |          |
|* 25 |             HASH JOIN                     |              |      1 |    106K|    100K|00:00:04.82 |     210K|   4967 |  7744K| 2666K|     1/0/0|
|  26 |              TABLE ACCESS BY INDEX ROWID  | T2           |      1 |    140K|    140K|00:00:00.42 |    1520 |      0 |       |      |          |
|  27 |               INDEX FULL SCAN             | SYS_C0022342 |      1 |    140K|    140K|00:00:00.08 |     511 |      0 |       |      |          |
|* 28 |              HASH JOIN                    |              |      1 |    104K|    100K|00:00:03.76 |     208K|   4967 |  7744K| 2666K|     1/0/0|
|  29 |               TABLE ACCESS BY INDEX ROWID | T2           |      1 |    140K|    140K|00:00:00.42 |    1520 |      0 |       |      |          |
|  30 |                INDEX FULL SCAN            | SYS_C0022342 |      1 |    140K|    140K|00:00:00.08 |     511 |      0 |       |      |          |
|  31 |               NESTED LOOPS                |              |      1 |    101K|    100K|00:00:02.60 |     207K|   4967 |       |      |          |
|  32 |                TABLE ACCESS FULL          | T1           |      1 |    100K|    100K|00:00:01.00 |    6022 |   4967 |       |      |          |
|  33 |                TABLE ACCESS BY INDEX ROWID| T2           |    100K|      1 |    100K|00:00:01.35 |     201K|      0 |       |      |          |
|* 34 |                 INDEX UNIQUE SCAN         | SYS_C0022342 |    100K|      1 |    100K|00:00:00.73 |     101K|      0 |       |      |          |
---------------------------------------------------------------------------­------------------------------------------------------------------------­--
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C"."LOOKUP14"="L14"."LOOKUP_ID")
   3 - access("C"."LOOKUP13"="L13"."LOOKUP_ID")
   5 - access("C"."LOOKUP12"="L12"."LOOKUP_ID")
   7 - access("C"."LOOKUP11"="L11"."LOOKUP_ID")
   9 - access("C"."LOOKUP10"="L10"."LOOKUP_ID")
  11 - access("C"."LOOKUP9"="L9"."LOOKUP_ID")
  13 - access("C"."LOOKUP8"="L8"."LOOKUP_ID")
  15 - access("C"."LOOKUP7"="L7"."LOOKUP_ID")
  17 - access("C"."LOOKUP6"="L6"."LOOKUP_ID")
  19 - access("C"."LOOKUP5"="L5"."LOOKUP_ID")
  22 - access("C"."LOOKUP4"="L4"."LOOKUP_ID")
  25 - access("C"."LOOKUP3"="L3"."LOOKUP_ID")
  28 - access("C"."LOOKUP2"="L2"."LOOKUP_ID")
  34 - access("C"."LOOKUP1"="L1"."LOOKUP_ID")

The short answer to the above demonstration is that the structure of the SQL statement makes a big difference in the execution time. Hiding information, possibily in views, retricts Oracle’s options when trying to determine the optimal execution plan – and disabling the options has a very negative impact on execution efficiency.  Your experience with your actual data set may be very different.
——————————–

SQL – Reformatting to Improve Performance 4

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Note: the Oracle cost-based query optimizer is able to automatically transform queries submitted by applications to produce an equivalent SQL statement that will hopefully improve SQL execution performance. The possible transformations are limited by the Oracle release version, the value of the OPTIMIZER_FEATURES_ENABLE parameter (and various other hidden parameters), the potential presence of NULL values, and hints embedded in the SQL statement.

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/25ee58d1a12e1fd1

(The thread also contains an interesting discussion about the effects of over-indexing tables.)

I have the following two select statements in some code I’ve been looking at.  I don’t have access to a computer with Oracle on it currently so I can’t determine this myself.

Both selects do the same thing.  The function in the second version simply checks the condition what is in the first and send back a ‘Y’ or ‘N’ whereas the first has the check in the where clause itself.

My question is this:  is any one of the two selects more efficient (or less efficient) than the other?

SELECT a.mbr_dob,
       a.mbr_first_nm,
       a.mbr_gender,
       b.mbr_key,
       a.mbr_last_nm,
       b.mbr_sys_key,
       b.mbr_updated_dt,
       a.mbr_x_fst_nm,
       a.mbr_x_lst_nm,
       a.person_id,
       a.z_person_id
  FROM mbr_person a,
       mbr_system b
 WHERE a.person_id = b.person_id
   AND a.deactivate = 0
   AND b.deactivate = 0
   AND (
             a.mbr_last_nm NOT IN ( 'DATA','CORRECTION' )
         AND a.mbr_first_nm NOT IN ( 'DATA','CORRECTION' )
        );

 

SELECT a.mbr_dob,
       a.mbr_first_nm,
       a.mbr_gender,
       b.mbr_key,
       a.mbr_last_nm,
       b.mbr_sys_key,
       b.mbr_updated_dt,
       a.mbr_x_fst_nm,
       a.mbr_x_lst_nm,
       a.person_id,
       a.z_person_id
  FROM mbr_person a,
       mbr_system b
 WHERE a.person_id = b.person_id
   AND a.deactivate = 0
   AND b.deactivate = 0
   AND not_data_correction( a.mbr_last_nm, a.mbr_first_nm ) = 'Y';

——————————–

I would caution against creating too many indexes, as this will likely negatively impact performance of other parts of the system while offering minimal assistance to this SQL statement.  Histograms could be important on the columns MBR_PERSON.DEACTIVATE, MBR_SYSTEM.DEACTIVATE, MBR_PERSON.MBR_LAST_NM, and MBR_PERSON.MBR_FIRST_NM.  The histograms will give Oracle’s cost based optimizer a better picture of the contents of the columns, rather than assuming an even spread of the data values between the min and max for the column.

Keep in mind that Oracle may transform your SQL statement into another equivalent form, and may use constraints and transitive closure to generate additional predicates (think entries in the WHERE) clause for the SQL statement.  You may want to perform timing with alternate SQL syntax:

SELECT a.mbr_dob,
       a.mbr_first_nm,
       a.mbr_gender,
       b.mbr_key,
       a.mbr_last_nm,
       b.mbr_sys_key,
       b.mbr_updated_dt,
       a.mbr_x_fst_nm,
       a.mbr_x_lst_nm,
       a.person_id,
       a.z_person_id
  FROM mbr_person a,
       mbr_system b
 WHERE a.person_id = b.person_id
   AND a.deactivate = 0
   AND b.deactivate = 0
   AND A.DEACTIVATE = B.DEACTIVATE
   AND A.MBR_LAST_NM NOT IN ('DATA','CORRECTION')
   AND A.MBR_FIRST_NM NOT IN ('DATA','CORRECTION');

Indexes on the MBR_PERSON.DEACTIVATE, MBR_SYSTEM.DEACTIVATE columns may be helpful if most of the rows have something other than 0 in those columns.  Indexes on the MBR_PERSON.MBR_LAST_NM and MBR_PERSON.MBR_FIRST_NM columns will likely be of limited use.  An index on MBR_PERSON.PERSON_ID, MBR_SYSTEM.PERSON_ID will likely be very helpful.

My guess is that the above SQL statement will perform a full tablescan on both tables.  The full tablescans may be the most efficient way to retrieve the rows.  However, that will depend greatly on the composition of the data in the two tables.  Oracle may select to perform a hash join between the two tables, so a large HASH_AREA_SIZE may help.

As has been mentioned in this thread, avoid using PL/SQL for something that can be done efficiently in pure SQL.  Context switches (and I suppose on-the-fly compiling of the PL/SQL code if not using Native code compilation) are expensive on some operating system platforms, and may greatly decrease performance.

The best way to test the performance of the various methods requires access to the Oracle database, unless of course you want to dig through 10046 traces at level 8 or 12.

——————————–

SQL – Reformatting to Improve Performance 3

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Note: the Oracle cost-based query optimizer is able to automatically transform queries submitted by applications to produce an equivalent SQL statement that will hopefully improve SQL execution performance. The possible transformations are limited by the Oracle release version, the value of the OPTIMIZER_FEATURES_ENABLE parameter (and various other hidden parameters), the potential presence of NULL values, and hints embedded in the SQL statement.

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/8661f136bdaefae4

The query is against a view (MY_VIEW below) in the same schema where the procedure lives.  The view selects from another view (EXT_VIEW below) in an external DB via a DBLINK.

Both servers are running Oracle 10g.  The host server is Red Hat Linux.  The external server is Win 2003.  If exact versions become important, I can post them.

The query that works, but slowly, is:

SELECT bv.* FROM MY_VIEW bv
WHERE bv.objectid in
(select linkid from LINKS_TABLE
  where projectid = 116448
    AND blocklist = 'N')
AND bv.GISDATASETNAME = 'XXX' ORDER BY bv.physical_length DESC

However, this takes 12 seconds to return exactly one row from the external database.  Here’s the explain plan:

Operation          Object Name
SELECT STATEMENT Optimizer Mode=ALL_ROWS
 SORT ORDER BY
  NESTED LOOPS SEMI
   REMOTE    .EXT_VIEW   EXTDB.US.OPVANTEK.COM
   TABLE ACCESS BY INDEX ROWID  MY_SCHEMA.LINKS_TABLE
    INDEX UNIQUE SCAN           OPDSCP.PK_PGM 1 0

——————————–

Oracle appears to be using the results of the remote data to drive into the MY_SCHEMA.LINKS_TABLE table using an index on the LINKS_TABLE table.  Thus, Oracle is retrieving all rows from the remote database where GISDATASETNAME = ‘XXX’ and probing the MY_SCHEMA.LINKS_TABLE for matching LINKID values.  You might repeat your explain plan using DBMS_XPLAN with the ALLSTATS LAST parameters to see how it compares.

Is it possible to rewrite the SQL statement into a more efficient form?

First question – can any one tell me a way to force the first query above to use the faster explain plan?  We know there will not be very many linkids returned by the inner query (less than 10).

Having given up on re-writing the first query, I then wrote some PL/SQL to loop over the inner query with a cursor and construct a comma separate list of all the linkids, which I can then use as the IN() clause of my dyanamic sql statement.  That works and runs much faster from a SQL Editor window (we use TOAD).

Let’s take another look at rewriting the original query, something like this:

SELECT /*+ ORDERED */
  BV.*
FROM
  (SELECT DISTINCT
    LINKID
  FROM
    LINKS_TABLE
  WHERE
    PROJECTID = 116448
    AND BLOCKLIST = 'N') L,
  MY_VIEW BV
WHERE
  L.LINKID=BV.OBJECTID
  AND BV.GISDATASETNAME = 'XXX'
ORDER BY
  BV.PHYSICAL_LENGTH DESC;

The above will instruct Oracle to retrieve the 10 or fewer rows from the LINK_TABLE, and use the results to drive into MY_VIEW, which points to the remote database.

How does a DBMS_XPLAN with the ALLSTATS LAST parameters for your original query compare with my rewrite above?

——————————–

SQL – Reformatting to Improve Performance 2

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Note: the Oracle cost-based query optimizer is able to automatically transform queries submitted by applications to produce an equivalent SQL statement that will hopefully improve SQL execution performance. The possible transformations are limited by the Oracle release version, the value of the OPTIMIZER_FEATURES_ENABLE parameter (and various other hidden parameters), the potential presence of NULL values, and hints embedded in the SQL statement.

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/d4e3f3f275ed894d

The following query is performing poorly in the app. I have tried the scalar subquery approach (using formatted to_char)  with no luck. any help would be appreciated.

SELECT  MP_NO, MP_DESC, MP_ACTIVE AS "FLAG_YES_NO",
(
  ( SELECT COUNT(*)     FROM ST, STINV
                WHERE ST.ST_ID = STINV.ST_ID
                AND ST.ST_POSTED IS NOT NULL
                AND STINV.MP_NO = MPT.MP_NO )
   +
  ( SELECT COUNT(*) FROM PT, PTINV
                  WHERE PT.PT_ID = PTINV.PT_ID
                  AND PT.PT_POSTED IS NOT NULL
                  AND PTINV.MP_NO = MPT.MP_NO  )
)
FROM MPT
WHERE COMP_ID = 1
GROUP BY MP_NO, MP_DESC, MP_ACTIVE

——————————–

It might be interesting to see how the performance and DBMS_XPLAN of your SQL statement compares with the following:

SELECT
  MPT.MP_NO,
  MPT.MP_DESC,
  MPT.MP_ACTIVE "FLAG_YES_NO",
  NVL(S.V1,0) + NVL(P.V2,0)
FROM
  MPT,
  (SELECT
    STINV.MP_NO,
    COUNT(*) V1
  FROM
    ST,
    STINV
  WHERE
    ST.ST_ID = STINV.ST_ID
    AND ST.ST_POSTED IS NOT NULL
  GROUP BY
    STINV.MP_NO) S,
  (SELECT
    PTINV.MP_NO,
    COUNT(*) V2
  FROM
    PT,
    PTINV
  WHERE
    PT.PT_ID = PTINV.PT_ID
    AND PT.PT_POSTED IS NOT NULL
  GROUP BY
    PTINV.MP_NO) P
WHERE
  MPT.COMP_ID=1
  AND MPT.MP_NO=S.MP_NO(+)
  AND MPT.MP_NO=P.MP_NO(+)
GROUP BY
  MPT.MP_NO,
  MPT.MP_DESC,
  MPT.MP_ACTIVE;

If possible, the outer join [ (+) ] should be removed from the SQL statement that I posted above.

Is MPT.COMP_ID a column defined as NUMBER?

——————————–

SQL – Reformatting to Improve Performance 1

December 4, 2009

(Forward to the Next Post in the Series)

Note: the Oracle cost-based query optimizer is able to automatically transform queries submitted by applications to produce an equivalent SQL statement that will hopefully improve SQL execution performance. The possible transformations are limited by the Oracle release version, the value of the OPTIMIZER_FEATURES_ENABLE parameter (and various other hidden parameters), the potential presence of NULL values, and hints embedded in the SQL statement.

Some time ago the following question appeared in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/9b81c1b5f729813c

There are three tables A B C with 18 Million, 6 M and 8M records respectivly.

select count(1) from
( Select * from A
where
not exists ( select 'x' from B where join on Keys )
and not exists ( select 'x' from C where join on keys )
)

The explain plan shows that it is using all key indexes for all tables and cost is 7366 for table A Index full scan. And other tables have a index Range scan 3 and 4. What could be a reason for this discrepency.

We are using oracle 9i , Linux operating system. For confidentiality I have renamed the tables

Query:

select /*+ parallel */
*
from A    where not exists
    (select /*+ parallel */ 'X'
             from b
            where stud_id = A.stud_id
              and cpnt_typ_id = A.cpnt_typ_id
              and cpnt_id = A.cpnt_id
              and rev_dte = A.rev_dte
              and cmpl_stat_id = A.cmpl_stat_id
              and compl_dte = a.compl_dte)
      and not exists
    (select /*+ parallel */ 'X'
             from c
            where stud_id = A.stud_id
              and cpnt_typ_id =A.cpnt_typ_id
              and cpnt_id = A.cpnt_id
              and rev_dte = A.rev_dte
              and cmpl_stat_id = A.cmpl_stat_id
              and compl_dte = A.compl_dte)

Plan :

SELECT STATEMENT, GOAL = CHOOSE           Cost=7400  Cardinality=    1   Bytes=     62   IO cost=7400
 SORT AGGREGATE                                      Cardinality=    1   Bytes=     62
  FILTER
   INDEX FAST FULL SCAN         PK_A      Cost=7393  Cardinality=46754   Bytes=2898748   IO cost=7393
   INDEX RANGE SCAN             IDX_B_1   Cost=   3  Cardinality=    1   Bytes=     58   IO cost=   3
   TABLE ACCESS BY INDEX ROWID  PA_C      Cost=   4  Cardinality=    1   Bytes=     60   IO cost=   4
    INDEX RANGE SCAN    Object  IX_C      Cost=   3  Cardinality=    1                   IO cost=   3

With the information provided, the best that I was able to offer is the following, with a manually transformed version of the SQL statement from the NOT EXISTS syntax into inline views:

——————————–

The Oracle version in part determines how Oracle will optimize and execute the SQL statement that you submit.  Certain transformations are performed automatically in 9i for NOT EXISTS queries, regardless of whether or not the cost will increase due to the transformation.  The cardinality numbers reported in the explain plan (a DBMS Xplan would be better) do not appear to be consistent with three tables having 18 million, 6 million and 8 million rows.  When collecting statistics, make certain that the DBMS_STATS routine is called with CASCADE=>TRUE specified.

Jonathan Lewis has commented on his blog a couple times about using PARALLEL, which only resulted in even longer execution times.  You might try reformatting the SQL statement into an equivalent form, and removing the PARALLEL hints.  Something like this:

SELECT
  A.*
FROM
  A,
  (SELECT DISTINCT
    STUD_ID,
    CPNT_TYP_ID,
    CPNT_ID,
    REV_DTE,
    CMPL_STAT_ID,
    COMPL_DTE
  FROM
    B) B,
  (SELECT DISTINCT
    STUD_ID,
    CPNT_TYP_ID,
    CPNT_ID,
    REV_DTE,
    CMPL_STAT_ID,
    COMPL_DTE
  FROM
    C) C
WHERE
  A.STUD_ID = B.STUD_ID(+)
  AND A.CPNT_TYP_ID = B.CPNT_TYP_ID(+)
  AND A.CPNT_ID = B.CPNT_ID(+)
  AND A.REV_DTE = B.REV_DTE(+)
  AND A.CMPL_STAT_ID = B.CMPL_STAT_ID(+)
  AND A.COMPL_DTE = B.COMPL_DTE(+)
  AND B.STUD_ID IS NULL
  AND A.STUD_ID = C.STUD_ID(+)
  AND A.CPNT_TYP_ID = C.CPNT_TYP_ID(+)
  AND A.CPNT_ID = C.CPNT_ID(+)
  AND A.REV_DTE = C.REV_DTE(+)
  AND A.CMPL_STAT_ID = C.CMPL_STAT_ID(+)
  AND A.COMPL_DTE = C.COMPL_DTE(+)
  AND C.STUD_ID IS NULL;

Compare the performance of the above with the performance of your original SQL statement using a 10046 trace at level 8.  The above will likely result in 2 sorting operations in 9i, so the SORT_AREA_SIZE parameter may need to be adjusted.

——————————–

Any other ideas?