1 2 Previous Next 19 Replies Latest reply on Mar 6, 2020 2:35 AM by Beauty_and_dBest

    EBS 12.2.4  queue tablespace

    Beauty_and_dBest

      EBS 12.2.4

      11gR2

      OL6

       

       

      Hi ALL,

       

      I am wondering why our database size is growing  so fast?

      Its size a year ago was only 400Gb, but now its almost 1Tb.

      So I am checking our database size via the OS file size,  going through the dbf files under the data folder.

      And I so these queue dbf have grown sooo big, almost  180Gb?

      -rw-r-----. 1 oraprod dba 34359730176 Sep 26 12:15 a_queue01.dbf

      -rw-r-----. 1 oraprod dba 34359730176 Sep 26 12:15 a_queue02.dbf

      -rw-r-----. 1 oraprod dba 34359615488 Sep 26 12:15 a_queue03.dbf

      -rw-r-----. 1 oraprod dba 32212262912 Sep 26 12:15 a_queue04.dbf

      -rw-r-----. 1 oraprod dba 32212262912 Sep 26 12:15 a_queue06.dbf

      -rw-r-----. 1 oraprod dba 10737426432 Sep 26 12:15 a_queue07.dbf

       

      Is there a table I can periodically truncate under this queue tablespace?

       

       

      Please help....

       

      Kind regards,

      jc

        • 1. Re: EBS 12.2.4  queue tablespace
          Beauty_and_dBest

          Hi ALL,

           

          SQL> select tablespace_name from dba_tablespaces where tablespace_name like '%QUEUE%';

           

           

          TABLESPACE_NAME

          ------------------------------

          APPS_TS_QUEUES

           

           

          SQL> select file_name from dba_data_files where tablespace_name='APPS_TS_QUEUES';

           

           

          FILE_NAME

          --------------------------------------------------------------------------------

          /home/orauat/UAT/data/a_queue01.dbf

          /home/orauat/UAT/data/a_queue02.dbf

          /home/orauat/UAT/data/a_queue03.dbf

          /home/orauat/UAT/data/a_queue04.dbf

          /home/orauat/UAT/data/a_queue05.dbf

          /home/orauat/UAT/data/a_queue06.dbf

          /home/orauat/UAT/data/a_queue07.dbf

           

           

          SQL> select segment_name from dba_segments where tablespace_name='APPS_TS_QUEUES';

           

           

          What is the above command taking soo long to run? How do I tune the above query?

           

          Please help how to interprete performance issue why above query hangs forever?

           

          Enter Unix process id: 13231

          =====================================================================

          SID/Serial  : 404,7

          Foreground  : PID: 13231 - oracle@uatdb.trade.local (QMNC)

          Shadow      : PID: 13231 - oracle@uatdb.trade.local (QMNC)

          Terminal    : UNKNOWN/ UNKNOWN

          OS User     : orauat on uatdb.trade.local

          Ora User    :

          Status Flags: ACTIVE DEDICATED BACKGROUND

          Tran Active : NONE

          Login Time  : Mon 15:50:37

          Last Call   : Mon 15:50:36 -   76.3 min

          Lock/ Latch : NONE/ NONE

          Latch Spin  : NONE

          Current SQL statement:

          Previous SQL statement:

          Session Waits:

                  WAITED SHORT TIME: Streams AQ: qmn coordinator idle wait

          Locks:

          =====================================================================

          SQL>

           

           

          Kind regards,

          • 2. Re: EBS 12.2.4  queue tablespace
            Maaz Khan

            Hello Jc,

             

            As you mentioned, files a_queue01* sum up to almost 180gb. However, we need to get further details  on tablespace APPS_TS_QUEUES, like -

            1. Top 10 segments consuming high volume of data in APPS_TS_QUEUES.

            2. Are you using any CRM applications for this environment?

             

            Can you please share output for below query-

            select owner
            , segment_name
            , segment_type
            , Gb,
            TABLESPACE_NAME
            from (
            select owner
            , segment_name
            , segment_type
            , bytes / 1024 / 1024/1024 "GB",
            TABLESPACE_NAME
            from dba_segments
            where TABLESPACE_NAME like 'APPS_TS_QUEUES'
            order by bytes desc
            )
            where rownum < 11

            /

             

            Regards,

            Maaz

            • 3. Re: EBS 12.2.4  queue tablespace
              Beauty_and_dBest

              Thanks Maaz,

               

              I think we do not have CRM.

              We only supply Malls with baby products.

               

               

              I am running this query:

              select segment_name||' '||bytes/1024 from dba_segments where tablespace_name='APPS_TS_QUEUES' order by bytes desc;

               

              But its taking forever 

               

              I also see lots high CPU consumers in "top" monitor, which are all related to queue waits. It covers all the screen page of the "top" monitor:

              Enter Unix process id: 13269

              =====================================================================

              SID/Serial  : 605,3

              Foreground  : PID: 13269 - oracle@uatdb.trade.local (Q000)

              Shadow      : PID: 13269 - oracle@uatdb.trade.local (Q000)

              Terminal    : UNKNOWN/ UNKNOWN

              OS User     : orauat on uatdb.trade.local

              Ora User    :

              Status Flags: ACTIVE DEDICATED BACKGROUND

              Tran Active : NONE

              Login Time  : Mon 15:50:47

              Last Call   : Mon 15:50:47 -  100.1 min

              Lock/ Latch : NONE/ NONE

              Latch Spin  : NONE

              Current SQL statement:

               

              Previous SQL statement:

                      select 1 from sys.aq$_subscriber_table where rownum < 2 and subs

                      criber_id <> 0 and table_objno <> 0

               

              Session Waits:

                      WAITING: Streams AQ: deallocate messages from Streams Pool

               

              Previous SQL statement:

                      select metadata from kopm$  where name='DB_FDO'

               

              Session Waits:

                      WAITING: Streams AQ: qmn slave idle wait

               

               

              Kind regards,

              • 4. Re: EBS 12.2.4  queue tablespace
                Beauty_and_dBest

                I am running now your query:

                 

                [orauat@uatdb ~]$ sqlplus apps/apps

                 

                 

                SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 30 17:19:09 2019

                 

                 

                Copyright (c) 1982, 2013, Oracle.  All rights reserved.

                 

                 

                 

                 

                Connected to:

                Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

                With the Partitioning, OLAP, Data Mining and Real Application Testing options

                 

                 

                SQL> select owner

                  2  , segment_name

                  3  , segment_type

                  4  , Gb,

                  5  TABLESPACE_NAME

                  6  from (

                  7  select owner

                  8  , segment_name

                  9  , segment_type

                10  , bytes / 1024 / 1024/1024 "GB",

                11  TABLESPACE_NAME

                12  from dba_segments

                13  where TABLESPACE_NAME like 'APPS_TS_QUEUES'

                14  order by bytes desc

                15  )

                16  where rownum < 11

                17

                SQL> /

                 

                 

                And it is also in waiting status?

                What is happening to our database? 

                 

                 

                Kind regards,

                • 5. Re: EBS 12.2.4  queue tablespace
                  Maaz Khan

                  Hello Jc,

                   

                  Can you please share below details -

                  1.Is this Prod/test/uat?

                  2. parameter value for - AQ_TM_PROCESSES

                  show parameter AQ_TM_PROCESSES;

                   

                  Please check for master note for QMON to understand Advanced Queueing process -

                  Master Note for AQ Queue Monitor Process (QMON) (Doc ID 305662.1)

                   

                   

                  Also, as mentioned earlier, please let us know if you are using and CRM application modules for this environment?

                   

                  Regards,

                  Maaz

                  • 6. Re: EBS 12.2.4  queue tablespace
                    Beauty_and_dBest

                    Hi Maaz,

                     

                    We are not using CRM,

                     

                    This is UAT instance, but PROD is also behaving the same issue.

                     

                    SQL> show parameter aq_tm

                     

                     

                    NAME                                 TYPE        VALUE

                    ------------------------------------ ----------- ------------------------------

                    aq_tm_processes                      integer     1

                    SQL>

                     

                     

                    Kind regards,

                    • 7. Re: EBS 12.2.4  queue tablespace
                      Maaz Khan

                      Hello Jc,

                       

                      In a test/uat environment, can you shutdown application services and then query list of tables and top 10 tables consuming more space. We can only move forward after analyzing which tables are consuming more space.

                       

                       

                      Regards,

                      Maaz

                      • 8. Re: EBS 12.2.4  queue tablespace
                        Beauty_and_dBest

                        Thanks Maaz,

                         

                        I will try to check....brb

                        • 11. Re: EBS 12.2.4  queue tablespace
                          Beauty_and_dBest

                          Thanks Shaik and ALL

                           

                          My issue is not about filling up the queue table, but rather hi cpu usage of queues

                           

                          oraprod   1930     1 68 Jan17 ?        14-13:22:35 ora_qmnc_PROD

                          oraprod  22991     1  8 Jan18 ?        1-17:00:40 ora_q00g_PROD

                           

                          I have high cpu on all the queues  qmnc, q001 to q020  background processes.

                           

                           

                          Please help...

                           

                           

                          Kind regards

                          • 12. Re: EBS 12.2.4  queue tablespace
                            mdtaylor

                            Hi jc,

                             

                            What is aq_tm_processes set to?  Are you purging workflow?

                             

                            QMON Processes Consuming CPU Resources Heavily (Doc ID 1239743.1)

                            Workflow Java Deferred Agent Listener and Workflow Deferred Agent Listener are Generating High CPU Utilization (Doc ID 1987409.1)

                            Queue Monitor (QMON) Consumes High Cpu When Retention Is Set for a Multi Consumer Queue (Doc ID 1484963.1)

                            Procedure to Manually Coalesce All the IOTs / Indexes Associated with Advanced Queueing Tables to Maintain Enqueue / Dequeue Performance; Reduce QMON CPU Usage and Redo Generation (Doc ID 271855.1)

                             

                            Regards,

                            Michael

                            • 13. Re: EBS 12.2.4  queue tablespace
                              Beauty_and_dBest

                              Thanks Michael,

                               

                              I will check that...brb

                              • 14. Re: EBS 12.2.4  queue tablespace
                                Beauty_and_dBest

                                Hi ALL,

                                 

                                Below is the top segment users of APPS_TS_QUEUES  tablespace.

                                Why is this ASO_ORDER_FEEDBACK_T has grown so big? 176Gb?

                                Is this a seeded EBS table or a custom table?

                                Is ASO a module of EBS or a custom schema?

                                 

                                How do I tune the ASO process and Module?

                                I found similar issue here > ASO_ORDER_FEEDBACK_T

                                 

                                Please help....

                                 

                                Kind  regards,

                                jc

                                1 2 Previous Next