SQL Access Advisor

This Article will make you learn how Oracle 11g Sql Access Advisor helps in analyzing indexes, materialized views, and so on as it does in Oracle Database 10g, in Oracle Database 11g SQL Access Advisor also analyzes tables and queries to identify possible partitioning strategies—a great help when designing optimal schema. In Oracle Database 11g SQL Access Advisor can now provide recommendations with respect to the entire workload, including considering the cost of creation and maintaining access structures.

The Problem

Here's a typical problem. The SQL statement below is issued by the application. The query seems resource intensive and slow.

select store_id, guest_id, count(1) cnt
from res r, trans t
where r.res_id between 2 and 40
and t.res_id = r.res_id
group by store_id, guest_id
/

This SQL touches two tables, RES and TRANS; the latter is a child table of the former. Oracle DBA have been asked to find solutions to improve query performance—and SQL Access Advisor is the perfect tool for the job.

Oracle DBA can interact with the advisor either via command line or Oracle Enterprise Manager Database Control, but using the GUI provides somewhat better value by letting Oracle DBA visualize the solution and reducing many tasks to simple pointing and clicking.

To solve the problem in the SQL using SQL Access Advisor in Enterprise Manager, follow the steps below.

The first task is, of course, to fire up Enterprise Manager. On the Database Homepage, scroll down to the bottom of the page where Oracle DBA will see several hyperlinks.

Read full article at http://Oracleonline.info