The SQLAccess Advisor provides a number of procedures which
can be called to help decide which materialized views and indexes to create
and drop. It makes this decision using either a hypothetical workload, which
it bases on your schema, or from an actual workload which can be provided by
the user, from Oracle Trace or from the contents of the SQL cache.
Workloads may also be filtered according to different criteria,
such as only use queries containing these tables or queries which have a priority
between this range.
To prepare the environment for using the SQLAccess Advisor,
you perform the steps below. Materialized views and indexes can be present when
the advisor is run, but for the purposes of this example they are removed so
that you can see what the advisor will recommend. You need to also setup the
cache so that the SQLAccess Advisor can generate some recommendations. Perform
the following:
1.
Open a terminal window and execute the following commands
to clean up your environment:
sqlplus system/<password>
@prepare_for_advisor
The prepare_for_advisor.sql
script contains the following:
DROP MATERIALIZED VIEW all_cust_sales_mv; DROP MATERIALIZED VIEW costs_mv; DROP MATERIALIZED VIEW costs_pm_mv; DROP MATERIALIZED VIEW cust_sales_mv; DROP MATERIALIZED VIEW some_cust_sales_mv; DROP MATERIALIZED VIEW cust_id_sales_aggr; DROP MATERIALIZED VIEW sales_cube_mv; DROP MATERIALIZED VIEW sales_gby_mv; DROP MATERIALIZED VIEW CUST_TOTAL_SALES_MV; DROP MATERIALIZED VIEW CUST_SALES_TIME_MV;
5.
Now you need to create the cache. Execute the following
commands:
@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;
You will use the SQL Cache you just setup to get some recommendations
from the SQL Access Advisor. Perform the following:
1.
Open your browser and enter the following URL:
http://<hostname>:5500/em
Enter sys/<password> as SYSDBA and
click Login.
2.
Scroll to the bottom of the Home page and click on Advisor
Central under Related Links.
3.
Click on the SQL Access Advisor link.
4.
Make sure Current and Recent SQL Activity is
checked and click on Show Advanced Options.
5.
Scroll down. Under Filter Options, select Filter
workload based on these options and check the filter Only SQL statements
executed by the following users. Then enter SH in the Users
field and click Next.
6.
Click Both Materialized Views and Indexes and
click Next.
7.
Enter the task name OBE<Today's Date>,
select Standard for the Schedule Type and click Next.
Now you can look at the results and implement them if you
wish. Perform the following:
1.
Make sure your job is selected and click View Result.
2.
Click on the Recommendation ID 1 to see the details
of the Recommendations.
3.
Here 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.
4.
To see the SQL Script that will be executed when you
schedule the implementation, click Show SQL.
5.
Scroll down to the bottom and you will see the statements
to create the materialized view with the change you just made. Click OK.
6.
To implement the recommendations, click Schedule
Implementation.
7.
Enter OBEIMPL<today's date> for the Job
Name and click Submit.
8.
Your implementation job was created and is now running.
Click the Run History tab.
9.
Make sure your job is selected and click View.
10.
Review the summary and click your database breadcrumb.
11.
Click Materialized Views from the Administration
tab.
12.
Enter SH in the schema field and click Go.
13.
Notice that the newly created Materialized View appears
in the list.
Place
the cursor on this icon to hide all screenshots.
Bookmark
