R12.1.2升级到R12.1.3打最大的patch时编译无效对象奇慢

wing hong · 发表于 2012-4-16 12:06

chen_7733 发表于 2012-4-16 10:26
经验:还是在一个节点上跑快一些,虽然还是不太明白rac的内部机制.

that's fine. if any question, just post it here.

The point is :
if any impact due to RAC, we should be able to see it from the AWR report.

tolywang · 发表于 2012-4-16 12:25

wing hong 发表于 2012-4-16 09:22
maybe u can post the first page of awr report for the production dry run ?

WORKLOAD REPOSITORY report for

DB Name       DB Id Instance    Inst Num Startup Time Release    RAC
------------ ----------- ------------ -------- --------------- ----------- ---
UAT          3148051840 uat1             1 15-Apr-12 11:12 11.2.0.2.0  NO
Host Name       Platform                      CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
szxitr05-ts    AIX-Based Systems (64-bit)       8    2             32.00
            Snap Id    Snap Time    Sessions Curs/Sess
         --------- ------------------- -------- ---------
Begin Snap:    17038 15-Apr-12 12:00:17       79    5.2
  End Snap:    17044 15-Apr-12 18:00:38       72    1.8
Elapsed:             360.34 (mins)
DB Time:             724.12 (mins)
Cache Sizes                      Begin       End
~~~~~~~~~~~                ---------- ----------
            Buffer Cache:    2,336M    2,336M  Std Block Size:       8K
         Shared Pool Size:    7,168M    7,168M    Log Buffer: 16,536K
Load Profile             Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~       --------------- --------------- ---------- ----------
   DB Time(s):             2.0             0.1    0.00    0.02
   DB CPU(s):             0.5             0.0    0.00    0.00
   Redo size:       953,153.7          67,541.5
Logical reads:          12,277.1             870.0
Block changes:          6,214.1             440.3
  Physical reads:             168.7             12.0
Physical writes:             112.2             8.0
   User calls:             116.2             8.2
      Parses:             287.1             20.3
   Hard parses:             11.3             0.8
W/A MB processed:             5.7             0.4
      Logons:             0.3             0.0
      Executes:             773.1             54.8
   Rollbacks:             0.0             0.0
Transactions:             14.1

tolywang · 发表于 2012-4-16 12:26

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         Buffer Nowait %: 99.86    Redo NoWait %:  100.00
         Buffer  Hit %: 98.93 In-memory Sort %:  100.00
         Library Hit %: 96.66       Soft Parse %: 96.06
      Execute to Parse %: 62.86       Latch Hit %: 99.64
Parse CPU to Parse Elapsd %: 15.06    % Non-Parse CPU: 91.34

Shared Pool Statistics       Begin End
                           ------  ------
         Memory Usage %: 12.57 67.21
% SQL with executions>1: 49.75 96.88
  % Memory for SQL w/exec>1: 46.06 89.56

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                         Avg
                                                      wait % DB
Event                               Waits    Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                        10,906       25.1
db file sequential read          2,558,504    8,477    3 19.5 User I/O
buffer busy waits                270,932    2,290    8 5.3 Concurrenc
enq: UL - contention                   388    2,198 5666 5.1 Applicatio
library cache lock                   264       344 1302    .8 Concurrenc

tolywang · 发表于 2012-4-16 12:28

Host CPU (CPUs: 8 Cores: 2 Sockets: )
~~~~~~~~       Load Average
            Begin    End    %User %System    %WIO    %Idle
         --------- --------- --------- --------- --------- ---------
            3.33    11.05    16.4    4.1    3.0    79.5

Instance CPU
~~~~~~~~~~~~
            % of total CPU for Instance:    6.5
            % of busy  CPU for Instance:    31.8
  %DB time waiting for CPU - Resource Mgr:    0.0

Memory Statistics
~~~~~~~~~~~~~~~~~                      Begin       End
               Host Mem (MB):    32,768.0    32,768.0
               SGA use (MB):    10,240.0    10,240.0
               PGA use (MB):       523.4       871.4
% Host Mem used for SGA+PGA:       32.85       33.91

wing hong · 发表于 2012-4-16 12:57

please format the output so that easier for people to read .

As per your case, maybe try this:

1. increase the SGA to 15G with db_cache_size = 8 GB

2. try to use RAC and compare the result with /without rac .

tolywang · 发表于 2012-4-16 13:09

wing hong 发表于 2012-4-16 12:57
please format the output so that easier for people to read .

As per your case, maybe try this:

好的，今天会回退数据库及apps 再进行一次测试。

归档或非归档模式对于 compile invalid object 应该影响不大吧？

wing hong · 发表于 2012-4-16 13:22

tolywang 发表于 2012-4-16 13:09
好的，今天会回退数据库及apps 再进行一次测试。

can't see much impact of it in the report

tolywang · 发表于 2012-4-17 09:57

本帖最后由 tolywang 于 2012-4-17 09:58 编辑

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 10,906 25.1
db file sequential read 2,558,504 8,477 3 19.5 User I/O
buffer busy waits 270,932 2,290 8 5.3 Concurrenc
enq: UL - contention 388 2,198 5666 5.1 Applicatio
library cache lock 264 344 1302 .8 Concurrenc

复制代码

是否可以根据上面的DB CPU 后的两个等待事件判断可能是IO问题。  昨晚flashback数据库后，又重新做了一次
演练，演练前请OS管理员调整了一个 DB 服务器 AIX 的I/O参数，数据库改为2个节点都开启。执行最大的patch,
编译无效对象快了很多，不过又遇到了个新问题，某个 worker 到一定程度的时候停滞， rerun 后就可以了。

具体调整了哪个参数，听说是什么 AMS，不知道是不是存储上的IO参数，  待会再问问

gmkratos · 发表于 2012-4-17 13:39

tolywang 发表于 2012-4-16 11:55
查看了一下，还有一个重大区别。

原来的主机都是测试的，升级时DB RAC 是非归档模式。

归档模式应该没有啥影响的

等待你的结果

cod4 · 发表于 2012-4-17 21:19

tolywang 发表于 2012-4-16 08:47
worker 数量都是 16

4core 的话，worker最好是8吧？

[系统安装/维护] R12.1.2升级到R12.1.3打最大的patch时编译无效对象奇慢