Solved

Performance Issues with PL/SQL Select Count(*)

Posted on 2004-10-29
1,295 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
Question by:narveer
    5 Comments
     
    LVL 34

    Expert Comment

    by:markgeer
    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:


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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    The Complete Ruby on Rails Developer Course

    Ruby on Rails is one of the most popular web development frameworks, and a useful tool used by both startups and more established companies to build strong graphic user interfaces, and responsive websites and apps.

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    This video shows how to recover a database from a user managed backup
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now