执行计划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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30268819/viewspace-1680698/