Re: Exporting and Importing SQL Statement Cache

From: joel garry <joel-garry_at_home.com>
Date: Thu, 27 Nov 2008 08:26:23 -0800 (PST)
Message-ID: <0b45b9c6-6448-4147-8ff0-c868f8365379@v15g2000yqn.googlegroups.com>


On Nov 27, 1:03 am, Stevo <steven.robb..._at_gmail.com> wrote:
> 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.

This is starting to sound like a "that's the way it is." I was reading your original post as you were swapping because things would become overloaded.

You could use the logminer to grab the sql that aren't inserts or updates and apply them to the other server before switching over, but whether that will help is based on what proportion are inserts/ updates, and how much of the sql is reusable (very app dependent), and how much of the load you see is from packages being loaded. That might be as much work as upgrading to a supported version.

Your original supposition may simply be partially correct and partially backwards. Oracle hashes the values of sql in order to not have to hard parse them (google hard and soft parse to understand the difference), an expensive operation. So often there are packages which can be pinned, which you can script as part of the db startup. Oracle has its standard packages, which ought to be pinned if you are aging out sql a lot. The procedure is called DBMS_SHARED_POOL.KEEP. You can google to find out how to tune the shared pool and which packages you want to pin, but be aware, there is lots of misinformation still floating about from that era.

You probably should get someone in there that knows about this kind of stuff.

So why are you switching servers? Some kind of mass update? Why are you still on 8i?

jg

--
@home.com is bogus.
Domain wars: http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9121678
Received on Thu Nov 27 2008 - 10:26:23 CST

Original text of this message