oracle sql语句_7个维度查看oracle执行计划的sql语句执行效率

概述

如何快速判断 SOL 执行计划是否高效，其实这是一个知识和经验的完美结合过程。其实也有一些维度可以作为参考的，下面一起来看看吧。

---

从执行计划读效率--返回行与逻辑读比率

DROP TABLE t;CREATE TABLE t as select * from dba_objects;--CREATE INDEX idx ON t (object_id);alter session set statistics_level=all;set linesize 200set pagesize 200select * from t where object_id=6;SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last')); 

说明：总共获取 1 条记录(A-ROWS)，产生 1249 次逻辑读( Buffers),这里很明显就有问题了。

---

从执行计划读效率--评估值准确的重要性

1、创建测试数据

DROP TABLE t1;CREATE TABLE t1 (id, col1, col2, pad) AS SELECT rownum, CASE WHEN rownum>5000 THEN 666 ELSE rownum END,rownum, lpad('*',100,'*') FROM dualCONNECT BY level <= 10000;INSERT INTO t1 SELECT id+10000, col1, col2, pad FROM t1;INSERT INTO t1 SELECT id+20000, col1, col2, pad FROM t1;INSERT INTO t1 SELECT id+40000, col1, col2, pad FROM t1;INSERT INTO t1 SELECT id+80000, col1, col2, pad FROM t1;COMMIT;CREATE INDEX t1_col1 ON t1 (col1);DROP TABLE t2;CREATE TABLE t2 AS SELECT * FROM t1 WHERE mod(col2,19) != 0;ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (id);

2、统计分析，但是不搜集直方图

BEGIN dbms_stats.gather_table_st