Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Performance Issues with PL/SQL Select Count(*)

Posted on 2004-10-29
5
Medium Priority
?
1,339 Views
Last Modified: 2009-02-03
Hi,

I am executing a stored procedure which is taking around 9 hours to execute. The reason for that is there are millions of records in the tables.The stored procedure performs very simple operation. It just finds the count for each and every agents with different conditions. I want to bring down the execution time from 9 hours to atleast 4 hours.Please suggest.

Here is the stored procedure:

CREATE OR REPLACE PROCEDURE TOTAL_CALLS_PER_AGENT(p_start_date IN DATE , p_end_date IN DATE)
AS

/* -------------------------------------------------------------------------- */
/* Declare Variables */
/* -------------------------------------------------------------------------- */

      v_agent_id            NUMBER(10);
      v_forename            app_agent.forename%TYPE;
      v_surname            app_agent.surname%TYPE;
      v_access_id            NUMBER(10);
      v_centre_code      app_agent.centre_code%TYPE;
      v_channel_code  app_agent.channel_code%TYPE;
      v_team_id            NUMBER(10);
      v_duty_id            app_agent.duty_identity%TYPE;
      v_total_calls   NUMBER(10);
      v_provides            NUMBER(10);
      v_ceases            NUMBER(10);
      v_transfers            NUMBER(10);
      v_changes            NUMBER(10);
      v_records            NUMBER(10);
      v_removes            NUMBER(10);
      v_acc_enq            NUMBER(10);
      v_gen_enq            NUMBER(10);
      v_q_enq                  NUMBER(10);
      v_ord_enq            NUMBER(10);
      v_lit_req            NUMBER(10);

/* -------------------------------------------------------------------------- */
/* Declare Cursors */
/* -------------------------------------------------------------------------- */
-- All the agents in the centre
      CURSOR cur_agents IS
                                   SELECT agent_id
                              FROM app_agent
                              WHERE active_flag='Y'
                              AND access_id<=2
                              ORDER by centre_code, team_id;
/* -------------------------------------------------------------------------- */
-- ** MAIN BODY OF CODE
/* -------------------------------------------------------------------------- */

BEGIN
dbms_output.enable(100000);

