数据库性能调优技术

一、概述

       这篇稿子是数据库性能调优技术的老三篇。上等同篇文章讲解了深刻摸底单表执行计划,单表执行计划是喻多表执行计划的底子。

       两摆设表的连年起三栽实施方式:1)嵌套循环连接;2)散列连接;3)归并连。两布置表连接时选立即三种植中的啊一样种啊?这取决于索引、以及总是的代价。在拖欠系列之老三篇(本文)文章被教授嵌套循环连接,第四篇稿子中教授散列连接,第五首文章被教授归并连续。在第六篇以后会分析IN子查询以及EXISTS子查询。

      达梦数据库、oracle数据库、sql server数据库在数据库执行计划者并凭本质区别,因此落得篇文章使达梦数据库作为实例数据库进行辨析,这首文章我们摘oracle 10g看成实例数据库。

朗诵毕本文后,应该能读懂就三独数据库的嵌套循环连接执行计划。

除此以外待说明一点的凡:因为oracle的源代码是勿明白的,我此描绘的是因实施计划、成本代价和10053文书进行反推的结果,尽管这样,从杀的大势达成讲话,不会见现出问题,仅开抛砖引玉。

 

仲、深入了解嵌套循环执行计划

Oracle数据库常用的示执行计划之措施产生半点栽:

1)set autotrace on 命令;

2)explain plan for 命令;

 

举例说明使用set
autotrace命令:

SQL> create table t1(c1 int,c2 int);

Table created.

SQL> create index it1c1 on t1(c1);

Index created.

SQL> insert into t1 values(1,1);

1 row created.

SQL> insert into t1 values(2,2);

1 row created.

SQL> commit;

Commit complete.

SQL> set autotrace on explain;

SQL> select c1 from t1 where c1=1;

        C1


         1

 

Execution Plan


  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)

   1    0   INDEX (RANGE SCAN) OF ‘IT1C1’ (INDEX) (Cost=1 Card=1 Bytes

          =13)

SQL> set autotrace off;

SQL>

 

       我们得以看到,执行了“set autotrace on
explain;”语句后,接下去的查询、插入、更新、删除语句就会见显示执行计划,直到执行“set autotrace
off;”语句。如果是装了“set autotrace on;”,除了会来得执行计划之外,还会展示有可行的统计信息。本系列文章非干查询代价的评估分析。

       我们打达同一段子代码中,我们发现在展示“select
c1 from t1 where c1=1;”执行计划之前展示了拖欠执行语句的查询结果。这说明:显示执行计划之前就是真地以拖欠查询语句执行了一如既往方方面面。这样见面带一个坏后果,假而我们今天发出同样修语句,执行的辰需半只钟头,即使我们仅用懂得该语句之实施计划,此种情形下,我们必须待半只钟头。因此,如果查询的性质好缓慢,我们好择选择使用explain plan for命令。

 

举例说明explain plan
for命令:

SQL> explain plan for
select c1 from t1 where c1=1;

Explained.

