Tuesday, February 3, 2015

How To Approach Different Oracle Database Performance Problems

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.

How To Approach Different Oracle Database Performance Problems

Jump Start Your Oracle Database Tuning Effort


Every Oracle Database Administrator will tell you no two performance problems are the same. But a seasoned Oracle DBA recognizes there are similarities...patterns. Fast problem pattern recognition allows us to minimize diagnosis time, so we can focus on developing amazing solutions.

I tend to group Oracle performance problems into four patterns. Quickly exploring these four patterns is what this article is all about.


You Can Not Possibly List Every Problem And Solution


When I teach, some Oracle Database Administrators want me to outline every conceivable problem along with the solution. Not only is the thought of this exhausting, it's not possible. Even my Stori product uses pattern matching. One of the keys to becoming a fantastic performance analyst is the ability quickly look at a problem and then decided which diagnosis approach is the best. For example, if you don't know the problem SQL (assuming there is one) tracing is not likely to be your best approach.

The Four Oracle Database Performance Patterns


Here are the four performance patterns I tend to group problems into.

The SQL Is Known


Many times there is a well know SQL statement that is responsible for the poor performance. While I will always do a quick Oracle Time Based Analysis (see below) and verify the accused SQL, I will directly attack this problem by tuning with SQL specific diagnostic and tuning tools.

But... I will also ask a senior application user, if the users are using the application correctly. Sometimes new applications users try and use a new application like their old application. It's like trying to drive a car with moving your feet as you are riding a bicycle... not going to work and it's dangerous!

Business Process Specific


I find that when the business is seriously affected by application performance issues, that's when the "limited budget" is suddenly not so limited. When managers and their business's are affected they want action.

When I'm approached to help solve a problem, I always ask how the business is being affected. If I keep hearing about a specific business process or application module I know two things.

First, there are many SQL statements involved. Second, the problem is bounded by a business process or application. This is when I start the diagnostic process with an Oracle Time Based Analysis approach which, will result in multiple solutions to the same problem.

As I teach in my online seminar How To Tune Oracle With An AWR Report, user feel performance through time. So, if our analysis is time based we can create a quantitative link between our analysis and their experience. If our analysis creates solutions that reduce time, then we can expect the user experience to improve. This combined with my "3 Circle" approach yields spot-on solutions very quickly.

While an Oracle Time Based Analysis is amazing, because Oracle does not instrument CPU consumption we can't answer the question, "What's Oracle doing with all that CPU?" If you want to drill into this topic check out my online seminar, Detailing Oracle CPU Consumption: The Missing Link.

It's Just Slow


How many times have I experienced this... It's Just Slow!


If what the user is attempting to explain is true, the performance issue is affecting a wide range of business processes. The problem is probably not a single issue (but could be) and clearly the key SQL is not know. Again, this is a perfect problem scenario to apply an Oracle Time Based Analysis.

The reason I say this is because an OTBA will look at the problem from multiple perspectives, categorize Oracle time and develop solutions to reduce those big categories of time. If you also do Unit Of Work Time Based Analysis, you can an even anticipate the impact of your solutions! Do an OraPub website search HERE or search my blog for UOWTBA.

Random Incident That Quickly Appears And Vanishes


This is the most difficult problem to fix. Mainly because the problem "randomly" appears and can't be duplicated. (Don't even bother calling Oracle Support to help in this situation.) Furthermore, it's too quick for an AWR report to show it's activity and you don't want to impact the production system by gathering tons of detailed performance statistics.

Even a solid Oracle Time Based Analysis will likely not help in this situation. Again, the problem is performance data collection and retention. The instrumented AWR or Statpack data does not provide enough detail. What we need step-by-step activity...like a timeline.

Because this type of problem scares both DBAs and business managers, you will likely need to answer questions like this:

  • What is that blip all about?
  • Did this impact users?
  • Has it happened before?
  • Will it happen again?
  • What should we do about it?

The only way I know how to truly diagnose a problem like this is to do a session-level time-line analysis. Thankfully, this is possible using the Oracle Active Session History data. Both v$active_session_history and dba_hist_active_sess_history are absolutely key in solving problems like this.

