Home » RDBMS Server » Performance Tuning » Need help in tuning this procedure
Need help in tuning this procedure [message #327536] Mon, 16 June 2008 16:57 Go to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
DECLARE

CURSOR Cur_sub_rp IS
SELECT A.SUB_ACCOUNT, B.PH_basic_srv,B.PH_Salesman,A.SUB_SSN
FROM STG_SUB_MASTER_MONTH_HISTORY A, STG_PHN_MASTER_MONTH_HISTORY
B
WHERE A.SUB_ACCOUNT = B.PH_ACCOUNT (+)
AND A.MONTH_ID = B.MONTH_ID ;

TYPE t_values_tab IS TABLE OF cur_sub_rp%rowtype ;
values_tab t_values_tab := t_values_tab() ;

BEGIN
OPEN Cur_sub_rp ;
LOOP
FETCH Cur_sub_rp BULK COLLECT INTO Values_tab
LIMIT 1000;
EXIT WHEN Cur_sub_rp%NOTFOUND ;
END LOOP ;
CLOSE Cur_sub_rp;
FORALL i IN VALUES_TAB.first..values_tab.last
INSERT INTO SUB_PHN_1 VALUES VALUES_TAB(i);
commit;
END;

The total process is taking 19 minutes to execute.
each table has 9million records.
can anyone help me tune the query.

Re: Need help in tuning this procedure [message #327537 is a reply to message #327536] Mon, 16 June 2008 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

http://www.orafaq.com/forum/t/84315/74940/
which of the suggestions in URL above have you tried & what were the results?
Re: Need help in tuning this procedure [message #327538 is a reply to message #327536] Mon, 16 June 2008 17:06 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Don't use a procedure, use pure SQL.
Re: Need help in tuning this procedure [message #327539 is a reply to message #327537] Mon, 16 June 2008 17:07 Go to previous messageGo to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member

Thanks a lott for letting me know my mistake. Now the code shoul be readable

DECLARE

CURSOR Cur_sub_rp IS
                SELECT A.SUB_ACCOUNT,  B.PH_basic_srv,B.PH_Salesman,A.SUB_SSN
                    FROM STG_SUB_MASTER_MONTH_HISTORY A, STG_PHN_MASTER_MONTH_HISTORY
 B 
                    WHERE A.SUB_ACCOUNT = B.PH_ACCOUNT (+)
                    AND A.MONTH_ID = B.MONTH_ID ;
                    
 TYPE t_values_tab IS TABLE OF cur_sub_rp%rowtype ;
 values_tab t_values_tab := t_values_tab()  ;

BEGIN 
    OPEN Cur_sub_rp ;
    LOOP 
        FETCH Cur_sub_rp BULK COLLECT INTO Values_tab
                                            LIMIT 1000;
        EXIT WHEN Cur_sub_rp%NOTFOUND ;
    END LOOP ;
    CLOSE Cur_sub_rp;
FORALL i IN VALUES_TAB.first..values_tab.last
INSERT INTO SUB_PHN_1 VALUES VALUES_TAB(i);
commit;
END;
Re: Need help in tuning this procedure [message #327540 is a reply to message #327536] Mon, 16 June 2008 17:07 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
post an EXPLAIN_PLAN for the SELECT.

How long does the SELECT run?
Re: Need help in tuning this procedure [message #327541 is a reply to message #327539] Mon, 16 June 2008 17:30 Go to previous messageGo to next message
elucian
Messages: 1
Registered: June 2008
Location: Chicago
Junior Member
Hi,

I'm not shore what you wish to do, but your loop is traversing all records and the insert is writing only the last 1000 records or less.

If you wish to join two tables and create the third, do not use PL/SQL loop wich is slow even if you use bulk binding. My advice is to use the following insert statement.

INSERT /*+APPEND*/ INTO SUB_PHN_1
SELECT A.SUB_ACCOUNT, B.PH_basic_srv,B.PH_Salesman,A.SUB_SSN
FROM STG_SUB_MASTER_MONTH_HISTORY A, STG_PHN_MASTER_MONTH_HISTORY B
WHERE A.SUB_ACCOUNT = B.PH_ACCOUNT (+)
AND A.MONTH_ID = B.MONTH_ID ;
COMMIT;

I hope this will help you.
can you explain where i went wrong. [message #327551 is a reply to message #327536] Mon, 16 June 2008 20:59 Go to previous messageGo to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
DECLARE

CURSOR Cur_sub_rp IS SELECT 
                sub.MARKET_CODE        ,

                        sub.SUB_CTYPE ,
                        phn.PH_BASIC_SRV        ,
                        sub.MONTH_ID  ,
                         sub.SUB_ACCOUNT        ,
                        sub.SUB_LAST_NAME     ,
                       sub.SUB_FIRST_NAME   ,
                        SUB.WS_ENTRY_DATE AS SUB_WS_ENTRY_DATE   ,
                        sub.WS_DISC_DATE       ,
                        sub.SUB_DISC_REASON ,
                        sub.SUB_BILL_CYCLE    ,
                        sub.SUB_MUN_CODE     ,
                        sub.SUB_30DAY_BAL     ,
                        sub.SUB_60DAY_BAL     ,
                        sub.SUB_90DAY_BAL     ,
                        sub.SUB_OVR90_BAL     ,
                        sub.SUB_WRTOFF_AMT ,
                        sub.SUB_BALANCE_DUE ,
                        sub.SUB_CURRENT_BAL ,
                        sub.SUB_STATUS,
                        phn.PH_MOBIL_ID         ,
                        phn.WS_ESN_UN           ,
                        phn.WS_START_DATE    ,
                        phn.WS_STOP_DATE      ,
                        PHN.WS_ENTRY_DATE AS PHN_WS_ENTRY_DATE   ,
                        phn.PH_STATUS            ,
                        phn.PH_FEAT_CODES     ,
                        phn.PH_MODEL  ,
                        phn.CRICKET_LOCATION_ID_KEY           ,
                        phn.FEATURE_SET_ID   
                      
                    FROM STG_SUB_MASTER_MONTH_HISTORY SUB, STG_PHN_MASTER_MONTH_HISTORY PHN
 
                    WHERE 
                    SUB.MONTH_ID = PHN.MONTH_ID
                    And SUB.SUB_ACCOUNT = PHN.PH_ACCOUNT (+)
                    And SUB.Sub_Account BETWEEN 0 AND 79552866226 ;
                    
                    
 TYPE t_values_tab IS TABLE OF cur_sub_rp%rowtype ;
 values_tab t_values_tab := t_values_tab()  ;

BEGIN 
    OPEN Cur_sub_rp ;
    LOOP 
        FETCH Cur_sub_rp BULK COLLECT INTO Values_tab
                                            LIMIT 1000;
        EXIT WHEN Cur_sub_rp%NOTFOUND ;
    END LOOP ;
    CLOSE Cur_sub_rp;
FORALL i IN VALUES_TAB.first..values_tab.last
INSERT INTO SUB_PHONE_RATEPLAN_FSS VALUES VALUES_TAB(i);
COMMIT;
END;


this was running for 30 mins and i cancelled the executing.
when select query is executed on the table at insert into it shows no records inspite of commit at the end.
Re: Need help in tuning this procedure [message #327643 is a reply to message #327536] Tue, 17 June 2008 02:27 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Firstly, I do not understand, why you LOOP the cursor, as the VALUES_TAB variable is rewritten in each cycle. See the demonstration:
SQL> select count(*) from user_objects;

  COUNT(*)
----------
      4321

SQL> set serveroutput on
SQL> DECLARE
  2    CURSOR Cur_sub_rp IS
  3      SELECT OBJECT_NAME
  4      FROM USER_OBJECTS;
  5                      
  6    TYPE t_values_tab IS TABLE OF cur_sub_rp%rowtype ;
  7    values_tab t_values_tab := t_values_tab();
  8  BEGIN 
  9    OPEN Cur_sub_rp ;
 10    LOOP 
 11        FETCH Cur_sub_rp BULK COLLECT INTO Values_tab
 12                                      LIMIT 1000;
 13        EXIT WHEN Cur_sub_rp%NOTFOUND ;
 14    END LOOP ;
 15    CLOSE Cur_sub_rp;
 16  
 17    DBMS_OUTPUT.PUT_LINE( 'Collection has '
 18      || to_char( values_tab.count ) ||' rows.' );
 19  END;
 20  /
Collection has 321 rows.

PL/SQL procedure successfully completed.

SQL> 

Secondly, you join two big tables on MONTH_ID and *_ACCOUNT. Do these column form primary or unique key in one of these tables? If no, how many rows in both tables have the same (MONTH_ID, *_ACCOUNT) values?
By the way, the use of (+) operator in the condition on *_ACCOUNT is ignored and both tables are inner joined.
Previous Topic: Extents and blocks
Next Topic: Performance issues
Goto Forum:
  


Current Time: Sun Dec 04 23:04:47 CST 2016

Total time taken to generate the page: 0.04046 seconds