optimize performance during oracle11i upgrade

[Dave Chamberlin]

Hello,
We are working on out upgrade from oracle applications 10.7 to 11i and our database is about 75GB. I have done several test upgrades and have the patching down to 25 hours or so (using rp8400 w/10 CPU and VA7410 disk array). I am looking to see if I can improve the patch time by changing init parameters for the database (block buffers, etc), resizing log files, or adding more spindles to the rp7410. The box has 20GB of RAM. The db_cache_size is 1200M, shared pool is 500M, the java_pool is 60M. I attached a copy of the initORA file. Any suggestions from appreciated.

[Steven E. Protter]

Shalom Dave,

I do not think your patch performance will be significantly improved by changing block size on init.ora or the OS.

A properly tuned box in general will assist in faster execution of this work.

I recommend dbc_max_pct be low and nearly the same as dbc_min_pct to prevent double buffering of oracle data.

Tweaking init.ora may help but I have suggestions other than the negative above.

SEP

[Patti Johnson]

Dave,

Review the upgrade log and find those steps that take the longest - then look for performance patches from Oracle - or tuning steps that you can take yourself.
Also set the number of workers as high as possible - with 10 cpu's you should be able to support 20 workers without a problem.
You can also take the db out of archivelog mode - just make sure you backup before and after the upgrade.

I've done a couple of these upgrades and depending on the products that you have installed (HR, Payroll, AR were problem children for me) you may find significant performance patches, but you need to look at the individual scripts being run by the upgrade to find the ones that need attention.

Patti

[TwoProc · Accepted solution]

Dave, the only thing that springs to mind that could improve things is your db_cache_size (block_buffers) - on a test box - try increasing that to 10G (just for the upgrade). Also, your shared pool is too small for 11i anyways, so go ahead and set it at 1G for the upgrade (and try to leave it at least there afterwards).

This may have anything from a profound impact on your upgrade, to very little impact on the process.

Also, I agree with the previous post about the number of workers, on a 10 way server I'd run with 25 workers (2.5x num cpus), but there is no hard and fast rule on that.

On another note -
Before your upgrade, you should be running some tests for comparison on performance from old to new. I think you need a much bigger buffer_cache for running Apps 11i, and I believe you need quite a bit more in the shared pool (I use 2G). Your java pool might be just a little light, I'd add 15M to it, making it 75M. Use statspack and some testing to see how you are doing for running some processes in the old system versus the new.

Except for running out of room in the box for operational processing, DON'T be afraid of adding more buffer cache, unless your buffer cache hit ratio at the worst period of the day stays steady at 99%, and doesn't drop off (by much if any). You'll hear that it's expensive to run large buffer caches in Oracle, and that you can slow the system down by doing so. I'm guessing that when servers used to run at 25,50, and 75Mhz - it may have been true, but it's certainly not the case any more. Just make sure you KNOW what your ram consumption is AFTER the database is already up and running and users are on it and leave that as headroom (and then some), so that you don't go into swap during the day.


Also, on some of your processes (heck, maybe even lots of them) you'll be missing some indexes that make the queries and data the way you use them run correctly. Some quick reviews of long running code for processes that show the largest slow downs after the upgrade would be good to do before go live. Many of them (but not all) will be solved by creating custom indexes to facilitate processing your seeded reports and activities. This is because the seeded indexes can't fully address how your company chooses to use and report with the tables and system provided by the application.

Good luck!

[Volker Borowski]

Hi Dave,

I'd give a try to configure new UNDO Management and automatic PGA beforehand.
Automatic PGA should give you better memory handling for the SORT-Buffers.

Monitor the Controlfile_write_activity during the upgrade. If you consider to disable archive logging, consider to use a single Controlfile during the upgrade (takes off two sync writes on Commit during upgrade), but DO NOT FORGET to reestablish the mirrorcopies afterwards.

Increase Online Redolog size to avoid checkpoints, you will get one Checkpoint for every Logswitch.
How big are they ?
Check the alertfile for how many checkpoints you encounter in this upgrade.
Check the alertfile for "... not complete"

