基于12c in-memory新特性的SQL优化比拼

在本次中#2014年Orcl-Con甲骨文控活动#引入了一个利用12c in-memory特性优化查询语句的workshop ，在不考虑索引等特性的前提下，仅仅使用12c IMCC特性，崔胄同学利用inmemory和并行特性将原本需要1分钟运行的SQL，优化到1.37秒，提升数十倍，成功赢得ipad!

该次SQL优化比拼的 原帖地址http://t.cn/RzURLTJ

OKAY 我们来优化一下, 既然索引，物化视图等传统技术无法使用，我们只能使用使用一些oracle的大数据处理技术来提高性能
首先创建表 scripts 可以查看 xxxxxxxx 
这里提一下, 在创建表的时候使用pctfree 0 来适当的降低了逻辑读。

创建完毕

COUNT(*)||'TIME_ROWS'
58432 time_rows
29402976 sales_rows
1776000 customers_rows
160 channles_rows

创建完后 跑了一下 

no tuning
172706 consistent gets
Elapsed: 00:00:22.11

oooooopss~ 22秒 看来需要优化
开始使用 in-memory 组件 来优化

SQL> select * from v$version;
BANNER 
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> show parameter inmemory

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 7
inmemory_query string ENABLE
inmemory_size big integer 16G
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE

如果内存有限 可以适当的只存放 需要的 列来降低使用memory

alter table SHOUG.times inmemory;
alter table SHOUG.sales inmemory;
alter table shoug.sales no inmemory(PROD_ID,PROMO_ID,QUANTITY_SOLD);
alter table shoug.customers inmemory;
alter table SHOUG.channels inmemory;

Statistics
41 recursive calls
17 db block gets
54 consistent gets
2 physical reads
1188 redo size
1584 bytes sent via SQLNet to client
562 bytes received via SQLNet from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
24 rows processed

Elapsed: 00:00:19.70

可以看到 物理读几乎已经很弱了, 但是速度还是不快 
优化CPU使用, 可以看到 inmemory 使用后 cpu 使用率达到了100% 但是, 可以看到等待全落在了 单颗 cpu上

所以根据数据量的大小, 来设置并行度
conn shoug/oracle
alter table shoug.sales parallel 8;
alter table shoug.times parallel 1;
alter table shoug.customers parallel 8;
alter table shoug.channel parallel 4;

select table_name,degree from user_tables;

set timing on
SELECT /* use inmemory / /+parallel (shoug.customers 8)*/ c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM SHOUG.sales s, SHOUG.times t, SHOUG.customers c
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND c.cust_state_province = 'FL'
AND t.calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12')
AND s.time_id IN
(SELECT time_id
FROM SHOUG.times
WHERE calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12'))
AND s.cust_id IN
(SELECT cust_id FROM SHOUG.customers WHERE cust_state_province = 'FL')
AND s.channel_id IN
(SELECT channel_id
FROM SHOUG.channels
WHERE channel_desc = 'Direct Sales')
GROUP BY c.cust_city, t.calendar_quarter_desc;

24 rows selected.

Elapsed: 00:00:01.37

Statistics
203 recursive calls
0 db block gets
254 consistent gets
0 physical reads
0 redo size
1574 bytes sent via SQLNet to client
562 bytes received via SQLNet from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)

[root@db ~]# top
top - 23:51:34 up 6 days, 18:18, 6 users, load average: 0.65, 0.17, 0.15
Tasks: 391 total, 3 running, 387 sleeping, 0 stopped, 1 zombie
Cpu0 : 23.3%us, 0.0%sy, 0.0%ni, 76.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 22.6%us, 0.3%sy, 0.0%ni, 77.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 23.7%us, 0.3%sy, 0.0%ni, 76.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 22.3%us, 0.0%sy, 0.0%ni, 77.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 54.8%us, 0.7%sy, 0.0%ni, 44.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 22.1%us, 0.0%sy, 0.0%ni, 77.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 24.3%us, 0.0%sy, 0.0%ni, 75.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 22.6%us, 0.3%sy, 0.0%ni, 77.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 32882416k total, 32061328k used, 821088k free, 13416k buffers
Swap: 8388600k total, 52k used, 8388548k free, 30221056k cached

可以看到cpu使用率达到了30% 以上, 并且, 已经没有内存排序

PS: 恭喜 oracle 在12.1.0.2 版本内 以inmemory 列存储的方式 推出了 vector计算方式, 打破了actian vector db 在大数据市场独领风骚的格局。