Re: Exporting and Importing SQL Statement Cache
Date: Thu, 27 Nov 2008 01:03:32 -0800 (PST)
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: 184.108.40.206
OS Name: Solaris 8
System: Sun SPARC E450
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