Improve performance of PL/SQL

I wrote a PL/SQL block that runs in Oracle 9i while it works on data from a 11g Oracle DB over a DB link. But the code is taking forver to complete. Data processed is around 1000 to 2000 records. I'm posting the code here:

DECLARE
   v_last_comment   VARCHAR2 (255);
   v_check          NUMBER         := 0;

   CURSOR noschedule_progs
   IS
      SELECT /*+ DRIVING_SITE(fcr) */
             fcr.request_id,
             DECODE
                (fcpt.user_concurrent_program_name,
                 'Report Set', fcr.description,
                 'Request Set Stage', fcr.description,
                 fcpt.user_concurrent_program_name
                ) user_concurrent_program_name,
             fcr.description, fcr.argument_text, fcr.concurrent_program_id,
             fcr.parent_request_id, fcr.actual_start_date,
             fcr.actual_completion_date,
             ROUND (  (fcr.actual_completion_date - fcr.actual_start_date)
                    * 24
                    * 60,
                    4
                   ) runtime,
             'No Schedule' program_status, fu.user_name,
             frt.responsibility_name, fcr.logfile_name
        FROM apps.fnd_concurrent_requests@db_link fcr,
             apps.fnd_concurrent_programs_tl@db_link fcpt,
             apps.fnd_user@db_link fu,
             apps.fnd_responsibility_tl@db_link frt
       WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
         AND fcr.requested_by = fu.user_id
         AND fcr.responsibility_id = frt.responsibility_id
         AND fcr.responsibility_application_id = frt.application_id
         AND fcr.phase_code = 'C'
         AND fcr.status_code IN ('C', 'G', 'E', 'X')
         AND fcr.actual_completion_date >= SYSDATE - 1 / 24
         AND fcr.request_id NOT IN (
                             SELECT NVL (parent_request_id, 1)
                               FROM apps.fnd_concurrent_requests@db_link)
         AND fcr.requested_by = 1508715
         AND fcr.request_id NOT IN (SELECT request_id
                                      FROM allen.alert_main
                                     WHERE program_status = 'No Schedule');

   PROCEDURE check_schedule (p_request_id IN NUMBER)
   IS
      v_count             NUMBER (2);
      v_parent_id         NUMBER (15);
      v_last_updated_by   NUMBER (15);
      v_program           VARCHAR2 (255);
      v_parent_program    VARCHAR2 (255);
      v_description       VARCHAR2 (255);
      v_status            VARCHAR2 (2);
   BEGIN
      SELECT NVL (MAX (phase_code), 'X')
        INTO v_status
        FROM apps.fnd_concurrent_requests@db_link
       WHERE request_id = p_request_id;

      IF v_status != 'R'
      THEN
         SELECT NVL (MAX (description), 'None')
           INTO v_description
           FROM apps.fnd_concurrent_requests@db_link
          WHERE request_id = p_request_id;

         SELECT COUNT (*)
           INTO v_count
           FROM apps.fnd_concurrent_requests@db_link
          WHERE parent_request_id = p_request_id
            AND concurrent_program_id =
                          (SELECT concurrent_program_id
                             FROM apps.fnd_concurrent_requests@db_link
                            WHERE request_id = p_request_id)
            AND NVL (description, 'None') = v_description;

         IF v_count = 0
         THEN
            SELECT NVL (MAX (parent_request_id), 1)
              INTO v_parent_id
              FROM apps.fnd_concurrent_requests@db_link
             WHERE request_id = p_request_id;

            SELECT NVL (MAX (last_updated_by), 0)
              INTO v_last_updated_by
              FROM apps.fnd_concurrent_requests@db_link
             WHERE request_id = p_request_id;

            IF v_parent_id = -1
            THEN
               IF v_last_updated_by != 4
               THEN
                  INSERT INTO ikndba.rac_aso_alert_main
                     SELECT /*+ DRIVING_SITE(fcr) */
                            ikndba.rac_aso_alert_seq.NEXTVAL, fcr.request_id,
                            DECODE
                               (fcpt.user_concurrent_program_name,
                                'Report Set', fcr.description,
                                'Request Set Stage', fcr.description,
                                fcpt.user_concurrent_program_name
                               ) user_concurrent_program_name,
                            fcr.argument_text, fcr.concurrent_program_id,
                            fcr.parent_request_id, fcr.actual_start_date,
                            fcr.actual_completion_date,
                            ROUND (  (  fcr.actual_completion_date
                                      - fcr.actual_start_date
                                     )
                                   * 24
                                   * 60,
                                   4
                                  ) runtime,
                            'No Schedule' program_status, fu.user_name,
                            frt.responsibility_name, fcr.logfile_name,
                            SYSDATE, 'Program Not in Schedule', NULL, 'OPEN',
                            'SYSTEM'
                       FROM apps.fnd_concurrent_requests@db_link fcr,
                            apps.fnd_concurrent_programs_tl@db_link fcpt,
                            apps.fnd_user@db_link fu,
                            apps.fnd_responsibility_tl@db_link frt
                      WHERE fcr.concurrent_program_id =
                                                    fcpt.concurrent_program_id
                        AND fcr.requested_by = fu.user_id
                        AND fcr.responsibility_id = frt.responsibility_id
                        AND fcr.responsibility_application_id =
                                                            frt.application_id
                        AND fcr.request_id = p_request_id
                        AND fcr.request_id NOT IN (
                               SELECT request_id
                                 FROM allen.alert_main
                                        WHERE program_status = 'No Schedule');
               commit
               END IF;
            ELSE
               SELECT NVL (MAX (DECODE (fcpt.user_concurrent_program_name,
                                        'Report Set', fcr.description,
                                        'Request Set Stage', fcr.description,
                                        fcpt.user_concurrent_program_name
                                       )
                               ),
                           'Purged'
                          )
                 INTO v_program
                 FROM apps.fnd_concurrent_requests@db_link fcr,
                      apps.fnd_concurrent_programs_tl@db_link fcpt
                WHERE request_id = p_request_id
                  AND fcr.concurrent_program_id = fcpt.concurrent_program_id;

               SELECT NVL (MAX (DECODE (fcpt.user_concurrent_program_name,
                                        'Report Set', fcr.description,
                                        'Request Set Stage', fcr.description,
                                        fcpt.user_concurrent_program_name
                                       )
                               ),
                           'Purged'
                          )
                 INTO v_parent_program
                 FROM apps.fnd_concurrent_requests@db_link fcr,
                      apps.fnd_concurrent_programs_tl@db_link fcpt
                WHERE request_id = v_parent_id
                  AND fcr.concurrent_program_id = fcpt.concurrent_program_id;

               IF v_parent_program = v_program AND v_program != 'Purged'
               THEN
                  INSERT INTO ikndba.rac_aso_alert_main
                     SELECT /*+ DRIVING_SITE(fcr) */
                            ikndba.rac_aso_alert_seq.NEXTVAL, fcr.request_id,
                            DECODE
                               (fcpt.user_concurrent_program_name,
                                'Report Set', fcr.description,
                                'Request Set Stage', fcr.description,
                                fcpt.user_concurrent_program_name
                               ) user_concurrent_program_name,
                            fcr.argument_text, fcr.concurrent_program_id,
                            fcr.parent_request_id, fcr.actual_start_date,
                            fcr.actual_completion_date,
                            ROUND (  (  fcr.actual_completion_date
                                      - fcr.actual_start_date
                                     )
                                   * 24
                                   * 60,
                                   4
                                  ) runtime,
                            'No Schedule' program_status, fu.user_name,
                            frt.responsibility_name, fcr.logfile_name,
                            SYSDATE, 'Program Not in Schedule', NULL, 'OPEN',
                            'SYSTEM'
                       FROM apps.fnd_concurrent_requests@db_link fcr,
                            apps.fnd_concurrent_programs_tl@db_link fcpt,
                            apps.fnd_user@db_link fu,
                            apps.fnd_responsibility_tl@db_link frt
                      WHERE fcr.concurrent_program_id =
                                                    fcpt.concurrent_program_id
                        AND fcr.requested_by = fu.user_id
                        AND fcr.responsibility_id = frt.responsibility_id
                        AND fcr.responsibility_application_id =
                                                            frt.application_id
                        AND fcr.request_id = p_request_id
                        AND fcr.request_id NOT IN (
                                          SELECT request_id
                                            FROM allen.alert_main
                                           WHERE program_status =
                                                                 'No Schedule');

                  COMMIT;
               ELSE
               SELECT NVL (MAX (parent_request_id), 1)
              INTO v_parent_id
              FROM apps.fnd_concurrent_requests@db_link
             WHERE request_id =v_parent_id;
                  check_schedule (v_parent_id);
               END IF;
            END IF;
         END IF;
      END IF;
   END;