dbms_output.put_line
('Forename,Surname,access_id,centre_code,channel_code,team_id,duty_id,total_calls,provides,ceases,transfers,changes,records,removes,acc_enq,general_enq,quick_enq,order_enq,lit_req');

      LOOP  
      IF NOT cur_agents%isopen THEN
            OPEN cur_agents;
      END IF;
      FETCH cur_agents INTO v_agent_id;
      IF cur_agents%NOTFOUND THEN
            CLOSE cur_agents;
            EXIT;
      ELSE

      -- Get the agent details
      SELECT forename, surname, access_id, centre_code, channel_code, team_id, duty_identity
      INTO v_forename, v_surname, v_access_id, v_centre_code, v_channel_code, v_team_id, v_duty_id
      FROM app_agent
      WHERE agent_id = v_agent_id;

      -- Calculate total number of calls
      v_total_calls := 0;
      SELECT  count(*)
      INTO v_total_calls
      FROM app_work_case awc, app_call_detail acd
      WHERE awc.call_id = acd.call_id
      AND acd.agent_id = v_agent_id
      AND awc.date_started >= p_start_date
      AND awc.date_started <= p_end_date
      AND awc.date_ended is not null;

    -- Calculate Total Number Of Provides
      v_provides := 0;
    SELECT  count(*)
    INTO v_provides
    FROM app_work_case awc, app_call_detail acd, app_provide_order apo
    WHERE awc.call_id=acd.call_id
      AND awc.work_case_id = apo.work_case_id
      AND apo.service_order_number is not null
      AND acd.agent_id = v_agent_id
      AND awc.date_started >= p_start_date
      AND awc.date_started <= p_end_date
      AND awc.date_ended is not null
      AND awc.work_type_id = 9;

    -- Calculate Total Number Of Ceases
      v_ceases := 0;
    SELECT  count(*)
    INTO v_ceases
    FROM app_work_case awc, app_call_detail acd, app_cease_order aco
    WHERE awc.call_id=acd.call_id
      AND awc.work_case_id=aco.work_case_id
      and aco.service_order_no is not null
      AND acd.agent_id = v_agent_id
      AND awc.date_started >= p_start_date
      AND awc.date_started <= p_end_date
      AND awc.date_ended is not null
      AND awc.work_type_id = 10;

        -- Calculate Total Number Of Transfers
      v_transfers := 0;
    SELECT  count(*)
    INTO v_transfers
    FROM app_work_case awc, app_call_detail acd, app_transfer_order ato
    WHERE awc.call_id=acd.call_id
      AND awc.work_case_id=ato.work_case_id
      AND ato.service_order_number is not null
      AND acd.agent_id = v_agent_id
      AND awc.date_started >= p_start_date
      AND awc.date_started <= p_end_date
      AND awc.date_ended is not null
      AND awc.work_type_id = 11;

        -- Calculate Total Number Of Changes
      v_changes := 0;
    SELECT  count(*)
    INTO v_changes
    FROM app_work_case awc, app_call_detail acd, app_change_order aco
    WHERE awc.call_id=acd.call_id
      AND awc.work_case_id=aco.work_case_id
      AND aco.service_order_no is not null
      AND acd.agent_id = v_agent_id
      AND awc.date_started >= p_start_date
      AND awc.date_started <= p_end_date
      AND awc.date_ended is not null
      AND awc.work_type_id = 13;

        -- Calculate Total Number Of Records
      v_records := 0;
    SELECT  count(*)
    INTO v_records
    FROM app_work_case awc, app_call_detail acd, app_record_order aro
    WHERE awc.call_id=acd.call_id
      AND awc.work_case_id=aro.work_case_id
      AND aro.service_order_no is not null
      AND acd.agent_id = v_agent_id
      AND awc.date_started >= p_start_date
      AND awc.date_started <= p_end_date
      AND awc.date_ended is not null
      AND awc.work_type_id = 14;

    -- Calculate Total Number Of Removes
      -- Not a Real Total
      v_removes := 0;
    SELECT  count(*)
    INTO v_removes
    FROM app_work_case awc, app_call_detail acd
    WHERE awc.call_id=acd.call_id
      AND acd.agent_id = v_agent_id
      AND awc.date_started >= p_start_date
      AND awc.date_started <= p_end_date
      AND awc.date_ended is not null
      AND awc.work_type_id = 12;

      -- Calculate Total Number Of Account Enquiries
      v_acc_enq := 0;
    SELECT  count(*)
    INTO v_acc_enq
    FROM app_work_case awc, app_call_detail acd
    WHERE awc.call_id=acd.call_id
      AND acd.agent_id = v_agent_id
      AND awc.date_started >= p_start_date
      AND awc.date_started <= p_end_date
      AND awc.date_ended is not null
      AND awc.work_type_id = 2;

      -- Calculate Total Number Of General Enquiries
      v_gen_enq := 0;
    SELECT  count(*)
    INTO v_gen_enq
    FROM app_work_case awc, app_call_detail acd
    WHERE awc.call_id=acd.call_id
      AND acd.agent_id = v_agent_id
      AND awc.date_started >= p_start_date
      AND awc.date_started <= p_end_date
      AND awc.date_ended is not null
      AND awc.work_type_id = 4;

      -- Calculate Total Number Of Quick Enquiries
      v_q_enq := 0;
    SELECT  count(*)
    INTO v_q_enq
    FROM app_work_case awc, app_call_detail acd
    WHERE awc.call_id=acd.call_id
      AND acd.agent_id = v_agent_id
      AND awc.date_started >= p_start_date
      AND awc.date_started <= p_end_date
      AND awc.date_ended is not null
      AND awc.work_type_id = 22;

      -- Calculate Total Number Of Order Enquiries
      v_ord_enq := 0;
    SELECT  count(*)
    INTO v_ord_enq
    FROM app_work_case awc, app_call_detail acd
    WHERE awc.call_id=acd.call_id
      AND acd.agent_id = v_agent_id
      AND awc.date_started >= p_start_date
      AND awc.date_started <= p_end_date
      AND awc.date_ended is not null
      AND awc.work_type_id = 6;

      -- Calculate Total Number Of Literature Request
      v_lit_req := 0;
    SELECT  count(*)
    INTO v_lit_req
    FROM app_work_case awc, app_call_detail acd
    WHERE awc.call_id=acd.call_id
      AND acd.agent_id = v_agent_id
      AND awc.date_started >= p_start_date
      AND awc.date_started <= p_end_date
      AND awc.date_ended is not null
      AND awc.work_type_id = 17;

      dbms_output.put_line
        (v_forename||','||v_surname||','||v_access_id||','||v_centre_code||','||v_channel_code||
            ','||v_team_id||',"'||v_duty_id||'",'||v_total_calls||','||v_provides||','||v_ceases||
            ','||v_transfers||','||v_changes||','||v_records||','||v_removes||','||v_acc_enq||
            ','||v_gen_enq||','||v_q_enq||','||v_ord_enq||','||v_lit_req);
      END IF;
      END LOOP;