SQL> select * from
table(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT


Plan hash value: 2624316456


| Id | Operation        | Name | Rows |
Bytes | Cost (%CPU)| Time     |


|   0 | SELECT STATEMENT |       |     1
|    13 |     1   (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IT1C1 |     1
|    13 |     1   (0)| 00:00:01 |


Predicate Information (identified by
operation id):


PLAN_TABLE_OUTPUT


   1 – access(“C1″=1)

Note


   – dynamic sampling used for this
statement

17 rows selected.

SQL>

       使用“explain plan for 查询语句;”生成执行计划,然后使“select * from
table(DBMS_XPLAN.display);”语句显示执行计划。

 

      下面的情,将经过一些例子来喻嵌套理解执行计划:


1.勿带索引的嵌套连接的行计划该怎么了解?

      构造处测试场景:

create table t1(c1 int,c2 int);

insert into t1 values(1,1);

insert into t1 values(2,2);

 

create table t2(d1 int,d2 int);

create index it2d1 on t2(d1);

insert into t2 values(1,1);

insert into t2 values(2,2);

insert into t2 values(3,3);

insert into t2 values(4,4);

     查询语句也:

select /*+ USE_NL(t2)
*/ c1,c2 from t1 inner join t2 on c1=d2;

     该报告句被“/*+ USE_NL(t2)
*/”是咱们常说之hint提示,这里的USE_NL告诉优化程序下嵌套连接对表进行连接,t2为中表。此查询语句的实行计划吗:

Execution Plan


   0      SELECT STATEMENT
Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=78)

   1    0   NESTED LOOPS (Cost=4 Card=2
Bytes=78)

   2    1     TABLE ACCESS (FULL) OF
‘T1’ (TABLE) (Cost=2 Card=2 Bytes

          =52)

 

   3    1     TABLE ACCESS (FULL) OF
‘T2’ (TABLE) (Cost=1 Card=1 Bytes

          =13)

 

       “Execution Plan”显示优化程序用来执行查询的步骤。每一样步都吃与一个ID值(以0开始)。第二独数字显示当前操作符的父结点。在这执行计划遭遇,“NESTED LOOPS”的父结点是“SELECT STATEMENT”,“TABLE ACCESS (FULL) OF ‘T1’
(TABLE)”与“TABLE ACCESS (FULL) OF ‘T2’
(TABLE)”的父结点都是“NESTED LOOPS”。也说不定称为,操作符“SELECT STATEMENT”的子女结点是“NESTED LOOPS”,操作符“NESTED LOOPS”的首先只儿女结点是“TABLE ACCESS (FULL) OF ‘T1’
(TABLE)”,操作符“NESTED LOOPS”的老二个男女结点是“TABLE ACCESS (FULL) OF ‘T2’
(TABLE)”。

  

     第二实施表示,对表T1进行全表扫描,括号中的老三独价值是该手续的血本代价,这里不作阐述。第三尽代表,对T2进行全表扫描,这里还暗藏了一个细节:此处进行了c1=d1的判断。参考explain plan for生成的履行计划:

SQL> explain plan for select /*+
USE_NL(t2) */ c1,c2 from t1 inner join t2 on c

1=d2;

Explained.

SQL> select * from
table(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT


Plan hash value: 4033694122


| Id | Operation              | Name |
Rows | Bytes | Cost (%CPU)| Time     |


|   0 | SELECT STATEMENT   |      |    
2 |    78 |     4   (0)| 00:00:01 |

|   1 | NESTED LOOPS      |      |     2
|    78 |     4   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| T1   |    
2 |    52 |     2   (0)| 00:00:01 |

|* 3
|   TABLE ACCESS FULL| T2   |     1 |    13 |     1   (0)| 00:00:01
|


PLAN_TABLE_OUTPUT


Predicate Information (identified by
operation id):


   3 –
filter(“C1″=”D2”)

Note


   – dynamic sampling used for this
statement

19 rows selected.

SQL>

 

      这里显得的步骤0、1、2、3暨前面通过set autotrace on命令显示的履计划在意义上是同等的。红颜色表明t2单单会扫描到符合过滤条件c1=d1的笔录才会用控制权传于父节点“NESTED LOOPS”。

      对于该查询语句的施行,如果因此代码可以描述成这么:

for (rec1 is t1’s first
record; rec1!=NULL; rec1=rec1->next)

   for(rec2 is t2’s
first record; rec2!=NULL; rec2=rec->next)

   {

     
if(rec1.c1==rec2.d1)

          
put result(rec1.c1,rec1.c2) into result set;

   }

 

    也就是说,t1与t2先生成笛卡尔集,然后过过滤条件c1=d1过滤该笛卡尔集。

   其实,数据库执行该语句之手续为是类似之,下面是行该语句的步骤:

1)TAF(T1)(“TABLE ACCESS (FULL) OF
‘T1’”的简写)取得T1的首先长长的记下(1,1)传递给NL(“NESTED LOOPS”的简写),将控制权传递给操作符NL。

2)操作符NL将控制权传为老二独孩子TAF(T2)(“TABLE ACCESS (FULL) OF
‘T2’”的简写)。

3)TAF(T2)取得T2的首先长达记下(1,1),符合过滤条件c1=d1,将控制权传给操作符NL。

4)NL将记录(1,1)传于SS(“SELECT
STATEMENT”的简写),将控制权传被SS。

5)SS将记录(1,1)放入结果集合,将决定权限传给NL。

6)NL将决定权限传为TAF(T2)。

7)TAF(T2)取得T2表的生一样条记下(2,2),不符合条件c1=d1;取得下一致长达记下(3,3),不符合条件(4,4)。取得下一样长长的记下,取不交记录。T2表扫描完。将决定权限传递让NL。

8)NL将决定权限传被第一只儿女TAF(T1)。

9)TAF(T1)取得T1表的产一致长达记下(2,2)传递让NL,将控制权传为NL。

10)            NL将控制权传给老二单子女TAF(T2)。

11)            TAF(T2)取得T2的率先久(1,1),不相符过滤条件c1=d1;取得下同样长长的记下(2,2),满足条件c1=d1,将控制权传给操作符NL。

