1.count(*)的效率
select count(*) from Tab
select count(1) from Tab
select count(pk) from Tab
select count(col) from Tab
实际上 经过transform之后, 都会变成 count(*) 。(注意: col非空时)
<pre name="code" class="sql">SQL> alter session set sql_trace=true;
SQL> select count(*), count(1), count(name) from a;
COUNT(*) COUNT(1) COUNT(NAME)
---------- ---------- -----------
1 1 1
SQL> alter session set sql_trace=false;
/* 生成的trace 在 user_dump_dest 目录下*/
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /opt/ora10g/admin/RACDB/bdump
core_dump_dest string /opt/ora10g/admin/RACDB/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /opt/ora10g/admin/RACDB/udump
优化器是Oracle用来生成SQL执行计划的一个组件,优化器通过考虑以下几个因素确定一个最好的计划:
a.Where子句 b.统计信息 c.DB参数 d.Hints e.数据字典里的一些信息
SQL查询提交到Oracle服务器,要经过以下几个步骤:
1.Parse: 检查语法,分析语义。在这个阶段,Oracle会把SQL拆分成Query Block集,交给优化器去优化。
2.Transformer: 交给transformer的是经过Parse的SQL语句——Query Block的集合。 这个集合是Query Block互相关联的。Transformer的任务是试图改变这些Query Block的关系,以得到更好的plan。例如, View Merging, Predicate Pushing, Subquery Unesting ,Query Rewrite, OR-expansion.
讲了两种 CNT, filter predicate push down
3.评估:评估器产生三种类型的量: Selectivity(选择率),Cardinality(基数)和Cost(成本)。这三个量是相互关联的,Cardinality从Selectivity而来,通常Cost是通过cardinality计算出来的。准确的统计信息会提高这些量的准确度。
4.计划生成:计划生成器尝试为每个query block生成不同的access path(访问路径), join的方式与join顺序。然后评估出每个path的row source,最后生成执行计划。
5.Row Source运算
Row Source是指一个查询中,可以被访问的数据集。它可能是一个表,一个索引,一个没有合并的视图, 或者是一个Join的结果集。
在SQL里边最慢的两个事情(排序,链接)
5.1 数据结构
I. Heap Table(堆表)
II. 通过Index访问表:
B_tree
Index Bitmap
III. 通过以下方式组织的表: 注意 表一般修改少,表一起查询,单独查询情况少 才用 index cluster
Cluster
Hash cluster (对于比较长的列,用hash cluster可能比较好,比如ename, 先hash一次,然后再去找数据)
IOT 索引+列名存储 更新的时候可能行链接, 只能单块读
热块的一般解决:减少pctfree, 如果是索引上,可以建反向索引
5.2 一元Row Source运算
Access Path
Access path是从数据库获取数据的路径,数据可以通过以下途径定位并取得数据,具体采用哪个path,取决于优化器的计算出的成本。
Access Path/ Full Table Scan
I. 执行多块读
II. 读High Water Mark以下的所有块(包括被删除的空块)
III. 过滤数据行
Full Table Scan是顺序地读表里所有的行,同时过滤掉不符合条件的的数据,在全表扫描期间,Oracle会读取high-water-mark以下的所有块,即使这些块已经被删除。每行数据,都要检查一下是否满足where条件。
FTS所读取的表块都是彼此相邻的块,这意味着,它一次可以读取多个块,一次读多少个块取决于初始化参数DB_FILE_MULTIBLOCK_READ_COUNT的设置,注意: 这个设置只是个最高值, 真被一次读多少个块,还是取决于数据与操作系统。
Rowid Scan
rowID包含三个方面的信息:数据文件号, 块号, 当前行在块中的行号。
ROWID是指向一个特定数据行的指针。
ROWID可以用于block dump。
ROWID Scan可以用于:
当数据被索引了,index块包含所有行的ROWID,以便快速访问。
当常规方法不可用时,可以直接用ROWID来访问数据。
Access Path/Index Scan
Index类型
B*-tree
普通索引
倒序索引
反向索引
键值压缩
函数索引
索引组织表
Bitmap
域索引
Index Scan(Unique)
Index Scan(Range) 一般单块读
Index Scan(Full) //多块读
Index Scan(Fast Full) 多块读 , 表很宽,而且 而且访问的列是非空,有索引 一般会走index scan fast full
Index Skip(Scan)
Index Scan(Index Join)
Using Bitmap Indexes
Combining Bitmap Indexes
以上列举的这些access path没有包含,cluster, IOT, partition的访问。
Cluster是表数据的一种存储方式,把一组表的数据存储在同一组cluster块里,因为他们有相同的列,并且经常一起使用。例如, EMP表和DEPT表共享相同的数据。当你把EMP表和DEPT表聚合在一起, Oracle会把EMP表和DEPT两个表中,所有具有相同DEPTNO的行数据,都存储在相同的cluster块中。
Hash Cluster是单表cluster,它会把具有相同hash-key的值存储在一起,Oracle会用hash值来定位cluster里的数据行。所以具有相同值的数据在物理上存储在一起。
DBMS_ROWID这个包可以将数据文件号,块号与行号分解出来。
其它Row Source运算符
Sort - Hash
Buffer Sort
InList Iterator
View
Count Stop Key
First Row/(Min/Max)
5.3 二元Row Source运算
Join Tables
Nested Loop(嵌套循环连接)
Sort Merge Join(排序合并连接)
Hash Join(哈希连接)
Cartesian product(笛卡尔连接)
Join Types (Anti - Semi - Star)
5.4 多元 Row Source运算
Filter(过滤)
Concatenation(串连)
Connect by(递归连接)
Union all / intersect / Minus (集合操作)
6.分析执行计划
7.预估cardinality
8.计算成本
开10046
alter session set events '10046 trace name context forever , level 12';
select * from t_iot where object_id<=1000;
alter session set events '10046 trace name context off';
查看执行计划
select * from table(dbms_xplan.display_cursor(null,null,'last'))
证明全表扫描fetch 比较耗时
drop table t5;
create table t5 as select object_id, object_name from all_objects;
alter session set events '10046 trace name context forever , level 12';
select * from t5 where object_id=1000;
alter session set events '10046 trace name context off';
tkprof racdb1_ora_17133.trc racdb1_ora_17133.txt
racdb1_ora_17133.txt 中部分内容
select * from t5 where object_id=1000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.05 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.04 71 223 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.10 71 224 0 1
建测试表
create table test_objects as select * from all_objects;
alter table test_objects add constraint PK_test_objects primary key (OBJECT_ID);
create index index_SUBOBJECT_NAME on test_objects(SUBOBJECT_NAME);
exec dbms_stats.gather_table_stats(null,'test_objects');
Index Scan(Unique)案例
条件列是主键或者unique索引,并且是 = 操作符
explain plan for select * from test_objects where OBJECT_ID=1000;
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 93 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_OBJECTS | 1 | 93 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_TEST_OBJECTS | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Index Scan(Range)案例
当<,> between作用在索引列时候,一般会发生 index range Scan , 当然具体怎么走, 还要看ORACLE根据表的情况比较各种路径后作出决定,大表一般都遵循此规律,小表可能会走 table full scan
explain plan for select OBJECT_ID from test_objects where OBJECT_ID<1000;
select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 962 | 4810 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| PK_TEST_OBJECTS | 962 | 4810 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Index Scan(Full)案例
将要排序,排序的字段就是索引,大表, 排序的字段又是非空, 一般都会走 Index Full Scan
explain plan for select OBJECT_ID from test_objects order by OBJECT_ID;
select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45949 | 224K| 97 (2)| 00:00:02 |
| 1 | INDEX FULL SCAN | PK_TEST_OBJECTS | 45949 | 224K| 97 (2)| 00:00:02 |
------------------------------------------------------------------------------------
Index Scan(FAST Full)案例
大表,不排序, select 部分是索引,选择字段非空 一般都会走 Index Fast Full Scan
explain plan for select OBJECT_ID from test_objects
select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45949 | 224K| 23 (5)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PK_TEST_OBJECTS | 45949 | 224K| 23 (5)| 00:00:01 |
----------------------------------------------------------------------------------------
注意:如果选择的字段 定义可以是null的, 不会走索引, 因为B树索引里边木有null值
explain plan for select SUBOBJECT_NAME from test_objects
select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45949 | 91898 | 141 (1)| 00:00:02 |
| 1 | TABLE ACCESS FULL| TEST_OBJECTS | 45949 | 91898 | 141 (1)| 00:00:02 |
----------------------------------------------------------------------------------
explain plan for select SUBOBJECT_NAME from test_objects where SUBOBJECT_NAME is not null
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 153 | 306 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | INDEX_SUBOBJECT_NAME | 153 | 306 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Index Skip(Scan) 案例
如果先导列的NDV (number of distinct value ) 太大,成本太高,一般还是会走 table access full
set autot on
drop table tc3;
create table tc3 as select * from all_objects;
exec dbms_stats.gather_table_stats(null,'TC3');
create index tc3_own_obj on tc3(owner,object_id);
select object_name from tc3 where object_id=3;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TC3 | 1 | 30 | 14 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TC3_OWN_OBJ | 1 | | 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
drop index tc3_own_obj;
create index tc3_obj_own on tc3(object_id,owner);
select object_name from tc3 where owner='SCOTT';
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3829 | 115K| 141 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TC3 | 3829 | 115K| 141 (1)| 00:00:02 |
--------------------------------------------------------------------------
set autot off
Nested Loop 案例
两表关联,一张是小表, 一张是大表, 而且小表和大表的关联条件的列 在大表中有索引,一般会走 Nested Loop
create table tmp_test_objects as select * from test_objects where rownum<20;
create index index_tmp_SUBOBJECT_NAME on tmp_test_objects(SUBOBJECT_NAME);
exec dbms_stats.gather_table_stats(null,'tmp_test_objects');
explain plan for select * from tmp_test_objects a , test_objects b where a.OBJECT_ID=b.OBJECT_ID ;
select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 4199 | 21 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 19 | 4199 | 21 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TMP_TEST_OBJECTS | 19 | 2432 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_OBJECTS | 1 | 93 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_TEST_OBJECTS | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Hash Join 案例
两张都是大表, 不管连接条件有没有索引
有索引
explain plan for select * from test_objects a , test_objects b where a.SUBOBJECT_NAME=b.SUBOBJECT_NAME ;
select * from table(dbms_xplan.display);
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 520 | 96720 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 520 | 96720 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_OBJECTS | 153 | 14229 | 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN | INDEX_SUBOBJECT_NAME | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST_OBJECTS | 153 | 14229 | 2 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | INDEX_SUBOBJECT_NAME | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
无索引
explain plan for select * from test_objects a , test_objects b where a.owner=b.owner ;
select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 211M| 36G| | 2858 (75)| 00:00:35 |
|* 1 | HASH JOIN | | 211M| 36G| 4712K| 2858 (75)| 00:00:35 |
| 2 | TABLE ACCESS FULL| TEST_OBJECTS | 45949 | 4173K| | 142 (2)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TEST_OBJECTS | 45949 | 4173K| | 142 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------
Join Types (Anti - Semi - Star)
Semi:exists 方式
Anti: not exists 方式
MERGE JOIN CARTESIAN 案例
当 有一个表只有一行的时候才会出现 merge join cartesian 可以通过 alter session set "_optimizer_cartesian_enabled"=true; 来关闭或启用这种行为
drop table tmp1;
drop table tmp2;
drop table tmp3;
create table tmp1 as select * from all_objects where rownum<2;
create table tmp2 as select * from all_objects where rownum<3;
create table tmp3 as select * from all_objects;
exec dbms_stats.gather_table_stats(null,'tmp1');
exec dbms_stats.gather_table_stats(null,'tmp2');
exec dbms_stats.gather_table_stats(null,'tmp3');
commit;
select a.object_id,b.object_id,c.object_id from tmp1 a, tmp2 b, tmp3 c where a.object_id = c.object_id and b.object_id = c.object_id;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 146 (2)| 00:00:02 |
|* 1 | HASH JOIN | | 1 | 11 | 146 (2)| 00:00:02 |
| 2 | MERGE JOIN CARTESIAN| | 2 | 12 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TMP1 | 1 | 3 | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 2 | 6 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TMP2 | 2 | 6 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TMP3 | 45958 | 224K| 141 (1)| 00:00:02 |
------------------------------------------------------------------------------
concatenation (_b_tree_bitmap_plans) 案例
drop table tt2;
create table tt2 as select * from all_objects;
create index tt2_idx on tt2(owner);
create index tt2_idx1 on tt2(object_id);
exec dbms_stats.gather_table_stats(null,'TT2');
alter session set "_b_tree_bitmap_plans" = false;
select * from tt2 where owner ='SCOTT' or object_id = 1000;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3536 | 321K| 104 (0)| 00:00:02 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TT2 | 1 | 93 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TT2_IDX1 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| TT2 | 3535 | 321K| 102 (0)| 00:00:02 |
|* 5 | INDEX RANGE SCAN | TT2_IDX | 3535 | | 9 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
--b*tree index bitmap
alter session set "_b_tree_bitmap_plans" = true;
select /*^^222*/ * from tt2 where owner ='SCOTT' or object_id = 1000;
--当两个or条件来自不同的表, CBO不会使用index bitmap, 而会用or-expansion