oracle9i数据库,在运行7天左右将变慢,大概14天左右慢到不能忍受的地步,查看后发现oracle进程占用CPU高达100%。现将alert_orcl日志发上,希望各位大虾能帮助解决,感激不尽。
Tue Aug 28 09:02:17 2007
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Tue Aug 28 09:02:17 2007
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 268435456
large_pool_size = 8388608
java_pool_size = 33554432
control_files = D:\oracle\ora92\orcl\control01.ctl, D:\oracle\ora92\orcl\control02.ctl, D:\oracle\ora92\orcl\control03.ctl
db_block_size = 8192
db_cache_size = 25165824
compatible = 9.2.0.0.0
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = orcl
dispatchers = (PROTOCOL=TCP) (SERVICE=orclXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = D:\oracle\admin\orcl\bdump
user_dump_dest = D:\oracle\admin\orcl\udump
core_dump_dest = D:\oracle\admin\orcl\cdump
sort_area_size = 524288
db_name = orcl
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 25165824
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Tue Aug 28 09:02:21 2007
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Aug 28 09:02:22 2007
alter database mount exclusive
Tue Aug 28 09:02:28 2007
Successful mount of redo thread 1, with mount id 1159571871.
Tue Aug 28 09:02:28 2007
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Tue Aug 28 09:02:28 2007
alter database open
Tue Aug 28 09:02:29 2007
Beginning crash recovery of 1 threads
Tue Aug 28 09:02:29 2007
Started first pass scan
Tue Aug 28 09:02:29 2007
Completed first pass scan
831 redo blocks read, 28 data blocks need recovery
Tue Aug 28 09:02:30 2007
Started recovery at
Thread 1: logseq 692, block 290, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 2 Seq 692 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\ORA92\ORCL\REDO02.LOG
Tue Aug 28 09:02:30 2007
Ended recovery at
Thread 1: logseq 692, block 1121, scn 0.107823237
28 data blocks read, 28 data blocks written, 831 redo blocks read
Crash recovery completed successfully
Tue Aug 28 09:02:32 2007
Thread 1 advanced to log sequence 693
Thread 1 opened at log sequence 693
Current log# 3 seq# 693 mem# 0: D:\ORACLE\ORA92\ORCL\REDO03.LOG
Successful open of redo thread 1.
Tue Aug 28 09:02:33 2007
SMON: enabling cache recovery
Tue Aug 28 09:02:35 2007
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Tue Aug 28 09:02:35 2007
SMON: enabling tx recovery
Tue Aug 28 09:02:35 2007
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
Fri Aug 31 21:43:17 2007
Thread 1 advanced to log sequence 694
Current log# 1 seq# 694 mem# 0: D:\ORACLE\ORA92\ORCL\REDO01.LOG
Sun Sep 02 16:12:14 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_344200.trc:Mon Sep 03 09:51:34 2007
Thread 1 advanced to log sequence 695
Current log# 2 seq# 695 mem# 0: D:\ORACLE\ORA92\ORCL\REDO02.LOG
Mon Sep 03 17:06:57 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_445604.trc:
ORA-12571: TNS: 包写入程序失败Tue Sep 04 08:40:27 2007
Thread 1 advanced to log sequence 696
Current log# 3 seq# 696 mem# 0: D:\ORACLE\ORA92\ORCL\REDO03.LOG
Tue Sep 04 21:20:53 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_498808.trc:
ORA-12571: TNS: 包写入程序失败Tue Sep 04 23:39:36 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_503812.trc:
ORA-12571: TNS: 包写入程序失败Wed Sep 05 08:51:32 2007
Thread 1 advanced to log sequence 697
Current log# 1 seq# 697 mem# 0: D:\ORACLE\ORA92\ORCL\REDO01.LOG
Wed Sep 05 21:38:29 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_586264.trc:Thu Sep 06 08:54:42 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_616236.trc:Thu Sep 06 10:23:38 2007
Thread 1 advanced to log sequence 698
Current log# 2 seq# 698 mem# 0: D:\ORACLE\ORA92\ORCL\REDO02.LOG
Thu Sep 06 11:33:00 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_623480.trc:
Tue Aug 28 09:02:17 2007
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Tue Aug 28 09:02:17 2007
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 268435456
large_pool_size = 8388608
java_pool_size = 33554432
control_files = D:\oracle\ora92\orcl\control01.ctl, D:\oracle\ora92\orcl\control02.ctl, D:\oracle\ora92\orcl\control03.ctl
db_block_size = 8192
db_cache_size = 25165824
compatible = 9.2.0.0.0
db_file_multiblock_read_count= 16
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = orcl
dispatchers = (PROTOCOL=TCP) (SERVICE=orclXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = D:\oracle\admin\orcl\bdump
user_dump_dest = D:\oracle\admin\orcl\udump
core_dump_dest = D:\oracle\admin\orcl\cdump
sort_area_size = 524288
db_name = orcl
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 25165824
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Tue Aug 28 09:02:21 2007
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Aug 28 09:02:22 2007
alter database mount exclusive
Tue Aug 28 09:02:28 2007
Successful mount of redo thread 1, with mount id 1159571871.
Tue Aug 28 09:02:28 2007
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Tue Aug 28 09:02:28 2007
alter database open
Tue Aug 28 09:02:29 2007
Beginning crash recovery of 1 threads
Tue Aug 28 09:02:29 2007
Started first pass scan
Tue Aug 28 09:02:29 2007
Completed first pass scan
831 redo blocks read, 28 data blocks need recovery
Tue Aug 28 09:02:30 2007
Started recovery at
Thread 1: logseq 692, block 290, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 2 Seq 692 Reading mem 0
Mem# 0 errs 0: D:\ORACLE\ORA92\ORCL\REDO02.LOG
Tue Aug 28 09:02:30 2007
Ended recovery at
Thread 1: logseq 692, block 1121, scn 0.107823237
28 data blocks read, 28 data blocks written, 831 redo blocks read
Crash recovery completed successfully
Tue Aug 28 09:02:32 2007
Thread 1 advanced to log sequence 693
Thread 1 opened at log sequence 693
Current log# 3 seq# 693 mem# 0: D:\ORACLE\ORA92\ORCL\REDO03.LOG
Successful open of redo thread 1.
Tue Aug 28 09:02:33 2007
SMON: enabling cache recovery
Tue Aug 28 09:02:35 2007
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Tue Aug 28 09:02:35 2007
SMON: enabling tx recovery
Tue Aug 28 09:02:35 2007
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
Fri Aug 31 21:43:17 2007
Thread 1 advanced to log sequence 694
Current log# 1 seq# 694 mem# 0: D:\ORACLE\ORA92\ORCL\REDO01.LOG
Sun Sep 02 16:12:14 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_344200.trc:Mon Sep 03 09:51:34 2007
Thread 1 advanced to log sequence 695
Current log# 2 seq# 695 mem# 0: D:\ORACLE\ORA92\ORCL\REDO02.LOG
Mon Sep 03 17:06:57 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_445604.trc:
ORA-12571: TNS: 包写入程序失败Tue Sep 04 08:40:27 2007
Thread 1 advanced to log sequence 696
Current log# 3 seq# 696 mem# 0: D:\ORACLE\ORA92\ORCL\REDO03.LOG
Tue Sep 04 21:20:53 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_498808.trc:
ORA-12571: TNS: 包写入程序失败Tue Sep 04 23:39:36 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_503812.trc:
ORA-12571: TNS: 包写入程序失败Wed Sep 05 08:51:32 2007
Thread 1 advanced to log sequence 697
Current log# 1 seq# 697 mem# 0: D:\ORACLE\ORA92\ORCL\REDO01.LOG
Wed Sep 05 21:38:29 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_586264.trc:Thu Sep 06 08:54:42 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_616236.trc:Thu Sep 06 10:23:38 2007
Thread 1 advanced to log sequence 698
Current log# 2 seq# 698 mem# 0: D:\ORACLE\ORA92\ORCL\REDO02.LOG
Thu Sep 06 11:33:00 2007
Errors in file d:\oracle\admin\orcl\udump\orcl_ora_623480.trc:
解决方案 »
- 安装了oracle 11g之后第一次使用sql developer出错
- oracle11g exp出整个用户数据库以后imp出错
- ora02180;急急急!
- 在使用sql*loader时,中文乱码导致的一个错误,请教大家是否遇到过同样的问题
- oracle中创建临时表并将正式表中的数据导入到临时表
- 急,求助,有关ORACLE字符集的问题
- pro*c 的 编辑/选项 无法使用,请各位指点!!
- 从oracle网站上下的oracle 9i和购买的oracle 9i有什么区别?
- oracle进程的问题
- 请教一个数据库更新的问题
- 怎样选择字段中只有数字、逗号和分号的记录,字段类型为clob
- ORACAL如何建立数据库
v$session_wait看看数据库什么情况.
select event,p1,p2,sid from v$session_wait如有时间最好作个statspack.
另外问一下,是你在负责这个库吗?
由于这个星期忙着开讲座,所以直到昨天才把report发到你的邮箱,我认为buffer hit太低了,但buffer hit低会造成oracle.exe进程占用98%以上的CPU资源吗?所以,还请您帮忙看看是什么问题。
不知道你的物理内存有多大.但是你的db_cache_size实在是太小了.才25M
0 WHERE (idx0.asord_idx_grp = '02' AND idx0.asord_idx_cont Like
unistr('\0025\5B66\751F\7BA1\7406\6587\4EF6\6C47\7F16\0025'))这个语句的物理读比较高.
想办法优化一下,这个这个语句最好bind var一下.