BUFFER CACHE 调优¶
设置参数¶
BUF_DATA_BUFFER_PAGES = 64
创建表¶
create table t(a int, b varchar(50), c varchar(50), d varchar(50), e varchar(50), f varchar(50), g varchar(50), h varchar(50), i varchar(50), j varchar(50), k varchar(50),l varchar(50), m varchar(50), n varchar(50));
数据插入程序
public class Test {
private static String url="jdbc:oscar://localhost:2003/osrdb ";
public static void main(String[] args) {
Test ioTest = new Test();
try {
Connection conn = ioTest.getConnection();
Statement st = conn.createStatement();
//循环插入
for(int i = 0; i < 100000; i++) {
st.executeUpdate("insert into t values("+12.0+",'asdasdadasdasdasdasdsadeweweweweweeweweweewwewe'," +
" 'weweweweeeeeewewewewewewwewewewewwewewewewe', " +
"'wewewewewewewewewewewewewewewewewewewwewweew'," +
"'sdfsfsffsdfdfsfsfsfdsdfdfsfdsfsdfsfsdfsddfdfds', " +
" 'sdfasdfasdfsafdasdfafsdfsdfsdfsdsdsdf'," +
" 'sdfasdfasdfsafdasdfafsdfsdfsdfsdsdsdf', " +
" 'sdfasdfasdfsafdasdfafsdfsdfsdfsdsdsdf', " +
"'sdfasdfasdfsafdasdfafsdfsdfsdfsdsdsdf', " +
" 'sdfasdfasdfsafdasdfafsdfsdfsdfsdsdsdf', " +
"'sdfasdfasdfsafdasdfafsdfsdfsdfsdsdsdf', " +
" 'sdfasdfasdfsafdasdfafsdfsdfsdfsdsdsdf', " +
"'sdfasdfasdfsafdasdfafsdfsdfsdfsdsdsdf', " +
" 'sdfasdfasdfsafdasdfafsdfsdfsdfsdsdsdf')");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 创建连接
* @return
* @description
* @version 1.0
* @author whw
* @update 2012-5-23 下午1:16:29
*/
public Connection getConnection() {
Connection con = null;
try {
Class.forName("com.oscar.Driver");
con = DriverManager.getConnection(url, "sysdba", "szoscar55");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
}
查询数据¶
打开运行时诊断工具BUFFER CACHE 缓存命中率监控，运行查询语句，
BUFFER CACHE 命中率
public class Test {
private static String url="jdbc:oscar://localhost:2003/osrdb";
public static void main(String[] args) {
Test ioTest = new Test();
try {
Connection conn = ioTest.getConnection();
//设置为手动提交
long start = System.currentTimeMillis();
Statement st = conn.createStatement();
//循环插入
for(int i = 0; i < 100; i++) {
st.executeQuery("select * from t");
}
long end = System.currentTimeMillis();
System.out.println(end - start);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 创建连接
* @return
* @description
* @version 1.0
* @author whw
* @update 2012-5-23 下午1:16:29
*/
public Connection getConnection(){
Connection con = null;
try {
Class.forName("com.oscar.Driver");
con = DriverManager.getConnection(url, "sysdba", "szoscar55");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
}
结果如下：
图 20-30 BUFFER CACHE 命中率
通过实时监控可以发现命中率为57%命中率比较低。运行时间为4140ms
性能调优¶
修改BUF_DATA_BUFFER_PAGES为8192（理论上讲在内存容许的情况下越大越好）重新执行查询程序。
结果如下：
图 20-31 BUFFER CACHE 命中率
程序的运行时间降低为2719ms，命中率也有很大的提升变为95%。
表格 20-14
| 名称 | 修改前 | 修改后 | 性能提升百分比（1 – 修改后/修改前） |
|---|---|---|---|
| 程序运行时间 | 4140 ms | 2719 ms | 34.3% |