物化檢視相關工具包
利用物化檢視進行查詢重寫,可以有效提高系統效能.但物化檢視查詢重寫受很多因素影響,物化檢視的快速重新整理功能也受很多因素影響.
Oracle為此提供了一些工具包,可以用於分析查詢重寫失敗原因,影響快速重新整理的原因,或者會自動生成支援快速重新整理DDL.
Oracle為此提供了一些工具包,可以用於分析查詢重寫失敗原因,影響快速重新整理的原因,或者會自動生成支援快速重新整理DDL.
1,使用dbms_advisor.tune_mview獲取物化檢視建立建議
1.1測試:
create table mv_base
as
select * from hr.employees;
1.1測試:
create table mv_base
as
select * from hr.employees;
--擬建立MV:
create materialized view mv_mview
refresh fast
as
select distinct department_id,manager_id
from mv_base;
begin
dbms_stats.gather_table_stats(user,'MV_BASE');
end;
/
dbms_stats.gather_table_stats(user,'MV_BASE');
end;
/
--調優任務:
declare
task_name varchar2(100);
BEGIN
task_name := 'My taskabcd';
DBMS_ADVISOR.TUNE_MVIEW(task_name,'create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base');
END;
/
declare
task_name varchar2(100);
BEGIN
task_name := 'My taskabcd';
DBMS_ADVISOR.TUNE_MVIEW(task_name,'create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base');
END;
/
--獲取建議:
SQL> select statement from USER_TUNE_MVIEW where task_name='My taskabcd';
CREATE MATERIALIZED VIEW LOG ON
"BNET"."MV_BASE" WITH ROWID, SEQUENCE ("MANAGER_ID","DEPARTMENT_ID")
INCLUDING NEW VALUES;
SQL> select statement from USER_TUNE_MVIEW where task_name='My taskabcd';
CREATE MATERIALIZED VIEW LOG ON
"BNET"."MV_BASE" WITH ROWID, SEQUENCE ("MANAGER_ID","DEPARTMENT_ID")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON "BNET"."MV_BASE"
ADD ROWID, SEQUENCE ("MANAGER_ID","DEPARTMENT_ID") INCLUDING NEW VALUES;
ADD ROWID, SEQUENCE ("MANAGER_ID","DEPARTMENT_ID") INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW BNET.MV_MVIEW
REFRESH FAST WITH ROWID
DISABLE QUERY REWRITE
AS
SELECT BNET.MV_BASE.DEPARTMENT_ID C1, BNET.MV_BASE.MANAGER_ID C2, COUNT(*) M1 FROM B
OCNET.MV_BASE GROUP BY BNET.MV_BASE.DE
PARTMENT_ID, BNET.MV_BASE.MANAGER_ID;
REFRESH FAST WITH ROWID
DISABLE QUERY REWRITE
AS
SELECT BNET.MV_BASE.DEPARTMENT_ID C1, BNET.MV_BASE.MANAGER_ID C2, COUNT(*) M1 FROM B
OCNET.MV_BASE GROUP BY BNET.MV_BASE.DE
PARTMENT_ID, BNET.MV_BASE.MANAGER_ID;
DROP MATERIALIZED VIEW BNET.MV_MVIEW
1.2常見錯誤:
1.2.1,task name 不能直接使用字元常量(IN OUT 引數)
SQL> declare
2 task_name varchar2(100);
3 BEGIN
4 task_name := 'My taskabcd';
5 DBMS_ADVISOR.TUNE_MVIEW('My taskx1','create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base');
6 END;
7 /
DBMS_ADVISOR.TUNE_MVIEW('My taskx1','create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base');
*
ERROR at line 5:
ORA-06550: line 5, column 27:
PLS-00363: expression 'My taskx1' cannot be used as an assignment target
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
1.2.1,task name 不能直接使用字元常量(IN OUT 引數)
SQL> declare
2 task_name varchar2(100);
3 BEGIN
4 task_name := 'My taskabcd';
5 DBMS_ADVISOR.TUNE_MVIEW('My taskx1','create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base');
6 END;
7 /
DBMS_ADVISOR.TUNE_MVIEW('My taskx1','create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base');
*
ERROR at line 5:
ORA-06550: line 5, column 27:
PLS-00363: expression 'My taskx1' cannot be used as an assignment target
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
1.2.2,基表沒有統計資料
create table mv_base1
as
select * from hr.employees;
declare
task_name varchar2(100);
BEGIN
task_name := 'My taskx3';
DBMS_ADVISOR.TUNE_MVIEW(task_name,'create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base1');
END;
/
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-03113: Cannot tune the MATERIALIZED VIEW statement
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1075
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 5
task_name varchar2(100);
BEGIN
task_name := 'My taskx3';
DBMS_ADVISOR.TUNE_MVIEW(task_name,'create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base1');
END;
/
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-03113: Cannot tune the MATERIALIZED VIEW statement
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1075
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 5
begin
dbms_stats.gather_table_stats(user,'MV_BASE1');
end;
/
dbms_stats.gather_table_stats(user,'MV_BASE1');
end;
/
declare
task_name varchar2(100);
BEGIN
task_name := 'My taskx3';
DBMS_ADVISOR.TUNE_MVIEW(task_name,'create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base1');
END;
/
task_name varchar2(100);
BEGIN
task_name := 'My taskx3';
DBMS_ADVISOR.TUNE_MVIEW(task_name,'create materialized view mv_mview refresh fast as select distinct department_id,manager_id from mv_base1');
END;
/
select statement from USER_TUNE_MVIEW where task_name='My taskx3';
--ok
--ok
1.2.3,沒有CREATE MATERIALIZED VIEW關鍵詞
declare
task_name varchar2(100);
BEGIN
task_name := 'My taskx4';
DBMS_ADVISOR.TUNE_MVIEW(task_name,'select distinct department_id,manager_id from mv_base');
END;
/
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-03112: Invalid CREATE MATERIALIZED VIEW statement
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1026
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 5
declare
task_name varchar2(100);
BEGIN
task_name := 'My taskx4';
DBMS_ADVISOR.TUNE_MVIEW(task_name,'select distinct department_id,manager_id from mv_base');
END;
/
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-03112: Invalid CREATE MATERIALIZED VIEW statement
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1026
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 5
2,使用dbms_mview.explain_mview分析物化檢視快速重新整理、查詢重寫能力
--根據前面建議,建立物化檢視:
CREATE MATERIALIZED VIEW LOG ON
"BNET"."MV_BASE" WITH ROWID, SEQUENCE ("MANAGER_ID","DEPARTMENT_ID")
INCLUDING NEW VALUES;
--根據前面建議,建立物化檢視:
CREATE MATERIALIZED VIEW LOG ON
"BNET"."MV_BASE" WITH ROWID, SEQUENCE ("MANAGER_ID","DEPARTMENT_ID")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW BNET.MV_MVIEW
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS
SELECT BNET.MV_BASE.DEPARTMENT_ID C1, BNET.MV_BASE.MANAGER_ID C2, COUNT(*) M1
FROM BNET.MV_BASE
GROUP BY BNET.MV_BASE.DEPARTMENT_ID, BNET.MV_BASE.MANAGER_ID;
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS
SELECT BNET.MV_BASE.DEPARTMENT_ID C1, BNET.MV_BASE.MANAGER_ID C2, COUNT(*) M1
FROM BNET.MV_BASE
GROUP BY BNET.MV_BASE.DEPARTMENT_ID, BNET.MV_BASE.MANAGER_ID;
select updatable,
rewrite_enabled,
rewrite_capability,
refresh_mode,
refresh_method,
fast_refreshable,
last_refresh_type,
staleness,
after_fast_refresh
from user_mviews
where mview_name = 'MV_MVIEW';
U R REWRITE_C REFRES REFRESH_ FAST_REFRESHABLE LAST_REF STALENESS AFTER_FAST_REFRESH
- - --------- ------ -------- ------------------ -------- ------------------- -------------------
N Y GENERAL DEMAND FAST DIRLOAD_DML COMPLETE FRESH FRESH
==>fast_refreshable:DIRLOAD_DML,僅支援特定條件下的重新整理
SQL> @?/rdbms/admin/utlxmv.sql
Table created.
SQL> exec dbms_mview.explain_mview('MV_MVIEW');
SQL> select capability_name,possible,msgtxt from MV_CAPABILITIES_TABLE;
CAPABILITY_NAME POSSIBLE MSGTXT
------------------------------ -------- --------------------------------------------------------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST Y <REWRITE Y
PCT_TABLE N relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y <REWRITE_PCT N general rewrite is not possible or PCT is not possible on any of the detail tabl
PCT_TABLE_REWRITE N relation is not a partitioned table
14 rows selected
---如果沒有count(*),有什麼影響
DROP MATERIALIZED VIEW BNET.MV_MVIEW;
DROP MATERIALIZED VIEW BNET.MV_MVIEW;
CREATE MATERIALIZED VIEW BNET.MV_MVIEW
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS
SELECT BNET.MV_BASE.DEPARTMENT_ID C1, BNET.MV_BASE.MANAGER_ID C2
FROM BNET.MV_BASE
GROUP BY BNET.MV_BASE.DEPARTMENT_ID, BNET.MV_BASE.MANAGER_ID;
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS
SELECT BNET.MV_BASE.DEPARTMENT_ID C1, BNET.MV_BASE.MANAGER_ID C2
FROM BNET.MV_BASE
GROUP BY BNET.MV_BASE.DEPARTMENT_ID, BNET.MV_BASE.MANAGER_ID;
select updatable,
rewrite_enabled,
rewrite_capability,
refresh_mode,
refresh_method,
fast_refreshable,
last_refresh_type,
staleness,
after_fast_refresh
from user_mviews
where mview_name = 'MV_MVIEW';
UPDATABLE REWRITE_ENABLED REWRITE_CAPABILITY REFRESH_MODE REFRESH_METHOD FAST_REFRESHABLE LAST_REFRESH_TYPE STALENESS AFTER_FAST_REFRESH
--------- --------------- ------------------ ------------ -------------- ------------------ ----------------- ---------- -------------------
N Y GENERAL DEMAND FAST DIRLOAD_LIMITEDDML COMPLETE FRESH FRESH
==>FAST_REFRESHABLE變化:DIRLOAD_DML-->DIRLOAD_LIMITEDDML
rewrite_enabled,
rewrite_capability,
refresh_mode,
refresh_method,
fast_refreshable,
last_refresh_type,
staleness,
after_fast_refresh
from user_mviews
where mview_name = 'MV_MVIEW';
UPDATABLE REWRITE_ENABLED REWRITE_CAPABILITY REFRESH_MODE REFRESH_METHOD FAST_REFRESHABLE LAST_REFRESH_TYPE STALENESS AFTER_FAST_REFRESH
--------- --------------- ------------------ ------------ -------------- ------------------ ----------------- ---------- -------------------
N Y GENERAL DEMAND FAST DIRLOAD_LIMITEDDML COMPLETE FRESH FRESH
==>FAST_REFRESHABLE變化:DIRLOAD_DML-->DIRLOAD_LIMITEDDML
SQL> exec dbms_mview.explain_mview('MV_MVIEW');
SQL> select capability_name,possible,msgtxt from MV_CAPABILITIES_TABLE;
CAPABILITY_NAME POSSIBLE MSGTXT
------------------------------ -------- --------------------------------------------------------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE Y
PCT_TABLE N relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list <==由Y變化為N
REFRESH_FAST_AFTER_ONETAB_DML N GROUP BY clause is present but no aggregate functions are used <==由Y變化為N
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled <==由Y變化為N
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possible or PCT is not possible on any of the detail tabl
PCT_TABLE_REWRITE N relation is not a partitioned table
15 rows selected
3,使用dbms_mview.explain_rewrite分析SQL查詢重寫能力
SQL> @?/rdbms/admin/utlxrw.sql
Table created.
exec dbms_mview.explain_rewrite('select distinct DEPARTMENT_ID,MANAGER_ID from mv_base');
select query,rewritten_txt,pass from REWRITE_TABLE;
QUERY
---------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
---------------------------------------------------------------------------------------------------------------------------------
PAS
---
select distinct DEPARTMENT_ID,MANAGER_ID from mv_base
SELECT MV_MVIEW.C1 DEPARTMENT_ID,MV_MVIEW.C2 MANAGER_ID FROM BNET.MV_MVIEW MV_MVIEW
NO
QUERY
---------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
---------------------------------------------------------------------------------------------------------------------------------
PAS
---
select distinct DEPARTMENT_ID,MANAGER_ID from mv_base
SELECT MV_MVIEW.C1 DEPARTMENT_ID,MV_MVIEW.C2 MANAGER_ID FROM BNET.MV_MVIEW MV_MVIEW
NO
select distinct DEPARTMENT_ID,MANAGER_ID from mv_base
SELECT DISTINCT MV_MVIEW.C1 DEPARTMENT_ID,MV_MVIEW.C2 MANAGER_ID FROM BNET.MV_MVIEW MV_MVIEW GROUP BY DEPARTMENT_ID,MANAGER_ID
YES
4,物化檢視重新整理
dbms_mview.refresh
dbms_mview.refresh_all_mviews
dbms_mview.refresh_dependent
dbms_refresh.refresh提供組重新整理功能
SELECT DISTINCT MV_MVIEW.C1 DEPARTMENT_ID,MV_MVIEW.C2 MANAGER_ID FROM BNET.MV_MVIEW MV_MVIEW GROUP BY DEPARTMENT_ID,MANAGER_ID
YES
4,物化檢視重新整理
dbms_mview.refresh
dbms_mview.refresh_all_mviews
dbms_mview.refresh_dependent
dbms_refresh.refresh提供組重新整理功能
5,DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY,用於針對特定負載提出物化檢視等建議,結果儲存在如下檢視:
SYSTEM.MVIEW_RECOMMENDATIONS
SYSTEM.MVIEW_RECOMMENDATIONS
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/18922393/viewspace-766836/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 物化檢視
- 物化檢視(zt)
- calcite物化檢視詳解
- Oracle普通檢視和物化檢視的區別Oracle
- 資料庫的物化檢視資料庫
- 物化檢視分割槽實驗
- 物化檢視幾個知識點
- ClickHouse 物化檢視學習總結
- Oracle物化檢視的建立及使用(二)Oracle
- Oracle物化檢視的建立及使用(一)Oracle
- ClickHouse 效能優化?試試物化檢視優化
- ClickHouse效能優化?試試物化檢視優化
- Oracle相關資料字典檢視Oracle
- 19 Oracle Data Guard 相關檢視Oracle
- 基於ROWID更新的物化檢視測試
- StarRocks 物化檢視重新整理流程及原理
- StarRocks 物化檢視重新整理流程和原理
- 物化檢視如何快速完成資料聚合操作?
- Linux檢視相關係統資訊Linux
- DBA_HIST相關檢視說明
- Postgres使用trigger自動重新整理物化檢視
- 物化檢視快速重新整理與ORA-00001
- 使用Materialise物化檢視解耦微服務架構解耦微服務架構
- Linux檔案內容檢視相關命令Linux
- 火山引擎ByteHouse:如何最佳化ClickHouse物化檢視能力?
- hg_job配置定時重新整理物化檢視
- Linux根據程式號,檢視相關資訊Linux
- 物化檢視日誌無法正常清除的解決方法
- 資料泵匯出匯入物化檢視(ORA-39083)
- 【MVIEW】Oracle通過物化檢視同步表資料及簡介ViewOracle
- mysql鎖分析相關的幾個系統檢視MySql
- 【AWR】Oracle awr相關檢視及體系介紹Oracle
- 檢視錶和索引碎片情況相關資訊索引
- ORACLE 物化檢視維護中相關基表發生欄位長度類DDL變更後需要重建或者全量重新整理Oracle
- 基於圖神經網路的動態物化檢視管理神經網路
- 用exp、imp遷移包含物化檢視日誌的資料
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- 使用SpringBoot+PostgreSQL物化檢視實現微服務設計模式 - vinsguruSpring BootSQL微服務設計模式
- [20230214]資料庫連線訪問asm相關檢視.txt資料庫ASM