Thanks for the question, abhishek.
Asked: September 21, 2020 - 10:30 am UTC
Last updated: October 05, 2020 - 12:10 am UTC
Version: Oracle 12c 12.2.0.1.0 Standard Edition
Viewed 1000+ times
You Asked
Clients complain that Application on the Oracle 12c DB is running slow. I do see some 100% CPU consumption for oracle processes with the top command. Now, because the oracle is standard edition, it does not have the diagnostic+tuning packs. In this situation, we are going for performance tuning. I shall be generating statspack report on the problematic DB. However, statspack will not give me any recommendation/advise to fix the issues, in the way ADDM does. So, how do I go about it? Can you please suggest me some tools by which I can check the issue as well as get some recommendations/advises.
Thank You.
and Chris said...
There may be some third-party tools, I'm not familiar enough with any to give recommendations. All the advisors in the database are part of diagnostics/tuning, so you're out of luck there.
Rather than relying on the advisors, I'd recommend spending some time learning how to tune SQL statements
I've recently launched a free course, Databases for Developers: Performance, which takes you through the basics tuning a SQL statement. Join this at:
https://devgym.oracle.com/pls/apex/dg/class/databases-for-developers-performance.html That said in many cases the biggest performance gains come from restructuring how the application sends SQL to the database. Advisors looking at single SQL statements won't help you here - you need to consider the whole transaction.
I'm covering these application-level tuning considerations as part of the DBA Masterclass series on 29th Sept 2020. There's still time to register if you want to join:
https://go.oracle.com/LP=98110
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment