This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 21, 2013 11:56 AM by Hussein Sawwan-Oracle RSS

EBS 11i AutoInvoice Performance Very Slow

yxes2013 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points