全表扫描成本算法初探

学习成本算法并记录如下

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

SQL> 
SQL> set serveroutput on
SQL> DECLARE
  2    v_ioseektim                   sys.aux_stats$.pval1%TYPE;
  3    v_iotfrspeed                  sys.aux_stats$.pval1%TYPE;
  4    v_cpuspeed                    sys.aux_stats$.pval1%TYPE;
  5    db_file_multiblock_read_count v$parameter.value%TYPE;
  6    v_db_block_size               v$parameter.value%TYPE;
  7    v_cpucycles                   plan_table.cpu_cost%TYPE;
  8    v_blocks                      user_tables.blocks%TYPE;
  9    v_sreadtim                    NUMBER(18, 4);
 10    v_mreadtim                    NUMBER(18, 4);
 11    v_cost                        PLS_INTEGER;
 12    v_table_name                  user_tables.table_name%TYPE := upper('test');
 13  BEGIN
 14    SELECT MAX(pval1) INTO v_ioseektim  FROM sys.aux_stats$ WHERE pname = 'IOSEEKTIM';
 15    SELECT MAX(pval1) INTO v_iotfrspeed  FROM sys.aux_stats$ WHERE pname = 'IOTFRSPEED';
 16    SELECT MAX(pval1) INTO v_cpuspeed  FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_MAIN' AND pname = 'CPUSPEEDNW';
 17    SELECT MAX(VALUE) INTO db_file_multiblock_read_count  FROM v$parameter WHERE NAME = 'db_file_multiblock_read_count';
 18    SELECT MAX(VALUE) INTO v_db_block_size  FROM v$parameter WHERE NAME = 'db_block_size';
 19    SELECT blocks INTO v_blocks  FROM user_tables WHERE table_name = v_table_name;
 20    SELECT MAX(cpu_cost) INTO v_cpucycles FROM plan_table;
 21  
 22    v_mreadtim := v_ioseektim + db_file_multiblock_read_count * v_db_block_size / v_iotfrspeed;
 23    v_sreadtim := v_ioseektim + v_db_block_size / v_iotfrspeed;
 24    v_cost := ceil((v_blocks / db_file_multiblock_read_count) * v_mreadtim / v_sreadtim + v_cpucycles / v_cpuspeed / v_sreadtim / 1000);
 25  
 26    dbms_output.put_line('ioseektim==>' || v_ioseektim);
 27    dbms_output.put_line('iotfrspeed==>' || v_iotfrspeed);
 28    dbms_output.put_line('db_file_multiblock_read_count==>' || db_file_multiblock_read_count);
 29    dbms_output.put_line('db_block_size==>' || v_db_block_size);
 30    dbms_output.put_line('cpucycles==>' || v_cpucycles);
 31    dbms_output.put_line('cpuspeed==>' || v_cpuspeed);
 32    dbms_output.put_line('mreadtim【ioseektim + db_file_multiblock_read_count * db_block_size / iotfrspeed】==>' || v_mreadtim);
 33    dbms_output.put_line('sreadtim【ioseektim + db_block_size / iotfrspeed】==>' || v_sreadtim);
 34    dbms_output.put_line('cost【ceil((blocks / db_file_multiblock_read_count) * mreadtim / sreadtim + cpucycles / cpuspeed / sreadtim / 1000)】==>' || v_cost);
 35  END;
 36  /
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
PL/SQL procedure successfully completed