Exadata上cell如何判断何时进行智能扫描？————2.在非Exadata环境的测试

创建测试表：

LUNAR@lunar>create table lunar as select * from dba_objects where rownum<=100;

Table created.

Elapsed: 00:00:00.35
LUNAR@lunar>select pid, spid from v$process p, v$session s where p.addr = s.paddr and s.sid=(select sid from v$mystat where rownum=1);

       PID SPID
---------- ------------------------------------------------
        24 12868

Elapsed: 00:00:00.05
LUNAR@lunar>

使用gdb跟踪：
———— 回到 session 2 —————-
(gdb) c
Continuing.
继续跟踪

———— 回到 session 1 —————-
执行： LUNAR@lunar>select count(1) from lunar;
…….

———— 回到 session 2 继续跟踪 —————-

(gdb) c
Continuing.

Breakpoint 6, 0x00000000021c9cd6 in kcfis_tablespace_is_on_sage ()
(gdb) c
Continuing.

Breakpoint 6, 0x00000000021c9cd6 in kcfis_tablespace_is_on_sage ()
(gdb) 

———— 回到 session 1 —————-
回到session 1，此时已经返回了结果：
LUNAR@lunar>select count(1) from lunar;

COUNT(1)
———-
100

Elapsed: 00:00:03.91
LUNAR@lunar>
[/shell]

我们看到，在非Exadata环境上，只执行了2次kcfis_tablespace_is_on_sage的判断，猜测该调用主要是针对执行计划中的两个语句涉及到的相关兑现搞定判断，判断他们是否在SAGE上，SAGE（Storage Appliance for Grid Environments）是Exadata最早的雏形，也是Oracle内部开发时该项目的名称。
在非Exadata上发现表空间不在SAGE上，因此执行了普通的块扫描而非Smart Scan。
.
对该语句执行event 10046 跟踪，我们看到，该语句是执行了“TABLE ACCESS FULL LUNAR”的全表扫描：

=====================
PARSING IN CURSOR #140534602396616 len=26 dep=0 uid=37 oct=3 lid=37 tim=1393962423520613 hv=3764746106 ad='7e125b28' sqlid='dprpmj3h6axvu'
select count(1) from lunar
END OF STMT
PARSE #140534602396616:c=7998,e=76754,p=0,cr=5,cu=0,mis=1,r=0,dep=0,og=1,plh=291688323,tim=1393962423520609
EXEC #140534602396616:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=291688323,tim=1393962423520740
WAIT #140534602396616: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=282 tim=1393962423520811
FETCH #140534602396616:c=1000,e=145,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=291688323,tim=1393962423520990
STAT #140534602396616 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=4 pr=0 pw=0 time=146 us)'
STAT #140534602396616 id=2 cnt=100 pid=1 pos=1 obj=18207 op='TABLE ACCESS FULL LUNAR (cr=4 pr=0 pw=0 time=335 us cost=3 size=0 card=100)'
WAIT #140534602396616: nam='SQL*Net message from client' ela= 216 driver id=1650815232 #bytes=1 p3=0 obj#=282 tim=1393962423521304
FETCH #140534602396616:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=291688323,tim=1393962423521371
WAIT #140534602396616: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=282 tim=1393962423521406

*** 2014-03-04 11:47:10.737
WAIT #140534602396616: nam='SQL*Net message from client' ela= 7215730 driver id=1650815232 #bytes=1 p3=0 obj#=282 tim=1393962430737152
CLOSE #140534602396616:c=0,e=80,dep=0,type=0,tim=1393962430737559
WAIT #0: nam='SQL*Net message to client' ela= 16 driver id=1650815232 #bytes=1 p3=0 obj#=282 tim=1393962430737806
WAIT #0: nam='SQL*Net message from client' ela= 20602 driver id=1650815232 #bytes=1 p3=0 obj#=282 tim=1393962430758517
WAIT #0: nam='SQL*Net message to client' ela= 18 driver id=1650815232 #bytes=1 p3=0 obj#=282 tim=1393962430759305
WAIT #0: nam='SQL*Net message from client' ela= 8527 driver id=1650815232 #bytes=1 p3=0 obj#=282 tim=1393962430767944
WAIT #0: nam='SQL*Net message to client' ela= 13 driver id=1650815232 #bytes=1 p3=0 obj#=282 tim=1393962430768344

格式化后的结果：

********************************************************************************

SQL ID: dprpmj3h6axvu Plan Hash: 291688323

select count(1) 
from
 lunar


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.07          0          5          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.07          0          9          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 37  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=4 pr=0 pw=0 time=146 us)
       100        100        100   TABLE ACCESS FULL LUNAR (cr=4 pr=0 pw=0 time=335 us cost=3 size=0 card=100)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message from client                     3      228.94        236.16
  SQL*Net message to client                       2        0.00          0.00



********************************************************************************

结论：
根据kcfis_tablespace_is_on_sage判断数据是否在cell上