Re: Exporting and Importing SQL Statement Cache

On 26 Nov, 20:39, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Nov 26, 2:41 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
> > On Nov 26, 8:54 am, Stevo <steven.robb..._at_gmail.com> wrote:
>
> > > On Nov 26, 4:04 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
>
> > > > On Nov 26, 10:50 am, Stevo <steven.robb..._at_gmail.com> wrote:
>
> > > > > Hi All,
>
> > > > > Does anyone know if it's possible to perform an export and import of
> > > > > the SQL Statement Cache in Oracle 8i?
>
> > > > > Thanks
> > > > > Steve.
>
> > > > Hi Steve,
>
> > > > I'm not sure what you mean by export import of it, but you can always
> > > > "select * from v$sql".  If you have statspack installed, you can also
> > > > look at the stats$sql_summary table (and maybe others).
>
> > > > To "import" it, start running the statements you pulled out above :)
>
> > > > HTH,
>
> > > > Steve
>
> > > Thanks Steve,
>
> > > I currently have oracle running on 2 servers and we only use one
> > > server in use at any one time. If we swap servers after a few days the
> > > new live server gets overloaded. If we swap servers after a short
> > > period of time the server has no trouble handling the traffic. I
> > > figured this is the sql statements being removed from the sql cache
> > > over time. So I'm trying to work out a way to preload the server with
> > > the sql statements before we swap sites. Is there a better way of
> > > doing this?
>
> > > Cheers,
> > > Steve.
>
> > How are you determining "overloaded?"  You probably should find out
> > what the real problem is and fix that.  Your figuring sounds just
> > wrong.  Look at your oracle alert log first, and post any errors you
> > find.  The sql cache ages out unused sql, it can cause ORA-403x errors
> > through memory fragmentation.  Even so, simply bouncing the instance
> > should fix it if that is what it is (and pinning packages and tuning
> > the area might be appropriate).  There are other possible maintenance
> > issues.
>
> > Post your exact (to 4 decimal places) version, your exact OS and
> > hardware details.  It sounds like an OS problem from what little
> > you've said, like a memory leak (of which Oracle has had some).  How
> > many users do you have?  Are they signing off properly?  How many
> > centuries do expect this app to last?
>
> > jg
> > --
> > @home.com is bogus.
> > Note to Alanis Morisette: Rain on your wedding day is only ironic if
> > you're marrying a weatherman- Hide quoted text -
>
> > - Show quoted text -
>
> I agree with joel.  More information on why you think the issue is the
> sql cache is needed.
>
> HTH -- Mark D Powell --

Oracle Version: 8.1.7.4
OS Name: Solaris 8
System: Sun SPARC E450
Memory: 3GB

No errors appear in the alert logs during the time we failover. By overloaded I can see that the system load output from top goes up to 16 when we swap servers. After about 5-10 minutes it levels back out averaging a load average of 2. The load didn't increase so dramatically when we swapped back after 15 minutes. It only seems to happen if we swap after a longer period of time. I'm possibly way off as my knowledge of oracle is limited. Welcome any suggestions.

Steve. Received on Thu Nov 27 2008 - 03:03:32 CST