| 9i-Clearing cache and memory [message #307059] |
Mon, 17 March 2008 14:21  |
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  |
rleishman
Messages: 3728
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 |
trantuananh24hg
Messages: 744
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
