Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Catherine.

Asked: August 31, 2001 - 10:24 am UTC

Last updated: November 21, 2012 - 9:41 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Please can you give me some detail on the following:
1) When a table is created is the whole of the initial extent written to disk, i.e. are even empty blocks written to disk and therefore OS blocks?
2) when data is updated in a data block will the same OS blocks be used when the data is written back to disk. So for the lifetime of an Oracle block, will it always map to the same OS block?

Thanks.

and Tom said...

1) when a tablespace is created, the blocks are formatted.

When a table grabs an extent, it gets already formatted blocks. We won't write anything to them until you put data in them.

2) that is upto the OS. I would say in general "probably" but I can think of cases where it is not so -- it depends on the underlying OS and disk software. (eg: consider an NT compressed drive for example -- it initially compresses very well -- all zeroes on the empty block. It takes very little room. When we put data on it -- it does not compress as well hence it'll take more space, it may occupy more / or different OS blocks now then before)....

NOT that it is recommended (quite the reverse) to use NT compressed drives for datafiles -- but it is an example where the block may "move". It is totally upto the filesystem driver. Logically speaking -- the block never moves, physically-- it is beyond our control.


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 03, 2001 - 4:37 am UTC

Thanks. I'm a lot clearer about how things work now!

about OS blocks

Andrea, April 20, 2007 - 10:50 am UTC

Hi Tom.

A question about Oracle block size.
You often says that for good performance database block size should be made equal to or a multiple of the OS block size.
Consider the following architectural configuration:
Oracle block size (for ex. 8k) smaller than OS block size (for ex. 64k).
Can you explain how a database can be affected by this configuration ?

Thanks in advance.
Tom Kyte
April 20, 2007 - 1:20 pm UTC

do you have an OS with such a capability.

DB block size smaller than OS block size

A reader, April 23, 2007 - 3:17 am UTC

Tom,

please clarify your claim.

The Operative System is SunOS 5.9.
the output of "df ¿g" statement (we are interested in /u01, /u02, /03 file systems) follows:

/ (/dev/md/dsk/d100 ): 8192 block size 1024 frag size

/proc (/proc ): 512 block size 512 frag size

/etc/mnttab (mnttab ): 512 block size 512 frag size

/dev/fd (fd ): 1024 block size 1024 frag size

/var (/dev/md/dsk/d104 ): 8192 block size 1024 frag size

/var/run (swap ): 8192 block size 8192 frag size

/dat (/dev/md/dsk/d300 ): 8192 block size 1024 frag size

/tmp (/dev/md/dsk/d106 ): 8192 block size 1024 frag size

/opt (/dev/md/dsk/d105 ): 8192 block size 1024 frag size

/backup (172.30.117.53:/fs_sviluppidb/database/backup): 8192 block size 512 frag size

/global/.devices/node@1(/dev/md/dsk/d203 ): 8192 block size 1024 frag size

/global/.devices/node@3(/dev/md/dsk/d303 ): 8192 block size 1024 frag size

/global/.devices/node@2(/dev/md/dsk/d103 ): 8192 block size 1024 frag size

/u03 (qfs3 ): 65536 block size 1024 frag size

/u02 (qfs2 ): 65536 block size 1024 frag size

/u01 (qfs1 ): 65536 block size 1024 frag size



The size of DB block instead:

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_size integer 8192


When Oracle writes or reads a block on disk, it works on 8k or 64k?

Andrea
Tom Kyte
April 23, 2007 - 4:30 pm UTC

8k, Oracle will request all IO's 8k at a time.

block -> extent -> segment ... object

DUR, November 21, 2012 - 7:59 am UTC

Hi Tom,

I'm confused after reading Logical Storage Structures chapter of Oracle Database Concepts 11g Release 2 (11.2).
There ( http://docs.oracle.com/cd/E11882_01/server.112/e25789/logical.htm ) are following informations:
1. Figure http://docs.oracle.com/cd/E11882_01/server.112/e25789/img_text/cncpt227.htm showing the relationship of segment to extent to oracle data block as one-to-many.
2. "Multiple tables can store rows in the same block" in the Data Block Overhead section
3. "A single data segment in a database stores the data for one user object" in the Overview of Segments section.

So, how can it be one-to-many relationship ? What about those blocks which contain rows of multiple tables? Does it happen only with row migration ? Or maybe I'm missing something ;)

Could you please comment on it ?
Tom Kyte
November 21, 2012 - 9:41 am UTC

when you use a cluster, IT (the cluster) is the segment, the table is not.

tables are not always the segment.

if you partition a table, the table has no segment - the partitions are the segments.

if you create a cluster with many tables in it, the cluster is the segment - the tables do not have a segment.


so, a block belongs to an extent, an extent belongs to a segment, a segment is associated with some object like a table, a cluster, a partition, an index and so on.
ops$tkyte%ORA11GR2> select distinct segment_type from dba_segments order by 1;

SEGMENT_TYPE
------------------
CLUSTER
INDEX
INDEX PARTITION
LOB PARTITION
LOBINDEX
LOBSEGMENT
NESTED TABLE
ROLLBACK
TABLE
TABLE PARTITION
TABLE SUBPARTITION
TYPE2 UNDO

12 rows selected.



there are many segment types...

:)

DUR, November 22, 2012 - 2:40 am UTC

Thank you, Tom. Now it's obvious...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.