MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Matching LOB Indexes and Segments

with 7 comments

I enjoyed Tom Kyte’s example of how you find matching a LOB Index to Segment (Expert Oracle Database Architecture, pp. 542). I’ve noticed variations of it posted in various locations. While it works well for sample scheme that have only one LOB, the following works for any number of LOBs in any schema. This simplifies working with system- and user-defined segment names. The first CASE statement ensures that joins between user-named segment names are possible. The second CASE statement ensures two things: (a) Joins between system generated segment names don’t throw an error when matching unrelated system generated return values found in the DBA_SEGEMENTS view; and (b) Joins between named segments are possible and don’t throw an error.

COL owner         FORMAT A5  HEADING "Owner"
COL TABLE_NAME    FORMAT A5  HEADING "Table|Name"
COL column_name   FORMAT A10 HEADING "Column|Name"
COL segment_name  FORMAT A26 HEADING "Segment Name"
COL segment_type  FORMAT A10 HEADING "Segment|Type"
COL bytes                    HEADING "Segment|Bytes"
 
SELECT   l.owner
,        l.TABLE_NAME
,        l.column_name
,        s.segment_name
,        s.segment_type
,        s.bytes
FROM     dba_lobs l
,        dba_segments s
WHERE    REGEXP_SUBSTR(l.segment_name,'([[:alnum:]]|[[:punct:]])+'
, CASE
    WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0
    THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1)
    ELSE 1
  END) =
REGEXP_SUBSTR(s.segment_name,'([[:alnum:]]|[[:punct:]])+'
, CASE
    WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0
    THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1)
    ELSE 1
  END)
AND      l.TABLE_NAME = UPPER('&table_name')
AND      l.owner = UPPER('&owner')
ORDER BY l.column_name, s.segment_name;

It produces the following type of output:

Table  Column                               Segment      Segment
Name   Name       Segment Name              Type           Bytes
------ ---------- ------------------------- ---------- ---------
PLSQL  ITEM_BLOB  SYS_IL0000074435C00007$$  LOBINDEX      65,536
PLSQL  ITEM_BLOB  SYS_LOB0000074435C00007$$ LOBSEGMENT 2,097,152
PLSQL  ITEM_DESC  SYS_IL0000074435C00006$$  LOBINDEX      65,536
PLSQL  ITEM_DESC  SYS_LOB0000074435C00006$$ LOBSEGMENT   720,896

This should help you monitor growth of LOBs in your database. The maximum CHUNK size is 32.768. If you elect a value greater than that you raise an ORA-22851 error.

Written by maclochlainn

April 17th, 2008 at 6:16 pm

7 Responses to 'Matching LOB Indexes and Segments'

