[20150508]列順序問題.txt

[20150508]列順序問題.txt

--連結:

<a href="https://viveklsharma.wordpress.com/2015/04/30/cpu-cycles-for-column-skipping/">https://viveklsharma.wordpress.com/2015/04/30/cpu-cycles-for-column-skipping/</a>

--測試列順序對CPU cost的影響:

SCOTT@test&gt; @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,

mod(level,5000) N10, mod(level,50000) N11,

mod(level,10) N2   

exec dbms_stats.gather_table_stats(user,'T1');

exec dbms_stats.gather_table_stats(user,'T2');

--注意N2定義的順序不一樣.

SCOTT@test&gt; 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,&amp;2 from &amp;1;

set termout on

--避免不必要的輸出.

SCOTT@test&gt; alter session set statistics_level=all;

Session altered.

SCOTT@test&gt; @x1 t1 n1

SCOTT@test&gt; @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&gt; @x1 t2 n1

SQL_ID  6yd1cud8ck97y, child number 0

select id,n1 from t2

Plan hash value: 1513984157

|   1 |  TABLE ACCESS FULL| T2   |      1 |    100K|   781K|   149   (1)| 00:00:01 |    100K|00:00:00.11 |    1319 |

   1 - SEL$1 / T2@SEL$1

--可以發現兩種CPU cost一樣.buffers也一樣.

--查詢N2字段看看:

SCOTT@test&gt; @x1 t1 n2

SQL_ID  4ssczwvuk346y, child number 0

select id,n2 from t1

SQL_ID  8aau83a5ct1u9, child number 0

select id,n2 from t2

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

--可以發現兩種CPU cost查詢T2時稍微多1個.buffers也一樣.說明列的順序對cpu cost存在一定的影響.一般設定是把經常查詢列放前面.

--補充連結裡面是如何計算的.

SCOTT@test&gt; explain plan for select id,n1 from t1;

Explained.

SCOTT@test&gt; column options format a30

SCOTT@test&gt; 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&gt; explain plan for select id,n2 from t2;

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&gt; column pname format a20

SCOTT@test&gt; 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&gt; select round(7121.44*&amp;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&gt; select round(7121.44*&amp;blocks+(150*100000)+(20*100000*(13-1))) from dual;

ROUND(7121.44*846+(150*100000)+(20*100000*(13-1)))

--------------------------------------------------

                                          45024738

--居然都對上了,這些資訊.如果查詢這樣按照作者的介紹CPU cost會更多.

SCOTT@test&gt; explain plan for select id, n1 from t2 where n2=:b1;

SELECT STATEMENT            151   60024738        148          1

TABLE ACCESS      FULL      151   60024738        148          1

--要在多記一次150 CPU Cycles for each Row.不了解???明白好像在where條件還有計算一次.

SCOTT@test&gt; select 23024738+150*100000+(20*100000*(13-2)) from dual;

23024738+150*100000+(20*100000*(13-2))

--------------------------------------

                              60024738

--對照下面的執行就很清楚了:

SCOTT@test&gt; explain plan for select id, n1 ,n2 from t2 ;