java与Oracle相关性能优化总结1

一 、 java相关 性能优化

1.java 中的硬代码如何解决：写配置文件
2 java应用程序访问数据库的过程（简单描述）

A、 加载驱动
B、 建立连接
C、 访问数据库，执行sql
D. 关闭连接

3.java中 深入理解JDBC 的预编译对象
使用预编译对象的优点：A、减少编译次数，提高系统效率:
a、JDBC中用的就是绑定变量 ，减少编译次数，提高执行效率
b、JDBC中 通常会犯下面语句的 错误：

PreparedStatement pst = con.prepareStatement( "select ? from emp ");
pst.setString(1,‘ename’);
这句SQL能够执行，但是输出结果是 ename
ename, ename......
因为在内部本身就将其当作“绑定变量” 本来就有引号，若是变量名或者是数据库对象名等对象，则会将其当成值来执行
相对于 select '值' from emp；
B、 更容易维护
C、安全性更高，避免sql注入：

例如：a、Oracle中的SQL注入 ： select * from t_user where id = 999or rownum = 1;
b、 sqlserver 中 的sql注入 ：select * from t_user where id = 999 or 1=1;
c、select * from user where id = 999 ; drop table user;
a、 因为在statement 中 将 id = 999or rownum = 1 当成 名令来执行；
而在PreparedStatement 中 将 id = 999or rownum = 1 当成字符串来执行；
b、 因为在statement 中会把 ;这符号当成一句话结束，而去执行drop table user这句话
而在PreparedStatement 中 会把这id = 999 ; drop table user整体当成字符串来执行

4、java中 的数据库连接池
JDBC作为中数据库访问技术 ， 简单易用，但是在 web 项目的应用程序开发中，存在很大的问题
，因为没一次web请求都会建立一些链接，建立连接是一个费时的活动，而且系统还有分配内存资源
这个时间对于几次或几十次访问的开销还感觉不出来。但是像企业电子商务网站，同时有
几百人，几千在线是很正常的事。在这种情况下频繁的数据库访问操作，将大量的消耗内存，并且效率降低
、网站响应的速度必将下降、严重的甚至会造成服务器。崩溃不是危言耸听，这就是制约某些电子商务网站发展的技术问题所在 。
其次 ，对于每次数据库连接都要在使用完后，关闭流，避免内存泄漏，否则如果数据库出现异常，而未能关闭，则会造成内存泄漏，最终不得不重启数据库。
还有，这种开发不能控制连接对象数，系统会毫无顾忌的分配资源给连接对象，如果连接过多会造成内存泄漏，最终可能会导致服务器崩溃。