END TOTAL_CALLS_PER_AGENT;
/
0
Comment
Question by:narveer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12443667
There are multiple ways this can be improved.  The easiest way is to combine your last six queries into one, since they involve the same two tables and just differ on the value for: "awc.work_type_id".  Having six separate queries though forces Oracle to read the same sets of records six times, when it could do the job in just one pass like this:

SELECT  awc.work_type_id, count(*)
    INTO [work_type], [qty]
    FROM app_work_case awc, app_call_detail acd
    WHERE awc.call_id=acd.call_id
     AND acd.agent_id = v_agent_id
     AND awc.date_started >= p_start_date
     AND awc.date_started <= p_end_date
     AND awc.date_ended is not null
     AND awc.work_type_id in (2,4,6,12,17,22)
     group by awc.work_type;

You will have to declare variables for [work_type] and [qty] and write "if ...elsif..." statements to assign the value of [qty] to the appropriate variable, but this will be *MUCH* faster than forcing Oracle to read these records six times.

Another possible change that is more involved would require partitioning the app_work_case table by "date_started" maybe by day, week, month or quarter, depending on what range of values your query usually uses.

Obviously the tables should be indexed on the columns used for the joins.
0
 
LVL 5

Accepted Solution

by:
Pontis earned 1000 total points
ID: 12443895


  You are selecting from same tables over and over again...


    Consider this:

 SELECT  count(*) to_calls ,
              sum(decode(awc.work_type_id,12,1,0)) removes,
             sum(decode(awc.work_type_id,2,1,0))  acc_enq,
             sum(decode(awc.work_type_id,4,1,0))  gen_enq,
             sum(decode(awc.work_type_id,22,1,0))  q_enq
     INTO v_total_calls, v_removes, v_acc_enq, v_gen_enq, v_q_enq
     FROM app_work_case awc, app_call_detail acd
     WHERE awc.call_id = acd.call_id
     AND acd.agent_id = v_agent_id
     AND awc.date_started >= p_start_date
     AND awc.date_started <= p_end_date
     AND awc.date_ended is not null;


  I think you get the idea... This will put seven of your queries into one. You can probably even add the other five by outer joining the other tables referenced there and  using a bit more complex decodes for the appropriate counts, such as:

sum(decode(awc.work_type_id, 9,decode(nvl(apo.service_order_number,0),0,0,1),0)) v_provides

This way you can have a single statement that will get you all the details for each agent. And going further, you can combine the main cursor (the agents you loop through) and this statement, so that you have a single SQL that does all the work.... This is what SQL is for... :)
Be sure that Oracle will do all the joining and looping much faster when it is in single SQL than doing it through PL/SQL loops... There is an overhead in using PL/SQL and in your case because of the volume it does add up to your 9 hours....
I think that with some tuning your report can be put into single SQL and run probably under 1 hour...

Another thing you might consider is actually building a materialized view that will have your values precomputed. With materialized view logs on your base tables you can have fast refresh capability, where Oracle will need only compute the values for new records since last refresh...

Pontis
0
 

Author Comment

by:narveer
ID: 12449658
Hi Pontis/markgeer,
I used your advice and I rewrote the SQL according to your suggestions.But still its taking around 5 Hours to run. I cant make any database changes as I am not a Sys Admin or a DBA and I am not authorized to do that.I have to work within my limits where I cant make any schema or database changes.

Can you please advice me some tuning tricks so that I can bring it down to 2 hours? Thanks in Advance.

Heres the Plan which was generated by toad:

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

