Home » RDBMS Server » Performance Tuning » performance issue
performance issue [message #322421] Fri, 23 May 2008 08:18 Go to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

This query is taking a long time to execute

Please check the query and the below is the explain plan mentioned to it

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

Below is the explain plan for the query

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 Byte
s=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_T
RANS' (Cost=2 Card=1 Bytes=81)

15 13 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TR
EE' (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_PARAMETE
R_TREE' (Cost=2 Card=1 Bytes=413)

27 22 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TR
EE' (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
Re: performance issue [message #322422 is a reply to message #322421] Fri, 23 May 2008 08:23 Go to previous messageGo to next message
BlackSwan
Messages: 25043
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: performance issue [message #322423 is a reply to message #322422] Fri, 23 May 2008 08:33 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Please can you help me in tuning the below query
Atleast any assistance would be of great help sir.


Regards
Re: performance issue [message #322425 is a reply to message #322421] Fri, 23 May 2008 08:37 Go to previous messageGo to next message
BlackSwan
Messages: 25043
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which of the suggestions have you tried & what were the results

Only you have the code.
Only you have the tables.
Only you have the data.

If YOU can not make it perform better,
why do you expect anyone else can assist you?
Re: performance issue [message #322428 is a reply to message #322423] Fri, 23 May 2008 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
manoj12 wrote on Fri, 23 May 2008 15:33
Dear Sir

Please can you help me in tuning the below query
Atleast any assistance would be of great help sir.


Regards


Dear Sir,

Please can you follow the guidelines as this has been requested to you in almost all the topics you posted.
At least this would be of great help to help you sir.

Regards
Michel

Re: performance issue [message #322432 is a reply to message #322425] Fri, 23 May 2008 09:02 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Well Do you wanted me to generate tkprof report of the below query

REGARDs

Re: performance issue [message #322454 is a reply to message #322432] Fri, 23 May 2008 10:38 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
dear sir

Please help me .I am new to this

Regards
Re: performance issue [message #322455 is a reply to message #322421] Fri, 23 May 2008 10:41 Go to previous messageGo to next message
BlackSwan
Messages: 25043
Registered: January 2009
Location: SoCal
Senior Member
Since you can not or will not follow Posting Guidelines, I won't waste my time spoon feeding you answers.
Re: performance issue [message #322456 is a reply to message #322455] Fri, 23 May 2008 10:47 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear sir

That is what I am asking you that do you wanted me to generate tkprof report for the below query

Regards

[Updated on: Fri, 23 May 2008 10:47]

Report message to a moderator

Re: performance issue [message #322732 is a reply to message #322456] Mon, 26 May 2008 02:03 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, you chose to ask for help elsewhere; that's perfectly OK, but - why do you refuse to properly format your messages? The one at dBforums is as badly written as the one you posted here. People would like to help you, but can not afford wasting their precious time trying to decipher unformatted information you provided.

Therefore, as long as you keep posting unreadable messages, you probably won't get the answer.

By the way, this one is hilarious:
dBforums
I am trying to tune the below sql.But below is the above sql
Re: performance issue [message #322740 is a reply to message #322732] Mon, 26 May 2008 02:19 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

I have not send you any unformatted messages sir.This is what the problem actually I am facing.And I would ask for your help to tune the below query.

Regards
Re: performance issue [message #322744 is a reply to message #322740] Mon, 26 May 2008 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As soon as you will post following the guidelines.

Regards
Michel
Re: performance issue [message #322756 is a reply to message #322744] Mon, 26 May 2008 02:47 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

I have understood sir
Do you wanted me to format the code now

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 Sir I have decided to create a function based index on the stmtdt column in the statement table since I can see from above explain plan.The query is taking 28.781 seconds to return only one row as well as the query is reading is 694183 data blocks (306954 physical reads
and 387229 consistent gets) to return ONE row -- that's a bit
excessive, I think.
I Think I should create a materialised view sir based on the various subqueries I am using. A materialized view
may be better than the in-line views as an MV can be indexed.
This is what my overall suggestion is all about sir.Sorry for the incovinience caused.
Well This is the above explain plan sir
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


Is the above suggestion correct sir.If I am wrong do correct me
Regards

[Updated on: Mon, 26 May 2008 02:48]

Report message to a moderator

Re: performance issue [message #322790 is a reply to message #322756] Mon, 26 May 2008 04:31 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Well I think the message is in the formatted way.I would like to hear from your sir.Can you please tell me your suggestion so that I can get the correct answer.I am extremely sorry for posting the message in the unformatted way.

Thanks and
regards
Re: performance issue [message #322792 is a reply to message #322790] Mon, 26 May 2008 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Well I think the message is in the formatted way.

Here's a formatted explain plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 2785462606

-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |     3 |  1770 |    31   (4)| 00:00:01 |
|*  1 |  HASH JOIN                           |                |     3 |  1770 |    31   (4)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN               |                |     3 |   957 |    31   (4)| 00:00:01 |
|*  3 |    HASH JOIN                         |                |     3 |   792 |    31   (4)| 00:00:01 |
|   4 |     NESTED LOOPS                     |                |     3 |   744 |    28   (0)| 00:00:01 |
|   5 |      NESTED LOOPS OUTER              |                |     3 |   693 |    25   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER             |                |     2 |   374 |    23   (0)| 00:00:01 |
|   7 |        NESTED LOOPS OUTER            |                |     2 |   342 |    21   (0)| 00:00:01 |
|   8 |         NESTED LOOPS OUTER           |                |     2 |   326 |    19   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                |                |     2 |   266 |    18   (0)| 00:00:01 |
|* 10 |           TABLE ACCESS BY INDEX ROWID| OBJ$           |     2 |    72 |    17   (0)| 00:00:01 |
|* 11 |            INDEX SKIP SCAN           | I_OBJ2         |     2 |       |    15   (0)| 00:00:01 |
|* 12 |           TABLE ACCESS CLUSTER       | TAB$           |     1 |    97 |     1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN         | I_OBJ#         |     1 |       |     0   (0)| 00:00:01 |
|  14 |          TABLE ACCESS BY INDEX ROWID | OBJ$           |     1 |    30 |     1   (0)| 00:00:01 |
|* 15 |           INDEX UNIQUE SCAN          | I_OBJ1         |     1 |       |     0   (0)| 00:00:01 |
|  16 |         TABLE ACCESS BY INDEX ROWID  | OBJ$           |     1 |     8 |     1   (0)| 00:00:01 |
|* 17 |          INDEX UNIQUE SCAN           | I_OBJ1         |     1 |       |     0   (0)| 00:00:01 |
|  18 |        TABLE ACCESS CLUSTER          | USER$          |     1 |    16 |     1   (0)| 00:00:01 |
|* 19 |         INDEX UNIQUE SCAN            | I_USER#        |     1 |       |     0   (0)| 00:00:01 |
|  20 |       TABLE ACCESS CLUSTER           | SEG$           |     2 |    88 |     1   (0)| 00:00:01 |
|* 21 |        INDEX UNIQUE SCAN             | I_FILE#_BLOCK# |     1 |       |     0   (0)| 00:00:01 |
|  22 |      TABLE ACCESS CLUSTER            | TS$            |     1 |    17 |     1   (0)| 00:00:01 |
|* 23 |       INDEX UNIQUE SCAN              | I_TS#          |     1 |       |     0   (0)| 00:00:01 |
|  24 |     TABLE ACCESS FULL                | USER$          |    50 |   800 |     2   (0)| 00:00:01 |
|  25 |    BUFFER SORT                       |                |     1 |    55 |    29   (4)| 00:00:01 |
|* 26 |     FIXED TABLE FULL                 | X$KSPPI        |     1 |    55 |     0   (0)| 00:00:01 |
|  27 |   FIXED TABLE FULL                   | X$KSPPCV       |   100 | 27100 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("KSPPI"."INDX"="KSPPCV"."INDX")
   3 - access("O"."OWNER#"="U"."USER#")
  10 - filter(BITAND("O"."FLAGS",128)=0)
  11 - access("O"."NAME"='T')
       filter("O"."NAME"='T')
  12 - filter(BITAND("T"."PROPERTY",1)=0)
  13 - access("O"."OBJ#"="T"."OBJ#")
  15 - access("T"."BOBJ#"="CO"."OBJ#"(+))
  17 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
  19 - access("CX"."OWNER#"="CU"."USER#"(+))
  21 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+) AND
              "T"."BLOCK#"="S"."BLOCK#"(+))
  23 - access("T"."TS#"="TS"."TS#")
  26 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')

Compare with yours.

Regards
Michel
Re: performance issue [message #322802 is a reply to message #322792] Mon, 26 May 2008 05:01 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
No Message Body

[Updated on: Mon, 26 May 2008 05:26]

Report message to a moderator

Re: performance issue [message #322813 is a reply to message #322802] Mon, 26 May 2008 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how to get this plan.

Use SQL*Plus, execute "set autotrace traceonly explain" and launch your query.

Regards
Michel
Re: performance issue [message #322827 is a reply to message #322813] Mon, 26 May 2008 06:25 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

I got the below output in the formatted way now sir.Could you please check and tell whether the suugesstion is correct or not



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                     |  Name                 | Rows  | Bytes |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |     1 |    61 |
|   1 |  SORT ORDER BY                |                       |     1 |    61 |
|   2 |   HASH JOIN SEMI              |                       |     1 |    61 |
|   3 |    TABLE ACCESS FULL          | STATEMENT             |     1 |    48 |
|   4 |    VIEW                       | VW_NSO_1              |     1 |    13 |
|   5 |     NESTED LOOPS              |                       |     1 |    91 |
|   6 |      HASH JOIN                |                       |     1 |    78 |
|   7 |       VIEW                    |                       |     1 |    39 |
|   8 |        SORT GROUP BY          |                       |     1 |    55 |
|   9 |         TABLE ACCESS FULL     | SUBJECTIVE_PRM_TRANS  |     1 |    55 |
|  10 |       VIEW                    |                       |     1 |    39 |
|  11 |        SORT GROUP BY          |                       |     1 |   107 |
|  12 |         FILTER                |                       |       |       |
|  13 |          HASH JOIN            |                       |     1 |   107 |
|  14 |           TABLE ACCESS FULL   | SUBJECTIVE_PRM_TRANS  |     1 |    81 |
|  15 |           TABLE ACCESS FULL   | MDL_PARAMETER_TREE    |   142 |  3692 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|  16 |          TABLE ACCESS FULL    | MDL_PARAMETER_TREE    |     1 |   155 |
|  17 |      VIEW                     |                       |     1 |    13 |
|  18 |       SORT AGGREGATE          |                       |     1 |   284 |
|  19 |        FILTER                 |                       |       |       |
|  20 |         TABLE ACCESS FULL     | MDL_PARAMETER_TREE    |     1 |   284 |
|  21 |         FILTER                |                       |       |       |
|  22 |          NESTED LOOPS         |                       |     1 |   636 |
|  23 |           MERGE JOIN CARTESIAN|                       |     1 |   481 |
|  24 |            TABLE ACCESS FULL  | SUBJECTIVE_PRM_TRANS  |     1 |    68 |
|  25 |            BUFFER SORT        |                       |     1 |   413 |
|  26 |             TABLE ACCESS FULL | MDL_PARAMETER_TREE    |     1 |   413 |
|  27 |           TABLE ACCESS FULL   | MDL_PARAMETER_TREE    |    12 |  1860 |
|  28 |          TABLE ACCESS FULL    | MDL_PARAMETER_TREE    |     1 |   155 |
--------------------------------------------------------------------------------
Note: cpu costing is off, 'PLAN_TABLE' is old version

36 rows selected

[Updated on: Mon, 26 May 2008 07:43]

Report message to a moderator

Re: performance issue [message #322879 is a reply to message #322421] Mon, 26 May 2008 10:02 Go to previous messageGo to next message
BlackSwan
Messages: 25043
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which of the suggestions have you tried & what were the results

Are statistics current on all objects accessed by this SQL?

[Updated on: Mon, 26 May 2008 10:03] by Moderator

Report message to a moderator

Re: performance issue [message #322979 is a reply to message #322879] Tue, 27 May 2008 00:07 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir
Quote:

As well I have suggested to create a materialized view and index that view as the materialized view can be index whereas the inline view cannot be index.


Also There are some basic problems that I found in the query and the explain plan that should be fixed

* 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 - we 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.



This is what I have suggested so far sir.Is the above suggestion correct?

Regards

[Updated on: Tue, 27 May 2008 01:10] by Moderator

Report message to a moderator

Re: performance issue [message #322999 is a reply to message #322979] Tue, 27 May 2008 00:51 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear sir

Any update

Regards
Re: performance issue [message #323129 is a reply to message #322999] Tue, 27 May 2008 07:00 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

I have created a function based index on the STMTDT COLUMN ON THE STATEMENT TABLE.AFTER THAT I HAVE CREATED A MATERIALISED VIEW ON THE INLINE VIEW CREATED.ANY HELP WOULD HELP ME TO RESOLVE THE ISSUE

REGARDS
Re: performance issue [message #323232 is a reply to message #323129] Tue, 27 May 2008 18:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
In the following subset of the SQL:
     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

You have inline views J and K in the FROM clause, but you do not have any join criteria (no WHERE clause). So this is like a CARTESIAN PRODUCT.

This is not a great problem because K will return a sinle row (it is a simple COUNT(*)). The thing that IS a problem is that K is being executed for each row returned from J.

Try reversing the order of J and K in the FROM clause (so that K is first) and add an ORDERED hint to stop it from nesting K.
     select /*+ORDERED*/ j.borrid, j.year, j.cntVariable + k.cntFixed as cntMdlTotals
     from
     (...) k, (...) j


Ross Leishman
Re: performance issue [message #323262 is a reply to message #323232] Wed, 28 May 2008 00:08 Go to previous message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Thank you very much sir.

Regards

[Updated on: Wed, 28 May 2008 04:31]

Report message to a moderator

Previous Topic: SQL STATEMENT TUNING
Next Topic: optimizer_index_cost_adj
Goto Forum:
  


Current Time: Wed Dec 07 08:35:15 CST 2016

Total time taken to generate the page: 0.11116 seconds