BEGIN
   FOR noschedule_progs_row IN noschedule_progs
   LOOP
      check_schedule (noschedule_progs_row.request_id);
   END LOOP;
COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLCODE);
END;
/

Is there a way to improve the efficiency of this program?

Upvotes: 0

Answers (4)

Bob

Reputation: 1

Create or fill a temporary resuslt table on the remote database you also can use MV. Put the insert statement into a sep procedure Don't use the commit for each record put some more comments in the code

Upvotes: 0

Rene

Reputation: 10541

The DRIVING_SITE hint can be useful to optimize distributed queries.

Upvotes: 0

APC

Reputation: 146239

You have posted a long screed of code and no actual details (performance profile, data volumes, business logic, etc). So the best you can hope for is some general observations.

Your procedure contains several complex queries which share a common attribute: almost all the tables are based on a remote database. You cannot tune these queries.

Or rather you cannot tune them remotely. You need to tune the queries locally, that is in the database where the tables reside. Once you have tuned the queries you can build views around them, and use those in your procedure.

In fact, that would be a good starting point. Often the performance problem of distributed queries is the cost of bringing unnecessary data across the database link. It is a lot more efficient to filter data in the home database and only transport filtered data sets.

It is extremely likely that you are paying this transport cost because you are joining those remote tables to a local table, allen.alert_main. So you need to handle that: either include it in the views (as a remote table), or remove the sub-query from the view and just reference it in the procedure.