SELECT STATEMENT Hint=CHOOSE            1               97                                              
  SORT AGGREGATE            1        200                                                     
    FILTER                                                                        
      NESTED LOOPS            1        200        97                                              
        NESTED LOOPS            1        181        96                                              
          NESTED LOOPS            1        168        96                                              
            NESTED LOOPS            1        149        94                                              
              NESTED LOOPS            1        130        93                                              
                HASH JOIN            1        111        91                                              
                  TABLE ACCESS BY INDEX ROWID      APP_CALL_DETAIL      130        3 K      2                                              
                    NESTED LOOPS            69        3 K      4                                              
                      TABLE ACCESS FULL      APP_AGENT      1        28        2                                              
                      INDEX RANGE SCAN      APP_CALL_DETAIL_IND_01      130               1                                              
                  TABLE ACCESS FULL      APP_WORK_CASE      14        798        86                                              
                TABLE ACCESS BY INDEX ROWID      APP_RECORD_ORDER      1        19        2                                              
                  INDEX RANGE SCAN      CHK_APP_RECORD_ORDER_01      1               1                                              
              TABLE ACCESS BY INDEX ROWID      APP_PROVIDE_ORDER      1        19        1                                              
                INDEX UNIQUE SCAN      PK_APP_PROVIDE_ORDER      1                                                            
            TABLE ACCESS BY INDEX ROWID      APP_CHANGE_ORDER      1        19        2                                              
              INDEX RANGE SCAN      APP_CHANGE_ORDER_IND_02      1               1                                              
          INDEX UNIQUE SCAN      PK_APP_TRANSFER_ORDER      1        13                                                     
        TABLE ACCESS BY INDEX ROWID      APP_CEASE_ORDER      1        19        1                                              
          INDEX UNIQUE SCAN      PK_APP_CEASE_ORDER      1                                                            

Heres the stored Procedure:

CREATE OR REPLACE PROCEDURE TOTAL_CALLS_PER_AGENT(p_start_date IN DATE , p_end_date IN DATE)
AS

/* -------------------------------------------------------------------------- */
/* Declare Variables */
/* -------------------------------------------------------------------------- */

      v_agent_id            NUMBER(10);
      v_forename            app_agent.forename%TYPE;
      v_surname            app_agent.surname%TYPE;
      v_access_id            NUMBER(10);
      v_centre_code      app_agent.centre_code%TYPE;
      v_channel_code  app_agent.channel_code%TYPE;
      v_team_id            NUMBER(10);
      v_duty_id            app_agent.duty_identity%TYPE;
      v_total_calls   NUMBER(10) := 0;
      v_provides            NUMBER(10) := 0;
      v_ceases            NUMBER(10) := 0;
      v_transfers            NUMBER(10) := 0;
      v_changes            NUMBER(10) := 0;
      v_records            NUMBER(10) := 0;
      v_removes            NUMBER(10) := 0;
      v_acc_enq            NUMBER(10) := 0;
      v_gen_enq            NUMBER(10) := 0;
      v_q_enq                  NUMBER(10) := 0;
      v_ord_enq            NUMBER(10) := 0;
      v_lit_req            NUMBER(10) := 0;

/* -------------------------------------------------------------------------- */
/* Declare Cursors */
/* -------------------------------------------------------------------------- */
-- All the agents in the centre
      CURSOR cur_agents IS
                                   SELECT agent_id
                              FROM app_agent
                              WHERE active_flag='Y'
                              AND access_id<=2
                              ORDER by centre_code, team_id;
/* -------------------------------------------------------------------------- */
-- ** MAIN BODY OF CODE
/* -------------------------------------------------------------------------- */

BEGIN
dbms_output.enable(100000);

