Re: 32bit to 64bit migration

Thanks...

"Dave A" <dave_and_vanna_at_hotmail.com> wrote in message news:ssd80jktpedd55_at_corp.supernews.com...
> 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.
> > >
> > >
> >
> >
>
>
Received on Tue Sep 19 2000 - 04:12:05 CDT