Skip to main content
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    p_manage_15 slow; slow SQL on z01 table

    • Article Type: General
    • Product: Aleph
    • Product Version: 20

    Description:
    We run p_manage_15 on our production server each weekend, starting at 21:00. Through the end of December it typically ran is 18-20 hours, finishing at about 17:00 on Saturday. We ran it twice in January, with run times of 34 and and 40 hours. From February 10 to date, p_manage_15 has taken 50-54 hours to run. These run times are drawn from Z100:

    Start Finish Elapsed
    ---------------------- ---------------------- -------
    FRI 02-DEC-11 21:13 SAT 03-DEC-11 17:51 20:38
    FRI 09-DEC-11 21:15 SAT 10-DEC-11 17:20 20:05
    FRI 16-DEC-11 21:03 SAT 17-DEC-11 16:20 19:17
    FRI 23-DEC-11 21:02 SAT 24-DEC-11 15:07 18:05
    FRI 30-DEC-11 21:01 SAT 31-DEC-11 16:53 19:52
    FRI 06-JAN-12 21:03 ------------------- -----
    FRI 13-JAN-12 21:02 SUN 15-JAN-12 13:56 40:54
    FRI 20-JAN-12 21:01 SUN 22-JAN-12 07:34 34:32
    FRI 27-JAN-12 21:03 ------------------- -----
    FRI 03-FEB-12 21:01 ------------------- -----
    FRI 10-FEB-12 21:56 MON 13-FEB-12 02:57 53:01
    FRI 17-FEB-12 21:01 MON 20-FEB-12 00:42 51:40
    FRI 24-FEB-12 21:02 SUN 26-FEB-12 23:11 50:08
    FRI 09-MAR-12 21:04 MON 12-MAR-12 03:19 54:15

    The runs on Jan 6, Jan 12, and Feb 3 were all terminated.

    We find that p_arc_01, which we usually run on Saturdays, is much slower when p_manage_15 is running.

    Start Finish Elapsed
    ---------------------- ---------------------- -------
    SAT 03-DEC-11 05:55 SAT 03-DEC-11 12:09 6:14
    TUE 06-DEC-11 02:38 TUE 06-DEC-11 13:43 11:05
    SAT 10-DEC-11 05:55 SAT 10-DEC-11 12:36 6:41
    SAT 17-DEC-11 05:55 SAT 17-DEC-11 12:20 6:25
    SAT 24-DEC-11 05:50 SAT 24-DEC-11 12:34 6:44
    SAT 31-DEC-11 05:53 SAT 31-DEC-11 13:05 7:12
    SAT 07-JAN-12 06:35 SAT 07-JAN-12 16:09 9:34
    SAT 14-JAN-12 05:30 SAT 14-JAN-12 10:25 4:55
    SAT 21-JAN-12 05:31 SAT 21-JAN-12 10:25 4:54
    MON 23-JAN-12 15:00 MON 23-JAN-12 22:04 7:04
    SAT 28-JAN-12 05:30 SAT 28-JAN-12 13:48 8:18
    SAT 04-FEB-12 06:15 SAT 04-FEB-12 15:04 8:49
    SAT 11-FEB-12 07:37 SAT 11-FEB-12 21:37 14:00
    SAT 18-FEB-12 07:24 SAT 18-FEB-12 22:01 14:37
    SAT 25-FEB-12 05:00 SAT 25-FEB-12 20:50 15:50
    SAT 03-MAR-12 06:22 ------------------- -----
    WED 07-MAR-12 14:00 WED 07-MAR-12 23:20 9:20
    SAT 10-MAR-12 05:00 SAT 10-MAR-12 17:40 12:40

    Resolution:
    The site reorganized abc01.z01 on their test server to improve performance. They wrote:

    "I 'rebuilt' the abc01.z01 table in sequential order of the z01_rec_key column values, and 'rebuilt' the indexes on the table. I was curious if this wouild improve the performance of the following p_manage_15 SQL statement, which accounts for a large portion of the job elapsed time.

    select /*+ index(z01 z01_id) */ * from ABC01.z01 where z01_rec_key >= :v1"

    After implementing the changes, p_manage_15 runtime dropped from 21:50 (May 17-18) to 14:44 (May 26-27).

    Our system administrators have also made progress on our response time issues by applying the latest Solaris patches to loon. RMAN backups that were taking over 13 hours now complete in about 1.5 hours. This change was implemented on May 17 (before the May 17-18 p_manage_15 run). We have scheduled patch installation on our production server, libprd, for June 3.

    We think that the combination of the Solaris patches and the Z01 optimizations will lay our performance issues to rest.

    [Later:]
    Our response time issues have improved since our admins installed the most recent Solaris patches on our test and production servers. The patch cluster apparently included some critical ZFS fixes that were relevant to our situation. In combination with the other things we have done, we believe our servers are now back to normal performance levels.


    Keywords: manage_15 p-manage-15 manage-15


    • Article last edited: 10/8/2013
    • Was this article helpful?