dbms_output.put_line
('Forename,Surname,access_id,centre_code,channel_code,team_id,duty_id,total_calls,provides,ceases,transfers,changes,records,removes,acc_enq,general_enq,quick_enq,order_enq,lit_req');

      LOOP  
      IF NOT cur_agents%isopen THEN
            OPEN cur_agents;
      END IF;
      FETCH cur_agents INTO v_agent_id;
      IF cur_agents%NOTFOUND THEN
            CLOSE cur_agents;
            EXIT;
      ELSE
      
      dbms_output.put_line(v_agent_id);
      
            SELECT  
                  app.forename,
                  app.surname,
                  app.access_id,
                  app.centre_code,
                  app.channel_code,
                  app.team_id,
                  app.duty_identity,
                  count(*) tot_calls ,             
                  nvl(sum(decode(awc.work_type_id,12,1,0)),0) removes,
                  nvl(sum(decode(awc.work_type_id, 9,decode(nvl(apo.service_order_number,'0'),'0',0,1),0)),0) provides,
                  nvl(sum(decode(awc.work_type_id, 10,decode(nvl(aco.service_order_no,'0'),'0',0,1),0)),0) ceases,
                  nvl(sum(decode(awc.work_type_id, 11,decode(nvl(apo.service_order_number,'0'),'0',0,1),0)),0) transfers,
                  nvl(sum(decode(awc.work_type_id, 13,decode(nvl(acho.service_order_no,'0'),'0',0,1),0)),0) changes,
                  nvl(sum(decode(awc.work_type_id, 14,decode(nvl(aro.service_order_no,'0'),'0',0,1),0)),0) records,
                  nvl(sum(decode(awc.work_type_id,2,1,0)),0)  acc_enq,
                  nvl(sum(decode(awc.work_type_id,4,1,0)),0)  gen_enq,
                  nvl(sum(decode(awc.work_type_id,22,1,0)),0)  q_enq,
                  nvl(sum(decode(awc.work_type_id,6,1,0)),0)  ord_enq,
                  nvl(sum(decode(awc.work_type_id,17,1,0)),0)  lit_enq
                INTO v_forename,
                         v_surname,
                         v_access_id,
                         v_centre_code,
                         v_channel_code,
                         v_team_id,
                         v_duty_id,
                          v_total_calls,
                         v_removes,
                         v_provides,
                         v_ceases,
                         v_transfers,
                         v_changes,
                         v_records,
                         v_acc_enq,
                         v_gen_enq,
                         v_q_enq,
                         v_ord_enq,
                         v_lit_req
                 FROM app_work_case awc,
                  app_call_detail acd,
                  app_provide_order apo,
                  app_cease_order aco,
                  app_transfer_order ato,
                  app_change_order acho,
                  app_record_order aro,
                  app_agent app
                 WHERE awc.call_id = acd.call_id
                   AND awc.work_case_id = apo.work_case_id(+)
                  AND awc.work_case_id= aco.work_case_id(+)
                  AND awc.work_case_id= ato.work_case_id(+)
                  AND awc.work_case_id= acho.work_case_id(+)
                  AND awc.work_case_id=aro.work_case_id(+)
                  AND app.agent_id = v_agent_id
                   AND acd.agent_id = v_agent_id
                  AND awc.date_started >= p_start_date
                 AND awc.date_started <= p_end_date
                 AND awc.date_ended is not null
                  GROUP BY app.forename, app.surname, app.access_id, app.centre_code, app.channel_code,
                                 app.team_id, app.duty_identity;


      dbms_output.put_line
        (v_forename||','||v_surname||','||v_access_id||','||v_centre_code||','||v_channel_code||
            ','||v_team_id||',"'||v_duty_id||'",'||v_total_calls||','||v_provides||','||v_ceases||
            ','||v_transfers||','||v_changes||','||v_records||','||v_removes||','||v_acc_enq||
            ','||v_gen_enq||','||v_q_enq||','||v_ord_enq||','||v_lit_req);
      END IF;
      END LOOP;
END TOTAL_CALLS_PER_AGENT;
/
0
 
LVL 5

Expert Comment

by:Pontis
ID: 12451754

 Try this....


CREATE OR REPLACE PROCEDURE TOTAL_CALLS_PER_AGENT(p_start_date IN DATE , p_end_date IN DATE)
AS

/* -------------------------------------------------------------------------- */
/* Declare Variables */
/* -------------------------------------------------------------------------- */

     v_agent_id          NUMBER(10);
     v_forename          app_agent.forename%TYPE;
     v_surname          app_agent.surname%TYPE;
     v_access_id          NUMBER(10);
     v_centre_code     app_agent.centre_code%TYPE;
     v_channel_code  app_agent.channel_code%TYPE;
     v_team_id          NUMBER(10);
     v_duty_id          app_agent.duty_identity%TYPE;
     v_total_calls   NUMBER(10) := 0;
     v_provides          NUMBER(10) := 0;
     v_ceases          NUMBER(10) := 0;
     v_transfers          NUMBER(10) := 0;
     v_changes          NUMBER(10) := 0;
     v_records          NUMBER(10) := 0;
     v_removes          NUMBER(10) := 0;
     v_acc_enq          NUMBER(10) := 0;
     v_gen_enq          NUMBER(10) := 0;
     v_q_enq               NUMBER(10) := 0;
     v_ord_enq          NUMBER(10) := 0;
     v_lit_req          NUMBER(10) := 0;

