Category Archives: Performance Tuning

New Release 12.2.11 Upgrade Guide Available!

Posted on by

Check out my most recent book: Oracle E-Business Suite: the little r12.2.11 upgrade essentials for managers and team members. It’s available on Amazon at https://www.amazon.com/s?k=978-1387919871

Here’s a description:

This is the latest edition of Mike Swing’s book on upgrading to Oracle E-Business Suite R12.2.11. The book covers the nuances and lessons learned from many upgrades performed since Release 12 was first released.

Putting together the right team to tackle the Oracle E-Business Suite R12.2.11 upgrade, and understanding the issues that the team needs to consider to be successful, can be quite a challenge. “the little r12.2.11 upgrade essentials for managers and team members” describes the big picture of what you need to consider before tackling the Release 12.2.11 upgrade. Based on TruTek’s popular R11i to R12 Technical Upgrade training classes, this book describes what managers, functional, and technical team members need to know to prepare to upgrade from Release 11i to Release 12.2.11 of Oracle’s E-Business Suite of Applications. Enhanced topics with this edition include: Online Patching, the ADOP Patching Cycle, Materialized Views, Customizations, Development Standards for Edition Based Redefinition, How Cross-Edition Triggers Work, and Understanding the Release 12.2 Architecture.

10 Ways to Leave your Upgrade, Faster

Posted on by

This blog is based on a RadiOAUG broadcast on April 9, 2013 at Collaborate in Denver.

10 Ways to Speed up your Upgrade.

1) Fast IO – Solid State Drive for the database files

2) Don’t do an in-place upgrade. This allows you to measure the upgrade on the actual machine you’ll use for the production upgrade.

3) Lots of disk space to stage files. Stage the 11i files so you don’t have to clone them before the upgrade. Stage the R1211 files and the post 1213 files, with all the customizations.

4) Try increasing parallel_max_servers to see if this allows greater parallelization.

5) Don’t use a threaded CPU architecture for the database server.

6) If you do use a threaded CPU server, use Automatic Degree of Parallelism (ADOP) to increase the parallelism. There are three modes: Manual, Limited and Auto. We tested Manual and Auto, and had the best results with Auto.

7) XDOLoader – entropy issue. Create a link to /dev/urandom called /dev/random. Note: 1065393.1.

8) Disable ARCHIVELOG Mode and Auditing

9) Use trace to diagnose performance issues:

oradebug setospid OSPID-number
oradebug unlimit
oradebug event 10046 trace name context forever, level 12
oradebug tracefile_name

Run tkprof on the tracefile and examine the tkprof output.

10) There are lots of functional scripts and datafixes that can speed your upgrade: facpupg.sql and glrsgup2.sql are two examples that allow you to run depreciation in advance and prepare posted journals before the upgrade.

Read more about it in my book, the little r12.1.3 tuning and troubleshooting guide for Oracle E-Business Suite

littler1213tuningFRONT200x155bueborder

 

Performance tuning the R12.1.3 Upgrade

Posted on by

Question from EBS DBA:

We are up to our sixth pass and are on our seventh and perhaps last one. The past two times have taken longer than the previous ones (I’m talking like 8-12 hours per on the 12.1 and 12.1.3 patches). Doesn’t seem like a lot but when you only have 72 hours it’s a lot!

I think I may know why but wanted to run it by you. We avoided running the statistics steps in order to save some time up front. Thinking about it now, could this end up adding hours to the two main patches?

I think yes, it could be huge. We are also looking at the SAN as well but thought I would start here.

Answer from Mike:

Yes, statistics can be very helpful. You should run these upfront, adstats.

Second, take a look at V$SYSTEM_EVENT, it will tell you what your wait events are. This is cumulative, so stop your database before you start the upgrade and it will refresh the table.

Your SAN is probably the biggest problem. Get everyone out of the SAN during the upgrade; you are sharing bandwidth. Also, ask your SAs if they can increase the stripe size for the upgrade. If you can stripe across 8-12 disks during the upgrade and the reduce that after the upgrade. The reason is concurrency. When you run the upgrade with the stripe size going across 12 disks, you’ll light up the whole SAN with the upgrade. Also, spread your mount points across the SAN so that you are using more disks and aren’t using just one or two mount points with a very limited number of disks. If you can light up the whole SAN you’ll have better throughput. However, reduce this after he upgrade, because with multiple users, they will be waiting for each other; waiting for each other’s transactions to complete. Transactions will be very fast with lots of mount points with more disks, but everyone will be waiting. As long as it’s just the upgrade running more disks with more disks in each stripe will improve you IOPS.

Also, check your network. You may be waiting on your network. If you have multiple DNS servers, test how long it takes to return an address from all the DNS servers. If you have a bad DNS address, SQLNet will wait 1 minute before trying the next DNS address. For a 3 second SQL transaction, this can be devastating.

Use fewer workers if you have poor IO and more workers if you have fast IO. For example, with just a few disks, use the same number of workers (3-4) as you have disks. With a large number of disks use 16 workers. However, this depends on the number of CPUs you have. More workers will help the compile stage. So, use about the same number of workers as you have CPUs, if you have lots of disks. If you have only a few disks, more CPUs and more workers will make the problem worse.

Slow TNS connections due to Network issue

Posted on by

Using OEL and 11g, an old / invalid DNS ip address in the first line of resolv.conf adds 60 seconds to each connection, causes very slow LOCAL=NO connections. The ip address in resolv.conf are checked from the top down. If the first IP Address is BAD, it waits 60 seconds to timeout. During the EBS 1211 MP it adds about 35 hours with 125K jobs, for a total of 53 hours. It runs in 18 hours using the correct DNS server in resolv.conf. It happens that the DB and AS are on the same server and should logically use a BEQUETH connection to eliminate the network performance issue.

The other option is to turn off DNS in the /etc/nsswitch.conf.