Subscribe to comments with RSS or TrackBack to 'Matching LOB Indexes and Segments'.

  1. Hi Michael!
    I really appreciate your effort to share your experience and kwnoledge with other Oracle enthusiats. With this and others articles incl. “Cleaning up wasted LOB space” you helped me a lot.

    Thanks and keep up the good work!

    Best regards

    Dejan

  2. Thanks for tips on LOBs. Here is another approach to getting the size of the lob and lobindex. I think the dba_lobs table is new on Oracle 10.

    SELECT tablespace_name, TABLE_NAME, column_name, 
           ds1.bytes lob_size,
           ds1.bytes index_size
      FROM dba_lobs dl
           INNER JOIN dba_segments ds1 ON ds1.segment_name = dl.segment_name
           INNER JOIN dba_segments ds2 ON ds2.segment_name = dl.index_name;

    Mike Madland

    17 Mar 09 at 4:08 pm

  3. This is quite up-to-date information. I think I’ll share it on Delicious.

    Liza

    24 Apr 09 at 5:06 am

  4. [...] should first check space, by using the query provided earlier in my blog to compare LOB indexes and segments. [...]

  5. Unfortunately the second query not only is a Cartesian join but it’s incorrect too.

    Alex

    13 Jul 11 at 8:14 pm

  6. Alex,

    I’m not sure why you think it’s a Cartesian join or incorrect, but it’s neither. Here’s the output from DBMS_XPLAN on it:

    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------
    Plan hash VALUE: 311028041
     
    ------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                 | Name           | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                          |                |    77 |   167K|  3610   (1)| 00:00:44 |
    |   1 |  SORT ORDER BY                            |                |    77 |   167K|  3610   (1)| 00:00:44 |
    |   2 |   NESTED LOOPS                            |                |    77 |   167K|  3609   (1)| 00:00:44 |
    |   3 |    VIEW                                   | DBA_LOBS       |     2 |  4106 |    26   (0)| 00:00:01 |
    |   4 |     UNION-ALL                             |                |       |       |            |       |
    |   5 |      NESTED LOOPS                         |                |     1 |   196 |    13   (0)| 00:00:01 |
    |   6 |       NESTED LOOPS                        |                |     1 |   191 |    12   (0)| 00:00:01 |
    |   7 |        NESTED LOOPS OUTER                 |                |     1 |   161 |    10   (0)| 00:00:01 |
    |   8 |         NESTED LOOPS OUTER                |                |     1 |   119 |     9   (0)| 00:00:01 |
    |   9 |          NESTED LOOPS                     |                |     1 |   116 |     8   (0)| 00:00:01 |
    |  10 |           NESTED LOOPS                    |                |     9 |   846 |     7   (0)| 00:00:01 |
    |  11 |            NESTED LOOPS                   |                |     1 |    70 |     6   (0)| 00:00:01 |
    |  12 |             NESTED LOOPS                  |                |     1 |    58 |     5   (0)| 00:00:01 |
    |  13 |              NESTED LOOPS                 |                |     1 |    22 |     2   (0)| 00:00:01 |
    |  14 |               TABLE ACCESS BY INDEX ROWID | USER$          |     1 |    19 |     1   (0)| 00:00:01 |
    |* 15 |                INDEX UNIQUE SCAN          | I_USER1        |     1 |       |     0   (0)| 00:00:01 |
    |  16 |               TABLE ACCESS CLUSTER        | TS$            |     1 |     3 |     1   (0)| 00:00:01 |
    |* 17 |                INDEX UNIQUE SCAN          | I_TS#          |     1 |       |     0   (0)| 00:00:01 |
    |* 18 |              TABLE ACCESS BY INDEX ROWID  | OBJ$           |     1 |    36 |     3   (0)| 00:00:01 |
    |* 19 |               INDEX RANGE SCAN            | I_OBJ2         |     1 |       |     2   (0)| 00:00:01 |
    |* 20 |             TABLE ACCESS CLUSTER          | TAB$           |     1 |    12 |     1   (0)| 00:00:01 |
    |* 21 |              INDEX UNIQUE SCAN            | I_OBJ#         |     1 |       |     0   (0)| 00:00:01 |
    |* 22 |            TABLE ACCESS CLUSTER           | COL$           |    11 |   264 |     1   (0)| 00:00:01 |
    |* 23 |             INDEX UNIQUE SCAN             | I_OBJ#         |     1 |       |     0   (0)| 00:00:01 |
    |* 24 |           TABLE ACCESS CLUSTER            | LOB$           |     1 |    22 |     1   (0)| 00:00:01 |
    |* 25 |            INDEX UNIQUE SCAN              | I_OBJ#         |     1 |       |     0   (0)| 00:00:01 |
    |  26 |          TABLE ACCESS CLUSTER             | TS$            |     1 |     3 |     1   (0)| 00:00:01 |
    |* 27 |           INDEX UNIQUE SCAN               | I_TS#          |     1 |       |     0   (0)| 00:00:01 |
    |* 28 |         TABLE ACCESS CLUSTER              | ATTRCOL$       |     1 |    42 |     1   (0)| 00:00:01 |
    |  29 |        TABLE ACCESS BY INDEX ROWID        | OBJ$           |     1 |    30 |     2   (0)| 00:00:01 |
    |* 30 |         INDEX RANGE SCAN                  | I_OBJ1         |     1 |       |     1   (0)| 00:00:01 |
    |* 31 |       INDEX RANGE SCAN                    | I_OBJ1         |     1 |     5 |     1   (0)| 00:00:01 |
    |  32 |      NESTED LOOPS                         |                |       |       |            |       |
    |  33 |       NESTED LOOPS                        |                |     1 |   202 |    13   (0)| 00:00:01 |
    |  34 |        NESTED LOOPS                       |                |     1 |   172 |    11   (0)| 00:00:01 |
    |  35 |         NESTED LOOPS                      |                |     1 |   167 |    10   (0)| 00:00:01 |
    |  36 |          NESTED LOOPS                     |                |     1 |   155 |     9   (0)| 00:00:01 |
    |  37 |           NESTED LOOPS OUTER              |                |     1 |   122 |     7   (0)| 00:00:01 |
    |  38 |            NESTED LOOPS                   |                |     1 |    80 |     6   (0)| 00:00:01 |
    |  39 |             NESTED LOOPS                  |                |     1 |    56 |     5   (0)| 00:00:01 |
    |  40 |              NESTED LOOPS OUTER           |                |     1 |    37 |     4   (0)| 00:00:01 |
    |  41 |               NESTED LOOPS                |                |     1 |    34 |     3   (0)| 00:00:01 |
    |  42 |                TABLE ACCESS BY INDEX ROWID| USER$          |     1 |    16 |     1   (0)| 00:00:01 |
    |* 43 |                 INDEX UNIQUE SCAN         | I_USER1        |     1 |       |     0   (0)| 00:00:01 |
    |  44 |                TABLE ACCESS FULL          | PARTLOB$       |     1 |    18 |     2   (0)| 00:00:01 |
    |  45 |               TABLE ACCESS CLUSTER        | TS$            |     1 |     3 |     1   (0)| 00:00:01 |
    |* 46 |                INDEX UNIQUE SCAN          | I_TS#          |     1 |       |     0   (0)| 00:00:01 |
    |  47 |              TABLE ACCESS BY INDEX ROWID  | LOB$           |     1 |    19 |     1   (0)| 00:00:01 |
    |* 48 |               INDEX UNIQUE SCAN           | I_LOB2         |     1 |       |     0   (0)| 00:00:01 |
    |* 49 |             TABLE ACCESS CLUSTER          | COL$           |     1 |    24 |     1   (0)| 00:00:01 |
    |* 50 |            TABLE ACCESS CLUSTER           | ATTRCOL$       |     1 |    42 |     1   (0)| 00:00:01 |
    |* 51 |           TABLE ACCESS BY INDEX ROWID     | OBJ$           |     1 |    33 |     2   (0)| 00:00:01 |
    |* 52 |            INDEX RANGE SCAN               | I_OBJ1         |     1 |       |     1   (0)| 00:00:01 |
    |* 53 |          TABLE ACCESS CLUSTER             | TAB$           |     1 |    12 |     1   (0)| 00:00:01 |
    |* 54 |           INDEX UNIQUE SCAN               | I_OBJ#         |     1 |       |     0   (0)| 00:00:01 |
    |* 55 |         INDEX RANGE SCAN                  | I_OBJ1         |     1 |     5 |     1   (0)| 00:00:01 |
    |* 56 |        INDEX RANGE SCAN                   | I_OBJ1         |     1 |       |     1   (0)| 00:00:01 |
    |  57 |       TABLE ACCESS BY INDEX ROWID         | OBJ$           |     1 |    30 |     2   (0)| 00:00:01 |
    |* 58 |    VIEW                                   | SYS_DBA_SEGS   |    38 |  6460 |  1791   (1)| 00:00:22 |
    |  59 |     UNION-ALL                             |                |       |       |            |       |
    |* 60 |      HASH JOIN RIGHT OUTER                |                |  1770 |   259K|  1700   (1)| 00:00:21 |
    |  61 |       INDEX FULL SCAN                     | I_USER2        |   107 |   428 |     1   (0)| 00:00:01 |
    |* 62 |       HASH JOIN                           |                |  1770 |   252K|  1698   (1)| 00:00:21 |
    |* 63 |        HASH JOIN                          |                |  1770 |   186K|  1458   (1)| 00:00:18 |
    |  64 |         TABLE ACCESS FULL                 | TS$            |     7 |    49 |     4   (0)| 00:00:01 |
    |  65 |         NESTED LOOPS                      |                |  1770 |   174K|  1453   (1)| 00:00:18 |
    |* 66 |          HASH JOIN                        |                |  1770 |   164K|  1453   (1)| 00:00:18 |
    |  67 |           TABLE ACCESS FULL               | SEG$           |  7950 |   201K|    64   (0)| 00:00:01 |
    |  68 |           VIEW                            | SYS_OBJECTS    |  8451 |   569K|  1388   (1)| 00:00:17 |
    |  69 |            UNION-ALL                      |                |       |       |            |       |
    |* 70 |             TABLE ACCESS FULL             | TAB$           |  2782 | 63986 |   345   (0)| 00:00:05 |
    |  71 |             TABLE ACCESS FULL             | TABPART$       |   206 |  3296 |     4   (0)| 00:00:01 |
    |  72 |             TABLE ACCESS FULL             | CLU$           |    10 |   140 |   345   (0)| 00:00:05 |
    |* 73 |             TABLE ACCESS FULL             | IND$           |  4291 | 81529 |   345   (0)| 00:00:05 |
    |  74 |             TABLE ACCESS FULL             | INDPART$       |   365 |  5840 |     5   (0)| 00:00:01 |
    |* 75 |             TABLE ACCESS FULL             | LOB$           |   794 | 16674 |   337   (0)| 00:00:05 |
    |  76 |             TABLE ACCESS FULL             | TABSUBPART$    |     1 |    52 |     2   (0)| 00:00:01 |
    |  77 |             TABLE ACCESS FULL             | INDSUBPART$    |     1 |    52 |     2   (0)| 00:00:01 |
    |  78 |             TABLE ACCESS FULL             | LOBFRAG$       |     1 |    17 |     2   (0)| 00:00:01 |
    |* 79 |          INDEX UNIQUE SCAN                | I_FILE2        |     1 |     6 |     0   (0)| 00:00:01 |
    |  80 |        TABLE ACCESS FULL                  | OBJ$           | 70879 |  2630K|   240   (1)| 00:00:03 |
    |* 81 |      HASH JOIN OUTER                      |                |     7 |   581 |    19   (6)| 00:00:01 |
    |* 82 |       HASH JOIN                           |                |     7 |   553 |    18   (6)| 00:00:01 |
    |  83 |        NESTED LOOPS                       |                |     7 |   504 |    13   (0)| 00:00:01 |
    |  84 |         NESTED LOOPS                      |                |     7 |   462 |    13   (0)| 00:00:01 |
    |* 85 |          TABLE ACCESS FULL                | UNDO$          |    11 |   407 |     2   (0)| 00:00:01 |
    |* 86 |          TABLE ACCESS CLUSTER             | SEG$           |     1 |    29 |     1   (0)| 00:00:01 |
    |* 87 |           INDEX UNIQUE SCAN               | I_FILE#_BLOCK# |     1 |       |     0   (0)| 00:00:01 |
    |* 88 |         INDEX UNIQUE SCAN                 | I_FILE2        |     1 |     6 |     0   (0)| 00:00:01 |
    |  89 |        TABLE ACCESS FULL                  | TS$            |     7 |    49 |     4   (0)| 00:00:01 |
    |  90 |       INDEX FULL SCAN                     | I_USER2        |   107 |   428 |     1   (0)| 00:00:01 |
    |* 91 |      HASH JOIN RIGHT OUTER                |                |  2066 |   108K|    73   (3)| 00:00:01 |
    |  92 |       INDEX FULL SCAN                     | I_USER2        |   107 |   428 |     1   (0)| 00:00:01 |
    |* 93 |       HASH JOIN                           |                |  2066 |   100K|    71   (2)| 00:00:01 |
    |  94 |        TABLE ACCESS FULL                  | TS$            |     7 |    49 |     4   (0)| 00:00:01 |
    |* 95 |        HASH JOIN                          |                |  2066 | 88838 |    67   (2)| 00:00:01 |
    |  96 |         TABLE ACCESS FULL                 | FILE$          |     5 |    45 |     2   (0)| 00:00:01 |
    |* 97 |         TABLE ACCESS FULL                 | SEG$           |  2066 | 70244 |    64   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------------
     
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
      15 - access("U"."NAME"='STUDENT')
      17 - access("U"."TEMPTS#"="TS1"."TS#")
      18 - FILTER(BITAND("O"."FLAGS",128)=0)
      19 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"='ITEM')
      20 - FILTER(BITAND("TA"."PROPERTY",32)32)
      21 - access("O"."OBJ#"="TA"."OBJ#")
      22 - FILTER(BITAND("C"."PROPERTY",32768)32768)
      23 - access("O"."OBJ#"="C"."OBJ#")
      24 - FILTER("C"."INTCOL#"="L"."INTCOL#")
      25 - access("C"."OBJ#"="L"."OBJ#")
      27 - access("L"."TS#"="TS"."TS#"(+))
      28 - FILTER("C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+))
      30 - access("L"."LOBJ#"="LO"."OBJ#")
      31 - access("L"."IND#"="IO"."OBJ#")
      43 - access("U"."NAME"='STUDENT')
      46 - access("PLOB"."DEFTS#"="TS1"."TS#"(+))
      48 - access("L"."LOBJ#"="PLOB"."LOBJ#")
      49 - FILTER(BITAND("C"."PROPERTY",32768)32768 AND "C"."OBJ#"="L"."OBJ#" AND "C"."INTCOL#"="L"."INTCOL#")
      50 - FILTER("C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+))
      51 - FILTER("O"."NAME"='ITEM')
      52 - access("O"."OBJ#"="C"."OBJ#" AND "O"."OWNER#"="U"."USER#")
      53 - FILTER(BITAND("TA"."PROPERTY",32)=32)
      54 - access("O"."OBJ#"="TA"."OBJ#")
      55 - access("L"."IND#"="IO"."OBJ#")
      56 - access("L"."LOBJ#"="LO"."OBJ#")
      58 - FILTER( REGEXP_SUBSTR ("L"."SEGMENT_NAME",'([[:alnum:]]|[[:punct:]])+',CASE  WHEN REGEXP_INSTR ("SEGMENT_NAME",'[[:digit:]]',1)>0 THEN  REGEXP_INSTR ("SEGMENT_NAME",'[[:digit:]]',1) ELSE 1 END )= REGEXP_SUBSTR ("SEGMENT_NAME",'([[:alnum:]]|[[:punct:]])+',CASE  WHEN  REGEXP_INSTR("SEGMENT_NAME",'[[:digit:]]',1)>0 THEN  REGEXP_INSTR ("SEGMENT_NAME",'[[:digit:]]',1) ELSE 1 END ))
      60 - access("O"."OWNER#"="U"."USER#"(+))
      62 - access("O"."OBJ#"="SO"."OBJECT_ID" AND "O"."TYPE#"="SO"."OBJECT_TYPE_ID")
      63 - access("S"."TS#"="TS"."TS#")
      66 - access("S"."FILE#"="SO"."HEADER_FILE" AND "S"."BLOCK#"="SO"."HEADER_BLOCK" AND "S"."TS#"="SO"."TS_NUMBER" AND "S"."TYPE#"="SO"."SEGMENT_TYPE_ID")
      70 - FILTER(BITAND("T"."PROPERTY",1024)=0)
      73 - FILTER("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=8 OR "I"."TYPE#"=9)
      75 - FILTER(BITAND("L"."PROPERTY",64)=0 OR BITAND("L"."PROPERTY",128)=128)
      79 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#")
      81 - access("S"."USER#"="U"."USER#"(+))
      82 - access("S"."TS#"="TS"."TS#")
      85 - FILTER("UN"."STATUS$"1)
      86 - FILTER("S"."TYPE#"=1 OR "S"."TYPE#"=10)
      87 - access("S"."TS#"="UN"."TS#" AND "S"."FILE#"="UN"."FILE#" AND "S"."BLOCK#"="UN"."BLOCK#")
      88 - access("UN"."TS#"="F"."TS#" AND "UN"."FILE#"="F"."RELFILE#")
      91 - access("S"."USER#"="U"."USER#"(+))
      93 - access("S"."TS#"="TS"."TS#")
      95 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#")
      97 - FILTER("S"."TYPE#"6 AND "S"."TYPE#"5 AND "S"."TYPE#"8 AND "S"."TYPE#"10 AND "S"."TYPE#"1)
     
    159 ROWS selected.

    maclochlainn

    13 Jul 11 at 10:24 pm

  7. I found that the best way to reclaim all the space of a LOB (and to rebuild its LOBINDEX) is to move it to a new tablespace:

    ALTER TABLE o.t move LOB (blobcol) STORE AS (TABLESPACE ts);

    Very simple, fast and efficient

    Eric Bergeron

    28 Feb 12 at 12:58 pm

Leave a Reply