[20120809]学习物化视图(补充).txt

1.测试环境:

SQL> select * from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

create table sales as select * from sh.sales;

insert into sales select * from sales;

insert into sales select * from sales;

insert into sales select * from sales;

CREATE MATERIALIZED VIEW LOG ON "SCOTT"."SALES" WITH ROWID, SEQUENCE ("CHANNEL_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES;

drop MATERIALIZED VIEW SCOTT.MV1;

CREATE MATERIALIZED VIEW SCOTT.MV1

 REFRESH FAST on commit 

 WITH ROWID

 enable QUERY REWRITE AS

 SELECT SCOTT.SALES.CHANNEL_ID C1,

 SUM("SCOTT"."SALES"."AMOUNT_SOLD") M1,

 COUNT("SCOTT"."SALES"."AMOUNT_SOLD") M2,

 COUNT(*) M3

 FROM SCOTT.SALES

 GROUP BY SCOTT.SALES.CHANNEL_ID;

2.开始测试:

SQL> set timing on

SQL> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)

---------- ---------------- ----------

         2        210770739    2064200

         4        109654416     947328

         3        463002086    4322624

         9       2219410.08      16592

Elapsed: 00:00:00.00

SQL> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  crucrsdqdrm0b, child number 0

-------------------------------------

select channel_id,sum(amount_sold),count(*) from sales group by

channel_id

Plan hash value: 2958490228

-------------------------------------------------------------------

| Id  | Operation                    | Name | E-Rows | Cost (%CPU)|

-------------------------------------------------------------------

|   0 | SELECT STATEMENT             |      |        |     3 (100)|

|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |      4 |     3   (0)|

-------------------------------------------------------------------

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

20 rows selected.

Elapsed: 00:00:00.02

--可以发现由于使用了物化视图,执行很快!

3.修改记录看看

回话1执行如下(不提交):

SQL1> update sales set amount_sold=amount_sold*1 where rownum<2;

1 row updated.

回话2执行如下:

SQL2> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)

---------- ---------------- ----------

         2        210770739    2064200

         4        109654416     947328

         3        463002086    4322624

         9       2219410.08      16592

SQL2> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  crucrsdqdrm0b, child number 0

-------------------------------------

select channel_id,sum(amount_sold),count(*) from sales group by

channel_id

Plan hash value: 2958490228

-------------------------------------------------------------------

| Id  | Operation                    | Name | E-Rows | Cost (%CPU)|

-------------------------------------------------------------------

|   0 | SELECT STATEMENT             |      |        |     3 (100)|

|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |      4 |     3   (0)|

-------------------------------------------------------------------

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

20 rows selected.

--由于没有提交,回话执行访问mview.

回到回话1执行:

SQL1> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)

---------- ---------------- ----------

         2        210770739    2064200

         4        109654416     947328

         3        463002086    4322624

         9       2219410.08      16592

Elapsed: 00:00:01.94

SQL1> @dpc

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------

SQL_ID  crucrsdqdrm0b, child number 1

-------------------------------------

select channel_id,sum(amount_sold),count(*) from sales group by

channel_id

Plan hash value: 2895541888

-------------------------------------------------------------------------------------

| Id  | Operation          | Name  | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |        |  9972 (100)|       |       |          |

|   1 |  HASH GROUP BY     |       |      4 |  9972   (4)|   899K|   899K| 2757K (0)|

|   2 |   TABLE ACCESS FULL| SALES |   7350K|  9734   (1)|       |       |          |

-------------------------------------------------------------------------------------

--可以发现,这种方式由于事务没有提交,mview也无法刷新,这种仅仅通过访问sales获得结果.

--这样访问时间变长.接着提交事务.

SQL1> commit;

SQL1> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)

---------- ---------------- ----------

         2        210770739    2064200

         4        109654416     947328

         3        463002086    4322624

         9       2219410.08      16592

Elapsed: 00:00:01.88

SQL> @dpc

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------

SQL_ID  crucrsdqdrm0b, child number 1

-------------------------------------

select channel_id,sum(amount_sold),count(*) from sales group by

channel_id

Plan hash value: 2895541888

-------------------------------------------------------------------------------------

| Id  | Operation          | Name  | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |       |        |  9972 (100)|       |       |          |

|   1 |  HASH GROUP BY     |       |      4 |  9972   (4)|   899K|   899K|  850K (0)|

|   2 |   TABLE ACCESS FULL| SALES |   7350K|  9734   (1)|       |       |          |

