oracle通过执行计划cost,执行计划Cost算法详解

——教主的实验内容，放到这里镇宅！

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott@192.168.1.118:1521/orcl

SQL> drop table test;

Table dropped

SQL> create table test as select * from dba_objects where 1=0;

Table created

SQL> alter table test pctfree 99 pctused 1;

Table altered

SQL> insert into test select * from dba_objects where rownum <=1;

1 row inserted

SQL> alter table test minimize records_per_block;

Table altered

SQL> insert into test select * from dba_objects where rownum <1000;

999 rows inserted

SQL> exec dbms_stats.gather_table_stats(user,'test');

PL/SQL procedure successfully completed

SQL> alter session set db_file_multiblock_read_count=16;

Session altered

SQL> alter session set "_table_scan_cost_plus_one"=false;

Session altered

SQL> explain plan for select count(*) from test;

Explained

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1950795681

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |   220   (1)| 00:00:03 |

|   1 |  SORT AGGREGATE    |      |     1 |            |          |

|   2 |   TABLE ACCESS FULL| TEST |  1000 |   220   (1)| 00:00:03 |

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

9 rows selected

set serveroutput on

DECLARE

v_ioseektim                   sys.aux_stats$.pval1%TYPE;

v_iotfrspeed                  sys.aux_stats$.pval1%TYPE;

v_cpuspeed                    sys.aux_stats$.pval1%TYPE;

db_file_multiblock_read_count v$parameter.value%TYPE;

v_db_block_size               v$parameter.value%TYPE;

v_cpucycles                   plan_table.cpu_cost%TYPE;

v_blocks                      user_tables.blocks%TYPE;

v_sreadtim                    NUMBER(18, 4);

v_mreadtim                    NUMBER(18, 4);

v_cost                        PLS_INTEGER;

v_table_name                  user_tables.table_name%TYPE := upper('test');

BEGIN

SELECT MAX(pval1) INTO v_ioseektim  FROM sys.aux_stats$ WHERE pname = 'IOSEEKTIM';

SELECT MAX(pval1) INTO v_iotfrspeed  FROM sys.aux_stats$ WHERE pname = 'IOTFRSPEED';

SELECT MAX(pval1) INTO v_cpuspeed  FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_MAIN' AND pname = 'CPUSPEEDNW';

SELECT MAX(VALUE) INTO db_file_multiblock_read_count  FROM v$parameter WHERE NAME = 'db_file_multiblock_read_count';

SELECT MAX(VALUE) INTO v_db_block_size  FROM v$parameter WHERE NAME = 'db_block_size';

SELECT blocks INTO v_blocks  FROM user_tables WHERE table_name = v_table_name;

SELECT MAX(cpu_cost) INTO v_cpucycles FROM plan_table;

v_mreadtim := v_ioseektim + db_file_multiblock_read_count * v_db_block_size / v_iotfrspeed;

v_sreadtim := v_ioseektim + v_db_block_size / v_iotfrspeed;

v_cost := ceil((v_blocks / db_file_multiblock_read_count) * v_mreadtim / v_sreadtim + v_cpucycles / v_cpuspeed / v_sreadtim / 1000);

dbms_output.put_line('ioseektim==>' || v_ioseektim);

dbms_output.put_line('iotfrspeed==>' || v_iotfrspeed);

dbms_output.put_line('db_file_multiblock_read_count==>' || db_file_multiblock_read_count);

dbms_output.put_line('db_block_size==>' || v_db_block_size);

dbms_output.put_line('cpucycles==>' || v_cpucycles);

dbms_output.put_line('cpuspeed==>' || v_cpuspeed);

dbms_output.put_line('mreadtim [ioseektim + db_file_multiblock_read_count * db_block_size / iotfrspeed] ==>' || v_mreadtim);

dbms_output.put_line('sreadtim [ioseektim + db_block_size / iotfrspeed]==>' || v_sreadtim);

dbms_output.put_line('cost[ceil((blocks / db_file_multiblock_read_count) * mreadtim / sreadtim + cpucycles / cpuspeed / sreadtim / 1000)]==>' || v_cost);

END;

/

ioseektim==>10

iotfrspeed==>4096

db_file_multiblock_read_count==>16

db_block_size==>8192

cpucycles==>7271440

cpuspeed==>674.703

mreadtim【ioseektim + db_file_multiblock_read_count * db_block_size / iotfrspeed】==>42

sreadtim【ioseektim + db_block_size / iotfrspeed】==>12

cost【ceil((blocks / db_file_multiblock_read_count) * mreadtim / sreadtim + cpucycles / cpuspeed / sreadtim / 1000)】==>220

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30268819/viewspace-1680698/，如需转载，请注明出处，否则将追究法律责任。