1 2 Previous Next 19 Replies Latest reply on Jul 18, 2016 6:06 AM by Pavan Kumar

    Tablespace growth on Exadata

    Balajidba

      Hi,

       

      A specific tablespace size is growing dramatically.

       

      Action taken

       

      First time I added datafile with  size 1 TB. Next time I added datafile with size 500 GB. But again I am alerts for tablespace size. Could you please comment on cause of tablespace growth?

       

      My findings

       

      There are already 826 datafiles added to the tablespace.

       

      Tablespace sizes in TB

       

      Tablespace Name      FREE_SPACE USED_SPACE TOTAL_SPACE

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

      PSAPSR3                        1.95472389     23.0013308         24.9560547

       

       

      Exadata server -- 11.2.0.4.0

      OS  -- Linux

      This is prod

       

       

      Regards,

      Bala

        • 1. Re: Tablespace growth on Exadata
          Balajidba

          Hi,

           

          Could someone please respond to this mail?

           

           

          Regards,

          Bala

          • 2. Re: Tablespace growth on Exadata
            robinsc

            Question is are you adding data to the table ? if so then the table will grow. the larger question is do you suspect there is any Exadata specific issue. Why don't you count the number of rows and compare with num_rows in last analysed to see if there has been significant growth. The table space name sounds like it might be a sap table ? could there be a mounth end or year ennd process going on that is taking up the space ?

            find out which object in the tablespace is growing first.

            • 3. Re: Tablespace growth on Exadata
              Balajidba

              Hi Robin,

               

              Thanks for your suggestions.

               

              Please find my inline answers.

              Question is are you adding data to the table ?

              No, I am not adding data to the table.

               

              the larger question is do you suspect there is any Exadata specific issue?

              No

               

              Why don't you count the number of rows and compare with num_rows in last analysed to see if there has been significant growth.

              Could you please tell me how to find this information as it is confusing for me to understand?


              The table space name sounds like it might be a sap table ? could there be a mounth end or year ennd process going on that is taking up the space ?

               

              I am not sure, will have to check with Apps team on this.

               

              find out which object in the tablespace is growing first.

              Could you please tell me how to find this information?

               

               

              Regards,

              Bala

              • 4. Re: Tablespace growth on Exadata
                PS_orclNerd

                Maybe they forgot to set up a housekeeping job.. sometimes it happens.. maybe you should check what tables/segments are growing, how big they are.. if they are some LOBs, it's possible someone inserts movies inside the tablespace.. do you know something about the application? .. you should be a friend with your application colleagues.

                 

                do you have AWR? if yes Scripts: Database,Table,Tablespace Growth Report using AWR « All about Database Administration, Tips & Tricks

                 

                and there is a possibility like:

                select * from table(dbms_space.object_growth_trend('RCAT','RSR','TABLE')) ;

                01-JUL-16 04.42.32.010539 PM                                                   18469414    19922944 INTERPOLATED

                02-JUL-16 04.42.32.010539 PM                                                   18469414    19922944 INTERPOLATED

                03-JUL-16 04.42.32.010539 PM                                                   18469414    19922944 INTERPOLATED

                04-JUL-16 04.42.32.010539 PM                                                   18473439    19922944 GOOD

                05-JUL-16 04.42.32.010539 PM                                                   18465648    19922944 GOOD

                06-JUL-16 04.42.32.010539 PM                                                   18538990    19922944 GOOD

                07-JUL-16 04.42.32.010539 PM                                                   18538998    19922944 GOOD

                08-JUL-16 04.42.32.010539 PM                                                   18548478    19922944 GOOD

                09-JUL-16 04.42.32.010539 PM                                                   18407122    19922944 GOOD

                10-JUL-16 04.42.32.010539 PM                                                   18440520    19922944 GOOD

                11-JUL-16 04.42.32.010539 PM                                                   18437300    19922944 GOOD

                12-JUL-16 04.42.32.010539 PM                                                   18425996    19922944 GOOD

                13-JUL-16 04.42.32.010539 PM                                                   18548409    19922944 PROJECTED

                14-JUL-16 04.42.32.010539 PM                                                   18548395    19922944 PROJECTED

                15-JUL-16 04.42.32.010539 PM                                                   18548381    19922944 PROJECTED

                DBMS_SPACE.OBJECT_GROWTH_TREND returns ORA-14551

                • 5. Re: Tablespace growth on Exadata
                  Balajidba

                  Hi Perf,

                   

                  maybe you should check what tables/segments are growing. , how big they are?

                  Could you please tell me how to find this information as I could see there are 108364 rows selected when I issue below command?

                   

                  select SEGMENT_name,tablespace_name,segment_type,sum(bytes/(1024*1024)) from DBA_segments where tablespace_name='PSAPSR3' group by SEGMENT_name,segment_type;

                   

                  do you know something about the application?

                  No, I checked with App team and they told that this issue is to due to year end activities but not 100% sure.

                   

                  Regards,

                  Bala

                  • 6. Re: Tablespace growth on Exadata
                    robinsc

                    order by sum bytes descending  take the top n rows run the sql at an interval of say half and hour and check which segments are growing.

                    However as I said if there are month end activities going on then this could be valid data that is being stored, In which case all you can do is keep adding files. You might look into HCC if some data in the large tables is historic ...

                    • 7. Re: Tablespace growth on Exadata
                      PS_orclNerd

                      col owner for a30

                      col segment_name for a30

                      col partition_name for a30

                      select * from (select owner, segment_name, partition_name, segment_type, bytes/1024/1024 from dba_segments where tablespace_name='&tbs' order by 4 desc) where rownum<=20 order by 5 asc ;

                       

                      I haven't seen a database where you have so much datafiles. You have so much luck to work on an Exadata

                      • 8. Re: Tablespace growth on Exadata
                        Balajidba

                        Hi Robin,

                         

                        order by sum bytes descending  take the top n rows run the sql at an interval of say half and hour and check which segments are growing.

                        Could you please write query and share it here?

                        This is to inform again there are 108364 rows selected when I ran below select query. So I think it's difficult to find which object is growing faster.

                         

                        select SEGMENT_name,tablespace_name,segment_type,sum(bytes/(1024*1024)) from DBA_segments where tablespace_name='PSAPSR3' group by SEGMENT_name,segment_type;

                         

                        Hi Perf,

                         

                        However as I said if there are month end activities going on then this could be valid data that is being stored, In which case all you can do is keep adding files.

                        Please suggest me how much size to add files at time of alert.

                         

                         

                        You might look into HCC if some data in the large tables is historic ... 

                        May I know what is HCC here? Could you please shed some light on it?

                         

                        Here is the output of select query

                         

                        OWNER                          SEGMENT_NAME           PARTITION_NAME                 SEGMENT_TYPE       BYTES/1024/1024
                        ------------------------------        ------------------------------ ------------------------------                                ------------------                ---------------
                        SAPSR3                         /BIC/FISD_C05                  /BIC/FISD_C050000000263        TABLE PARTITION              .0625
                        SAPSR3                         /BIC/FZCPM_C67                 /BIC/FZCPM_C670000001392       TABLE PARTITION              .0625
                        SAPSR3                         /BIC/FISD_C04                  /BIC/FISD_C040000001679        TABLE PARTITION               .125
                        SAPSR3                         /BIC/FZCPM_C68                 /BIC/FZCPM_C680000001341       TABLE PARTITION               .125
                        SAPSR3                         /BIC/FIINV_C15                 /BIC/FIINV_C150000000578       TABLE PARTITION              .1875
                        SAPSR3                         /BIC/FZCPM_C61                 /BIC/FZCPM_C610000001344       TABLE PARTITION                .25
                        SAPSR3                         /BI0/F0PP_C03                  /BI0/F0PP_C030000003181        TABLE PARTITION               .625
                        SAPSR3                         /BIC/FISD_C72                  /BIC/FISD_C720000000518        TABLE PARTITION               .625
                        SAPSR3                         /BIC/FIDLV_C02                 /BIC/FIDLV_C020000000563       TABLE PARTITION               .625
                        SAPSR3                         /BIC/FIDLV_C03                 /BIC/FIDLV_C030000000470       TABLE PARTITION              .6875
                        SAPSR3                         /BI0/F0PP_C03                  /BI0/F0PP_C030000003182        TABLE PARTITION                .75
                        SAPSR3                         /BIC/FIOSD_C71                 /BIC/FIOSD_C710000000250       TABLE PARTITION                  2
                        SAPSR3                         /BIC/FISD_C04                  /BIC/FISD_C040000001681        TABLE PARTITION                  3
                        SAPSR3                         /BIC/FISD_C04                  /BIC/FISD_C040000001680        TABLE PARTITION                  3
                        SAPSR3                         /BIC/FISD_C04                  /BIC/FISD_C040000001678        TABLE PARTITION                  6
                        SAPSR3                         /BIC/FISD_C18                  /BIC/FISD_C180000000255        TABLE PARTITION                 80
                        SAPSR3                         /BIC/B0003590000               /BIC/B00035900000000000004     TABLE PARTITION            92.1875
                        SAPSR3                         /BIC/B0003591000               /BIC/B00035910000000000007     TABLE PARTITION                 96
                        SAPSR3                         /BIC/B0004130000               /BIC/B00041300000000000074     TABLE PARTITION                228
                        SAPSR3                         /BIC/B0004067000               /BIC/B00040670000000000002     TABLE PARTITION                280
                        
                        20 rows selected.
                        

                         

                         

                        Regards,

                        Bala

                        • 9. Re: Tablespace growth on Exadata
                          robinsc

                          set lines 1000

                          set pages 0

                          select  * from (select SEGMENT_name,tablespace_name,segment_type,sum(bytes/(1024*1024))  "size" from DBA_segments where tablespace_name='PSAPSR3' group by SEGMENT_name,segment_type,tablespace_name order by 4 desc)where rownum <50 ;

                           

                          but running this at intervals you can see which large objects sizes are increasing. alternatively use the .

                          HCC is hybrid columnar compression which is available on Exadata and can be used to reduce storage space by an table or table partition.


                          BALA  why don't you explore bigfile tablespaces. that will allow any size tablespace using a single datafile. On exadata it doesn't matter since everything is in asm.

                          • 10. Re: Tablespace growth on Exadata
                            robinsc

                            Hi perf the order by 4 desc is not ordering by size but by segment type which is wrong. Bala please run the query I sent and see what that output gives.

                             

                            you should keep 5 desc inside if you want the largest objects.

                            • 11. Re: Tablespace growth on Exadata
                              Balajidba

                              Hi Robin,

                               

                              HCC is hybrid columnar compression which is available on Exadata and can be used to reduce storage space by an table or table partition.

                              Could you please guide or provide me link on how to use HCC on table on Exadata?

                               

                              BALA  why don't you explore bigfile tablespaces. that will allow any size tablespace using a single datafile. On exadata it doesn't matter since everything is in asm.

                              Thanks for your information. I will use it.

                               

                              Bala please run the query I sent and see what that output gives.

                              I ran the select query every 30 mins and I didn't find changes in the table size.

                               

                              Regards,

                              Bala

                              • 12. Re: Tablespace growth on Exadata
                                robinsc

                                Google hcc or check the docs as there are plenty of examples. If nothing is growing I also assume your initial problem of table space exhaustion is also solved. Please confirm.

                                If we have helped you please don't hesitate to mark answers as helpful.

                                • 13. Re: Tablespace growth on Exadata
                                  PS_orclNerd

                                  Ou, sorry.. the inner query needs to have a order by 5 desc.. not 4 ..

                                  • 14. Re: Tablespace growth on Exadata
                                    Balajidba

                                    Hi Robin/Perf,

                                     

                                    I see there is few variation in tables growth today as compared to yesterday's one and we haven't received any alert for today. I will monitor it and revert if required further assistance on this issue.

                                     

                                    Thank you !

                                     

                                    Regards,

                                    Bala

                                    1 person found this helpful
                                    1 2 Previous Next