ASH samples Oracle Database session activity once each second (by default). This is very different than measuring how long something takes, which is the data an AWR report is based upon. Because sampling is non-continuous, a lot of detail can be collected, stored and analyzed.

A time-line type of analysis is so important, I enhanced my ASH tools in my OraPub System Monitor (OSM) toolkit to provide this type of analysis. If you want to check them out, download the OSM toolkit HERE, install it and read the osm/interactive/ash-readme.txt file.

As an example, using these tools you can construct an incident time-line like this:

HH:MM:SS.FFF User/Process  Notes
------------ ------------- -----------------
15:18:28.796 suspect (837) started the massive update (see SQL below)

15:28:00.389 user (57)     application hung (row lock on TM_SHEET_LINE_EXPLOR)
15:28:30.486 user (74)     application hung (row lock on TM_SHEET_LINE_EXPLOR)
15:29:30.??? -             row locks becomes the top wait event (16 locked users)
15:29:50.749 user (83)     application hung (row lock on TM_SHEET_LINE_EXPLOR)

15:30:20.871 user (837)    suspect broke out of update (implied)
15:30:20.871 user (57)     application returned
15:30:20.871 user (74)     application returned
15:30:20.871 user (83)     application returned

15:30:30.905 smon (721)    first smon action since before 15:25:00 (os thread startup)
15:30:50.974 user (837)    first wait for undo - suspect broke out of update
15:30:50.974 -             225 active session, now top event (wait for a undo record)

15:33:41.636 smon (721)    last PQ event (PX Deq: Test for msg)
15:33:41.636 user (837)    application returned to suspect. Undo completed
15:33:51.670 smon (721)    last related event (DFS lock handle)

Without ASH seemingly random problems would be a virtually impossible nightmare scenario for an Oracle DBA.

Summary


It's true. You need the right tool for the job. And the same is true when diagnosing Oracle Database performance. What I've done above is group probably 90% of the problems we face as Oracle DBAs into four categories. And each of these categories needs a special kind of tool and/or diagnosis method.

Once we recognize the problem pattern and get the best tool/method involved to diagnosis the problem, then we will know the time spent developing amazing solutions is time well spent.

Enjoy your work!

Craig.


