Results 1 to 4 of 4

Thread: Query Tuning

  1. #1
    Join Date
    Dec 2003
    Location
    India
    Posts
    12

    Unanswered: Query Tuning

    Hi All,
    I have a query like this on one of my databases and needs tuning:

    INSERT INTO TAB3 (tr_sub_prem_key, best_address_line_1,
    best_address_line_2,best_address_line_3,
    best_address_line_4,postcode, tr_sub_premise,
    tr_sub_premise_ind,tr_premise_name,
    tr_premise_name_ind,tr_thoroughfare_number,
    tr_depend_thoroughfare,source_table,address_status )
    (SELECT /*+ index(X PK_X) */ X.tr_sub_prem_key,
    X.best_address_line_1,X.best_address_line_2,
    X.best_address_line_3,X.best_address_line_4,
    X.best_postcode,X.tr_sub_premise,
    X.tr_sub_premise_ind,X.tr_premise_name,
    X.tr_premise_name_ind,X.tr_thoroughfare_number,
    X.tr_depend_thoroughfare,'X','F' FROM X,TAB3
    WHERE TAB3.TR_SUB_PREM_KEY =X.tr_sub_prem_key
    AND TAB3.SOURCE_TABLE='ABC' AND TAB3.ADDRESS_STATUS='C' );

    In this query there are 2 tables TAB3 and X ,TAB3 is a global temporary table which contains around 20 records and table X contains around 1 Million records.This query takes around 15 seconds to execute .TAB3 doesnot contain any indexes.
    The expalin plan for this query is as follows


    Execution Plan
    ----------------------------------------------------------
    0 INSERT STATEMENT Optimizer=FIRST_ROWS (Cost=13 Card=1 Bytes=
    180)

    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'X'
    (Cost=2 Card=1 Bytes=148)

    2 1 NESTED LOOPS (Cost=13 Card=1 Bytes=180)
    3 2 TABLE ACCESS (FULL) OF 'TAB3' (Cost=11 Card=1 Bytes=32
    )

    4 2 INDEX (RANGE SCAN) OF 'PK_X' (NON
    -UNIQUE) (Cost=1 Card=1)





    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    123863 consistent gets
    123858 physical reads
    0 redo size
    469 bytes sent via SQL*Net to client
    2051 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    0 rows processed

    Can this query be tuned in such a fashion that it takes less than a second to complete the insert.

    Thanks in advance

    Sbdash

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: Query Tuning

    Hi,

    try to create composite index on TAB3 to avoid full scan.
    Don't forget to analyze it.

    hope this help you

    Originally posted by sbdash
    Hi All,
    I have a query like this on one of my databases and needs tuning:

    INSERT INTO TAB3 (tr_sub_prem_key, best_address_line_1,
    best_address_line_2,best_address_line_3,
    best_address_line_4,postcode, tr_sub_premise,
    tr_sub_premise_ind,tr_premise_name,
    tr_premise_name_ind,tr_thoroughfare_number,
    tr_depend_thoroughfare,source_table,address_status )
    (SELECT /*+ index(X PK_X) */ X.tr_sub_prem_key,
    X.best_address_line_1,X.best_address_line_2,
    X.best_address_line_3,X.best_address_line_4,
    X.best_postcode,X.tr_sub_premise,
    X.tr_sub_premise_ind,X.tr_premise_name,
    X.tr_premise_name_ind,X.tr_thoroughfare_number,
    X.tr_depend_thoroughfare,'X','F' FROM X,TAB3
    WHERE TAB3.TR_SUB_PREM_KEY =X.tr_sub_prem_key
    AND TAB3.SOURCE_TABLE='ABC' AND TAB3.ADDRESS_STATUS='C' );

    In this query there are 2 tables TAB3 and X ,TAB3 is a global temporary table which contains around 20 records and table X contains around 1 Million records.This query takes around 15 seconds to execute .TAB3 doesnot contain any indexes.
    The expalin plan for this query is as follows


    Execution Plan
    ----------------------------------------------------------
    0 INSERT STATEMENT Optimizer=FIRST_ROWS (Cost=13 Card=1 Bytes=
    180)

    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'X'
    (Cost=2 Card=1 Bytes=148)

    2 1 NESTED LOOPS (Cost=13 Card=1 Bytes=180)
    3 2 TABLE ACCESS (FULL) OF 'TAB3' (Cost=11 Card=1 Bytes=32
    )

    4 2 INDEX (RANGE SCAN) OF 'PK_X' (NON
    -UNIQUE) (Cost=1 Card=1)





    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    123863 consistent gets
    123858 physical reads
    0 redo size
    469 bytes sent via SQL*Net to client
    2051 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    0 rows processed

    Can this query be tuned in such a fashion that it takes less than a second to complete the insert.

    Thanks in advance

    Sbdash

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Is PK_X a nonunique index and if it is should it be an unique index (Primary Key?) as this will probably improve performance quite a bit. Also it is doing a lot of physical reads, can you afford to put the index into the KEEP buffer pool. Other thing to do would be to ensure the index is not on the same disk as the table (if your not using striping)

    Dont know if an index on TAB3 would help as it is such a small table that a FTS might be more efficient.

    Alan

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    I am assuming that PK index is being used since there is no other proper index. Create an index based on X.tr_sub_prem_key.

    Also, use
    PHP Code:
    INSERT /*+ APPEND */ INTO TAB3 
    if you can.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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