12 Replies Latest reply on Nov 23, 2010 4:32 AM by 606331

    db reorg

    606331
      Hi all,

      db 9.2.0
      os solaris 8

      please any one help to me what are the pre checks for db -reorg.
      my db size 500+ .before&after reorg what should I need to check?


      please let me know if any one knows.

      thanks
        • 1. Re: db reorg
          sb92075
          db 9.2.0 os solaris 8
          Dictionary Managed or Locally Managed Tablespaces
          • 2. Re: db reorg
            AliD
            Can you specify:

            1. Why are you reorganizing the database?
            2. How many schema/object/tablespace do you have?
            3. What does the disk layer look like?
            4. How many huge tables do you have?
            5. Do you have partitioning license?
            • 3. Re: db reorg
              606331
              Locally Managed Tablespaces
              • 4. Re: db reorg
                606331
                1. Why are you reorganizing the database? for better Performence

                2. How many schema/object/tablespace do you have?20/noidea/32
                3. What does the disk layer look like?SAN
                4. How many huge tables do you have?no idea
                5. Do you have partitioning license?yes
                • 5. Re: db reorg
                  AliD
                  Oh! I missed one question, a big one:

                  How much time do you have to execute the reorg?
                  • 6. Re: db reorg
                    sb92075
                    Locally Managed Tablespaces
                    LMT typically do not require any maintenance.
                    • 7. Re: db reorg
                      606331
                      we have 1day time to do this activity
                      • 8. Re: db reorg
                        AliD
                        1. Why are you reorganizing the database? for better Performence
                        4. How many huge tables do you have?no idea>
                        It looks to me you are hoping the reorg will fix some of the performance issues you have, instead of analyzing the bottlenecks and scientifically concluding a reorg is necessary.

                        Do you know what your steps are for the reorg itself? export/import the schemas, rebuilding indexes, moving tables across tablespaces, etc..?

                        For precheck, I start with finding the costly SQLs and find out if the disk IO is the culprit. Then find the objects (tables/indexes,..) in those queries and plan to avoid hotspots, creating partitions or subpartitions using the best key, adding indexes, etc. Note that you use SAN and it is already striped. So moving tablespaces around shouldn't help much.

                        For Postcheck, make sure all indexes are usable then run a regression test on the SQLs identified in precheck and a quick overall application test.
                        1 person found this helpful
                        • 9. Re: db reorg
                          CKPT
                          It is quiet easir if you perform by OEM because it will take care of all those like invalid object compile & gathering statistics of objects.

                          if you are choosing for manual reorganization. specifically you need to check which datafiles are need to shrink.. after that check for invalid objects and validate those then gather stats.

                          from google you can

                          http://www.google.co.in/search?hl=en&rls=com.microsoft%3Aen-us&q=database+reorganization+oracle&aq=2&aqi=g1&aql=&oq=database+reorganization+ora&gs_rfai=

                          Thanks
                          • 10. Re: db reorg
                            606331
                            moving tables across tablespaces, etc..?
                            could you please explain moving tables across tablespaces..at what situations we go for this?
                            If my storage is SAN?it is not good..right?
                            If my storage local disk array?is it good one?
                            • 11. Re: db reorg
                              sb92075
                              my db size 500+ .before&after reorg what should I need to check?
                              which metric & what value indicates that any tablespace would benefit from a reorganization?
                              what SQL measures performance gain after doing a tablespace reorganization?

                              If you can't quantify both answers, you are simply practicing alchemy.
                              • 12. Re: db reorg
                                606331
                                Thanks for ur response.

                                how to find out
                                which metric & what value indicates that any tablespace would benefit from a reorganization?.........................

                                what SQL measures performance gain after doing a tablespace reorganization?

                                How we can predicate above Q?