sqlplus ljbljb
drop table t purge;
create table t x int ;
--将共享池清空
alter system flush shared_pool;
脚本2-29 单车到飞船试验前的准备工作
有一个开发人员写了类似如下的一个简单存储过程，实现了将1到10万的值插入t表的需求，具体如下：
create or replace procedure proc1
as
begin
for i in 1 .. 100000
loop
execute
immediate
''insert into t values
''||i||'''';
commit;
end loop;
end;
SQL connect ljbljb
已连接。
SQL drop table t purge;
表已删除。
SQL create table t x int ;
表已创建。
SQL alter system flush shared_pool;
系统已更改。
SQL set timing on
SQL exec proc1 ;
PLSQL 过程已成功完成。
已用时间: 00: 00: 42.87
SQL select count* from t;
COUNT*
------------------------
100000
脚本2-31 首次试验42秒完成，仅是单车速度
该语句用42秒时间完成10万条记录的插入，大家觉得速度快吗？”
“1秒钟插入2千多条记录，速度好快啊！”小莲在数学上的反应还是很敏捷。
“哦，你希望还能更快一点吗？”梁老师问。
“哦，当然希望了，肯定是越快越好了。”小莲回答得不假思索。
“其实这个简单的过程如果想更快，靠的就是对体系结构的理解，下面大家跟我查询一下该过程执行中，数据库共享池中的相关情况。
共享池中缓存下来的SQL语句以及HASH出来的唯一值，都可以在v$sql中对应的SQL_TEXT
和SQL_ID字段中查询到，而解析的次数和执行的次数分别可以从PARSE_CALL和EXECUTIONS字段中获取。
由于这个过程PROC1执行的是insert into t
的系列插入，于是我们执行如下语句来查询PROC1在数据库共享池中执行的情况，具体如下：
select t.sql_text, t.sql_id,t.PARSE_CALLS, t.EXECUTIONS
from v$sql t
where sql_text like ''%insert into t values%'';
脚本2-32 原来是因为未用绑定变量
为了让SQL语句及展现结果看起来更美观，我将上述SQL在PLSQL
DEVELOPER工具中查询（这个工具有着很友好的界面，尤其适合数据库开发人员使用，因此除了介绍先前的sqlplus
工具外，这里也顺道提一下这个PLSQL
DEVELOPER工具），发现共享池中有大量的类似SQL语句，而SQL_ID各自不同，每个语句都只是解析1次，执行1次，解析了10万次了，怪不得速度如此之慢，如图2-26所示。
图2-26 绑定变量
2.3.2.2 绑定变量，摩托速度
此时我们这么想，要是insert into t values 99898、insert into t values
99762 等这10万条语句如果都能合并成一种写法，比如用变量代替具体值，成为insert into t values :X
，那岂不是这10万条语句可以被HASH成一个SQL_ID值，不就可以做到解析1次，执行10万次了？这就大大减少了解析时间。
这就是数据库的一个典型优化，绑定变量优化！
接下来我们将proc1改进为proc2，具体写法如下：
create or replace procedure proc2
as
begin
for i in 1 .. 100000
loop
execute
immediate
''insert into t values :x ''
using i;
commit;
end loop;
end;
SQL drop table t purge;
表已删除。
SQL create table t x int ;
表已创建。
SQL alter system flush shared_pool;
系统已更改。
SQL set timing on
已用时间: 00: 00: 00.00
SQL exec proc2;
PLSQL 过程已成功完成。
已用时间: 00: 00: 08.41
SQL select count* from t;
COUNT*
-----------------
100000
脚本2-34 第2次改进后8秒完成，单车变摩托
这次我们惊奇地发现，速度从原来的42.87秒缩减为8.41秒，大幅度提升了，每秒插入记录数达到1万多条，大大超过之前的每秒插入2千多条的速度，为什么会这么神奇呢？”梁老师停下来问大家。
“因为语句被绑定变量了，解析次数变少了！”梁老师的话同学们记得很牢。
“很好，那我们一起看看吧，看看有啥变化（如图2-27所示）：
图2-27 解析与执行次数
虽然插入的语句值各不相同，但是都被绑定为:x，所以被HASH成唯一一个HASH值，名称为dxz576128adaw，很明显可以看出解析1次，执行10万次，这就是速度大幅度提升的原因了。
这下大家对这个速度满意了吧。”
小莲这下才发现，原来简单的体系结构后面还真有不少玄机啊，也不只是上堂课梁老师说的加大减少共享池这么简单，小莲觉得今天的课太值了。
2.3.2.3 静态改写，汽车速度
“大家还想不想再快一点？”梁老师这次开口吓了大家一跳。
“梁老师您太贪心了吧！”晶晶打趣地说道，看来课堂上同学们还真是被梁老师带动得无拘无束了，什么话都敢说啊。
“其实真的是还能再快的，你们看看这两个过程，是否觉得哪里写得有点别扭？”梁老师提醒大家认真看proc1和proc2。
“梁老师，这个execute immediate和双引号是啥意思啊，为什么不直接写成insert into t values
i啊？”刚才开玩笑的曾祥也提问了。
“不错！终于看出来了，execute
immediate是一种动态SQL的写法，常用于表名字段名是变量、入参的情况，由于表名都不知道，所以当然不能直接写SQL语句了，所以要靠动态SQL语句根据传入的表名参数，来拼成一条SQL语句，由execute
immediate调用执行。但是这里显然不需要多此一举，因为insert into t values
i完全可以满足需求，表名就是t啊。
我们来改写成proc3，如下：
create or replace procedure proc3
as
begin
for i in 1 .. 100000
loop
insert into t values i;
commit;
end loop;
end;
SQL drop table t purge;
表已删除。
SQL create table t x int ;
表已创建。
SQL alter system flush shared_pool;
系统已更改。
SQL set timing on
SQL exec proc3;
PLSQL 过程已成功完成。
已用时间: 00: 00: 06.25
SQL select count* from t;
COUNT*
------------------
100000
脚本2-36 第3次改进后6秒完成，摩托变汽车
大家看看，现在是什么情况？” 梁老师笑着说。
“哇，又快了！”晶晶惊叫起来，引来了一片笑声。
“为什么会快了，我们分析分析，这个语句肯定有用到绑定变量，一般来说，静态SQL会自动使用绑定变量，我们来查看查看（如图2-28所示）。
SQL drop table t purge;
表已删除。
SQL create table t x int ;
表已创建。
SQL alter system flush shared_pool;
系统已更改。
SQL set timing on
SQL insert into t select rownum from dual connect by
level=100000;
已创建100000行。
已用时间: 00: 00: 00.25
SQL commit;
提交完成。
已用时间: 00: 00: 00.00
SQL select count* from t;
COUNT*
-----------------
100000
脚本2-39 第5次用集合写法后0.22秒完成，动车变飞机
大家认真看看完成的时间吧。”梁老师得意地回过头望着大家。
“0.25秒！”这次不再是晶晶一人的声音了，是全部同学一起喊起来了。
“每秒插入40万条，太快了吧！”小莲再次喊出声来。
“为什么会快这么多呢？其实是因为原先的过程变成了SQL，一条一条插入的语句变成了一个集合的概念，变成了一整批地写进DATA
BUFFER区里，好比你要运砖头到目的地，一种是一块砖头拿到目的地，再返回拿第二块，直到拿完全部。而另一种是全部放在板车一起推至目的地，只是这里的目的地是DATA
BUFFER区而已。
听明白了吗？”
“听明白了！”大家都很激动，梁老师真像一个魔术师。
“听明白就好，现在你们该好好想想请老师晚上去哪里吃饭了。”
同学们都乐了，大家心里都知道梁老师是在开玩笑。
2.3.2.6 直接路径，火箭速度
“没声音啊，看来大家怕花钱啊，要不老师再给同学们一个机会，如果我还能让这个插入语句更快，大家就请客，否则老师请客，如何？”
梁老师有完没完啊，都每秒钟40万条的速度了，还想快，疯了吧。同学们议论纷纷。
“那我开始了，因为前面完成时间都已经到零点几秒了，太小会有误差，所以我准备把数据量放大100倍，10万条改为插入1000万条，前面飞机速度的语句变成如下：
SQL connect ljbljb
已连接。
SQL drop table t purge;
表已删除。
SQL create table t x int ;
表已创建。
SQL alter system flush shared_pool;
系统已更改。
SQL set timing on
SQL insert into t select rownum from dual connect by
level=10000000;
已创建10000000行。
已用时间: 00: 00: 23.22
SQL commit;
脚本2-40 试验准备，将集合写法的试验数据量放大100倍
发现插入1000万条记录完成的时间是23秒多，大致为每秒钟43万条记录，和插入10万条记录时的速度每秒40万条大体接近，下面我们改用create
table 的直接路径方式来新建t表，看看这样的方法速度能否有提升。
SQL drop table t purge;
表已删除。
已用时间: 00: 00: 11.07
SQL alter system flush shared_pool;
系统已更改。
已用时间: 00: 00: 00.02
SQL set timing on
SQL create table t as select rownum x from dual connect by
level=10000000;
表已创建。
已用时间: 00: 00: 10.14
脚本2-41 第6次改进，直接路径让飞机变火箭
测试结果是，速度又有了2倍多的提升，只需要10秒即可完成，等同于插入速度为每秒钟100万条，要不是亲眼所见，还真不敢相信吧。
同学们知道这是为什么吗？真正的原因在于，insert into t select ……的方式是将数据先写到DATA
BUFFER中，然后再刷到磁盘中。而create table t
的方式却是跳过了数据缓存区，直接写进磁盘中，这种方式又称之为直接路径读写方式，因为原本是数据先到内存，再到磁盘，更改为直接到磁盘，少了一个步骤，因而速度提升了许多。
直接路径读写方式的缺点在于由于数据不经过数据缓存区，所以在数据缓存区中一定读不到这些数据，因此一定会有物理读。但是在很多时候，尤其是海量数据需要迁移插入时，快速插入才是真正的第一目的，该表一般记录巨大，DATA
BUFFER甚至还装不下其十分之一、百分之一，这些共享的数据意义也不大，这时，我们一般会选择直接路径读写的方式来完成海量数据的插入。
同学们，听明白了没，现在大家甘心请客了吧？”梁老师时刻不忘调侃。
台下都听呆住了，梁老师这堂课真是神了，大家半天没回过神来。
2.3.2.7 并行设置，飞船速度
“大家这次请客是请定了，具体时间老师通知哦。”
台下哈哈大笑，大家还是回过神来了，今天这堂课给梁老师弄得一愣一愣的。
“同学们，插入语句还能再快吗？”
这下台下同学们都不敢应答了，梁老师没完没了，却每次都能不断加速，而且用到的知识又和体系知识息息相关，让自己一听就明白了。可以看出梁老师这个经典案例的例子是精心构造的，真可以说是经典中的经典。
“看来是被我给忽悠住了，大家都不敢应了。”梁老师笑着说道，“其实遇到性能好的机器，还是可以大幅度提升性能的，大家看如下语句，我设置日志关闭nologging，并且设置parallel
16 表示用到机器的16个CPU，结果在笔记本环境收效不是很明显，因为我的环境是单核的机器。
后来我把如下SQL运行在强劲的服务器上，有16个CPU，下面的语句仅仅在4秒不到的时间内就完成了，速度相对于前面的火箭速度而言，快多了，几乎是每秒钟300万条的插入速度，具体如下：
drop table t purge;
alter system flush shared_pool;
set timing on
create table t nologging parallel 64
as select rownum x from dual connect by level=10000000;
脚本2-42 第7次改进，并行原理让火箭变飞船
不过并行最大的特点就是占用了大多数CPU的资源，如果是一个并发环境，很多应用在跑，因为这个影响了别的应用，导致别的应用资源不足，将引发很多严重问题，所以需要三思而后行，了解清楚该机器是否允许你这样占用全部的资源。
好了，今天我以一条最简单的插入顺序数字进某表的SQL语句为例，方法从存储过程实现到仅用单条SQL完成；速度从原先的每秒2千多条提升到每秒300万条，这中间都做了哪些改进呢，是否都和体系结构有关呢？
希望大家好好复习这一节的经典案例，回忆一下速度的6次飞跃之旅，好好感受一下灵活应用知识的无限魅力。”
購物車/收銀台(0) | 在線留言板
| 付款方式
| 運費計算
| 聯絡我們
| 幫助中心 |
加入書簽
HOME
新書上架
