PURPOSE
This article is a step by step demonstration on how to use the Oracle10g SQLAccess Advisor (DBMS_ADVISOR) package to get recommendations on what Materialized Views to create in order to support a given set of queries (a workload) issued on the database. The workload is imported from the Oracle10g Automatic Workload Repository using a SQL Tuning Set.
SCOPE & APPLICATION
The article may be of interest to Data Warehouse Application Developers, Database Administrators and Oracle Support Engineers working with Summary Materialized View Query Rewrite in Oracle10g.
USING DBMS_ADVISOR WITH A WORKLOAD IMPORTED FROM THE AUTOMATIC WORKLOAD REPOSITORY
The demonstration in this article uses the SH sample schema, included with Oracle10g Sample Schemas.
The SQLAccess Advisor can process a set of actual queries issued against the database and generate a set of recommendations on what Materialized Views to create in order to give better performance with these queries. The set of queries is called a Workload.
In this article the Workload is obtained from the Automatic Workload Repository (AWR), a new feature with Oracle10g. The AWR collects, processes and maintains performance statistics for problem detection and tuning purposes both in memory and stored in database tables. The collected statistics include SQL statements which consume a significant portion of system resources. The Workload to be used by the SQLAccess Advisor is obtained from the AWR in the form of a SQL Tuning Set (STS.)
The demonstration consists of the following steps:
Prepare the SH sample schema for using SQLAccess Advisor PL/SQL API.
Run a demonstration set of resource intensive queries on the SH schema. These are examples of queries which can be run on a typical Data Warehouse schema. The queries will be automatically stored in the AWR. We will then use them as our Workload for tuning with SQLAccess Advisor.
Create the Workload to be processed by SQLAccess Advisor by creating a SQL Tuning Set and populating it with the top queries stored in the AWR during the time period where the demo queries where run in Step 2.
Process the Workload using SQLAccess Advisor by creating and executing a Tuning Task.
Review the generated recommendations and get them in SQL script form.
Implement the recommendations by executing the script generated in Step 5.
Rerun the demo queries and verify that the recommendations have improved their performance.
Clean up the elements of the SQLAccess Advisor tuning activity.
PRIVILEGES REQUIRED FOR USAGE OF SQLACCESS ADVISOR
In order for a database user to utilize DBMS_ADVISOR, the ADVISOR system privilege must first be granted to it.
SQL> connect / as sysdba
Connected.
SQL> grant advisor to sh;
Grant succeeded.
For using SQLAccess Advisor with a Workload imported from the AWR, the user must be able to work with SQL Tuning Sets. This requires that the ADMINISTER SQL TUNING SET system privilege be granted.
SQL> grant administer sql tuning set to sh;
Grant succeeded.
DBMS_ADVISOR provides functionality for writing output of the tuning process out to files. In order to be able to write such files, the database user must be granted the CREATE ANY DIRECTORY system privilege.
SQL> grant create any directory to sh;
Grant succeeded.
EXECUTING THE WORKLOAD TO BE TUNED BY SQLACCESS ADVISOR
Here we will execute the demonstration queries against the SH sample schema. These 4 queries are resource intensive and will be automatically stored into the AWR.
The queries are run from a separate script called wkld.sql. We will run the script 4 times to eliminate performance differences due to e.g. an empty Buffer Cache and to allow SQLAccess Advisor to work on a steady state. (The results shown below are from the 4th run.)
We gather Workload Repository snapshots manually immediately before and after executing the queries. This is in order to focus the tuning effort on a time interval that contains little other than the queries on which we wish to base the recommendations produced by SQLAccess Advisor.
SQL> connect / as sysdba
Connected.
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
PL/SQL procedure successfully completed.
SQL> @wkld.sql
SQL> @wkld.sql
SQL> @wkld.sql
SQL> @wkld.sql
Connected.
The first query performs approx. 290 Physical Reads and runs in 3.9 sec:
Elapsed: 00:00:03.89
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=24364 Card=918843 Bytes=57887109)
1 0 SORT (GROUP BY) (Cost=24364 Card=918843 Bytes=57887109)
2 1 HASH JOIN (Cost=473 Card=918843 Bytes=57887109)
3 2 TABLE ACCESS (FULL) OF ‘TIMES’ (TABLE) (Cost=15 Card=1826 Bytes=29216)
4 2 HASH JOIN (Cost=438 Card=918843 Bytes=43185621)
5 4 TABLE ACCESS (FULL) OF ‘PRODUCTS’ (TABLE) (Cost=3 Card=72 Bytes=2160)
6 4 PARTITION RANGE (ALL) (Cost=415 Card=918843 Bytes=15620331)
7 6 TABLE ACCESS (FULL) OF ‘SALES’ (TABLE) (Cost=415 Card=918843 Bytes=15620331)
Statistics
———————————————————-
3 recursive calls
6 db block gets
1777 consistent gets
288 physical reads
0 redo size
1043103 bytes sent via SQL*Net to client
26951 bytes received via SQL*Net from client
2406 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
36066 rows processed
The second query performs approx. 290 Physical Reads and runs in 3.9 sec (similar to first query:)
36066 rows selected.
Elapsed: 00:00:03.89
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=24364 Card=918843 Bytes=57887109)
1 0 SORT (GROUP BY) (Cost=24364 Card=918843 Bytes=57887109)
2 1 HASH JOIN (Cost=473 Card=918843 Bytes=57887109)
3 2 TABLE ACCESS (FULL) OF ‘TIMES’ (TABLE) (Cost=15 Card=1826 Bytes=29216)
4 2 HASH JOIN (Cost=438 Card=918843 Bytes=43185621)
5 4 TABLE ACCESS (FULL) OF ‘PRODUCTS’ (TABLE) (Cost=3 Card=72 Bytes=2160)
6 4 PARTITION RANGE (ALL) (Cost=415 Card=918843 Bytes=15620331)
7 6 TABLE ACCESS (FULL) OF ‘SALES’ (TABLE) (Cost=415 Card=918843 Bytes=15620331)
Statistics
———————————————————-
3 recursive calls
6 db block gets
1777 consistent gets
288 physical reads
0 redo size
1043103 bytes sent via SQL*Net to client
26951 bytes received via SQL*Net from client
2406 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
36066 rows processed
The third query performs approx. 140 Physical Reads and runs in 1.3 sec:
30312 rows selected.
Elapsed: 00:00:01.32
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4022 Card=183769 Bytes=10842371)
1 0 SORT (GROUP BY) (Cost=4022 Card=183769 Bytes=10842371)
2 1 HASH JOIN (Cost=458 Card=183769 Bytes=10842371)
3 2 TABLE ACCESS (FULL) OF ‘TIMES’ (TABLE) (Cost=15 Card=1826 Bytes=29216)
4 2 HASH JOIN (Cost=438 Card=183769 Bytes=7902067)
5 4 TABLE ACCESS (BY INDEX ROWID) OF ‘PRODUCTS’ (TABLE) (Cost=3 Card=14 Bytes=294)
6 5 INDEX (RANGE SCAN) OF ‘PRODUCTS_PROD_CAT_IX’ (INDEX) (Cost=1 Card=1)
7 4 PARTITION RANGE (ALL) (Cost=415 Card=918843 Bytes=20214546)
8 7 TABLE ACCESS (FULL) OF ‘SALES’ (TABLE) (Cost=415 Card=918843 Bytes=20214546)
Statistics
———————————————————-
2 recursive calls
3 db block gets
1776 consistent gets
137 physical reads
0 redo size
662888 bytes sent via SQL*Net to client
22727 bytes received via SQL*Net from client
2022 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
30312 rows processed
The fourth query performs no Physical Reads and runs in 0.6 sec:
209 rows selected.
Elapsed: 00:00:00.62
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=462 Card=556 Bytes=28356)
1 0 SORT (GROUP BY) (Cost=462 Card=556 Bytes=28356)
2 1 HASH JOIN (Cost=455 Card=45942 Bytes=2343042)
3 2 TABLE ACCESS (FULL) OF ‘TIMES’ (TABLE) (Cost=15 Card=1826 Bytes=29216)
4 2 HASH JOIN (Cost=438 Card=45942 Bytes=1607970)
5 4 TABLE ACCESS (FULL) OF ‘PRODUCTS’ (TABLE) (Cost=3 Card=4 Bytes=72)
6 4 PARTITION RANGE (ALL) (Cost=415 Card=918843 Bytes=15620331)
7 6 TABLE ACCESS (FULL) OF ‘SALES’ (TABLE) (Cost=415 Card=918843 Bytes=15620331)
Statistics
———————————————————-
0 recursive calls
0 db block gets
1777 consistent gets
0 physical reads
0 redo size
6143 bytes sent via SQL*Net to client
650 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
209 rows processed
SQL> connect / as sysdba
Connected.
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
PL/SQL procedure successfully completed.
CREATING A SQL TUNING SET WITH THE WORKLOAD QUERIES
In this section we create a SQL Tuning Set and populate it with the workload queries we ran in the previous section.
SQL> connect sh/sh
Connected.
First we find the identifiers of the Workload Repository Snapshots covering the time period on which we want SQLAccess Advisor to base its recommendations. Here we assume that these will be the two most recent snapshots in the AWR.
SQL> variable snap_id number;
begin
select max(snap_id) into :snap_id from dba_hist_snapshot;
end;
/
PL/SQL procedure successfully completed.
The Workload queries are imported from the AWR in the form of a SQL Tuning Set (STS.) First we create the STS:
SQL> exec DBMS_SQLTUNE.CREATE_SQLSET(‘MY_SQL_TUNING_SET’);
PL/SQL procedure successfully completed.
Then we load the queries from the AWR into the STS. This is done using the code below: a SQLSET_CURSOR is used to select the queries from the time period between the two most recent snapshots (:snap_id – 1 and :snap_id). The queries we are interested in are the ones which have executed more than once and have performed a significant number of Disk Reads (executions > 1 and disk_reads > 100.) The queries captured in the cursor are then loaded into the STS.
SQL> declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(:snap_id – 1, :snap_id,
‘executions > 1 and disk_reads > 100′,NULL,’disk_reads’)) p;
DBMS_SQLTUNE.LOAD_SQLSET(‘MY_SQL_TUNING_SET’, baseline_ref_cursor);
end;
/
PL/SQL procedure successfully completed.
We can verify that we have captured our demo workload into the STS using the following query:
SQL> set long 1024
SQL> select SQL_TEXT from table(DBMS_SQLTUNE.SELECT_SQLSET(‘MY_SQL_TUNING_SET’));
SQL_TEXT
——————————————————————————–
SELECT p.prod_category, t.week_ending_day, s.cust_id, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND p.prod_category = ‘Photo’
GROUP BY p.prod_category, t.week_ending_day, s.cust_id
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
declare
vsn varchar2(20);
begin
vsn := dbms_rcvman.getPackageVersion;
:pkg_vsn:pkg_vsn_i := vsn;
if vsn is not null then
:pkg_vsnub4 :=
to_number(substr(vsn,1,2) || substr(vsn,4,2) ||
substr(vsn,7,2));
end if;
end;
SELECT p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_name, s.time_id, t.week_ending_day
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a
nd intcol#=:2 and row#=:3 order by bucket
SELECT p.prod_name, s.time_id, t.week_ending_day,
SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_name, s.time_id, t.week_ending_day
7 rows selected.
We can see the first 3 queries from our workload, a PLSQL call and some recursive SQL statements in the output.
As expected, the fourth query has been left out as it performs less than 100 Disk Reads which in this demo is our criterion for significant resource consumption.
EXECUTING A SQLACCESS ADVISOR TUNING TASK ON THE DEMO WORKLOAD
Here we execute the SQLAccess Advisor Tuning Task on the queries we captured into the SQL Tuning Set in the previous section.
First we create a SQL Workload and import the SQL Tuning Set:
SQL> VARIABLE name VARCHAR2(20)
SQL> begin
:name := ‘MY_STS_WORKLOAD’;
DBMS_ADVISOR.CREATE_SQLWKLD(:name);
end;
/
PL/SQL procedure successfully completed.
SQL> VARIABLE saved_stmts NUMBER;
SQL> VARIABLE failed_stmts NUMBER;
SQL> begin
DBMS_ADVISOR.IMPORT_SQLWKLD_STS(‘MY_STS_WORKLOAD’, ‘MY_SQL_TUNING_SET’, ‘NEW’, 1, :saved_stmts, :failed_stmts);
end;
/
PL/SQL procedure successfully completed.
We can check the number of SQL statements that were successfully imported into the SQL Workload:
SQL> PRINT saved_stmts
SAVED_STMTS
———–
3
SQL> PRINT failed_stmts
FAILED_STMTS
————
3
Then we create a Tuning Task for the SQLAccess Advisor:
SQL> VARIABLE task_id NUMBER
SQL> VARIABLE task_name VARCHAR2(255)
SQL> begin
:task_name := ‘MY_SQLACCESS_TASK’;
DBMS_ADVISOR.CREATE_TASK (‘SQL Access Advisor’, :task_id, :task_name);
end;
/
PL/SQL procedure successfully completed.
SQL> PRINT task_id
TASK_ID
———-
689
Next step is to associate the SQL Workload with the Tuning Task:
SQL> begin
DBMS_ADVISOR.ADD_SQLWKLD_REF(‘MY_SQLACCESS_TASK’, ‘MY_STS_WORKLOAD’);
end;
/
PL/SQL procedure successfully completed.
Finally we execute the SQLAccess Advisor Tuning Task:
SQL> begin
DBMS_ADVISOR.EXECUTE_TASK(‘MY_SQLACCESS_TASK’);
end;
/
PL/SQL procedure successfully completed.
VIEWING THE RECOMMENDATIONS AND GENERATING SCRIPTS
The following queries show information on the recommendations generated by the SQLAccess Advisor:
This query shows each recommendation, its order of importance (rank) and the improvement in Optimizer Cost (benefit):
SQL> SELECT rec_id, rank, benefit
FROM user_advisor_recommendations
WHERE task_name = :task_name;
REC_ID RANK BENEFIT
———- ———- ———-
1 1 173344
2 2 6820
We have 2 recommendations for our demo queries on the SH sample schema.
This query shows which SQL statements in the workload are affected by the recommendations as well as the Optimizer Cost of the queries before and after the recommendations are implemented and the improvement to this Cost as a percentage value:
SQL> SELECT sql_id, rec_id, precost, postcost, (precost-postcost)*100/precost AS percent_benefit
FROM user_advisor_sqla_wk_stmts
WHERE task_name = :task_name AND workload_name = :name;
SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT
———- ———- ———- ———- —————
97 1 97460 10788 88.9308434
98 1 97460 10788 88.9308434
99 2 16088 9268 42.3918449
Our workload had 3 queries hence the above output.
The last query shows a list of actions recommended by the SQLAccess Advisor as a result of the tuning activity:
SQL> SELECT rec_id, action_id, substr(command,1,30) AS command
FROM user_advisor_actions
WHERE task_name = :task_name
ORDER BY rec_id, action_id;
REC_ID ACTION_ID COMMAND
———- ———- ——————————
1 1 CREATE MATERIALIZED VIEW LOG
1 3 CREATE MATERIALIZED VIEW LOG
1 5 CREATE MATERIALIZED VIEW LOG
1 7 CREATE MATERIALIZED VIEW
1 8 GATHER TABLE STATISTICS
2 1 CREATE MATERIALIZED VIEW LOG
2 3 CREATE MATERIALIZED VIEW LOG
2 5 CREATE MATERIALIZED VIEW LOG
2 9 CREATE MATERIALIZED VIEW
2 10 GATHER TABLE STATISTICS
10 rows selected.
We will now get the full recommendations generated by SQLAccess Advisor in script form.
Before we do that we need to create a directory for the script. This involves precreating the directory using an Operating System command (e.g. “mkdir” in Unix) followed by a CREATE DIRECTORY command:
SQL> create directory advisor_results as ‘/tmp/tunedir’;
Directory created.
SQL> exec DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name), ‘ADVISOR_RESULTS’, ‘sts_script.sql’);
PL/SQL procedure successfully completed.
To view sample contents from the generated script click here.
We can also generate an UNDO script for removing the implemented recommendations:
SQL> exec DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_name,’UNDO’), ‘ADVISOR_RESULTS’, ‘sts_undo.sql’);
To view sample contents from the generated undo script click here.
EXECUTING THE GENERATED RECOMMENDATIONS
To implement the recommendations we simply execute the generated script:
SQL> connect sh/sh
Connected.
SQL> @/tmp/tunedir/sts_script.sql
Materialized view log created.
Materialized view log created.
Materialized view log created.
Materialized view created.
PL/SQL procedure successfully completed.
Materialized view created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
TESTING THE EFFECT OF THE RECOMMENDATIONS
To test the effect of the recommendations we rerun the original workload script:
SQL> connect sh/sh
Connected.
SQL> set timing on
SQL> @wkld.sql
SQL> @wkld.sql
Connected.
The first query now runs in 0.6 sec compared to the original 3.9 sec.
36066 rows selected.
Elapsed: 00:00:00.61
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=70 Card=36066 Bytes=1731168)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF ‘MV$$_02DD0002’ (MAT_VIEW REWRITE) (Cost=70 Card=36066 Bytes=1731168)
Statistics
———————————————————-
0 recursive calls
0 db block gets
2677 consistent gets
268 physical reads
0 redo size
1043103 bytes sent via SQL*Net to client
26951 bytes received via SQL*Net from client
2406 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36066 rows processed
The second query now runs in approx. 0.6 sec (originally 3.9 sec) and does no Physical Reads:
36066 rows selected.
Elapsed: 00:00:00.59
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=70 Card=36066 Bytes=1731168)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF ‘MV$$_02DD0002’ (MAT_VIEW REWRITE) (Cost=70 Card=36066 Bytes=1731168)
Statistics
———————————————————-
0 recursive calls
0 db block gets
2677 consistent gets
0 physical reads
0 redo size
1043103 bytes sent via SQL*Net to client
26951 bytes received via SQL*Net from client
2406 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36066 rows processed
The third query now runs in approx. 0.5 sec (originally 1.3 sec):
30312 rows selected.
Elapsed: 00:00:00.48
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=320 Card=44659 Bytes=1607724)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF ‘MV$$_02DD0003’ (MAT_VIEW REWRITE) (Cost=320 Card=4465 9 Bytes=1607724)
Statistics
———————————————————-
0 recursive calls
0 db block gets
3382 consistent gets
1356 physical reads
0 redo size
662888 bytes sent via SQL*Net to client
22727 bytes received via SQL*Net from client
2022 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30312 rows processed
The fourth query has unchanged performance as it was not affected by the tuning recommendations:
209 rows selected.
Elapsed: 00:00:00.64
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=462 Card=556 Bytes=28356)
1 0 SORT (GROUP BY) (Cost=462 Card=556 Bytes=28356)
2 1 HASH JOIN (Cost=455 Card=45942 Bytes=2343042)
3 2 TABLE ACCESS (FULL) OF ‘TIMES’ (TABLE) (Cost=15 Card=1826 Bytes=29216)
4 2 HASH JOIN (Cost=438 Card=45942 Bytes=1607970)
5 4 TABLE ACCESS (FULL) OF ‘PRODUCTS’ (TABLE) (Cost=3 Card=4 Bytes=72)
6 4 PARTITION RANGE (ALL) (Cost=415 Card=918843 Bytes=15620331)
7 6 TABLE ACCESS (FULL) OF ‘SALES’ (TABLE) (Cost=415 Card=918843 Bytes=15620331)
Statistics
———————————————————-
0 recursive calls
0 db block gets
1777 consistent gets
1288 physical reads
0 redo size
6143 bytes sent via SQL*Net to client
650 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
209 rows processed
CLEANING UP AFTER THE TUNING ACTIVITY
Here we clean up the SH sample schema from the elements of our tuning activity.
To delete the Tuning Task we first need to reset it, then remove its association with the SQL Workload and delete the SQL Workload:
SQL> exec DBMS_ADVISOR.RESET_TASK(:task_name);
PL/SQL procedure successfully completed.
SQL> exec DBMS_ADVISOR.DELETE_SQLWKLD_REF(:task_name, :name);
PL/SQL procedure successfully completed.
SQL> exec DBMS_ADVISOR.DELETE_SQLWKLD(:name);
PL/SQL procedure successfully completed.
SQL> exec DBMS_ADVISOR.DELETE_TASK(:task_name);
PL/SQL procedure successfully completed.
After the Tuning Task is deleted we can drop the SQL Tuning Set:
SQL> exec DBMS_SQLTUNE.DROP_SQLSET(‘MY_SQL_TUNING_SET’);
PL/SQL procedure successfully completed.
Finally to restore the SH sample schema to its original state we run the undo script:
SQL> @/tmp/tunedir/sts_undo.sql
SQL> drop materialized view log on products;
Materialized view log dropped.
SQL> drop materialized view log on times;
Materialized view log dropped.
SQL> drop materialized view log on sales;
Materialized view log dropped.
Note: to complete the restoration you will need to drop the Materialized Views manually.
@ This is due to the unpublished Bug 3117513.
SAMPLE AUTOMATICALLY GENERATED RECOMMENDATIONS SCRIPT “STS_SCRIPT.SQL”
Rem SQL Access Advisor: Version 10.1.0.1 – Production
Rem
Rem Username: SH
Rem Task: MY_SQLACCESS_TASK
Rem Execution date: 30/03/2004 13:22
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW LOG ON
“SH”.”PRODUCTS”
WITH ROWID, SEQUENCE(“PROD_ID”,”PROD_NAME”,”PROD_CATEGORY”)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
“SH”.”TIMES”
WITH ROWID, SEQUENCE(“TIME_ID”,”WEEK_ENDING_DAY”)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
“SH”.”SALES”
WITH ROWID, SEQUENCE(“PROD_ID”,”CUST_ID”,”TIME_ID”,”AMOUNT_SOLD”)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW “SH”.”MV$$_02DD0002″
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.PRODUCTS.PROD_NAME C1, SH.TIMES.WEEK_ENDING_DAY C2, SH.SALES.TIME_ID
C3, SUM(“SH”.”SALES”.”AMOUNT_SOLD”) M1, COUNT(“SH”.”SALES”.”AMOUNT_SOLD”)
M2, COUNT(*) M3 FROM SH.PRODUCTS, SH.TIMES, SH.SALES WHERE SH.SALES.TIME_ID
= SH.TIMES.TIME_ID AND SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY
SH.PRODUCTS.PROD_NAME, SH.TIMES.WEEK_ENDING_DAY, SH.SALES.TIME_ID;
begin
dbms_stats.gather_table_stats(‘”SH”‘,'”MV$$_02DD0002″‘,NULL,dbms_stats.auto_sample_size);
end;
/
CREATE MATERIALIZED VIEW “SH”.”MV$$_02DD0003″
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.PRODUCTS.PROD_CATEGORY C1, SH.TIMES.WEEK_ENDING_DAY C2, SH.SALES.CUST_ID
C3, SUM(“SH”.”SALES”.”AMOUNT_SOLD”) M1, COUNT(“SH”.”SALES”.”AMOUNT_SOLD”)
M2, COUNT(*) M3 FROM SH.PRODUCTS, SH.TIMES, SH.SALES WHERE SH.SALES.TIME_ID
= SH.TIMES.TIME_ID AND SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY
SH.PRODUCTS.PROD_CATEGORY, SH.TIMES.WEEK_ENDING_DAY, SH.SALES.CUST_ID;
begin
dbms_stats.gather_table_stats(‘”SH”‘,'”MV$$_02DD0003″‘,NULL,dbms_stats.auto_sample_size);
end;
/
whenever sqlerror EXIT SQL.SQLCODE
begin
dbms_advisor.mark_recommendation(‘MY_SQLACCESS_TASK’,1,’IMPLEMENTED’);
dbms_advisor.mark_recommendation(‘MY_SQLACCESS_TASK’,2,’IMPLEMENTED’);
end;
/
SAMPLE AUTOMATICALLY GENERATED RECOMMENDATIONS SCRIPT “STS_UNDO.SQL”
Rem SQL Access Advisor: Version 10.1.0.1 – Production
Rem
Rem Username: SH
Rem Task: MY_SQLACCESS_TASK
Rem Execution date: 30/03/2004 13:22
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
Note: due to Bug:3117513 the undo script is empty.
SQL CODE FOR SAMPLE WORKLOAD FILE “WKLD.SQL”
connect sh/sh
set autotrace traceonly
set timing on
SELECT p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_name, s.time_id, t.week_ending_day;
SELECT p.prod_name, s.time_id, t.week_ending_day,
SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_name, s.time_id, t.week_ending_day;
SELECT p.prod_category, t.week_ending_day, s.cust_id, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND p.prod_category = ‘Photo’
GROUP BY p.prod_category, t.week_ending_day, s.cust_id;
SELECT p.prod_subcategory_desc, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND p.prod_subcategory_desc LIKE ‘%Audio’
GROUP BY p.prod_subcategory_desc, t.week_ending_day;
set autotrace off
set timing off