What is the Automatic SQL Tuning and How to Automated SQL Tuning in Oracle | Oracle Database Performance Tuning Tutorial -12

Hi,

I will explain What is the Automatic SQL Tuning and How to Automated SQL Tuning in this article.

 

 

Automatic SQL Tuning in Oracle

 

You can read the following article before this to learn What is the SQL Tuning Advisor and SQL Access Advisor.

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

 

 

 

 

Oracle database can identify high load SQL statements automatically that are good candidates for Tuning with 10g version via Automatic Database Diagnostic Monitor (ADDM).

This process can be summarized with the following image.

 

 

A brief description of the SQL tuning steps are as follows.

 

Automated SQL Tuning in Oracle

 

Step 1:  AWR Top SQL identification is done and TOP SQLs are collected in the Workload Repository.

If you want to learn more detail about Workload Repository and its content, you should read the following article.

What is the Oracle AWR ( Automatic Workload Repository ) Report

 

 

Step 2:  Automatic Database Diagnostic Monitor (ADDM) identifies high load SQL statements automatically.

 

 

 

Step 3:  Run SQL Tuning Advisor to find a new, better execution plan.

 

 

if you haven’t read the SQL Tuning advisor article yet, you can read it by clicking this link.

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

 

 

 

Step 4: Review the new SQL Profiles, indexes and SQL Plan recommended by SQL Tuning Advisor, if they are better than the Old, you can accept SQL profile, create index and etc…

You should monitor these recommendations after implement, if they are not useful, you can drop or disable them again.

 

SQL Tuning process is automated and called  “Automatic SQL Tuning” with Oracle Database 11without requiring user intervention.  Automatic SQL Tuning uses the AUTOTASK framework and runs every night by default.

 

This process can be summarized with the following image.

 

If the ACCEPT_SQL_PROFILES task parameter is set to TRUE, then all SQL Profiles can be implemented automatically, other recommendations  (create new indexes, refresh stale statistics, or restructure SQL statements) are reported.

 

You can configure the Automatic SQL Tuning Task using Enterprise manager cloud control as follows.

 

Step -1: Open Automated Maintenance Tasks as follows.

 

 

All automated maintenance tasks are as follows. Click the Automatic SQL Tuning .

 

 

Automatic SQL Tuning task can be configured in this step.

You can enable or disable Automatic SQL Tuning (SYS_AUTO_SQL_TUNING_TASK )

 

 

You can enable or disable Automatic Implementation of SQL Profiles.

 

Click the View Report button to see All Analyzed SQLs.

 

All Analyzed SQLs are as follows. You can review ALL SQLs’ status and SQL Profile, Index, Restructure SQL, Alternative Plan and other status.

You can implement all SQL Profiles by clicking the Implement All SQL Profiles button.

 

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

 

Next post link about Performance Tuning Tutorial is as follows.

SQL Plan Management Using SQL Plan Baselines in Oracle | Oracle Database Performance Tuning Tutorial -14

 

 

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.

Leave a Reply

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