I suppose the remote database is an Oracle Apps system. That may pose some restrictions on what you're allowed to do.

Another possible inefficiency: execute selects multiple times instead of once. This ...

        SELECT NVL (MAX (parent_request_id), 1)
          INTO v_parent_id
          FROM apps.fnd_concurrent_requests@db_link
         WHERE request_id = p_request_id;

        SELECT NVL (MAX (last_updated_by), 0)
          INTO v_last_updated_by
          FROM apps.fnd_concurrent_requests@db_link
         WHERE request_id = p_request_id;

... could be this ...

        SELECT NVL (MAX (parent_request_id), 1), NVL (MAX (last_updated_by), 0)
          INTO v_parent_id, v_last_updated_by
          FROM apps.fnd_concurrent_requests@db_link
         WHERE request_id = p_request_id;

Although it's a guess: sometimes two separate reads are more efficient than a combined read, depending on indexes, etc. But you do that kind of thing a lot, so it is worth investigating.

Upvotes: 1

planben

Reputation: 700

it seems that this table : apps.fnd_concurrent_requests is getting selected several times in this procedure. try to place the relevent data in a WITH clause at the procedure's start to avoid unnecessary I/O on this table. you can find more information about the WITH clause here

Upvotes: 0

Improve performance of PL/SQL

Answers (4)

Related Questions