12 Replies Latest reply: Mar 26, 2011 3:26 PM by SK REDDY RSS

PSAPSR3 Tablespace is only growing very fast in PROD

John sat
Currently Being Moderated

Dear All,

 

In our Prod Server  -> PSAPSR3 Tablespace is only growing very fast (Note : with 5 days i have extened 2 time PSAPSR3 table space) .

 

let me know the permament solution is only extending table space ? or any alternate solution to control specific table space growth ?

 

pls check DB02 Table space details :

 

PSAPSR3     219,640.00     10,010.81     95     YES     220,000.00     10,370.81     95     22     157,305     226,884     ONLINE     PERMANENT

PSAPSR3700     71,120.00     3,506.75     95     YES     170,000.00     102,386.75     40     17     868     11,389     ONLINE     PERMANENT

PSAPSR3USR     20.00     1.94     90     YES     10,000.00     9,981.94     0     1     38     108     ONLINE     PERMANENT

PSAPTEMP     4,260.00     4,260.00     0     YES     10,000.00     10,000.00     0     1     0     0     ONLINE     TEMPORARY

PSAPUNDO     10,000.00     8,391.44     16     NO     10,000.00     8,391.44     16     1     20     498     ONLINE     UNDO

SYSAUX     480.00     22.88     95     YES     10,000.00     9,542.88     5     1     991     2,633     ONLINE     PERMANENT

SYSTEM     880.00     5.44     99     YES     10,000.00     9,125.44     9     1     1,212     2,835     ONLINE     PERMANENT

 

 

