Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    42

    Unanswered: Tuning Of An Insert Statement

    Hi
    Can anybody help me out in findning out the reason behind the long execution time. Actually when following statement is executed for single user it is taking 230 MilliSeconds but when called for concurrent users it is taking 3 second for some cases but not for all.

    INSERT INTO TAB3 (tr_sub_prem_key, tr_address_quality,
    best_address_line_1, best_address_line_2,best_address_line_3,
    best_address_line_4,postcode, tr_org_name,tr_po_box, tr_sub_premise,tr_sub_premise_ind,
    tr_premise_name,tr_premise_name_ind,tr_thoroughfar e_number,tr_depend_thoroughfare,
    tr_thoroughfare,tr_thoroughfare_ind, tr_double_depend_locality,tr_locality,tr_locality_ ind,
    tr_town,tr_town_ind,tr_county,tr_county_ind,
    tr_postal_outcode,tr_postal_incode, tr_delivery_point, tr_fail_level,
    tr_return_code, tr_country_name,cherished_name,css_indicator,addre ss_status,source_table,order_status)SELECT /*+ INDEX (NAD_ADDRESS NAD_ADDRESS_TR_WINDOW_KEY) */ tr_sub_prem_key, tr_address_quality,
    best_address_line_1, best_address_line_2, best_address_line_3,
    best_address_line_4, best_postcode, tr_org_name,
    tr_po_box, tr_sub_premise,tr_sub_premise_ind,tr_premise_name, tr_premise_name_ind,
    tr_thoroughfare_number,tr_depend_thoroughfare,
    tr_thoroughfare,tr_thoroughfare_ind, tr_double_depend_locality,
    tr_locality,tr_locality_ind,tr_town,tr_town_ind, tr_county,tr_county_ind,tr_postal_outcode,
    tr_postal_incode, tr_delivery_point, tr_fail_level,
    tr_return_code, tr_country_name,CHERISHED_NAME,css_indicator,addre ss_status,'NAD_ADDRESS','1' FROM NAD_ADDRESS
    WHERE EXISTS (SELECT NULL FROM TAB1 WHERE TAB1.TR_WINDOW_KEY=NAD_ADDRESS.TR_WINDOW_KEY)

    In Above Statement Tab3, Tab1 are a Global Temporary Tables. I have an index in NAD_ADDRESS On Tr_Window_key but no indexes on Tab1. Also i have an index in TAB3 on Tr_Sub_Prem_Key.

    The Explain Plan is

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    INSERT STATEMENT Optimizer Mode=FIRST_ROWS 89 K 44837
    TABLE ACCESS BY INDEX ROWID NAD_OWNER_DEV.NAD_ADDRESS 22 3 K 11
    NESTED LOOPS 89 K 16 M 44837
    SORT UNIQUE
    TABLE ACCESS FULL NAD_OWNER_DEV.TAB1 4 K 67 K 11
    INDEX RANGE SCAN NAD_OWNER_DEV.NAD_ADDRESS_TR_WINDOW_KEY 22 3


    Thanks In Advance

    Regards
    Rasmi

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    take out the hint and post the explain plan.

    also, how many distinct values are in column: TAB1.TR_WINDOW_KEY
    thanks.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2004
    Posts
    42
    Hi Duck
    Thanks for your quick reply. Please find the explain plan after taking out the Hint.

    Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

    INSERT STATEMENT Optimizer Mode=FIRST_ROWS 89 K 44837
    TABLE ACCESS BY INDEX ROWID NAD_OWNER_DEV.NAD_ADDRESS 22 3 K 11 :Q45284001 PCWC
    NESTED LOOPS 89 K 16 M 44837 :Q45284001 P->S QC (RANDOM)
    SORT UNIQUE :Q45284000 S->P RND-ROBIN
    TABLE ACCESS FULL NAD_OWNER_DEV.TAB1 4 K 67 K 11
    INDEX RANGE SCAN NAD_OWNER_DEV.NAD_ADDRESS_TR_WINDOW_KEY 22 3 :Q45284001 PCWP


    Also there may be atmost 20 values in TAB1.TR_WINDOW_KEY

    But currently i am running for One vale only i mean TAB1 contains One value only.

    Anticipating a reply from your side.

    Thanks & Regards
    Rasmi

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1. What does the execution plan look like if you just do a straight join between TAB1 and nad_address. It might remove the sort operation.

    2. Also try using other optimizer modes other than FIRST_ROWS and see if that makes a difference (also remove the hint).

    Alan

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    why is it doing FIRST ROWS???

    did you analyze the tables and indexes??

    PHP Code:
    select table_namelast_analyzed 
    from user_tables
    where table_name in 
    ('TAB1','NAD_ADDRESS'); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    May 2004
    Posts
    42
    Thanks
    Actually we are using the CHHOSE based Optimizer Mode for which our tables should have statistics. So we are analyzing tables everyday and Nad_Address tables contains 30 Millions Of Data. Is there any other solutions?. One more thing when i commented out the statement which was taking time then i found that the next statement is taking time. So i think this is not due to our SQL statements. Please send me your views regarding this. The executions time is being varied from time to time. So what should i do?.

    Thanks & Regards
    Rasmi

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    simple:
    - partition the 30million row table
    - create local indexes
    - 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
  •