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

Hi,

I will explain What is the SQL Access Advisor and How to Run SQL Access Advisor in this article.

 

Oracle database has Query optimizer which can perform SQL Tuning automatically. This Query optimizer is running in two modes.

Normal Mode: Optimizer should operate with very limited time, and it must find a good execution in this limited time. This execution plan is not probably the best.

Tuning Mode: Optimizer operates in several minutes to tune SQL Statements, there is no limited time. Oracle optimizer try to find lots of execution plan which is better than the execution plan produced under the normal mode.

 

 

There are many advisors who direct DBAs and facilitate their some tasks in the Oracle Database as follows. Using these advisors, you can perform some tasks much more easily.

  • SQL Tuning Advisor
  • SQL Access Advisor
  • Memory Advisor
  • Undo Advisor
  • Data Recovery Advisor (DRA)

 

I have explained SQL Tuning Advisor, You can read it with the following link, if you want to learn SQL Tuning Advisor.

 

https://ittutorial.org/oracle-database-performance-tuning-tutorial-10-how-to-run-sql-tuning-advisor-via-enterprise-manager-cloud-control/

I will explain SQL Access Advisor which is introduced in Oracle 10g in this article.

The SQL Access Advisor is an useful advisor like SQL Tuning Advisor for Oracle DBAs. It makes suggestions about  SQL access methods (
Indexes, Materalized views, Materialized view logs, Partitions and etc  ) and helps resolve performance problems in Oracle database.

SQL Access Advisor usage model is as follows.

 

blank

 

SQL Access Advisor takes as input for a workload such as SQL Cache and SQL Tuning Sets from workload repository.

As a Result of running SQL Access Advisor, it makes suggestions  as follows.

Recommendation
Comprehensive
Limited
Add new (partitioned) index on table or materialized view. YES YES
Drop an unused index. YES NO
Modify an existing index by changing the index type. YES NO
Modify an existing index by adding columns at the end. YES YES
Add a new (partitioned) materialized view. YES YES
Drop an unused materialized view (log). YES NO
Add a new materialized view log. YES YES
Modify an existing materialized view log to add new columns
or clauses.
YES YES
Partition an existing unpartitioned table or index. YES YES

 

 

Let’s go to make an example about SQL Access Advisor.

You can run SQL Access advisor with Enterprise Manager and DBMS_ADVISOR package.

 

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

blank

 

 

Step -2:  Specify initial options as follows.

 

blank

 

 

Step -3: Select the source of workload in this step. I just give as Input an old SQL Tuning set as follows.

blank

 

Step -4: Select Indexes, Materialized Views and Partitioning option as follows.

blank

 

 

 

Step -5: Set the other options and click Next button.

blank

 

 

Step -6: SQL Access Advisor is processing now.

blank

 

it is completed, check Results.

blank

 

Step -6:  Summary report is as follows. you can review the recommendations.

blank

 

You can review recommendations detailed.

blank

 

 

Once reviewing is completed, if recommendation is good for you, then you can implement them.

 

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

 

 

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

 

https://ittutorial.org/oracle-sql-tuning-tips-and-tricks-tutorial-3/

 

 

 1,090 views last month,  18 views today

About Mehmet Salih Deveci

blank
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 mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Check Also

blank

Alter System Flush Shared pool in Oracle

Hi, I will explain Alter System Flush Shared pool in Oracle in this post.  4,615 views …

Leave a Reply