Re: Exporting and Importing SQL Statement Cache
- From: joel garry <joel-garry@xxxxxxxx>
- Date: Thu, 27 Nov 2008 16:41:16 -0800 (PST)
On Nov 27, 1:40 pm, Stevo <steven.robb...@xxxxxxxxx> wrote:
On 27 Nov, 16:26, joel garry <joel-ga...@xxxxxxxx> wrote:
On Nov 27, 1:03 am, Stevo <steven.robb...@xxxxxxxxx> wrote:
On 26 Nov, 20:39, Mark D Powell <Mark.Pow...@xxxxxxx> wrote:
On Nov 26, 2:41 pm, joel garry <joel-ga...@xxxxxxxx> wrote:
On Nov 26, 8:54 am, Stevo <steven.robb...@xxxxxxxxx> wrote:
On Nov 26, 4:04 pm, Steve Howard <stevedhow...@xxxxxxxxx> wrote:
On Nov 26, 10:50 am, Stevo <steven.robb...@xxxxxxxxx> 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=viewArticleBas...
Wasn't implying that "thats the way it is". Just figured that I hadn't
explained the configuration well enough. In my original post I was
referring to the server becoming overloaded "after" we swap. The
database is being used for a website and the servers are located in
different sites. When we perform work on one site we tend to run the
website from the other site. As the traffic is redirected to the
unused site the database we're swapping to is initially hit pretty
hard. Only select queries are created by the web traffic.
I had done some research on pinning packages and believe this may
help. You right about getting someone that has experience with this
kind of work. My manager knows we need to hire a DBA but in the
meantime I'm obliged to try and help.
Thanks for the help.
Steve.
Sounds like you would benefit from upgrading and using dataguard with
graceful switchback. There is a paper you could google called
Graceful Switchover and Switchback Oracle Standby Database if you want
to try it with your version. But I'd still recommend upgrading. I'd
even point and laugh at the management responsible for any production
site still using 8i. linux, commodity boxes with lots of memory and
disk are cheap, you're paying for Oracle support... right?
jg
--
@home.com is bogus.
http://www.liveleak.com/
.
- Follow-Ups:
- Re: Exporting and Importing SQL Statement Cache
- From: hpuxrac
- Re: Exporting and Importing SQL Statement Cache
- References:
- Exporting and Importing SQL Statement Cache
- From: Stevo
- Re: Exporting and Importing SQL Statement Cache
- From: Steve Howard
- Re: Exporting and Importing SQL Statement Cache
- From: Stevo
- Re: Exporting and Importing SQL Statement Cache
- From: joel garry
- Re: Exporting and Importing SQL Statement Cache
- From: Mark D Powell
- Re: Exporting and Importing SQL Statement Cache
- From: Stevo
- Re: Exporting and Importing SQL Statement Cache
- From: joel garry
- Re: Exporting and Importing SQL Statement Cache
- From: Stevo
- Exporting and Importing SQL Statement Cache
- Prev by Date: Standby database Transport Lag
- Next by Date: Re: Exporting and Importing SQL Statement Cache
- Previous by thread: Re: Exporting and Importing SQL Statement Cache
- Next by thread: Re: Exporting and Importing SQL Statement Cache
- Index(es):
Relevant Pages
|
Loading