数据库连接池（connection pool）的工作原理
　　1、基本概念及原理
由上面的分析可以看出，问题的根源就在于对数据库连接资源的低效管理。我们知道，
　　对于共享资源，有一个很著名的设计模式：资源池（Resource Pool）。该模式正是为了解决资源的频繁分配﹑释放所造成的问题。为解决上述问题，可以采用数据库连接池技术。数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。我们可以通过设定连接池最大连接数来防止系统无尽的与数据库连接。更为重要的是我们可以通过连接池的管理机制监视数据库的连接的数量﹑使用情况，为系统开发﹑测试及性能调整提供依据
　　2、服务器自带的连接池
　　JDBC的API中没有提供连接池的方法。一些大型的WEB应用服务器如BEA的WebLogic和IBM的WebSphere等提供了连接池的机制，但是必须有其第三方的专用类方法支持连接池的用法。
　　连接池关键问题分析
　　1、并发问题
　　为了使连接管理服务具有最大的通用性，必须考虑多线程环境，即并发问题。这个问题相对比较好解决，因为Java语言自身提供了对并发管理的支持，使用synchronized关键字即可确保线程是同步的。使用方法为直接在类方法前面加上synchronized关键字，如：
public synchronized Connection getConnection（）
　　2、多数据库服务器和多用户
　　对于大型的企业级应用，常常需要同时连接不同的数据库（如连接Oracle和Sybase）。如何连接不同的数据库呢？我们采用的策略是：设计一个符合单例模式的连接池管理类，在连接池管理类的唯一实例被创建时读取一个资源文件，其中资源文件中存放着多个数据库的url地址（<poolName.url>）﹑用户名（<poolName.user>）﹑密码（<poolName.password>）等信息。如tx.url=192.168.1.123:5000/tx_it，tx.user=cyl，tx.password=123456。根据资源文件提供的信息，创建多个连接池类的实例，每一个实例都是一个特定数据库的连接池。连接池管理类实例为每个连接池实例取一个名字，通过不同的名字来管理不同的连接池。
　　3、事务处理
　　我们知道，事务具有原子性，此时要求对数据库的操作符合“ALL-ALL-NOTHING”原则,即对于一组SQL语句要么全做，要么全不做。
在Java语言中，Connection类本身提供了对事务的支持，可以通过设置Connection的AutoCommit属性为false,然后显式的调用commit或rollback方法来实现。但要高效的进行Connection复用，就必须提供相应的事务支持机制。可采用每一个事务独占一个连接来实现，这种方法可以大大降低事务管理的复杂性。
　　4、连接池的分配与释放
　　连接池的分配与释放，对系统的性能有很大的影响。合理的分配与释放，可以提高连接的复用度，从而降低建立新连接的开销，同时还可以加快用户的访问速度。
　　对于连接的管理可使用空闲池。即把已经创建但尚未分配出去的连接按创建时间存放到一个空闲池中。每当用户请求一个连接时，系统首先检查空闲池内有没有空闲连接。如果有就把建立时间最长（通过容器的顺序存放实现）的那个连接分配给他（实际是先做连接是否有效的判断，如果可用就分配给用户，如不可用就把这个连接从空闲池删掉，重新检测空闲池是否还有连接）；如果没有则检查当前所开连接池是否达到连接池所允许的最大连接数（maxConn）,如果没有达到，就新建一个连接，如果已经达到，就等待一定的时间（timeout）。如果在等待的时间内有连接被释放出来就可以把这个连接分配给等待的用户，如果等待时间超过预定时间timeout,则返回空值（null）。系统对已经分配出去正在使用的连接只做计数，当使用完后再返还给空闲池。对于空闲连接的状态，可开辟专门的线程定时检测，这样会花费一定的系统开销，但可以保证较快的响应速度。也可采取不开辟专门线程，只是在分配前检测的方法。
　　5、连接池的配置与维护
　　连接池中到底应该放置多少连接，才能使系统的性能最佳？系统可采取设置最小连接数（minConn）和最大连接数（maxConn）来控制连接池中的连接。最小连接数是系统启动时连接池所创建的连接数。如果创建过多，则系统启动就慢，但创建后系统的响应速度会很快；如果创建过少，则系统启动的很快，响应起来却慢。这样，可以在开发时，设置较小的最小连接数，开发起来会快，而在系统实际使用时设置较大的，因为这样对访问客户来说速度会快些。最大连接数是连接池中允许连接的最大数目，具体设置多少，要看系统的访问量，可通过反复测试，找到最佳点。
　　如何确保连接池中的最小连接数呢？有动态和静态两种策略。动态即每隔一定时间就对连接池进行检测，如果发现连接数量小于最小连接数，则补充相应数量的新连接,以保证连接池的正常运转。静态是发现空闲连接不够时再去检查。
连接池的实现


二、Oracle性能优化


Oracle优化：

将优化目标器设置为choose.
alter session set optimizer_goal=choose;

有些地方测试的结果并不明显，特别是有索引的时候，情况变的很复杂。

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

第一集：


