Computing cost -- full table scan, computing cost -- table Scan

Computing cost -- full table scan, computing cost -- table Scan

The following describes how to manually calculate the cost value in the oracle execution plan.

The cost is calculated as follows:
Cost = (
# SRds * sreadtim +
# MRds * mreadtim +
CPUCycles/cpuspeed
)/Sreadtime


# SRds-number of single block reads
# MRds-number of multi-block reads
# CPUCyles-number of CPU cycles number of CPU clock cycles


Sreadtim-single block read time (unit: milliseconds, 1000 milliseconds equals 1 second)
Mreadtim-multi block read time (unit: milliseconds, 1000 milliseconds equals 1 second)
Cpuspeed-CPU cycles per second CPU frequency (unit: MHZ) Unit: seconds


Mreadtim = ioseektim + db_file_multiblock_count * db_block_size/iotftspeed
Sreadtim = ioseektim + db_block_size/iotfrspeed

@ The script will be provided later

SQL> create table aaa as select * from dba_objects where rownum <= 10000;

SQL> conn scott/tiger
Connected.
SQL> alter system set db_file_multiblock_read_count = 16;
System altered.
SQL> explain plan for select count (*) from aaa;
Explained.

SQL> @ getplan
'General, outline, starts'
Enter value for plan type:
PLAN_TABLE_OUTPUT
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 977873394
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
-------------------------------------------------------------------
| 0 | select statement | 1 | 33 (0) | 00:00:01 |
| 1 | sort aggregate | 1 |
| 2 | table access full | AAA | 10000 | 33 (0) | 00:00:01 |
-------------------------------------------------------------------

SQL> @ getmreadtime -- the time of one multi-block read
Mreadtim
----------
42
1 row selected.
SQL> @ getsreadtime -- the time when a single block is read
Sreadtim
----------
12
1 row selected.
SQL> @ getcputime -- cpu consumed time
Cputim
----------
. 928809822
1 row selected.
SQL> @ getmreadnum -- scott. aaa full table scan is required for multiple reads
Enter value for owner: scott
Enter value for table_name: aaa
MREADNUM
----------
8.8125
1 row selected.
SQL> @ gettablecost -- calculate cost
Enter value for mreadtime: 42
Enter value for mreadnum: 8.8125
Enter value for cputime: 0.928809822
Enter value for sreadtime: 12

(42*8.8125 + 0.928809822)/12
--------------------------
30.9211508
1 row selected.
Conn/as sysdba
@ Getparam_imp -- check implicit Parameters
Enter value for parameter_name: _ table_scan_cost_plus_one
_ Table_scan_cost_plus_one TRUE

SQL> conn scott/tiger
Connected.
SQL> alter session set "_ table_scan_cost_plus_one" = false;
Session altered.
SQL> explain plan for select count (*) from aaa;
Explained.
SQL> @ getplan
'General, outline, starts'
Enter value for plan type:
PLAN_TABLE_OUTPUT
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 977873394


-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
-------------------------------------------------------------------
| 0 | select statement | 1 | 32 (0) | 00:00:01 |
| 1 | sort aggregate | 1 |
| 2 | table access full | AAA | 10000 | 32 (0) | 00:00:01 |
-------------------------------------------------------------------

-- The following is the @ script

-- @ Getmreadtime
Select (select pval1 from sys. aux_stats $ where pname = 'ioseektim') +
(Select value from v $ parameter where name = 'db _ file_multiblock_read_count ')*
(Select value from v $ parameter where name = 'db _ block_size ')/
(Select pval1 from sys. aux_stats $ where pname = 'iotfrspeed') "mreadtim"
From dual;

-- @ Getsreadtime
Select (select pval1 from sys. aux_stats $ where pname = 'ioseektim') +
(Select value from v $ parameter where name = 'db _ block_size ')/
(Select pval1 from sys. aux_stats $ where pname = 'iotfrspeed') "sreadtim"
From dual;

-- @ Getcputime
Select (select distinct cpu_cost from plan_table where cpu_cost is not null )/
(Select pval1 from sys. aux_stats $ where sname = 'sysstats _ main' and pname = 'cpuspeednw ')/
1000 "cputim"
From dual;

-- @ Getmreadnum
Select (select BLOCKS from dba_tables where owner = upper ('& owner') and table_name = upper (' & table_name '))/
(Select value from v $ parameter where name = 'db _ file_multiblock_read_count ') "mreadnum"
From dual;

@ Gettablecost
Select (& mreadtime * & mreadnum + & cputime)/& sreadtime from dual;

-- @ Getparam_imp
SELECT nam. ksppinm NAME, val. ksppstvl VALUE
FROM sys. x $ ksppi nam, sys. x $ ksppsv val
WHERE nam. indx = val. indx
AND nam. ksppinm LIKE '% & parameter_name %'
Order by 1;
 

-- @ Getplan
Set feedback off
Pro 'General, outline, starts'
Pro
Acc type prompt 'enter value for plan type: 'default' general'
Select * from table (dbms_xplan.display) where '& type' = 'General ';
Select * from table (dbms_xplan.display (null, null, 'advanced-projection ') where' & type '= 'outline ';
SELECT * from table (DBMS_XPLAN.DISPLAY_CURSOR (NULL, NULL, 'allstats LAST ') where' & type '= 'starts ';
Set feedback on
Undef type
/

Reprinted please specify the address of this Article

What is the difference between full table scan and index scan in the database?

A full table scan means that all the data in the table must be passed over to display the data results. An index scan is an index. You only need to scan a part of the data to obtain the results,
For example, in the Xinhua Dictionary, if there is no pinyin or stroke index, we need to query the word "done" from the first page of the dictionary, the part that is inserted at the beginning of Z can be found. Even if it is found, it is not sure whether it is still there (assuming that the dictionary is unordered), so you have to find it later and know that the original dictionary has been turned over, "Oh, the record we just found is the expected result ". Index scanning means that we know in advance that the word "do" is in the Z area of Pinyin, and then view the pinyin "zuo" on that page according to the preceding directory, then, you can find the desired result on that page, which greatly reduces the query time.

How can I extract the latest record from an incomplete table scan in SQL?

Select name from employee info table group by name having count (name)> 1

-------------------------
Select * from employee info table
Where name in (select name from employee info table group by name having count (name)> 1)