关于ORACLE BLOB字段的插入问题

Question

表结构如下：数据文件N多个，表空间利用率60%
create table ZJVIO_NB.VIO_TEMP_IMG
(
  LSH   NUMBER not null,
  XH    VARCHAR2(16),
  ZJ1   BLOB,
  ZJLB1 VARCHAR2(10),
  ZJ2   BLOB,
  ZJLB2 VARCHAR2(10),
  ZJ3   BLOB,
  ZJLB3 VARCHAR2(10),
  ZJ4   BLOB,
  ZJLB4 VARCHAR2(10),
  ZJ5   BLOB,
  ZJLB5 VARCHAR2(10)
)
tablespace ZJVIO_TEMP_IMG
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 72K
    next 56K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table ZJVIO_NB.VIO_TEMP_IMG
  add constraint PK_VIO_TEMP_IMG_LSH primary key (LSH)
  using index 
  tablespace ZJVIO_TEMP_IMG
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 72K
    next 56K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
刚开始对zj1、zj2插入图片，每张图片1M左右，刚开始插入很快。后来渐渐要一分钟，到现在zj1、zj2已经无法插入。换成把图片插入到zj3、zj4，很快，一s左右，运行3、4天变成3s。我想再过一段时间zj3、zj4插入也会变慢到无法再插入。最后字段用完这个表无法再插入信息。
delete数据之后发现插入没有变快。alter table move之类的了解过，好像不管用，这个表的数据700G，move应该行不通。
请问：该怎么办。

Answer 1

慢到无法再插入？
你插入的逻辑是什么样的？
insert into (empty_blob()), 再update? 还是直接insert, bind blob?
700G, 每行有一个1M左右的blob，那么也就70万左右的记录量，不应该这么卡。但无论如何，这样的大图片，不应该存到blob字段里头。应该改成保存为路径。
想改成这样也很容易。加一个路径字段。对新入的值，只存路径。
旧的已有图片，找个空闲时间，直接图片BLOB读取存为本地文件，并入路径值。

Answer 2

insert into (empty_blob()), 再update。
恩，字段zji、zj2已经无法插入了，插到最后报出通信通道错误。而字段zj3、zj4现在插入不到2s。
这个表的数据被别的软件取走，这个软件不是我们做的，要别人改可能会有难度。
为什么删除数据之后性能没有改善呢？

Answer 3

嗯，如果是java，不建议你用empty_blob()，再update,效率实在太低。
如果是OCI，其实有别的解决办法，最好是调用存储过程。你这个错误，应该跟数据库的配置有关：
shared_pool_size
这个参数值可能设得太小了。
另外，对于有大BLOB值的情形，加滚段要设的比平常大得多才行。你改完参数以后，再测测试试。
跟delete不delete，一点关系都没有。

Answer 4

用的是java，我测试直接insert跟insert into (empty_blob()), 再update差不多，后者还要稍微快一点点，单个测试是这样，长期运行的话就不知道了。
“加滚段要设的比平常大得多才行。”这个应该怎么设置？
为什么zj1、zj2两个字段插入会变得这么慢呢？

Answer 5

你insert，再select for update, 这个主键值你得取出来吧。
能把你的代码帖出个大概吗？它不可能比insert, setBinaryStream还要快的。至于参数设置，你能google出一大堆的。增大share pool size
增大回滚段

Answer 6

谢谢你这么早起来帮我。
public void insertEpInfoPic1(final ElecPoliceTPDTO e) {
StringBuffer sb = new StringBuffer();
try{
sb.append("INSERT INTO ZJVIO_NB.VIO_TEMP_IMG(LSH,ZJ4,ZJLB4) VALUES(?,empty_blob(),?)");
commonDao.getJdbcTemplate().execute(
sb.toString(),
new AbstractLobCreatingPreparedStatementCallback(
ElecPoliceDAOImpl.defaultLobHandler) { protected void setValues(PreparedStatement ps,
LobCreator lobCreator) throws SQLException,DataAccessException {
ps.setString(1, e.getJlbh());
ps.setString(2, "0022000000");
}
});
}catch(Exception ex){
ex.printStackTrace();
}
sb = new StringBuffer();
sb.append("UPDATE ZJVIO_NB.VIO_TEMP_IMG SET ZJ4=? WHERE LSH=?");
commonDao.getJdbcTemplate().execute(
sb.toString(),
new AbstractLobCreatingPreparedStatementCallback(
ElecPoliceDAOImpl.defaultLobHandler) { protected void setValues(PreparedStatement ps,
LobCreator lobCreator) throws SQLException,DataAccessException {
lobCreator.setBlobAsBytes(ps, 1, e.getTp());
ps.setString(2, e.getJlbh());
}
});
}
我觉得跟代码应该是没有关系，应为我刚开始插很快，换字段也很快~