???? 如何进行ORACLE SQL优化
1.where子句连接条件的顺序：(Oracle采用自下而上的顺序解析where子句)
a.表之间的连接写在where之前
b.过滤最多记录数的条件放在where子句的末尾
例如：
（低效）：where子句的最后一个条件过滤的数据不是最多的
SELECT empno,ename,job,sal
FROM EMP E
WHERE SAL > 50000
AND JOB = 'MANAGER'
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);

（高效）：where子句的最后一个条件过滤的数据最多
SELECT empno,ename,job,sal
FROM EMP E
WHERE SAL > 50000
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
AND JOB = 'MANAGER'
小结：先测试出每个过滤条件能返回的数据数，
返回最少的放最后，返回最多的放第一个
中间的条件也按这个顺序排
------------------------------------------

2.避免使用 * ,它是传说中的效率杀手。

3.最高效的删除重复记录方法：（使用rowid）
delete emp e where e.rowid>
(
select min(x.rowid) from emp x
where x.emp_no=e.emp_no
);

4.使用truncate代替delete，删除所有记录，不用写日志。

5.尽量多使用commit;

6.用where子句代替having子句：
避免使用having子句，having只会在检索出所有记录之后才对结果进行过滤。
这个处理需要排序，总计等操作。
如果能通过where子句限制记录的数目，就能减少这方面的开销。
例如：
（低效）
select deptno,avg(sal) from emp
group by deptno
having deptno!=10 and deptno!=30;
（高效）
select deptno,avg(sal) from emp
where deptno!=10 and deptno!=30
group by deptno;
小结：通过对300万条记录的多次测试，效果还算明显。

--------------------------------

7.连接多表查询时使用表的别名（Alias）
使用别名可以减少解析时间，可以很快确定哪个列属于哪个表。

8.使用exists代替in（和not exists代替not in）
exists只管有没有返回记录，不管返回的是什么数据。
in就要管返回的是什么。

9.基础表的选择:(基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问). 根据优化器的不同, SQL语句中基础表的选择是不一样的.)
1、如果你使用的是CBO (COST BASED OPTIMIZER),优化器会检查SQL语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径.
2、如果你用RBO (RULE BASED OPTIMIZER) , 并且所有的连接条件都有索引对应, 在这种情况下, 基础表就是FROM 子句中列在最后的那个表.(注意没有相比较的索引的列，那个表会被当作基础表)

10.第一列使用索引


*****************************************************************************
第二集：

Oracle中--优化SQL语句执行的原则
1.基本经验
a已经检验的语句和已在共享池中的语句之间要完全一样
b变量名称尽量一致
c合理使用外联接
d少用多层嵌套
e多用并发

2.优化的一般步骤
a、调整sga区，使得sga区的是用最优。
b、优化sql语句本身的工具有explain,sql trace等
c、数据库结构调整
d、项目结构调整

3.写sql语句的经验：
a、对于大表的查询使用索引
b、少用in,exist等
c、使用集合运算

4.对于大表查询中的列，应尽量避免进行诸如：
to_char,to_date,to_number等转换。

5.有索引的尽量使用索引，有用到索引的条件写在前面（如果有必要就建一些索引）

6.尽量避免全表扫描，限制条件尽可能多，以便更快搜索到数据。

8.如何让你的SQL运行得更快：
1、不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高！
a.有大量重复值、且经常有范围查询（between, >,<,>=,<=）
和order by、group by发生的列，可考虑建立群集索引；
b.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；
c.组合索引尽量使关键查询形成索引覆盖，前导列一定是使用最频繁的列。
2、不充份的连接条件
多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。
3、不可优化的where子句
下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢。
select * from record where
substring(card_no,1,4)=5378(13秒)
select * from record where
amount/30< 1000（11秒）
select * from record where
convert(char(10),date,112)=19991201（10秒）
分析：
where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：
select * from record where card_no like
5378%（< 1秒）
select * from record where amount
< 1000*30（< 1秒）
select * from record where date= 1999/12/01
（< 1秒）SQL明显快起来！
4、把or拆成多个语句
把select count(*) from stuff where id_no=0 or id_no=1;
拆分成select count(*) from stuff where id_no=0
select count(*) from stuff where id_no=1再加起来

