Skip to Main Content
  • Questions
  • Tuning DB Running Slow in Standard Edition

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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

Comments

Thank You

Abhishek, October 03, 2020 - 10:29 am UTC

Dear Chris,

As always, thank you for the suggestions.

I shall be checking out Databases for Developers: Performance as soon as possible.

Unfortunately, I missed the class on application-level tuning considerations as part of the DBA Masterclass series on 29th Sept 2020.

Would I get to watch it in the below mentioned link please? : https://go.oracle.com/LP=98110#On-Demand-Webinars

Regards,
Abhishek
Connor McDonald
October 05, 2020 - 12:10 am UTC

Yes, it will move over to the on-demand tab soon once we've completed the video editing

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.