1 2 3 4 Previous Next 49 Replies Latest reply: Jul 9, 2009 2:13 PM by Hoek RSS

    Please help me to tune this PL/SQL...

    phani marella
      Hi everyone,
      
          I have a SQL query which runs ok when i run it individually but the same query if 
      i use it in a procedure.The procedure is hanging up.Could someone please help to tune this 
      SQL query and please check my procedure why is it hanging up.
      
      
      SQL Query
      =========
      
      SELECT active_members.member_nbr,
        active_members.name_last,
        active_members.name_first,
        active_members.name_middle,
        active_members.dob,
        active_members.sex,
        active_members.subsciber_nbr,
        active_members.ssn,
        active_members.name_suffix,
        active_members.class_x,
        active_members.aff_nbr,
        (
      CASE
      WHEN TRIM(active_members.class_x) = 'SE' THEN
          (SELECT DISTINCT(mssp.member_nbr)
           FROM member_span mssp
           WHERE SUBSTR(mssp.member_nbr,    1,    9) = SUBSTR(active_members.member_nbr,    1,    9)
           AND mssp.class_x = 'SP'
           AND rownum = 1)
         WHEN TRIM(active_members.class_x) = 'SP' THEN
            (SELECT DISTINCT(mssp.member_nbr)
             FROM member_span mssp
             WHERE SUBSTR(mssp.member_nbr,    1,    9) = SUBSTR(active_members.member_nbr,    1,    9)
             AND mssp.class_x = 'SE'
             AND rownum = 1)
        ELSE
          NULL
         END)
      spouse_member_nbr,
        active_members.division_nbr,
        active_members.ymdeff,
        active_members.ymdend,
        active_members.actual_ymd_enddt,
        active_members.email_id,
        active_members.network_id,
        active_members.insurance_company_code,
        active_members.cob_flag,
        active_members.vip_flag,
        active_members.pre_x_flag,
        active_members.region,
        active_contracts.language_x,
        active_contracts.corp_nbr,
        active_members.group_nbr,
        active_members.non_erisa_status
      FROM
        (SELECT mb_active.member_nbr,
           mb_active.contract_nbr,
           mb_active.name_last,
           mb_active.name_first,
           mb_active.name_middle,
           ms_active.ymdeff,
           ms_active.ymdend,
           to_char(to_date(
         CASE
         WHEN LENGTH(mb_active.ymdbirth) = 8 THEN mb_active.ymdbirth
         ELSE NULL
         END,    'YYYYMMDD'),    'MM/DD/YYYY') dob,
           mb_active.sex,
           to_char(to_date(ms_active.ymdeff,    'YYYYMMDD'),    'MM/DD/YYYY') ymdeff_formatted,
           to_char(to_date(ms_active.ymdend,    'YYYYMMDD'),    'MM/DD/YYYY') ymdend_formatted,
           ms_active.void,
          (
         CASE
         WHEN SUBSTR(mb_active.member_nbr,    10,    2) = '00' THEN mb_active.member_nbr
         ELSE SUBSTR(mb_active.member_nbr,    1,    9) || '00'
         END) subsciber_nbr,
           mb_active.ssn,
           mb_active.name_suffix,
           ms_active.class_x,
           ms_active.aff_nbr,
           ms_active.division_nbr,
          (
         CASE
         WHEN TRIM(ms_active.ymdend) = '99991231' THEN NULL
         ELSE to_char(to_date(ms_active.ymdend,    'YYYYMMDD'),    'MM/DD/YYYY')
         END) actual_ymd_enddt,
          (
         CASE
         WHEN TRIM(ms_active.business_unit) = '01' THEN ms_active.business_unit || '-' || ms_active.prog_nbr
         WHEN TRIM(ms_active.business_unit) = '03' THEN ms_active.business_unit || '-' || ms_active.prog_nbr || '-' || ms_active.carrier
         ELSE NULL
         END) network_id,
           ms_active.business_unit || '-' || ms_active.prog_nbr || '-' || ms_active.carrier insurance_company_code,
            (SELECT DISTINCT(email)
           FROM dbo.av_mem_email
           WHERE dbo.av_mem_email.member_nbr = mb_active.member_nbr
           AND rownum = 1)
        email_id,
           mb_active.lr_response cob_flag,
           mb_active.record_nbr vip_flag,
           ms_active.pre_exist pre_x_flag,
           ms_active.region region,
           ms_active.group_nbr,
          (
         CASE
         WHEN
          (SELECT TRIM(div.div_status)
           FROM division div
           WHERE TRIM(div.division_nbr) = TRIM(ms_active.division_nbr)) = 'NULL' THEN
            'Y'
           ELSE
            'N'
           END)
        non_erisa_status
         FROM member mb_active,
           member_span ms_active
         WHERE mb_active.member_nbr = ms_active.member_nbr
         AND(20090707 BETWEEN ms_active.ymdeff
         AND ms_active.ymdend
         AND TRIM(ms_active.void) IS NULL 
        ))
      active_members,
          (SELECT DISTINCT(contract.contract_nbr),
           contract.language_x,
           contract_span.corp_nbr
         FROM contract,
           contract_span
         WHERE contract.contract_nbr = contract_span.contract_nbr
         AND(20090707 BETWEEN contract_span.ymdeff
         AND contract_span.ymdend)
         AND TRIM(contract_span.void) IS
        NULL)
      active_contracts
      WHERE TRIM(active_members.contract_nbr) = TRIM(active_contracts.contract_nbr);
      
      Taking around 6 minute to run and it returns """"268267"""" records
      
      Explain Plan for the above SQL:
      ===============================
      
      
      
      "PLAN_TABLE_OUTPUT"
      "Plan hash value: 379550299"
      " "
      "--------------------------------------------------------------------------------------------------------"
      "| Id  | Operation                      | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |"
      "--------------------------------------------------------------------------------------------------------"
      "|   0 | SELECT STATEMENT               |               |  2609K|   659M|       | 91679   (3)| 00:18:21 |"
      "|   1 |  HASH UNIQUE                   |               |     1 |    16 |       | 54461   (2)| 00:10:54 |"
      "|*  2 |   COUNT STOPKEY                |               |       |       |       |            |          |"
      "|*  3 |    TABLE ACCESS FULL           | MEMBER_SPAN   | 12891 |   201K|       | 54459   (2)| 00:10:54 |"
      "|   4 |    HASH UNIQUE                 |               |     1 |    16 |  2424K| 54736   (2)| 00:10:57 |"
      "|*  5 |     COUNT STOPKEY              |               |       |       |       |            |          |"
      "|*  6 |      TABLE ACCESS FULL         | MEMBER_SPAN   | 51541 |   805K|       | 54459   (2)| 00:10:54 |"
      "|   7 |  HASH UNIQUE                   |               |     1 |    50 |       |    64   (4)| 00:00:01 |"
      "|*  8 |   COUNT STOPKEY                |               |       |       |       |            |          |"
      "|*  9 |    TABLE ACCESS FULL           | AV_MEM_EMAIL  |     1 |    50 |       |    63   (2)| 00:00:01 |"
      "|* 10 |  TABLE ACCESS FULL             | DIVISION      |     1 |    14 |       |     3   (0)| 00:00:01 |"
      "|* 11 |  HASH JOIN                     |               |  2609K|   659M|       | 91679   (3)| 00:18:21 |"
      "|  12 |   VIEW                         |               |   581 | 12782 |       |  8184   (4)| 00:01:39 |"
      "|  13 |    HASH UNIQUE                 |               |   581 | 26145 |       |  8184   (4)| 00:01:39 |"
      "|  14 |     TABLE ACCESS BY INDEX ROWID| CONTRACT      |     1 |    14 |       |     2   (0)| 00:00:01 |"
      "|  15 |      NESTED LOOPS              |               |   581 | 26145 |       |  8183   (4)| 00:01:39 |"
      "|* 16 |       TABLE ACCESS FULL        | CONTRACT_SPAN |   581 | 18011 |       |  7019   (5)| 00:01:25 |"
      "|* 17 |       INDEX RANGE SCAN         | CONTRACT_IX1  |     1 |       |       |     1   (0)| 00:00:01 |"
      "|* 18 |   HASH JOIN                    |               |   449K|   104M|    39M| 83466   (2)| 00:16:42 |"
      "|* 19 |    TABLE ACCESS FULL           | MEMBER_SPAN   |   449K|    34M|       | 54964   (3)| 00:11:00 |"
      "|  20 |    TABLE ACCESS FULL           | MEMBER        |  1436K|   221M|       | 14664   (2)| 00:02:56 |"
      "--------------------------------------------------------------------------------------------------------"
      " "
      "Predicate Information (identified by operation id):"
      "---------------------------------------------------"
      " "
      "   2 - filter(ROWNUM=1)"
      "   3 - filter(SUBSTR("MSSP"."MEMBER_NBR",1,9)=SUBSTR(:B1,1,9) AND "MSSP"."CLASS_X"='SP')"
      "   5 - filter(ROWNUM=1)"
      "   6 - filter(SUBSTR("MSSP"."MEMBER_NBR",1,9)=SUBSTR(:B1,1,9) AND "MSSP"."CLASS_X"='SE')"
      "   8 - filter(ROWNUM=1)"
      "   9 - filter("AV_MEM_EMAIL"."MEMBER_NBR"=:B1)"
      "  10 - filter(TRIM("DIV"."DIVISION_NBR")=TRIM(:B1))"
      "  11 - access(TRIM("MB_ACTIVE"."CONTRACT_NBR")=TRIM("ACTIVE_CONTRACTS"."CONTRACT_NBR"))"
      "  16 - filter("CONTRACT_SPAN"."YMDEFF"<=20090707 AND TRIM("CONTRACT_SPAN"."VOID") IS NULL AND "
      "              "CONTRACT_SPAN"."YMDEND">=20090707)"
      "  17 - access("CONTRACT"."CONTRACT_NBR"="CONTRACT_SPAN"."CONTRACT_NBR")"
      "  18 - access("MB_ACTIVE"."MEMBER_NBR"="MS_ACTIVE"."MEMBER_NBR")"
      "  19 - filter(TRIM("MS_ACTIVE"."VOID") IS NULL AND "MS_ACTIVE"."YMDEFF"<=20090707 AND "
      "              "MS_ACTIVE"."YMDEND">=20090707)"
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      SAME SQL IN A PROCEDURE..IT IS HANGING UP
      ========================================
      
      create or replace PROCEDURE TEST_CURRENT_PCP_SPAN is 
       EXTRACTED_STRING VARCHAR2(32767);
       FILEHANDLER UTL_FILE.FILE_TYPE;
       test_str varchar2(100);
       
       pcp_eff_date number(10);
       file_name varchar2(50);
         CURSOR MEMBERS
         IS 
            
      SELECT active_members.member_nbr,
        active_members.name_last,
        active_members.name_first,
        active_members.name_middle,
        active_members.dob,
        active_members.sex,
        active_members.subsciber_nbr,
        active_members.ssn,
        active_members.name_suffix,
        active_members.class_x,
        active_members.aff_nbr,
        (
      CASE
      WHEN TRIM(active_members.class_x) = 'SE' THEN
          (SELECT DISTINCT(mssp.member_nbr)
           FROM member_span mssp
           WHERE SUBSTR(mssp.member_nbr,    1,    9) = SUBSTR(active_members.member_nbr,    1,    9)
           AND mssp.class_x = 'SP'
           AND rownum = 1)
         WHEN TRIM(active_members.class_x) = 'SP' THEN
            (SELECT DISTINCT(mssp.member_nbr)
             FROM member_span mssp
             WHERE SUBSTR(mssp.member_nbr,    1,    9) = SUBSTR(active_members.member_nbr,    1,    9)
             AND mssp.class_x = 'SE'
             AND rownum = 1)
        ELSE
          NULL
         END)
      spouse_member_nbr,
        active_members.division_nbr,
        active_members.ymdeff,
        active_members.ymdend,
        active_members.actual_ymd_enddt,
        active_members.email_id,
        active_members.network_id,
        active_members.insurance_company_code,
        active_members.cob_flag,
        active_members.vip_flag,
        active_members.pre_x_flag,
        active_members.region,
        active_contracts.language_x,
        active_contracts.corp_nbr,
        active_members.group_nbr,
        active_members.non_erisa_status
      FROM
        (SELECT mb_active.member_nbr,
           mb_active.contract_nbr,
           mb_active.name_last,
           mb_active.name_first,
           mb_active.name_middle,
           ms_active.ymdeff,
           ms_active.ymdend,
           to_char(to_date(
         CASE
         WHEN LENGTH(mb_active.ymdbirth) = 8 THEN mb_active.ymdbirth
         ELSE NULL
         END,    'YYYYMMDD'),    'MM/DD/YYYY') dob,
           mb_active.sex,
           to_char(to_date(ms_active.ymdeff,    'YYYYMMDD'),    'MM/DD/YYYY') ymdeff_formatted,
           to_char(to_date(ms_active.ymdend,    'YYYYMMDD'),    'MM/DD/YYYY') ymdend_formatted,
           ms_active.void,
          (
         CASE
         WHEN SUBSTR(mb_active.member_nbr,    10,    2) = '00' THEN mb_active.member_nbr
         ELSE SUBSTR(mb_active.member_nbr,    1,    9) || '00'
         END) subsciber_nbr,
           mb_active.ssn,
           mb_active.name_suffix,
           ms_active.class_x,
           ms_active.aff_nbr,
           ms_active.division_nbr,
          (
         CASE
         WHEN TRIM(ms_active.ymdend) = '99991231' THEN NULL
         ELSE to_char(to_date(ms_active.ymdend,    'YYYYMMDD'),    'MM/DD/YYYY')
         END) actual_ymd_enddt,
          (
         CASE
         WHEN TRIM(ms_active.business_unit) = '01' THEN ms_active.business_unit || '-' || ms_active.prog_nbr
         WHEN TRIM(ms_active.business_unit) = '03' THEN ms_active.business_unit || '-' || ms_active.prog_nbr || '-' || ms_active.carrier
         ELSE NULL
         END) network_id,
           ms_active.business_unit || '-' || ms_active.prog_nbr || '-' || ms_active.carrier insurance_company_code,
            (SELECT DISTINCT(email)
           FROM dbo.av_mem_email
           WHERE dbo.av_mem_email.member_nbr = mb_active.member_nbr
           AND rownum = 1)
        email_id,
           mb_active.lr_response cob_flag,
           mb_active.record_nbr vip_flag,
           ms_active.pre_exist pre_x_flag,
           ms_active.region region,
           ms_active.group_nbr,
          (
         CASE
         WHEN
          (SELECT TRIM(div.div_status)
           FROM division div
           WHERE TRIM(div.division_nbr) = TRIM(ms_active.division_nbr)) = 'NULL' THEN
            'Y'
           ELSE
            'N'
           END)
        non_erisa_status
         FROM member mb_active,
           member_span ms_active
         WHERE mb_active.member_nbr = ms_active.member_nbr
         AND(20090707 BETWEEN ms_active.ymdeff
         AND ms_active.ymdend
         AND TRIM(ms_active.void) IS NULL 
         
        ))
      active_members,
          (SELECT DISTINCT(contract.contract_nbr),
           contract.language_x,
           contract_span.corp_nbr
         FROM contract,
           contract_span
         WHERE contract.contract_nbr = contract_span.contract_nbr
         AND(20090707 BETWEEN contract_span.ymdeff
         AND contract_span.ymdend)
         AND TRIM(contract_span.void) IS
        NULL)
      active_contracts
      WHERE TRIM(active_members.contract_nbr) = TRIM(active_contracts.contract_nbr);
      
      
        TYPE MEM IS TABLE OF MEMBERS%ROWTYPE INDEX BY PLS_INTEGER;
        TABLE_MEM MEM;
      MEMBER_ADDR   MGONZALEZ.CPKG_UTIL.ADDR;
      
        
      BEGIN  
         test_str := '''A10000213'''||','||'''A10000213'''; 
         insert into test_number_char(str) values ('start time of MEMBER_LOAD_CURRENT_PCP_SPAN '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
         commit;
         file_name := 'member_load'||to_char(sysdate,'YYYYMMDDHH24MI')||'.txt';
         FILEHANDLER := UTL_FILE.FOPEN('AVMED_UTL_FILE',file_name, 'W',10000);
         
         insert into test_number_char(str) values ('start time of opening members cursor(before open members command) '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
         commit;
         OPEN MEMBERS;
        
      LOOP
      
            FETCH MEMBERS 
               BULK COLLECT INTO TABLE_MEM LIMIT 1000 ;
             EXIT WHEN TABLE_MEM.COUNT = 0; 
      insert into test_number_char(str) values ('start time of outer loop '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
      commit;
       
            FOR i IN 1 .. TABLE_MEM.COUNT 
            LOOP
             
            EXTRACTED_STRING := TRIM(TABLE_MEM(i).MEMBER_NBR)||'| '||      
                                TRIM(TABLE_MEM(i).NAME_LAST)||'| '||       
                              TRIM(TABLE_MEM(i).NAME_FIRST)||'| '||       
                               TRIM(TABLE_MEM(i).NAME_MIDDLE)||'| '||    
                               TRIM(TABLE_MEM(i).ssn)||'| '||             
                               TABLE_MEM(i).subsciber_nbr||'| '||         
                               TRIM(TABLE_MEM(i).class_x)||'| '||          
                               TRIM(TABLE_MEM(i).DOB)||'| '||              
                               TRIM(TABLE_MEM(i).SEX)||'| ' ;              
                                 
      
      
      
                EXTRACTED_STRING   :=
                      EXTRACTED_STRING ||
                    TRIM(TABLE_MEM(i).aff_nbr)||'| '||                          
                                  pcp_eff_date||'| '||                  
                TABLE_MEM(i).actual_ymd_enddt||'| '||                         
                    TRIM(TABLE_MEM(i).division_nbr)||'| '||                     
                    ' '||'| '||                                                
                    ' '||'| '||                                                   
                    ' '||'| '||                                                  
                    TABLE_MEM(i).network_id||'| '||                               
                    ' '||'| '||                                                   
                    ' '||'| '||                                                   
                    ' '||'| '||                                                   
                    ' '||'| '||                                                  
                    ' '||'| '||                                                   
                   TRIM(TABLE_MEM(i).name_suffix)||'| '||                        
                   ' '||'| '||                                                    
                   TRIM(TABLE_MEM(i).spouse_member_nbr)||'| '||                   
                   ' '||'| '||                                                    
                   ' '||'| '||                                                    
                   ' '||'| '||                                                   
                   ' '||'| '||                                                    
                   ' '||'| '||                                                   
                   ' '||'| '||                                                    
                   ' '||'| '||                                                    
                   ' '||'| '||                                                    
                   ' '||'| '||                                                    
                   ' '||'| '||                                                    
                   ' '||'| '||                                                    
                   ' '||'| '||                                                   
                   ' '||'| '||                                                    
                  TRIM(TABLE_MEM(i).email_id)||'| '||                                 
                  TABLE_MEM(i).Insurance_company_code||'| '||                     
                  TABLE_MEM(i).group_nbr||'| '||                                  
                  TABLE_MEM(i).language_x||'| '||                                
                  TABLE_MEM(i).region||'| '||                                     
                  TABLE_MEM(i).corp_nbr||'| '||                                   
                  TABLE_MEM(i).non_erisa_status||'| '||                           
                  TABLE_MEM(i).cob_flag||'| '||                                   
                  TABLE_MEM(i).pre_x_flag||'| '||                                 
                  TABLE_MEM(i).vip_flag                                        
                  ;
                   
      
      
                   EXTRACTED_STRING   := rtrim(EXTRACTED_STRING,' ');
             
              UTL_FILE.PUT_LINE(FILEHANDLER,EXTRACTED_STRING,TRUE);
              
              EXTRACTED_STRING := NULL; 
              pcp_eff_date := NULL;
      
            END LOOP;
            insert into test_number_char(str) values ('end time of outer loop '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
       commit;
      END LOOP;
      
       close members;
       insert into test_number_char(str) values ('end time of opening members cursor '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
       commit;
       
       commit;
       
         UTL_FILE.FCLOSE(FILEHANDLER);
         
         insert into test_number_char(str) values ('End time of MEMBER_LOAD_CURRENT_PCP_SPAN '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
         commit;
         
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line(   'ERROR getting members '
                                    || SQLCODE
                                    || ' '
                                    || SQLERRM);
      END ;
      
      
      In my Test table which i am inserting to check the times...
      I am geting on these 2 records after that it hangs up...
      
      start time of MEMBER_LOAD_CURRENT_PCP_SPAN 07/08/2009 11:41:21
      start time of opening members cursor(before open members command) 07/08/2009 11:41:21
      I have to call some other functions for each member to get additional details thats the reason i am going for Procedure.Instead it is just a simple SQL

      Thanks in advance
        • 1. Re: Please help me to tune this PL/SQL...
          Centinul
          It is probably the row-by-row processing that is being done that is slowing it down. However take a look at the DBMS_PROFILER package which can be used to check out PL/SQL code to see where the bottlenecks are.

          HTH!
          • 2. Re: Please help me to tune this PL/SQL...
            phani marella
            Hi Centinul,

            Thanks for the reply ..Is there anything wrong that i am doing?

            If i run the SQL it is not taking that much time but the same thing in a procedure hangs up..

            any other suggestions please..to find the cause for this..

            thanks
            • 3. Re: Please help me to tune this PL/SQL...
              RPuttagunta
              Let me ask you this:

              Why are you using a collection instead of just using the cursor to put into utl_file?
              • 4. Re: Please help me to tune this PL/SQL...
                Hoek
                Hi Phani

                any other suggestions please..to find the cause for this..
                Like Centinul already mentioned, you could use DBMS_PROFILER to measure.
                Or have a DBA trace/tkprof a session that runs that code to identify what's going on.

                At a glance: I'd try to lose the inner loop, if possible...
                • 5. Re: Please help me to tune this PL/SQL...
                  phani marella
                  Hi Hoek,

                  Do you any simple example that show how to use DBMS_PROFILER? I never used it. I will go through it,but please post any example if you have .

                  Thanks
                  phani
                  • 6. Re: Please help me to tune this PL/SQL...
                    SanjayRs
                    Example of Dbms_profiler

                    http://db-oracl.blogspot.com/2008/12/plsql-profiler-usage.html

                    SS
                    • 7. Re: Please help me to tune this PL/SQL...
                      Hoek
                      Hi,


                      A simple example...
                      Perhaps this:
                      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:458240723799#51296126448092

                      just noticed Sanjay's simpler example ;)

                      But first of all we need to know whether it is installed or whether you or your DBA need to install the neccesary tables etc.

                      What do you get when you:
                      desc PLSQL_PROFILER_RUNS
                      ?

                      And, depending on your development tools: PL/SQL Developer has profiling more or less built-in, by the way.

                      (Personally I use trace far more often than profiling)
                      • 8. Re: Please help me to tune this PL/SQL...
                        phani marella
                        Hi sanjay,


                        thanks for that link..i will try that..

                        sanjay and hoek..i have one more question for you..Did you check the explain plan for my query? what do you say about it? Please suggest me if i need to change anything in the SQL query..

                        Thanks
                        • 9. Re: Please help me to tune this PL/SQL...
                          phani marella
                          Hi hoek,sanjay..

                          I have following plsql_profiler_runs,plsql_profiler_units,plsql_profiler_data in my database.
                          I am running it in the following way..
                          I have a question on this...Do i have run my procedure? It is taking lot of time..Is there any way that we can profile it without runnning the procedure as we do for Explain Plan for any SQL query..what i mean is ...is it possible to find out the issues without running the proc?

                          Ex:
                          something like this..
                          Explain plan for select * from emp;
                          DECLARE
                          vrun NUMBER;
                          BEGIN
                          vrun := SYS.DBMS_PROFILER.start_profiler ('TESTRUN1');
                          DBMS_OUTPUT.put_line ('START PROFILER STATUS ' || vrun);
                          MEMBER_LOAD_CURRENT_PCP_SPAN(); -- calling procedure
                          vrun := SYS.DBMS_PROFILER.stop_profiler;
                          DBMS_OUTPUT.put_line ('STOP PROFILER STATUS ' || vrun);
                          DBMS_OUTPUT.put_line ('0 successful');
                          DBMS_OUTPUT.put_line ('1 incorrect parameter');
                          DBMS_OUTPUT.put_line ('2 data flush operation failed');
                          DBMS_OUTPUT.put_line ('-1 version mismatch between package and tables');
                          END;
                          Thanks
                          phani
                          • 10. Re: Please help me to tune this PL/SQL...
                            Hoek
                            Please suggest me if i need to change anything in the SQL query..
                            From your post I understand that your query runs OK, but when you use it in your procedure things aren't OK anymore.
                            There's a difference between them:
                            You have a loop in a loop in your procedure.
                            It seems like the inner loop is extracting a string, doing some additional formatting (TRIMming, adding spaces), from your outer loop.
                            You should check if it's possible to select the correctly formatted sting in your outer query rightaway and get rid of the inner query.


                            Regarding the explain plan:

                            " 11 - access(TRIM("MB_ACTIVE"."CONTRACT_NBR")=TRIM("ACTIVE_CONTRACTS"."CONTRACT_NBR"))"

                            Takes quite some time...
                            Don't know nothing about your indexes, but perhaps a function based index for both columns might reduce time here...
                            • 11. Re: Please help me to tune this PL/SQL...
                              Hoek
                              It is taking lot of time
                              You could perhaps split it up in smaller, isolated parts.
                              Running certain steps separate.
                              ..is it possible to find out the issues without running the proc?
                              Other than have other people on OTN give their ideas/share experiences?
                              Not that I'm aware of.
                              There are lots of statistical views, you might be able to deduct from their data, but I personally prefer running the procedure and just trace it. But I'm always willing to learn new approaches, btw.


                              Still, you have 2 things to look at, at least:

                              - avoid loops in loops if possible
                              - check your predicates, if you apply a function, you lose the index, unless you create a function based index.


                              edit

                              One more thing (how could I overlook that):
                              You commit your instrumentation code ( insert into test_number_char(str) ) IN the loop.
                              Please remove commit in your loops...

                              Commit only once, at the end of your transaction.
                              Never commit in a loop.
                              (Preferrably the client commits)


                              And remove every commit in your loop...


                              Edited by: hoek on Jul 8, 2009 8:03 PM
                              • 12. Re: Please help me to tune this PL/SQL...
                                SanjayRs
                                Phani,

                                You don't have to run all the loop you can run one full loop and exit out or stop the loop, then you can examine the output of the profiler runs and analyze where the time is spend and check for expected number for executions for each statements.

                                You should find out the top 80% time taking sql statements are only 20% of the sql or less.


                                SS
                                • 13. Re: Please help me to tune this PL/SQL...
                                  phani marella
                                  sanjay,

                                  sorry to bug you again...

                                  i didnt get this statement..
                                  You should find out the top 80% time taking sql statements are only 20% of the sql or less
                                  could you please clarify above statement..

                                  Thanks
                                  phani
                                  • 14. Re: Please help me to tune this PL/SQL...
                                    Tubby
                                    How are you determining that this is 'hanging up'.

                                    What are you using to run the stored procedure?

                                    I ask because i see...
                                       EXCEPTION
                                          WHEN OTHERS
                                          THEN
                                             DBMS_OUTPUT.put_line(   'ERROR getting members '
                                                                  || SQLCODE
                                                                  || ' '
                                                                  || SQLERRM);
                                    END ;
                                    Which would lead me to believe you're running this procedure through say SQLDEVELOPER and then checking your 'log' table and not seeing more data being put in there, but since you've used this exception non-catch (it will swallow errors and DO NOTHING) you're under the impression it's still running, when in all actuality you've hit some unexpected error.

                                    It's entirely possible i'm wrong, but try removing the exception block and see what happens....
                                    1 2 3 4 Previous Next