DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: SQL Tuning

  1. #1
    Join Date
    Dec 1999
    Posts
    217

    SQL Tuning

    Guys/Gals,

    Would like your help here. I am not a SQL Tuning guru but I have to tune this SQL. Please help ..

    SELECT a.operator
    , a.run_id
    , a.f_batch_prefix
    , b.oprid
    FROM batch_vg a
    , oprdefn b
    WHERE b.oprid = a.oprid
    AND substr(a.run_id,1,2)='04';

    Tell me in which way can this SQL be written better. Right now when this SQL is run it times out.

    All input will be highly appreciated.

    Chintzs

  2. #2
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    No statistics::::No help
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    post the explain plan plz
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Dec 1999
    Posts
    217
    Here is the explain plan:
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=165695768 Card=2 Byt
    es=100)

    1 0 MERGE JOIN (Cost=165695768 Card=2 Bytes=100)
    2 1 INDEX (FULL SCAN) OF 'AB_PSOPRDEFN' (UNIQUE) (Cost=14 Ca
    rd=3821 Bytes=26747)

    3 1 SORT (JOIN) (Cost=165695754 Card=2 Bytes=86)
    4 3 VIEW OF 'BATCH_VG' (Cost=165695750 Card=2 By
    tes=86)

    5 4 SORT (UNIQUE) (Cost=165695750 Card=2 Bytes=205)
    6 5 UNION-ALL
    7 6 MERGE JOIN (Cost=106769835 Card=1 Bytes=102)
    8 7 SORT (JOIN) (Cost=106769796 Card=1 Bytes=95)
    9 8 MERGE JOIN (Cost=106769791 Card=1 Bytes=95)
    10 9 SORT (JOIN) (Cost=15901 Card=23 Bytes=1173
    )

    11 10 HASH JOIN (Cost=15896 Card=23 Bytes=1173
    )

    12 11 INDEX (FAST FULL SCAN) OF 'ADJ_ER
    N_CTL' (UNIQUE) (Cost=8 Card=206 Bytes=4944)

    13 11 VIEW OF 'ADJERN_TRNTTL' (Cost=158
    86 Card=1025100 Bytes=27677700)

    14 13 SORT (GROUP BY) (Cost=15886 Card=102
    5100 Bytes=18451800)

    15 14 INDEX (FAST FULL SCAN) OF 'PR
    _ADJUSTMNTS' (UNIQUE) (Cost=369 Card=1025100 Bytes=18451800)

    16 9 SORT (JOIN) (Cost=106753891 Card=903012257
    Bytes=39732539308)

    17 16 VIEW OF 'ADJERN_ACCESS' (Cost=52603
    57708 Card=903012257 Bytes=39732539308)

    18 17 SORT (GROUP BY) (Cost=66159401 Card=90
    3012257 Bytes=68628931532)

    19 18 FILTER
    20 19 HASH JOIN (Cost=1483438 Card=90301
    2257 Bytes=68628931532)

    21 20 TABLE ACCESS (FULL) OF 'PSOPRDEF
    N' (Cost=5 Card=3821 Bytes=57315)

    22 20 NESTED LOOPS (Cost=1481440 Card=
    98076443 Bytes=5982663023)

    23 22 TABLE ACCESS (FULL) OF 'SCR
    TY_TBL_DEPT' (Cost=4 Card=1914 Bytes=63162)

    24 22 TABLE ACCESS (FULL) OF 'P
    R_ADJUSTMNTS' (Cost=774 Card=51255 Bytes=1435140)

    25 19 FILTER
    26 25 FILTER
    27 26 TABLE ACCESS (BY INDEX ROWID)
    OF 'TREENODE' (Cost=3 Card=1 Bytes=42)

    28 27 BITMAP CONVERSION (TO ROWIDS
    )

    29 28 BITMAP AND
    30 29 BITMAP CONVERSION (FROM
    ROWIDS)

    31 30 INDEX (RANGE SCAN) OF
    'CPSTREENODE' (NON-UNIQUE) (Cost=1 Card=1)

    32 29 BITMAP CONVERSION (FROM
    ROWIDS)

    33 32 INDEX (RANGE SCAN) OF
    'FPSTREENODE' (NON-UNIQUE) (Cost=3 Card=1)

    34 25 FILTER
    35 34 TABLE ACCESS (BY INDEX ROWID)
    OF 'SCRTY_TBL_DEPT' (Cost=3 Card=1 Bytes=25)

    36 35 INDEX (RANGE SCAN) OF '3SC
    RTY_TBL_DEPT' (NON-UNIQUE) (Cost=2 Card=2)

    37 7 SORT (JOIN) (Cost=40 Card=3821 Bytes=26747)
    38 37 INDEX (FAST FULL SCAN) OF 'BPSOPRDEFN' (NO
    N-UNIQUE) (Cost=2 Card=3821 Bytes=26747)

    39 6 MERGE JOIN (Cost=58925905 Card=1 Bytes=103)
    40 39 SORT (JOIN) (Cost=58925866 Card=1 Bytes=96)
    41 40 MERGE JOIN (Cost=58925861 Card=1 Bytes=96)
    42 41 SORT (JOIN) (Cost=8864 Card=18 Bytes=936)
    43 42 HASH JOIN (Cost=8860 Card=18 Bytes=936)
    44 43 INDEX (FAST FULL SCAN) OF 'ADJ_DE
    D_CTL' (UNIQUE) (Cost=4 Card=68 Bytes=1700)

    45 43 VIEW OF 'ADJDED_TRNTTL' (Cost=885
    5 Card=566944 Bytes=15307488)

    46 45 SORT (GROUP BY) (Cost=8855 Card=5669
    44 Bytes=10204992)

    47 46 INDEX (FAST FULL SCAN) OF 'AD
    J_DEDUCTNS' (UNIQUE) (Cost=272 Card=566944 Bytes=10204992)

    48 41 SORT (JOIN) (Cost=58916997 Card=499421892
    Bytes=21974563248)

    49 48 VIEW OF 'ADJDED_ACCESS' (Cost=29091
    80155 Card=499421892 Bytes=21974563248)

    50 49 SORT (GROUP BY) (Cost=36465720 Card=49
    9421892 Bytes=37956063792)

    51 50 FILTER
    52 51 HASH JOIN (Cost=695895 Card=499421
    892 Bytes=37956063792)

    53 52 TABLE ACCESS (FULL) OF 'OPRDEF
    N' (Cost=5 Card=3821 Bytes=57315)

    54 52 NESTED LOOPS (Cost=694786 Card=5
    4242367 Bytes=3308784387)

    55 54 TABLE ACCESS (FULL) OF 'SCR
    TY_TBL_DEPT' (Cost=4 Card=1914 Bytes=63162)

    56 54 TABLE ACCESS (FULL) OF 'A
    DJ_DEDUCTNS' (Cost=363 Card=28347 Bytes=793716)

    57 51 FILTER
    58 57 FILTER
    59 58 TABLE ACCESS (BY INDEX ROWID)
    OF 'TREENODE' (Cost=3 Card=1 Bytes=42)

    60 59 BITMAP CONVERSION (TO ROWIDS
    )

    61 60 BITMAP AND
    62 61 BITMAP CONVERSION (FROM
    ROWIDS)

    63 62 INDEX (RANGE SCAN) OF
    'CPSTREENODE' (NON-UNIQUE) (Cost=1 Card=1)

    64 61 BITMAP CONVERSION (FROM
    ROWIDS)

    65 64 INDEX (RANGE SCAN) OF
    'FPSTREENODE' (NON-UNIQUE) (Cost=3 Card=1)

    66 57 FILTER
    67 66 TABLE ACCESS (BY INDEX ROWID)
    OF 'SCRTY_TBL_DEPT' (Cost=3 Card=1 Bytes=25)

    68 67 INDEX (RANGE SCAN) OF '3SC
    RTY_TBL_DEPT' (NON-UNIQUE) (Cost=2 Card=2)

    69 39 SORT (JOIN) (Cost=40 Card=3821 Bytes=26747)
    70 69 INDEX (FAST FULL SCAN) OF 'BPSOPRDEFN' (NO
    N-UNIQUE) (Cost=2 Card=3821 Bytes=26747)

    Let me know if you want it in a different format.

    Thanks in advance.
    Chintzs

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    If this query is based on two separate views, you may want to tune the views separately then tune them together. Be careful of joining views together that are based on the same underlying tables, that could cause a performance issue. Also be careful of writing views on views on views calling the same tables in the various layers.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    and learn how to format posts properly, impossible to read otherwise

  7. #7
    Join Date
    Dec 1999
    Posts
    217
    Based on some of your input I started looking at the underlying objects closely and I see that the Select statement is making a join between a view(BATCH_VG) and a table(oprdefn). The View is very complex and does query the "oprdefn" table too. The code for the view is:
    CREATE OR REPLACE VIEW "SCOTT"."BATCH_VG" ("OPERATOR",
    "RUN_ID","F_BATCH_PREFIX","OPRID") AS
    select a.operator, a.run_id, a.f_batch_prefix, zz1.oprid
    from adj_ern_ctl a, adjern_access zz1,
    adjern_trnttl zz2, oprdefn y
    where a.run_id = zz1.run_id
    and a.run_id = zz2.run_id
    and zz1.run_id = zz2.run_id
    and zz1.batch_run_id = zz2.batch_run_id
    and zz1.f_adjern_accessttl = zz2.f_adj_ern_tran_ttl
    and a.batch_run_id = zz1.batch_run_id
    and a.batch_run_id = zz2.batch_run_id
    and y.oprid = zz1.oprid union
    select a1.operator, a1.run_id, a1.f_batch_prefix, zz11.oprid
    from adj_ded_ctl a1, adjded_access zz11,
    adjded_trnttl zz21, oprdefn y1
    where a1.run_id = zz11.run_id
    and a1.run_id = zz21.run_id
    and zz11.run_id = zz21.run_id
    and zz11.batch_run_id = zz21.batch_run_id
    and zz11.f_adjded_accessttl = zz21.f_adj_ded_tran_ttl
    and a1.batch_run_id = zz11.batch_run_id
    and a1.batch_run_id = zz21.batch_run_id
    and y1.oprid = zz11.oprid;

    I look forward to hearing your input on this and pointing me in the direction this query should be.
    Thanks in advance,

    Chintzs

  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857

    Re: SQL Tuning

    Originally posted by Chintz
    Guys/Gals,

    Would like your help here. I am not a SQL Tuning guru but I have to tune this SQL. Please help ..

    SELECT a.operator
    , a.run_id
    , a.f_batch_prefix
    , b.oprid
    FROM batch_vg a
    , oprdefn b
    WHERE b.oprid = a.oprid
    AND substr(a.run_id,1,2)='04';

    Tell me in which way can this SQL be written better. Right now when this SQL is run it times out.

    All input will be highly appreciated.

    Chintzs
    This is a simple sql select statement that could be just derives from views with maybe from underlying views/table(as what gandolf is trying to say).
    You might just need to rewrite your code.
    can you post the descriptions of tables involve?

  9. #9
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Try to code it using the original table name and be sure you
    had analyzed it and got index both on oprid Did someone said it yet?
    What I mean is u can use smaller temporary tables. Just extract the
    basic info from each tables and insert/join into smaller products

    Ms C3
    Last edited by kris123; 04-29-2005 at 01:10 AM.

  10. #10
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by kris123
    Try to code it using the original table name and be sure you
    had analyzed it and got index both on oprid Did someone said it yet?
    What I mean is u can use smaller temporary tables. Just extract the
    basic info from each tables and insert/join into smaller products

    Ms C3
    That's exactly what I'm trying to say dear.

    But what about function-based Index? I am sure it will help the performance too.

Posting Permissions

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


Click Here to Expand Forum to Full Width