There are 3 types of space management problem that can be addressed with RESUMABLE SPACE ALLOCATION.

1. Out of space in the tablespace - (Common issue during impdp from upper environment to lower environment)

2. Maximum extents reached in the segment

3. Spece Quota exceeded for a user

DBA can automatically make statement resumable by setting init.ora parameter RESUMABLE_TIMEOUT = 5000 (value >0). This value is in seconds.

It can also be done in the session level,

SQL> alter session enable resumable timeout 5000;

SQL> desc dba_resumable;
 Name                               Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 USER_ID                            NUMBER
 SESSION_ID                            NUMBER
 INSTANCE_ID                            NUMBER
 COORD_INSTANCE_ID                        NUMBER
 COORD_SESSION_ID                        NUMBER
 STATUS                             VARCHAR2(9)
 TIMEOUT                            NUMBER
 START_TIME                            VARCHAR2(20)
 SUSPEND_TIME                            VARCHAR2(20)
 RESUME_TIME                            VARCHAR2(20)
 NAME                                VARCHAR2(4000)
 SQL_TEXT                            VARCHAR2(1000)
 ERROR_NUMBER                            NUMBER
 ERROR_PARAMETER1                        VARCHAR2(80)
 ERROR_PARAMETER2                        VARCHAR2(80)
 ERROR_PARAMETER3                        VARCHAR2(80)
 ERROR_PARAMETER4                        VARCHAR2(80)
 ERROR_PARAMETER5                        VARCHAR2(80)
 ERROR_MSG                            VARCHAR2(4000)







SQL> select  USER_ID, INSTANCE_ID, STATUS, NAME, ERROR_MSG from dba_resumable;

no rows selected

SQL>

SQL> desc V$SYSAUX_OCCUPANTS 
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 OCCUPANT_NAME                        VARCHAR2(64)
 OCCUPANT_DESC                        VARCHAR2(64)
 SCHEMA_NAME                        VARCHAR2(64)
 MOVE_PROCEDURE                     VARCHAR2(64)
 MOVE_PROCEDURE_DESC                    VARCHAR2(64)
 SPACE_USAGE_KBYTES                    NUMBER

The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace. It reduces the number of tablespaces created by default, both in the seed database and in user-defined databases.

During normal database operation, Oracle Database does not allow the SYSAUX tablespace to be dropped or renamed. Transportable tablespaces for SYSAUX is not supported.

SQL> select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES from V$SYSAUX_OCCUPANTS ;

OCCUPANT_NAME             OCCUPANT_DESC              SPACE_USAGE_KBYTES
-------------------------------- -------------------------------- ------------------
LOGMNR                 LogMiner                    8064
LOGSTDBY             Logical Standby                1408
SMON_SCN_TIME             Transaction Layer - SCN to TIME        3200
                 mapping

PL/SCOPE             PL/SQL Identifier Collection            1600
STREAMS              Oracle Streams                 1024
AUDIT_TABLES             DB audit tables                   0
XDB                 XDB                          131008
AO                 Analytical Workspace Object Tabl           46208
                 e

XSOQHIST             OLAP API History Tables               46208
XSAMD                 OLAP Catalog                    7488
SM/AWR                 Server Manageability - Automatic           73280
                  Workload Repository

SM/ADVISOR             Server Manageability - Advisor F        8448
                 ramework

SM/OPTSTAT             Server Manageability - Optimizer           14272
                  Statistics History

SM/OTHER             Server Manageability - Other Com        6144
                 ponents

STATSPACK             Statspack Repository                   0
SDO                 Oracle Spatial                    46016
WM                 Workspace Manager                3136
ORDIM                 Oracle Multimedia ORDSYS Compone         448
                 nts

ORDIM/ORDDATA             Oracle Multimedia ORDDATA Compon           11584
                 ents

ORDIM/ORDPLUGINS         Oracle Multimedia ORDPLUGINS Com           0
                 ponents

ORDIM/SI_INFORMTN_SCHEMA     Oracle Multimedia SI_INFORMTN_SC           0
                 HEMA Components

EM                 Enterprise Manager Repository               49280
TEXT                 Oracle Text                    3584
ULTRASEARCH             Oracle Ultra Search                   0
ULTRASEARCH_DEMO_USER         Oracle Ultra Search Demo User               0
EXPRESSION_FILTER         Expression Filter System            3712
EM_MONITORING_USER         Enterprise Manager Monitoring Us         512
                 er

TSM                 Oracle Transparent Session Migra           0
                 tion User

SQL_MANAGEMENT_BASE         SQL Management Base Schema            1728
AUTO_TASK             Automated Maintenance Tasks             320
JOB_SCHEDULER             Unified Job Scheduler                 448

