CBO Full Table Scan, cbofulltablescan

CBO Full Table Scan, cbofulltablescan

Reprinted please indicate the source: http://blog.csdn.net/guoyjoe/article/details/44261859

**************************************** *******
I. Full Table Scan-FTS algorithm of CBO
**************************************** *******

1. Create a table
SQL> create table gyj_t1 (id int, name varchar2 (20 ));

Table created.

2. Insert 0.1 million rows of data
SQL> begin
2 for I in 1 .. 100000 loop
3 insert into gyj_t1 values (I, lpad ('gyj', '10', 'J') | I );
4 commit;
5 end loop;
6 end;
7/

PL/SQL procedure successfully completed.


3. Collect statistics
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS (
3 ownname => 'gyj ',
4 tabname => 'gyj _ t1 ',
5 estimate_percent = & gt; 100,
6 method_opt => 'for all columns size 1 ',
7 degree => DBMS_STATS.AUTO_DEGREE,
8 cascade => TRUE
9 );
10 END;
11/

PL/SQL procedure successfully completed.

/

4. Execute the SQL statement and generate the execution plan. You can see the cost of the full table scan as follows:
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 | K | 84 (2) | 00:00:02 |
---------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
373 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL * Net to client
523 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)

5. CBO formula for full table Scan
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) Multiple read Parameters
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 = 'ottfrspeed') "mreadtim"
7 from dual;

Mreadtim
----------
42

That is, 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 = 'ottfrspeed') "sreadtim"
4 from dual;

Sreadtim
----------
12
That is, 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


(10) CPUSPEEDNW
SQL> select pname, pval1 from sys. aux_stats $ where sname = 'sysstats _ main ';

PNAME PVAL1
----------------------------------------
CPUSPEEDNW 2894.14695
IOSEEKTIM 10
Iotfrspeed4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

(11) Implicit parameters:
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 FALSE
By one


(12) 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