Kindly advise

  • Re: PSAPSR3 Tablespace is only growing very fast in PROD
    Sunil Bujade
    Currently Being Moderated

    Check the highest growing tables in DB02 -> Segments -> Overview -> Top growth.

    Is there any data upload going on?

    Have you scheduled the SAP standard jobs to reorg your dumps, spools, job logs etc...

    How much growth is last two weeks?

    • Re: PSAPSR3 Tablespace is only growing very fast in PROD
      John sat
      Currently Being Moderated

      Dear Sunil,

       

      pls check the below highest growing tables in DB02 -> Segments -> Overview -> Top growth (week)

       

      SAPSR3     BALDAT          TABLE     PSAPSR3     38,142.000     1,469.240     783     4,882,176     0.000

      SAPSR3     CDCLS          TABLE     PSAPSR3     9,027.000     345.080     326     1,155,456     0.000

      SAPSR3     BDCP          TABLE     PSAPSR3     4,551.000     181.200     255     582,528     0.000

      SAPSR3     BALDAT~0          INDEX     PSAPSR3     4,537.000     173.800     258     580,736     0.000

      SAPSR3     BDCPS~1          INDEX     PSAPSR3     4,039.000     161.240     247     516,992     0.000

      SAPSR3     BDCP~POS          INDEX     PSAPSR3     3,392.000     135.280     236     434,176     0.000

      SAPSR3     EDI40          TABLE     PSAPSR3     3,091.000     122.840     232     395,648     0.000

      SAPSR3     BDCP~1          INDEX     PSAPSR3     2,887.000     115.080     229     369,536     0.000

      SAPSR3     MARC          TABLE     PSAPSR3     2,951.000     113.560     230     377,728     0.000

      SAPSR3     BDCPS~0          INDEX     PSAPSR3     2,496.000     99.520     222     319,488     0.000

      SAPSR3     BDCP2          TABLE     PSAPSR3     2,373.000     94.912     221     303,744     0.000

      SAPSR3     CDHDR          TABLE     PSAPSR3     2,305.000     86.760     220     295,040     0.000

      SAPSR3     CDHDR~0          INDEX     PSAPSR3     2,119.000     79.640     217     271,232     0.000

      SAPSR3     BDCP~0          INDEX     PSAPSR3     1,863.000     74.320     213     238,464     0.000

      SAPSR3     CDCLS~0          INDEX     PSAPSR3     1,965.000     73.480     214     251,520     0.000

      SAPSR3     MBEW          TABLE     PSAPSR3     1,797.000     69.320     212     230,016     0.000

      SAPSR3     KONP          TABLE     PSAPSR3     1,728.000     69.080     210     221,184     0.000

      SAPSR3     BDCP2~001          INDEX     PSAPSR3     1,543.000     61.715     208     197,504     0.000

      SAPSR3     RFBLG          TABLE     PSAPSR3     3,200.000     58.880     233     409,600     0.000

      SAPSR3     KONH          TABLE     PSAPSR3     1,309.000     52.335     206     167,552     0.000

       

      Kindly advise

      • Re: PSAPSR3 Tablespace is only growing very fast in PROD
        Michael Hofmänner
        Currently Being Moderated

        There is a bunch of tables here that shouldn't be there. You must consider deleting older data.

         

        For example:

        BALDAT - application log, this is only logging information, either try to write less or start deleting older entries. Check note [195157 - Application log: Deletion of logs|https://service.sap.com/sap/support/notes/195157]

         

        Others are BDCP* (change pointers), EDI40 (idocs) and CDHDR/CDCLS (change documents). Have a look at note [706478 - Preventing Basis tables from increasing considerably|https://service.sap.com/sap/support/notes/706478]

         

        You need to setup a archiving / data deletion concept for the other objects here as well. The sooner you start with that the better. Application folks will have to be involved, because they are the guys that might or might not need the data after all. After deleting a big part of the rows you should reorganise the tables.

         

        Cheers Michael

        • Re: PSAPSR3 Tablespace is only growing very fast in PROD
          John sat
          Currently Being Moderated

          Dear MHO/Sunil/Eric,

           

          still the PSAPSR3 tablespace keep on growing ,

           

          Pls check the DB02 ,segments details .

           

          SAPSR3     BALDAT          TABLE     PSAPSR3     42,622.000     268.800     853     5,455,616

          SAPSR3     SYS_LOB0000072694C00007$$          LOBSEGMENT     PSAPSR3     5,914.000     191.533     277     756,992

          SAPSR3     CDCLS          TABLE     PSAPSR3     9,091.000     38.400     327     1,163,648

          SAPSR3     SYS_LOB0000082646C00006$$          LOBSEGMENT     PSAPSR3     1,664.000     37.067     209     212,992

          SAPSR3     BALDAT~0          INDEX     PSAPSR3     5,049.000     32.000     266     646,272

          SAPSR3     EDI40          TABLE     PSAPSR3     3,155.000     23.467     233     403,840

          SAPSR3     CDCLS~0          INDEX     PSAPSR3     1,965.000     19.200     214     251,520

          SAPSR3     BDCP2~001          INDEX     PSAPSR3     1,543.000     18.400     208     197,504

          SAPSR3     BDCPS~1          INDEX     PSAPSR3     4,039.000     17.067     247     516,992

          SAPSR3     APQD          TABLE     PSAPSR3     1,671.000     17.067     210     213,888

          SAPSR3     CDHDR~0          INDEX     PSAPSR3     2,183.000     12.800     218     279,424

          SAPSR3     CDHDR          TABLE     PSAPSR3     2,305.000     12.800     220     295,040

          SAPSR3     BDCP2~0          INDEX     PSAPSR3     1,000.000     12.533     196     128,000

          SAPSR3     ZBIPRICING~0          INDEX     PSAPSR3     320.000     10.600     111     40,960

          SAPSR3     WRPL          TABLE     PSAPSR3     288.000     8.700     107     36,864

          SAPSR3     FAGL_SPLINFO          TABLE     PSAPSR3     1,016.000     8.000     198     130,048

          SAPSR3     FAGL_SPLINFO_VAL~0          INDEX     PSAPSR3     736.000     8.000     163     94,208

          SAPSR3     ZBIPRICING          TABLE     PSAPSR3     208.000     6.931     97     26,624

          SAPSR3     MARC~Y          INDEX     PSAPSR3     176.000     5.533     93     22,528

          SYS     WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_2349179954_18942     TABLE PARTITION     SYSAUX     6.000     5.375     21     768

          SAPSR3     MARC~VBM          INDEX     PSAPSR3     152.000     4.867     90     19,456

          SAPSR3     MARC~D          INDEX     PSAPSR3     136.000     4.367     88     17,408

          SAPSR3     FAGLFLEXA          TABLE     PSAPSR3     2,052.000     4.267     216     262,656

          SAPSR3     RFBLG          TABLE     PSAPSR3     3,200.000     4.267     233     409,600

          SAPSR3     BDCPS          TABLE     PSAPSR3     1,280.000     4.267     203     163,840

          SAPSR3     BDCP~POS          INDEX     PSAPSR3     3,392.000     4.267     236     434,176

          SAPSR3     BALHDR          TABLE     PSAPSR3     864.000     4.000     179     110,592

          SAPSR3     FAGL_SPLINFO~0          INDEX     PSAPSR3     361.000     3.767     117     46,208

          SAPSR3     ACCTIT          TABLE     PSAPSR3     289.000     3.733     108     36,992

          SAPSR3     WRPT~0          INDEX     PSAPSR3     112.000     3.731     85     14,336

          SAPSR3     FAGL_SPLINFO_VAL          TABLE     PSAPSR3     448.000     3.467     127     57,344

          SAPSR3     COEJ          TABLE     PSAPSR3     1,089.000     3.200     201     139,392

          SAPSR3     ZBISALEDATA3          TABLE     PSAPSR3     176.000     3.200     93     22,528

          SAPSR3     COEP~1          INDEX     PSAPSR3     927.000     3.167     187     118,656

          SAPSR3     GLPCP          TABLE     PSAPSR3     891.000     2.933     183     114,048

          SAPSR3     ZBISALEDATA          TABLE     PSAPSR3     376.000     2.933     118     48,128

          SAPSR3     WBBP          TABLE     PSAPSR3     344.000     2.933     114     44,032

          SYS     WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_2349179954_18918     TABLE PARTITION     SYSAUX     6.000     2.594     21     768

          SAPSR3     FAGL_SPLINFO~1          INDEX     PSAPSR3     280.000     2.400     106     35,840

          SAPSR3     SE16N_CD_DATA          TABLE     PSAPSR3     72.000     2.333     80     9,216

          SAPSR3     KONH          TABLE     PSAPSR3     1,373.000     2.133     207     175,744

          SAPSR3     GLPCA          TABLE     PSAPSR3     2,437.000     2.133     222     311,936

          SAPSR3     BDCP~0          INDEX     PSAPSR3     1,863.000     2.133     213     238,464

          SAPSR3     SYS_LOB0000161775C00013$$          LOBSEGMENT     PSAPSR3700     5,210.000     2.133     266     666,880

          SAPSR3     BDCPS~0          INDEX     PSAPSR3     2,496.000     2.133     222     319,488

          SAPSR3     D010TAB          TABLE     PSAPSR3700     2,176.000     2.133     217     278,528

          SAPSR3     COEP          TABLE     PSAPSR3     2,117.000     2.133     217     270,976

          SAPSR3     FAGLFLEXA~0          INDEX     PSAPSR3     808.000     2.133     172     103,424

          SAPSR3     BSIS          TABLE     PSAPSR3     1,734.000     2.133     211     221,952

          SAPSR3     BSAS          TABLE     PSAPSR3     1,650.000     2.133     210     211,200

          SAPSR3     GLPCA~3          INDEX     PSAPSR3     382.000     1.867     119     48,896

          SAPSR3     BKPF          TABLE     PSAPSR3     1,012.000     1.867     198     129,536

          SAPSR3     FAGLFLEXA~3          INDEX     PSAPSR3     744.000     1.867     164     95,232

          SAPSR3     FAGLFLEXA~2          INDEX     PSAPSR3     661.000     1.867     154     84,608

          SAPSR3     WRPL~001          INDEX     PSAPSR3     112.000     1.867     85     14,336

          SAPSR3     WRPL~0          INDEX     PSAPSR3     112.000     1.667     85     14,336

          SAPSR3     PCL2          TABLE     PSAPSR3     1,000.000     1.600     196     128,000

          SAPSR3     GLPCA~2          INDEX     PSAPSR3     345.000     1.600     115     44,160

          SAPSR3     FAGL_SPLINFO~3          INDEX     PSAPSR3     136.000     1.600     88     17,408

          SAPSR3     MARC~WRK          INDEX     PSAPSR3     160.000     1.600     91     20,480

          SAPSR3     MSEG          TABLE     PSAPSR3     136.000     1.600     88     17,408

          SAPSR3     ZBISALEDATA~0          INDEX     PSAPSR3     208.000     1.600     97     26,624

          SAPSR3     ZBISALEDATA3~0          INDEX     PSAPSR3     195.000     1.500     96     24,960

          SYS     WRH$_ACTIVE_SESSION_HISTORY     WRH$_ACTIVE_2349179954_18894     TABLE PARTITION

           

           

          Kindly suggest

          • Re: PSAPSR3 Tablespace is only growing very fast in PROD
            Sunil Bujade
            Currently Being Moderated

            >>SAPSR3 BALDAT TABLE PSAPSR3 42,622.000 268.800 853 5,455,616

            see above your table BALDAT is growing and due to this your BALDAT indexes also growing. Have you activated the application logs? check with your functional team and also search on SAP marketplace for more help.

            • Re: PSAPSR3 Tablespace is only growing very fast in PROD
              John sat
              Currently Being Moderated

              Dear Sunil ,

               

              How can check application logs activated ?

               

              Kindly advise

              • Re: PSAPSR3 Tablespace is only growing very fast in PROD
                Sunil Bujade
                Currently Being Moderated

                Can you execute tcode SLG1 and see what logs you can see? You can delete old logs using tcode SLG2. And tcode SLG0 to define entries for your own applications in the application log.

              • Re: PSAPSR3 Tablespace is only growing very fast in PROD
                Bhavik  G. Shroff
                Currently Being Moderated

                Hi,

                 

                 

                PSAPSR3 Tablespace is the default Tablespace, where all SAP data objects of ABAP Stack (Transaction Data & Master Data) are getting stored.

                So it has the clear relationship with the count of Data Insert/Update/Delete done in Database against the frequency of business activities done (daily/weekly/monthly/seasonable growth). If such loaded activities are in execution, which are part of your business process, then based on the requirement and priority, you can ignore such expected growth of application Tables. At this time you have to manage the Space to accommodate such Data load.

                   

                Along with the above suggestions as mentioned in threads  (you can take the help of SAP Notes 706478 && 195157), the Table reorganization within the Tablespace or Tablespace reorganization also needs to be done on some interval of time.

                 

                As suggested in [SAP Note 821687 - FAQ: Space utilization and fragmentation in Oracle|https://service.sap.com/sap/support/notes/821687], You can execute the following SQL statement to get the Information about the Top 40 Tables with Top Unused Space. Based on the output , you can plan the reorganization of the required Tables.

                 

                 

                SELECT * FROM
                (SELECT
                    SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
                    NUM_ROWS,
                    AVG_ROW_LEN ROWLEN,
                    BLOCKS,
                    ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,
                    ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *
                      (1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,
                    ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) -
                      (AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"
                  FROM DBA_TABLES
                  WHERE
                    NUM_ROWS IS NOT NULL AND
                    OWNER LIKE 'SAP%' AND
                    PARTITIONED = 'NO' AND
                    (IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)
                  ORDER BY 7 DESC)
                WHERE ROWNUM <=40;

                 

                 

                 

                 

                Regards,

                Bhavik G. Shroff

  • Re: PSAPSR3 Tablespace is only growing very fast in PROD
    Eric Brunelle
    Currently Being Moderated

    Hi,

     

    with what size did you grow PSAPSR3 last 2 times?

     

    did you add new datafiles or did you grow existing ones?

    • Re: PSAPSR3 Tablespace is only growing very fast in PROD
      John sat
      Currently Being Moderated

      Dear Eric ,

       

      Previously 20 datafiles in PSAPSR3 ,

       

      i have added 2 datafiles (each 10GB so total 20GB space i have extended)to PSAPSR3 .

       

      pls check below DB02 details .

      PSAPSR3     219,640.00     9,362.75     96     YES     220,000.00     9,722.75     96     22     157,305     226,917     ONLINE     PERMANENT

       

       

      Dear Sunil ,

       

      Yes Data uploads are going on but my question is only PSAPSR3 table space keep on growing . pls check the above details of PSAPSR3 again  it shows total used % 96 .

       

      Kindly suggest

  • Re: PSAPSR3 Tablespace is only growing very fast in PROD
    John sat
    Currently Being Moderated

    Temp i will close this thread .....

Actions