Developing and Implementing Applications, continued - The Golden Rule for Space Calculations (Page 4 of 8 )
Keep your space calculations simple, generic, and consistent across databases. There are far more productive ways to spend your work time than performing extremely detailed space calculations that Oracle may ignore anyway. Even if you follow the most rigorous sizing calculations, you cannot be sure how Oracle will load the data into the table or index.
In the following section, you’ll see how to simplify the space-estimation process, freeing you to perform much more useful DBA functions. These processes should be followed whether you are generating the default storage values for a dictionary managed tablespace or the extent sizes for locally managed tablespaces.
NOTE
In an Oracle 10g database, you should be using locally managed tablespaces. If you have upgraded from a prior release that used dictionary-managed tablespaces, you should replace them with locally managed tablespaces.
The Ground Rules for Space Calculations
Oracle follows a set of internal rules when allocating space:
- Oracle only allocates whole blocks, not parts of blocks.
- Oracle allocates sets of blocks rather than individual blocks.
- Oracle may allocate larger or smaller sets of blocks depending on the available free space in the tablespace.
Your goal should be to work with Oracle’s space-allocation methods instead of against them. If you use consistent extent sizes, you can largely delegate the space allocation to Oracle even in a dictionary-managed tablespace.
The Impact of Extent Size on Performance
There is no direct performance benefit gained by reducing the number of extents in a table. In some situations (such as in Parallel Query environments), having multiple extents in a table can significantly reduce I/O contention and enhance your performance. Regardless of the number of extents in your tables, they need to be properly sized.
Oracle reads data from tables in two ways: by RowID (usually immediately following an index access) and via full table scans. If the data is read via RowID, the number of extents in the table is not a factor in the read performance. Oracle will read each row from its physical location (as specified in the RowID) and retrieve the data.
If the data is read via a full table scan, the size of your extents can impact performance to a very small degree. When reading data via a full table scan, Oracle will read multiple blocks at a time. The number of blocks read at a time is set via the DB_FILE_MULTIBLOCK_READ_COUNT database initialization parameter and is limited by the operating system’s I/O buffer size. For example, if your database block size is 8KB and your operating system’s I/O buffer size is 128KB, you can read up to 16 blocks per read during a full table scan. In that case, setting DB_FILE_ MULTIBLOCK_READ_COUNT to a value higher than 16 will not change the performance of the full table scans.
Your extent sizes should take advantage of Oracle’s ability to perform multiblock reads during full table scans. For example, if your operating system’s I/O buffer is 128KB, your extent sizes should be a multiple of 128KB.
Consider a table that has ten extents, each of which is 128KB in size. For this example, the operating system’s I/O buffer size is 128KB. To perform a full table scan, Oracle must perform ten reads (because 128KB is the operating system I/O buffer size). If the data is compressed into a single 1280KB extent, Oracle still must perform ten reads to scan the table. Compressing the extents results in no gain in performance.
If the table’s extent size is not a multiple of the I/O buffer size, the number of reads required for full table scans may increase. For the same 1280KB table, you could create eight extents that are 160KB each. To read the first extent, Oracle will perform two reads: one for the first 128KB of the extent, and a second read for the last 32KB of the extent (reads cannot span extents). To read the whole table, Oracle must therefore perform two reads per extent, or 16 reads. Reducing the number of extents from ten to eight increases the number of reads by 60 percent.
To avoid paying a performance penalty for your extent sizes, you must choose from one of the following strategies:
- Create extents that are significantly larger than your I/O size. If the extents are very large, very few additional reads will be necessary even if the extent size is not a multiple of the I/O buffer size.
- Set DB_FILE_MULTIBLOCK_READ_COUNT to take full advantage of the I/O buffer size for your operating system. Note that setting this too high may make the optimizer think that full table scans are more efficient than they actually are, resulting in changes to existing execution plans.
- If you must create small extents, choose extent sizes that are a multiple of the I/O buffer size for your operating system.
If the I/O buffer size for your operating system is 128KB, your pool of extent sizes from which to choose are 128KB, 256KB, 512KB, 1MB, and so on. You can further reduce the pool of extent sizes from which to choose.
Use a pool of extent sizes that meets the following criterion: Every extent size will hold an integral multiple of every smaller extent size. The simplest implementation of this rule is to create extent sizes that increase by doubling: 1MB, 2MB, 4MB, 16MB, 32MB. To reduce the number of extent sizes to manage, you can quadruple the values instead of doubling: 1MB, 4MB, 16MB, and so on. Use these values for your locally managed tablespaces or rely on their automatic extent-sizing feature.
Next: Sizing the Objects >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459). Check it out today at your favorite bookstore. Buy this book now.
|
|
