数据库重启之后,Temp临时表空间使用率为零,数据查询很慢.不知道什么原因,求教!!!
sidALRT.log日志如下:
ump file E:\oracle\admin\MySID\bdump\MySIDALRT.LOG
Mon Oct 27 10:14:52 2008
ORACLE V9.0.1.1.1 - Production vsnsta=0
vsnsql=10 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Starting up ORACLE RDBMS Version: 9.0.1.1.1.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 46137344
java_pool_size = 33554432
control_files = E:\oracle\oradata\MySID\CONTROL01.CTL, E:\oracle\oradata\MySID\CONTROL02.CTL, E:\oracle\oradata\MySID\CONTROL03.CTL
db_block_size = 8192
db_cache_size = 33554432
compatible = 9.0.0
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = MySID
dispatchers = (PROTOCOL=TCP)(SER=MODOSE), (PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer), (PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer), (PROTOCOL=TCP)
hash_area_size = 1048576
background_dump_dest = E:\oracle\admin\MySID\bdump
user_dump_dest = E:\oracle\admin\MySID\udump
core_dump_dest = E:\oracle\admin\MySID\cdump
sort_area_size = 1048576
db_name = MySID
open_cursors = 800
ifile = E:\oracle\admin\MySID\pfile\init.ora
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
Mon Oct 27 10:14:54 2008
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Mon Oct 27 10:14:57 2008
alter database mount exclusive
Mon Oct 27 10:15:03 2008
Successful mount of redo thread 1, with mount id 3028477986.
Mon Oct 27 10:15:03 2008
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Mon Oct 27 10:15:03 2008
alter database open
Mon Oct 27 10:15:03 2008
Beginning crash recovery of 1 threads
Mon Oct 27 10:15:03 2008
Started first pass scan
Mon Oct 27 10:15:04 2008
Completed first pass scan
Mon Oct 27 10:15:04 2008
Started recovery at
Thread 1: logseq 90, block 17526, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 2 Seq 90 Reading mem 0
Mem# 0 errs 0: E:\ORACLE\ORADATA\MySID\REDO02.LOG
Mon Oct 27 10:15:04 2008
Ended recovery at
Thread 1: logseq 90, block 24867, scn 0.10167283
63 data blocks read, 63 data blocks written, 7341 redo blocks read
Crash recovery completed successfully
Mon Oct 27 10:15:05 2008
Thread 1 advanced to log sequence 91
Thread 1 opened at log sequence 91
Current log# 3 seq# 91 mem# 0: E:\ORACLE\ORADATA\MySID\REDO03.LOG
Successful open of redo thread 1.
Mon Oct 27 10:15:05 2008
SMON: enabling cache recovery
Mon Oct 27 10:15:06 2008
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.
Mon Oct 27 10:15:06 2008
SMON: enabling tx recovery
Mon Oct 27 10:15:10 2008
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
------解决方案--------------------
重新创建试。。把db_cache_size手动调大。这个是高速数据缓存。
------解决方案--------------------
select *
from database_properties
where property_value like '%DEFAULT%';
看看你的数据库是哪个默认的临时表空间？
------解决方案--------------------
这位弟兄的贴子请看一下，看对楼主有无帮助。
http://www.hackhome.com/InfoView/Article_164074.html
------解决方案--------------------
数据库重启后，临时表空间的使用率是0，这个没有问题的。随着系统的使用，临时表空间的使用率就不是0了。
shared_poo_size和db_cache_size都太小了，试着分别改成200m和100m试试。