oracle cost cardinality,Oracle 简单的嵌套循环cost计算方法！

虽然一直在强调在平时的优化过程当中，不要单方面的看cost来优化，但为了更深入了解嵌套循环，我们可以简单看下嵌套循环的cost计算方法。

1. 首先建立t1和t2测试表

PgSQL

SQL> conn scott/tiger

Connected.

SQL> create table t1 as select object_id id , object_name,owner from dba_objects;

Table created.

SQL> create table t2 as select object_id id , status,temporary from dba_objects;

Table created.

SQL> select count(*) from t1;

COUNT(*)

----------

91561

SQL> select count(*) from t2;

COUNT(*)

----------

91562

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

SQL>connscott/tiger

Connected.

SQL>createtablet1asselectobject_idid,object_name,ownerfromdba_objects;

Tablecreated.

SQL>createtablet2asselectobject_idid,status,temporaryfromdba_objects;

Tablecreated.

SQL>selectcount(*)fromt1;

COUNT(*)

----------

91561

SQL>selectcount(*)fromt2;

COUNT(*)

----------

91562

2. 给2个表收集统计信息

PgSQL

SQL> BEGIN

2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',

3 tabname => 'T1',

4 estimate_percent => 100,

5 method_opt => 'for all columns size AUTO',

6 degree => DBMS_STATS.AUTO_DEGREE,

7 cascade=>TRUE

8 );

9 END;

10 /

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',

3 tabname => 'T2',

4 estimate_percent => 100,

5 method_opt => 'for all columns size AUTO',

6 degree => DBMS_STATS.AUTO_DEGREE,

7 cascade=>TRUE

8 );

9 END;

10 /

PL/SQL procedure successfully completed.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

SQL>BEGIN

2DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',

3tabname=>'T1',

4estimate_percent=>100,

5method_opt=>'for all columns size AUTO',

6degree=>DBMS_STATS.AUTO_DEGREE,

7cascade=>TRUE

8);

9END;

10/

PL/SQLproceduresuccessfullycompleted.

SQL>BEGIN

2DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',

3tabname=>'T2',

4estimate_percent=>100,

5method_opt=>'for all columns size AUTO',

6degree=>DBMS_STATS.AUTO_DEGREE,

7cascade=>TRUE

8);

9END;

10/

PL/SQLproceduresuccessfullycompleted.

3. 查看嵌套循环的执行计划

PgSQL

SQL> explain plan for select /*+ use_nl(t1,t2) */ t1.owner,t1.object_name,t2.status from t1,t2 where t1.id=t2.id and t2.status='VALID';

Explained.

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

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1967407726

---------------------------------------------------------------------------

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

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 45781 | 2145K| 5976K (1)| 00:03:54 |

| 1 | NESTED LOOPS | | 45781 | 2145K| 5976K (1)| 00:03:54 |

| 2 | TABLE ACCESS FULL| T1 | 91561 | 3218K| 147 (1)| 00:00:01 |

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

---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("T1"."ID"="T2"."ID" AND "T2"."STATUS"='VALID')

15 rows selected.

4. 根据解析的sql，查看plan table里面的io_cost信息

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

SQL>explainplanforselect/*+ use_nl(t1,t2) */t1.owner,t1.object_name,t2.statusfromt1,t2wheret1.id=t2.idandt2.status='VALID';

Explained.

