[20150508]列顺序问题.txt

--链接:

--测试列顺序对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