- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: optimize performance during oracle11i upgrade
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-01-2006 02:37 AM
тАО08-01-2006 02:37 AM
Re: optimize performance during oracle11i upgrade
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-01-2006 05:16 PM
тАО08-01-2006 05:16 PM
Re: optimize performance during oracle11i upgrade
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-02-2006 01:57 AM
тАО08-02-2006 01:57 AM
Re: optimize performance during oracle11i upgrade
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-02-2006 03:38 AM
тАО08-02-2006 03:38 AM
Re: optimize performance during oracle11i upgrade
(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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-02-2006 09:36 PM
тАО08-02-2006 09:36 PM
Re: optimize performance during oracle11i upgrade
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-02-2006 10:34 PM
тАО08-02-2006 10:34 PM
Re: optimize performance during oracle11i upgrade
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-03-2006 02:11 AM
тАО08-03-2006 02:11 AM
Re: optimize performance during oracle11i upgrade
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-03-2006 02:59 AM
тАО08-03-2006 02:59 AM
Re: optimize performance during oracle11i upgrade
Like American golfer "Hubie Green" was destined by name to play golf - a man named "Antunes" was meant to tune systems.
:-)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-03-2006 04:03 AM
тАО08-03-2006 04:03 AM
Re: optimize performance during oracle11i upgrade
See me more like a Warren Buffet old fashion kind of guy: always very cautious about where he places is money. :-)
Best Regards,
Eric
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-03-2006 08:00 PM
тАО08-03-2006 08:00 PM
Re: optimize performance during oracle11i upgrade
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