求助 oracle 吃cpu

lovewwd

第一次tunning , 不懂的地方多多，大家一定指正呀   

仓促中只统计了3小时数据，不知道前辈们能看出什么毛病来吗？
忘了说机器配置
true64 （2 cpu）
oracle 816  
2G memeory
磁盘阵列几十个G吧

贴不上来，用附件了

sundog315

虽然配置文件有些问题，但我看不出问题所在。但有些建议。
sql area                             334,866,816  342,924,352    8,057,536

三个小时只增长了不到8M，300多M都空余着。不如将这300M给db_block_buffers。
db file scattered read         10,381,957          0           0     0  276.8
db file sequential read         3,222,627          0           0     0   85.9
latch free                      1,361,632  1,358,507           0     0   36.3
前两个可能跟db_block_buffers有关吧。
latch少了。增加一个吧。

拙见拙见

songyuee

timed_statistic打开了吗？
怎么时间全是0呀

4pal

呼呼，就看到头两部分信息，已经找到了严重的问题。

最关键的是，你们的应用程序没有 用 绑定变量，系统每秒钟要进行
Hard parses:                 6.01
太恐怖了，
（而软分析，也不是少数 28.88/秒）
更要命的是，你们的shared_pool竟然有500多M

于是，重启系统之后，由于 hard parse（之前从不存在的sql），shared_pool不断的给消耗消耗，直到N天之后，shared_pool没有可用的free memory，于是数据库系统开始 flush shared pool ，而这个过程，会剧烈的折腾你们的系统，Shared_pool越大，折腾得越剧烈，造成了如今你们的现象（N天之后狂慢，重启就可以了）。
上面的分析，我还没提到在 开始的运行阶段，其实性能也遭到了极大的抑制（写这个贴子的时候，我没有继续看下去，应该library cache争用也异常剧烈），但是你们忽略了。

将shared_pool_size 绝对应该控制在 200M以内。但是这样不解决问题。根本的解决方法，就是改写sql语句，重用 绑定变量。减小了shared pool之后，你们的现象 周期反而会缩短，说不定过了1天就出现问题。
tom说了，改写，用绑定变量。否则，周期的那种现象，会永远如噩梦般缠绕在你们的系统上


第二个严重的问题：
Logical reads:            39,824.72 / 秒
每秒钟 如此多的 逻辑读，不知道你们的应用程序是怎么写的，这么多 logical reads，是不是都是你想要的？跟踪系统，找出那些logical reads剧烈的 查询语句出来。主意不必要的full table scan
上面的logical reads的单位 是  block
过多的logical reads，也导致了你们系统过多的physical reads，5,641.91/秒，14%的逻辑读导致物理I/O

第二个问题只影响你们的系统性能。

4pal

Rollback / transaction Pct:       11.45

平均每十个事务中有一个要回滚，呜呼，如此高的回滚，真是令人惊愕。回滚的代价 太巨大，要控制好你们的应用呀。
这也是只影响性能。

4pal

Library Hit   Ratio:       88.33
        Soft Parse Ratio:       79.18

这两个数据，进一步证明了系统存在太多的 硬分析

4pal

Top 5 Wait Events
~~~~~~~~~~~~~~~~~                                             Wait     % Total
Event                                               Waits  Time (cs)   Wt Time
-------------------------------------------- ------------ ------------ -------
latch free                                      1,361,632            0     .00

Wait Events for DB: EDI_DB  Instance: EDI_DB  Snaps:       1 -     18
                                                                   Avg
                                                     Total Wait   wait  Waits
Event                               Waits   Timeouts  Time (cs)   (ms)   /txn
---------------------------- ------------ ---------- ----------- ----- ------
latch free                      1,361,632  1,358,507           0     0   36.3

这两个数据，进一步说明 由于hard parse而引起的 shared pool Latch争用 是如此剧烈

4pal

至于
db file scattered read         10,381,957          0           0     0  276.8
db file sequential read         3,222,627          0           0     0   85.9
就是我之前说的 第二个问题

select file_name ,status  from van_recv_file_ctl where file_seq_no=:b0 for update

这句top 1 SQL，请想想是否有必要 for update

算了，看多好多  查询结果上千blocks甚至上万的query语句，奇怪，要这么大的结果集，不知道你们的应用是用来干啥的@_@当然，这也许不是问题，确实是你们的需求也说不定

                                      Avg Read                  Total Avg Wait
Tablespace                      Reads   (ms)        Writes      Waits   (ms)
------------------------- ----------- -------- ----------- ---------- --------
EDI_DATA_V3                13,595,316      0.0      10,723      3,345      0.0
TEMP_EDI_DB                   279,064      0.0     492,387          0      0.0

IO严重不均衡，集中在 tablespace EDI_DATA_V3  上。
而且你们的 索引表空间，相对而言，活动少得可怜，我凭空猜测，你们的应用，基本上没用到index。（你们的是DSS系统？明显不是，因为存在太多的hard parse；是OLTP？好像也不是，查询结果集都是那么的大，不符合oltp的定义）

4pal

在你统计的3个小时内，总共
Instance Activity Stats for DB: EDI_DB  Instance: EDI_DB  Snaps:       1 -

Statistic                                    Total   per Second    per Trans
--------------------------------- ---------------- ------------ ------------
parse count (hard)                          60,691          6.0          1.6
parse count (total)                        291,548         28.9          7.8


Library Cache Activity for DB: EDI_DB  Instance: EDI_DB  Snaps:       1 -
->"Pct Misses"  should be very low

                    Get      Pct       Pin      Pct                Invali-
Namespace        Requests    Miss   Requests    Miss      Reloads  dations
--------------- ----------- ------ ----------- ------ ----------- --------
SQL AREA            291,188   20.7     802,537   15.1          47        0

用掉了整整
sql area                             334,866,816  342,924,352    8,057,536
300多M呀



呵呵，俺对照着 expert one-on-one，第一次分析statspack完毕。挺好玩的

sanow

最初由 4pal 发布
[B]呼呼，就看到头两部分信息，已经找到了严重的问题。

最关键的是，你们的应用程序没有 用 绑定变量，系统每秒钟要进行
Hard parses:                 6.01
太恐怖了，
（而软分析，也不是少数 28.88/秒）
更要命的是，你们的shared_pool竟然有500多M

第二个问题只影响你们的系统性能。 [/B]

能否详细说一下怎么没有用绑定变量？