Re: 32bit to 64bit migration

I can tell you from personal experience that I have seen database performance turned around by shrinking SGAs dramatically. Let me give you an example. At SBC I support the production,dev, test and training databases for our Long distance, ADSL and Cellular businesses(no, i don't work alone :-). We normally assume responsibility for a new database because performance has gotten out of hand and the present crew (often consultants) aren't able to provide satisfactory performance(not a slam on consultants).

Usually it starts out like this: A database is setup and goes to production. There are full table scans all over the place and none of the SQL uses bind variables. The "easy" answer? Allocate more memory. Temporarily things get a bit better. Then the activity level increases and performance tanks again. The solution? You guessed it, allocate more memory.

Eventually, the 2gb SGA is reached and there is no more fix for the problem left. That's when we get called in. We run OLTP and datawarehouse systems and the largest SGA we have is around 400mb. That's like an initial change we just take for granted. Usually this in an of itself is enough to achieve a significant performance gain. By the time the SGA gets that large Oracle is spending most of it's time trying to maintain LRU lists and such.

One common reason to enlarge the SGA is CPU overutilization due to statements getting reparsed a zillion times a day and that shows up as a low library cache hit ratio. The question that has to be asked before going to a bigger SGA is, why is the SQL getting reparsed? Is identical SQL getting aged out because the SGA is too small or is it because there are no bind variables in the code so Oracle thinks SQL which is identical except for the literal values is non distinct? Usually it is the latter and bind variables are the answer, not enlarging the SGA. (Oracle 8i does address this as it can understand that the SQL is in actuality identical)

Others will crank up db block buffers so they can cache frequently accessed and full table scanned tables. This is nearly always a mistake. If you have full table scans running rampant, the solution is to properly index,partition and sql tune.

Allocating memory is the easy fix and that is why so many folks do it. The problem is that it causes performance problems of it's own because it is very expensive to manage the LRU lists on all the stuff in 2G of memory. Think of it like this. If your library cache hit ratio is low because of all the non distinct SQL running through it forcing the "good" stuff out prematurely, how will enlarging the shared pool help? The library cache will just contain more copies of nondistinct SQL that will never be reused because the next time through it will have different literal values. All that has been added is overhead to maintain the LRU list.

The same is true of db block buffers. Full table scans will rapidly flush everything out of the buffer pool, no matter how big one makes it. The full scans must be eliminated if they are the cause of poor performance.

So, why is shrinking the SGA often the biggest performance increase? Because the additional size isn't benefiting anything and the expense of maintaining it is dragging down performance.

I hope I have addressed the questions you were asking.

--
Dave A


"Adrian Shepherd" <theshepherds_at_hotmail.com> wrote in message
news:0Mmx5.12718$tj4.92084_at_news-server.bigpond.net.au...


> Interesting comment Dave,

>

> I have a feeling you are spot on, but I am unable to offer any convincing

> evidence to the businesses concerned, do you have anything concrete,

 URL's,


> personal experience, examples ?

>

> Regards

>

> Adrian

>

> "Dave A" <dave_and_vanna_at_hotmail.com> wrote in message

> news:ssbssf3ih3t106_at_corp.supernews.com...

> > There is no good reason for a SGA larger than 2g.  In fact a 2 gb SGA

 will


> > almost certainly kill your performance.  There is simply too much

 overhead


> > maintaining it.

> >

> > Are your hit ratio's low?

> >

> > --

> > Dave A

> >

> >

> > <xiangz_at_rocketmail.com> wrote in message

 news:8q4n2r$sa5$1_at_nnrp1.deja.com...


> > > Hi all,

> > >

> > > I installed a 32 bit oracle 8.1.5 on HPUX 11(64bit OS), then I found

> > > out that the SGA size can't excced 2G due to 32 bit limitation. Now I

> > > would like to migrate oracle 8.1.5 32 bit to oracle 8.1.5 64 bit.

> > > Is it possible to reuse all the data file directly? How about the

> > > control file?

> > >

> > > What's the simplest way and safest way to do this?

> > >

> > > Or I have to export all the data out, and import to new 64 bit

 database?


> > >

> > > Any hints, suggestion?

> > >

> > > -Xiang Zhao

> > >

> > >

> > >

> > >

> > >

> > > Sent via Deja.com http://www.deja.com/

> > > Before you buy.

> >

> >

>

>