/* -------------------------------------------------------------------------- */
/* Declare Cursors */
/* -------------------------------------------------------------------------- */
-- All the agents in the centre
     CURSOR cur_agent_data IS

          SELECT  
             app.agent_id,
               app.forename,
               app.surname,
               app.access_id,
               app.centre_code,
               app.channel_code,
               app.team_id,
               app.duty_identity,
               count(*) tot_calls ,          
               nvl(sum(decode(awc.work_type_id,12,1,0)),0) removes,
               nvl(sum(decode(awc.work_type_id, 9,decode(nvl(apo.service_order_number,'0'),'0',0,1),0)),0) provides,
               nvl(sum(decode(awc.work_type_id, 10,decode(nvl(aco.service_order_no,'0'),'0',0,1),0)),0) ceases,
               nvl(sum(decode(awc.work_type_id, 11,decode(nvl(apo.service_order_number,'0'),'0',0,1),0)),0) transfers,
               nvl(sum(decode(awc.work_type_id, 13,decode(nvl(acho.service_order_no,'0'),'0',0,1),0)),0) changes,
               nvl(sum(decode(awc.work_type_id, 14,decode(nvl(aro.service_order_no,'0'),'0',0,1),0)),0) records,
               nvl(sum(decode(awc.work_type_id,2,1,0)),0)  acc_enq,
               nvl(sum(decode(awc.work_type_id,4,1,0)),0)  gen_enq,
               nvl(sum(decode(awc.work_type_id,22,1,0)),0)  q_enq,
               nvl(sum(decode(awc.work_type_id,6,1,0)),0)  ord_enq,
               nvl(sum(decode(awc.work_type_id,17,1,0)),0)  lit_enq
               FROM app_work_case awc,
               app_call_detail acd,
               app_provide_order apo,
               app_cease_order aco,
               app_transfer_order ato,
               app_change_order acho,
               app_record_order aro,
               app_agent app
               WHERE awc.call_id = acd.call_id
                AND awc.work_case_id = apo.work_case_id(+)
               AND awc.work_case_id= aco.work_case_id(+)
               AND awc.work_case_id= ato.work_case_id(+)
               AND awc.work_case_id= acho.work_case_id(+)
               AND awc.work_case_id=aro.work_case_id(+)
               AND acd.agent_id = app.agent_id
               AND awc.date_started >= p_start_date
               AND awc.date_started <= p_end_date
               AND awc.date_ended is not null
               AND app.active_flag='Y'
             AND app.access_id<=2
               GROUP BY app.forename, app.surname, app.access_id, app.centre_code, app.channel_code,
                            app.team_id, app.duty_identity;


/* -------------------------------------------------------------------------- */
-- ** MAIN BODY OF CODE
/* -------------------------------------------------------------------------- */

BEGIN
dbms_output.enable(100000);

dbms_output.put_line
('Forename,Surname,access_id,centre_code,channel_code,team_id,duty_id,total_calls,provides,ceases,transfers,changes,records,removes,acc_enq,general_enq,quick_enq,order_enq,lit_req');

     OPEN cur_agent_data;

     LOOP  
     FETCH cur_agent_data  INTO
                     v_agent_id,
                     v_forename,
                     v_surname,
                     v_access_id,
                     v_centre_code,
                     v_channel_code,
                     v_team_id,
                     v_duty_id,
                      v_total_calls,
                     v_removes,
                     v_provides,
                     v_ceases,
                     v_transfers,
                     v_changes,
                     v_records,
                     v_acc_enq,
                     v_gen_enq,
                     v_q_enq,
                     v_ord_enq,
                     v_lit_req

     IF cur_agent_data%NOTFOUND THEN
          CLOSE cur_agent_data;
          EXIT;
     ELSE
     
     dbms_output.put_line(v_agent_id);
     

     dbms_output.put_line
        (v_forename||','||v_surname||','||v_access_id||','||v_centre_code||','||v_channel_code||
          ','||v_team_id||',"'||v_duty_id||'",'||v_total_calls||','||v_provides||','||v_ceases||
          ','||v_transfers||','||v_changes||','||v_records||','||v_removes||','||v_acc_enq||
          ','||v_gen_enq||','||v_q_enq||','||v_ord_enq||','||v_lit_req);
     END IF;
     END LOOP;
