Results 1 to 4 of 4
  1. #1
    Join Date
    May 2008
    Posts
    3

    Unanswered: Performance issue

    Dear Sir

    I am trying to tune the below sql.But below is the above sql

    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 of the below 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 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_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

    Well let me also tell you that there is no index there in the above query.also As we look it is reading 694183 data blocks (306954 physical reads
    and 387229 consistent gets) to return ONE row -- that's a bit
    excessive, I think.
    Well I wanted to create a procedure and I am going to use this query in the begin section.
    Also the record is taking 28.781 seconds elapsed time to return one row

    Any help would help to resolve the issue

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2008
    Posts
    3

    Smile Performance issue.

    Dear Sir

    I have created indexes on three tables

    ***********************************STATEMENT****** **************************************************
    -- Create/Recreate indexes
    prompt:

    prompt composite index on borrid,user_id:

    create index IND3 on STATEMENT (BORRID,USER_ID);

    *****************************SUBJECTIVE_PRM_TRANS* ************************************************** *
    prompt:
    prompt composite index on borrid,mdlid,user_id column:

    create index IND4 on SUBJECTIVE_PRM_TRANS (BORRID,MDLID,USER_ID);

    prompt:

    *****************************MDL_PARAMETER_TREE*** ************************************************** ***************
    PROMPT:
    PROMPT INDEX ON ROOTNODE COLUMN:
    - Create/Recreate indexes
    create index IND2 on MDL_PARAMETER_TREE (ROOTNODE);

    prompt:
    prompt index on MDLID COLUMN:

    create index IND5 on MDL_PARAMETER_TREE (MDLID);

    prompt:

    After creating the indexes the output was executed in 1 milliseconds.But I would like to ask you one thing.Is the Merge Join Cartesion kills the performance?.Its seems like after applying the indexes on the three tables the query became faster.As well as the cost of the query is now after applying the index is 156.Initially the cost was 28.How come there is a increase in the cost after applying the indexes in the three table?Please help to resolve the issue.?
    Last edited by manoj12; 06-09-08 at 07:22. Reason: forget to update something

  4. #4
    Join Date
    May 2008
    Posts
    3

    Performance issue

    Dear sir

    Please check below the statistics of the above query after applying the indexes


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    6468 consistent gets
    0 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


    and the explain plan
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=156 Card=1 Bytes=28)
    1 0 SORT (ORDER BY) (Cost=156 Card=1 Bytes=28)
    2 1 MERGE JOIN (SEMI) (Cost=154 Card=1 Bytes=28)
    3 2 SORT (JOIN) (Cost=4 Card=1 Bytes=15)
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'STATEMENT' (Cost=2 Card=1 Bytes=15)

    5 4 INDEX (RANGE SCAN) OF 'IND3' (NON-UNIQUE) (Cost=1 Card=1)

    6 2 SORT (UNIQUE) (Cost=151 Card=1 Bytes=13)
    7 6 VIEW OF 'VW_NSO_1' (Cost=149 Card=1 Bytes=13)
    8 7 MERGE JOIN (Cost=149 Card=1 Bytes=91)
    9 8 SORT (JOIN) (Cost=140 Card=1 Bytes=52)
    10 9 MERGE JOIN (CARTESIAN) (Cost=139 Card=1 Bytes=52)

    11 10 VIEW (Cost=4 Card=1 Bytes=39)
    12 11 SORT (GROUP BY) (Cost=4 Card=1 Bytes=17)
    13 12 TABLE ACCESS (BY INDEX ROWID) OF 'SUbjectTIVE_PRM_TRANS' (Cost=2 Card=1 Bytes=17)

    14 13 INDEX (RANGE SCAN) OF 'IND4' (NON-UNIQUE) (Cost=1 Card=1)

    15 10 BUFFER (SORT) (Cost=139 Card=1 Bytes=13)
    16 15 VIEW
    17 16 SORT (AGGREGATE)
    18 17 FILTER
    19 18 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1 Bytes=11)

    20 18 FILTER
    21 20 TABLE ACCESS (BY INDEX ROWID) OF 'MDL_PARAMETER_TREE' (Cost=2 Card=7 Bytes=70)

    22 21 NESTED LOOPS (Cost=68 Card=1 Bytes=44)

    23 22 MERGE JOIN (CARTESIAN) (Cost=6 Card=1 Bytes=34)

    24 23 TABLE ACCESS (BY INDEX ROWID) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2 Card=1 Bytes=20)

    25 24 INDEX (RANGE SCAN) OF 'IND4' (NON-UNIQUE) (Cost=1 Card=1)

    26 23 BUFFER (SORT) (Cost=64 Card=1 Bytes=14)

    27 26 TABLE ACCESS (BY INDEX ROWID) OF 'MDL_PARAMETER_TREE' (Cost=64 Card=1 Bytes=14)

    28 27 INDEX (RANGE SCAN) OF 'IND5' (NON-UNIQUE) (Cost=2 Card=85)

    29 22 INDEX (RANGE SCAN) OF 'IND2' (NON-UNIQUE) (Cost=1 Card=92)

    30 20 TABLE ACCESS (BY INDEX ROWID) OF 'MDL_PARAMETER_TREE' (Cost=64 Card=1 Bytes=41)

    31 30 INDEX (RANGE SCAN) OF 'IND5' (NON-UNIQUE) (Cost=2 Card=85)

    32 8 FILTER
    33 32 SORT (JOIN)
    34 33 VIEW (Cost=72 Card=1 Bytes=39)
    35 34 SORT (GROUP BY) (Cost=8 Card=1 Bytes=31)
    36 35 FILTER
    37 36 HASH JOIN (Cost=6 Card=1 Bytes=31)
    38 37 TABLE ACCESS (BY INDEX ROWID) OF 'SUBJECTIVE_PRM_TRANS' (Cost=2 Card=1 Bytes=24)

    39 38 INDEX (RANGE SCAN) OF 'IND4' (NON-UNIQUE) (Cost=1 Card=1)

    40 37 TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=85 Bytes=595)

    41 36 TABLE ACCESS (BY INDEX ROWID) OF 'MDL_PARAMETER_TREE' (Cost=64 Card=1 Bytes=41)

    42 41 INDEX (RANGE SCAN) OF 'IND5' (NON-UNIQUE) (Cost=2 Card=85)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •