3 Replies Latest reply: Sep 10, 2012 9:29 AM by kuljeet singh - RSS

    Application R12 slow down

    User470870-OC
      Hi
      datatbas version : 11.0.1.7
      Applications 12.1.3
      can any one tel us what is happening and what can be done as it is happening in PROD instance

      Applications slow down when log switch occurs daily exactly at particular time.
      Today it took around 1 hour.

      Alter log file info when issue occured.
      [oraprod@prod trace]$ top
      top - 11:26:54 up 2 days, 19:56, 3 users, load average: 9.74, 9.44, 7.34
      Tasks: 530 total, 1 running, 529 sleeping, 0 stopped, 0 zombie
      Cpu(s): 0.3%us, 0.2%sy, 0.0%ni, 98.8%id, 0.7%wa, 0.0%hi, 0.0%si, 0.0%st
      Mem: 15972352k total, 15948424k used, 23928k free, 308584k buffers
      Swap: 8193108k total, 119924k used, 8073184k free, 12644764k cached
      PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
      3209 oraprod 15 0 13000 1336 724 R 1.9 0.0 0:00.02 top
      9945 oraprod 16 0 1235m 50m 47m D 1.9 0.3 0:04.07 oracle
      11372 oraprod 15 0 1242m 343m 327m S 1.9 2.2 0:04.99 oracle
      1 root 15 0 10348 684 576 S 0.0 0.0 0:01.28 init

      [oraprod@prod trace]$ ps -ef |grep 9945
      oraprod 3220 2023 0 11:27 pts/2 00:00:00 grep 9945
      oraprod 9945 1 0 Sep09 ? 00:00:04 ora_lgwr_OBAPROD
      [oraprod@prod trace]$ ps -ef |grep 11372
      oraprod 3223 2023 0 11:27 pts/2 00:00:00 grep 11372
      oraprod 11372 1 0 Sep09 ? 00:00:05 oracleOBAPROD (LOCAL=NO)

      Alert log details:
      Mon Sep 10 11:04:35 2012
      Beginning log switch checkpoint up to RBA [0x1ac.2.10], SCN: 5965243578741
      Thread 1 advanced to log sequence 428 (LGWR switch)
      Current log# 5 seq# 428 mem# 0: /u02/OBAPROD/db/apps_st/data/log05a.dbf
      Current log# 5 seq# 428 mem# 1: /u02/OBAPROD/db/apps_st/data/log05b.dbf
      Mon Sep 10 11:05:21 2012
      Completed checkpoint up to RBA [0x1ac.2.10], SCN: 5965243578741
      Mon Sep 10 11:08:17 2012
      Beginning log switch checkpoint up to RBA [0x1ad.2.10], SCN: 5965243623271
      Thread 1 advanced to log sequence 429 (LGWR switch)
      Current log# 2 seq# 429 mem# 0: /u02/OBAPROD/db/apps_st/data/log02a.dbf
      Current log# 2 seq# 429 mem# 1: /u02/OBAPROD/db/apps_st/data/log02b.dbf
      Mon Sep 10 11:10:51 2012
      Completed checkpoint up to RBA [0x1ad.2.10], SCN: 5965243623271
      Mon Sep 10 11:18:25 2012
      Thread 1 cannot allocate new log, sequence 430
      Private strand flush not complete
      Current log# 2 seq# 429 mem# 0: /u02/OBAPROD/db/apps_st/data/log02a.dbf
      Current log# 2 seq# 429 mem# 1: /u02/OBAPROD/db/apps_st/data/log02b.dbf
      Mon Sep 10 11:21:16 2012
      Beginning log switch checkpoint up to RBA [0x1ae.2.10], SCN: 5965243668907
      Thread 1 advanced to log sequence 430 (LGWR switch)
      Current log# 1 seq# 430 mem# 0: /u02/OBAPROD/db/apps_st/data/log01a.dbf
      Current log# 1 seq# 430 mem# 1: /u02/OBAPROD/db/apps_st/data/log01b.dbf
      Mon Sep 10 11:25:39 2012
      Completed checkpoint up to RBA [0x1ae.2.10], SCN: 5965243668907
      Mon Sep 10 11:32:15 2012
      Thread 1 cannot allocate new log, sequence 431
      Private strand flush not complete
      Current log# 1 seq# 430 mem# 0: /u02/OBAPROD/db/apps_st/data/log01a.dbf
      Current log# 1 seq# 430 mem# 1: /u02/OBAPROD/db/apps_st/data/log01b.dbf
      Mon Sep 10 11:33:46 2012
      Beginning log switch checkpoint up to RBA [0x1af.2.10], SCN: 5965245396278
      Thread 1 advanced to log sequence 431 (LGWR switch)
      Current log# 6 seq# 431 mem# 0: /u02/OBAPROD/db/apps_st/data/log06a.dbf
      Current log# 6 seq# 431 mem# 1: /u02/OBAPROD/db/apps_st/data/log06b.dbf
      Mon Sep 10 11:38:10 2012
      Completed checkpoint up to RBA [0x1af.2.10], SCN: 5965245396278
      Mon Sep 10 11:43:16 2012
      Beginning log switch checkpoint up to RBA [0x1b0.2.10], SCN: 5965245420329
      Thread 1 advanced to log sequence 432 (LGWR switch)
      Current log# 7 seq# 432 mem# 0: /u02/OBAPROD/db/apps_st/data/log07a.dbf
      Current log# 7 seq# 432 mem# 1: /u02/OBAPROD/db/apps_st/data/log07b.dbf
      Mon Sep 10 11:48:34 2012
      Completed checkpoint up to RBA [0x1b0.2.10], SCN: 5965245420329

      We have a logfile size of 500MB,
      Fast_start_mttr_target is 30 sec, archive log mode enabled and also db_writer_processes=10.
      Please let us know what is causing the problem.

      Thanks....

      Edited by: 955685 on Sep 9, 2012 11:16 PM
        • 1. Re: Application R12 slow down
          kuljeet singh -
          whats the size of redo log... logswitch happening very frequent in above log...4 logswitch in 6 mins.make a plan to increase redolog size.
          Cpu(s): 0.3%us, 0.2%sy, 0.0%ni, 98.8%id, 0.7%wa, 0.0%hi, 0.0%si, 0.0%st
          mostly idle cpu.
          can any one tel us what is happening and what can be done as it is happening in PROD instance
          you have to generate AWR /statspack report to get more detail on this.
          • 2. Re: Application R12 slow down
            User470870-OC
            Hi

            Redolog file size is 500MB and this type of logswitch occurs at particular time(around 10:30 AM to 11:30 AM) cause db performe very slow. After i dont see anyhing in alertlog even logswitch will not happen at all and instance will be normal.

            And also what do you mean cpu idle ..? What must be done..?

            Thanks.

            Edited by: 955685 on Sep 10, 2012 4:42 AM
            • 3. Re: Application R12 slow down
              kuljeet singh -
              And also what do you mean cpu idle ..? What must be done..?
              means its free ,doesn't have much work to process.
              Redolog file size is 500MB and this type of logswitch occurs at particular time(around 10:30 AM to 11:30 AM) cause db performe very slow.
              generate ASH report to get detail on this, may be some batch job are generating huge archivelog due to DML activity.