134 comments:

  1. We are ERPTREE Leading oracle fusion HCM Online Training institute. We are providing online training services since 1999. still we are adding more oracle related courses as the technology changes.
    2000+ online courses are available. we all ways achieve our goal in satisfying students in result we have global recognition to our site. we have best faculty for all of our online courses.


    Oracle fusion HCM Online Training

    ReplyDelete
  2. Oracle fusion procurement training empowering present day agencies watch our unfastened tutorials 80+ hours of brilliant motion pics
    The challenge-oriented video come to be bested tutorials &help
    Thanks for sharing.
    oracle fusion procurement online training
    oracle fusion procurement training

    ReplyDelete
  3. coming forward with practical solutions for addressing the problems. jumpstartoffices.com

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Australia Best Tutor is one of the best Online Assignment Help providers at an affordable price. Here All Learners or Students are getting best quality assignment help with reference and styles formatting.

    Visit us for more Information

    Australia Best Tutor
    Sydney, NSW, Australia
    Call @ +61-730-407-305
    Live Chat @ https://www.australiabesttutor.com




    Our Services

    Online assignment help Australia
    my assignment help Australia
    assignment help
    help with assignment
    Online instant assignment help
    Online Assignment help Services

    ReplyDelete
  7. Really something Grate in this article Thanks for sharing this. We are providing Online Training Classes. After reading this slightly I am changed my way of introduction about my training to people.

    Best bca college in noida
    Top bca colleges in noida

    ReplyDelete
  8. The most effective method to Solve Oracle 12c Performance Issue through Remote DBA Services
    The greater part of the clients grumbling that they have some execution issue in regards to their Oracle database. Their Oracle database works moderate or not executes some inquiry. In the event that you have a similar issue at that point picks our fabulous Online Oracle DB Support or Database Administration for Oracle. The Cognegic is All-In-One help arrangement which encourages you to determine your issues which comes in our database. Here we consistently check the wellbeing, execution and accessibility of your database and if found any sort of specialized issues at that point investigate them on the spot.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  9. The most effective method to Solve Oracle Database Performance Issue through Remote DBA Services
    Assume in the event that you are persistently attempting to information from an Oracle database yet it just is by all accounts getting terrible or low execution at that point clearly implies there is something turning out badly that is the reason this execution issue happens. Cognegic give all day and all night bolster and completely modified help for those clients who going up against Oracle execution issue. To take care of this issue promptly contact to Online Oracle DB Support or Database Administration for Oracle.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  10. Get in Touch with Online Oracle DB Support to Solve Oracle Issue
    Get versatile and administered Oracle bolster through Cognegic's Database Administration for Oracle. We will probably give lively and trustworthy help to customers who go up against a sort of specific issue as for their Oracle database. In case you find botches in your Oracle database like internal code botch, memory plan issue, and invalid number issue et cetera then basically you can contact to our Remote DBA Services and experienced the latest and push Support.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  11. Handle on your Oracle Internal Code Errors with Cognegic's Online Oracle DB Support
    In MS SQL Server you will discover one most ordinary and happening issue i.e. General Network Error or it is correspondingly called Communication interface thwarted expectation when an application identified with SQL Server. This sort of screw up happens unequivocally when running expansive database tries. To confine this issue you need to check the present number of TCP affiliations that the server right now has. In any case, on the off chance that you are not set up to research this by communicating by at that point contact to Cognegic's Microsoft SQL Server Support or Remote Infrastructure Management Support for Microsoft SQL Server System gives centrality strategy concerning this issue.
    For More Info: https://cognegicsystems.com
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com

    ReplyDelete
  12. Each department of CAD have specific programmes which, while completed could provide you with a recognisable qualification that could assist you get a job in anything design enterprise which you would really like.

    AutoCAD training in Noida

    AutoCAD training institute in Noida


    Best AutoCAD training institute in Noida

    ReplyDelete
  13. Very nice post here thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.
    simultaneous interpretation equipment
    conference interpreting equipment
    tour guide system
    silent disco headphones
    electronic voting pads
    laser barcode scanner
    bosch simultaneous interpretation system

    ReplyDelete
  14. I like this article very much, if Anyone Reads Your Blog, Each And Every one will appreciate you.
    Oracle Fusion HCM Online Training

    ReplyDelete
  15. The job and salary prospects of DATA SCIENTISTS make it an even more worthwhile educational investment for new entrants and professionals in the near term.

    data science course in hyderabad

    ReplyDelete
  16. You might be tempted to give up halfway through the course thinking that you cannot do it but the continuous motivation and excellent teaching by professionals will boost your morale and allow you to achieve it for sure.

    Data Science Course in Hyderabad

    ReplyDelete
  17. This comment has been removed by the author.

    ReplyDelete
  18. Amazing web journal I visit this blog it's extremely marvelous. Interestingly, in this blog content composed plainly and reasonable. The substance of data is educational.Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

    ReplyDelete
  19. Надявам се, че ще имате повече интересни и интересни статии. Благодаря ви много

    lưới chống chuột

    cửa lưới dạng xếp

    cửa lưới tự cuốn

    cửa lưới chống muỗi hà nội

    ReplyDelete
  20. thanks for sharing useful information ...we have learned so much information from your blog.. keep sharing
    SAP PM Training

    SCCM 2012 Training

    SCCM 2016 Training

    Splunk Admin Training

    ReplyDelete
  21. smart outsourcing solutions is the best outsourcing training
    in Dhaka, if you start outsourcing please
    visit us: graphic design training
    digital marketing training

    ReplyDelete
  22. Thanks for sharing, nice post! Post really provice useful information!

    Hương Lâm chuyên cung cấp bán máy photocopy và dịch vụ cho thuê máy photocopy giá rẻ, uy tín TP.HCM

    ReplyDelete


  23. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog,i want to share some Information about splunk tutorial and splunk videos . keep updates regularly..

    ReplyDelete
  24. This is so elegant and logical and clearly explained. Brilliantly goes through what could be a complex process and makes it obvious.
    ssis tutorial for beginners

    ReplyDelete
  25. Hey Loved the post! Great article and congrats on Reaching the To 50! I will be back to visit often

    Power Bi Training in Hyderabad

    ReplyDelete
  26. Thanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle Fusion Financials . Actually I was looking for the same information on internet for Oracle Fusion Financials and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more aboutOracle Fusion Financials . By attending Oracle Fusion FinancialsTraining .

    ReplyDelete
  27. Nice Blog !
    Our team at QuickBooks Customer Support Number 1-855-974-6537 is working continuously to help you assuage your QuickBooks problems in less time amid the COVID 19 pandemic.

    ReplyDelete
  28. Great article with very unique and useful information,,,
    Thank you,,,,Keep Updating,,,

    Power BI Online Training

    ReplyDelete
  29. Great article with very unique and useful information,,,
    Thank you,,,,Keep Updating,
    Villas in Rajahmundry
    Apartments in Rajahmundry

    ReplyDelete
  30. Nice Blog !
    QuickBooks accounting software is no strange from annoying errors or bugs. Among all these errors, QuickBooks Error 8007 is very common. In case you have also encountered this error,Our experts are available 24/7 around the clock for you.

    ReplyDelete
  31. Your blog is really amazing!! The way you represented your content is very nice manner.Incase If you face any technical problem QuickBooks, contact:
    <a href="https://goo.gl/maps/WrtPzoabs5wU2QHq7”>QuickBooks Customer Service phone number</a>

    ReplyDelete
  32. nice blog
    Our Digital Marketing course in Hyderabad focuses on Making you employeable.

    We make sure you have the right skill to get a job in Digital Marketing.
    digital marketing course in hyderabad

    ReplyDelete
  33. DIGITAL MARKETING COURSE IN HYDERABAD OUTCOMES
    Our Digital Marketing course in Hyderabad is a huge benefit to students, job seekers, freelancers and business owners. If you are a student or a job seeker this course will help you get a good job in Digital Marketing. If you are a freelancer or a business owner, this course would give you numerous tricks to generate more leads for your business. Honestly speaking, Digital Marketing is easy to learn. Executing and getting results from Digital Marketing isn’t difficult too. I would say it is a simple 3 step process.
    Step 01
    https://www.digitalbrolly.com/digital-marketing-course-in-hyderabad/

    ReplyDelete
  34. K9 Security England provides high quality, social dogs that are raised and trained specifically to obey and protect their owners. Similar to a child enrolled in martial arts classes, our pups start training at a very young age. By the time they're adults, just like the child, our dogs will be confident, disciplined, obedient, respectful and very well capable of defending on command. www.k9securityengland.com/

    ReplyDelete
  35. Hey!! This is such an amazing article that I have read today & you don't believe that I have enjoyed a lot while reading this amazing article. thanx for sharing such an amazing article with us. SEO Company in Hyderabad

    ReplyDelete
  36. Hey! Nice Blog, I have been using QuickBooks for a long time. One day, I encountered QuickBooks Customer Service in my software, then I called QuickBooks Customer Service. They resolved my error in the least possible time.

    ReplyDelete
  37. Login Your roboforex login Account To Read The Latest News About The Platform.s

    ReplyDelete
  38. I Think This Is Owsm Post, But If You Check This usd-rmb Review To Learn More About The Company, Their Products, And The Trading Platform. You Will Find Here How To Open An Account And How To Get Started Online Trading.

    ReplyDelete
  39. DiskGenius Professional Is the best crack software website for all Mac and Windows users around the world.

    ReplyDelete

  40. Click this LINK

    PowerDirector Pro is an all-in-one video production software that allows users to capture, edit, author and burn their movie creations onto DVDs, VCDs, SVCDs or MiniDVDs. ... It also features a Disc Making Wizard to quickly and easily author and burn videos onto DVDs or CDs.

    ReplyDelete
  41. MKVToolnix Crack is an amazing POST with good content.FINDCRACK is the best crack software site for all Mac and Windows users all over the world.

    ReplyDelete
  42. hello sir,I truly appreciate this post. I?ve been looking everywhere for this! Thank goodness I found it on Bing. You have made my day! ThanksUltra Adware Killer Crack

    ReplyDelete
  43. hello sir, This is a really good article. I recently discovered your blog. symptoms of sihr of separation.thank youMikroTik Beta crack

    ReplyDelete
  44. I am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it up
    Adobe After Effects CC

    ReplyDelete
  45. Nitro Pro Crack Keygen is a snappy and keen apparatus. It permits you to adjust, change over, make, sign, and offer standard PDF records. It is immediate and normal to use so you can accomplish work more productive way.

    ReplyDelete
  46. I’m glad that you just shared this helpful info with us. Please keep us informed like this
    Resolutiion

    ReplyDelete
  47. It’s great and fantastic piece. Keep it up as I look forward to read more from this website.
    Adobe Premiere Elements Full Crack

    ReplyDelete
  48. It’s great and fantastic piece. Keep it up as I look forward to read more from this website.
    Adobe Premiere Crack

    ReplyDelete
  49. Thanks for reading,
    This post was hopefully helpful to you.
    Your post was interesting and informative, so thanks for sharing.
    PC Crack

    ReplyDelete

  50. You provide a fantastic resource and you give it away for free.
    My favorite websites are those that recognize the importance of providing free resources.
    It is said that everything goes around and comes around again. Post more and keep us informed. Happy reading!
    Tenoshare 4ukey serial key

    ReplyDelete
  51. It’s great and fantastic piece. Keep it up as I look forward to read more from this website.
    Adobe Photoshop

    ReplyDelete
  52. thanks for share a good blog if like read more post visit it https://duckcreektraining.com/

    ReplyDelete
  53. blufftitler ultimate crack/

    Craigs and Shannons disappearing as more unusual names and spellings take over. ... Craigs, in particular, are becoming an endangered species: only 25 boys were given the name in 2017, compared with 705 in 1996 - a fall of 97%.

    ReplyDelete
  54. clip-studio paint crack The name Christopher is derived from the Greek word Christóforos, meaning “bearer of Christ.” It is composed of two Greek elements Christós (Christ) and phero (to bear, carry). ... Origin: Christopher is an English name of Greek origin meaning “bearer of Christ

    ReplyDelete
  55. I hope you're doing really well and enjoying the day ahead. We are a Digital Marketing agency focusing on Forex Trading Seo . We have been on top of Google search for the phrases you can see on the right and we will be happy to tell you how we did it.

    ReplyDelete
  56. What is Forex Trading Seo Expert ? In short, it is the art of optimizing your online business through a careful balance of content, social media, and external search criteria such as links and citations. This can have a significant impact on traffic volume coming to your site.

    ReplyDelete
  57. Trading Directory is an experienced and reliable Forex Trading company. Here We compare online brokers using transparent 10 point checklists, so you can easily compare the most important factors in one glance.

    ReplyDelete
  58. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly..
    ibm full form in india |
    ssb ka full form |
    what is the full form of dp |
    full form of brics |
    gnm nursing full form |
    full form of bce |
    full form of php |
    bhim full form |
    nota full form in india |
    apec full form

    ReplyDelete
  59. Timber dining Chairs

    Timber dining chairs set are the finest & spectacular wooden dining chairs. Timber dining chairs are durable & very comfy.

    Timber wood's biggest advantage is that it is strong and durable and lasts long as you can't afford to have a loosely carved chair for everyday dining use.

    ReplyDelete
  60. This comment has been removed by the author.

    ReplyDelete
  61. thank you for sharing your informative blog and keep sharing.
    Java course in Hyderabad

    ReplyDelete
  62. Upgrade your career clinical data mange ment from industry experts gets complete hands on servicess, on our sclinbio.

    ReplyDelete
  63. This is a good web site and alos very useful content i like this web site thanks for sharing this blog our https//.Sclinbio./com

    ReplyDelete
  64. Nice blog...it was so interesting to read & I feel thankful to you for posting such a good blog.
    keep updates regularly...

    ReplyDelete
  65. You are doing great work. Also download software from this link Splunk Enterprise Crackeado

    ReplyDelete
  66. Welcome to iTech Manthra, your premier destination for top-notch seo training in hyderabad
    ! We specialize in providing comprehensive courses that o

    ReplyDelete