31 rows selected.

SQL>

Published in Oracle Blog - By an user in Optimizer development team

This article will describe both of these issues and explain how you can address them both in Oracle Database 10gR2 and 11gR1.

Out of Range Large tables are often decomposed into smaller pieces called partitions in order to improve query performance and ease of data management. The Oracle query optimizer relies on both the statistics of the entire table (global statistics) and the statistics of the individual partitions (partition statistics) to select a good execution plan for a SQL statement. If the query needs to access only a single partition, the optimizer uses only the statistics of the accessed partition. If the query access more than one partition, it uses a combination of global and partition statistics.

"Out of Range" means that the value supplied in a where clause predicate is outside the domain of values represented by the [minimum, maximum] column statistics. The optimizer prorates the selectivity based on the distance between the predicate value and the maximum value (assuming the value is higher than the max), that is, the farther the value is from the maximum value, the lower the selectivity will be. This situation occurs most frequently in tables that are range partitioned by a date column, a new partition is added, and then queried while rows are still being loaded in the new partition. The partition statistics will be stale very quickly due to the continuous trickle feed load even if the statistics get refreshed periodically. The maximum value known to the optimizer is not correct leading to the "Out of Range" condition. The under-estimation of selectivity often leads the query optimizer to pick a sub optimal plan. For example, the query optimizer would pick an index access path while a full scan is a better choice.

The "Out of Range" condition can be prevented by using the new copy table statistics procedure available in Oracle Database10.2.0.4 and 11g. This procedure copies the statistics of the source [sub] partition to the destination [sub] partition. It also copies the statistics of the dependent objects: columns, local (partitioned) indexes etc. It adjusts the minimum and maximum values of the partitioning column as follows; it uses the high bound partitioning value as the maximum value of the first partitioning column (it is possible to have concatenated partition columns) and high bound partitioning value of the previous partition as the minimum value of the first partitioning column for range partitioned table. It can optionally scale some of the other statistics like the number of blocks, number of rows etc. of the destination partition.

Assume we have a table called SALES that is ranged partitioned by quarter on the SALES_DATE column. At the end of every day data is loaded into latest partition. However, statistics are only gathered at the end of every quarter when the partition is fully loaded. Assuming global and partition level statistics (for all fully loaded partitions) are up to date, use the following steps in order to prevent getting a sub-optimal plan due to "out of range".

1. Lock the table statistics using LOCK_TABLE_STATS procedure in DBMS_STATS. This is to avoid interference from auto statistics job.

EXEC DBMS_STATS.LOCK_TABLE_STATS('SH','SALES');

2. Before beginning the initial load into each new partition (say SALES_Q4_2000) copy the statistics from the previous partition (say SALES_Q3_2000) using COPY_TABLE_STATS. You need to specify FORCE=>TRUE to override the statistics lock. 

EXEC DBMS_STATS.COPY_TABLE_STATS ('SH', 'SALES', 'SALES_Q3_2000', 'SALES_Q4_2000', FORCE=>TRUE);

Expensive global statistics collection

In data warehouse environment it is very common to do a bulk load directly into one or more empty partitions. This will make the partition statistics stale and may also make the global statistics stale. Re-gathering statistics for the effected partitions and for the entire table can be very time consuming. Traditionally, statistics collection is done in a two-pass approach:

• In the first pass we will scan the table to gather the global statistics
• In the second pass we will scan the partitions that have been changed to gather their partition level statistics.

The full scan of the table for global statistics collection can be very expensive depending on the size of the table. Note that the scan of the entire table is done even if we change a small subset of partitions.

In Oracle Database 11g, we avoid scanning the whole table when computing global statistics by deriving the global statistics from the partition statistics. Some of the statistics can be derived easily and accurately from partition statistics. For example, number of rows at global level is the sum of number of rows of partitions. Even global histogram can be derived from partition histograms. But the number of distinct values (NDV) of a column cannot be derived from partition level NDVs. So, Oracle maintains another structure called a synopsis for each column at the partition level. A synopsis can be considered as sample of distinct values. The NDV can be accurately derived from synopses. We can also merge multiple synopses into one. The global NDV is derived from the synopsis generated by merging all of the partition level synopses. To summarize

1. Gather statistics and create synopses for the changed partitions only
2. Oracle automatically merges partition level synopses into a global synopsis
3. The global statistics are automatically derived from the partition level statistics and global synopses

Incremental maintenance feature is disabled by default. It can be enabled by changing the INCREMENTAL table preference to true. It can also be enabled for a particular schema or at the database level. If you are interested in more details of the incremental maintenance feature, please refer to the following paper presented in SIGMOD 2008 and to our previous blog entry on new ndv gathering in 11g.

