9.0.2.8 Serious performance issues

Greetings!

I'm a DBA primarily with background in DB2 and MSSQL but I am being asked to help support an Oracle issue.

Version:9.0.2.8
OS: Linux; Redhat

We have numerous jobs that typically take less then 2 minutes that are now, as of last week, taking 1-2 hours and it appears to be purely random. This behavior started last week and the more I dig into this situation it might be linked with taking on some new clients and the load caused by the increase.

We originally thought that it was an issue with statistics begin created for the tables. Fooling with tweaking the "Analyze table" scripts seems to cause one process to improve at the cost of another process.

Then we started to see commit issues on mass updates and we were seeing the error "Rollback segment too small".

I started to think these might all be symptoms of another issue like a lack of resources but I'm no expert with Oracle. So what can I look at in the v$ tables to figure this out?

Here is one thing that really stuck out to me in the v$latch table but it could be nothing:

Name: cache buffers chains
Gets: 99,338,117,727.00
Wait_Time: 20,420,444,970.00

Any advice is greatly appreciated! If you need more information please just ask!

-Art

Rollback segment too small is typically caused by a long running transaction and other session trying to get before change data. Look for a process that is running too long and see what kind of waits it is experiencing. You can check v$session_wait view.

Grab the offending query and see query plan to see if it is what you expect it to be.

That's a Ora9i instance, do you have statspack running in the system?

Identify five queries then check explain plan and buffer_gets for each one of them... two weeks ago against today.

How much has data volume increased lately?
Any table getting Full Table Scan has substantially increased its size?
Look at wait events, which one is on top of the other... sequential read or scattered read?

Either way... my initial guess is that this is a combination of improper statistics with queries that do not scale up properly.

Well things are not panning out and given the evaluation given by the linux admins and the hardware having problems we are now in the throws of preparing to move the database to another server...

The databases are on a SAN and they are talking about basically moving the lun(s) to a new server. I know with DB2 and SQL Server how to do this and the caveats as well as how to verify but I am unsure of doing this with Oracle.

I've been searching the web on this and haven't come up with what would be the steps pertaining to moving the database structure to another server. Is there a detach/attach steps that need to be done?

Once the SAN resources are moved, what would be a basic overview of verifying that everything is working as intended?

Any advice or a link on this topic is greatly appreciated!!

-Art

detach/attach is a SQL Server concept - it doesn't exist per-se in Oracle.

Is new server going to run exactly the same version of the Operating System?

Yes, according to the Linux admin's it will be the same version of Redhat 4 as well as the same version of Oracle 9.2.0.8.

Quote:

---

Originally Posted by Artz

Yes, according to the Linux admin's it will be the same version of Redhat 4 as well as the same version of Oracle 9.2.0.8.

---

That would be helpful; try this...

1- Shutdown all instances in affected box
2- Ask Sysadmin to duplicate all your filesystem structure including Oracle binaries, etc. onto your new host.
3- Let Sysadmin map LUNs to new host
4- Startup instances in new host

...as you can see this works as a cold backup.

Thank you!

Do you know of any articles that touch on this subject in additional depth? I'd really like to read up on this subject.

Sure, just bing "oracle, clone database, cold backup"... tons of hits like: http://www.shutdownabort.com/quickguides/clone_cold.php

Thanks for the link and the keywords to search off of!

Quote:

---

Originally Posted by Artz

Thanks for the link and the keywords to search off of!

---

Glad to help.