1 2 Previous Next 18 Replies Latest reply: May 28, 2009 1:21 AM by JustinCave RSS

    Reg. reorg on 10g database.

    586975
      hi,

      I have a database .I could see lot of chunk space in it.
      Due to lot of transactions the space left unused.
      I would to gain space at database level.

      could any one please tell the effective way in 10g to do reorg of database.

      your help is much needed.

      waiting to hear from you...
        • 1. Re: Reg. reorg on 10g database.
          JustinCave
          user583972 wrote:
          I have a database .I could see lot of chunk space in it.
          Can you explain exactly what you are seeing (i.e. the queries you are running and the results that you are seeing)? What structure do you believe has free space and why do you believe that structure has free space.
          Due to lot of transactions the space left unused.
          Huh? Transactions should be able to use any unused space in objects.
          I would to gain space at database level.
          I'm not sure I understand. Are you trying to shrink the data files associated with a database? Increase the free space in those data files? Something else?
          could any one please tell the effective way in 10g to do reorg of database.
          Assuming it is necessary, it will depend on the answers to the questions above.

          Justin
          • 2. Re: Reg. reorg on 10g database.
            sb92075
            could any one please tell the effective way in 10g to do reorg of database.
            The most effective use of your time, effort worry, would be to increase the Buffer Cache Hit Ratio (BCHR).
            As the increase of BCHR will dwarf the results you obtain from a simple tablespace reorg.
            • 3. Re: Reg. reorg on 10g database.
              JustinCave
              sb92075 wrote:
              could any one please tell the effective way in 10g to do reorg of database.
              The most effective use of your time, effort worry, would be to increase the Buffer Cache Hit Ratio (BCHR).
              As the increase of BCHR will dwarf the results you obtain from a simple tablespace reorg.
              That's interesting advice. Why do you believe it is correct?

              Generally, the debate is whether the buffer cache hit ratio is completely meaningless or whether it is a poor measure that might have some meaning if you use it as a point to things that might be interesting to investigate. I haven't heard anyone suggest that changing the buffer-cache hit ratio should be a primary or secondary goal of tuning in many years.

              Increasing the BCHR may cause performance to improve. Or it may cause performance to decrease. Or it may have no impact on performance.

              There are a number of scripts that are easily google-able that will increase the BCHR to whatever you'd like. They won't have any positive benefit to performance, but you can get as many 9's in the BCHR as you'd like.

              Additionally, the BCHR has nothing whatsoever to do with space utilization, which is what the original poster is asking about.

              Justin
              • 4. Re: Reg. reorg on 10g database.
                586975
                Can you explain exactly what you are seeing (i.e. the queries you are running and the results that you are seeing)? What structure do you believe has free space and why do you believe that structure has free space.

                I am using the below sql to find out the freespace left in a datafile.I could see lot of free space available.
                But not able to utilise that space .
                select sum(bytes)/1024/1024 from dba_free_space where file_id='2'



                I'm not sure I understand. Are you trying to shrink the data files associated with a database? Increase the free space in those data files? Something else?

                Here comes my question.

                what is the better way to do reorg. of a table?

                alter table tname move tablespace ---

                or

                alter table tablename shrink

                or


                alter table tablename shrink compact

                which one is better?
                • 5. Re: Reg. reorg on 10g database.
                  JustinCave
                  1) It's normal to have free space in DBA_FREE_SPACE. Particularly in a production database where data files are generally not set to autoextend, you would generally keep quite a bit of free space in the data files. DBAs generally start getting paged if free space drops below 10 or 20%.

                  2) Free space in DBA_FREE_SPACE is available for objects that need it because transactions are inserting data. Why do you believe that you are not able to utilize that space?

                  3) The best option is not to reorganize the table in the first place. The fastest way to do anything is to figure out that you don't have to. Beyond that, it would depend on what the problem is. Space in DBA_FREE_SPACE does not indicate any need to reorganize any table.

                  Justin
                  • 6. Re: Reg. reorg on 10g database.
                    sb92075
                    That's interesting advice. Why do you believe it is correct?
                    Both are searches for the Golden Fleece which will remain the myths of legend.
                    • 7. Re: Reg. reorg on 10g database.
                      699921
                      ///better way to re-org the table.///

                      i guess all the commands you have mentioned can reorganise the table, but if your table is created with a particular storage clause it cannot reorganise behind that clause. for example if the table is created with 50mb size and you have actually 1 mb data, still you cannot reorganise the table with that command you have mentioned, in that case you have to substitue storage clause also

                      alter table tname move tablespace ...storage

                      for resizing datafile

                      alter tablespace tablespacename coalesce
                      alter database datafile '' resize
                      • 8. Re: Reg. reorg on 10g database.
                        586975
                        1) It's normal to have free space in DBA_FREE_SPACE. Particularly in a production database where data files are generally not set to autoextend, you would generally keep quite a bit of free space in the data files. DBAs generally start getting paged if free space drops below 10 or 20%.


                        yes you are correct, but the thing is i am not able to resize the datafile (decrease the size of the datafile).
                        to the size shown by above query in some tablespaces atleast to 50% of the size which shown in dba_free_space query.

                        Free space in DBA_FREE_SPACE is available for objects that need it because transactions are inserting data. Why do you believe that you are not able to utilize that space?

                        My answer is above one for this.

                        The best option is not to reorganize the table in the first place. The fastest way to do anything is to figure out that you don't have to. Beyond that, it would depend on what the problem is. Space in DBA_FREE_SPACE does not indicate any need to reorganize any table.

                        I am not talking about single table i am talking about the whole database.

                        Please answer this question

                        what is the difference between

                        moving a table to a tablespace and shrinking a table which one is better and what is the exact need of each?
                        • 9. Re: Reg. reorg on 10g database.
                          JustinCave
                          sb92075 wrote:
                          That's interesting advice. Why do you believe it is correct?
                          Both are searches for the Golden Fleece which will remain the myths of legend.
                          I have to admit, I'm not sure I understand. Are you agreeing with me that tuning via the BCHR is a myth?

                          Justin
                          • 10. Re: Reg. reorg on 10g database.
                            586975
                            what you said is exactly correct but to be precise my question is

                            which is the best one among reorg-table methods?

                            1.) alter table tablename move tablespace -----

                            or

                            2.) alter tablespace tsname coalesce

                            or

                            3.) alter table shrink cascade
                            or

                            4.) alter table shrink compact?
                            • 11. Re: Reg. reorg on 10g database.
                              sb92075
                              I have to admit, I'm not sure I understand. Are you agreeing with me that tuning via the BCHR is a myth?
                              What part of BOTH do you not understand?
                              Tuning to BCHR will achieve the same results as a table reorg.
                              Going around in circles is movement.
                              But most folks would not deem it progress.
                              Never confuse movement, with progress.
                              • 12. Re: Reg. reorg on 10g database.
                                JustinCave
                                OK, so the answer to my original question of what you are trying to accomplish is to shrink the size of a data file, correct? So it doesn't matter that the space is available for other transactions-- you don't want that space associate with the database any longer.

                                If you are trying to resize a data file, you need to move the objects in the tablespace the data file is associated with that happen to have blocks above whatever point you want to resize the data file to into a different tablespace (this requires more storage space initially), resize the data file, and then move the objects back. Of course, you'll need to rebuild any indexes on the tables that have been moved.

                                You would shrink a table if the volume of data the table will hold has been permanently decreased and you want to release the space back to the tablespace. That's not what you appear to be after.

                                Justin
                                • 13. Re: Reg. reorg on 10g database.
                                  JustinCave
                                  user10316225 wrote:
                                  ///better way to re-org the table.///

                                  i guess all the commands you have mentioned can reorganise the table, but if your table is created with a particular storage clause it cannot reorganise behind that clause. for example if the table is created with 50mb size and you have actually 1 mb data, still you cannot reorganise the table with that command you have mentioned, in that case you have to substitue storage clause also

                                  alter table tname move tablespace ...storage
                                  That's not entirely correct. If you happen to have specified a minimum table size in your storage clause, you would have to change the storage clause in order to free up that space. That is a very unusual case, though, particularly in the last decade or so since the advent of locally managed tablespaces and the disappearance of the myth that objects should be in a single segment.

                                  Depending on the database version, you have different options for reclaiming the 49 MB of space in your hypothetical table (unless you specified a minimum size of 50 MB) without touching the storage clause.

                                  Justin
                                  • 14. Re: Reg. reorg on 10g database.
                                    699921
                                    moving a table to a tablespace and shrinking a table which one is better and what is the exact need of each?
                                    since you are concerned about storage i will highlight the storage part alone for these 2 commands

                                    ALTER TABLE <TNAME> MOVE TABLESPACE <TSNAME > STORAGE (INITIAL 10M)

                                    suppose if you have a table created with 50m and you have data for only 10m, and there is absolutely no need for the table table to grow.
                                    without losing data you can reorganise or rebuild the table with the above command.

                                    ALTER TABLE <TNAME> SHRINK

                                    this is something which can be used when there are frequent updates and deletes, and you have table has grown to a large extent and now it has reduced because of delete. mostly used to move the high water mark, and for imporving performance on huge size table.
                                    1 2 Previous Next