Other databases are behaving well
saha, January 04, 2011 - 1:57 pm UTC
Hi,
My other databases are performing well except one database. We are using SAN disks and the database which is running slow is not more than 500GB in size.
resource consumption is normal like other day.
Though it is slow. Is there any possible way to improve performance though??
My databases used be behaving in very comfortably manner at the time of heavy load in each application.
after new year celebration no such heavy activity is started at application level too.. :-P
what else can be the reason behind slowness?
Do I need to take care of other things also??
Please suggest..
January 04, 2011 - 2:19 pm UTC
The disk speed would not lead to ORA-3136's - that is indicative of a swamped machine - or a database that is overloaded.
tell me exactly how you are measuring that only this ONE database is slow - all around slow - slow to connect to, slow to run a query like select * from dual, etc.
That is, give us a bit more context - I'm still going with "the machine seems overloaded" until you give us some evidence otherwise.
database slow
saha, January 04, 2011 - 2:47 pm UTC
Yes Slow to perform DML DDL. But Today I got some lag to connect to database unlike yesterday. yesterday connecting to the database was normal bit DB performance was slow.. But today two times I saw connecting to the database took little more time than usual.
like if we make a table from another table by create table as select * from table and insert the same values then for few tables which is generally completed with in few minutes is taking more than a hour to complete. I check the usage of TEMP and UNDO tablespace usage .. it is normal ..
Today I took logical backup by expdp for a schema which is around 17GB+ (less than 20GB to be rounded up) of size took more than two hours to complete. But then I guess for that case may be there are so many changes were taking place in the objects of that schema.
Please help now. and let me know if you need any further information
January 04, 2011 - 2:55 pm UTC
exec dbms_monitor.session_trace_enable( waits=> true );
do something slow
exit
generate the tkprof and post the section - JUST the section - for the "do something slow" bit.
do you have access to ASH and AWR - what have you done so far to try to figure this out?
Compared to...
Dana, January 04, 2011 - 10:03 pm UTC
And is the database slow for the whole day? Or just five minutes? When it was "normal" was there a AWR/Statspack? And is there an AWR/Statspack to compare when it was slow? It may sound picky but "slow" is very subjective.
AWR/Statspack provides an objective measure of service.
Use OS utilities...
A reader, January 04, 2011 - 10:52 pm UTC
Use OS utilities like iostat/vmstat etc. AWR reports might help as well.
But as Tom has also suggested, check which session is slowest (one way I do it is to get the top process and then extract the details for the same)
databases per host
a reader, January 06, 2011 - 7:05 am UTC
Tom,
Is there any documentation from Oracle that advocates one database per host? I have searched the Oracle documentation, OTN, and metalink and cannot find any information.
The reason I ask it that I have a team that preaches many databases per host, and until they see Oracle documentation that recommends one database per host, the topic of one database per host is off limits.
I can argue the benefits of one database per host, such as troubleshooting, maintenance, performance, administration, etc until I am blue in the face, but cannot move forward without official Oracle documentation. Any help would be great!
Thanks
January 06, 2011 - 8:41 am UTC
... Is there any documentation from Oracle that advocates one database per host? ...
No, in fact you'll find people that don't necessarily agree with my stance on this. Hence the "instance caging" feature in 11g.
... The reason I ask it that I have a team that preaches many databases per host,
and until they see Oracle documentation that recommends one database per host,
the topic of one database per host is off limits ...
wow, so if we write something - they'll just do it? Neat. What do they do when they get notes that conflict with each other (as they do) or documentation that contradicts itself? Do they just go around in circles all day?
We should write a piece of documentation that is one page long. It would read:
page 1: the statement on the other side of this page is TRUE
page 2: the statement on the other side of this page is FALSE
... but
cannot move forward without official Oracle documentation. ...
that has to rank up near the top of stupid reasons to do things. Sorry - there is no documentation that states this, there will never be. In fact we do support it, it is possible, there are features in support of it.
It is just, in my experience and opinion, a bad idea
Thanks
a reader, January 06, 2011 - 9:54 am UTC
Tom,
Thanks for your quick reply, I appreciate the information and laughs! I think we do run in circles here, as they say, "It's been confusion from the word go!"
Thanks again
database is slow
Saha, January 06, 2011 - 1:48 pm UTC
Hi,
thank you for your support and quick reply. I got probable the reason behind my slowness of my database. I wrote in the beginning that my database(which was slow) was sharing datafiles' location with another database datafiles' location and they were in FATA disk. The other database was going through huge activities. So I/O contention was the problem. Again that is my assumption from monitoring the activities of other database. That is why(probably) rest of the databases were not impacted in terms of performance.
Anyway if anyone thinks that my assumption is wrong please correct me..
-Regards,
Saha
January 06, 2011 - 2:06 pm UTC
then the other database was "slow" as well. contention is a two way street.
As stated - look to the OS - it would be the thing that can tell you what is going on. When you run more than one database on a host - and your database is "slow", you have to search OUTSIDE the database for the cause - we cannot help you.
This is why, to me, the only right number of databases per host is....
one.
14 databases
Jonathan Taylor, January 06, 2011 - 7:52 pm UTC
I bet out of those 14 databases, one or more are test databases sharing with production databases.
That is the setup I have to endure, which in addition to performance, has raised numerous interesting issues over they years. For example, test databases being able to read and (over)write files intended for the production database....
database is slow
saha, January 08, 2011 - 2:44 pm UTC
@above..
we are having one production database.... And other databases are mostly development and 3-4 are for testing one is for Benchmarking.....
But again I don't feel other databases were having any performance issues///....
Regards,
Saha...
Alexander, October 26, 2011 - 10:33 am UTC
Tom,
With regard to your follow up here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2817104300346634070#2825800200346267674 You know what's really frustrating about that? There are very skilled, knowledgeable, technical engineers at Oracle telling our company we can "spin up" instances on RAC/Exadata because that's what our architects want to do for a virtualized Oracle environment. They were here on site. I look at RAC class from one of these guys, he's very good, but seriously who in their right mind would suggest many instances per host on a RAC node?
Our architects are treating databases like application servers, and want to be able to "spin up" (they LOVE that term..) instances like a VM.
October 26, 2011 - 1:15 pm UTC
Exadata is a little different - with exadata you can control
a) the amount of memory
b) the amount of cpu
c) the amount of IO
a given instance can use. It is pretty close to virtualization at that point.
BUT
what you need to do is strictly limit the resources an instance can have. If you have a 12 core box and you fire up 4 instances on it and let them all go gang busters - bamm - you are looking at node evictions left and right. You would want to limit each instance to 2-4 cores each. You would have to have 2.5 to 4 instances all going gang busters to evict that node then (you've reduced the chance of an eviction).
I wouldn't want to go to "many" instances - a few - but not "many" - in these circumstances.
On a normal box where you have only CPU and memory that you can manage - I'll stick with my "one" answer.
shitty slow DB
A reader, September 19, 2017 - 6:44 pm UTC
shitty slow oracle db
September 20, 2017 - 1:53 am UTC
How very mature of you