-------------------------------------------------------------------------------------

--奇怪,一旦出现这种情况后,执行计划不再访问物化视图.切换回话2执行.

SQL2> set timing on

SQL2> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)

---------- ---------------- ----------

         2        210770739    2064200

         4        109654416     947328

         3        463002086    4322624

         9       2219410.08      16592

Elapsed: 00:00:01.88

--回话2也一样,执行计划不再访问物化视图.

SQL2> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;

MVIEW_NAME                     LAST_REF LAST_REFRESH_DATE

------------------------------ -------- -------------------

MV1                            FAST     2012-08-09 15:55:39

Elapsed: 00:00:00.00

4.再修改记录并且commit看看.不再重复问题同上.

SQL1> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)

---------- ---------------- ----------

         2        210770739    2064200

         4        109654416     947328

         3        463002086    4322624

         9       2219410.08      16592

Elapsed: 00:00:01.81

SQL1> select MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE from user_mviews;

MVIEW_NAME                     LAST_REF LAST_REFRESH_DATE

------------------------------ -------- -------------------

MV1                            FAST     2012-08-09 16:01:19

SQL1> select sql_id,child_number,sql_text from v$sql where sql_id='crucrsdqdrm0b';

SQL_ID        CHILD_NUMBER SQL_TEXT

------------- ------------ --------------------------------------------------------------------------------

crucrsdqdrm0b            1 select channel_id,sum(amount_sold),count(*) from sales group by channel_id

Elapsed: 00:00:00.00

--可以发现仅仅存在child_number=1. child_number=0的不存在了.难道物化视图没刷新,还是有问题?

5.修改语句如下:

select channel_id,sum(amount_sold),count(*) from sales group by channel_id;

为

Select channel_id,sum(amount_sold),count(*) from sales group by channel_id;

SQL1> Select channel_id,sum(amount_sold),count(*) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)

---------- ---------------- ----------

         2        210770739    2064200

         4        109654416     947328

         3        463002086    4322624

         9       2219410.08      16592

Elapsed: 00:00:00.00

SQL1> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------

SQL_ID  aykmr2yy598f0, child number 0

-------------------------------------

Select channel_id,sum(amount_sold),count(*) from sales group by

channel_id

Plan hash value: 2958490228

-------------------------------------------------------------------

| Id  | Operation                    | Name | E-Rows | Cost (%CPU)|

-------------------------------------------------------------------

|   0 | SELECT STATEMENT             |      |        |     3 (100)|

|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |      4 |     3   (0)|

-------------------------------------------------------------------

--我仅仅改变一个字母,由于这条语句第1次执行没有在shared pool.可以发现可以使用物化视图.

6.接着重复试验看看.

SQL1> update sales set amount_sold=amount_sold*1 where rownum<2;

SQL1> commit;

SQL1> Select channel_id,sum(amount_sold),count(*) from sales group by channel_id;

SQL> CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)

---------- ---------------- ----------

         2        210770739    2064200

         4        109654416     947328

         3        463002086    4322624

         9       2219410.08      16592

Elapsed: 00:00:00.00

--只要没有出现在DML的rollback/commit前,该sql语句就没有问题.

6.刷新shared pool看看.

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.23

SQL> select channel_id,sum(amount_sold),count(*) from sales group by channel_id;

CHANNEL_ID SUM(AMOUNT_SOLD)   COUNT(*)

---------- ---------------- ----------

         2        210770739    2064200

         4        109654416     947328

         3        463002086    4322624

         9       2219410.08      16592

Elapsed: 00:00:00.14

SQL> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  crucrsdqdrm0b, child number 0

-------------------------------------

select channel_id,sum(amount_sold),count(*) from sales group by

channel_id

Plan hash value: 2958490228

-------------------------------------------------------------------

| Id  | Operation                    | Name | E-Rows | Cost (%CPU)|

-------------------------------------------------------------------

|   0 | SELECT STATEMENT             |      |        |     3 (100)|

|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV1  |      4 |     3   (0)|

-------------------------------------------------------------------

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

20 rows selected.

Elapsed: 00:00:00.34

SQL> select sql_id,child_number,sql_text from v$sql where sql_id='crucrsdqdrm0b';

SQL_ID        CHILD_NUMBER SQL_TEXT

------------- ------------ ---------------------------------------------------------------------------

crucrsdqdrm0b            0 select channel_id,sum(amount_sold),count(*) from sales group by channel_id

Elapsed: 00:00:00.01

--可以发现刷新后,一切OK!