Database News You Can Use
A monthly newsletter for Oracle users
You’re invited to submit your Oracle-related questions to us at email@example.com. Please include your name and telephone number in your email.
This month’s question comes to us from David M. in Boston: Does it matter how many extents we have if we’re using locally managed tablespaces?
Terry Sutton of the Database Specialists team responds: It matters some, but not as much as it did with dictionary managed tablespaces. We no longer have data dictionary fragmentation in the UET$ and FET$ tables, and that is one of the biggest reasons to use locally managed tablespaces (LMTs). Having hundreds or even thousands of extents won’t cause huge performance problems. But there are still reasons to not have a large number of extents for a segment.
In Oracle 8i, tablespace quotas are still stored in the data dictionary and updated on an extent-by-extent basis when a segment is extended, dropped, or truncated. (This seems not to be the case in Oracle 9i and later.) Also, full table scans incur one additional logical read per 10 extents. I’m not sure why this is the case, but others have reported on it and our testing has borne this out. While this isn’t a huge overhead, it can be unnecessary.
And queries about the extents can still require a lot of I/O if there are a lot of extents. To quote Steve Adams:
Extents are only recorded in the extent map in the segment header block of each segment, and if necessary, in additional extent map blocks within the segments. This is a great blessing in every respect but one—namely that this information is not cached in the dictionary cache. It must be obtained from the database blocks every time that it is required, and if those blocks are not in the buffer cache, that involves I/O and potentially lots of it. For example, a query against the DBA_EXTENTS view could well have to read every segment header and every additional extent map block in the entire database. For this reason, we recommend that the number of extents per segment in locally managed tablespaces be limited to the number of rows that can be accommodated in the extent map within the segment header block—that is, approximately (db_block_size / 16) – 7.
So the performance implications of lots of extents in locally managed tablespaces aren’t as significant as with dictionary managed tablespaces, but they are still there. In addition, if you use a GUI tool (such as Enterprise Manager) to monitor your database, the display of thousands of extents for a segment can be less than useful.
For these reasons, we almost always use the AUTOALLOCATE option when creating locally managed tablespaces. This option, available since Oracle 8i, lets Oracle determine how big to make each extent for a segment. The first several extents will be 64 Kb, the next several will be 1 Mb, the next several will be 8 Mb, and so on. A small number of standard extent sizes are always used. Thus when a segment is dropped, the free space “holes” are sized appropriately for reuse by other segments.
The great benefit of AUTOALLOCATE is that you don’t have to know how large your tables and indexes are going to be. If they’re small, they have a few small extents. If they’re large, they have some small extents, some medium extents, and some large extents. But you won’t have unusable wasted space if the segment is dropped, and you won’t have segments with thousands of extents (unless your segments are very, very large, in which case you ought to partition them). If all your tablespaces are AUTOALLOCATE, you don’t have to worry about someone putting a new segment into the wrong tablespace and winding up with tens of thousands of extents (or alternatively putting a table with 128 Kb of data into the tablespace with 1Gb extents, wasting an entire gigabyte for a small table).
To summarize, new tablespaces should be created as locally managed tablespaces with the AUTOALLOCATE feature in almost all cases. This is also the default behavior when creating tablespaces in Oracle 10g. (I would like to thank Steve Adams, Julian Dyke, and Connor McDonald for their observations which were incorporated into this article.)
“Security and audit, to be effective, must be an integral part of an application,” writes Lewis R. Cunningham in his 15-page white paper entitled: Oracle 10g Security and Audit. He goes on to explain that “the Oracle 10g database provides a rich set of features to secure and audit an application.” Cunningham offers a survey of Oracle security features, covering authentication, authorization, data access, and auditing. He defines these terms, discusses Oracle features such as Row Level Security, Virtual Private Database, Fine Grained Audit, Fine Grained Access Control, and also provides basic examples. Although “10g” appears in the paper’s title, many of the features discussed are available in Oracle 9i as well.
The paper also includes a useful list of 14 best practices to follow when it comes to Oracle security. Check it out at http://oracle.ittoolbox.com/white-papers/oracle-10g-security-and-audit-3792.
Database Specialists, Inc. senior staff consultant Terry Sutton has been invited to join an exclusive list of recognized leaders in the Oracle industry featured at the fourth annual Hotsos Symposium. The conference—dedicated to issues of Oracle system performance—takes place March 6-9 in Dallas, Texas. This is the second year Terry has been invited to participate as a presenter at the Hotsos Symposium. He will be discussing optimizer statistics, various options for gathering them, and their effects on performance.
Former Oracle Corp. president Ray Lane will be the keynote speaker at the event. In addition, many well-known authors and speakers will present material on topics such as performance analysis, RAC, capacity planning, and using Statspack.
For more information about the event, see https://portal.hotsos.com/events/SYM06. Advance registration discounts apply until February 18, 2006.
For the second year in a row, Database Specialists president Roger Schrag has been elected to the position of Director of Conference Programming for the 400+ member Northern California Oracle Users Group (NoCOUG). Roger is entering his sixth year on the board of directors for NoCOUG, an organization that is dedicated to offering education, information sharing, and networking opportunities.
Lisa Loper, Database Specialists VP of Operations, was elected as the Vendor Coordinator after serving on the board of directors for four years as the NoCOUG Journal Editor. “Database Specialists still places a high value on contributing to the Oracle users community. Spending time volunteering with NoCOUG is one of the ways we do that—plus it’s enjoyable to be a part of such a great volunteer-run organization,” said Loper. For more information about NoCOUG, visit http://www.nocoug.org .