1 2 3 Previous Next 30 Replies Latest reply on Nov 12, 2019 1:56 PM by Jonathan Lewis

    Find largest table including indexes and lobs

    Brian.B

      Our in-house database is growing fast. I am trying to pin down which table is growing the most counting indexes and lobs.

      To do that I have been working on this query. I want to make sure that I am not double counting the lobs, or indexes.

       

      Does this query make sense? Do you see any errors in my logic?

       

      Thanks

       

      SELECT tab.owner, tab.table_name, lob.column_name,
             TO_CHAR( SUM( NVL(seg_tab.bytes, 0)+NVL(seg_lob.bytes, 0)+NVL(seg_idx.bytes, 0) ) /1024/1024, '999,999,999' )       AS sum_lob_mb
        FROM dba_tables             tab
        LEFT OUTER JOIN dba_lobs    lob
          ON tab.owner            = lob.owner
         AND tab.table_name       = lob.table_name
        LEFT OUTER JOIN dba_indexes idx
          ON tab.owner            = idx.owner
         AND tab.table_name       = idx.table_name
         AND idx.index_type NOT IN ( 'LOB', 'IOT - TOP', 'CLUSTER' )
        LEFT OUTER JOIN dba_segments      seg_tab
          ON lob.owner            = seg_tab.owner
         AND lob.table_name       = seg_tab.segment_name
        LEFT OUTER JOIN dba_segments      seg_lob
          ON lob.owner            = seg_lob.owner
         AND lob.segment_name     = seg_lob.segment_name
        LEFT OUTER JOIN dba_segments      seg_idx
          ON lob.owner            = seg_idx.owner
         AND idx.index_name       = seg_idx.segment_name
       GROUP BY tab.owner, tab.table_name, lob.column_name
      HAVING SUM( NVL(seg_tab.bytes, 0)+NVL(seg_lob.bytes, 0)+NVL(seg_idx.bytes, 0) ) /1024/1024 > 1024
       ORDER BY SUM( NVL(seg_tab.bytes, 0)+NVL(seg_lob.bytes, 0)+NVL(seg_idx.bytes, 0) );
      
        • 1. Re: Find largest table including indexes and lobs
          John Thorton

          Brian.B wrote:

           

          Our in-house database is growing fast. I am trying to pin down which table is growing the most counting indexes and lobs.

          To do that I have been working on this query. I want to make sure that I am not double counting the lobs, or indexes.

           

          Does this query make sense? Do you see any errors in my logic?

           

          Thanks

           

          1. SELECTtab.owner,tab.table_name,lob.column_name,
          2. TO_CHAR(SUM(seg_tab.bytes+seg_lob.bytes+seg_idx.bytes)/1024/1024,'999,999,999')ASsum_lob_mb
          3. FROMdba_tablestab
          4. LEFTOUTERJOINdba_lobslob
          5. ONtab.owner=lob.owner
          6. ANDtab.table_name=lob.table_name
          7. LEFTOUTERJOINdba_indexesidx
          8. ONtab.owner=idx.owner
          9. ANDtab.table_name=idx.table_name
          10. ANDidx.index_typeNOTIN('LOB','IOT-TOP','CLUSTER')
          11. LEFTOUTERJOINdba_segmentsseg_tab
          12. ONlob.owner=seg_tab.owner
          13. ANDlob.table_name=seg_tab.segment_name
          14. LEFTOUTERJOINdba_segmentsseg_lob
          15. ONlob.owner=seg_lob.owner
          16. ANDlob.segment_name=seg_lob.segment_name
          17. LEFTOUTERJOINdba_segmentsseg_idx
          18. ONlob.owner=seg_idx.owner
          19. ANDidx.index_name=seg_idx.segment_name
          20. GROUPBYtab.owner,tab.table_name,lob.column_name
          21. HAVINGSUM(seg_tab.bytes+seg_lob.bytes+seg_idx.bytes)/1024/1024>1024
          22. ORDERBYSUM(seg_tab.bytes+seg_lob.bytes+seg_idx.bytes);

          what about PARTITIONED tables?

          • 2. Re: Find largest table including indexes and lobs
            Vsevolod Afanassiev

            The query gives you the current size of the tables. It doesn't give you the growth rate. To get the growth rate save the data from dba_segments into your own table, wait a few days, save again, and then compare these two tables.

             

            It helps to understand the data in the tables: how many rows are inserted per day and for how long they are kept. For non-partitioned tables: find a date/timestamp column, something like CREATED_DATE, and count the rows per day. If a table is partitioned by day (or hour, week, month) then check how the size of partitions changed over time.

            • 3. Re: Find largest table including indexes and lobs
              Brian.B

              We don't have the partitioning option. So partitioned tables aren't a concern. I guess I can add that in to pick up any partitioned tables that exist in the sys schema.

              • 4. Re: Find largest table including indexes and lobs
                JohnWatson2

                Brian.B wrote:

                 

                We don't have the partitioning option. So partitioned tables aren't a concern. I guess I can add that in to pick up any partitioned tables that exist in the sys schema.

                You do have a partitioned tale: your unified audit trail. That can grow to umpteen gigabytes very quickly.

                • 5. Re: Find largest table including indexes and lobs
                  AndrewSayer

                  You’ll be summing null whenever a null sneaks into that expression - whenever a table doesn‘t have a lob for example. You can fix this by adding up nvl(bytes,0) instead.


                  You’ll be double counting the table size per index in the table. Best to join to a subquery that sums what you need:

                   

                  left join (select table_name, sum(bytes) from dba_indexes I join dba_segments.. group by table_name) ind on ind.table_name = ...

                   

                  To identify growth, you need this picture at different times.

                   

                  I would actually suggest logging this total-table-size as well as individual segment sizes. Not all indexes will grow at the same rate, with some designs it’s fairly easy for a table to stay a similar size and one index grows and grows and grows until its 10 times the size of the table. Being able to drill down to the individual segment that’s growing will make life easier.

                   

                  Have you purchased the diagnostics pack licence? There is a view here that does a lot of the work for you.

                  • 6. Re: Find largest table including indexes and lobs
                    Jonathan Lewis

                    @Brian B.  (created as a response to Andrew Sayer by accident,)

                     

                    Have you tested this query at all ?

                    To limit the work and output add the predicate where tab.owner = '{some test schema name}'

                    Then create a single unindexed table with no LOB columns in the test schema and see what the query gives you

                      create table t1 pctfree 50 as select * from all_objects -- should give you a suitable table

                    then add an index, and check the query results

                        create iondex t1_i1 on t1(object_name, object_id, object_type) pctfree 80;  -- should give you a suitable ib=ndex.

                    then add a lob column with a load of data and see what you get

                    then add a second index

                    then add a second lob

                     

                     

                    If you have tested the query (with restriction to a single schema) I suspect you've tested it with a single heap table with a single lob column and single index (i.e. an example that is unintentionally designed to have the best chance of producing the expected result) rather then trying to think of examples aimed at exposing possible flaws in the logic.

                     

                    Example - if you don't have a lob column then your join to "seg_tab" based on lob.owner will be joining on the NULL produced by the outer join from tab to lob, so it won't find the table segment information.

                     

                    Andrew suggested joining aggregated subqueries, I think I'd look at strategies using a UNION ALL approach - though there's probably not much difference between the two approaches, either in performance of clarity.

                     

                    One detail worth mentioning - the owner of an index need not be the owner of the table.  But, as you did with the comment about partitioning, you may decide that that will make no difference in your system.

                     

                     

                    Regards

                    Jonathan Lewis

                    • 7. Re: Find largest table including indexes and lobs
                      DeepC

                      You can use oracle supplied pakcage procedure dbms_space.space_usage, instead

                      • 8. Re: Find largest table including indexes and lobs
                        Jonathan Lewis

                        Andrew,

                         

                        Apologies - you probably realised the previous response was supposed to be directed to Brian B,

                        I'm not sure how I managed to make it a reply to you.

                         

                        Regards

                        Jonathan Lewis

                        • 9. Re: Find largest table including indexes and lobs
                          Brian.B

                          Hi Jonathan,

                           

                           

                          I did test it and then eventually realized that my query doesn't show the information that I wanted. I must

                          be missing some joins because I think it is over counting the space used. I will probably rewrite the query

                          to start with dba_segments and join to various tables to translate everything to table_name. I can then get

                          a list of how large every table is counting the tables, indexes and lobs. Ideally I can avoid using a UNION

                          if I start with dba_segments.

                           

                           

                          I did add the NVL's back in. I had them and dropped them as a result of changing the query. I have also looked

                          through the database to drop and recreate indexes where the index owner and table owner didn't match. So I was

                          aware of that. there was a data map that showed where various objects where stored. That went away somewhere

                          between 9i and 10g. It would be great is Cloud Control or the database had better ways of tracking database growth.

                          I probably should have added a job to track space used every week over a year ago and then I would have better

                          information on where the space is going. I do have a restore version of the database from 18 months ago.

                          So I can query between the database 18 months ago and now. I am just trying to get the right query to show

                          how tables have grown.

                           

                           

                          Thanks

                          • 10. Re: Find largest table including indexes and lobs
                            Brian.B

                            Hi Deepc,

                             

                            I have used the DBMS_SPACE package. I'm just not sure about the easiest way to track size by table taking into account the size of the table, its indexes and lobs.\

                             

                            Thanks

                            • 11. Re: Find largest table including indexes and lobs
                              DeepC

                              Brian.B wrote:

                               

                              Hi Deepc,

                               

                              I have used the DBMS_SPACE package. I'm just not sure about the easiest way to track size by table taking into account the size of the table, its indexes and lobs.\

                               

                              Thanks

                              DBMS_SPACE  probably not the easiest (but should not be that difficult too - have to write a script), but the most accurate way to calcuate size of segments.

                              • 12. Re: Find largest table including indexes and lobs
                                Jonathan Lewis

                                Break your query into three parts - and test each part separately against a single, carefully constructed schema that covers all the options you want:

                                 

                                Part 1:  join dba_tables to dba_segments to get size (owner, table_name, bytes)

                                Part 2:  join dba_indexes to dba_segments to get sizie of indexes, (table_owner, table_name, index_name, bytes)

                                Part 3:  join dba_lobs to dba_segments to get sizes of lobs  (owner, table_name, column_name, bytes);

                                 

                                Then

                                select * from (

                                select * from part1  --- adding in a null column to match parts 2 and 3)

                                union all

                                select * from part2

                                union all

                                select * form part3

                                )

                                 

                                Compare this with the figures you get from select * from dba_segments

                                 

                                Finally change wrap the whole thing with select owner, table_name, sum(bytes) from ...

                                You'll have to be a bit careful with IOTs, and this won't handle nested tables or partitioning - and there may be other esoteric bits that manage to hide

                                 

                                 

                                Regards

                                Jonathan Lewis

                                • 14. Re: Find largest table including indexes and lobs
                                  Jonathan Lewis

                                  I've taken the outline I suggested and written up a possible solution.

                                  https://jonathanlewis.wordpress.com/2019/11/06/table-space

                                   

                                  Regards

                                  Jonathan Lewis

                                  1 2 3 Previous Next