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