Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: Tune the Query to Increase Performance.

    Query:

    QUERY:

    SELECT DET.ECL_LOG_NUMBER AS LOG_NUMBER, DET.ECL_CREATION_DATE AS CREATION_DATE, upper(DET.ECL_DESCRIPTION) AS DESCRIPTION,
    DET.ECL_REQUESTED_DATE AS REQUESTED_DATE ,upper(DET.ECL_REQUESTOR_NAME) AS NAME, DET.ECL_ENVIRONMENT AS ENV,DET.ECL_EXECUTED_STATUS AS EXE_STATUS,
    fun_ecl_status(DET.ECL_LOG_NUMBER) as APPROVERSSTATUS,
    decode(STA.ECL_APPROVER1,'APPROVED', STA.ECL_APPROVER1 || ' ' || to_char(STA.ECL_APPROVER1_DATE,'MM-dd-yyyy HH24:MIS'),
    'REJECTED', STA.ECL_APPROVER1 || ' ' || to_char(STA.ECL_APPROVER1_DATE,'MM-dd-yyyy HH24:MIS'),
    STA.ECL_APPROVER1) AS APPROVER1,
    decode(STA.ECL_APPROVER2,'APPROVED', STA.ECL_APPROVER2 || ' ' || to_char(STA.ECL_APPROVER2_DATE,'MM-dd-yyyy HH24:MIS'),
    'REJECTED', STA.ECL_APPROVER2 || ' ' || to_char(STA.ECL_APPROVER2_DATE,'MM-dd-yyyy HH24:MIS'), STA.ECL_APPROVER2) AS APPROVER2,
    decode(STA.ECL_APPROVER3,'APPROVED', STA.ECL_APPROVER3 || ' ' || to_char(STA.ECL_APPROVER3_DATE,'MM-dd-yyyy HH24:MIS'),
    'REJECTED', STA.ECL_APPROVER3 || ' '|| to_char(STA.ECL_APPROVER3_DATE,'MM-dd-yyyy HH24:MIS'), STA.ECL_APPROVER3) AS APPROVER3,
    decode(STA.ECL_APPROVER4,'APPROVED', STA.ECL_APPROVER4 || ' ' || to_char(STA.ECL_APPROVER4_DATE,'MM-dd-yyyy HH24:MIS'),
    'REJECTED', STA.ECL_APPROVER4 || ' '|| to_char(STA.ECL_APPROVER4_DATE,'MM-dd-yyyy HH24:MIS'), STA.ECL_APPROVER4) AS APPROVER4,
    decode(STA.ECL_APPROVER5,'APPROVED', STA.ECL_APPROVER5 || ' ' || to_char(STA.ECL_APPROVER5_DATE,'MM-dd-yyyy HH24:MIS'),
    'REJECTED', STA.ECL_APPROVER5 || ' '|| to_char(STA.ECL_APPROVER5_DATE,'MM-dd-yyyy HH24:MIS'), STA.ECL_APPROVER5) AS APPROVER5
    FROM ECL_DETAILS DET, ECL_STATUS_INFO STA
    WHERE DET.ECL_LOG_NUMBER = STA.ECL_LOG_NUMBER
    AND DET.ECL_EXECUTED_STATUS != 'COMPLETED'
    ORDER BY DET.ECL_LOG_NUMBER DESC;





    --------------------------------------------------------------------------------

    Execution Plan

    --------------------------------------------------------------------------------
    Plan hash value: 2429005956


    --------------------------------------------------------------------------------
    Id Operation Name Rows Bytes Cost (%CPU) Time


    --------------------------------------------------------------------------------
    0 SELECT STATEMENT 71 10508 9 (23) 00:00:01
    1 SORT ORDER BY 71 10508 9 (23) 00:00:01
    * 2 HASH JOIN 71 10508 8 (13) 00:00:01
    * 3 TABLE ACCESS FULL ECL_DETAILS 71 5254 4 (0) 00:00:01
    4 TABLE ACCESS FULL ECL_STATUS_INFO 84 6216 3 (0) 00:00:01


    --------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    --------------------------------------------------------------------------------

    2 - access("DET"."ECL_LOG_NUMBER"="STA"."ECL_LOG_NUMBE R")
    3 - filter("DET"."ECL_EXECUTED_STATUS"'COMPLETED')


    highest cost is 9%. last analyzed of two tables are july 6th 2012. Indexes are also thr in both tables for column (ECL_LOG_NUMBER ).
    Indexes are also Analysed on Jully 6th 2012. in both table 84rec are present (ECL_DETAILS DET, ECL_STATUS_INFO STA ).
    Query Final output is fetching 71 records, so Table Access full in execution plan.
    Created Function based index on function(fun_ecl_status).
    after removing order by clause .. cost is reduced. but application team needs order by records in descending order.
    Please suggest me what else i have to do to get better performance query.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why is 1 second not acceptable?
    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
    Jul 2012
    Posts
    2
    Thanks for your suggestion. it's taking less time i.e. 1 second in Development but still in production it is taking some more time.

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    So exactly what results do you get in production (execution plan etc)?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Be aware that Oracle adjusts its execution plans to the environment.

    If you gathered all statistics correctly (I'm also talking about the system statistics) then it might make sense for the optimizer to use Full Table Scans.

    Let's do a little math:

    Lets say, we have two tables with a couple of date, number and varchar fields (no lobs), which would use - let's be generous - in average 5 KByte per record.

    So, if this table holds 80 records, it will use 400 K

    If your db_filemultiblock_read_count is set correctly, it should be (at least) > 60 if you have average disks.

    This means that one physical I/O gets > 60 blocks with a size of (I'm guessing, you are using the default blocksize) 8K each.
    This means, we get (at least) datablocks worth 8K * 60 = 480K with each physical disk access.

    As all relevant data blocks of each table can be accessed with one physical I/O, using an index would double the I/Os (first read the index block - then the corresponding data block).

    You see: sometimes it makes sense to use FTS


    One more thing:
    Please, please, please with honey on top:
    Code:
    TAG 
       YOUR 
          CODE 
                !!!
    It's one mouseclick for you , and it saves a lot of time for all of us ...
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Posting Permissions

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