END TOTAL_CALLS_PER_AGENT;
/
0
 

Author Comment

by:narveer
ID: 12453612
Pontis,
Unfortunately cursor doesnt select any rows ie the Number of rows fetched by cursor is zero.But If we can get this piece of code running then i guess we can make the procedure run in less than 30 min.FYI I am looking through 8 million records and 950 agent ids.
The tables I am using are as follows:

SQL> desc app_work_case;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WORK_CASE_ID                              NOT NULL NUMBER(9)
 CUSTOMER_ID                                        NUMBER(9)
 DATE_STARTED                              NOT NULL DATE
 TIME_STARTED                              NOT NULL VARCHAR2(8)
 DATE_ENDED                                         DATE
 TIME_ENDED                                         VARCHAR2(8)
 FOLLOWUP_FLAG                                      VARCHAR2(1)
 WORK_TYPE_ID                                       NUMBER(4)
 WORK_CASE_DURATION                                 NUMBER(9)
 CALL_ID                                            NUMBER(9)

SQL> select count(*) from app_work_case where date_started >= '22-OCT-04';

  COUNT(*)
----------
   3342195

SQL> desc app_call_detail;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CALL_ID                                   NOT NULL NUMBER(9)
 CAMPAIGN_ID                                        NUMBER(4)
 DATE_STARTED                                       DATE
 TIME_STARTED                                       VARCHAR2(8)
 CALL_DURATION                                      NUMBER(5)
 COMP_STAT_ID                                       NUMBER(4)
 NAME_ID_CONTACT                                    NUMBER(9)
 PHONE_NUM_ID_CONTACT                               NUMBER(9)
 DATE_ENDED                                         DATE
 TIME_ENDED                                         VARCHAR2(8)
 AGENT_ID                                           NUMBER(4)
 SYSTEM_ENTRY                                       NUMBER(4)

SQL> select count(*) from app_call_detail where date_started >= '22-OCT-04';

  COUNT(*)
----------
   2284295

SQL> desc app_agent;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AGENT_ID                                  NOT NULL NUMBER(4)
 FORENAME                                  NOT NULL VARCHAR2(20)
 SURNAME                                   NOT NULL VARCHAR2(30)
 USERNAME                                  NOT NULL VARCHAR2(20)
 ACTIVE_FLAG                                        VARCHAR2(1)
 ACCESS_ID                                          NUMBER
 TEAM_ID                                            NUMBER(4)
 CENTRE_CODE                                        VARCHAR2(3)
 CHANNEL_CODE                                       VARCHAR2(3)
 DUTY_IDENTITY                                      VARCHAR2(2)
 EXCEPTION_HANDLER                                  VARCHAR2(1)
 EXTENSION_NO                                       VARCHAR2(6)
 LOGON_STATUS                              NOT NULL NUMBER(1)
 FOLLOWUP_HANDLER                                   CHAR(1)
 PBX_LOGIN                                          NUMBER(6)
 EMAIL_ADDRESS                                      VARCHAR2(50)

SQL> desc app_cease_order;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WORK_CASE_ID                              NOT NULL NUMBER(9)
 SERVICE_ORDER_NO                                   VARCHAR2(9)
 ACCOUNT_NO                                NOT NULL VARCHAR2(8)
 REMARKS                                            VARCHAR2(150)
 SOFT_DIAL_TONE_FLAG                       NOT NULL VARCHAR2(1)
 DISTRICT_CODE                                      VARCHAR2(3)
 CTL_CODE                                           VARCHAR2(4)
 GUIDE_IND                                 NOT NULL VARCHAR2(1)
 DUTY_CODE                                          VARCHAR2(5)
 SPECIAL_SERVICE_IND                       NOT NULL VARCHAR2(1)
 CUSTOMER_TYPE                                      VARCHAR2(3)
 BACKDATE_COMPLETION_DATE                           DATE

