Re: Exporting and Importing SQL Statement Cache

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