Assume we have table called SALES that is range partitioned by day on the SALES_DATE column. At the end of every day data is loaded into latest partition and partition statistics are gathered. Global statistics are only gathered at the end of every month because gathering them is very time and resource intensive. Use the following steps in order to maintain global statistics after every load.

1 -Turn on incremental feature for the table. 

EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE');

2 -At the end of every load gather table statistics using GATHER_TABLE_STATS command. You don't need to specify the partition name. Also, do not specify the granularity parameter. The command will collect statistics for partitions with stale or missing statistics and update the global statistics based on the partition level statistics and synopsis.

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES');

Note: that the incremental maintenance feature was introduced in Oracle Database 11g Release 1. However, we also provide a solution in Oracle Database10g Release 2 (10.2.0.4) that simulates the same behavior. The 10g solution is a new value, 'APPROX_GLOBAL AND PARTITION' for the GRANULARITY parameter of the GATHER_TABLE_STATS procedures. It behaves the same as the incremental maintenance feature except that we don't update the NDV for non-partitioning columns and number of distinct keys of the index at the global level. For partitioned column we update the NDV as the sum of NDV at the partition levels. Also we set the NDV of columns of unique indexes as the number of rows of the table. In general, non-partitioning column NDV at the global level becomes stale less often. It may be possible to collect global statistics less frequently then the default (when table changes 10%) since approx_global option maintains most of the global statistics accurately.

Let's take a look at an example to see how you would effectively use the Oracle Database 10g approach.
After the data load is complete, gather statistics using DBMS_STATS.GATHER_TABLE_STATS for the last partition (say SALES_11FEB2009), specify granularity => 'APPROX_GLOBAL AND PARTITION'. It will collect statistics for the specified partition and derive global statistics from partition statistics (except for NDV as described before).

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SH', 'SALES', 'SALES_11FEB2009', GRANULARITY => 'APPROX_GLOBAL AND PARTITION');

It is necessary to install the one off patch for bug 8719831 if you are using the above features in 10.2.0.4 (patch 8877245) or in 11.1.0.7 (patch 8877251)

1. Composite range-range partitioning : The partition method will partition data using the range method. Then for each partition, subpartition will be created using range method.

2. Composite list-range partitioning : The partition method will partition data using the list method. Then for each partition, subpartition will be created using range method.

3. Composite list-hash partitioning : The partition method will partition data using the list method. Then for each partition, subpartition will be created using hash method.

4. Composite list-list partitioning : The partition method will partition data using the list method. Then for each partition, subpartition will be created using list method.

Oracle tries to automate the configuration of the system to maximize the performance of parallel operations, However there's still  a lot of scope for manual tweaking. Increasing the DOP beyond and optimal point fails to result in further performance increase.

DETERMINING THE DEGREE OF PARALLELISM (DOP): An optimal DOP is critical for good  parallel performance. Oracle determines the DOP as follows.

1. If parallel execution is indicated or requested, but no DOP is specified, the default DOP is set to twice the no of CPU cores on the system. For a RAC system, the DOP will be twice the number of cores in the entire cluster. This default is controlled by the configuration parameter PARALLEL_THREADS_PER_CPU.

2. From Oracle 11g R2 forward, if PARALLEL_DEGREE_POLICY is set to auto, Oracle will adjust the DOP depending on the nature of the operation to be performed and the sizes of the objects involved.

3. If PARALLEL_ADAPTIVE_MULTI_USER is set to TRUE, Oracle will adjust the DOP based on the overall load on the system. When the system is more heavily loaded , the DOP will be reduced.

4. if PARALLEL_IO_CAP is set to TRUE in Oracle 11g or higher, Oracle will limit the DOP to that which the IO subsystem can support. These IO subsystem limit can be calculated by using the procedure DBMS_RESOURCE_MANAGER.CALIBRATE_IO.

5. A DOP can be specified at the table or index level by using PARALLEL clause of CREATE TABLE, CREATE INDEX, ALTER TABLE, or ALTER INDEX.

6. A PARALLEL hint can be used to specify the DOP for a specific table within a query.

7. Regardless of any other setting, the DOP can not exceed that which can be supported by PARALLEL_MAX_SERVERS.  For most SQL statements, the no of servers required will be twice the requested DOP.


NOTE: For ad-hoc query execution, you might want to set an explicit DOP. However, for SQL embedded within an application, this might not be good idea because the SQL will be less able to adapt to change in machine configuration (more CPU for instance), workload(more concurrent sessions), or configuration(change in the no of parallel slaves or default DOP). For embedded SQL, it's probably better to omit an explicit DOP or to use the AUTO keyword (in Oracle 11g R2 and higher).