.......................................................
小结：

1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。
3.要善于使用存储过程，它使SQL变得更加灵活和高效。
--SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。


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


三。JDBC调用各种存储过程
JDBC调用各种存储过程

JDBC调用存储过程(无返回值)》》》》》》》》》》》》》》》》》》》》
-----------------------表------------------------
--创建学生表
create table t_s(
s_id number(3) primary key,
s_name varchar2(10) not null,
s_class number(3)
);
drop table t_s;
select * from t_s;
delete from t_s;
create sequence autonum;
--创建班级表
create table t_c(
autonumber number(3),
c_id number(3),
c_name varchar2(10)
);
--测试用
call add_s(123,'张三',40);
---------------------存储过程------------------------
create or replace procedure add_s
(
p_s_id t_s.s_id%type, --参数类型定义为字段类型
p_s_name t_s.s_name%type,
p_c_id t_c.c_id%type
)
as
num number;
new_name t_s.s_name%type;
begin
--处理姓名(如果不从查询结果取值,用 := )
new_name := substr(trim(p_s_name),1,8);
--将查询结果保存到变量,只能用select into
select count(*) into num from t_c c where c.c_id=p_c_id;
if(num=0) then
insert into t_c(c_id,c_name) values(p_c_id,'新班');
end if;
insert into t_s(s_id,s_name,s_class)
values(p_s_id,new_name,p_c_id);
commit;
dbms_output.put_line('执行完毕');
end;
--------------------java类----------------------
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* 无返回值的
* @author lovo
*
*/
public class Procedure {
public void conn(){
//1.配置驱动程序(?)
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//2.指出连接信息
String user = "scott";
String pwd = "tiger";
String url = "jdbc:oracle:thin:@localhost:1521:lovo";
//3.创建连接对象
Connection conn = null;
CallableStatement cstmt = null;
//sql变为delete/update,都可以直接执行
String procedure = "{call add_s(?,?,?)}";//sql无;
try {
conn = DriverManager.getConnection(url, user, pwd);
//5.创建语句对象
cstmt = conn.prepareCall(procedure);
//3个问号的值
cstmt.setString(1,"250");
cstmt.setString(2,"小三");
cstmt.setString(3,"25");

//6.执行
cstmt.executeUpdate();
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}finally{
try {
cstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* @param args
*/
public static void main(String[] args) {
new Procedure().conn();
}
}

JDBC调用存储过程(有返回值)》》》》》》》》》》》》》》》》》》》》
--指定参数为out类型即可返回值
--返回结果值示例
create or replace procedure sp_value(
id1 in number,
id2 out number
)
as
begin
id2 := id1*200;
end;
------------------java类---------------------
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* 有返回值的
* @author lovo
*
*/
public class ProcedureReturn {
public void conn(){
//1.配置驱动程序(?)
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//2.指出连接信息
String user = "scott";
String pwd = "tiger";
String url = "jdbc:oracle:thin:@localhost:1521:lovo";
//3.创建连接对象
Connection conn = null;
CallableStatement cstmt = null;
//sql变为delete/update,都可以直接执行
String procedure = "{call sp_value(?,?)}";//sql无;
try {
conn = DriverManager.getConnection(url, user, pwd);
//5.创建语句对象
cstmt = conn.prepareCall(procedure);
//3个问号的值
cstmt.setString(1,"450");
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
//6.执行
cstmt.executeUpdate();
//从输入参数中获取值
int value = cstmt.getInt(2);
System.out.println("返回: " + value);
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}finally{
try {
cstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* @param args
*/
public static void main(String[] args) {
new ProcedureReturn().conn();
}
}