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)
Slow databases on a Solaris server after flushing buffer cache of one database [message #631754] Mon, 19 January 2015 09:16 Go to next message
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>CAn someone please review and suggest the needful?

root> reboot -f
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

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 Go to previous messageGo to next message
John Watson
Messages: 8922
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.

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 Go to previous messageGo to next message
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,
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 Go to previous messageGo to next message
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?
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

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 Go to previous messageGo to next message
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)           

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 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Yea, I now see your point. Thank you Michel.
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 Go to previous message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thank you BlackSwan.
Previous Topic: 10g to 11g upgrade
Next Topic: Startup force and shutdown abort
Goto Forum:
  


Current Time: Thu Mar 28 17:02:20 CDT 2024