Answer 7

public void insertEpInfoPic1(final ElecPoliceTPDTO e) {
StringBuffer sb = new StringBuffer();
try{
sb.append("INSERT INTO ZJVIO_NB.VIO_TEMP_IMG(LSH,ZJ4,ZJLB4) VALUES(?,empty_blob(),?)");
commonDao.getJdbcTemplate().execute(
sb.toString(),
new AbstractLobCreatingPreparedStatementCallback(
ElecPoliceDAOImpl.defaultLobHandler) {protected void setValues(PreparedStatement ps,
LobCreator lobCreator) throws SQLException,DataAccessException {
ps.setString(1, e.getJlbh());
ps.setString(2, "0022000000");
}
});
}catch(Exception ex){
ex.printStackTrace();
}
sb = new StringBuffer();
sb.append("UPDATE ZJVIO_NB.VIO_TEMP_IMG SET ZJ4=? WHERE LSH=?");
commonDao.getJdbcTemplate().execute(
sb.toString(),
new AbstractLobCreatingPreparedStatementCallback(
ElecPoliceDAOImpl.defaultLobHandler) {protected void setValues(PreparedStatement ps,
LobCreator lobCreator) throws SQLException,DataAccessException {
lobCreator.setBlobAsBytes(ps, 1, e.getTp());
ps.setString(2, e.getJlbh());
}
});
}

Answer 8

我看了一下你的代码，对spring的jdbctemplate的这种模式不是很感冒。
它也有不需要select再update的方式插入blob，请看这篇帖子，介绍的很详细，希望你按照这个方法测试一下：
http://stackoverflow.com/questions/2770877/spring-jdbctemplate-insert-blob-and-return-generated-keyfinal File blobIn = new File("spring2004.jpg");
final InputStream blobIs = new FileInputStream(blobIn);
jdbcTemplate.execute(
  "INSERT INTO lob_table (id, a_blob) VALUES (?, ?)",
  new AbstractLobCreatingPreparedStatementCallback(lobhandler) {                         
      protected void setValues(PreparedStatement ps, LobCreator lobCreator) 
          throws SQLException {
        ps.setLong(1, 1L);
        lobCreator.setBlobAsBinaryStream(ps, 2, blobIs, (int)blobIn.length());           
      }
  }
);
blobIs.close();另外，你说的越来越慢，我只是推测，有可能是被锁住的原因。select和update之间，update的时候，都有可能发生锁等待。你先用上边的方法试试吧。看看到底有什么问题。

Answer 9

昨天晚上测试了一下，改成直接插入速度没有提升。
按照你的代码写了个单例测试了几张图片，zj1、zj2速度插入依然很慢，十多分钟。剩下的字段3s，1.5M左右的图片。
我现在想这样下去会不会越来越慢？能不能通过删除数据使插入速度变快？

Answer 10

还有我把这个表的索引删了重建，把数据、索引和blob字段单独放在两个表空间，速度都没有提高。

Answer 11

无论如何，也不应该如此之慢。
这个表结构也真是。。
字段顺序最好能调整一下。把blob字段统一放到表的末尾，非blob放到blob的前头。
另外，既然后边越来越慢，你可以对应的看看数据库的配置参数。
适当的debug也是有必要的。一个表中不应该有这么多的BLOB列。
如果是move 表，不妨直接用insert /*+ append */  into table_dest
select from table_source
where ...
你的BLOB字段都是1M左右或以上的大小吗？还是有的大小不足4K？

Answer 12

现在是：虽然慢，但是一天的数据能处理过来。跟客户提了要求，要是他们增加数据量，就要重新配置数据库服务器，表结构也要重新设计。现在的数据库服务器还是5年前的！
软件客户已经在用了，现在该表结构比较困难，要把数据倒出来先备份。哎，纠结的项目。上次的参数我配置了，shared_pool_size，java_pool_size，好像没用。
先结贴，谢谢你的帮助。

Answer 13

哦，忘了说，有三种图片，400K，800K，1.7M.

Answer 14

哥表示在网上找到这个，很是诧异～～～这个表，也是我的数据来源表，哈哈。最近，这种情况在舟山也出现了，但舟山出现的情况和镇海又不一样，舟山是zj1到zj4都OK，但zj5就死活进不去。
慢到pl/sql挂掉为止。情况很诡异呀，刚看到有人提到共享池的大小问题，也突然隐隐觉得有可能，但经过楼主朋友测试后也告失败了，目前DBA正在了解这个事情，估计过段时间会有结果吧。兄弟等等吧。～～～