12)            NL将记录(2,2)传于SS,将控制权传于SS。

13)            SS将记录(2,2)放入结果集,将控制权传于NL。

14)            NL将决定权限传被TAF(T2)。

15)            TAF(T2)取得T2的下一样条记下(3,3),不相符过滤条件c1=d1;取得下同样长达记下(4,4),不抱过滤条件c1=d1;取得下一致长条记下,取不至记录。T2表扫描了。将控制权限传递让NL。

16)            NL将决定权限传于第一独男女TAF(T1)。

17)            TAF(T1)取得T1表的产一致长条记下,取不至记录,T1表明扫描完。将控制权传被NL,通知NL扫描了。

18)            NL将控制权限传给SS,通知SS操作了。

19)            SS将结果集(包含记录(1,1)、(2,2))发送给客户端。

 

 
在方的事例中,只询问显示t1的排列,如果只要出示t2的排,情况是同样,只是TAF(T2)需要将符合条件的T2记录传递给NL,然后NL组合成符合条件的(c1,c2,d1,d2)传递给SS。

select /*+ USE_NL(t2)
*/ c1,c2,d1,d2 from t1 inner join t2 on c1=d2;

相应的实行计划:

Execution Plan


   0      SELECT STATEMENT
Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=104

          )

   1    0   NESTED LOOPS (Cost=4 Card=2
Bytes=104)

   2    1     TABLE ACCESS (FULL) OF
‘T1’ (TABLE) (Cost=2 Card=2 Bytes

          =52)

   3    1     TABLE ACCESS (FULL) OF
‘T2’ (TABLE) (Cost=1 Card=1 Bytes

          =26)

 

2.动非唯一索引的嵌套连接的履行计划该如何晓得?

      测试数据及1丁讲述的一模一样。

  

   查询语句:

select /*+ index(t2)
*/ c1,c2,d1 from t1 inner join t2 on c1=d1;

 

   对应的履计划:

Execution Plan


   0      SELECT STATEMENT
Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=78)

   1    0   NESTED LOOPS (Cost=4 Card=2
Bytes=78)

   2    1     TABLE ACCESS (FULL) OF
‘T1’ (TABLE) (Cost=2 Card=2 Bytes

          =52)

 

   3    1     INDEX (RANGE SCAN) OF
‘IT2D1’ (INDEX) (Cost=1 Card=1 Byt

          es=13)

 

    使用explain plan对应的尽计划:

SQL> select * from
table(dbms_xplan.display);

PLAN_TABLE_OUTPUT


Plan hash value: 2841753667


| Id | Operation          | Name |
Rows | Bytes | Cost (%CPU)| Time     |


|   0 | SELECT STATEMENT   |       |    
2 |    78 |     4   (0)| 00:00:01 |

|   1 | NESTED LOOPS      |       |    
2 |    78 |     4   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| T1    |    
2 |    52 |     2   (0)| 00:00:01 |

|* 3 |   INDEX RANGE SCAN | IT2D1 |    
1 |    13 |     1   (0)| 00:00:01 |


PLAN_TABLE_OUTPUT


Predicate Information (identified by
operation id):


   3 –
access(“C1″=”D1”)    //此处的access表示以值3失命中索引IT2D1对应的B树。

Note


   – dynamic sampling used for this
statement

19 rows selected.

SQL>

   

 对于该查询语句之施行,如果因此代码可以描述成这么:

for (rec1 is t1’s first
record; rec1!=NULL; rec1=rec1->next)

   for(rec2 in t2’s
first record that match c1=d1; d1=c1; rec2=rec->next)

   {

        
put result(rec1.c1,rec1.c2,rec2.d1) into result
set;

   }

仍库实行该实施语句的手续为是接近的,下面是执行该实施语句的步调:

1)      TAF(T1)(“TABLE ACCESS (FULL) OF ‘T1’”的简写)取得表T1之率先长长的记下(1,1)传递给NL(“NESTED LOOPS”的简写),将控制权传递让NL。

2)      操作符NL将控制权传递给老二个男女IRS(IT2D1)(“INDEX (RANGE
SCAN) OF ‘IT2D1’”的简写)。

3)      IRS(IT2D1)使用键值(1)去命中索引IT2D1对应的B树,得到索引记录(1,rowid1)。将d1对应之数额(1)传递让NL,将控制权传递给NL。注意,在本例中,将d1的多寡上传是因select中出现了d1,也就是说要以d1的价传被客户端,如果select中莫d1,此处即与达到例中是平等的,不待传递d1叫上层。

