計算cost--全表掃描，計算cost--表掃描

計算cost--全表掃描，計算cost--表掃描

下面教大家如何手工算出oracle執行計畫中的cost值。

成本的計算方式如下：
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     CPU刻度數  


sreadtim - single block read time    單塊讀耗時(單位milliseconds 毫秒，1000毫秒等於1秒
mreadtim - multi block read time     多塊讀耗時(單位milliseconds 毫秒，1000毫秒等於1秒)
cpuspeed - CPU cycles per second     CPU頻率(單位MHZ)   單位是秒


mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed 
sreadtim=ioseektim+db_block_size/iotfrspeed  

@指令碼將在後面給出

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
----------------------------------------------------------------------------------------------------------------------------------------------------------------
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    --一次多塊讀的時間
  mreadtim
----------
        42
1 row selected.
SQL> @getsreadtime   --一次單塊讀的時間
  sreadtim
----------
        12
1 row selected.
SQL> @getcputime   --消耗的cpu的時間
    cputim
----------
.928809822
1 row selected.
SQL> @getmreadnum             --scott.aaa全表掃描是多塊讀需要的次數
Enter value for owner: scott
Enter value for table_name: aaa
  MREADNUM
----------
    8.8125
1 row selected.
SQL> @gettablecost           --計算出成本
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                   --查隱含參數
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
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 |
-------------------------------------------------------------------

--以下是@指令碼

--@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
/

轉載請註明本文地址

資料庫中全表掃描與索引掃描的不同是？

全表掃描的意思就是要把表中所有資料過一遍才能顯示資料結果，索引掃描就是索引，只需要掃描一部分資料就可以得到結果，
打個比方吧，在新華字典中，如果沒有拼音或筆畫索引，當我們尋找“做”這個字就要從字典第一頁一次往後查，一直插到Z開頭的部分才能找到，即使找到也不確定後面是不是還有（假定字典是無序狀態的），因此還得往後找，知道正本字典翻完，才確定“哦，原來剛才找到的那個記錄就是想要的結果了”。索引掃描的意思就是我們預Crowdsourced Security Testing道“做”這個字在拼音的Z地區，然後根據前面目錄查看"zuo"這個拼音在那一頁，然後直接翻到那一頁就能找到我們要的結果了，這樣就能大大減少查詢的時間。
 
SQL語言 怎不全表掃描直接提取最新一次的記錄？

select 姓名 from 員工資訊表 group by 姓名 having count(姓名) > 1

-------------------------
select * from 員工資訊表
where 姓名 in (select 姓名 from 員工資訊表 group by 姓名 having count(姓名) > 1)