In case you have plenty of CPUs, increase the number of DB_WR processes.

java_pool seems small, shared pool as well, and you should surely increase db_cache_size.

db_block_checksum is quite expensive I mind to remember. May be you can switch it off for the upgrade ?

Good luck
Volker

[Dave Chamberlin]

Thanks for the replies. I will definitely increase the buffer cache - I have lots of RAM. I reviewed the oracle logfile and there are a couple hundred of the "checkpoint not complete" messages during the main upgrade. The redo logs are 100M. Would you suggest increasing the size of those - if so how much? How expensive are checkpoints? thanks

[TwoProc]

Well, it depends on how often you're switching. But, since you're not always completing them before the next one comes up, it would be really safe to double it, as 200M isn't very large at all, and then see how you're doing.

Also, check that your redo's cache size is large enough.

[Dave Chamberlin]

Do you mean log buffer? If so it is currently 4M. if not - what parameter do you mean? What would you consider optimal?

[Eric Antunes]

Hi Dave,

Redolog sizes are dependent on the activity of each database but you can monitor the redolog switches to check if yours are undersized.

My redo related parameters are:

log_checkpoint_interval = 40960
log_buffer = 1048576
log_checkpoints_to_alert = true
3 groups of 10Mb

Also, during the upgrade, disable timed_statistics...

Best Regards,

Eric Antunes

[Eric Antunes]

After setting log_checkpoints_to_alert to true and restarting the database, you can monitor the log switches with:

$tail -fn15 /.../alert_.log

Best Regards,

Eric Antunes

[TwoProc]

Well, yes 4M is pretty small for the log buffer in my opinion. But, that does depend on the activity. We had a real bottleneck here on that very item (which was set to 10M) and resolved it by setting the logs to 70M. This had a substantial, measurable impact on the servers' ability to handle large loads (no difference in small loads) of data entry and subsequent transactional I/O. Your mileage will vary I'm sure, so review it in the log file by setting the init.ora parameters as described in the previous posting by Eric.

[Yogeeraj_1]

hi Dave, John and Eric,

allow me to also add:
During the upgrade, the data will be constantly trickling out in the background. If your log buffer is too small -- you'll be waiting for lgwr to write the data not only when you commit, but when as you generate redo entries as well. You need sufficient
space for you to be ADDING to the redo buffer as lgwr is writing it out.

Normally, you should look at wait events. If you see lots of log_buffer_space waits, consider making the log_buffer init.ora parameter even bigger.

hope this helps too!
kind regards
yogeeraj

[Eric Antunes]

Hi Yogeeraj,

I read on a Metalink Note that we usually wont benefit from a greater than 1Mb log buffer. I supose this usually word would refer normal loads, not upgrades.

Best Regards,

Eric Antunes

[TwoProc]

Eric, re: 1M log buffer:
(If I may humbly offer some perspective)
One thing I've learned from doing a lot of tuning is that it can be very wrong to "go by the book" and not question settings and rules of thumb that we've all come to use and live by - ESPECIALLY those that seek to exclude tuning opportunities by their nature. I was VERY SURPRISED to learn this lesson. I really believed early on that if I learned and followed all of the conventional wisdom on set up and tuning that I really couldn't go wrong in setting up servers and services. I now believe differently.

Besides tuning individual poor running queries throughout the system, the largest systemic changes that we've gotten large performance returns on have come from tuning areas that many of the dbas (including on-site tuning experts from Oracle) have, by rule of thumb have chosen to exclude as performance opportunities. The reason is that most of the tuning experts expect to come on site and find the "one thing wrong" that you've got on your server that will fix your problem. When, in fact, *IF* you've done your homework and properly laid out your server architecture --- there really isn't anything "wrong" with your server. It becomes merely a case of correctly laying out and allocating the resources properly for each aspect of your server, by making the best possible use of your memory, disk, and I/O systems in the box, and doing all of this in a maintainable, supportable manner. At the same time, you need to be regularly making sure that you've tuned the living heck out of the worst offending code that your programmers (and software providers) happen to be throw at you. If you've done those things above and you're STILL not getting the performance you need, then you can confidently make your case that options for additional hardware should be considered.

