adpatch worker details – where time goes?

I suppose everyone who’s reading this article has met adpatch, so some words about Oracle Application patching, how much time spent on it. If the patch is small or You have huge time gap available to apply patch on production then most probably You’re not interested why it’s take so much time as it takes. But in situations where You have small time gap, patch is huge or You’re performing some upgrades and You need estimates most probably You want to know where adpatch spent time.

Of course this information can be found into adpatch and adworker log files but this information is very hard to get out of them. Second place to look is the application itself under OAM -> Applied patches section. Al thought information here is in detailed level, for DBA’s it’s hard to see full picture. There is very detailed information how much time adpatch spent compiling invalids or put information into tables but this information is not relevant at this point of view. We are more interested into workers who actually did the job and from this perceptive we can’t obtain full picture from AOM, we have check each applied file one by one.

I was very interested how much time each adworker spent on each assignment to check can something can be tuned or skipped. Also I was very interested when I do next patching how much time I can leave long running adworker to run before start to check them at the DB level. To find this information I did trace on OAM I found some key tables from where information is gathered and wrote simple SQL that get all workers during one adpatch session and orders them by time spent on assignment.

select TRUNC(t.ELAPSED_TIME * 24 * 60) MINUTES,
       t.PROGRAM_RUN_ID,
       t.job_name,
       t.product,
       t.worker_id,
       t.phase,
       t.phase_name,
       t.arguments,
       t.start_time,
       t.end_time,
       t.subdirectory,
       t.command
  from applsys.AD_PROGRAM_RUN_TASK_JOBS t
 WHERE PROGRAM_RUN_ID in
       (select program_run_id
          from AD_PATCH_RUNS
         where patch_top like '%<patch_location>%')
 ORDER BY ELAPSED_TIME DESC;

Here is sample output:

What advantages can we have if we know this information?

  • First during some bigger patch or upgrade You can check support.oracle.com knowledge base if someone already don’t have some performance problems with this particular adworker job, maybe there is some fix or know-how how to make run this task faster, also for some products I saw articles when it’s possible to run task manually after upgrade if this is related to huge data fix or movement
  • Second, next time we run this patch at test instance we can wait for this job and trace it or do whatever else to find why it’s running so long
  • Third during long upgrades we can plan time when we actually don’t have to check adpatch output and get some rest

With this SQL statement also merged patch tasks can be found as SQL where clause is patch_top not actual patch number (usually I run adpatch  from path where patch is unzipped, so patch number will be always into path) so if You have other standard then key here is program_run_id.

Currently best adworker task improvement I did is to save 1124 minutes from pure upgrade time by skipping long-running tasks which are not needed (found by support.oracle.com or service requests) or postponing some tasks from single r12 upgrade driver.

Advertisements
    • Chandrasekhar
    • August 28th, 2011

    Excellent Work…It really Helpful for us to find time spent for each file on adpatch.

    Thanks – Chandrasekhar

    • Thanks! If You’re doing large updates You can query applsys.AD_PROGRAM_RUN_TASK_JOBS for other useful information as well, for example each phase start_date, time spent on phase and always be informed whether patching is smooth or something is wrong.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: