# 2. 优化物化视图定义
# 创建带索引的物化视图
SQL> CREATE MATERIALIZED VIEW fgedu.mv_fgedu_orders_summary_idx
2 BUILD IMMEDIATE
3 REFRESH FAST ON DEMAND
4 AS
5 SELECT customer_id, SUM(amount) total_amount, COUNT(*) order_count
6 FROM fgedu.fgedu_orders
7 GROUP BY customer_id;

# 创建索引
SQL> CREATE INDEX idx_mv_fgedu_orders_customer ON fgedu.mv_fgedu_orders_summary_idx(customer_id);

# 3. 测试查询性能
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.mv_fgedu_orders_summary WHERE customer_id = 1;

CUSTOMER_ID TOTAL_AMOUNT ORDER_COUNT
———– ———— ———–
1 12000 3

Elapsed: 00:00:00.01

SQL> SELECT * FROM fgedu.mv_fgedu_orders_summary_idx WHERE customer_id = 1;

CUSTOMER_ID TOTAL_AMOUNT ORDER_COUNT
———– ———— ———–
1 12000 3

Elapsed: 00:00:00.00

# 4. 调整物化视图刷新策略
# 创建定期刷新的物化视图
SQL> CREATE MATERIALIZED VIEW fgedu.mv_fgedu_orders_daily
2 BUILD IMMEDIATE
3 REFRESH FAST ON DEMAND
4 START WITH SYSDATE
5 NEXT SYSDATE + 1/24
6 AS
7 SELECT TRUNC(order_date) order_day, SUM(amount) total_amount, COUNT(*) order_count
8 FROM fgedu.fgedu_orders
9 GROUP BY TRUNC(order_date);

# 5. 验证调优效果
# 执行查询，比较性能
SQL> EXPLAIN PLAN FOR SELECT * FROM fgedu.mv_fgedu_orders_summary_idx WHERE customer_id = 1;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
————————————————————————–
Plan hash value: 3912729313

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
| 1 | INDEX RANGE SCAN| IDX_MV_FGEDU_ORDERS_CUSTOMER | 1 | 26 | 1 (0)| 00:00:01 |
————————————————————————–

Note
—– – dynamic statistics used: dynamic sampling (level=2)

# 6. 优化物化视图刷新
# 使用并行刷新
SQL> ALTER SESSION ENABLE PARALLEL DML;
SQL> EXEC DBMS_MVIEW.REFRESH(‘fgedu.mv_fgedu_orders_summary’, ‘F’, PARALLELISM => 4);

# 7. 测试大批量数据的物化视图性能
# 插入大量测试数据
SQL> BEGIN
2 FOR i IN 10000..20000 LOOP
3 INSERT INTO fgedu.fgedu_orders VALUES (i, MOD(i, 1000) + 1, SYSDATE – MOD(i, 365), i * 10);
4 IF MOD(i, 1000) = 0 THEN
5 COMMIT;
6 END IF;
7 END LOOP;
8 COMMIT;
9 END;
10 /

# 8. 刷新物化视图并测量时间
SQL> SET TIMING ON
SQL> EXEC DBMS_MVIEW.REFRESH(‘fgedu.mv_fgedu_orders_summary’, ‘F’);

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.23

# 9. 测试查询性能
SQL> SELECT * FROM fgedu.mv_fgedu_orders_summary WHERE customer_id = 1;

CUSTOMER_ID TOTAL_AMOUNT ORDER_COUNT
———– ———— ———–
1 123450 13

Elapsed: 00:00:00.00

Part05-风哥经验总结与分享

5.1 物化视图调优最佳实践

• 选择合适的物化视图类型：根据查询需求选择合适的物化视图类型
• 优化物化视图定义：优化物化视图的查询语句和索引
• 定期监控：定期监控物化视图使用情况，及时发现问题
• 分析瓶颈：分析物化视图性能的瓶颈，采取相应的措施
• 持续优化：根据数据变化频率持续优化物化视图刷新策略

5.2 物化视图调优注意事项

• 根据查询需求选择合适的物化视图类型
• 优化物化视图定义，提高查询性能
• 定期监控物化视图使用情况，及时发现问题
• 结合其他性能工具，全面分析物化视图性能
• 与开发团队分享物化视图调优结果，提高应用程序性能

5.3 物化视图调优建议

• 建立物化视图调优流程，定期执行物化视图配置和分析
• 培训DBA，提高物化视图管理能力
• 建立物化视图调优结果的审核机制
• 跟踪物化视图使用的变化趋势
• 与Oracle支持团队保持沟通，获取物化视图调优的最佳实践

更多视频教程www.fgedu.net.cn

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com