Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    6

    Unanswered: How to improve performance on PL/SQL query

    I am not sure how to improve the performance of this sql query. Please help. Two or 3 suggestions would be great! Not sure if adding partions to the full table scan would help

    Here is the SQL and Execution Plan:
    select
    a.target_match_id id, b.dataset_id dataset_id,
    b.dataset_name dataset_name, a.dataset_update_id dataset_update,
    lower(b.dataset_tableprefix) prefix,
    (select count(*)
    from mp_match_req_mesg
    where match_request_id = a.match_request_id
    and target_match_id = a.target_match_id
    and updateaction in (1, 2)
    ) mesg_count,
    decode(a.dataset_update_id,
    NULL,
    decode( nvl(a.external_channel_ind, 0),
    0,
    'ALL (' || (select sum(active_count)
    from dataset_update where a.dataset_id = dataset_id
    and available = 1 and
    updateaction not in (3, 4)) || ')',
    'External Query'),
    (select decode(update_name, null,
    TO_CHAR(date_source_generated, 'yyyymmdd'),
    update_name) || ' ALL (' || active_count || ')'
    from dataset_update
    where a.dataset_update_id = dataset_update_id)
    ) updateinfo,
    status_name status
    from mp_target_match a, dataset b, mp_status_code c
    where a.match_request_id = :1 and
    a.dataset_id = b.dataset_id and
    a.status_code = c.status_code

    OPERATION| NAME| ROWS| BYTES| COST| PARTITION START| PARTITION STOP
    SELECT STATEMENT| 235| 18K| 19|
    SORT AGGREGATE 1 | 12
    TABLE ACCESS BY INDEX ROWID| MP_MATCH_REQ_MESG| 1| 12| 6
    BITMAP CONVERSION TO ROWIDS
    BITMAP AND
    BITMAP CONVERSION FROM ROWIDS
    INDEX RANGE SCAN| MPMRM_IDX_1| 156| 3
    BITMAP CONVERSION FROM ROWIDS
    INDEX RANGE SCAN| MPMRM_IDX_2| 156| 3|
    SORT AGGREGATE 1 13
    TABLE ACCESS FULL| DATASET_UPDATE| 13| 169| 214|
    TABLE ACCESS BY INDEX ROWID| DATASET_UPDATE| 1 |121 |2|
    INDEX UNIQUE SCAN| PK_DATASET_UPD| 1| 1
    HASH JOIN| 235| 18K| 19|
    NESTED LOOPS| 235| 10K| 9|
    TABLE ACCESS BY INDEX ROWID| MP_TARGET_MATCH| 470| |14K |7
    INDEX RANGE SCAN MPTMH_MPMRQ_FK 470 4
    TABLE ACCESS BY INDEX ROWID| MP_STATUS_CODE |1 |15 |1
    INDEX UNIQUE SCAN| PK_STATUS_CODE| 1| 0
    TABLE ACCESS FULL| DATASET 998| 33K| 10

  2. #2
    Join Date
    May 2009
    Posts
    6
    I forgot to note that although this table is small at the moment it will be growing substantially very quickly. Hence why i would like to optimize as soon as possible

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Do NOT cross/multi-post

    http://www.dbforums.com/oracle/16420...un-faster.html

    All columns in WHERE clause need to be indexed and all tables & indexes need current statistics.
    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.

Posting Permissions

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