OraFAQ Forum: Server Administration » need help to clear buffer pool (merged)

Home » RDBMS Server » Server Administration » need help to clear buffer pool (merged) (8.1.7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

need help to clear buffer pool (merged) [message #462254]

Wed, 23 June 2010 05:25

ultgnp
Messages: 106
Registered: May 2010
Location: Germany

Senior Member

Hi Friends,

I am trying to tune a report. I ran the report couple of times. I then created an index and the report is super fast now. I know that the recently run queries are in shared pool, so the query might run fast. Could anyone please tell me how to flush data out of shared_pool manually everytime before i run the report.

I saw a command "Alter system flush shared_pool". Is this the only command? Please suggest.

Best Regards,
Atiq

Report message to a moderator

Re: need help to clear buffer pool (merged) [message #462263 is a reply to message #462254]

Wed, 23 June 2010 05:40

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

know that the recently run queries are in shared pool, so the query might run fast

This is only part true. Oracle just omit the parsing phase as it already has the execution plan in memory but it still executes the statement.
And it may be faster because some blocks are already loaded in buffer cache.

In 8i, you cannot flush the buffer cache, you can only flush the shared pool, this mean you can just ask Oracle to forget all about metadata but not about data.

Regards
Michel

Report message to a moderator

Re: need help to clear buffer pool (merged) [message #462269 is a reply to message #462263]

Wed, 23 June 2010 05:54

ultgnp
Messages: 106
Registered: May 2010
Location: Germany

Senior Member

Thanks Michel,

As i am working on development machine, can i restart the system so that all the buffer cache is freed? Is it possible?

[Updated on: Wed, 23 June 2010 05:54]

Report message to a moderator

Re: need help to clear buffer pool (merged) [message #462278 is a reply to message #462269]

Wed, 23 June 2010 06:18

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes, you can restart the instance (no need to restart the whole server).

Regards
Michel

Report message to a moderator

Re: need help to clear buffer pool (merged) [message #462284 is a reply to message #462254]

Wed, 23 June 2010 06:25

ultgnp
Messages: 106
Registered: May 2010
Location: Germany

Senior Member

http://www.orafaq.com/forum/r/prev_loaded/1/152909/ has very good description of this issue.

Report message to a moderator

Re: need help to clear buffer pool (merged) [message #462290 is a reply to message #462284]

Wed, 23 June 2010 07:07

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Your link is wrong.

Regards
Michel

Report message to a moderator

Re: need help to clear buffer pool (merged) [message #462306 is a reply to message #462290]

Wed, 23 June 2010 08:15

ultgnp
Messages: 106
Registered: May 2010
Location: Germany

Senior Member

http://www.oracle.com/technology/oramag/oracle/03-jul/o43asktom.html

Report message to a moderator

Re: need help to clear buffer pool (merged) [message #462310 is a reply to message #462306]

Wed, 23 June 2010 08:20

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

You realise the linked article says flushing the pool is a total waste of time?

Report message to a moderator

Re: need help to clear buffer pool (merged) [message #462314 is a reply to message #462310]

Wed, 23 June 2010 08:32

ultgnp
Messages: 106
Registered: May 2010
Location: Germany

Senior Member

Yes, I learnt a new lesson today.

Report message to a moderator

Previous Topic:	migration of oracle 10gR2 to oracle 11gR2
Next Topic:	DBMS_STATS for a Partitioned table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 10:39:07 CST 2024