IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi,
I will explain What is the SQL Access Advisor and How to Run SQL Access Advisor in this article.
SQL Access Advisor in Oracle
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.
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.
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 |
Run SQL Access Advisor with Enterprise Manager
Let’s go to make an example about SQL Access Advisor.
You can run SQL Access advisor with Enterprise Manager and DBMS_ADVISOR package.
SQL Access Advisor Running
Step -1: Open SQL Access Advisor tab as follows.
Step -2: Specify initial options as follows.
Step -3: Select the source of workload in this step. I just give as Input an old SQL Tuning set as follows.
Step -4: Select Indexes, Materialized Views and Partitioning option as follows.
Step -5: Set the other options and click Next button.
Step -6: SQL Access Advisor is processing now.
it is completed, check Results.
Step -6: Summary report is as follows. you can review the recommendations.
You can review recommendations detailed.
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.
Next post link about Performance Tuning Tutorial is as follows.
Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.