[Eric Antunes]

Hi John,

It is always great to share opinions and experiences.

Since I'm a DBA, I've more or less followed those rule os thumb and had no major tuning issues until now. I'd one little performance issue in one LOV until last month and guess what? It was just a missing join... I've analysed that SQL many many times but, for some reason, I didn't see (until last month) that a join was missing. Result: a LOV that use to take 3 minutes now takes just 3 seconds!

The common DBA's mistakes I've assisted are:

- Oversizing the database memory structures like the shared pool and buffer cache, somehow thinking that the database will be the only one who will need the server memory...;

- Ignoring that SQL can and MUST be shared in the shared pool. Of course this is the development job but the DBA must enforce this idea;

- Fragmentation of the tablespaces and datafiles. Why extending the datafile by 100Mb if YOU KNOW you will need 1Gb until the end of the next year?? About tablespaces, indexes are always rebuildable but tables aren't unless by export/import. A DBA with a database having tables with more than 50 extents and doing nothing to correct the situation is sure to have troubles with performance in the future...

If after those 3 topics the DBA is still in trouble with performance, then it may be the time to forget those Oracle advices but until then I don't recommend...

PS: Sorry Dave to use your thread to talk about other topics than yours.

Best Regards,

Eric

[Yogeeraj_1]

hi Eric,

you will agree with me that with LMTs and Autoextend features, nowadays we no longer worry about about sizing tablespaces and fragmentation...

coming back to the log buffers issue, i still think that it should be sized accordingly since redo may be generated at a rate higher than lgwr can write it to disk. So the lgwr falls behind (more redo is created than can
be written in some period of time).

It could be that there is insufficiently sized redo logs (resulting in waits for log
file switches).

It is most probable that this monster operation will be generating tons of redo in a big burst and must wait for lgwr to catch up.

Anyway, i believe in oversizing some parameters just for the sake of the upgrade and later re-sizing them back to "normal" afterwards...

kind regards
yogeeraj

[Eric Antunes]

Yogeeraj,

I totaly agree with you for RDBMS version 9i or above but most of the fragmentation problems come from the past with earlier versions. Does LMT solve the fragmentation that comes from those earlier versions?

About oversizing some parameters for upgrades it may give some extra speed: I will try it since I'm in almost the same upgarding process than Dave (I'm just starting from a greater apps version - 11.0.3)



Dave,

Anyway your 25 hours don't seem to much to me:

I take 8 hours patching just to bring apps from 8.0.5 to 8.0.6...

Best Regards,

Eric

[TwoProc]

Hello once again Eric,

Like American golfer "Hubie Green" was destined by name to play golf - a man named "Antunes" was meant to tune systems.

:-)

[Eric Antunes]

Hi John,

See me more like a Warren Buffet old fashion kind of guy: always very cautious about where he places is money. :-)

Best Regards,

Eric

[Yogeeraj_1]

Hi Eric (alias warren) :) ,

To see if you suffer from "fragmentation", you can query DBA_FREE_SPACE (best to do an alter tablespace coalesce first to ensure all contigous free regions are made into 1 big free region). DBA_FREE_SPACE will report the size of all free extents. You would look for ANY free extent that is smaller then the smallest NEXT extent size for any object in that tablespace.


"fragmentation is when you have 500MB free in the tablespace, yet are unable to allocate a next extent because your tablespace is like swiss cheese, full of different sized holes -- none of which are usuable"


As from Oracle8i, we would all use locally managed tablespaces. These would use either UNIFORM sizing or automatic allocation scheme. In either case, it is almost impossible to get into a situation where you have unusable free space.


As you probably know fragmentation occurs in a tablespace when you have lots of varied sized extents.
eg.

5 blocks allocated
50 blocks allocated
15 blocks allocated
35 blocks allocated
150 blocks allocated
10 blocks allocated

