Shulou(Shulou.com)06/01 Report--

SQL Access Advisor index recommendations include bitmap, function-based, and B-tree indexes. A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. Bitmap indexes are most commonly used in a data warehouse to index unique or near-unique keys. SQL Access Advisor materialized view recommendations include fast refreshable and full refreshable MVs, for either general rewrite or exact text match rewrite.

SQL Access Advisor, using the TUNE_MVIEW procedure, also recommends how to optimize materialized views so that they can be fast refreshable and take advantage of general query rewrite.The DBMS_ADVISOR package consists of a collection of analysis and advisory functions and procedures callable from any PL/SQL program.

N alternative to querying the metadata to see the recommendations is to create a script of the SQL statements for the recommendations, using the procedure GET_TASK_SCRIPT. The resulting script is an executable SQL file that can contain DROP, CREATE, and ALTER statements. For new objects, the names of the materialized views, materialized view logs, and indexes are auto-generated by using the user-specified name template. You should review the generated SQL script before attempting to execute it.

[oracle@ORACLERAC2 ~] $pwd

/ home/oracle

[oracle@ORACLERAC2 ~] $mkdir scripts

SQL > select DBMS_METADATA.GET_DDL ('MATERIALIZED_VIEW','PROD_COST_MV','SH') from dual

DBMS_METADATA.GET_DDL ('MATERIALIZED_VIEW','PROD_COST_MV','SH')

