CBO's full Table scan-fts algorithm

Reprint Please specify source: http://blog.csdn.net/guoyjoe/article/details/44261859

***********************************************
First, CBO's full Table scan-fts algorithm
***********************************************

1. Build a table
Sql> CREATE TABLE gyj_t1 (ID int,name varchar2 (20));

Table created.

2. Insert 100,000 rows of data
Sql> begin
2 for I in 1.. 100000 loop
3 INSERT into GYJ_T1 values (I,lpad (' Gyj ', ' Ten ', ' J ') | | i);
4 commit;
5 end Loop;
6 end;
7/

PL/SQL procedure successfully completed.


3. Collect statistical information
Sql> BEGIN
2 dbms_stats. Gather_table_stats (
3 Ownname = ' GYJ ',
4 tabname = ' Gyj_t1 ',
5 estimate_percent = 100,
6 method_opt = ' For all columns size 1 ',
7 degree = dbms_stats. Auto_degree,
8 Cascade=>true
9);
Ten END;
11/

PL/SQL procedure successfully completed.

/

4. Run SQL to generate the run schedule. You can see the cost of a full table scan such as the following
Sql> set Autot traceonly;
Sql> Select COUNT (*) from GYJ_T1;


Execution Plan
----------------------------------------------------------
Plan Hash value:2553183190

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU) | Time |
---------------------------------------------------------------------
| 0 |        SELECT STATEMENT |     |    1 | 84 (2) | 00:00:02 |
|  1 |        SORT AGGREGATE |     |            1 |          | |
|   2 | TABLE ACCESS full|   Gyj_t1 |    100k| 84 (2) | 00:00:02 |
---------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 Recursive calls
0 db Block gets
373 consistent gets
0 physical Reads
0 Redo Size
526 Bytes sent via sql*net to client
523 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)

5. Full table Scan CBO formula
FTS cost = I/o cost + CPU cost
I/o cost = 1 + ceil (#MRds * (Mreadtim/sreadtim))
#MRds = #Blks/MBRC
CPU cost = ROUND (#CPUCycles/cpuspeed/1000/sreadtim)

6. #MRds
#MRds = #Blks/MBRC
(1) #Blks
Sql> Select blocks from dba_tables where owner= ' GYJ ' and table_name= ' gyj_t1 ';

BLOCKS
----------
370

(2) Multi-block reading of the number of references
Sql> Show Parameter Db_file_multiblock_read_count

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_file_multiblock_read_count Integer 16


(3) #MRds
Sql> select 370/16 from dual;

370/16
----------
23.125

7, Mreadtim


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
----------
42

namely Mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed
=10+16*8192/4096=42

8, Sreadtim
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
namely Sreadtim=ioseektim+db_block_size/iotfrspeed
=10+8192/4096=12


(9) #CPUCycles

Explain plan for SELECT COUNT (*) from GYJ_T1;
Sql> select Cpu_cost from plan_table;
Cpu_cost
----------
17634933


(Ten) CPUSPEEDNW
Sql> Select PName, pval1 from sys.aux_stats$ where sname= ' sysstats_main ';

PNAME PVAL1
------------------------------ ----------
CPUSPEEDNW 2894.14695
Ioseektim 10
Iotfrspeed 4096
Sreadtim
Mreadtim
Cpuspeed
MBRC
Maxthr
Slavethr

(11) Implied number of references:
Sql> @?/rdbms/admin/show_para
Enter value for P:table_scan_cost
Old 12:and Upper (I.KSPPINM) like Upper ('%&p% ')
New 12:and Upper (I.KSPPINM) like Upper ('%table_scan_cost% ')

P_name p_description P_value ISDEFAULT ismodified Isadj
---------------------------------------- -------------------------------------------------- --------------------- --------- --------- ---------- -----
_table_scan_cost_plus_one Bump estimated full table scan and index FFS cost true True FALSE FALSE
by one


() IO Cost
I/o cost = 1 + ceil (#MRds * (Mreadtim/sreadtim))
/O cost = 1 + ceil (23.125 * ((10+16*8192/4096)/(10+8192/4096)))
Sql> Select 1 + ceil (23.125 * (10+16*8192/4096)/(10+8192/4096)) from dual;

1+ceil (23.125* (10+16*8192/4096)/(10+8192/4096))
-------------------------------------------------
82

(13) CPU Cost
CPU cost = ROUND (#CPUCycles/cpuspeed/1000/sreadtim)
ROUND (17634933/2894.14695/1000/(10+8192/4096)) =1

CBO's full Table scan-fts algorithm