本篇博文将分为4个部分。如下:
一. SQL语句管理
二. 批量接口
二. 批量接口
三. 事务设计
四. 使用存储过程降低网络流量
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - -- - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - -
1 . SQL语句管理
1.1 优化解析
Oracle在共享池(share pool)中维护了一份SQL语句的缓存。如果在此共享池中发现匹配的SQL语句,就可以避免掉解析大部分的开销。在共享池中找到匹配的SQL所对应的解析被称为软解析(soft parse)。反之为硬解析(hard parse)。
硬解析不仅耗费CPU的时间,在有大量会话想要同时缓存SQL到share pool时还会造成争用。
a. 绑定变量与游标管理
SQL语句在客户端被称为游标(cursor)或者上下文域(Context area)。在低级别的编程语言中(如C++)Oracle的API徐涛显式的创建游标。在更高级的编程语言中(如java或者C#)。游标表现为一个更加抽象的SQL语句对象。如在java中创建、执行并销毁了一个关联到某游标的SQL statement对象。如下(代码片段):
Statement s = oracleConn.createStatement();
s.execute(" UPDATE staff SET staff_name = 'zyj
1' WHERE staffId = '
2013060012' ");
s.close();
上面的语句执行后,如果在执行下面的片段:
Statement s = oracleConn.createStatement();
s.execute(" UPDATE staff SET staff_name = 'zyj
2' WHERE staffId = '2013060012' ");
s.close();
虽然只是
staff_name 赋值变化了,但在share pool中缓存的是不同的SQL语句。所以应该使用绑定变量,
如下:
PreparedStatement stmt = oracleConn.
preparestatement(" UPDATE
staff
SET
staff_name
= ? WHERE staffId = ?
");
stmt.setstring(1
,"
zyj
1
"
);
stmt.setint(2
,
2013060012
);
stmt.execute();
这样无论如何设置
staff_name
与
staffId
在share pool 中都是同一条SQL,性能肯定会提升。
使用绑定变量不仅可以降低单个应用的执行时间,也可以降低share pool中SQL语句上的闩锁与互斥争用出现的概率。
另,如果已有的程序使用了SQL 硬解析,这可以通过设置Oraac了的CURSOR_SHARING 参数来指示Oracle透明的将字面量替换为绑定变量,设置方法为:ALTER SYSTEM SET cursor_sharing = FORCE . 还可以设置为其他值(EXACT、SIMILAR)设置后的效果如下:
UPDATE staff SET staff_name = 'zyj1' WHERE staffId = '2013060012' 替换为如下的SQL语句:
UPDATE staff SET staff_name = : "SYS_B_0" WHERE staffId = : "SYS_B_1"
1.2 避免执行不必要的SQL语句
一句经典的SQL优化原则: 优化得最好的SQL语句是从不被执行的SQL语句。
下面用一个例子来说明。
FOR r IN ( SELECT *
FROM emp
)
LOOP
SELECT depname -----> 对于每个emp,这个SELECT都会被执行一次。
INTO v_dep_name
FORM department
WHERE depId = r. depId;
IF v_dep_name = 'IT'
THEN
NULL;
END IF;
END LOOP;
可以通过如下优化:
SELECT depId
INTO v_dep_id
FROM department
WHERE depname = 'IT';
FOR r IN ( SELECT *
FROM emp )
LOOP
IF r.depId=v_dep_id ;
THEN
NULL;
END IF;
END LOOP;
1.3 Oracle 11g 基于客户端的结果缓存(待研究)
2. 批量接口
Oracle可以从DB中一次检索出一条记录,也可以一次检索出一批记录,批量检索可以减少发送到DB服务器的请求次数,也可以降低网络流量和逻辑IO的开销。
2.1 实现批量提取
我们使用BULK COLLECT INTO子句来将记录提取到一个PL/SQL集合中,例如:
DECLARE
TYPE cust_id_type IS TABLE OF zyj.customers.cust_id%TYPE INDEX BY BINARY_INTEGER ; ---声明集合变量
TYPE cust_last_name_type IS TABLE OF
zyj.customers.cust_last_name%TYPE INDEX BY BINARY_INTEGER;
---声明集合变量
cust_id_list cust_id_type;
cust_name_list cust_last_name_type;
BEGIN
SELECT cust_id , cust_last_name
BULK COLLECT INTO
cust_id_list , cust_name_list
FROM zyj.customers;
END;
在java中可以使用setFetchSize方法来设置批量的大小:
PreparedStatement stmt = oracleConn.
preparestatement(sql);
stmt.setFetchSize(20);
ResultSet r = stmt.executeQuery();
while(r.next()) {
//other code
}
——
使用批量提取在单词调用中从DB获取的记录,可以同时降低DB与网络的开销。对于批量查询来讲,批量提取大约可以带来一个数量级(10倍)的性能提升。
2.2 批量插入
这里使用FORALL 命令来通过一个操作插入一个集合的数据:
DECLARE
TYPE xtype IS TABLE OF mytable.x%TYPE INDEX BY BINARY_INTEGER;
---声明集合变量
TYPE ytype IS TABLE OF mytable.y%TYPE INDEX BY BINARY_INTEGER ;
---声明集合变量
xlist xtype;
ylist ytype;
BEGIN
FOR i IN 1 .. 100
LOOP
xlist (i) := i;
ylist(i) := 'This is number ' || i;
END LOOP;
FORALL i IN 1 .. xlist.COUNT --如果用FOR则循环体内的媒体SQL或者DML语句会发送给SQL引擎执行。FORALL会一次性发送执行。
INSERT INTO mytable
(x,y)
VALUES (xlist(i) , ylist(i));
END;
END;
在java中可以使用addBatch和executeBatch方法来通过一次操作插入多条记录。如下:
String sql = "INSERT INTO mytable (x,y) VALUES (:1 , :2)";
PreparedStatement insertStmt = oracleConn.
preparestatement(sql);
int batchSize = 10;
for (int i=1;i<1000;i++) {
insertStmt
.setInt(1,i);
insertStmt
.setString(2,"This is number "+i);
insertStmt
.addBatch();
if (i % batchSize == 0) {
insertStmt
.executeBatch();
}
}
insertStmt
.executeBatch();
3 事务设计
3.1. 选择锁的策略:
a. 两个会话同时尝试修改同一条记录的概率如何? 如果可能性接近于0.选择乐观锁策略可能会更合适。
b. 在交互应用中,让用户重试事务是否可以接受? 如果不可接受,最好不要选择乐观锁策略。
c. 在批量处理应用中,重试事务带来的开销是否比由其他绘画释放锁带来的延迟更大? 如果是,使用悲观锁策略可能会更高效。
d. 悲观锁倾向于持有更短时间的锁。从而降低了遭遇锁争用的可能性。
e. 在交互应用中,悲观锁允许无限期地持有锁。交互应用中常常会出现这样的一种现象,比如提取并锁住数据后挂起,等待用户来点击OK按钮。如果用户出去开会了或者出去吃饭了.......没有意识到这条被显示的记录加了锁,则很可能会导致这条记录被锁住好几个小时。
——确定应用事务处理模型时,在乐观锁与悲观锁策略之间做一个正式的选择,并告知相关人员。
3.2. 在实现锁的时候,可以考虑Oracle 10g R1后加入的一个伪列:ORA_ROWSCN 即: Oracle Syste Change Number。这个自己没有实践,这里给出一个网站参考一下
http://oracle.chinaitlab.com/induction/776869.html 部分内容如下:
create
table
t1(i
int
);
insert into t1 values ( 1 );
insert into t1 values ( 2 );
commit ;
create table t2 rowdependencies as select * from t1;
insert into t1 values ( 1 );
insert into t1 values ( 2 );
commit ;
create table t2 rowdependencies as select * from t1;
4.使用存储过程来降低网络流量
- - - - - - - - -- - - - - - - - - - - - - - - - - - - - 2013-06-02 0:19。 终于写完了累啊这个周末。明天去打网球锻炼- - - - - - - - - - - - - - - - - -