SQL>select*fromtable(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Planhashvalue:1967407726

---------------------------------------------------------------------------

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

---------------------------------------------------------------------------

|0|SELECTSTATEMENT||45781|2145K|5976K(1)|00:03:54|

|1|NESTEDLOOPS||45781|2145K|5976K(1)|00:03:54|

|2|TABLEACCESSFULL|T1|91561|3218K|147(1)|00:00:01|

|*3|TABLEACCESSFULL|T2|1|12|65(0)|00:00:01|

---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

PredicateInformation(identifiedbyoperationid):

---------------------------------------------------

3-filter("T1"."ID"="T2"."ID"AND"T2"."STATUS"='VALID')

15rowsselected.

4.根据解析的sql，查看plantable里面的io_cost信息

PgSQL

SQL> select OPERATION,OPTIONS,IO_COST,CPU_COST FROM PLAN_TABLE;

OPERATION OPTIONS IO_COST CPU_COST

---------------------------------------- ---------------------------------------- ---------- ----------

SELECT STATEMENT 5926815 2.0002E+12

NESTED LOOPS 5926815 2.0002E+12

TABLE ACCESS FULL 146 21178075

TABLE ACCESS FULL 65 21845714

1

2

3

4

5

6

7

8

SQL>selectOPERATION,OPTIONS,IO_COST,CPU_COSTFROMPLAN_TABLE;

OPERATIONOPTIONSIO_COSTCPU_COST

---------------------------------------- ---------------------------------------- ---------- ----------

SELECTSTATEMENT59268152.0002E+12

NESTEDLOOPS59268152.0002E+12

TABLEACCESSFULL14621178075

TABLEACCESSFULL6521845714

PgSQL

SQL> select pname, pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

PNAME PVAL1

------------------------------ ----------

CPUSPEED

CPUSPEEDNW 3364.86486

IOSEEKTIM 10

IOTFRSPEED 4096

MAXTHR

MBRC

MREADTIM

SLAVETHR

SREADTIM

9 rows selected.

下面先来看一下nested loops成本的计算方法：COST = (OUTER TABLE IO COST) + (OUTER TABLE CARDINALITY) * (INNER TABLE IO COST) + CPU COST

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

SQL>selectpname,pval1fromsys.aux_stats$wheresname='SYSSTATS_MAIN';

PNAMEPVAL1

------------------------------ ----------

CPUSPEED

CPUSPEEDNW3364.86486

IOSEEKTIM10

IOTFRSPEED4096

MAXTHR

MBRC

MREADTIM

SLAVETHR

SREADTIM

9rowsselected.

下面先来看一下nestedloops成本的计算方法：COST=(OUTERTABLEIOCOST)+(OUTERTABLECARDINALITY)*(INNERTABLEIOCOST)+CPUCOST

所以 IO COST = CEIL(outer table blocks*mreadtim/db_file_multiblock_read_count/sreadtim)+ CEIL((outer table cardinality)*inner table

然后我们看一下上面查询出来的信息，由于ＭBRC=0，所以这里采用的是非工作量统计方式：mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed:

PgSQL

SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +

2 (select value

3 from v$parameter

4 where name = 'db_file_multiblock_read_count') *

5 (select value from v$parameter where name = 'db_block_size') /

6 (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "mreadtim"

7 from dual;

mreadtim

----------

88

1

2

3

4

5

6

7

8

9

10

11

SQL>select(selectpval1fromsys.aux_stats$wherepname='IOSEEKTIM')+

2(selectvalue

3fromv$parameter

4wherename='db_file_multiblock_read_count')*

5(selectvaluefromv$parameterwherename='db_block_size')/

6(selectpval1fromsys.aux_stats$wherepname='IOTFRSPEED')"mreadtim"

7fromdual;

mreadtim

----------

88

PgSQL

sreadtim=ioseektim+db_block_size/iotfrspeed :

1

sreadtim=ioseektim+db_block_size/iotfrspeed:

PgSQL

SQL> select (select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM') +

2 (select value from v$parameter where name = 'db_block_size') /

3 (select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED') "sreadtim"

4 from dual;

sreadtim

----------

12

1

2

3

4

5

6

7

8

SQL>select(selectpval1fromsys.aux_stats$wherepname='IOSEEKTIM')+

2(selectvaluefromv$parameterwherename='db_block_size')/

3(selectpval1fromsys.aux_stats$wherepname='IOTFRSPEED')"sreadtim"

4fromdual;

sreadtim

----------

12

CPU COST = ceil(PLAN_TABLE.cpu_cost/cpuspeed/1000/sreadtim):

PgSQL

SQL> select ceil(CPU_COST/3364.86486/1000/12) from PLAN_TABLE where OPERATION='NESTED LOOPS';

CEIL(CPU_COST/3364.86486/1000/12)

---------------------------------

49538

那总的IO COST+总的CPU COST：

1

2

3

4

5

6

SQL>selectceil(CPU_COST/3364.86486/1000/12)fromPLAN_TABLEwhereOPERATION='NESTED LOOPS';

CEIL(CPU_COST/3364.86486/1000/12)

---------------------------------

49538

那总的IOCOST+总的CPUCOST：

PgSQL

SQL> select 5926815+49538 from dual;

5926815+49538

-------------

5976353

1

2

3

4

5

SQL>select5926815+49538fromdual;

5926815+49538

-------------

5976353

这里算出来的cost为5976353，和我们前面sql执行的时候，统计出来的cost 5976K很相近，嵌套循环的cost算法就是这么算出来的。

转载请注明: 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

最后编辑：2014-02-20作者：Jerry

一个积极向上的小青年，热衷于分享--Focus on DB,BI,ETL