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 versionPlease 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
Leave a Reply