1 2 Previous Next 15 Replies Latest reply: Jan 21, 2013 1:56 PM by Hussein Sawwan-Oracle RSS

    EBS 11i AutoInvoice Performance Very Slow

    yxes2013
      Hi all,

      EBS 11i
      Oracle 9.2.0.6 32bit

      AutoInvoice Transfer Program has been bugging down the Company's business, a bad start for 2013. Please help for this serious problem :(

      Attached is the "STATSPACK" report for the slow performance time.
      STATSPACK report for
      
      DB Name         DB Id    Instance     Inst Num Release     Cluster Host
      ------------ ----------- ------------ -------- ----------- ------- ------------
      OAPROD        4204192100 OAPROD              1 9.2.0.6.0   NO      oel5.prod
                                                                         e.local
      
                    Snap Id     Snap Time      Sessions Curs/Sess Comment
                  --------- ------------------ -------- --------- -------------------
      Begin Snap:       551 02-Jan-13 22:00:04      123      59.1
        End Snap:       553 03-Jan-13 00:00:02       97      48.7
         Elapsed:              119.97 (mins)
      
      Cache Sizes (end)
      ~~~~~~~~~~~~~~~~~
                     Buffer Cache:       288M      Std Block Size:          8K
                 Shared Pool Size:       288M          Log Buffer:     10,240K
      
      Load Profile
      ~~~~~~~~~~~~                            Per Second       Per Transaction
                                         ---------------       ---------------
                        Redo size:             54,437.02             65,579.53
                    Logical reads:          2,053,336.52          2,473,626.15
                    Block changes:                383.39                461.86
                   Physical reads:             43,270.67             52,127.57
                  Physical writes:                 46.63                 56.18
                       User calls:                 35.83                 43.16
                           Parses:                 25.53                 30.76
                      Hard parses:                  0.18                  0.21
                            Sorts:                186.25                224.38
                           Logons:                  0.08                  0.10
                         Executes:                108.76                131.02
                     Transactions:                  0.83
      
        % Blocks changed per Read:    0.02    Recursive Call %:     85.81
       Rollback per transaction %:    3.18       Rows per Sort:      3.77
      
      Instance Efficiency Percentages (Target 100%)
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                  Buffer Nowait %:   99.99       Redo NoWait %:     99.98
                  Buffer  Hit   %:   97.90    In-memory Sort %:    100.00
                  Library Hit   %:   99.87        Soft Parse %:     99.31
               Execute to Parse %:   76.52         Latch Hit %:     99.96
      Parse CPU to Parse Elapsd %:   37.01     % Non-Parse CPU:     99.98
      
       Shared Pool Statistics        Begin   End
                                     ------  ------
                   Memory Usage %:   95.13   94.82
          % SQL with executions>1:   83.58   95.42
        % Memory for SQL w/exec>1:   79.28   92.57
      
      Top 5 Timed Events
      ~~~~~~~~~~~~~~~~~~                                                     % Total
      Event                                               Waits    Time (s) Ela Time
      -------------------------------------------- ------------ ----------- --------
      CPU time                                                       74,717    84.80
      latch free                                        181,788       8,101     9.19
      db file scattered read                         47,546,136       2,893     3.28
      db file sequential read                        23,092,208       2,006     2.28
      buffer busy waits                               1,812,920         193      .22
                -------------------------------------------------------------
      ^LWait Events for DB: OAPROD  Instance: OAPROD  Snaps: 551 -553
      -> s  - second
      -> cs - centisecond -     100th of a second
      -> ms - millisecond -    1000th of a second
      -> us - microsecond - 1000000th of a second
      -> ordered by wait time desc, waits desc (idle events last)
      
                                                                         Avg
                                                           Total Wait   wait    Waits
      Event                               Waits   Timeouts   Time (s)   (ms)     /txn
      ---------------------------- ------------ ---------- ---------- ------ --------
      latch free                        181,788    180,378      8,101     45     30.4
      db file scattered read         47,546,136          0      2,893      0  7,957.5
      db file sequential read        23,092,208          0      2,006      0  3,864.8
      buffer busy waits               1,812,920          2        193      0    303.4
      log file switch (checkpoint           124         51         69    560      0.0
      log file parallel write            21,251          0         39      2      3.6
      log file sync                       4,072          0         21      5      0.7
      control file parallel write         3,061          0         12      4      0.5
      library cache load lock                 5          2          9   1806      0.0
      log file switch completion             54          6          8    148      0.0
      PX Deq: Execute Reply               7,268          0          6      1      1.2
      PX Deq: Signal ACK                    297        103          6     21      0.0
      PX qref latch                         310        279          5     16      0.1
      PX Deq: Msg Fragment                4,301          0          4      1      0.7
      PX Deq Credit: send blkd            1,256          0          2      2      0.2
      PX Deq: Parse Reply                   267          0          2      7      0.0
      process startup                        46          0          2     39      0.0
      PX Deq: Table Q Sample                 84          0          2     22      0.0
      library cache pin                     337          0          1      4      0.1
      kksfbc child completion               116         76          1     11      0.0
      write complete waits                    2          1          1    491      0.0
      PX Deq: Join ACK                      212          0          1      4      0.0
      SQL*Net break/reset to clien        2,152          0          1      0      0.4
      enqueue                                80          0          1      7      0.0
      db file parallel read                  38          0          0     13      0.0
      SQL*Net more data to client        24,272          0          0      0      4.1
      log file single write                 160          0          0      2      0.0
      PX Deq: Table Q qref                   62          0          0      4      0.0
      control file sequential read        4,336          0          0      0      0.7
      direct path read                   45,818          0          0      0      7.7
      library cache lock                      1          0          0     76      0.0
      direct path write                  22,821          0          0      0      3.8
      LGWR wait for redo copy             5,387          0          0      0      0.9
      PX Deq Credit: need buffer            206          0          0      0      0.0
      undo segment extension             40,035     40,028          0      0      6.7
      db file parallel write              3,904          0          0      0      0.7
      db file single write                   40          0          0      0      0.0
      log file sequential read              160          0          0      0      0.0
      async disk IO                          40          0          0      0      0.0
      SQL*Net message from client       269,626          0    337,613   1252     45.1
      pipe get                            5,313      5,286     21,063   3964      0.9
      queue messages                      1,466      1,464     14,023   9566      0.2
      wakeup time manager                   239        238      6,738  28191      0.0
      PX Idle Wait                        3,475      3,042      6,026   1734      0.6
      jobq slave wait                       961        960      2,824   2939      0.2
      PX Deq: Execution Msg               9,423          1        159     17      1.6
      PX Deq: Table Q Normal              1,362          4          8      6      0.2
      SQL*Net more data from clien       12,643          0          2      0      2.1
      SQL*Net message to client         269,616          0          0      0     45.1
      ^LWait Events for DB: OAPROD  Instance: OAPROD  Snaps: 551 -553
      -> s  - second
      -> cs - centisecond -     100th of a second
      -> ms - millisecond -    1000th of a second
      -> us - microsecond - 1000000th of a second
      -> ordered by wait time desc, waits desc (idle events last)
      
                                                                         Avg
                                                           Total Wait   wait    Waits
      Event                               Waits   Timeouts   Time (s)   (ms)     /txn
      ---------------------------- ------------ ---------- ---------- ------ --------
                -------------------------------------------------------------
      ^LBackground Wait Events for DB: OAPROD  Instance: OAPROD  Snaps: 551 -553
      -> ordered by wait time desc, waits desc (idle events last)
      
                                                                         Avg
                                                           Total Wait   wait    Waits
      Event                               Waits   Timeouts   Time (s)   (ms)     /txn
      ---------------------------- ------------ ---------- ---------- ------ --------
      log file parallel write            21,251          0         39      2      3.6
      latch free                            407        353         13     32      0.1
      control file parallel write         2,981          0         12      4      0.5
      db file scattered read              3,464          0          5      1      0.6
      db file sequential read             1,756          0          2      1      0.3
      rdbms ipc reply                       137          0          2     16      0.0
      log file single write                 160          0          0      2      0.0
      enqueue                                17          0          0      6      0.0
      control file sequential read        3,346          0          0      0      0.6
      LGWR wait for redo copy             5,387          0          0      0      0.9
      db file parallel write              3,904          0          0      0      0.7
      direct path read                    1,833          0          0      0      0.3
      direct path write                   1,833          0          0      0      0.3
      log file sequential read              160          0          0      0      0.0
      buffer busy waits                      21          0          0      0      0.0
      rdbms ipc message                  41,753      9,476     33,069    792      7.0
      pmon timer                          4,438      2,164      7,021   1582      0.7
      smon timer                            522          1      6,820  13064      0.1
                -------------------------------------------------------------
      ^LSQL ordered by Gets for DB: OAPROD  Instance: OAPROD  Snaps: 551 -553
      -> End Buffer Gets Threshold:     10000
      -> Note that resources reported for PL/SQL includes the resources used by
         all SQL statements called within the PL/SQL code.  As individual SQL
         statements are also reported, it is possible and valid for the summed
         total % to exceed 100
                              
      
                                                           CPU      Elapsd
        Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
      --------------- ------------ -------------- ------ -------- --------- ----------
        5,636,117,159           28  201,289,898.5   38.1 ######## #########  835260576
      Module: RAXTRX
       INSERT INTO RA_INTERFACE_ERRORS  (INTERFACE_LINE_ID,   MESSAGE_
      TEXT,   INVALID_VALUE) SELECT  INTERFACE_LINE_ID, :b_err_msg6, '
      trx_number='||T.TRX_NUMBER||','||'customer_trx_id='||TL.CUSTOMER
      _TRX_ID FROM RA_INTERFACE_LINES_GT IL, RA_CUSTOMER_TRX_LINES TL,
       RA_CUSTOMER_TRX T WHERE  IL.REQUEST_ID = :b1 AND    IL.INTERFAC
      
          511,626,129           20   25,581,306.5    3.5  8155.90  15045.56  205460129
      Module: JDBC Thin Client
      select  TRANSACTION_BRANCH , BRANCH_ADDRESS_1 , BRANCH_ADDRESS_2
       , BRANCH_PHONES , BRANCH_FAX , BRANCH_TIN , TRX_NUMBER , TRX_NU
      MBER_DISP , TRX_DATE , TRX_DATE_DISP , BILL_TO_CUSTOMER_ID , BIL
      L_TO_CUSTOMER_NAME , SHIP_DATE_ACTUAL_DISP , SHIP_TO_NAME , SHIP
      _TO_ADDRESS1 , SHIP_TO_ADDRESS2 , SHIP_TO_ADDRESS3 , SHIP_TO_ADD
      
          114,013,000        8,544       13,344.2    0.8  2919.47   4527.54  803969757
      Module: JDBC Thin Client
      SELECT COALESCE(DELV_ADDR.CITY, DELV_ADDR.PROVINCE) FROM APPS.OE
      _ORDER_HEADERS_ALL OE_HEADERS , APPS.RA_SITE_USES_ALL DELV_SITE
      , APPS.RA_ADDRESSES_ALL DELV_ADDR WHERE TO_CHAR(ORDER_NUMBER) =
      :B1 AND DELV_SITE.SITE_USE_ID(+) = OE_HEADERS.DELIVER_TO_ORG_ID
      AND DELV_ADDR.ADDRESS_ID(+) = DELV_SITE.ADDRESS_ID
      
          113,972,897        8,550       13,330.2    0.8  2924.20   4550.22 3761220362
      Module: JDBC Thin Client
      SELECT DELV_ADDR.ADDRESS1 FROM APPS.OE_ORDER_HEADERS_ALL OE_HEAD
      ERS , APPS.RA_SITE_USES_ALL DELV_SITE , APPS.RA_ADDRESSES_ALL DE
      LV_ADDR WHERE TO_CHAR(ORDER_NUMBER) = :B1 AND DELV_SITE.SITE_USE
      _ID(+) = OE_HEADERS.DELIVER_TO_ORG_ID AND DELV_ADDR.ADDRESS_ID(+
      ) = DELV_SITE.ADDRESS_ID
      
          113,972,850        8,551       13,328.6    0.8  2919.13   4533.69 4273350236
      Module: JDBC Thin Client
      ...more... > http://www.datafilehost.com/download-60d3df1e.html

      If downloading this file...
      Uncheck [ ] Use our download manager and get recommended downloads

      Your assistance is highly appreciated.

      Thanks a lot,

      Jan
        • 1. Re: EBS 11i AutoInvoice Performance Very Slow
          yxes2013
          Is our SGA rightly configured?
          SQL> show parameter sga
          
          NAME                                 TYPE        VALUE
          ------------------------------------ ----------- ------------------------------
          lock_sga                             boolean     FALSE
          pre_page_sga                         boolean     FALSE
          sga_max_size                         big integer 1688804248
          SQL> show sga
          
          Total System Global Area 1688804248 bytes
          Fixed Size                   453528 bytes
          Variable Size            1375731712 bytes
          Database Buffers          301989888 bytes
          Redo Buffers               10629120 bytes
          • 2. Re: EBS 11i AutoInvoice Performance Very Slow
            yxes2013
            I checked our Tablespaces
                                                                   TOTAL              USED             FREE        %USED
            03-JAN-13       PORTAL                                104857600           491520        104366080        .47
            03-JAN-13       APPS_UNDOTS1                         6291456000        178061312       6113394688       2.83
            03-JAN-13       ODM                                   104857600         10158080         94699520       9.69
            03-JAN-13       OLAP                                  104857600         15728640         89128960         15
            03-JAN-13       OWAPUB                                524288000        203857920        320430080      38.88
            03-JAN-13       SYSTEM                              14183735296       8191549440       5992185856      57.75
            03-JAN-13       APPS_TS_NOLOGGING                    2842034176       1995702272        846331904      70.22
            03-JAN-13       APPS_TS_SUMMARY                      1048576000        899416064        149159936      85.78
            03-JAN-13       APPS_TS_TX_IDX                      44660948992      39719927808       4941021184      88.94
            03-JAN-13       APPS_TS_SEED                         2621440000       2356019200        265420800      89.88
            03-JAN-13       APPS_TS_TX_DATA                     91277475840      88933908480       2343567360      97.43 *
            03-JAN-13       CTXD                                  101572608         99074048          2498560      97.54 *
            03-JAN-13       APPS_TS_INTERFACE                    2145255424       2144600064           655360      99.97 *
            03-JAN-13       APPS_TS_ARCHIVE                      1495924736       1495924736                0        100 *
            03-JAN-13       APPS_TS_MEDIA                        2062811136       2062811136                0        100 *
            03-JAN-13       APPS_TS_QUEUES                      48955523072      48955523072                0        100 *
            I can not understand why some tablespaces can not autoextend? Can this be the cause of slowdown?
            • 3. Re: EBS 11i AutoInvoice Performance Very Slow
              EBSDBA
              Hi,

              OH, Feel sorry for this situation... No problems it makes you stronger as you solve the issues.


              Yes, You have to make sure that there is enough space in the last 3 tablespaces.

              Please just add new data files

                   Autoinvoice Performance Issue - Stuck On Insert Into AR_LINE_CONTS [ID 1279669.1]
                   Autoinvoice Performance Issue: Full Table Scan on GL_CODE_COMBINATIONS [ID 365872.1]

              do you see any errors in the database log file?

              Also concentrate on application patching.. I mean be with the latest patches FOR ALL the modules you are using.

              Hope you will fix this problem soon.

              Thanks
              • 4. Re: EBS 11i AutoInvoice Performance Very Slow
                yxes2013
                Could it be cause by small redo logs, which is frequently switching? :(
                Beginning log switch checkpoint up to RBA [0x21186.2.10], SCN: 0x056e.66a6d05f
                Thread 1 advanced to log sequence 135558
                  Current log# 1 seq# 135558 mem# 0: /u02/oracle/oaproddata/log01a.dbf
                  Current log# 1 seq# 135558 mem# 1: /u02/oracle/oaproddata/log01b.dbf
                Thread 1 cannot allocate new log, sequence 135559
                Checkpoint not complete
                  Current log# 1 seq# 135558 mem# 0: /u02/oracle/oaproddata/log01a.dbf
                  Current log# 1 seq# 135558 mem# 1: /u02/oracle/oaproddata/log01b.dbf
                Wed Jan  2 20:37:01 2013
                Completed checkpoint up to RBA [0x21186.2.10], SCN: 0x056e.66a6d05f
                Wed Jan  2 20:37:01 2013
                Beginning log switch checkpoint up to RBA [0x21187.2.10], SCN: 0x056e.66a6d5e4
                Thread 1 advanced to log sequence 135559
                  Current log# 2 seq# 135559 mem# 0: /u02/oracle/oaproddata/log02a.dbf
                  Current log# 2 seq# 135559 mem# 1: /u02/oracle/oaproddata/log02b.dbf
                Thread 1 cannot allocate new log, sequence 135560
                Checkpoint not complete
                  Current log# 2 seq# 135559 mem# 0: /u02/oracle/oaproddata/log02a.dbf
                  Current log# 2 seq# 135559 mem# 1: /u02/oracle/oaproddata/log02b.dbf
                Wed Jan  2 20:37:07 2013
                Completed checkpoint up to RBA [0x21187.2.10], SCN: 0x056e.66a6d5e4
                Wed Jan  2 20:37:07 2013
                Beginning log switch checkpoint up to RBA [0x21188.2.10], SCN: 0x056e.66a6e3f2
                Thread 1 advanced to log sequence 135560
                  Current log# 1 seq# 135560 mem# 0: /u02/oracle/oaproddata/log01a.dbf
                  Current log# 1 seq# 135560 mem# 1: /u02/oracle/oaproddata/log01b.dbf
                Wed Jan  2 20:37:18 2013
                Thread 1 cannot allocate new log, sequence 135561
                Checkpoint not complete
                  Current log# 1 seq# 135560 mem# 0: /u02/oracle/oaproddata/log01a.dbf
                  Current log# 1 seq# 135560 mem# 1: /u02/oracle/oaproddata/log01b.dbf
                Wed Jan  2 20:37:18 2013
                Completed checkpoint up to RBA [0x21188.2.10], SCN: 0x056e.66a6e3f2
                Wed Jan  2 20:37:18 2013
                Beginning log switch checkpoint up to RBA [0x21189.2.10], SCN: 0x056e.66a6f0a4
                Thread 1 advanced to log sequence 135561
                  Current log# 2 seq# 135561 mem# 0: /u02/oracle/oaproddata/log02a.dbf
                  Current log# 2 seq# 135561 mem# 1: /u02/oracle/oaproddata/log02b.dbf
                Wed Jan  2 20:40:14 2013
                Completed checkpoint up to RBA [0x21189.2.10], SCN: 0x056e.66a6f0a4
                Wed Jan  2 20:40:53 2013
                Beginning log switch checkpoint up to RBA [0x2118a.2.10], SCN: 0x056e.66a70cb2
                Thread 1 advanced to log sequence 135562
                  Current log# 1 seq# 135562 mem# 0: /u02/oracle/oaproddata/log01a.dbf
                  Current log# 1 seq# 135562 mem# 1: /u02/oracle/oaproddata/log01b.dbf
                Wed Jan  2 20:43:24 2013
                Completed checkpoint up to RBA [0x2118a.2.10], SCN: 0x056e.66a70cb2
                Thanks
                • 5. Re: EBS 11i AutoInvoice Performance Very Slow
                  yxes2013
                  I also see repeating ORA-1555 on the same statement in alert log, How can I avoid this?
                  Beginning log switch checkpoint up to RBA [0x211b2.2.10], SCN: 0x056e.70580afc
                  Thread 1 advanced to log sequence 135602
                    Current log# 1 seq# 135602 mem# 0: /u02/oracle/oaproddata/log01a.dbf
                    Current log# 1 seq# 135602 mem# 1: /u02/oracle/oaproddata/log01b.dbf
                  Wed Jan  2 21:26:14 2013
                  Completed checkpoint up to RBA [0x211b2.2.10], SCN: 0x056e.70580afc
                  Wed Jan  2 21:26:16 2013
                  ORA-01555 caused by SQL statement below (Query Duration=2913 sec, SCN: 0x056e.66a6f853):
                  Wed Jan  2 21:26:16 2013
                   INSERT INTO RA_INTERFACE_ERRORS
                   (INTERFACE_LINE_ID,
                    MESSAGE_TEXT,
                    INVALID_VALUE)
                  SELECT
                  INTERFACE_LINE_ID,
                  :b_err_msg6,
                  'trx_number='||T.TRX_NUMBER||','||'customer_trx_id='||TL.CUSTOMER_TRX_ID
                  FROM RA_INTERFACE_LINES_GT IL, RA_CUSTOMER_TRX_LINES TL, RA_CUSTOMER_TRX T
                  WHERE  IL.REQUEST_ID = :b1
                  AND    IL.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
                  AND    T.CUSTOMER_TRX_ID =TL.CUSTOMER_TRX_ID
                  AND  IL.INTERFACE_LINE_CONTEXT = TL.INTERFACE_LINE_CONTEXT
                  AND IL.INTERFACE_LINE_ATTRIBUTE1 = TL.INTERFACE_LINE_ATTRIBUTE1
                  AND IL.INTERFACE_LINE_ATTRIBUTE2 = TL.INTERFACE_LINE_ATTRIBUTE2
                  AND IL.INTERFACE_LINE_ATTRIBUTE3 = TL.INTERFACE_LINE_ATTRIBUTE3
                  AND IL.INTERFACE_LINE_ATTRIBUTE4 = TL.INTERFACE_LINE_ATTRIBUTE4
                  AND IL.INTERFACE_LINE_ATTRIBUTE5 = TL.INTERFACE_LINE_ATTRIBUTE5
                  AND IL.INTERFACE_LINE_ATTRIBUTE6 = TL.INTERFACE_LINE_ATTRIBUTE6
                  AND IL.INTERFACE_LINE_ATTRIBUTE7 = TL.INTERFACE_LINE_ATTRIBUTE7
                  AND IL.INTERFACE_LINE_ATTRIBUTE8 = TL.INTERFACE_LINE_ATTRIBUTE8
                  AND IL.INTERFACE_LINE_ATTRIBUTE9 = TL.INTERFACE_LINE_ATT
                  Wed Jan  2 21:26:22 2013
                  Beginning log switch checkpoint up to RBA [0x211b3.2.10], SCN: 0x056e.7062ba10
                  Thread 1 advanced to log sequence 135603
                    Current log# 2 seq# 135603 mem# 0: /u02/oracle/oaproddata/log02a.dbf
                    Current log# 2 seq# 135603 mem# 1: /u02/oracle/oaproddata/log02b.dbf
                  Wed Jan  2 21:26:22 2013
                  Thanks
                  • 6. Re: EBS 11i AutoInvoice Performance Very Slow
                    yxes2013
                    Thanks EB,
                    Hope you will fix this problem soon.
                    Can you see any problem in the statspack report? Does it really help to resolve performance issue?

                    Have you seen hussein?

                    Thanks
                    • 7. Re: EBS 11i AutoInvoice Performance Very Slow
                      yxes2013
                      SQL> show parameter undo
                      
                      NAME                                 TYPE        VALUE
                      ------------------------------------ ----------- ------------------------------
                      undo_management                      string      AUTO
                      undo_retention                       integer     1800
                      undo_suppress_errors                 boolean     FALSE
                      undo_tablespace                      string      APPS_UNDOTS1
                      Do I need to increase undo_retention? By how much?

                      Thanks
                      • 8. Re: EBS 11i AutoInvoice Performance Very Slow
                        Hussein Sawwan-Oracle
                        yxes2013 wrote:
                        I also see repeating ORA-1555 on the same statement in alert log, How can I avoid this?
                        Please fix this error before you proceed.

                        ORA-01555 When Max Query Length Is Less Than Undo Retention [ID 1131474.1]
                        ORA-01555 Using Automatic Undo Management - Causes and Solutions [ID 269814.1]
                        ORA-01555 When Using Undo Tablespace [ID 216971.1]
                        Master Note for ORA-1555 Errors [ID 1307334.1]
                        TROUBLESHOOTING GUIDE (TSG) - ORA-1555 [ID 467872.1]

                        Thanks,
                        Hussein
                        • 9. Re: EBS 11i AutoInvoice Performance Very Slow
                          Hussein Sawwan-Oracle
                          yxes2013 wrote:
                          SQL> show parameter undo
                          
                          NAME                                 TYPE        VALUE
                          ------------------------------------ ----------- ------------------------------
                          undo_management                      string      AUTO
                          undo_retention                       integer     1800
                          undo_suppress_errors                 boolean     FALSE
                          undo_tablespace                      string      APPS_UNDOTS1
                          Do I need to increase undo_retention? By how much?

                          Thanks
                          Ora-1555 And Value Of UNDO_RETENTION Parameter in Seconds [ID 822411.1]
                          Automatic Tuning of Undo_retention Causes Space Problems [ID 420525.1
                          How To Size UNDO Tablespace For Automatic Undo Management [ID 262066.1]

                          Thanks,
                          Hussein
                          • 10. Re: EBS 11i AutoInvoice Performance Very Slow
                            yxes2013
                            Hi Hussein,

                            I just noticed that our large_pool_size was not set. It is okay or being overridden by the other parameter?

                            SQL> show parameter large_pool

                            NAME TYPE VALUE
                            ------------------------------------ ----------- ------------------------------
                            large_pool_size big integer 0
                            SQL>

                            Thanks,

                            Jan
                            • 11. Re: EBS 11i AutoInvoice Performance Very Slow
                              Hussein Sawwan-Oracle
                              yxes2013 wrote:
                              Hi Hussein,

                              I just noticed that our large_pool_size was not set. It is okay or being overridden by the other parameter?

                              SQL> show parameter large_pool

                              NAME TYPE VALUE
                              ------------------------------------ ----------- ------------------------------
                              large_pool_size big integer 0
                              SQL>

                              Thanks,

                              Jan
                              LARGE_POOL_SIZE
                              http://docs.oracle.com/cd/B10501_01/server.920/a96536/ch184.htm#1018084

                              Init.ora Parameter "LARGE_POOL_SIZE" Reference Note [ID 47334.1]

                              Thanks,
                              Hussein
                              • 12. Re: EBS 11i AutoInvoice Performance Very Slow
                                yxes2013
                                Hi hussein,

                                Is this normal locks for the apps table which is "3". Im selecting the locks even if there is no users in the system.

                                SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id,
                                l.oracle_username, l.locked_mode
                                FROM dba_objects o, gv$locked_object l
                                WHERE o.object_id = l.object_id;
                                 OWNER       OBJECT_NAME                                OBJECT_TYPE       LAST_DDL  STATUS       SESSION_ID       ORACLE_USERNAME       LOCKED_MODE 
                                                                                                                            
                                 APPLSYS       FND_CONCURRENT_QUEUES               TABLE      24-APR-10      VALID      109      APPS      3
                                 APPLSYS       FND_CONCURRENT_REQUESTS               TABLE      24-APR-10      VALID      25      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_T       TABLE      24-APR-10      VALID      143      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_T       TABLE      24-APR-10      VALID      139      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_T       TABLE      24-APR-10      VALID      138      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_T       TABLE      24-APR-10      VALID      131      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_T       TABLE      24-APR-10      VALID      115      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_T       TABLE      24-APR-10      VALID      103      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_T       TABLE      24-APR-10      VALID      87      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_H       TABLE      24-APR-10      VALID      143      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_H       TABLE      24-APR-10      VALID      139      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_H       TABLE      24-APR-10      VALID      138      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_H       TABLE      24-APR-10      VALID      131      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_H       TABLE      24-APR-10      VALID      115      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_H       TABLE      24-APR-10      VALID      103      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_H       TABLE      24-APR-10      VALID      87      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_I       TABLE      24-APR-10      VALID      143      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_I       TABLE      24-APR-10      VALID      139      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_I       TABLE      24-APR-10      VALID      138      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_I       TABLE      24-APR-10      VALID      131      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_I       TABLE      24-APR-10      VALID      115      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_I       TABLE      24-APR-10      VALID      103      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_OPP_AQTBL_I       TABLE      24-APR-10      VALID      87      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_T       TABLE      24-APR-10      VALID      121      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_T       TABLE      24-APR-10      VALID      104      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_T       TABLE      24-APR-10      VALID      95      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_T       TABLE      24-APR-10      VALID      75      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_T       TABLE      24-APR-10      VALID      67      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_T       TABLE      24-APR-10      VALID      66      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_T       TABLE      24-APR-10      VALID      55      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_T       TABLE      24-APR-10      VALID      54      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_T       TABLE      24-APR-10      VALID      52      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_H       TABLE      24-APR-10      VALID      121      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_H       TABLE      24-APR-10      VALID      104      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_H       TABLE      24-APR-10      VALID      95      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_H       TABLE      24-APR-10      VALID      75      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_H       TABLE      24-APR-10      VALID      67      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_H       TABLE      24-APR-10      VALID      66      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_H       TABLE      24-APR-10      VALID      55      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_H       TABLE      24-APR-10      VALID      54      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_H       TABLE      24-APR-10      VALID      52      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_I       TABLE      24-APR-10      VALID      121      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_I       TABLE      24-APR-10      VALID      104      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_I       TABLE      24-APR-10      VALID      95      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_I       TABLE      24-APR-10      VALID      75      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_I       TABLE      24-APR-10      VALID      67      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_I       TABLE      24-APR-10      VALID      66      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_I       TABLE      24-APR-10      VALID      55      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_I       TABLE      24-APR-10      VALID      54      APPS      3
                                 APPLSYS       AQ$_FND_CP_GSM_IPC_AQTBL_I       TABLE      24-APR-10      VALID      52      APPS      3
                                Thanks,

                                Edited by: yxes2013 on 21.1.2013 10:18
                                • 13. Re: EBS 11i AutoInvoice Performance Very Slow
                                  Hussein Sawwan-Oracle
                                  Is this normal locks for the apps table which is "3". Im selecting the locks even if there is no users in the system
                                  Yes, it is normal. You can find locks as long as the application services are up and you have scheduled concurrent requests running when no one is connected.

                                  Thanks,
                                  Hussein
                                  • 14. Re: EBS 11i AutoInvoice Performance Very Slow
                                    yxes2013
                                    Thanks again,

                                    The users are complaining that the oracle apps is hanging even if few users are only using like 4 users, and sometimes even no one is using it... so weird. Can this
                                    be caused by concurrent batch scheduled jobs running background? How can I list all the concurrent jobs currently running?
                                    1 2 Previous Next