Results 1 to 7 of 7

Thread: SQL Performance

  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    15

    Unhappy Unanswered: SQL Performance

    The query below is taking more than 15/20 seconds to finish.
    A quick glance suggests the distinct and the outer join as the culprits.
    Any suggestions to bring down the response time ?

    SELECT /*+ first_rows */ distinct
    files.APP_SHORT_NAME PRODUCT,
    files.SUBDIR DIRECTORY,
    files.filename FILENAME
    vers.version VERSION,
    vers.version_segment1, vers.version_segment2,
    vers.version_segment3, vers.version_segment4,
    vers.version_segment5, vers.version_segment6,
    vers.version_segment7, vers.version_segment8,
    vers.version_segment9, vers.version_segment10
    FROM
    ad_files files,
    ad_patch_run_bug_actions rba,
    ad_patch_common_actions cact,
    ad_file_versions vers,
    ad_patch_run_bugs prb
    WHERE prb.patch_run_id = 234
    and rba.executed_flag = 'Y'
    and rba.PATCH_RUN_BUG_ID = prb.PATCH_RUN_BUG_ID
    and cact.COMMON_ACTION_ID= rba.COMMON_ACTION_ID
    and cact.ACTION_CODE in ('copy', 'forcecopy','RU_Update')
    and files.FILE_ID = rba.FILE_ID
    and vers.FILE_VERSION_ID(+) = rba.PATCH_FILE_VERSION_ID
    ORDER BY
    files.filename


    Operation Object Name Rows Bytes Cost

    SELECT STATEMENT Hint=HINT: FIRST_ROWS 848 K 44293
    SORT UNIQUE 848 K 99 M 27943
    HASH JOIN 848 K 99 M 11592
    TABLE ACCESS BY INDEX ROWID AD_PATCH_RUN_BUGS 2 K 28 K 1477
    INDEX RANGE SCAN AD_PATCH_RUN_BUGS_U2 325 20
    HASH JOIN 848 K 91 M 9951
    TABLE ACCESS FULL AD_FILES 504 K 21 M 762
    HASH JOIN OUTER 848 K 55 M 6812
    HASH JOIN 848 K 25 M 3936
    VIEW index$_join$_003 153 K 1 M 883
    HASH JOIN 153 K 1 M
    INLIST ITERATOR
    INDEX RANGE SCAN AD_PATCH_COMMON_ACTIONS_N1 153 K 1 M 551
    INDEX FAST FULL SCAN AD_PATCH_COMMON_ACTIONS_U1 153 K 1 M 551
    TABLE ACCESS FULL AD_PATCH_RUN_BUG_ACTIONS 848 K 17 M 2263
    TABLE ACCESS FULL AD_FILE_VERSIONS 730 K 25 M 1084
    thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Only tables whici contribute to the SELECT clause
    should exist within the FROM clause.
    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
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Without knowing more about your database it is hard to say. But it looks to me like you may be missing a join condition on AD_FILE_VERSIONS: this is joined only by a column called FILE_VERSION_ID; should there not also be a join based on the "FILE_ID"?

  4. #4
    Join Date
    Mar 2004
    Location
    India
    Posts
    15

    Unhappy

    Thanks for your replies anacedent and andrewst.

    Am a kind of a newbie to SQL tuning, could you be a bit more specific on
    why/how i should not use tables in the FROM clause which do not contribute
    to the SELECT clause.

    And andrew, thats the problem, i can't use the FILE_ID to restrict
    AD_FILE_VERSIONS as, it may not have all the records in it.
    (notice the outer join on it).

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by sjanarth
    And andrew, thats the problem, i can't use the FILE_ID to restrict AD_FILE_VERSIONS as, it may not have all the records in it. (notice the outer join on it).
    So are you saying that there is a FILE_ID column in AD_FILE_VERSIONS, but you are ignoring it in your query? Hard to see how that could make sense: if one file has a version 7 then your query will say that every file has a version 7! The outer join exists precisely to cater for cases where a joined table may not have all the records:

    and vers.FILE_ID(+) = rba.FILE_ID
    and vers.FILE_VERSION_ID(+) = rba.PATCH_FILE_VERSION_ID

    That will return the matching "vers" row if there is one, or a dummy "vers" record with all NULLs if there is not. But either way, you will still get the "rba" row in the result.

  6. #6
    Join Date
    Mar 2004
    Location
    India
    Posts
    15

    Unhappy

    A small correction -

    and vers.FILE_ID(+) = rba.FILE_ID
    and vers.FILE_VERSION_ID(+) = rba.PATCH_FILE_VERSION_ID

    It should be

    and files.FILE_ID(+) = rba.FILE_ID
    and vers.FILE_VERSION_ID(+) = rba.PATCH_FILE_VERSION_ID

    Filenames come from the AD_FILES table while the AD_FILE_VERSIONS table houses the version numbers alone.
    There might be cases where a file is not version controlled and hence that should also be listed.
    This is why we have the outer join on ad_file_versions.

    And moreover, i believe we can't have two outer joins on a single table.
    Correct me if am wrong. Thanks for your interest.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Filenames come from the AD_FILES table while the D_FILE_VERSIONS table houses the version numbers alone.
    In that case, i.e. if the "vers" table does not have a FILE_ID column, then ignore my previous comment. I got the false impression it had from your prior post where you said "i can't use the FILE_ID to restrict AD_FILE_VERSIONS as, it may not have all the records in it". You didn't say "... because no such column exists in that table"!

    And moreover, i believe we can't have two outer joins on a single table.
    Correct me if am wrong. Thanks for your interest.
    Well, yes and no. You can certainly have two or more outer join conditions on a single table as in my example:

    and vers.FILE_ID(+) = rba.FILE_ID
    and vers.FILE_VERSION_ID(+) = rba.PATCH_FILE_VERSION_ID

    However, the outer table (in this case "vers") cannot be outer joined to more than one table. So this would be illegal:

    and vers.FILE_ID(+) = files.FILE_ID
    and vers.FILE_VERSION_ID(+) = rba.PATCH_FILE_VERSION_ID

    because it tries to outer join "vers" to both "rba" and "files".

    BTW, your other example is also OK:

    and files.FILE_ID(+) = rba.FILE_ID
    and vers.FILE_VERSION_ID(+) = rba.PATCH_FILE_VERSION_ID

    because this outer joins "files" to one table ("rba") and also outer joins "vers" to one table.

    But anyway, based on what I now know, it appears that your outer join was correct already.

Posting Permissions

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