SQL Access Advisor using ORACLE Enterprise Manager

Step-1: Connect to ORACLE Enterprise Manager  (OEM) and go to "Advisor Central".

Step-2: After clicking on "Advisor Central", the following page will appear. Choose "SQL Advisors" section as highlighted in yellow in the image below.

Step-3: Now, we see Sql access advisor & SQL Tuning advisor. Choose SQL Access Advisor to proceed with our tuning task.

Step-4: Sql Acess Advisor wizard will open , Choose " Recommend New Access Structures" and click Continue

Step5: Choose Current and Recent SQL Activity. This option fetches SQL's from SQL Cache.

Step-6: Choose recommended options for indexes, materialized views, partitions as shown in below image and click next.

Step-7: Define the Advisor Task schedule, choose RUN Imediately and click next.

Step-8: Review the Advisor task options and click submit

Step-9: After Submit, it will take us to Advisor Central screen on which we can notice our advisor task in RUNNING status.

Step-10: After waiting for some time, the advisor task status will be changed to COMPLETED then click on the advisor task name to see the results.

Step11- Go to recommendations tab and click on SHOW SQL to view the recommendations from advisor.

Step12- After clicking on SHOW SQL, the following page will display with recommended objects to create like indexes, materialized views and/or partitions.

That's all.... We completed with tuning the SQL statements in SQL Cache using SQL Access Advisor from Oracle Enterprise Manager...