SQL> desc app_provide_order;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WORK_CASE_ID                              NOT NULL NUMBER(9)
 SERVICE_ORDER_NUMBER                               VARCHAR2(9)
 ACCOUNT_NUMBER                                     VARCHAR2(8)
 FOLLOW_UP_CREATED                         NOT NULL CHAR(1)
 FLOW_THROUGH                              NOT NULL CHAR(1)
 ORDER_ISSUED                              NOT NULL CHAR(1)
 REMARKS1                                           VARCHAR2(70)
 REMARKS2                                           VARCHAR2(70)
 REMARKS3                                           VARCHAR2(70)
 REMARKS4                                           VARCHAR2(70)
 REMARKS5                                           VARCHAR2(70)
 REMARKS6                                           VARCHAR2(70)
 REMARKS7                                           VARCHAR2(70)
 REMARKS8                                           VARCHAR2(70)
 DEFER_ORDER_FLAG                                   CHAR(1)

SQL> desc app_transfer_order;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WORK_CASE_ID                              NOT NULL NUMBER(9)
 SERVICE_ORDER_NUMBER                               VARCHAR2(9)
 ACCOUNT_NUMBER                                     VARCHAR2(8)
 FOLLOW_UP_CREATED                         NOT NULL CHAR(1)
 ORDER_ISSUED                              NOT NULL CHAR(1)
 REMARKS1                                           VARCHAR2(66)
 REMARKS2                                           VARCHAR2(70)

SQL> desc app_change_order;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                  NOT NULL NUMBER(9)
 WORK_CASE_ID                              NOT NULL NUMBER(9)
 CHANGE_ORDER_TYPE_ID                      NOT NULL NUMBER(9)
 TRANSACTION_ID                                     NUMBER(9)
 DISTRICT_CODE                             NOT NULL VARCHAR2(3)
 ORDER_REMARKS1                                     VARCHAR2(70)
 ORDER_REMARKS2                                     VARCHAR2(70)
 ORDER_REMARKS3                                     VARCHAR2(70)
 ORDER_REMARKS4                                     VARCHAR2(70)
 ORDER_REMARKS5                                     VARCHAR2(70)
 ORDER_REMARKS6                                     VARCHAR2(70)
 ORDER_REMARKS7                                     VARCHAR2(70)
 ORDER_REMARKS8                                     VARCHAR2(70)
 DUE_DATE                                  NOT NULL DATE
 DUE_TIME                                  NOT NULL VARCHAR2(8)
 ACCOUNT_NO                                         VARCHAR2(8)
 USE_GUIDE_FLAG                                     VARCHAR2(1)
 SERVICE_ORDER_NO                                   VARCHAR2(9)
 EXCHANGE_CODE                                      VARCHAR2(4)
 MAIN_STD_CODE                                      VARCHAR2(4)
 MAIN_PHONE_NO                                      VARCHAR2(7)
 PORT_RESERVED_FLAG                                 VARCHAR2(1)
 APPOINTMENT_MADE_FLAG                              VARCHAR2(1)
 DEFER_ORDER_FLAG                                   CHAR(1)
 HI_SPEED_FLAG                                      CHAR(1)

SQL> desc app_record_order;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                  NOT NULL NUMBER(9)
 WORK_CASE_ID                              NOT NULL NUMBER(9)
 RECORD_ORDER_TYPE_ID                      NOT NULL NUMBER(9)
 TRANSACTION_ID                            NOT NULL NUMBER(9)
 ACCOUNT_NO                                         VARCHAR2(8)
 REMARKS1                                           VARCHAR2(70)
 REMARKS2                                           VARCHAR2(70)
 SERVICE_ORDER_NO                                   VARCHAR2(9)
 DISTRICT_CODE                                      VARCHAR2(3)

SQL> select count(*) from app_provide_order;

  COUNT(*)
----------
    101497

SQL>    select count(*) from app_cease_order;

  COUNT(*)
----------
     27406

SQL> select count(*) from app_transfer_order;

  COUNT(*)
----------
     13412

SQL> select count(*) from app_transfer_order;

  COUNT(*)
----------
     13412

SQL> select count(*) from app_change_order;

  COUNT(*)
----------
    152843

SQL> select count(*) from app_record_order;

  COUNT(*)
----------
     14152
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question