Performance issue

7 views

Skip to first unread message

manoj

unread,

May 27, 2008, 7:01:08 AM5/27/08

to ORACLE_DBA_EXPERTS

Dear Sir

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
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------
| 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

19 rows selected.

Please assist me sir to tune the below query

Regards

ora...@msn.com

unread,

May 27, 2008, 9:07:14 AM5/27/08

to ORACLE_DBA_EXPERTS

> ---------------------------------------------------------------------------------------------
>
> ----------------------------------------------------------------------------------

> ----------------------------------------------------------------------------------

> | 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
>
> 19 rows selected.
>
> Please assist me sir to tune the below query
>
> Regards

I find it quite difficult to believe you have learned nothing from the
prior exchanges on query tuning, however:

-- The 'select count(*)' requires a full table scan or an index fast
full scan, and if you have no indexes on columns declared as NOT NULL
then the only option available is a FULL TABLE SCAN. And, apparently,
there are no appropriate indexes built on any of these tables.

-- Since rootnode is not indexed the only option is a full table scan.

-- Since, apparently, NONE of the columns in MDL_PARAMETER_TREE are
indexed there is absolutely no other option than a FULL TABLE SCAN.

You have sufficient information to build the necessary indexes to
improve this query's performance. I strongly suggest you do so, and
learn something in the process.

David Fitzjarrell

Charles Hooper

unread,

May 27, 2008, 5:19:40 PM5/27/08

to ORACLE_DBA_EXPERTS

> ---------------------------------------------------------------------------------------------
>
> ----------------------------------------------------------------------------------

> ----------------------------------------------------------------------------------

>
> Note: cpu costing is off, 'PLAN_TABLE' is old version
>
> 19 rows selected.
>
> Please assist me sir to tune the below query
>
> Regards

Adding to David's comments, 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:

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

If the output of the 10046 trace is not excessively long, post it here
for analysis.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Reply all

Reply to author

Forward

0 new messages