9 Replies Latest reply on Apr 19, 2017 1:14 AM by mdtaylor

    EBS daily transaction count

    Beauty_and_dBest

      Hi ALL,

       

      EBS 11i and 12

       

       

      Is there a script to get our daily ebs module transactions count?

      How to get Volume of transaction per module by extracting  from the database?

       

       

      Please help....

       

       

      Kind regards,

      jc

        • 1. Re: EBS daily transaction count
          mdtaylor

          Hi jc,

           

          You would have to define what a transaction is.  Is it on a header line or the transaction lines.  Does a single order with 200 lines count as one transaction or 200 transactions.  Once you define a transaction, then you can group by day for major OM/AP/AR/PO transactions for the modules you care about.

           

          Regards,

          Michael

          • 2. Re: EBS daily transaction count
            Srini Chavali-Oracle

            What is the business or technical reason for extracting this info ?

            • 3. Re: EBS daily transaction count
              Beauty_and_dBest

              Hi All,

               

              The client needs to know how much data growth they have every day, or month, or  every year for input on disk storage capacity planning and backup plan and data purging plan?

              Or is it negligible in EBS the transaction data?

               

               

              Kind regards,

              • 4. Re: EBS daily transaction count
                Kanda-Oracle

                1. At regular interval check dba_segments to monitor the growth..

                 

                SEGMENT_NAME , BLOCKS ,bytes from dba_segments

                 

                2. Identify big tables from step1. Please check Note 752322.1

                 

                  This has lots of tables that you can explore whether such tables are required to be "purged" and de-fragmented subsequently.

                 

                Thanks


                N Kandasamy

                • 5. Re: EBS daily transaction count
                  mdtaylor

                  Hi jc,

                   

                  You can use the following custom process to monitor database growth over time:

                   

                  Database size history, Oracle Apps DBA Kaparelis

                   

                  You can also see which tables are using the most space with the following:

                   

                  select owner, DECODE(instr(segment_name,'_N'),0,segment_name,substr(segment_name,1,instr(segment_name,'_N')-1)) object, sum(bytes)/1024/1024 from dba_segments

                  where tablespace_name = 'APPS_TS_TX_DATA' --and owner = 'MSC'

                  group by owner, DECODE(instr(segment_name,'_N'),0,segment_name,substr(segment_name,1,instr(segment_name,'_N')-1)) order by 3 desc;

                   

                  Usually the largest segments and schemas are XLA and you cannot really purge those records if you still want to complete purchase accrual reconciliations successfully.

                   

                  Regards,

                  Michael

                  • 6. Re: EBS daily transaction count
                    Beauty_and_dBest

                    Thanks ALL,

                     

                     

                    I just thought Oracle is able to handle big or large data/database?

                    Why do I need to  purge if I need all the data in 5 years. Does it really degrade performance to have lots of data? even if you have indexes?

                     

                    Is it mandatory to purge historical data? and reload it back when you need it? It this the "Best Practice"? It seems a waste of energy and resources

                     

                     

                    Kind regards,

                    • 7. Re: EBS daily transaction count
                      mdtaylor

                      Hi jc,

                       

                      Oracle EBS can definitely handle large volumes.  I have seen 20TB EBS systems.  In general, you can keep most transactional data indefinitely.  There are a few exceptions including concurrent requests, workflow runtime data, msc_errors etc.

                       

                      Make sure production does not have debug profiles set and keep concurrent requests and workflow tables from blowing up and you should be ok.

                       

                      Regards,

                      Michael

                      • 8. Re: EBS daily transaction count
                        Beauty_and_dBest

                        Thanks Michael and ALL,

                         

                        Can you shares scripts to check the following?

                         

                        1. How to check if debug profiles where turned on for all the users or modules?

                        2. How to check if concurrent requests are blowing up?

                        3. How to check if workflow tables are blowing up?

                         

                         

                        Kindest regards,

                        • 9. Re: EBS daily transaction count
                          mdtaylor

                          1. select  p.user_profile_option_name  profile

                          , '1 - Site' Scope

                          , 'Site' Value_scope

                          , v.level_value

                          , v.profile_option_value v_profile

                          , u.user_name updated_by

                          from fnd_profile_option_values v

                          , fnd_profile_options_vl p

                          , fnd_user u

                          where v.profile_option_id = p.profile_option_id

                          and (v.level_id = 10001)

                          and UPPER(p.user_profile_option_name) like '%DEB%'

                          and v.last_updated_by = u.user_id

                          Union

                          select  p.user_profile_option_name

                          , '2 - Appli.'

                          , a.application_short_name

                          , v.level_value

                          , v.profile_option_value

                          , u.user_name updated_by

                          from fnd_profile_option_values v

                          , fnd_profile_options_vl p

                          , fnd_application a

                          , fnd_user u

                          where v.profile_option_id = p.profile_option_id

                          and (v.level_id = 10002 and a.application_id = v.level_value)

                          and UPPER(p.user_profile_option_name) like '%DEB%'

                          and v.last_updated_by = u.user_id

                          Union

                          select p.user_profile_option_name

                          , '3 - Respon.'

                          , r.responsibility_name

                          , v.level_value

                          , v.profile_option_value

                          , u.user_name updated_by

                          from fnd_profile_option_values v

                          , fnd_profile_options_vl p

                          , fnd_responsibility_vl r

                          , fnd_user u

                          where v.profile_option_id = p.profile_option_id

                          and (v.level_id = 10003 and r.responsibility_id = v.level_value)

                          and UPPER(p.user_profile_option_name) like '%DEB%'

                          and v.last_updated_by = u.user_id

                          Union

                          select p.user_profile_option_name

                          , '3 - User'

                          , u.user_name

                          , v.level_value

                          , v.profile_option_value

                          , u.user_name updated_by

                          from fnd_profile_option_values v

                          , fnd_profile_options_vl p

                          , fnd_user u

                          where v.profile_option_id = p.profile_option_id

                          and (v.level_id = 10004 and u.user_id = v.level_value)

                          and UPPER(p.user_profile_option_name) like '%DEB%'

                          and v.last_updated_by = u.user_id

                          union

                          select  p.user_profile_option_name  profile

                          , '1 - Site' Scope

                          , 'Site' Value_scope

                          , v.level_value

                          , v.profile_option_value v_profile

                          , u.user_name updated_by

                          from fnd_profile_option_values v

                          , fnd_profile_options_vl p

                          , fnd_user u

                          where v.profile_option_id = p.profile_option_id

                          and (v.level_id = 10001)

                          and UPPER(p.user_profile_option_name) like '%TRACE%'

                          and v.last_updated_by = u.user_id

                          Union

                          select  p.user_profile_option_name

                          , '2 - Appli.'

                          , a.application_short_name

                          , v.level_value

                          , v.profile_option_value

                          , u.user_name updated_by

                          from fnd_profile_option_values v

                          , fnd_profile_options_vl p

                          , fnd_application a

                          , fnd_user u

                          where v.profile_option_id = p.profile_option_id

                          and (v.level_id = 10002 and a.application_id = v.level_value)

                          and UPPER(p.user_profile_option_name) like '%TRACE%'

                          and v.last_updated_by = u.user_id

                          Union

                          select p.user_profile_option_name

                          , '3 - Respon.'

                          , r.responsibility_name

                          , v.level_value

                          , v.profile_option_value

                          , u.user_name updated_by

                          from fnd_profile_option_values v

                          , fnd_profile_options_vl p

                          , fnd_responsibility_vl r

                          , fnd_user u

                          where v.profile_option_id = p.profile_option_id

                          and (v.level_id = 10003 and r.responsibility_id = v.level_value)

                          and UPPER(p.user_profile_option_name) like '%TRACE%'

                          and v.last_updated_by = u.user_id

                          Union

                          select p.user_profile_option_name

                          , '3 - User'

                          , u.user_name

                          , v.level_value

                          , v.profile_option_value

                          , u.user_name updated_by

                          from fnd_profile_option_values v

                          , fnd_profile_options_vl p

                          , fnd_user u

                          where v.profile_option_id = p.profile_option_id

                          and (v.level_id = 10004 and u.user_id = v.level_value)

                          and UPPER(p.user_profile_option_name) like '%TRACE%'

                          and v.last_updated_by = u.user_id

                          union

                          select  p.user_profile_option_name  profile

                          , '1 - Site' Scope

                          , 'Site' Value_scope

                          , v.level_value

                          , v.profile_option_value v_profile

                          , u.user_name updated_by

                          from fnd_profile_option_values v

                          , fnd_profile_options_vl p

                          , fnd_user u

                          where v.profile_option_id = p.profile_option_id

                          and (v.level_id = 10001)

                          and UPPER(p.user_profile_option_name) like 'INIT%'

                          and v.last_updated_by = u.user_id

                          Union

                          select  p.user_profile_option_name

                          , '2 - Appli.'

                          , a.application_short_name

                          , v.level_value

                          , v.profile_option_value

                          , u.user_name updated_by

                          from fnd_profile_option_values v

                          , fnd_profile_options_vl p

                          , fnd_application a

                          , fnd_user u

                          where v.profile_option_id = p.profile_option_id

                          and (v.level_id = 10002 and a.application_id = v.level_value)

                          and UPPER(p.user_profile_option_name) like 'INIT%'

                          and v.last_updated_by = u.user_id

                          Union

                          select p.user_profile_option_name

                          , '3 - Respon.'

                          , r.responsibility_name

                          , v.level_value

                          , v.profile_option_value

                          , u.user_name updated_by

                          from fnd_profile_option_values v

                          , fnd_profile_options_vl p

                          , fnd_responsibility_vl r

                          , fnd_user u

                          where v.profile_option_id = p.profile_option_id

                          and (v.level_id = 10003 and r.responsibility_id = v.level_value)

                          and UPPER(p.user_profile_option_name) like 'INIT%'

                          and v.last_updated_by = u.user_id

                          Union

                          select p.user_profile_option_name

                          , '3 - User'

                          , u.user_name

                          , v.level_value

                          , v.profile_option_value

                          , u.user_name updated_by

                          from fnd_profile_option_values v

                          , fnd_profile_options_vl p

                          , fnd_user u

                          where v.profile_option_id = p.profile_option_id

                          and (v.level_id = 10004 and u.user_id = v.level_value)

                          and UPPER(p.user_profile_option_name) like 'INIT%'

                          and v.last_updated_by = u.user_id

                          order by 1,2,3

                          /

                           

                          2. select count(*) from fnd_concurrent_requests;

                           

                          Should be less than 500k rows, 50k-100k is likely ideal.  Ensure Purge Concurrent Request and/or Manager Data is scheduled.

                           

                          3. Workflow: Oracle is not Magic, it just takes years of experience: Handy script to find out eligible workflow data for purging

                           

                          Regards,

                          Michael