oracle 8i升级后性能不佳 [英] Poor performance after oracle 8i upgrade

问题描述

大家好


我不是DBA，而是unix管理员。


我们的DBA从oracle 8.0升级后.5到我们的

测试服务器上的oracle 8.1.7.4，我们注意到我们的应用程序大幅减速

（acocobol）。从~10分钟到~45分钟。

服务器信息：

HP Proliant ML350G3

1024MB Ram

1 x Intel Xeon 2.8GHz处理器

Unixware 7.1.1


总等待

等待等待

事件等待超时时间（cs）

（ms）/ txn

---------------------------- ------------ --------- - -----------

------ ------

日志文件同步90,709 0 123,003

14 1.0

日志文件并行写入90,719 0 121,555

13 1.0


我们的重做日志在镜像上。

我们也试过设置OPTIMIZER_FEATURES_ENABLE = 8.0.0

但没有运气.....

DBA'试过了升级2种不同的方式

1.将原始数据库从8.0.5升级到8.1.7.4

2.建立全新的数据库从头开始然后导入数据。


两种方式都有相同的性能问题。

有没有人知道为什么会在8.1.7.4和不在

8.0.5，以及我们如何解决它。


thankyou


问候

Ruth

解决方案

> DBA'尝试了2种不同的升级方式

1.将原始数据库从8.0.5升级到8.1.7.4
2.从头开始构建全新的数据库然后导入数据。两种方式都有相同的性能问题。

有没有人知道为什么会在8.1.7.4上发生这种情况而不是在8.0.5上，以及我们如何可以解决它。

我要做的第一件事是确保你的统计数据最多可以在你的桌子上显示......如果您使用上面的步骤1，那么这应该是

的情况，但无论如何都要确认。


查询last_analzed和num_rows的user_tables ...


如果这些是最新的，你需要在启用

sql_trace的情况下运行8.0.5下的过程，然后将其与8.1.7的相同比较。


使用tkprof格式化跟踪文件并按

" prsela，fchela，exeela"进行排序确定最重的SQL，并确定两次经过时间之间的差异。


如果你得到的''cpu time''和''的值非常不同'已用时间''

然后您可能需要启用10046（8级）跟踪来识别

等待事件。


祝你好运..


Matt

我会更新数据库的统计数据，然后从那里开始。


Mike

rr * ***@goldcoast.qld.gov.au （露丝）在留言中写道：< 7d ************************ *@posting.google.c om> ...

大家好

我不是DBA，而是unix管理员。

之后我们的DBA在我们的测试服务器上从oracle 8.0.5升级到oracle 8.1.7.4，我们注意到我们的应用程序（acocobol）大幅减速。从~10分钟到~45分钟。

服务器信息：
HP Proliant ML350G3
1024MB Ram
1 x Intel Xeon 2.8GHz处理器
Unixware 7.1.1

DBA已经运行了一个报告来指示I / O问题。

总等待
等待等待
事件等待超时时间（ cs）
（ms）/ txn
---------------------------- ------- ----- ---------- -----------
------ ------
日志文件同步90,709 0 123,003
14 1.0
日志文件并行写入90,719 0 121,555
13 1.0

我们的重做日志在镜子上。

我们也尝试过设置OPTIMIZER_FEATURES_ENABLE = 8.0.0
但没有运气.....

DBA尝试升级2种不同的方式
1.升级原始数据库8.0.5到8.1.7.4
2.从头开始构建全新的数据库然后导入数据。

两种方式都有相同的性能问题。

有没有人知道为什么会在8.1.7.4上发生这种情况而不是在8.0.5上，我们如何解决这个问题。

谢谢你

露丝

如何运行一些sql跟踪一些长期运行的
流程？运行统计数据怎么样？ 8.1.7

中的优化器可能会选择与8.0.5优化器不同的计划。


Pete'的

Hi All

I am not a DBA, but a unix administrator.

After our DBA''s upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our
test server, we have noticed a big slow down in our application
(acocobol). From ~10mins to ~45mins.
Server info:
HP Proliant ML350G3
1024MB Ram
1 x Intel Xeon 2.8GHz processor
Unixware 7.1.1
The DBA''s have run a report which indicates an I/O problem.

Total Wait
wait Waits
Event Waits Timeouts Time (cs)
(ms) /txn
---------------------------- ------------ ---------- -----------
------ ------
log file sync 90,709 0 123,003
14 1.0
log file parallel write 90,719 0 121,555
13 1.0

Our redo logs are on a mirror.
We also have tried setting OPTIMIZER_FEATURES_ENABLE=8.0.0
but no luck .....
The DBA''s tried the upgrade 2 different ways
1. upgrade original database from 8.0.5 to 8.1.7.4
2. built brand new database from scratch then importing the data.

Both ways have the same performance problem.
Does anyone have any idea why this is occurring on 8.1.7.4 and not on
8.0.5, and how we can fix it.

thankyou

regards
Ruth

解决方案

> The DBA''s tried the upgrade 2 different ways

1. upgrade original database from 8.0.5 to 8.1.7.4
2. built brand new database from scratch then importing the data.

Both ways have the same performance problem.
Does anyone have any idea why this is occurring on 8.1.7.4 and not on
8.0.5, and how we can fix it.

The first thing I would do is make sure that your statistics are up to
date on your tables... If you used step 1 above this should be the
case, but confirm this anyway.

Query user_tables for last_analzed and num_rows...

If these are up to date you need to run the process under 8.0.5 with
sql_trace enabled and then compare this with the same for 8.1.7.

Use tkprof to format the trace files and sort by
"prsela,fchela,exeela" to identify the heaviest SQL and determine the
difference between the 2 elapsed times.

If you get vastly different values for ''cpu time'' and ''elapsed time''
then you may need to enable a 10046 (level 8) trace to identify the
wait events.

Good luck..

Matt

I would update the statistics on the DB and then go from there.

Mike

rr****@goldcoast.qld.gov.au (Ruth) wrote in message news:<7d*************************@posting.google.c om>...

Hi All

I am not a DBA, but a unix administrator.

After our DBA''s upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our
test server, we have noticed a big slow down in our application
(acocobol). From ~10mins to ~45mins.
Server info:
HP Proliant ML350G3
1024MB Ram
1 x Intel Xeon 2.8GHz processor
Unixware 7.1.1
The DBA''s have run a report which indicates an I/O problem.

Total Wait
wait Waits
Event Waits Timeouts Time (cs)
(ms) /txn
---------------------------- ------------ ---------- -----------
------ ------
log file sync 90,709 0 123,003
14 1.0
log file parallel write 90,719 0 121,555
13 1.0

Our redo logs are on a mirror.
We also have tried setting OPTIMIZER_FEATURES_ENABLE=8.0.0
but no luck .....
The DBA''s tried the upgrade 2 different ways
1. upgrade original database from 8.0.5 to 8.1.7.4
2. built brand new database from scratch then importing the data.

Both ways have the same performance problem.
Does anyone have any idea why this is occurring on 8.1.7.4 and not on
8.0.5, and how we can fix it.

thankyou

regards
Ruth

How about running some sql traces on some of the long running
processes? How about running statistics? The optimizer in 8.1.7
could be choosing a different plan than the 8.0.5 optimizer.

Pete''s

这篇关于oracle 8i升级后性能不佳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！