OTN Discussion Forums : Please help me to tune this PL/SQL... ...

phani marella

Posts: 306
Registered: 05/24/07

Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 9:12 AM

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.***,
  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.***,
     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.***,
  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.***,
     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).***)||'| ' ;              
                           
 
 
 
          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

Centinul

Posts: 2,675
Registered: 03/04/08

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 9:16 AM

in response to: phani marella

Reply

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!

phani marella

Posts: 306
Registered: 05/24/07

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 9:20 AM

in response to: Centinul

Reply

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

RPuttagunta

Posts: 943
Registered: 01/08/04

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 9:22 AM

in response to: phani marella

Reply

Let me ask you this:

Why are you using a collection instead of just using the cursor to put into utl_file?

hoek

Posts: 4,494
Registered: 05/07/02

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 9:25 AM

in response to: phani marella

Reply

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...

phani marella

Posts: 306
Registered: 05/24/07

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 9:47 AM

in response to: hoek

Reply

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

Sänjay

Posts: 3,022
Registered: 01/08/06

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 9:57 AM

in response to: phani marella

Helpful

Reply

Example of Dbms_profiler

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

SS

hoek

Posts: 4,494
Registered: 05/07/02

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 10:07 AM

in response to: phani marella

Reply

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)

phani marella

Posts: 306
Registered: 05/24/07

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 10:07 AM

in response to: Sänjay

Reply

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

phani marella

Posts: 306
Registered: 05/24/07

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 10:21 AM

in response to: phani marella

Reply

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

hoek

Posts: 4,494
Registered: 05/07/02

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 10:25 AM

in response to: phani marella

Helpful

Reply

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...

hoek

Posts: 4,494
Registered: 05/07/02

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 10:35 AM

in response to: phani marella

Helpful

Reply

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

Sänjay

Posts: 3,022
Registered: 01/08/06

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 10:51 AM

in response to: phani marella

Reply

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

phani marella

Posts: 306
Registered: 05/24/07

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 10:56 AM

in response to: Sänjay

Reply

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

Tubby

Posts: 1,820
Registered: 10/01/01

Re: Please help me to tune this PL/SQL...
Posted: Jul 8, 2009 11:05 AM

in response to: phani marella

Reply

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....