[20150508]列顺序问题.txt
--链接:
https://viveklsharma.wordpress.com/2015/04/30/cpu-cycles-for-column-skipping/
--测试列顺序对CPU cost的影响:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table T1 as
select level ID, mod(level,2) N1, mod(level,10) N2, mod(level,100) N3, mod(level,1000) N4,
mod(level,1000) N5, mod(level,10000) N6, mod(level,5) N7, mod(level,50) N8, mod(level,500) N9,
case when mod(level,10000)=0 then 'AIOUG' else dbms_random.string('A',10) end V6,
mod(level,5000) N10, mod(level,50000) N11
from dual connect by level
create table T2 as
select level ID, mod(level,2) N1, mod(level,100) N3, mod(level,1000) N4, mod(level,1000) N5,
mod(level,10000) N6, mod(level,5) N7, mod(level,50) N8, mod(level,500) N9,
case when mod(level,10000)=0 then 'AIOUG' else dbms_random.string('A',10) end V6,
mod(level,5000) N10, mod(level,50000) N11,
mod(level,10) N2
from dual connect by level
exec dbms_stats.gather_table_stats(user,'T1');
exec dbms_stats.gather_table_stats(user,'T2');
--注意N2定义的顺序不一样.
SCOTT@test> select owner, num_rows, blocks, last_analyzed from dba_tables where table_name in ('T1','T2') and owner=user;
OWNER NUM_ROWS BLOCKS LAST_ANALYZED
------ ---------- ---------- -------------------
SCOTT 100000 846 2015-05-08 09:36:05
SCOTT 100000 846 2015-05-08 09:36:02
$ cat x1.sql
set termout off
select id,&2 from &1;
set termout on
--避免不必要的输出.
SCOTT@test> alter session set statistics_level=all;
Session altered.
SCOTT@test> @x1 t1 n1
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3svh6k0ryh0uv, child number 0
-------------------------------------
select id,n1 from t1
Plan hash value: 3617692013
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 149 (100)| | 100K|00:00:00.11 | 1319 |
| 1 | TABLE ACCESS FULL| T1 | 1 | 100K| 781K| 149 (1)| 00:00:01 | 100K|00:00:00.11 | 1319 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
SCOTT@test> @x1 t2 n1
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6yd1cud8ck97y, child number 0
-------------------------------------
select id,n1 from t2
Plan hash value: 1513984157
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 149 (100)| | 100K|00:00:00.11 | 1319 |
| 1 | TABLE ACCESS FULL| T2 | 1 | 100K| 781K| 149 (1)| 00:00:01 | 100K|00:00:00.11 | 1319 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T2@SEL$1
--可以发现两种CPU cost一样.buffers也一样.
--查询N2字段看看:
SCOTT@test> @x1 t1 n2
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4ssczwvuk346y, child number 0
-------------------------------------
select id,n2 from t1
Plan hash value: 3617692013
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 149 (100)| | 100K|00:00:00.11 | 1319 |
| 1 | TABLE ACCESS FULL| T1 | 1 | 100K| 781K| 149 (1)| 00:00:01 | 100K|00:00:00.11 | 1319 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8aau83a5ct1u9, child number 0
-------------------------------------
select id,n2 from t2
Plan hash value: 1513984157
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 150 (100)| | 100K|00:00:00.11 | 1319 |
| 1 | TABLE ACCESS FULL| T2 | 1 | 100K| 781K| 150 (2)| 00:00:01 | 100K|00:00:00.11 | 1319 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T2@SEL$1
--可以发现两种CPU cost查询T2时稍微多1个.buffers也一样.说明列的顺序对cpu cost存在一定的影响.一般设置是把经常查询列放前面.
--补充链接里面是如何计算的.
SCOTT@test> explain plan for select id,n1 from t1;
Explained.
SCOTT@test> column options format a30
SCOTT@test> select OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table;
OPERATION OPTIONS COST CPU_COST IO_COST TIME
----------------- -------- ----- ---------- ---------- ----------
SELECT STATEMENT 149 23024738 148 1
TABLE ACCESS FULL 149 23024738 148 1
--退出,我的plan_table 是临时表.
SCOTT@test> explain plan for select id,n2 from t2;
Explained.
SCOTT@test> select OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table;
OPERATION OPTIONS COST CPU_COST IO_COST TIME
----------------- -------- ---- ---------- ---------- ----------
SELECT STATEMENT 150 45024738 148 1
TABLE ACCESS FULL 150 45024738 148 1
The CPU_Cost for a Full Table Scan of T1 is 23024738. I assume, the other numbers are Oracle calculations and are the
defaults. 7121.44 Cpu Cycles per block multiplied by number of blocks. 150 CPU Cycles for each Row and 20 CPU Cycles for
Column Skip. So, 20*100000*(2-1) is for 20 CPU Cycles multiplied by Highest ID – Lowest ID (2-1) of the columns
referred in the query. Since the two tables are same in terms of number of rows and blocks, the calculation for a FTS
should remain same. Lets see..
--这些来自哪里?
SCOTT@test> column pname format a20
SCOTT@test> Select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ -------------------- ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 03-02-2015 11:52
SYSSTATS_INFO DSTOP 03-02-2015 11:52
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 1517
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
SCOTT@test> select round(7121.44*&blocks+(150*100000)+(20*100000*(2-1))) from dual;
Enter value for blocks: 846
ROUND(7121.44*846+(150*100000)+(20*100000*(2-1)))
-------------------------------------------------
23024738
SCOTT@test> select round(7121.44*&blocks+(150*100000)+(20*100000*(13-1))) from dual;
Enter value for blocks: 846
ROUND(7121.44*846+(150*100000)+(20*100000*(13-1)))
--------------------------------------------------
45024738
--居然都对上了,这些信息.如果查询这样按照作者的介绍CPU cost会更多.
SCOTT@test> explain plan for select id, n1 from t2 where n2=:b1;
Explained.
SCOTT@test> column options format a30
SCOTT@test> select OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table;
OPERATION OPTIONS COST CPU_COST IO_COST TIME
----------------- -------- ---- ---------- ---------- ----------
SELECT STATEMENT 151 60024738 148 1
TABLE ACCESS FULL 151 60024738 148 1
--要在多记一次150 CPU Cycles for each Row.不理解???明白好像在where条件还有计算一次.
SCOTT@test> select 23024738+150*100000+(20*100000*(13-2)) from dual;
23024738+150*100000+(20*100000*(13-2))
--------------------------------------
60024738
--对照下面的执行就很清楚了:
SCOTT@test> explain plan for select id, n1 ,n2 from t2 ;
Explained.
SCOTT@test> column options format a30
SCOTT@test> select OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table;
OPERATION OPTIONS COST CPU_COST IO_COST TIME
----------------- -------- ---- ---------- ---------- ----------
SELECT STATEMENT 150 45024738 148 1
TABLE ACCESS FULL 150 45024738 148 1