and on top of that when we start DROPPING/truncating objects we leave "holes" of weird sizes.


With an LMT using system managed extents, you'll find the extents are one of a COUPLE of sizes - not an infinite number of sizes and each size can be used by any other object (cause the NEXT extent is not a fixed number -- it is some number we decide for you AT the point in time we need it). With an LMT, you don't get into that circumstance, hence the tablespace is not fragmented, it simply has free space in it that can be used for other stuff.


It will not fragment.

(We prefer uniform when we KNOW the sizes of the objects. But if you don't know or don't care to know, system allocated is just fine)

I would suggest that you follow the Oracle guidelies for creation of LMTs when doing your next upgrate/migration. Also, when you go to oracle 10g, there are tons of new features than will make you even more happier!


good luck and
kind regards
yogeeraj

[Dave Chamberlin]

Thanks for all the comments - I don't mind the extra comments Eric et al - after all this IS a forum for discussion and
I like to see the different sides of issues). BTW - in the 11i upgrade, even though all tablespaces other than system are converted to local extent management - existing structures are NOT rebuilt (at least under the existing OFA model) - so fragmentation can still be an issue. IMHO - I think the real issue in space management for us (OLTP db) is archiving old data - instead of dragging around boatloads of data that will never see the light of day...

On my current upgrade test, I did increase the block buffers (now db_cache_size) from 1GB to 5GB. I also increased redo log size to 250M and increased log_buffer from 4M to 30M. Everything went great until the phase that processes orders (ontup231) - and it was crawling MUCH slower than in prev runs. Found out that my associates decided to try this upgrade without closing any orders - which we have done in all prev tests. I looked at what was taking so long - about 12GB of stuff had been inserted into 2 workflow tables and their indexes (and this phase was only half done). We killed the upgrade, restored backup and we are doing it over correctly. Exciting eh? At least this time I expect to see the tuning pay off...:)

[TwoProc]

Dave, thanks for the response on how the upgrade is going. I agree with your posting about archiving as I'm working on that very topic this week. Good luck with the upgrades, and in trying to keep the testing team consistent (on closes).

Also, Yogeeraj, I agree with the using LMT with uniform spacing - I converted to it many years back, and enjoy it. In regards to Dave's comment about not doing it for past data - we ran across the same thing. I fixed all of that when we first went to database version 9i, and did a full import of everything onto LMT uniform tables for all tablespaces. And yes, we don't worry about fragmentation nearly as much anymore.


Good luck,

John

[Volker Borowski]

Dave,

if you already know your "big-brother-tables", that cause trouble for conversion, did you consider to increase parallel-degree on these prior conversion.

Since a "conversion" usually needs to read all data of a table, a full table scan should be the plan of choice (well mostly), and in this case it can be more speedy when doing it with parallel query. It would be of help, if you could track down the statements that are operated in these phases, although I am not sure if it is permitted to runs some statspack or SQL-Trace while this one is on the way.
(And check then documentation in addition, if PQ [parallel query] is permitted in this conversion, not sure about this)

alter table .... parallel 8;

will use 8 PQ procs doing the FTS.
This scales almost linear up to several GB tablesize.

I use it often to create secondary indexes on big tables (
CREATE INDEX ... parallel 24
alter index ... noparallel
).

Needs some parameters
parallel_max_servers...
parallel_threads_per_cpu...
and may be some more, which I do not have handy right now.

Check it out:

set timing on

select count(*)
from bigtable;

-- run it twice to be fair for caching

select /*+ parallel (bigtable, 8) */ count(*)
from bigtable;

-- run it twice as well (caching) an check
-- "ps -ef | grep ora_p" while running
-- to see PQ-procs showing up

If this gives you significant speedup, set the parallel_degree of the table in question. Be carefull, setting 3 tables to "parallel 8" might need 24 PQ-procs when they are operated at the same time, which has additional impact on need for TEMP and UNDO, so be sure to be single in conversion or otherwise try to use a lower value.

Check old degree value before (column degree in dba_tables / dba_indexes) and reset afterwards or set "noparallel".

Volker