Perform the Installing Oracle Database 10g on Windows tutorial.

Download the saa.zip file into your working directory (c:\wkdir).

Invoke SQL*Plus as follows: Select Start > Programs > Oracle - OraDb10g_home1 > Application Development > SQL Plus. Enter /nolog for the User Name and click OK.

Connect as sysdba:

connect / as sysdba

Now you must create the cache. Execute the following command:

@c:\wkdir\advisor_cache_setup

The advisor_cache_setup.sql script contains the following:

alter system flush shared_pool;
grant advisor to sh;

connect sh/sh;
SELECT c.cust_last_name, sum(s.amount_sold) AS dollars,
sum(s.quantity_sold) as quantity
FROM sales s , customers c, products p
WHERE c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND c.cust_state_province IN ('Dublin','Galway')
GROUP BY c.cust_last_name;

SELECT c.cust_id, SUM(amount_sold) AS dollar_sales 
  FROM sales s, customers c WHERE s.cust_id= c.cust_id  GROUP BY c.cust_id;

select sum(unit_cost) from costs group by prod_id;
 

Open your browser and invoke Enterprise Manager Database Control by entering the following URL:

http://<your host name>:1158/em

Enter sys/oracle as SYSDBA and click Login.

Scroll to the bottom of the Home page and click Advisor Central in the Related Links section.

Click the SQL Access Advisor link.

Select Use Default Options and click Continue.

Make sure Current and Recent SQL Activity is selected and then click Filter Options.

Scroll down. Under Filter Options, select Filter Workload Based on these Options. In the Users section, select Include only SQL statements executed by these users and enter SH in the Users field. Scroll up to the top of the page.

Click Next.

Select Both Indexes and Materialized Views in the Recommendation Types section and click Next.

Enter the task name OBE<any name > under Advisor Task Information. Make sure that Standard is selected for the Schedule Type in the Scheduling Options section. Click Next.

Click Submit.

Make sure that your job OBE<any name> is selected in the Results section, and click View Result.
Note: If you do not see the SQL Access Advisor Task you created, click Refresh. If you receive the message "No recommendations were generated because the workload was empty", you may need to restart this tutorial. There may be a timing issue depending on the memory of the machine you are using.

To see a summary of the recommendations, click Show Recommendation Action Counts.

To see the type of recommendations, click Show Statement Counts.

To see the types of statements in the workload, click Recommendations.

Click 1 in the ID column to see the recommendation details.

On this page you can customize the Object Name, Schema, and Tablespace to implement the recommendations. Scroll down and change the Schema Name for the Create Materialized View to SH and click OK.

To see the SQL Script that will be executed when you schedule the implementation, select recommendation ID 1 and click Show SQL.

Scroll down to the bottom and you will see the statements to create the materialized view with the change you just made. Click Done.

Click SQL Statements.

You see the SQL statements that will be improved by these recommendations. Scroll up.

Click Details.

You see the details of workload, task options and journal entries. Click Recommendations.

To implement the recommendations, click Schedule Implementation.

Enter OBEIMPL<any name > for the job name and click Submit.

The job has been successfully created. Click the Scheduler Job link.

Review the summary information and scroll to the bottom to check the status.

Click OK.

Click the Database Instance link to return to the Administration page.

Click Materialized Views in the Schema section.

Enter SH in the Schema field and click Go.

Note that the newly created materialized view appears on the list. Click the Database Instance link to return to the Administration page.