情况是这样的:
系统:windows server 2008 64位
Oracle版本:Oracle 11g 64位
服务器: IBM 3850 X5
之前装上11G 运行一切正常,正常了3个月,今天发现Tomcat上的应用程序写入数据很慢,后查找问题,发现从启动tomcat连接数据库时都要3分钟左右,在数据库服务器本机上使用sqlplus登陆仍然需要1分钟才能连接上,使用PL/SQL 查询表 点查询 也需要1分钟的样子(只有不到10条数据的表),重启服务器无效果,但使用Tomcat平台的应用程序做查询 等,却无严重的延迟情况,在线跪求高手解决。PS:此库数据量最大的表 数据量大概在5000万条,和此问题是否有关。
系统:windows server 2008 64位
Oracle版本:Oracle 11g 64位
服务器: IBM 3850 X5
之前装上11G 运行一切正常,正常了3个月,今天发现Tomcat上的应用程序写入数据很慢,后查找问题,发现从启动tomcat连接数据库时都要3分钟左右,在数据库服务器本机上使用sqlplus登陆仍然需要1分钟才能连接上,使用PL/SQL 查询表 点查询 也需要1分钟的样子(只有不到10条数据的表),重启服务器无效果,但使用Tomcat平台的应用程序做查询 等,却无严重的延迟情况,在线跪求高手解决。PS:此库数据量最大的表 数据量大概在5000万条,和此问题是否有关。
解决方案 »
- 麻烦大家推荐一本 经典的oracle olap 开发方面的书籍!谢谢大家
- 动态行列转换,求指点
- 下载oracle的哪个版本合适
- PowerDesigner怎么导入Oracle
- 希望好手能帮我,谢谢!我白天提了这个问题。
- 在SQL*PLUS里面执行以下语句,为什么不行呢?
- 怎么写工具检查.dat文件里面是不是有重复的数据
- 在oracle中用A表數據updateB表數據的問題,請大家賜教,急!!!!!!!!!!!!!
- 求IBM/AIX下双机备份下的Oracle安装方法策略???
- 一个SQL SERVER或ORACLE数据库,其数据是以文件的形式存于硬盘吗
- pl/sql 存储过程
- 半夜三更的求解。这个sql怎么写??
看是不是sga分配太多了,系统使用的内存太少
cpu使用情况方能找到答案!
这个问题搞的我好烦啊。
AWR报告是什么
2、查看oracle的资源
sqlplus /nolog
conn / as sysdba
show sga
Fixed Size 2140400 bytes
Variable Size 7985067792 bytes
Database Buffers 5704253440 bytes
Redo Buffers 4427776 bytes我用CREATE PFILE='ahfu.ora' FROM SPFILE; 创建了这个文件 内容如下dzjc.__db_cache_size=5704253440
dzjc.__java_pool_size=268435456
dzjc.__large_pool_size=67108864
dzjc.__oracle_base='F:\app\Administrator'#ORACLE_BASE set from environment
dzjc.__pga_aggregate_target=4764729344
dzjc.__sga_target=8992587776
dzjc.__shared_io_pool_size=0
dzjc.__shared_pool_size=2818572288
dzjc.__streams_pool_size=0
*.audit_file_dest='F:\app\Administrator\admin\DZJC\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='F:\app\Administrator\oradata\DZJC\control01.ctl','F:\app\Administrator\oradata\DZJC\control02.ctl','F:\app\Administrator\oradata\DZJC\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DZJC'
*.db_recovery_file_dest='F:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='F:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DZJCXDB)'
*.memory_target=13736345600
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
2、select * from v$session_Wait;贴出对应内容
Adjusting the default value of parameter parallel_max_servers
from 160 to 135 due to the value of parameter processes (150)
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in client-side pfile F:\APP\ADMINISTRATOR\ADMIN\DZJC\PFILE\INIT.ORA on machine WIN-ZGRIU3LXHTY
System parameters with non-default values:
processes = 150
memory_target = 13120M
control_files = "F:\APP\ADMINISTRATOR\ORADATA\DZJC\CONTROL01.CTL"
control_files = "F:\APP\ADMINISTRATOR\ORADATA\DZJC\CONTROL02.CTL"
control_files = "F:\APP\ADMINISTRATOR\ORADATA\DZJC\CONTROL03.CTL"
db_block_size = 8192
compatible = "11.1.0.0.0"
db_recovery_file_dest = "F:\app\Administrator\flash_recovery_area"
db_recovery_file_dest_size= 2G
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=DZJCXDB)"
audit_file_dest = "F:\APP\ADMINISTRATOR\ADMIN\DZJC\ADUMP"
audit_trail = "DB"
db_name = "DZJC"
open_cursors = 300
diagnostic_dest = "F:\APP\ADMINISTRATOR"
Tue Jul 05 23:58:56 2011
PMON started with pid=3, OS id=3204
Tue Jul 05 23:58:56 2011
VKTM started with pid=5, OS id=4780 at elevated priority
VKTM running at (20)ms precision
Tue Jul 05 23:58:56 2011
DIAG started with pid=7, OS id=3528
Tue Jul 05 23:58:56 2011
DBRM started with pid=9, OS id=3972
Tue Jul 05 23:58:56 2011
PSP0 started with pid=11, OS id=2116
Tue Jul 05 23:58:56 2011
DIA0 started with pid=13, OS id=2804
Tue Jul 05 23:58:56 2011
MMAN started with pid=15, OS id=3792
Tue Jul 05 23:58:56 2011
DBW0 started with pid=2, OS id=1636
Tue Jul 05 23:58:56 2011
DBW1 started with pid=17, OS id=3636
Tue Jul 05 23:58:56 2011
LGWR started with pid=19, OS id=4576
Tue Jul 05 23:58:56 2011
CKPT started with pid=21, OS id=2424
Tue Jul 05 23:58:56 2011
SMON started with pid=23, OS id=3744
Tue Jul 05 23:58:56 2011
RECO started with pid=25, OS id=2172
Tue Jul 05 23:58:56 2011
MMON started with pid=27, OS id=4040
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Jul 05 23:58:56 2011
MMNL started with pid=29, OS id=5020
starting up 1 shared server(s) ...
ORACLE_BASE from environment = F:\app\Administrator***********************************************************************Fatal NI connect error 12537, connecting to:
(LOCAL=NO) VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Time: 20-9月 -2011 09:33:13
Tracing not turned on.
Tns error struct:
ns main err code: 12537
TNS-12537: TNS: 连接关闭
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
ORA-609 : opiodr aborting process unknown ospid (2168_8072)
Tue Sep 20 09:33:23 2011
***********************************************************************Fatal NI connect error 12537, connecting to:
(LOCAL=NO) VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Time: 20-9月 -2011 09:33:23
Tracing not turned on.
Tns error struct:
ns main err code: 12537
TNS-12537: TNS: 连接关闭
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
ORA-609 : opiodr aborting process unknown ospid (2168_6696)
Tue Sep 20 09:34:13 2011
***********************************************************************Fatal NI connect error 12570, connecting to:
(LOCAL=NO) VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Time: 20-9月 -2011 09:34:13
Tracing not turned on.
Tns error struct:
ns main err code: 12570
TNS-12570: TNS: 包阅读程序失败
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
ORA-609 : opiodr aborting process unknown ospid (2168_8112)
Tue Sep 20 09:34:44 2011
***********************************************************************Fatal NI connect error 12570, connecting to:
(LOCAL=NO) VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Time: 20-9月 -2011 09:34:44
Tracing not turned on.
Tns error struct:
ns main err code: 12570
TNS-12570: TNS: 包阅读程序失败
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
ORA-609 : opiodr aborting process unknown ospid (2168_7956)
Tue Sep 20 09:35:24 2011
***********************************************************************Fatal NI connect error 12570, connecting to:
(LOCAL=NO) VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Time: 20-9月 -2011 09:35:24
Tracing not turned on.
Tns error struct:
ns main err code: 12570
TNS-12570: TNS: 包阅读程序失败
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
ORA-609 : opiodr aborting process unknown ospid (2168_7816)
Tue Sep 20 09:35:45 2011
***********************************************************************Fatal NI connect error 12570, connecting to:
(LOCAL=NO) VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Time: 20-9月 -2011 09:35:45
Tracing not turned on.
Tns error struct:
ns main err code: 12570
TNS-12570: TNS: 包阅读程序失败
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
ORA-609 : opiodr aborting process unknown ospid (2168_7796)
Tue Sep 20 09:36:15 2011
***********************************************************************Fatal NI connect error 12570, connecting to:
(LOCAL=NO) VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.1.0.7.0 - Production
Time: 20-9月 -2011 09:36:15
Tracing not turned on.
Tns error struct:
ns main err code: 12570
TNS-12570: TNS: 包阅读程序失败
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
ORA-609 : opiodr aborting process unknown ospid (2168_7836)
Tue Sep 20 09:37:06 2011
***********************************************************************
应该是你应用程序出现的问题。
*.processes=150
还有你的SGA设置得可能过大具体设多少需要通过sga报告来看
*.processes=150
还有你的SGA设置得可能过大具体设多少需要通过sga报告来看
下面是你日志里报错的建议,关闭你服务器上的防火墙,杀毒软件,看是不是这个问题引起了监听的不稳定
TNS-12570 TNS:packet reader failureCause: An error occurred during a data receive. This message is not normally visible to the user.Action: For further details, turn on tracing and re-execute the operation. If the error persists, contact Oracle Support Services.