1 2 3 Previous Next 30 Replies Latest reply on Nov 12, 2019 1:56 PM by Jonathan Lewis Go to original post
      • 15. Re: Find largest table including indexes and lobs
        Brian.B

        Hi Jonathan,

         

        Your approach makes sense. i will certainly look into that. The script from Sayan looks good as well. There are several tables like "TDE_NTwFsCHUfOOD/gQwEAAH9GXg==". I'm not sure what those tables are and how to track them back to an object type. The database has index oriented tables, but most are in Oracle schema's, so I don't think that they are a huge factor.

         

        Thanks

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

          If you follow the link I posted earlier you'll see that Sayan's script doesn't handle IOTs properly while mine collects all the bits.

           

          I don't recognise the odd table_names.  Garbage names usually mean they're tables that have been dropped but are sill in the recyclebin, however I expect to see those "tables" gettiing new names that start with "BIN". You can always query dba_recyclebin to see what's there.

           

          Given the TDE prefix it's possble that these tables something to do with transparent data encryption. I'd be interested to hear what you discover (or someone else who's seen them before could tell us). You could query dba_segments for those segment_names to see who owns them, what they are and what tablespace they're in.

           

          Regards

          Jonathan Lewis

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

            Hi Jonathan,

             

            The TDE tables are nested tables. I have 491 nested tables, unless the more than one segment name translates to a single table. Seems like a lot.

            I will take your query and add in some code to capture the storage for a nested table and re-post the updated SQL.

             

            Not just anyone would cast a constant used in a query. I guess that is why you are an Oracle Ace and I am not. ;-)

             

             

            SQL> select SEGMENT_NAME, SEGMENT_TYPE, SEGMENT_SUBTYPE, TO_CHAR( BYTES/1024/1024, '999,999,999') mb 
            2 from dba_segments where segment_name='TDE_NTwFsCHUe8OD/gQwEAAH9GXg==';
            
            
            SEGMENT_NAME                        SEGMENT_TYPE                        SEGMENT_SUBTYPE                MB
            ----------------------------------- ----------------------------------- ------------------------------ -----
            TDE_NTwFsCHUe8OD/gQwEAAH9GXg==      NESTED TABLE                        ASSM                             858
            
            
            1 row selected.
            
            
            Elapsed: 00:00:00.05
            
            • 18. Re: Find largest table including indexes and lobs
              Jonathan Lewis

              In theory my code does capture nested table under the base table.

              I've just copied it from my blog and run it against the test schema in 12.2 and 19.3 and it seems to work correctly.

               

              Can you execute

               

              select owner, parent_table_name, parent_table_column from dba_nested_tables where table_name = 'one of your TDE names';

               

              Then check if there's anything special about the parent table that I probably haven't allowed for in my script.

               

               

              Regards

              Jonathan Lewis

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

                I did see the nested table part after my last post. I ran the query and I wonder if the issue is related to the column being in lower case.

                 

                select parent_table_column from dba_nested_tables where table_name = 'TDE_NTwFsCHUyzOD/gQwEAAH9GXg==';
                
                
                PARENT_TABLE_COLUMN
                ------------------------------
                Currency
                
                
                1 row selected.
                
                • 20. Re: Find largest table including indexes and lobs
                  Brian.B

                  The nested table query needs to be hierarchical, since the table can nest more than once. I queried the table_name and parent_table_name with the segment_name I posted, took the parent_table_name from that and queried with that for the table_name and took that parent_table_name and queried with that for the table_name. It took 5 queries to get to the actual table_name.

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

                    Just tested for mixed case and quoted names.

                    The script still behaves.

                     

                    And I've added a nested table (not on the blog) to the IOT and it still behaves.

                     

                    Regards

                    Jonathan Lewis

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

                      Brian.B wrote:

                       

                      The nested table query needs to be hierarchical, since the table can nest more than once. I queried the table_name and parent_table_name with the segment_name I posted, took the parent_table_name from that and queried with that for the table_name and took that parent_table_name and queried with that for the table_name. It took 5 queries to get to the actual table_name.

                       

                      Argh!

                       

                      I'll have to think about that one.

                      I've often seen people trying to turn an Oracle database into a C-ISAM system, this is the first time I've seen it being turned into a hierarchical database!

                       

                      Regards

                      Jonathan Lewis

                       

                      It's probably going to take me half an hour to get the syntax of a doubly nested table right - let alone going 5 deep.

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

                        Fear not. I have the SQL. I should have mentioned that our developers use XML storage. My bad.

                         

                        SELECT nst_tab.owner,    nst_tab.parent_table_name,      SUM(nst_tab.bytes) bytes
                          FROM ( SELECT CONNECT_BY_ROOT 
                                        nst.owner                AS owner,
                                        nst.parent_table_name    AS parent_table_name,
                                        nst.parent_table_column  AS parent_table_column, 
                                        seg.bytes                AS bytes
                                   FROM dba_nested_tables nst
                                  INNER JOIN dba_segments seg
                                     ON nst.owner       = seg.owner
                                    AND nst.table_name  = seg.segment_name
                                  WHERE nst.owner       = 'MYSCHEMA'
                                    AND ( nst.owner, nst.parent_table_name )
                                    NOT IN ( SELECT owner, table_name
                                               FROM dba_nested_tables )
                                CONNECT BY PRIOR nst.owner      = nst.owner
                                             AND nst.table_name = nst.parent_table_name ) nst_tab
                         GROUP BY nst_tab.owner, nst_tab.parent_table_name
                         ORDER BY SUM( nst_tab.bytes ) DESC;
                        
                        • 24. Re: Find largest table including indexes and lobs
                          Brian.B

                          Hi Jonathan,

                           

                          I converted it to the formatting that I use and changed the four sections into WITH clauses. I also added a constant table so that I only need to change that name once.

                          Plus I changed the CAST to VARCHAR2(30), not that it matters that much, but in 11g table_name and owner are VARCHAR2(30). In my main schema I can account for

                          1,095,185 out of 1,141,649 MB. I guess losing 46,464 GB out of a little over 1.1 TB isn't terrible. It might be worth turning this into a procedure that populates a temporary

                          table and sums the table to output the results. I will work on that later.

                           

                          Hopefully someone else finds this code useful. If I account for the missing storage I will update my code.

                          Thanks for helping me to get a better handle on the amount of storage each table is using.

                           

                           

                           

                          WITH get_schema_constant
                          AS ( SELECT CAST('MYSCHEMA' AS VARCHAR2(30)) owner FROM dual ),   -- Hard code the schema name once and call it many times
                               get_table_segments                                           -- all of the table segments
                          AS ( SELECT tab.owner,
                                      CAST('TABLE'   AS VARCHAR2(30)) object_type,
                                      CASE WHEN tab.iot_type = 'IOT_OVERFLOW' 
                                           THEN tab.iot_name
                                           ELSE tab.table_name
                                       END object_name,
                                      CASE WHEN tab.iot_type = 'IOT_OVERFLOW' 
                                           THEN tab.table_name
                                       END auxiliary_name,
                                      NVL(seg_tab.bytes, 0)  bytes  
                                 FROM get_schema_constant sch, dba_tables               tab  
                                 LEFT OUTER JOIN dba_segments                           seg_tab  
                                   ON seg_tab.owner        = tab.owner
                                  AND seg_tab.segment_name = tab.table_name
                                WHERE tab.owner            = sch.owner
                                  AND NVL(tab.iot_type, 'NORMAL') != 'IOT' ),
                               get_index_segments                                          -- All index segments
                          AS ( SELECT ind.table_owner owner, 
                                      CAST('INDEX' AS VARCHAR2(30))                     object_type,
                                      NVL( nst.parent_table_name, ind.table_name )      table_name,
                                      index_name                                        auxiliary_name,
                                      NVL(seg_ind.bytes, 0)                             bytes
                                 FROM get_schema_constant sch, dba_indexes              ind  
                                 LEFT OUTER JOIN dba_nested_tables nst
                                   ON nst.owner       = ind.table_owner
                                  AND nst.table_name  = ind.table_name
                                 LEFT OUTER JOIN dba_segments                           seg_ind  
                                   ON seg_ind.owner          = ind.owner
                                  AND seg_ind.segment_name   = ind.index_name 
                                WHERE ind.table_owner        = sch.owner ),
                               get_lob_segments
                          AS ( SELECT lob.owner,                                             -- All of the lob segments
                                      CAST('LOB'       AS VARCHAR2(30))                 object_type,
                                      lob.table_name                                    table_name,
                                      lob.column_name                                   auxiliary_name,
                                      NVL(seg_lob.bytes, 0)                             bytes  
                                 FROM get_schema_constant sch, dba_lobs                 lob
                                 LEFT OUTER JOIN dba_segments                           seg_lob
                                   ON seg_lob.owner        = lob.owner
                                  AND seg_lob.segment_name = lob.segment_name
                                WHERE lob.owner            = sch.owner ),
                               get_nested_table_segments                                    -- Nested tables
                          AS ( SELECT nst_tab.owner                                     owner,
                                      CAST('NESTED'    AS VARCHAR2(30))                 object_type,
                                      nst_tab.parent_table_name                         table_name,
                                      'N/A'                                             auxiliary_name,
                          SUM(nst_tab.bytes)                                bytes
                                 FROM ( SELECT CONNECT_BY_ROOT 
                                               nst.owner                             AS owner,
                                               nst.parent_table_name                 AS parent_table_name,
                                               nst.parent_table_column               AS parent_table_column, 
                                               seg.bytes                             AS bytes
                                          FROM get_schema_constant sch, dba_nested_tables nst
                                         INNER JOIN dba_segments seg
                                            ON nst.owner       = seg.owner
                                           AND nst.table_name  = seg.segment_name
                                         WHERE nst.owner       = sch.owner
                                           AND ( nst.owner, nst.parent_table_name )
                                           NOT IN ( SELECT owner, table_name
                                                      FROM dba_nested_tables )
                                       CONNECT BY PRIOR nst.owner      = nst.owner
                                                    AND nst.table_name = nst.parent_table_name ) nst_tab
                                GROUP BY nst_tab.owner, nst_tab.parent_table_name )
                          SELECT owner, object_name, TO_CHAR( SUM(bytes) /1024/1024, '999,999,999') MB
                            FROM ( SELECT * FROM get_table_segments
                                    UNION ALL
                                   SELECT * FROM get_index_segments
                                    UNION ALL
                                   SELECT * FROM get_lob_segments
                                    UNION ALL
                          SELECT * FROM get_nested_table_segments )
                           GROUP BY owner, object_name
                           ORDER BY SUM(bytes)
                          /
                          
                          • 25. Re: Find largest table including indexes and lobs
                            Jonathan Lewis

                            Brian,

                             

                            I tested your code against my test schema and I think you're losing segment sizes from indexes on the 2nd and lower layers of nested tables.

                            I think I've got a variant that gets all of the bits (for the multiple nestings, that is) but I haven't tested it yet.

                            Will continue tomorrow.

                             

                            Regards

                            Jonathan Lewis

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

                              I am missing bits. I continue to get the TDE tables from the LOBS and INDEXES. So I guess I need more hierarchical subqueries. :-)

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

                                Brian.B wrote:

                                 

                                I am missing bits. I continue to get the TDE tables from the LOBS and INDEXES. So I guess I need more hierarchical subqueries. :-)

                                 

                                The approach I've taken is to replace the occurrences of dba_nested_tables with a CTE that  reports the "parent" table as the one at the top of the hierarchy rather than the immediate. It's a connect by query similar to yours, with the same "not in" subquery, but focuses purely on dba_nested_tables.

                                 

                                This seemed to work in the quick test I did last night - but I want to check it a little more before I publish an addendum to the blog note.


                                Regards

                                Jonathan Lewis

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

                                  I've just updated my blog post to supply code that caters for multi-level nested tables: https://jonathanlewis.wordpress.com/2019/11/06/table-space/

                                  And I've written a short note on deriving and testing the view to replace dba_nested_tables in the big query: https://jonathanlewis.wordpress.com/2019/11/09/nested-tables/

                                   

                                  It will be interesting to see if the XML implementation manages to find a nastier way of constructuing nested tables that my code doesn't yet recognise properly.


                                  Regards

                                  Jonathan Lewis

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

                                    Hi Jonathan,

                                     

                                    With your updated query, I was able to account for 95% of the space used in my largest schema. Notably, I realized that one table user 55% of the schema storage. I ran the same query in a database that I restored from 18 months ago and discovered that this table tripled in size in 18 months. This gives me the ammunition that I need to go back to development and talk about data life cycle as it relates to this table. So hopefully we can control the growth of our in-house database.

                                     

                                    Thanks