计算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 '%&¶meter_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开头的部分才能找到，即使找到也不确定后面是不是还有（假定字典是无序状态的），因此还得往后找，知道正本字典翻完，才确定“哦，原来刚才找到的那个记录就是想要的结果了”。索引扫描的意思就是我们预先知道“做”这个字在拼音的Z区域，然后根据前面目录查看"zuo"这个拼音在那一页，然后直接翻到那一页就能找到我们要的结果了，这样就能大大减少查询的时间。
select 姓名 from 员工信息表 group by 姓名 having count(姓名) > 1
-------------------------
select * from 员工信息表
where 姓名 in (select 姓名 from 员工信息表 group by 姓名 having count(姓名) > 1)