4)      操作NL组合生成记录(1,1,1)(对应select项(c1,c2,d1))传被SS,将控制权传为SS。

5)      操作符SS将记录(1,1,1)放入结果集,将控制权传为NL。

6)      NL将控制权传被IRS(IT2D1)。此处传被IRS(IT2D1)的来由是,it2d1是未唯一索引,可能出少长条以上之笔录符合d1=1。

7)      IRS(IT2D1)取得下一样漫长记下(2,rowid2),因为2!=1,所以对应d1=1之目录查找已经终结,通知NL,将控制权限传递让NL。

8)      NL控制权传给TAF(T1)。

9)      TAF(T1)取得下一样漫长记下(2,2)传递让NL,将控制权传为NL。

10) NL将控制权传于IRS(IT2D1)。

11) IRS(IT2D1)使用键值(2)去命中索引IT2D1对应之B树,得到索引记录(2,rowid2)。将d1对应之数据(2)传递让NL,将控制权传递让NL。

12) 操作NL组合生成记录(2,2,2)传给SS,将控制权传被SS。

13) 操作符SS将记录(2,2,2)放入结果集,将控制权传给NL。

14) NL将控制权传为IRS(IT2D1)。

15) IRS(IT2D1)取得下一样条记下(3,rowid3),因为3!=2,所以针对应d1=2之目录查找已经结,通知NL查找了,将控制权限传递让NL。

16) NL控制权传为TAF(T1)。

17) TAF(T1)取得下一样条记下,发现就围观了,通知NL扫描了,将控制权传于NL。

18) NL通知SS扫描了,将控制权传被SS。

19) SS将结果集(包含记录(1,1,1)、(2,2,2))发送给客户端。

 

 

3.应用唯一索引的嵌套连接的履行计划该如何晓得?

 测试数据与1遭描述的平。删除原来的非唯一索引,建立唯一索引:

drop index
it2d1;

create unique index
iut2d1 on t2(d1);

 查询语句:

select /*+ index(t2)
*/ c1,c2,d1 from t1 inner join t2 on c1=d1;

 

对应之行计划:

Execution Plan


   0      SELECT STATEMENT
Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=78)

   1    0   NESTED LOOPS (Cost=2 Card=2
Bytes=78)

   2    1     TABLE ACCESS (FULL) OF
‘T1’ (TABLE) (Cost=2 Card=2 Bytes

          =52)

   3    1     INDEX (UNIQUE SCAN) OF
‘IUT2D1’ (INDEX (UNIQUE)) (Cost=0

           Card=1 Bytes=13)

 

  该实施计划同2着讲述的履行过程看似:

1)      TAF(T1)(“TABLE ACCESS (FULL) OF ‘T1’”的简写)取得表T1底第一漫漫记下(1,1)传递让NL(“NESTED LOOPS”的简写),将控制权传递给NL。

2)      操作符NL将控制权传递让老二独孩子IUS(IUT2D1)(“INDEX (UNIQUE SCAN) OF ‘IUT2D1””的简写)。

3)      IUS(IUT2D1)使用键值(1)去命中索引IUT2D1对应之B树,得到索引记录(1,rowid1)。将d1对应之数(1)传递给NL,将控制权传递让NL。

4)      操作NL组合生成记录(1,1,1)(对应select项(c1,c2,d1))传给SS,将控制权传被SS。

5)      操作符SS将记录(1,1,1)放入结果集,将控制权传给NL。

6)      NL控制权传于TAF(T1)。因为iut2d1是绝无仅有索引,所以只是恐有雷同修记下满足d1=1,所以这匪欲拿决定权限重新招给IUS(IUT2D1)。

7)      TAF(T1)取得下一样长长的记下(2,2)传递让NL,将控制权传给NL。

8)      NL将控制权传为IRS(IUT2D1)。

9)      IUS(IUT2D1)使用键值(2)去命中索引IUT2D1对应的B树,得到索引记录(2,rowid2)。将d1对应的多寡(2)传递给NL,将控制权传递给NL。

10) 操作NL组合生成记录(2,2,2)传给SS,将控制权传给SS。

11) 操作符SS将记录(2,2,2)放入结果集,将控制权传给NL。

12) NL控制权传被TAF(T1)。

13) TAF(T1)取得下一致漫漫记下,发现都围观完,通知NL扫描完,将控制权传为NL。

14) NL通知SS扫描完,将控制权传给SS。

15) SS将结果集(包含记录(1,1,1)、(2,2,2))发送给客户端。

 

 

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1756306

相关文章