Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 1.8 December 12, 2000 TABLE OF CONTENTS 1) Pop Quiz 2) This Issue's URLs 3) Oracle Caching 4) Answer to the Pop Quiz 5) Did you know... --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What's the difference among tablespaces, datafiles, segments, extents, and data blocks in Oracle? --------------------------------------------------------- 2) THIS ISSUE'S URLs --------------------------------------------------------- Oracle has a product called JDeveloper for creating and editing Java-based applications. It's available for download at http://www.oracle.com/ip/develop/ids/index.html?jdeveloper.html. Other Java editors include: - Borland's JBuilder: http://www.inprise.com/jbuilder/ - IBM's VisualAge: http://www7.software.ibm.com/vad.nsf/ --------------------------------------------------------- 3) Oracle Caching --------------------------------------------------------- In the last issue of the Oracle Pro eXTRA, I discussed the issue of caching Oracle data. A number of you sent me additional tips. Thank you all for your feedback. There's a query hint that allows Oracle to cache a table, as shown here: SELECT /*+ CACHE(scott_emp) */ ename FROM scott.emp scott_emp; In addition, there's a CACHE clause in the CREATE TABLE or ALTER TABLE command: create table tmp_tbl (tmp_id number, tmp_desc varchar2(80)) cache; In this scenario, Oracle will place data blocks at the MRU (most recently used) end of the database buffer cache, such that the table data can remain in memory for as long as possible. As far as full table scans are concerned, Oracle by default bypasses the LRU algorithm and immediately places the data blocks returned at the LRU end of the database buffer cache. In other words, data blocks returned by full table scans will be swapped out of memory as soon as the query is done. As a result, it's definitely better to use the methods detailed above and in the previous issue of the eXTRA for caching a table in Oracle. Thank you once again for your input. --------------------------------------------------------- 4) ANSWER TO THE POP QUIZ --------------------------------------------------------- A datafile is an operating system file used by Oracle to store data. You can list the datafiles that belong to a database by issuing the following query: SQL> select * from dba_tablespaces; Note that you should log in as the Oracle DBA (i.e., the SYSTEM database account) when executing this query. * * * * * A tablespace is made up of one of more datafiles. It's the storage structure on which tables, indexes, and other database objects are created. It can be considered a "logical flat space," spanning one or more physical physical datafiles. Issue the following query to retrieve the list of datafiles: SQL> select * from dba_data_files order by tablespace_name; In the query, you'll be able to determine the location of the datafiles and the tablespace to which each datafile belongs. * * * * * A segment is a database object that requires storage. Oracle supports various types of segments, including data segments (for tables), index segments (for indexes), and rollback segments. SQL> select * from dba_segments order by segment_name; The preceding query will display all of the segments belonging to a database. To find out the segments for the currently connected user, enter: SQL> select * from user_segments order by segment_name; Note that in these data dictionary views for segments, there are several useful columns, namely: * segment_name - name of the database segment * segment_type - e.g., data segment, index segment, rollback segment * tablespace_name - tablespace on which the segment resides * extents - number of extents allocated to the segment * bytes - total size of the segment To find out the number of segments by segment type, run the query: SQL> select segment_type, count(*) from user_segments order by segment_type; Certain database objects such as database sequences and stored procedures don't require their own storage; as a result, they don't have a corresponding segment. In general, a table typically has one and only one corresponding data segment; however, this is no longer the case in Oracle8. A database column with a datatype of LOB (large objects) has its own separate data and index segments, in addition to the data segment belonging to the table. A partitioned table, another new feature of Oracle8, has separate data and index segments for each of its partitions. * * * * * An extent is the space allocated to an object (e.g., a table) to meet a storage allocation request. When a table is first created, its storage clause specifies the size of the initial allocation---i.e., initial extent. This space is allocated to the table even though the table might contain no rows. After table creation, rows may be added to the table with the INSERT statement. Oracle will first try to use the space already allocated to the table when locating space for the INSERT statement. If it's not able to do so, Oracle has to resort to dynamic extension (i.e., assigning a new extent to the table). The size of a particular extent is determined by the storage clause of the table. Issue the following query to find out the storage parameters: SQL> select table_name, initial_extent, next_extent, min_extents, max_extents, pct_increase from user_tables order by table_name; Using this formula, the size of each of the extents is as follows: extent 1 = initial_extent extent 2 = next_extent extent 3 = next_extent * (1 + (pct_increase/100)) extent 4 = extent 3 * (1 + (pct_increase/100)) extent 5 = ... The maximum number of extents allowed is determined by the max_extents column. To find out the actual size of a table, you can refer to the segments view described earlier. To find out the full listing of extents allocated to a table, issue the following query, where 'EMP' refers to the name of the table: SQL> select * from user_extents where segment_name = 'EMP'; Each extent, by definition, is made up of contiguous data blocks (data blocks that are next to each other). Free extents information can be retrieved by the following query: SQL> select * from dba_free_space where tablespace_name = 'USER' order by bytes desc; Each row displayed corresponds to one free extent that exists in the USER tablespace. The largest extent (the row with the largest size), not the total size of all free extents, determines the size of the largest possible extent for the tablespace. If you request space that's larger than the largest free extent, Oracle might have to resort to one of the following: a) Combine contiguous extents into larger extents. This process is called coalescing. b) Increase the size of the datafile for the tablespace if the datafile is auto-extendable. c) Return an error indicating that it's not able to allocate space of the requested size. * * * * * A data block is determined at database creation. It corresponds to the smallest unit of data storage and physical I/O by Oracle. Block sizes range from 2K to 32K and, for all intents and purposes, can't be altered without re-creating a brand new database. To find out the database block size, run: SQL> select * from v$parameter where name like 'db_block_size'; Note that data is stored in uppercase in Oracle's data dictionary. This is one of the exceptions. You must query Oracle's initialization parameter values in lowercase. In general, use small data blocks for OLTP (online transaction processing) applications and large data blocks for data warehouse applications. --------------------------------------------------------- 5) DID YOU KNOW... --------------------------------------------------------- * that a column of DATE datatype in Oracle stores both the date and time values but doesn't store fractions of a second? * that Oracle provides a CONNECT BY clause in the SELECT statement for recursively displaying data organized in a tree structure (e.g., family tree)? * that Oracle allows you to create indexes based on the result of a function? We'll explore these topics in the next issue. --------------------------------------------------------- Well, that's it for this week. I welcome your feedback, input, tips, suggestions, Web sites, and other Oracle- related news. If you send me something, please let me know whether I can use your name with your comments. I apologize in advance if I don't respond personally to each of your questions or suggestions, but I'll get to as many as I can in the eNewsletter if not personally. Garry Chan, Editor Database Architect mailto:GChan@ProcaseConsulting.com This eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2000 http://www.pinnaclepublishing.com All rights reserved.