Optimizing Table Performance
Topics In this Section:1.1. Building a Database That Maximizes Performance.
1.2. Creating Tablespaces that Maximizes performance.
1.3. Matching Table types to Business Requirements.
1.4. Choosing Table features for Performance.
1.5. Avoiding Extent Allocation delays when creating Tables.
1.6. Maximizing Data Loading Speeds.
1.7. Efficiently Removing Table Data.
1.8. Displaying Automated Segment Advisor Advice.
1.9. Manually Generating Segment Advisor Advice.
1.10. Automatically Emailing Segment Advisor Output.
1.11. Rebuilding Rows spanning Multiple Blocks.
1.12. Freeing Unused Table Space.
1.13. Compressing Data for Direct Path Loading.
1.14. Compressing Data for All DML.
1.15. Compressing Data at the Column Level.
1.16. Monitoring Table Usage.
Table Performance Primarily depends on , the features implemented when creating the table , storage type, tablespace , table type, data types and we should possess table creation standards keeping performance in mind as foundation of performance optimization with respect to Tables which store data or information.
A tablespace is the logical structure that allows you to manage a group of datafiles. Datafiles are the
physical datafiles on disk. When configuring tablespaces, there are several features to be aware of that can have far-reaching performance implications, namely locally managed tablespaces and automatic segment storage–managed tablespaces. When you reasonably implement these features, you maximize your ability to obtain acceptable future table performance.
1-1. Building a Database That Maximizes Performance
Problem
You realize when initially creating a database that some features (when enabled) have long-lasting
ramifications for table performance and availability. Specifically, when creating the database, you want to do the following:
ramifications for table performance and availability. Specifically, when creating the database, you want to do the following:
• Enforce that every tablespace ever created in the database must be locally
managed. Locally managed tablespaces deliver better performance than the
deprecated dictionary-managed technology.
• Ensure users are automatically assigned a default permanent tablespace. This
guarantees that when users are created they are assigned a default tablespace
other than SYSTEM. You don’t want users ever creating objects in the SYSTEM
tablespace, as this can adversely affect performance and availability.
• Ensure users are automatically assigned a default temporary tablespace. This
guarantees that when users are created they are assigned a temporary tablespace
other than SYSTEM. You don’t ever want users using the SYSTEM tablespace for a
temporary sorting space, as this can adversely affect performance and availability.
managed. Locally managed tablespaces deliver better performance than the
deprecated dictionary-managed technology.
• Ensure users are automatically assigned a default permanent tablespace. This
guarantees that when users are created they are assigned a default tablespace
other than SYSTEM. You don’t want users ever creating objects in the SYSTEM
tablespace, as this can adversely affect performance and availability.
• Ensure users are automatically assigned a default temporary tablespace. This
guarantees that when users are created they are assigned a temporary tablespace
other than SYSTEM. You don’t ever want users using the SYSTEM tablespace for a
temporary sorting space, as this can adversely affect performance and availability.
Solution
Use a script such as the following to create a database that adheres to reasonable standards that set the
foundation for a well-performing database:
foundation for a well-performing database:
CREATE DATABASE O11R2
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 1024
MAXINSTANCES 1
MAXLOGHISTORY 680
CHARACTER SET AL32UTF8
DATAFILE
'/ora01/dbfile/O11R2/system01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE undotbs1 DATAFILE
'/ora02/dbfile/O11R2/undotbs01.dbf'
SIZE 800M
SYSAUX DATAFILE
'/ora03/dbfile/O11R2/sysaux01.dbf'
SIZE 500M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'/ora02/dbfile/O11R2/temp01.dbf'
SIZE 500M
DEFAULT TABLESPACE USERS DATAFILE
'/ora01/dbfile/O11R2/users01.dbf'
SIZE 50M
LOGFILE GROUP 1
('/ora01/oraredo/O11R2/redo01a.rdo',
'/ora02/oraredo/O11R2/redo01b.rdo') SIZE 200M,
GROUP 2
('/ora01/oraredo/O11R2/redo02a.rdo',
'/ora02/oraredo/O11R2/redo02b.rdo') SIZE 200M,
GROUP 3
('/ora01/oraredo/O11R2/redo03a.rdo',
'/ora02/oraredo/O11R2/redo03b.rdo') SIZE 200M
USER sys IDENTIFIED BY topfoo
USER system IDENTIFIED BY topsecrectfoo;
The prior CREATE DATABASE script helps establish a good foundation for performance by enabling features such as the following:
• Defines the SYSTEM tablespace as locally managed via the EXTENT MANAGEMENT LOCAL clause; this ensures that all tablespaces ever created in database are locally managed. If you are using Oracle Database 11g R2 or higher, the EXTENT MANAGEMENT DICTIONARY clause has been deprecated.
• Defines a default tablespace named USERS for any user created without an explicitly defined default tablespace; this helps prevent users from being assigned the SYSTEM tablespace as the default. Users created with a default tablespace of SYSTEM can have an adverse impact on performance.
• Defines a default temporary tablespace named TEMP for all users; this helps prevent users from being assigned the SYSTEM tablespace as the default temporary tablespace. Users created with a default temporary tablespace of SYSTEM can have an adverse impact on performance, as this will cause contention for resources in the SYSTEM tablespace. Solid performance starts with a correctly configured database. The prior recommendations help you create a reliable infrastructure for your table data.
How It Works
A properly configured and created database will help ensure that your database performs well. It is true that you can modify features after the database is created. However, oftentimes a poorly crafted CREATE DATABASE script leads to a permanent handicap on performance. In production database environments, it’s sometimes difficult to get the downtime that might be required to reconfigure an improperly configured database. If possible, think about performance at every step in creating an environment, starting with how you create the database.When creating a database, you should also consider features that affect maintainability. A sustainable database results in more uptime, which is part of the overall performance equation. The CREATE DATABASE statement in the “Solution” section also factors in the following sustainability features:
• Creates an automatic UNDO tablespace (automatic undo management is enabled by
setting the UNDO_MANAGEMENT and UNDO_TABLESPACE initialization parameters); this
allows Oracle to automatically manage the rollback segments. This relieves you of
having to regularly monitor and tweak.
• Places datafiles in directories that follow standards for the environment; this helps
with maintenance and manageability, which results in better long-term
availability and thus better performance.
• Sets passwords to non-default values for DBA-related users; this ensures the
database is more secure, which in the long run can also affect performance (for
example, if a malcontent hacks into the database and deletes data, then
performance will suffer).
• Establishes three groups of online redo logs, with two members each, sized
appropriately for the transaction load; the size of the redo log directly affects the
rate at which they switch. When redo logs switch too often, this can degrade
performance.
You should take the time to ensure that each database you build adheres to commonly accepted standards that help ensure you start on a firm performance foundation. If you’ve inherited a database and want to verify the default permanent tablespace setting, use a query such as this:
SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';
If you need to modify the default permanent tablespace, do so as follows:
SQL> alter database default tablespace users;
To verify the setting of the default temporary tablespace, use this query:
SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
To change the setting of the temporary tablespace, you can do so as follows:
SQL> alter database default temporary tablespace temp;
You can verify the UNDO tablespace settings via this query:
select name, value
from v$parameter
where name in ('undo_management','undo_tablespace');
If you need to change the undo tablespace, first create a new undo tablespace and then use the
ALTER SYSTEM SET UNDO_TABLESPACE statement.
Key Points to Remember in 1-1.
* While creating Database, make sure Tablespaces are Locally managed.
* Default Tablespaces and Default TEMP tablespaces are provided, so that users work will not affect Database performance by using SYSTEM tablespace.
* Makesure 3 redo log group exists, in order to make sure prevention of Data Loss.each group having 2 members.
* SYS and SYSTEM password should be given keeping security in mind, instead of default password.
* Make sure Automatic UnDo management is enabled. Automatic undo management is enabled by setting the UNDO_MANAGEMENT.
* DATABASE_PROPERTIES is the table, where properties related to Database are stored.
* Also V$PARAMETER is the view containing database system level parameters.
* Use ALTER DATABASE to changes related to DATAFILES,TABLESPACES.
* Use ALTER SYSTEM to changes related to UNDO,LOG related changes.
1-2. Creating Tablespaces to Maximize Performance
Problem
You realize that tablespaces are the logical containers for database objects such as tables and indexes.
Furthermore, you’re aware that if you don’t specify storage attributes when creating objects, then the
tables and indexes automatically inherit the storage characteristics of the tablespaces (that the tables
and indexes are created within). Therefore you want to create tablespaces in a manner that maximizes
table performance and maintainability.
Furthermore, you’re aware that if you don’t specify storage attributes when creating objects, then the
tables and indexes automatically inherit the storage characteristics of the tablespaces (that the tables
and indexes are created within). Therefore you want to create tablespaces in a manner that maximizes
table performance and maintainability.
Solution
When you have the choice, tablespaces should always be created with the following two features
enabled:
• Locally managed
• Automatic segment space management (ASSM)
Here’s an example of creating a tablespace that enables the prior two features:
enabled:
• Locally managed
• Automatic segment space management (ASSM)
Here’s an example of creating a tablespace that enables the prior two features:
create tablespace tools
datafile '/ora01/dbfile/INVREP/tools01.dbf'
size 100m -- Fixed datafile size
extent management local -- Locally managed
uniform size 128k -- Uniform extent size
segment space management auto -- ASSM
/
datafile '/ora01/dbfile/INVREP/tools01.dbf'
size 100m -- Fixed datafile size
extent management local -- Locally managed
uniform size 128k -- Uniform extent size
segment space management auto -- ASSM
/
Note As of Oracle Database 11g R2, the EXTENT MANAGEMENT DICTIONARY clause has been deprecated. Locally managed tablespaces are more efficient than dictionary-managed tablespaces. This feature is enabled via the EXTENT MANAGEMENT LOCAL clause. Furthermore, if you created your database with the SYSTEM tablespace as locally managed, you will not be permitted to later create a dictionary-managed tablespace. This is the desired behavior. The ASSM feature allows for Oracle to manage many of the storage characteristics that formerly had to be manually adjusted by the DBA on a table-by-table basis. ASSM is enabled via the SEGMENT SPACE MANAGEMENT AUTO clause. Using ASSM relieves you of these manual tweaking activities. Furthermore, some of Oracle’s space management features (such as shrinking a table and SecureFile LOBs) are allowed only when using ASSM tablespaces. If you want to take advantage of these features, then you must create your tablespaces using ASSM. You can choose to have the extent size be consistently the same for every extent within the tablespace via the UNIFORM SIZE clause. Alternatively you can specify AUTOALLOCATE. This allows Oracle to allocate extent sizes of 64 KB, 1 MB, 8 MB, and 64 MB. You may prefer the auto-allocation behavior if theobjects in the tablespace typically are of varying size.
How It Works
Prior to Oracle Database 11g R2, you had the option of creating a tablespace as dictionary-managed.
This architecture uses structures in Oracle’s data dictionary to manage an object’s extent allocation and free space. Dictionary-managed tablespaces tend to experience poor performance as the number of extents for a table or index reaches the thousands.You should never use dictionary-managed tablespaces; instead use locally managed tablespaces. Locally managed tablespaces use a bitmap in each datafile to manage the object extents and free space and are much more efficient than the deprecated dictionary-managed architecture.
This architecture uses structures in Oracle’s data dictionary to manage an object’s extent allocation and free space. Dictionary-managed tablespaces tend to experience poor performance as the number of extents for a table or index reaches the thousands.You should never use dictionary-managed tablespaces; instead use locally managed tablespaces. Locally managed tablespaces use a bitmap in each datafile to manage the object extents and free space and are much more efficient than the deprecated dictionary-managed architecture.
In prior versions of Oracle, DBAs would spend endless hours monitoring and modifying the physical
space management aspects of a table. The combination of locally managed and ASSM render many of these space settings obsolete. For example, the storage parameters are not valid parameters in locally managed tablespaces:
• NEXT
• PCTINCREASE
• MINEXTENTS
• MAXEXTENTS
• DEFAULT
The SEGMENT SPACE MANAGEMENT AUTO clause instructs Oracle to manage physical space within the block. When you use this clause, there is no need to specify parameters such as the following:
• PCTUSED
• FREELISTS
• FREELIST GROUPS
The alternative to AUTO space management is MANUAL space management. When you use MANUAL, you can adjust the previously mentioned parameters depending on the needs of your application. We recommend that you use AUTO (and do not use MANUAL). Using AUTO reduces the number of parameters you’d otherwise need to configure and manage. You can verify the use of locally managed and ASSM with the following query:
select
tablespace_name
,extent_management
,segment_space_management
from dba_tablespaces;
Here is some sample output:
TABLESPACE_NAME EXTENT_MAN SEGMENT
------------------------------ ----------------- -------
SYSTEM LOCAL MANUAL
SYSAUX LOCAL AUTO
UNDOTBS1 LOCAL MANUAL
TEMP LOCAL MANUAL
USERS LOCAL AUTO
TOOLS LOCAL AUTO
Note You cannot create the SYSTEM tablespace with automatic segment space management. Also, the ASSM feature is valid only for permanent, locally managed tablespaces.You can also specify that a datafile automatically grow when it becomes full. This is set through the AUTOEXTEND ON clause. If you use this feature, we recommend that you set an overall maximum size for the datafile. This will prevent runaway or erroneous SQL from accidentally consuming all available diskspace. Here’s an example clause:
SIZE 1G AUTOEXTEND ON MAXSIZE 10G
When you create a tablespace, you can also specify the tablespace type to be smallfile or bigfile.
Prior to Oracle Database 10g, smallfile was your only choice. A smallfile tablespace allows you to
create one or more datafiles to be associated with a single tablespace. This allows you to spread out the datafiles (associated with one tablespace) across many different mount points. For many environments, you’ll require this type of flexibility.
The bigfile tablespace can have only one datafile associated with it. The main advantage of the
bigfile feature is that you can create very large datafiles, which in turn allows you to create very large
databases. For example, with the 8 KB block size, you can create a datafile as large as 32 TB. With a 32 KB block size, you can create a datafile up to 128 TB. Also, when using bigfile, you will typically have fewer datafiles to manage and maintain. This behavior may be desirable in environments where you use Oracle’s Automatic Storage Management (ASM) feature. In ASM environments, you typically are presented with just one logical disk location from which you allocate space.
Here’s an example of creating a bigfile tablespace:
create bigfile tablespace tools_bf
datafile '/ora01/dbfile/O11R2/tools_bf01.dbf'
size 100m
extent management local
uniform size 128k
segment space management auto
/
You can verify the tablespace type via this query:
SQL> select tablespace_name, bigfile from dba_tablespaces;
Unless specified, the default tablespace type is smallfile. You can make bigfile the default
tablespace type for a database when you create it via the SET DEFAULT BIGFILE TABLESPACE clause. You can alter the default tablespace type for a database to be bigfile using the
ALTER DATABASE SET DEFAULT
BIGFILE TABLESPACE statement.
