概述
如何快速判断 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'));
![a9ef4d3c9d9388f7e1d4e215b96d434c.png](https://i-blog.csdnimg.cn/blog_migrate/a16cdd67c9b7bf212209248b564ed025.jpeg)
![85b33d15173363ac976e34811c68e540.png](https://i-blog.csdnimg.cn/blog_migrate/9197af10bc3815520560a1d82f75b29d.jpeg)
说明:总共获取 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);
![d6c9151bc6add5d1b44f9f6cd13cb296.png](https://i-blog.csdnimg.cn/blog_migrate/c3ac62138e3d3e021a22bf70573e7949.jpeg)
2、统计分析,但是不搜集直方图
BEGIN dbms_stats.gather_table_st