1 2 3 Previous Next 44 Replies Latest reply: Jan 8, 2013 7:59 PM by Hussein Sawwan-Oracle RSS

    Ebs 11i Performanace issue, OEL 5.6 Orac 9.2.0.6 32 bit

    yxes2013
      Hi All,

      OEL 5.6
      DB 9.2.0.6

      We have an 11i apps on one server and the database on another server, but the database is only 32 bit so it can not use full memory power of the server
      which is 12G. How do I migrate the database from 32 bit to 64bit? without touching the apps. Can I clone 32-bit to 64-bit database?


      Thanks,
      Jan

      Edited by: yxes2013 on 21.12.2012 17:40 ****ADDED DB 9.2.0.6 , OEL 5.6
        • 1. Re: EBS 11i 32-bit Database
          Kashif M
          What is the DB version and OS ?
          Meanwhile please review MOS Note:

          Can you / How to Upgrade RDBMS and Convert From 32-bit to 64-bit Binaries Directly on Linux or Windows based Intel Platforms Using the Database Upgrade Assistant (DBUA) [ID 757245.1]

          Thanks
          • 2. Re: EBS 11i 32-bit Database
            EBSDBA
            Hi,
            Can I clone 32-bit to 64-bit database?
            Yes you can do it. Please see Question 13 from
            FAQ: Cloning Oracle Applications Release 11i [ID 216664.1]


            And also See

            How to Convert a 32-bit Database to 64-bit Database on Linux? [ID 341880.1]

            Cannot Open Business Area After Upgrade of Database From 32-bit to 64-bit, Error "ORA-33272: Analytic workspace ZPB.ZPBCODE cannot be opened" [ID 352784.1]

            Thanks
            • 3. Re: EBS 11i 32-bit Database
              Hussein Sawwan-Oracle
              What is your database version? OS?

              It is possible to migrate the database fom 32-bit to 64-bit.

              Using Oracle EBS with a Split Configuration Database Tier on 11gR2 [ID 946413.1] -- (Option A) Migrate Linux 32-bit database to Linux x86-64
              Using Oracle E-Business Suite with a Split Configuration Database Tier on Oracle 11g Release 1 [ID 562983.1] -- (Option B) Migrate Linux 32-bit database to Linux x86-64
              Using Oracle Applications with a Split Configuration Database Tier on Oracle 10g Release 2 [ID 369693.1] -- (Option B) Migrate Linux 32-bit database to Linux Itanium or Linux x86-64
              Using Oracle Applications with a Split Configuration Database Tier on Oracle 9i Release 2 [ID 304489.1] -- (Option B) Migrate Linux 32-bit database to Linux Itanium or Linux x86-64

              Changing between 32-bit and 64-bit Word Sizes [ID 62290.1]
              How To Change Oracle 11g Wordsize from 32-bit to 64-bit. [ID 548978.1]

              Thanks,
              Hussein
              • 4. Re: EBS 11i 32-bit Database
                yxes2013
                Thanks everyone...


                Actually this Oracle Financial apps has 40 users but they are doing lots of reporting. Does the 32-bit 2Gb memory limit enough for this users?
                Are there companies that runs EBS with 32 database and still doing good? Or is there a notes from Oracle 32bit is still recommended?


                My other question is, based on your experienced is migrating 32-bit database to 64bit easy or complex? easy means you can do it in less than 2 hrs with less process to do, complex is when you
                do it more than 2 hrs and has lots of process and patching to do.

                Thanks again,
                Jan
                • 5. Re: EBS 11i 32-bit Database
                  EBSDBA
                  Hi Jan,
                  Thanks everyone...
                  Actually this Oracle Financial apps has 40 users but they are doing lots of reporting. Does the 32-bit 2Gb memory limit enough for this users?
                  Are there companies that runs EBS with 32 database and still doing good? Or is there a notes from Oracle 32bit is still recommended?
                  My other question is, based on your experienced is migrating 32-bit database to 64bit easy or complex? easy means you can do it in less than 2 hrs with less process to do, complex is when you 
                  do it more than 2 hrs and has lots of process and patching to do.
                  
                  Thanks again,
                  Jan
                  40 users are not many :). If reports are slow you have to find out the reason why they are running slow. On what basis/proof are you going to migrate to 64 bit operating system. The problem may still be there if we migrate to 64 bit.

                  So first find out why these reports are running slow then take the necessary action.

                  Yes we do have some customers who had 100 users running on 9i with 1GB SGA (In total) still going good.

                  Few things you would need to check are.

                  1. Are stats gathered recently?
                  2. Run statspack report.
                  3. Check if you have indexes and are in usable state.

                  Thanks
                  • 6. Re: EBS 11i 32-bit Database
                    yxes2013
                    Thanks ebsdba,

                    Sorry its is a long weekend holiday till Christmas day Dec25. We have long holidays so I can not check now.

                    Come this Dec 26, I will try to do what you advised

                    1. Are stats gathered recently?
                    2. Run statspack report.
                    3. Check if you have indexes and are in usable state.


                    For Item #3 I guess the Oracle Apps is already complete with indexes? Is there an instance that you added one on your own?
                    Is it supported by the product development group to add more indexes? Will it not affect other applications?


                    Thanks,
                    Jan
                    • 7. Re: EBS 11i 32-bit Database
                      Hussein Sawwan-Oracle
                      My other question is, based on your experienced is migrating 32-bit database to 64bit easy or complex? easy means you can do it in less than 2 hrs with less process to do, complex is when you
                      do it more than 2 hrs and has lots of process and patching to do.
                      It is not complex, but I would say it is a new experience if you have not dont it before. It would take more than 2 hours, but should not take longer than 8 hours.

                      Thanks,
                      Hussein
                      • 8. Re: EBS 11i 32-bit Database
                        Hussein Sawwan-Oracle
                        Come this Dec 26, I will try to do what you advised

                        1. Are stats gathered recently?
                        2. Run statspack report.
                        3. Check if you have indexes and are in usable state.

                        For Item #3 I guess the Oracle Apps is already complete with indexes? Is there an instance that you added one on your own?
                        Is it supported by the product development group to add more indexes? Will it not affect other applications?
                        Yes, you can create custom indexes on your custom objects but not on seeded objects as this may impact the performance. It is usually unsupported to modify the seeded objects or alter them, so if you have any issues with those seeded objects please log a SR and Oracle support should help in resolving them (i.e. might advise you to create additional indexes by developing a patch for you).

                        Thanks,
                        Hussein
                        • 9. Re: EBS 11i 32-bit Database
                          yxes2013
                          Hi hussein,

                          Note: For Oracle apps FND_STATS should be used instead DBMS_STATS....
                          I think there is no problem with the table analysis since we got it scheduled weekly, and the list shows that only SYS tables have not been analized which is okay right?
                            1* SELECT COUNT(1) FROM DBA_tables WHERE LAST_ANALYZED<SYSDATE-10
                          SQL> /
                          
                            COUNT(1)
                          ----------
                                 385
                          
                          SQL> select owner,count(*) from dba_tables WHERE LAST_ANALYZED<SYSDATE-10 group by owner;
                          
                          OWNER                            COUNT(*)
                          ------------------------------ ----------
                          BOM                                     5
                          GL                                      1
                          CUS                                    41
                          PO                                     15
                          SYS                                   322
                          ZPB                                     1
                          
                          6 rows selected.
                          I wonder why there are tables not included? like BOM(5), GL(1), CUS(41,) PO(15), ZPB(1)

                          Do I need to analyze in manually 1 by 1 using > analyze table compute statistics? Do I need to analyze thier indexes separately?

                          Thanks,
                          Jan
                          • 10. Re: EBS 11i 32-bit Database
                            Srini Chavali-Oracle
                            In EBS, statistics for some tables are never gathered for various reasons. These are documented in table FND_EXCLUDE_TABLE_STATS - if the tables you found are listed in the FND_EXCLUDE_TABLE_STATS, then you do not have to do anything special.

                            For SYS statistics, pl see this MOS Doc

                            EBPERF FAQ - Collecting Statistics in Oracle EBS 11i and R12 [ID 368252.1]

                            HTH
                            Srini
                            • 11. Re: EBS 11i 32-bit Database
                              yxes2013
                              In EBS, statistics for some tables are never gathered for various reasons. These are documented in table FND_EXCLUDE_TABLE_STATS - if the tables you found are listed in the FND_EXCLUDE_TABLE_STATS, then you do not have to do anything special.
                              Yikes...I already analyzed them :( .....will it do bad in our applications? How do I revert it back?

                              These are the tables that I manually analyzed:
                              analyze table GL.GL_OPEN_INTERIM compute statistics;
                              analyze table PO.PO_HEADERS_GT compute statistics;
                              analyze table PO.PO_RETROPRICE_GT compute statistics;
                              analyze table PO.PO_REQ_SPLIT_LINES_GT compute statistics;
                              analyze table PO.PO_LINES_GT compute statistics;
                              analyze table PO.PO_LINE_LOCATIONS_GT compute statistics;
                              analyze table PO.PO_DISTRIBUTIONS_GT compute statistics;
                              analyze table PO.PO_RELEASES_GT compute statistics;
                              analyze table PO.PO_REQ_HEADERS_GT compute statistics;
                              analyze table PO.PO_REQ_LINES_GT compute statistics;
                              analyze table PO.PO_REQ_DISTRIBUTIONS_GT compute statistics;
                              analyze table PO.PO_ONLINE_REPORT_TEXT_GT compute statistics;
                              analyze table BOM.CST_CG_LIST_TEMP compute statistics;
                              analyze table BOM.CST_INV_COST_TEMP compute statistics;
                              analyze table BOM.CST_INV_QTY_TEMP compute statistics;
                              analyze table BOM.CST_ITEM_LIST_TEMP compute statistics;
                              analyze table BOM.CST_SUB_LIST_TEMP compute statistics;
                              analyze table PO.PO_ADDRESS_DETAILS_GT compute statistics;
                              analyze table PO.PO_COMMUNICATION_GT compute statistics;
                              analyze table PO.PO_ENCUMBRANCE_GT compute statistics;
                              analyze table PO.PO_SESSION_GT compute statistics;
                              analyze table ZPB.ZPB_DC_VAL_RESULTS_GT compute statistics;
                              analyze table JSCUS.USR_AR_CUSTOMER_IPP compute statistics;
                              analyze table JSCUS.USR_AR_TRANSACTIONS compute statistics;
                              analyze table JSCUS.USR_PA_MERCHANDISE_ACT_SUPPORT compute statistics;
                              analyze table JSCUS.USR_SL_ACTIVITY_BUDGET_ALLOC compute statistics;
                              analyze table JSCUS.USR_OE_INTERFACE_STAGING compute statistics;
                              analyze table JSCUS.USR_PA_MERCH_ACT_SUP_MOD compute statistics;
                              analyze table JSCUS.USR_SL_ORDER_TRANSACTIONS compute statistics;
                              analyze table JSCUS.USR_PO_HEADERS_ALL compute statistics;
                              analyze table JSCUS.USR_SL_CUSTOMER_TRX compute statistics;
                              analyze table JSCUS.USR_PA_MERCH_ACT_SUP_MOD_BKP compute statistics;
                              analyze table JSCUS.USR_SL_SALES_PERSON compute statistics;
                              analyze table JSCUS.USR_SPES_T_SO_LINE compute statistics;
                              analyze table JSCUS.USR_IN_PICK_CONFIRMATION compute statistics;
                              analyze table JSCUS.USR_IN_UNIT_CONVERSIONS compute statistics;
                              analyze table JSCUS.USR_IN_UNIT_CONVERSIONS_OLD compute statistics;
                              analyze table JSCUS.USR_IN_PICK_CONFIRMATION_BCK compute statistics;
                              analyze table JSCUS.USR_IN_PICK_CONFIRMATION_B_ALL compute statistics;
                              analyze table JSCUS.USR_SL_SOLOMON_LKUP_ITEM_CAT compute statistics;
                              analyze table JSCUS.USR_WEB_IPPLOADER_SHEETNAME compute statistics;
                              analyze table JSCUS.USR_SL_SALES_OFFTAKE_TBLE compute statistics;
                              analyze table JSCUS.USR_SL_SOLOMON_LKUP_TERRITORY compute statistics;
                              analyze table JSCUS.USR_RA_INTERFACE_LINES_ALL compute statistics;
                              analyze table JSCUS.USR_SL_SOLOMON_LKUP_CUST_REF compute statistics;
                              analyze table JSCUS.USR_SL_SALES_OFF_TAKE_TBL compute statistics;
                              analyze table JSCUS.USR_SL_ORACLE_SALES_OFF_TAKE compute statistics;
                              analyze table JSCUS.USR_SL_MDC_MAPPERS compute statistics;
                              analyze table JSCUS.USR_WEB_ROLES compute statistics;
                              analyze table JSCUS.USR_WEB_USER_ROLES compute statistics;
                              analyze table JSCUS.USR_WEB_USERS compute statistics;
                              analyze table JSCUS.USR_WEB_DELIVERY_MONITORING compute statistics;
                              analyze table JSCUS.USR_WEB_DEPARTMENTS compute statistics;
                              analyze table JSCUS.USR_WEB_USER_DEPTS compute statistics;
                              analyze table JSCUS.USR_SL_MDC_MAPPERS_ORACLE_TRGT compute statistics;
                              analyze table JSCUS.USR_IN_PICK_CONFIMATION_B_AL compute statistics;
                              analyze table JSCUS.USR_WEB_MODULES compute statistics;
                              analyze table JSCUS.USR_SL_MDC_MAPPERS_ORACLE compute statistics;
                              analyze table JSCUS.USR_WEB_DELIVERY_MONITORING_B compute statistics;
                              analyze table JSCUS.BAK_RCV_TRANSACTIONS_INTERFACE compute statistics;
                              analyze table JSCUS.USR_WEB_USER_LOGS compute statistics;
                              analyze table JSCUS.USR_WEB_API_LOG compute statistics;
                              analyze table JSCUS.BAK_MTL_TRANSACTIONS_INTERFACE compute statistics;
                              Thanks
                              • 12. Re: EBS 11i 32-bit Database
                                Hussein Sawwan-Oracle
                                I think there is no problem with the table analysis since we got it scheduled weekly, and the list shows that only SYS tables have not been analized which is okay right?
                                This should be OK.
                                I wonder why there are tables not included? like BOM(5), GL(1), CUS(41,) PO(15), ZPB(1)

                                Do I need to analyze in manually 1 by 1 using > analyze table compute statistics? Do I need to analyze thier indexes separately?
                                You can analyze them manually but if those schemas/tables are not related to the issue you have then you can skip them.

                                Thanks,
                                Hussein
                                • 13. Re: EBS 11i 32-bit Database
                                  Hussein Sawwan-Oracle
                                  Yikes...I already analyzed them :( .....will it do bad in our applications? How do I revert it back?
                                  There is nothing wrong in what you did and you do not have to revert back.

                                  Thanks,
                                  Hussein
                                  • 14. Re: EBS 11i 32-bit Database
                                    yxes2013
                                    Thanks hussein,

                                    The users told me that they encounter slowness in the performance when the do the "transfer" process. I do not know what program is being run in this process. Can this be caught in the
                                    Statspack run?
                                    1 2 3 Previous Next