DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Tuning sql

  1. #1
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681

    Tuning sql

    Hi Friends,

    How can I improve performance of the ff program which runs slow.
    Thanks

    ============
    set serveroutput on
    spool telnum.lst

    DECLARE
    v_customer_id NUMBER := 0;
    v_phone VARCHAR2(35):=NULL;
    v_fax VARCHAR2(35):=NULL;
    v_email VARCHAR2(50):=NULL;
    v_process VARCHAR2(40):=NULL;

    cursor c_customer is
    SELECT cca.customer_id
    FROM cax_customers_all cca;

    BEGIN

    select to_char(sysdate,'DD-MON-RR HH24:MI:SS') into v_process from
    dual;
    dbms_output.put_line('update phone ==>'||v_process);

    OPEN c_customer;

    LOOP

    FETCH c_customer
    INTO v_customer_id;

    EXIT WHEN c_customer%NOTFOUND;

    -- dbms_output.put_line('fetched customer
    ==>'||v_customer_id);

    BEGIN

    SELECT
    decode(rp.area_code,null,'','(')||rp.area_code||
    decode(rp.area_code,null,'',')
    ')||rp.phone_number
    INTO v_phone
    FROM ar_phones_v rp
    WHERE rp.status = 'A'
    AND rp.phone_type = 'GEN'
    AND rp.primary_flag = 'Y'
    AND ((rp.owner_table_id = v_customer_id
    and rp.owner_table_name = 'HZ_PARTIES')
    or (rp.owner_table_id in (select
    ad.address_id

    from ar_addresses_v ad

    where ad.customer_id = v_customer_id)
    and rp.owner_table_name =
    'HZ_PARTY_SITES')
    or (rp.owner_table_id in (select
    ac.rel_party_id

    from ar_contacts_v ac

    where ac.customer_id = v_customer_id

    and ac.status = 'A')
    and rp.owner_table_name =
    'HZ_PARTIES'))
    AND rownum < 2
    ;

    -- dbms_output.put_line('update customer
    ==>'||v_customer_id);

    UPDATE CAX_CUSTOMERS_ALL
    SET PHONE = nvl(v_phone,' ')
    WHERE customer_id = v_customer_id
    ;

    EXCEPTION
    WHEN NO_DATA_FOUND then null;

    END;

    END LOOP;

    CLOSE c_customer;

    select to_char(sysdate,'DD-MON-RR HH24:MI:SS') into
    v_process from dual;
    dbms_output.put_line('end ==>'||v_process);

    END
    ;
    /
    spool off
    Behind The Success And Failure Of A Man Is A Woman

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Please post available indexes, cardinalities, execution plan.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Thanks...sorry, how do I run execution plan again
    Behind The Success And Failure Of A Man Is A Woman

  4. #4
    Join Date
    Mar 2002
    Posts
    534
    why do you need a LOOP?

    If you would include your SELECT in the UPDATE (or MERGE) it would probably be much faster.

    An example out of the Oracle doc:
    Code:
    UPDATE employees a 
        SET department_id = 
            (SELECT department_id 
                FROM departments 
                WHERE location_id = '2100'), 
            (salary, commission_pct) = 
            (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) 
              FROM employees b 
              WHERE a.department_id = b.department_id) 
        WHERE department_id IN 
            (SELECT department_id 
              FROM departments
              WHERE location_id = 2900 
                  OR location_id = 2700)

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    Quote Originally Posted by yxez
    Thanks...sorry, how do I run execution plan again
    http://www.google.com/search?hl=en&q...e+explain+plan

  6. #6
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Thats what I think of also I have proven thats sqlplus updates faster
    than pl/sql but "they" said here that u get much "cotrol" over the program if
    you do it in pl/sql like error handling

    Thanks
    Behind The Success And Failure Of A Man Is A Woman

  7. #7
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    SQL Plus is not faster than PL/SQL. It is about time to understand that SQL and SQL Plus are different things. :-P
    The update-select is faster since it is called bulk operation. No context switches between PL/SQL engine and SQL engine occurs.
    If you make that update-select into a PL/SQL procedure it will not perform significantly worse than the single update-select statement.
    About the control, yes that's true. If you want to commit on each 1000 rows, or if you want to see exactly update of which row fails and why, then probably that will help, although 10g has some nice features in the bulk operations which helps a lot here BUT without any commit into the loop all you have with the loop is that you will print out the exact row that failed
    Cheers
    Last edited by Bore; 08-23-2007 at 09:50 AM.

  8. #8
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Thanks my friend
    Behind The Success And Failure Of A Man Is A Woman

Posting Permissions

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


Click Here to Expand Forum to Full Width