OraFAQ Forum: Server Administration » Slow databases on a Solaris server after flushing buffer cache of one database

Home » RDBMS Server » Server Administration » Slow databases on a Solaris server after flushing buffer cache of one database (Oracle 10.2.0.3 on Solaris 10)

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

Slow databases on a Solaris server after flushing buffer cache of one database [message #631754]

Mon, 19 January 2015 09:16

orausern
Messages: 826
Registered: December 2005

Senior Member

Hi,

I have 6 databases on Oracle 10.2.0.3 (didn't get OK to upgrade to 11g yet and that is a seperate and unrelated issue). Today I flushed buffer cache of one of this database. The command just hanged for more than 20 minutes.

Then I killed that session. Since then , all databases on the solaris server are very very slow. It takes like 15 seconds to start a new session. My guess is something related to memory has gone wrong. But I really don't know enough to be sure. CAn someone please review and suggest the needful?

Thanks,
OrauserN

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631755 is a reply to message #631754]

Mon, 19 January 2015 09:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>CAn someone please review and suggest the needful?

root> reboot -f

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631757 is a reply to message #631755]

Mon, 19 January 2015 09:23

orausern
Messages: 826
Registered: December 2005

Senior Member

Thanks BlackSwan. Can you also explain what the root cause of the issue could be , that leads to this situation.

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631758 is a reply to message #631757]

Mon, 19 January 2015 09:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> what the root cause of the issue could be , that leads to this situation.

Ready, Fire, AIM!

Problem Exists Between Keyboard And Chair

>Today I flushed buffer cache
post URL where Oracle documents that flush of buffer cache should ever be done.

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631759 is a reply to message #631758]

Mon, 19 January 2015 09:29

orausern
Messages: 826
Registered: December 2005

Senior Member

Well! I was working on a performance issue and wanted to check what is the effect of rewrite of a sql query. Now I didn't want the previous execution to affect the next execution. So I flushed the buffer cache. This is because I wanted to get accurate data on how much time the revised sql takes without being influenced by previous execution. that is why I had done it.

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631763 is a reply to message #631759]

Mon, 19 January 2015 10:14

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

Senior Member
Account Moderator

But your query will never be executed when the buffer cache will be empty, won't it?

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631773 is a reply to message #631763]

Mon, 19 January 2015 11:06

orausern
Messages: 826
Registered: December 2005

Senior Member

I used to follow this practice of flushing buffer cache! Now I have burnt my fingers and I am going to check whether this should be done really!

Also this mystery came to an end....the alert log of the db , whose buffer cache I had flushed had this errors...I have export backup so tomorrow I will try to rebuild the db from scratch!!

ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file 1 (block # 3667)
ORA-01110: data file 1: '/u01/oradata/db1/system1.dbf'
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
SVR4 Error: 5: I/O error

[Updated on: Mon, 19 January 2015 11:06]

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631774 is a reply to message #631773]

Mon, 19 January 2015 11:09

orausern
Messages: 826
Registered: December 2005

Senior Member

Also Mike , about this point:

Quote:

But your query will never be executed when the buffer cache will be empty, won't it?

Well...what I used to do is to flush the shared pool, buffer cache and wait 3 minutes. This means whatever data nowthe query has to get will come from IO and not from memory. That gives a good idea of how the performance will be - as I thought. Is this wrong and if yes why? I am not clear on it.

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631778 is a reply to message #631774]

Mon, 19 January 2015 11:24

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

Senior Member
Account Moderator

No, it will not.
In real case, the buffer cache will contain blocks for your query and other blocks; row cache will contain information about the objects your query uses...
The only way to get a realistic idea of the performances is to execute it in an environment similar to the one it will execute in the end.

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631780 is a reply to message #631773]

Mon, 19 January 2015 11:49

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

orausern wrote on Mon, 19 January 2015 17:06

I used to follow this practice of flushing buffer cache! Now I have burnt my fingers and I am going to check whether this should be done really!

Also this mystery came to an end....the alert log of the db , whose buffer cache I had flushed had this errors...I have export backup so tomorrow I will try to rebuild the db from scratch!!
ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file 1 (block # 3667)
ORA-01110: data file 1: '/u01/oradata/db1/system1.dbf'
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
SVR4 Error: 5: I/O error

This looks like a hardware or operating system issue: Oracle is havig problems with that file. Importing an export dump file is not going to fix that, you'll need to restore from a real backup and then recover it.
Before you do that, you had better take a look at the file and do a few basic checks. Is it the right sort of size? Does it have the right access permissions? Can you read and write any other files on the dsame device? Then run dbv against the file to check for corruptions.

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631781 is a reply to message #631778]

Mon, 19 January 2015 12:36

orausern
Messages: 826
Registered: December 2005

Senior Member

Michel Cadot wrote on Mon, 19 January 2015 17:24

The only way to get a realistic idea of the performances is to execute it in an environment similar to the one it will execute in the end.

Thank you Michel. Here my doubt is that: if you don't flush buffer cache, the data may already be present in buffer cache so next execution may show 'artificially fast response time' which may not happen in real-time. So the chances that our query's data may not be present in buffer cache is far higher than the chance that it is present. Therefore it is more realistic to flush buffer cache -is what I was thinking. Is that wrong?

Thanks,

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631782 is a reply to message #631780]

Mon, 19 January 2015 12:39

orausern
Messages: 826
Registered: December 2005

Senior Member

John Watson wrote on Mon, 19 January 2015 17:49

[code]
This looks like a hardware or operating system issue: Oracle is havig problems with that file. Importing an export dump file is not going to fix that, you'll need to restore from a real backup and then recover it.
Before you do that, you had better take a look at the file and do a few basic checks. Is it the right sort of size? Does it have the right access permissions? Can you read and write any other files on the dsame device? Then run dbv against the file to check for corruptions.

Thank you John! What I am thinking to do is to : DROP this database and create the Database back with same name and import with its previous full export file. Hope that will work right?

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631784 is a reply to message #631781]

Mon, 19 January 2015 12:45

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

Senior Member
Account Moderator

Quote:

Therefore it is more realistic to flush buffer cache -is what I was thinking. Is that wrong?

The answer is "it depends".
Maybe or maybe not.
It depends on the query and concurrent workload and the history of the instance.

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631785 is a reply to message #631782]

Mon, 19 January 2015 12:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

run dbv against /u01/oradata/db1/system1.dbf

[oracle@localhost ~]$ dbv

DBVERIFY: Release 11.2.0.2.0 - Production on Mon Jan 19 10:45:44 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
FILE        File to Verify                 (NONE)
START       Start Block                    (First Block of File)
END         End Block                      (Last Block of File)
BLOCKSIZE   Logical Block Size             (8192)
LOGFILE     Output Log                     (NONE)
FEEDBACK    Display Progress               (0)
PARFILE     Parameter File                 (NONE)
USERID      Username/Password              (NONE)
SEGMENT_ID  Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN    Highest Block SCN To Verify    (NONE)
            (scn_wrap.scn_base OR scn)

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631786 is a reply to message #631784]

Mon, 19 January 2015 12:48

orausern
Messages: 826
Registered: December 2005

Senior Member

Yea, I now see your point. Thank you Michel.

Report message to a moderator

Re: Slow databases on a Solaris server after flushing buffer cache of one database [message #631787 is a reply to message #631785]

Mon, 19 January 2015 12:48

orausern
Messages: 826
Registered: December 2005

Senior Member

Thank you BlackSwan.

Report message to a moderator

Previous Topic:	10g to 11g upgrade
Next Topic:	Startup force and shutdown abort

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 11:32:37 CST 2025