[20120809]學習物化檢視(補充).txt
[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!
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-740521/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 物化檢視-學習篇
- [20120808]學習物化檢視.txt
- 物化檢視學習筆記筆記
- 物化檢視妙用__表同步使用物化檢視方法
- 【物化檢視】幾種物化檢視日誌分析
- Servlet學習補充Servlet
- 【物化檢視】根據物化檢視日誌快速重新整理物化檢視的過程
- oracle物化檢視Oracle
- vi的補充學習
- 物化檢視詳解
- oracle 建立物化檢視Oracle
- Oracle 物化檢視建立Oracle
- materialized view (物化檢視)ZedView
- 物化檢視 on commitMIT
- 物化檢視日誌表被DROP後建立物化檢視報錯
- Oracle如何根據物化檢視日誌快速重新整理物化檢視Oracle
- 物化檢視的快速重新整理測試與物化檢視日誌
- 普通檢視和物化檢視的區別
- ORACLE中的物化檢視（OCM複習總結）Oracle
- calcite物化檢視詳解
- Oracle物化檢視詳解Oracle
- ORACLE物化檢視測試Oracle
- Oracle 物化檢視案例分享Oracle
- 物化檢視梳理總結
- ZT 物化檢視詳解
- Oracle物化檢視語法Oracle
- ORACLE物化檢視入門Oracle
- 刷物化檢視並行並行
- oracle物化檢視系列(二)Oracle
- oracle物化檢視系列(一)Oracle
- [20150610]使用物化檢視同步資料.txt
- 12c 物化檢視 - 理解完全重新整理的物化檢視工作原理
- 物化檢視匯出匯入可能導致物化檢視日誌的失效
- Oracle普通檢視和物化檢視的區別Oracle
- [20121101]物化檢視與表(Materialized Views and Tables).txtZedView
- 物化檢視重新整理並非完全根據物化檢視日誌記錄
- [20121212]謹慎使用set autotrace traceonly檢視執行計劃[補充].txt
- CUUG oracle物化檢視講解Oracle