on 12-09-2010 12:58 PM
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
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
Temp i will close this thread .....
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
with what size did you grow PSAPSR3 last 2 times?
did you add new datafiles or did you grow existing ones?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.