How to Run SQL Tuning Advisor via Enterprise Manager Cloud Control | Oracle Database Performance Tuning Tutorial -13

Hi,

I will explain How to Run SQL Tuning Advisor via Enterprise Manager Cloud Control in this article.

 

Run SQL Tuning Advisor 

Read the previous article before this with the following link, if you don’t know what is the SQL Tuning Advisor.

What is the SQL Tuning Advisor and How to Run SQL Tuning Advisor Manually in Oracle | Oracle Database Performance Tuning Tutorial -10

 

 

Run SQL Tuning Advisor via Enterprise Manager Cloud Control

You can run SQL Tuning advisor both for Single SQL Statements and for lots of SQL statements same time. I have created SQL Tuning Task at the previous article manually.

 

I will create SQL Tuning task using Enterprise manager Cloud control with the following steps.

 

 

 

Step -1: Open SQL Tuning Advisor tab as follows.

 

 

Step -2: You should specify the parameters to schedule a job for running SQL Tuning Advisor as follows.

You can choose existing SQL Tuning Set, Historical SQL from AWR or TOP Activity for SQL Tuning Set parameter.

 

SQL Tuning Set is an database object  which is collection of SQL Statements that you can create for tuning.

Click the Historical SQL (AWR) link to select some SQL statements from AWR.

 

 

Step -3: I have chosen Historical SQL from AWR as follows, and selected some SQL Statements for Tuning as follows.

Click the Schedule SQL Tuning Advisor Button to continue.

 

 

 

 

 

Step -4: SQL Statements are specified for Tuning as follows. Now you can set other Scheduler parameters, then Click Submit button to create SQL Tuning Advisor job.

 

SQL Tuning Advisor task is processing as follows.

 

 

Step -5: SQL Tuning Advisor task is completed. You can display the SQL Tuning Result Summary as follows.

Review this report detailed, and then decide for any Tuning.

 

 

 

Click the Show All Results button.

 

 

Step -6: All results are as follows. You can implement all SQL Profiles or Validate All Profiles with SPA.

I have reviewed and decided to implement ALL SQL Profiles.

Click the Implement All SQL Profiles button.

 

 

 

Click Yes button to confirm this task.

 

Step -7: The Recommended SQL profiles has been created as follows.

 

You can view the Recommendations detailed by clicking View Recommendation button as follows.

You can compare Explain plans by clicking the Glasses mark at the righ side as follows.

 

 

Comparision of the explain plans are as follows.

 

You can review the Original Explain plan and New Explain plan with SQL Profile.

 

 

 

 

I will continue to explain Performance tuning tutorial in the next articles.

 

Next post link about Performance Tuning Tutorial is as follows.

What is the SQL Access Advisor and How to Run SQL Access Advisor | Oracle Database Performance Tuning Tutorial -11

 

 

 

 

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

 

SQL Tuning Tips and Tricks Tutorial in Oracle -3

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

3 comments

  1. I very much agree with what you wrote in this article, this is information that I had been looking for, thank you because you are willing to share with us.

Leave a Reply

Your email address will not be published. Required fields are marked *