我用vc及oracle oci绑定变量的方法进行大批量二进制数据的加载(40G),经过多种参数的调整,最后只能达到800m/h的速度(512m RAM),老板要求10G/h左右。通过report观测,发现每次加载oracle产生大量的undo操作即redo日志切换,我的数据库是no archivelog方式,表nologging方式,该如何屏蔽oracle上述两项操作,以提高加载速度?
解决方案 »
- 碰到PLS-00103错误各位大哥大姐们帮帮忙
- PL/SQL DEV连接数据库时跳出错误“Dynamic performance tables not accessible...”
- 急求高手解答
- order by 速度问题?先谢拉!
- 老问题了,上次没解决,求ORACLE查询语句的写法.
- LOAD DATA和控制文件的问题
- 菜鸟问,会者不难,难者不会
- 有关ORALCE EXPRESS SERVER 的启动问题
- what is the dbms_output.put_line()
- preparedStatement Interface 问题,高手请指教
- 能否在oracle的存储过程中使用嵌套游标?
- oracle数据库同步的问题,在线等.马上结.
alter table table_name nologgingLOGGING | NOLOGGING
Specify whether subsequent Direct Loader (SQL*Loader) and direct-path INSERT operations against a nonpartitioned table, table partition, all partitions of a partitioned table, or all subpartitions of a partition will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file. When used with the modify_table_default_attrs clause, this clause affects the logging attribute of a partitioned table. LOGGING|NOLOGGING also specifies whether ALTER TABLE ... MOVE and ALTER TABLE ... SPLIT operations will be logged or not logged. For a table or table partition, if you omit LOGGING|NOLOGGING, the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides. For LOBs, if you omit LOGGING|NOLOGGING, If you specify CACHE, then LOGGING is used (because you cannot have CACHE NOLOGGING). If you specify NOCACHE or CACHE READS, the logging attribute defaults to the logging attribute of the tablespace in which the LOB resides. NOLOGGING does not apply to LOBs that are stored inline with row data. That is, if you specify NOLOGGING for LOBs with values less than 4000 bytes and you have not disabled STORAGE IN ROW, Oracle ignores the NOLOGGING specification and treats the LOB data the same as other table data. In NOLOGGING mode, data is modified with minimal logging (to new extents invalid and to record dictionary changes). When applied during media recovery, the extent invalidation records a range of blocks as logically corrupt, because the redo data is not logged. Therefore, if you cannot afford to lose this table, it is important to back up the table after the NOLOGGING operation. If the database is run in ARCHIVELOG mode, media recovery from a backup made before the LOGGING operation will restore the table. However, media recovery from a backup made before the NOLOGGING operation will not restore the table. The logging attribute of the base table is independent of that of its indexes.
会找到的undo操作即redo日志切换:如果是nologging不应该有日志操作,应该是回滚断产生的要在windows下达到10G/h问题不大,就是服务器压力比较高
1. undo操作即redo日志切换
----------------------
undo和redo怎么能相提并论呢? 只要你的transction没有提交,
回滚段就要保存前映像的数据,如果事务很大,可能引发
snapshot too old的错误
2.纠正zzzsssccc(籍秋风)的一个错误:
如果是nologging不应该有日志操作
------------------------------
具体原因看我下面的分析,就算没有索引,也会有重做日志,只是
比较小而已. 3.使用Direct Insert 方式可以大量减小redo log, 可是索引的维护
需要产生重做日志,因此,你如果采用这种方法,必须先禁用目标
表上的相关索引. alter index ... unusable;
alter session set skip_unsable_indexes=true;
insert /*+ append */ into ...
commit;
alter index ... rebuild tablespace ...
... 4. 查看DML操作产生的重做日志 SQL>运行DML语句; SQL> Select name,value from v$sysstat
where name like '%redo size%';
现在问题的关键是:insert /*+ append */ into ...
直接路径加载有条件:
Direct-path INSERT supports only the subquery syntax of the INSERT statement, not the VALUES clause. For more information on the subquery syntax of INSERT statements。
我的程序要读数据加载,只能通过values语句,所以我屡次测试,速度没有提高。
leecooper0918,此处能否改进?
以前写过的程序,通过100M网传输,可以达到10G/h
[email protected]