CREATE MATERIALIZED VIEW "SH". "PROD_COST_MV" ("TIME_ID", "PROD_SUBCATEGORY",

SUM_UNITS ")

ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"

BUILD IMMEDIATE

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"

REFRESH FORCE ON DEMAND

USING DEFAULT LOCAL ROLLBACK SEGMENT

USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE

AS SELECT time_id, prod_subcategory,SUM (unit_cost) AS sum_units FROM costs c

Products p WHERE c.prod_id = p.prod_id GROUP BY time_id, prod_subcategory

SQL > show parameter query

NAME TYPE VALUE

-

Query_rewrite_enabled string TRUE

Query_rewrite_integrity string enforced

SQL > create directory tune_results as'/ home/oracle/scripts'

Directory created.

SQL > grant read,write,execute on directory tune_results to public

Grant succeeded.

SQL > grant advisor to sh

Grant succeeded.

SQL > conn sh/sh

Connected.

SQL > var task_cust_mv varchar2 (30)

SQL > var create_mv_ddl varchar2 (4000)

SQL > exec: task_cust_mv: = 'cust_mv'

PL/SQL procedure successfully completed.

SQL > exec: create_mv_ddl: = 'CREATE MATERIALIZED VIEW "SH." PROD_COST_MV "TABLESPACE" USERS "USING INDEX PCTFREE 10 INITRANS 1 MAXTRANS 255STORAGE (INITIAL 64k BUFFER_POOL DEFAULT) LOGGING USING INDEX TABLESPACE" USERS "PCTFREE 10 INITRANS 2 MAXTRANS 255STORAGE (INITIAL 64k BUFFER_POOL DEFAULT) REFRESH FORCE ON DEMAND AS SELECT time_id, prod_subcategory,SUM (unit_cost) AS sum_units FROM costs ccent products p WHERE c.prod_id = p.prod_id GROUP BY time_id, prod_subcategory'

PL/SQL procedure successfully completed.

SQL > exec dbms_advisor.tune_mview (: task_cust_mv,: create_mv_ddl)

#

Script generation using DBMS_ADVISOR.GET_TASK_SCRIPT function and DBMS_ADVISOR.CREATE_FILE procedure.

Now generate both the implementation and undo scripts and place them in / tmp/script_dir/mv_create.sql and / tmp/script_dir/mv_undo.sql, respectively.

#

PL/SQL procedure successfully completed.

SQL > EXECUTE DBMS_ADVISOR.CREATE_FILE (DBMS_ADVISOR.GET_TASK_SCRIPT (: task_cust_mv), 'TUNE_RESULTS','mv_create.sql')

PL/SQL procedure successfully completed.

SQL > EXECUTE DBMS_ADVISOR.CREATE_FILE (DBMS_ADVISOR.GET_TASK_SCRIPT (: task_cust_mv), 'TUNE_RESULTS','mv_undo.sql')

PL/SQL procedure successfully completed.

The following recommendation from TUNE_MVIEW contains the materialized view logs and multiple materialized view (Use USER_TUNE_MVIEW or DBA_TUNE_MVIEW views):

SCRIPT_TYPE

-

STATEMENT

IMPLEMENTATION

CREATE MATERIALIZED VIEW LOG ON "SH". COSTS "WITH ROWID, SEQUENCE (" PROD_ID "," TI "

ME_ID "," UNIT_COST ") INCLUDING NEW VALUES

IMPLEMENTATION

ALTER MATERIALIZED VIEW LOG FORCE ON "SH". "COSTS" ADD ROWID, SEQUENCE ("PROD_ID"

, "TIME_ID", "UNIT_COST") INCLUDING NEW VALUES

IMPLEMENTATION

CREATE MATERIALIZED VIEW LOG ON "SH". "PRODUCTS" WITH ROWID, SEQUENCE ("PROD_ID"

"PROD_SUBCATEGORY") INCLUDING NEW VALUES

IMPLEMENTATION

ALTER MATERIALIZED VIEW LOG FORCE ON "SH". "PRODUCTS" ADD ROWID, SEQUENCE ("PROD_

ID "," PROD_SUBCATEGORY ") INCLUDING NEW VALUES

IMPLEMENTATION

CREATE MATERIALIZED VIEW SH.PROD_COST_MV TABLESPACE "USERS" PCTFREE 10 INITRANS

1 MAXTRANS 255STORAGE (INITIAL 64k BUFFER_POOL DEFAULT) LOGGING USING INDEX

TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255STORAGE (INITIAL 64k BUF

FER_POOL DEFAULT) REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT SH.PR

ODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM ("SH". "COSTS". UNIT_COST ") M

1, COUNT ("SH". "COSTS". "UNIT_COST") M2, COUNT (*) M3 FROM SH.PRODUCTS, SH.COSTS WH

ERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY

, SH.COSTS.TIME_ID

The UNDO output is as follows:

UNDO

DROP MATERIALIZED VIEW SH.PROD_COST_MV

IMPLEMENTATION

CREATE MATERIALIZED VIEW LOG ON "SH". COSTS "WITH ROWID, SEQUENCE (" PROD_ID "," TI "

ME_ID "," UNIT_COST ") INCLUDING NEW VALUES

IMPLEMENTATION

ALTER MATERIALIZED VIEW LOG FORCE ON "SH". "COSTS" ADD ROWID, SEQUENCE ("PROD_ID"

, "TIME_ID", "UNIT_COST") INCLUDING NEW VALUES

IMPLEMENTATION

CREATE MATERIALIZED VIEW LOG ON "SH". "PRODUCTS" WITH ROWID, SEQUENCE ("PROD_ID"

"PROD_SUBCATEGORY") INCLUDING NEW VALUES

IMPLEMENTATION

ALTER MATERIALIZED VIEW LOG FORCE ON "SH". "PRODUCTS" ADD ROWID, SEQUENCE ("PROD_

ID "," PROD_SUBCATEGORY ") INCLUDING NEW VALUES

IMPLEMENTATION

CREATE MATERIALIZED VIEW SH.PROD_COST_MV TABLESPACE "USERS" PCTFREE 10 INITRANS

1 MAXTRANS 255STORAGE (INITIAL 64K BUFFER_POOL DEFAULT) LOGGING USING INDEX TA

BLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255STORAGE (INITIAL 64K BUFFER

_ POOL DEFAULT) REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT SH.PRODU

CTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM ("SH". "COSTS". "UNIT_COST") M1

COUNT ("SH". "COSTS". "UNIT_COST") M2, COUNT (*) M3 FROM SH.PRODUCTS, SH.COSTS WHERE

SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY, S

H.COSTS.TIME_ID

UNDO

DROP MATERIALIZED VIEW SH.PROD_COST_MV

25 rows selected.

SQL >

SQL > l

1 * select script_type,statement from dba_tune_mview

[oracle@ORACLERAC2 ~] $pwd

/ home/oracle

[oracle@ORACLERAC2 ~] $ls

Coldprod1.tar.gz coldprod2.tar.gz pwd scripts

[oracle@ORACLERAC2 ~] $cd scripts/

[oracle@ORACLERAC2 scripts] $ls

Mv_create.sql mv.sql mv_undo.sql shit

[oracle@ORACLERAC2 scripts] $

[oracle@ORACLERAC2 scripts] $

[oracle@ORACLERAC2 scripts] $ls-lart

Total 24

-rw-r--r--. 1 oracle oinstall 772 Aug 19 11:37 mv.sql

-rw-r--r--. 1 oracle oinstall 574 Aug 19 13:23 shit

Drwx-. 4 oracle oinstall 4096 Aug 19 13:23..

-rw-r--r--. 1 oracle oinstall 1314 Aug 19 15:17 mv_create.sql

Drwxr-xr-x. 2 oracle oinstall 4096 Aug 19 15:20.

-rw-r--r--. 1 oracle oinstall 1314 Aug 19 15:20 mv_undo.sql

[oracle@ORACLERAC2 scripts] $cat mv_create.sql

Rem SQL Access Advisor: Version 11.2.0.4.0-Production

Rem

Rem Username: SH

Rem Task: cust_mv

Rem Execution date:

Rem

CREATE MATERIALIZED VIEW LOG ON

"SH." COSTS.

WITH ROWID, SEQUENCE ("PROD_ID", "TIME_ID", "UNIT_COST")

INCLUDING NEW VALUES

ALTER MATERIALIZED VIEW LOG FORCE ON

"SH." COSTS.

ADD ROWID, SEQUENCE ("PROD_ID", "TIME_ID", "UNIT_COST")

INCLUDING NEW VALUES

CREATE MATERIALIZED VIEW LOG ON

"SH." PRODUCTS.

WITH ROWID, SEQUENCE ("PROD_ID", "PROD_SUBCATEGORY")

INCLUDING NEW VALUES

ALTER MATERIALIZED VIEW LOG FORCE ON

"SH." PRODUCTS.

ADD ROWID, SEQUENCE ("PROD_ID", "PROD_SUBCATEGORY")

INCLUDING NEW VALUES

CREATE MATERIALIZED VIEW SH.PROD_COST_MV

TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255STORAGE (INITIAL 64K BUFFER_POOL

DEFAULT) LOGGING USING INDEX TABLESPACE "USERS" PCTFREE 10 INITRANS 2

MAXTRANS 255STORAGE (INITIAL 64K BUFFER_POOL DEFAULT)

REFRESH FAST WITH ROWID

DISABLE QUERY REWRITE

AS SELECT SH.PRODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM ("SH". "COSTS". "UNIT_COST")

M1, COUNT ("SH". "COSTS". "UNIT_COST") M2, COUNT (*) M3 FROM SH.PRODUCTS

SH.COSTS WHERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY

SH.COSTS.TIME_ID

SQL > SELECT STATEMENT FROM USER_TUNE_MVIEW

2 WHERE TASK_NAME=: task_cust_mv AND SCRIPT_TYPE='IMPLEMENTATION'

STATEMENT

CREATE MATERIALIZED VIEW LOG ON "SH". COSTS "WITH ROWID, SEQUENCE (" PROD_ID "," TI "

ME_ID "," UNIT_COST ") INCLUDING NEW VALUES

ALTER MATERIALIZED VIEW LOG FORCE ON "SH". "COSTS" ADD ROWID, SEQUENCE ("PROD_ID"

, "TIME_ID", "UNIT_COST") INCLUDING NEW VALUES

CREATE MATERIALIZED VIEW LOG ON "SH". "PRODUCTS" WITH ROWID, SEQUENCE ("PROD_ID"

"PROD_SUBCATEGORY") INCLUDING NEW VALUES

ALTER MATERIALIZED VIEW LOG FORCE ON "SH". "PRODUCTS" ADD ROWID, SEQUENCE ("PROD_

ID "," PROD_SUBCATEGORY ") INCLUDING NEW VALUES

CREATE MATERIALIZED VIEW SH.PROD_COST_MV TABLESPACE "USERS" PCTFREE 10 INITRANS

1 MAXTRANS 255STORAGE (INITIAL 64k BUFFER_POOL DEFAULT) LOGGING USING INDEX

TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255STORAGE (INITIAL 64k BUF

FER_POOL DEFAULT) REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS SELECT SH.PR

ODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM ("SH". "COSTS". UNIT_COST ") M

1, COUNT ("SH". "COSTS". "UNIT_COST") M2, COUNT (*) M3 FROM SH.PRODUCTS, SH.COSTS WH

ERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY

, SH.COSTS.TIME_ID

Here is the keyword DISABLE QUERY REWRITE marked in red.

SQL > @ / home/oracle/scripts/mv_create.sql

Materialized view log created.

Materialized view log altered.

Materialized view log created.

Materialized view log altered.

Materialized view created.

SQL > set autot trace exp

SQL > SELECT SH.PRODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM ("SH". "COSTS". "UNIT_COST") M1 count ("SH". "COSTS". "UNIT_COST") M2, COUNT (*) M3 FROM SH.PRODUCTS, SH.COSTS

2 WHERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY, SH.COSTS.TIME_ID

Execution Plan

Plan hash value: 3557764342

-

| | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | Pstart | Pstop |

-

| | 0 | SELECT STATEMENT | | 21680 | 741k | | 541 (1) | 00:00:07 |

| | 1 | HASH GROUP BY | | 21680 | 741k | 3880K | 541 (1) | 00:00:07 |

| | * 2 | HASH JOIN | | 82112 | 2806K | | 139 (1) | 00:00:02 |

| | 3 | VIEW | index$_join$_001 | 72 | 1296 | | 2 (0) | 00:00:01 |

| | * 4 | HASH JOIN |

| | 5 | INDEX FAST FULL SCAN | PRODUCTS_PK | 72 | 1296 | | 1 (0) | 00:00:01 |

| | 6 | INDEX FAST FULL SCAN | PRODUCTS_PROD_SUBCAT_IX | 72 | 1296 | | 1 (0) | 00:00:01 |

| | 7 | PARTITION RANGE ALL | | 82112 | 1363K | | 137 (1) | 00:00:02 | 1 | 28 |

| | 8 | TABLE ACCESS FULL | COSTS | 82112 | 1363K | | 137 (1) | 00:00:02 | 1 | 28 |

-

Predicate Information (identified by operation id):

2-access ("COSTS". "PROD_ID" = "PRODUCTS". "PROD_ID")

4-access (ROWID=ROWID)

# # #

Here is the key, remember to change DISABLE QUERY REWRITE to ENABLE QUERY REWRITE.

"mv_create.sql" 39L, 1313C written

[oracle@ORACLERAC2 scripts] $

[oracle@ORACLERAC2 scripts] $

[oracle@ORACLERAC2 scripts] $ls

Mv_create.sql mv.sql mv_undo.sql shit

[oracle@ORACLERAC2 scripts] $cat mv_create.sql

Rem SQL Access Advisor: Version 11.2.0.4.0-Production

Rem

Rem Username: SH

Rem Task: cust_mv

Rem Execution date:

Rem

CREATE MATERIALIZED VIEW LOG ON

"SH." COSTS.

WITH ROWID, SEQUENCE ("PROD_ID", "TIME_ID", "UNIT_COST")

INCLUDING NEW VALUES

ALTER MATERIALIZED VIEW LOG FORCE ON

"SH." COSTS.

ADD ROWID, SEQUENCE ("PROD_ID", "TIME_ID", "UNIT_COST")

INCLUDING NEW VALUES

CREATE MATERIALIZED VIEW LOG ON

"SH." PRODUCTS.

WITH ROWID, SEQUENCE ("PROD_ID", "PROD_SUBCATEGORY")

INCLUDING NEW VALUES

ALTER MATERIALIZED VIEW LOG FORCE ON

"SH." PRODUCTS.

ADD ROWID, SEQUENCE ("PROD_ID", "PROD_SUBCATEGORY")

INCLUDING NEW VALUES

CREATE MATERIALIZED VIEW SH.PROD_COST_MV

TABLESPACE "USERS" PCTFREE 10 INITRANS 1 MAXTRANS 255STORAGE (INITIAL 64K BUFFER_POOL

DEFAULT) LOGGING USING INDEX TABLESPACE "USERS" PCTFREE 10 INITRANS 2

MAXTRANS 255STORAGE (INITIAL 64K BUFFER_POOL DEFAULT)

REFRESH FAST WITH ROWID

ENABLE QUERY REWRITE

AS SELECT SH.PRODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM ("SH". "COSTS". "UNIT_COST")

M1, COUNT ("SH". "COSTS". "UNIT_COST") M2, COUNT (*) M3 FROM SH.PRODUCTS

SH.COSTS WHERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY

SH.COSTS.TIME_ID

# # #

SQL > DROP MATERIALIZED VIEW SH.PROD_COST_MV

Materialized view dropped.

SQL > @ / home/oracle/scripts/mv_create.sql

CREATE MATERIALIZED VIEW LOG ON

*

ERROR at line 1:

ORA-12000: a materialized view log already exists on table 'COSTS'

Materialized view log altered.

CREATE MATERIALIZED VIEW LOG ON

*

ERROR at line 1:

ORA-12000: a materialized view log already exists on table 'PRODUCTS'

Materialized view log altered.

Materialized view created.

SQL >

SQL > set autot trace exp

SQL > SELECT SH.PRODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM ("SH". "COSTS". "UNIT_COST") M1 count ("SH". "COSTS". "UNIT_COST") M2, COUNT (*) M3 FROM SH.PRODUCTS, SH.COSTS

2 WHERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY, SH.COSTS.TIME_ID

Execution Plan

Plan hash value: 2761323600

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 13762 | 1007K | 22 (0) | 00:00:01 |

| | 1 | MAT_VIEW REWRITE ACCESS FULL | PROD_COST_MV | 13762 | 1007K | 22 (0) | 00:00:01 |

-

Note

-

-dynamic sampling used for this statement (level=2)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.