Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

Chapter 5 Oracle Tablespace Design

Now that we see the block sizing issues, let’s examine the storage options within Oracle tablespaces.

The evolution of Oracle file structures

Starting with Oracle8i, Oracle has introduced three new tablespace parameters that automate tablespace and file storage management functions:

  • Locally Managed Tablespaces (LMT)—The LMT tablespace is implemented by adding the EXTENT MANAGEMENT LOCAL clause to the tablespace definition. LMT tablespaces automate extent management and remove the ability to specify the NEXT storage parameter.
     

  • Automatic Segment Space Management (ASSM)—The ASSM tablespace is new in Oracle9i and is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition. ASM tablespaces automate freelist management and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.
     

  • Automatic Storage Management (ASM) - The Automatic Storage Management (ASM) features allows for the automatic stripe-and-mirror everywhere approach to be used to automatically load balance the disk I/O sub-system and remove the need for the DBA to specify physical file locations when allocating a tablespace.

Here is an example of a tablespace with these new parameters:

create tablespace
   asm_test
datafile
   'c:\oracle\oradata\diogenes\asm_test.dbf
size
   5m
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
;

In Oracle9i, we expect an error if we try to specify PCTUSED for a table defined inside a tablespace with Automatic Segment Space Management:

SQL> create table
  2                    test_table
  3                    (c1 number)
  4  tablespace
  5                    asm_test
  6  storage
  7                    ( pctfree 20 pctused 30 )
  8  ;
 
   ( pctfree 20 pctused 30 )
     *
ERROR at line 7:
ORA-02143: invalid STORAGE option

However, here we see an important point.  While Oracle9i rejects the PCTUSED parameter with locally managed tablespaces with Automatic Segment Space Management, it does allow you to enter invalid settings for NEXT and FREELISTS settings:

SQL> create table
  2                    test_table
  3                    (c1 number)
  4  tablespace
  5                    asm_test
  6  storage
  7                    ( freelists 30 next 5m ) ; 

Table created.

From the docs, we see the convoluted rules for extent, storage and freelist management parameters:

If you specified the MINIMUM EXTENT clause, Oracle evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCTINCREASE is 0. If so, Oracle creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, Oracle ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.

If you did not specify MINIMUM EXTENT clause, Oracle evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.

When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:

MANUAL - Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.

AUTO - This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.

PCTFREE is still required even with ASSM because it determines how rows will be packed into blocks, while freelists, pctused and pctincrease are ignored with ASSM because they are used for transaction/block management.


Got Oracle Scripts?

BC shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, we shares our secrets for navigating the Oracle data dictionary.

Packed with more than 690 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.

http://www.dba-oracle.com/bp/bp_elec_adv_mon_tuning.htm

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.