CODEWITHSUNDEEP
oracle-databasedatabase-administrationsegment
Caffeinated
Caffeinated

Reputation: 12484

Segments, Extents, and Data Blocks in the Oracle Database Architecture , how to use?

In Oracle Database Architecture, there are Segments, Extents, and Data Blocks.

enter image description here

But when when would I , as a programmer, need to know about these things? How are they useful for me, say - when I'm testing out Oracle memory management.

Is there a way to query these memory units? Or is it mainly just for the dba side? thanks

Upvotes: 3

Views: 1999

Answers (1)

mrjoltcola
mrjoltcola

Reputation: 20842

It is good to know the underlying physical storage architecture, as it may allow you to design and write more efficient database applications; but it isn't strictly necessary.

Segments, extents and blocks are an implementation detail.

That said, you should know how to analyze them, and you can see them without DBA privileges, use the USER_SEGMENTS and USER_EXTENTS views for starters.

Segments are useful to see the low level storage of your tables / indexes. You should know how big your tables are.

Here is an example use for starters to get an understanding of the objects in a database or tablespace.

SQL> select segment_type, segment_name, bytes from user_segments
where segment_type in ('TABLE', 'INDEX') order by bytes desc


SEGMENT_TYPE       SEGMENT_NAME                                       BYTES
------------------ --------------------------------------------------------------------------------- ----------
TABLE          BIG                                            209715200
TABLE          FAKE                                         4194304
TABLE          MD_TRIGGERS                                       262144
TABLE          MD_DERIVATIVES                                    196608
INDEX          IX_ZZ_NVL                                         196608
TABLE          MD_PRIVILEGES                                     196608
INDEX          MD_COLUMNS_PERF_IDX                                   131072
INDEX          MD_DERIVATIVES_PERF_IDX1                              131072
...

One particular use of this query is to study before and after footprint of tables and indexes to see how effective a rebuild was. For example you may observe that you have hundreds of tiny tables that are in a large extent tablespace, you can rebuild those tables with smaller initial extents, and use the segment view to see how well that works.

I could find which tablespace they are in and decide that BIG might be a good table to move out of the USERS tablespace into a larger extent tablespace.

SQL> select segment_type, tablespace_name, segment_name, bytes from user_segments
  where segment_type in ('TABLE', 'INDEX') order by bytes desc;
    2  
SEGMENT_TYPE       TABLESPACE_NAME        SEGMENT_NAME                                       BYTES
------------------ ------------------------------ --------------------------------------------------------------------------------- ----------
TABLE          USERS              BIG                                            209715200
TABLE          USERS              FAKE                                             4194304
TABLE          USERS              MD_TRIGGERS                                       262144
TABLE          USERS              MD_DERIVATIVES                                    196608

Upvotes: 4

Related Questions