9.0.2.8 Serious performance issues
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: 9.0.2.8 Serious performance issues

  1. #1
    Join Date
    Dec 2010
    Posts
    5

    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

  2. #2
    Join Date
    Dec 2002
    Posts
    60
    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.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Dec 2010
    Posts
    5

    Moving servers!

    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

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Dec 2010
    Posts
    5
    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.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Artz View Post
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Dec 2010
    Posts
    5
    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.

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Sure, just bing "oracle, clone database, cold backup"... tons of hits like: http://www.shutdownabort.com/quickguides/clone_cold.php
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Dec 2010
    Posts
    5
    Thanks for the link and the keywords to search off of!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width