Home » RDBMS Server » Performance Tuning » 9i-Clearing cache and memory (oracle 9i, Aix)
9i-Clearing cache and memory [message #307059] Mon, 17 March 2008 14:21 Go to next message
shlomi_bt
Messages: 16
Registered: August 2005
Location: Canada
Junior Member

Hi,
I have a tricky problem where i'm trying to tune a query.
this query runs for let's say 2 minutes elapsed time.
When i take this query out of the trace, assign the binds
and run it on my session, the first attempt works almose as in
the trace BUT - the second runs in couple of seconds.
I tried to clear both the buffer cache and shared pool using:
>alter system flush shared_pool;
>alter session set events = 'immediate trace name flush_cache';
But it dosen't seems to help.
The funniest thing is that i tried to bounced the instance and
still it works in couple of seconds....

Does anybody know how to clear this instance memory portion without restarting the whole box?

Does anybody has an idea how can i tune this one where the second
time it works in asec?
thanks
Re: 9i-Clearing cache and memory [message #307121 is a reply to message #307059] Mon, 17 March 2008 20:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sounds as though you may have some hardware level cacheing - check with your System Administrator. If so, you won't be able to manage this from Oracle.

Ross Leishman
Re: 9i-Clearing cache and memory [message #307124 is a reply to message #307121] Mon, 17 March 2008 21:11 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
You should not flushed the shared_pool, this action would make all of the statements must be re-processed in a cycle: Hard parse/cached/execution. May be your hard-ware in trouble, may be not. Try to solve one by one step.
Re: 9i-Clearing cache and memory [message #307721 is a reply to message #307121] Wed, 19 March 2008 12:15 Go to previous message
shlomi_bt
Messages: 16
Registered: August 2005
Location: Canada
Junior Member

Hi,
Yes, it look's like it's the cache on the AIX side...
thanks.
Previous Topic: PERFORMANCE OF A QUERY ;
Next Topic: Can anybody explain this index
Goto Forum:
  


Current Time: Wed Dec 07 20:32:09 CST 2016

Total time taken to generate the page: 0.08627 seconds