[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!

[20120809]學習物化檢視(補充).txt

相關文章