Oracle 9i New Features

 
Dynamic SGA
The dynamic SGA infrastructure will allow for the sizing of the Buffer Cache, Shared Pool and the Large Pool (see note below) without having to shutdown the database. In this new model, a new unit of allocation is created called the 'Granule'. A granule is a unit of contiguous virtual memory allocation. The size of a granule depends on the estimated total SGA size, whose calculation is based on the value of the parameter SGA_MAX_SIZE. This would be 4MB if the SGA size is less than 128MB, else it will be 16MB. The Buffer Cache, Shared Pool and Large Pool are allowed to grow and shrink based on granule boundaries.
At instance startup the Oracle Server allocates the granule entries, one for each granule to support SGA_MAX_SIZE bytes of address space. During the startup each component acquires as many granules as it requires. The minimum SGA is three granules, as follows:
    1. One Granule for Fixed SGA (includes redo buffers)
    2. One Granule for Buffer Cache
    3. One Granule for Shared Pool

We can alter the granules allocated to components using the 'ALTER SYSTEM' command. The granules are rounded up to the nearest of the default graunule size (4MB or 16MB). Example:
            alter system set shared_pool_size=64M
You can create an SPFILE from an init.ora file and vice versa with the CREATE SPFILE and CREATE PFILE commands.
Most parts of the SGA can be dynamically resized and have default sizes.

• Set the SGA_MAX_SIZE parameter to specify the largest the SGA is allowed to be. This is static. If you don't set SGA_MAX_SIZE, it will default to the initial size of the SGA (meaning that you can dynamically shrink the SGA but not grow it).
• Set DB_CACHE_SIZE to the size in bytes of the buffer cache for the standard block size. This replaces DB_BLOCK_BUFFERS, and will default to a setting based on SGA_MAX_SIZE if not specified.
  
• Set DB_Nk_CACHE_SIZE to specify the sizes of the buffer caches for alternate block sizes.
  
• DB_CACHE_SIZE, DB_NK_CACHE_SIZE, SHARED_POOL_SIZE and LARGE_POOL_SIZE are all dynamic parameters, meaning you can alter their settings without shutting down the instance.
  
• You can still set DB_BLOCK_BUFFERS and omit SGA_MAX_SIZE but the SGA and buffer cache size will be static.

Changing init.ora parameters
Before Oracle9i, Oracle instances were always started using a text file called an init<SID>.ora. This file is by default located in the "$ORACLE_HOME/dbs" directory. In Oracle9i, Oracle has also introduced the 'SPFILE', which is a binary file stored on the database Server. Changes which are applied to the instance parameters may be persistent accross all startup/shutdown procedures. In Oracle9i, you can startup the instance using either an spfile or an init.ora file.
The default initialization files must are located as follows:
- on Unix ---> $ORACLE_HOME/dbs
- on WindowsNT/2000 ---> %ORACLE_HOME%\database

1. Specifying neither file:
sqlplus /nolog
SQL> connect / as sysdba
SQL> startup
Oracle first searches if the spfile<SID>.ora exists. If it does not, Oracle searchs for the spfile.ora parameter file. If neither spfile<SID>.ora nor spfile.ora exist, Oracle will use the init<SID>.ora parameter file.

2. Specifying init.ora file:
This behavior is the same as in previous database versions.
SQL> startup pfile=d:\ora901\database\initORA901.ora

3. Specifying spfile:
In this case, you must startup with an init.ora file in which you only specify the spfile parameter full name:
spfile=d:\ora901\database\spfiletest.ora
SQL> startup pfile=d:\ora901\database\inittest.ora

So in order to modify an init.ora parameter and make it persistant you should:
1- Backup your init.ora file
2- sqlplus "/ as sysdba"
3- create pfile from spfile;
4- edit your init.ora file created by the previous command and add/modify your parameter
5- shutdown immediate
6- create spfile from pfile;
7- startup

Startup the DB Remotely
1- Check that the server has a SPFILE
2- Create at the client PC the file pfilename.ora with just one line with
    spfile=/u01/app/oracle/...../spfileSID.ora    --> Location of the spfile on the server
3- Sqlplus /nolog
4- connect user/password@DB as sysdba
5- startup pfile=pfilename.ora

Parameter Scope
The scope of the ALTER SYSTEM SET command can be defined using the following:
        ALTER SYSTEM SET parameter = value SCOPE=[SPFILE/MEMORY/BOTH];

The actions of the scope parameters are:

• BOTH - (Default) The parameter takes affect in the current instance and is stored in the SPFILE.
• SPFILE - The parameter is altered in the SPFILE only. It does not affect the current instance.
  
• MEMORY - The parameter takes affect in the current instance, but is not stored in the SPFILE.

A parameter value can be reset to the default using:
        ALTER SYSTEM RESET OPEN_CURSORS SID='*' SCOPE='SPFILE';

In a Real Application Cluster (RAC) environment node-specific parameters can be set using the SID parameter:
        ALTER SYSTEM SET OPEN_CURSORS=500 SID='SID1' SCOPE='SPFILE';

Multiple Block Sizes
In Oracle we can have multiple block sizes at the same time. When a tablespace is created we can assign a block size for the objects that will be created in that tablespace.This is a great help for OLTP and Indexes.
The DB_BLOCK_SIZE parameter determines the size of your standard block size in the database and is the only block size for the entire database. Unless you specify it, all the tablespaces will be created using that size.
The DB_CACHE_SIZE parameter specifies the size (in bytes) of the cache of the standard block sized buffers. Notice that you don’t set the number of database buffers; rather, you specify the size of the buffer cache itself in the DB_CACHE_SIZE parameter.

With all these adjustments, you have have your tablespaces with different block sizes, so you can put your big objects in your BIG Tablespace.

Before you use non standard block size, you have to define the cache size for these non standard block size. We have a paramter called DB_nK_CACHE_SIZE for setting the cache size for non standard block size.
The new init.ora parameters that allow you to use non-default block sizes are:
Parameter                            Block Size
DB_2K_CACHE_SIZE        2KB
DB_4K_CACHE_SIZE        4KB
DB_8K_CACHE_SIZE        8KB
DB_16K_CACHE_SIZE    16KB
DB_32K_CACHE_SIZE    32KB

Another classification for buffer cache is depending on the algorithm used to keep the contents into the cache. We have basically 3 types in this catagory.
1) DB_KEEP_CACHE_SIZE
2) DB_RECYCLE_CACHE_SIZE
3) DB_CACHE_SIZE
DB_KEEP_CACHE_SIZE is where the object are always present when they are loaded. The objects which qualifies for this cache are those which are very frquently accessed and which has to be retained in memory. For example, frquently used small lookup tables. This cache is a subset of default cache defined by parameter DB_CACHE_SIZE. For any database we need to have DB_CACHE_SIZE set.
DB_RECYCLE_CACHE_SIZE is where you dont want to store the object. You want to clear off the object from cache as soon as it is used. You have to be careful while using this, since this may incure performance hit in case you allocated a frequently used object to this cache.
DB_CACHE_SIZE is the size for default cache.

The instance must be restarted before changes to these parameters take effect. The DB_nK_CACHE_SIZE parameters can only be set to zero if there are no online tablespace with a nK block size.
    alter system set DB_4K_CACHE_SIZE = 10M scope=pfile;
    alter system set DB_16K_CACHE_SIZE = 8M scope=pfile;

Once the caches are defined the tablespaces can be defined:
create tablespace test_tbs4k datafile ‘c:\oracle\test_tbs4k_1.dbf’ size 100M blocksize 4K extent management local;

Automatic SQL Execution Memory Management and Oracle self-tune PGA memory
The performance of complex long running queries, typical in a DSS environment, depends to a large extent on the memory available in the Program Global Area (PGA). In Oracle8i and previous releases of the database, administrators sized the PGA by carefully adjusting a number of initialization parameters. Oracle9i completely automates the management of PGA memory. Administrators merely need to specify the maximum amount of PGA memory available to an instance using a newly introduced parameter PGA_AGGREGATE_TARGET. The database server automatically distributes this memory among various active queries in an intelligent manner so as to ensure maximum performance benefits and the most efficient utilization of memory. Furthermore, Oracle9i can adapt itself to changing workload thus utilizing resources efficiently regardless of the load on the system. The amount of the PGA memory available to an instance can be dynamically changed by altering the value of the PGA_AGGREGATE_TARGET parameter making it possible to add to and remove PGA memory from an active instance online. In the automatic mode, administrators no longer have to manually tune the sizes of the individual work areas using parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE.
The automatic SQL execution memory management feature is enabled by setting the parameter WORK_AREA_SIZE to AUTO. For backward compatibility reasons, Oracle9i continues to support the manual PGA management mode. The manual mode can be activated by either setting the WORK_AREA_SIZE parameter to MANUAL or not specifying a value for PGA_AGGREGATE_TARGET.
In Summary,  you can have Oracle self-tune PGA memory usage instead of setting sort_area_size, hash_area_size, bitmap_merge_area_size, and create_bitmap_area_size manually.
- Set PGA_AGGREGATE_TARGET to the total amount of physical memory available for use by all dedicated server processes.
- Oracle will then self-tune the *_AREA_SIZE parameters for all dedicated server connections.
- You can still set the *_AREA_SIZE parameters manually and omit PGA_AGGREGATE_TARGET for manual tuning.

How To Tune PGA_AGGREGATE_TARGET (from ML note 223730.1)
To determine the appropriate setting for PGA_AGGREGATE_TARGET  parameter I recommend to follow the following steps:

1- Make a first estimate for PGA_AGGREGATE_TARGET  based on the following rule:
    PGA_AGGREGATE_TARGET  = (Total Physical Memory * 0.16)    - For OLTP systems  (16% of Server Physical Mem)
    PGA_AGGREGATE_TARGET  = (Total Physical Memory * 0 .4  )   - For DSS systems   (40% of Server Physical Mem)

So for example if we have an Oracle instance configured on system with 16G of  Physical memory, then the suggested PGA_AGGREGATE_TARGET parameter value we should  start with incase we have OLTP system is (16 G * .16) ~= 2.5G and in case we have DSS system is (16 G * 0.4) ~= 6.5 G. 
In the above equation we assume that 20 % of the memory will be used by the OS,  and in OLTP system 20 % of the remaining memory will be used for  PGA_AGGREGATE_TARGET and the remaining memory is going for Oracle SGA memory and non-oracle processes memory. So make sure that you have enough memory for your SGA and also for non-oracle processes

2- Use the view v$sysstat to check PGA_AGGREGATE_TARGET values.
The following query gives the total number and the percentage of times work areas were executed in these three modes since the database instance was started.
select substr(name,1,32) profile, cnt count,
       decode(total, 0, 0, round(cnt*100/total)) percentage
  from (select name, value cnt, (sum(value) over ()) total
          from v$sysstat
          where name like 'workarea exec%');
   
PROFILE                                 CNT PERCENTAGE
-------------------------------- ---------- ----------
workarea executions - optimal       9378431         99   --> operations performed in memory
workarea executions - onepass            47          1   --> operations performed in disk
workarea executions - multipass          14          0   --> operations performed in disk

This output of this query is used to tell the DBA when to dynamically adjust pga_aggregate_target.  These executions fall into three categories:
OPTIMAL: Operations that were performed in memory
ONEPASS: When the operation was too big to be performed in memory, then part of it spills onto disk.
MULTIPASS: If more that one pass was needed on disk.
Ideally all executions should be in the optimal statistics and the onepass and multipass should be 0.

3- Monitor performance using available PGA statistics and see if PGA_AGGREGATE_TARGET is under-sized or over-sized. Several dynamic performance views are available for this purpose:

- V$PGASTAT
This view provides instance-level statistics on the PGA memory usage and the automatic PGA memory manager. For example:
 
select substr(name,1,40) name, value, unit from V$PGASTAT;
NAME                                          VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter            250609664 bytes       (a)
aggregate PGA auto target                 103809024 bytes
global memory bound                        12529664 bytes       (b)
total PGA inuse                           135284736 bytes       (c)
total PGA allocated                       149349376 bytes       (d)
maximum PGA allocated                     250475520 bytes
total freeable PGA memory                   2883584 bytes
PGA memory freed back to OS              6.0456E+11 bytes
total PGA used for auto workareas                 0 bytes       (e)
maximum PGA used for auto workareas        13918208 bytes
total PGA used for manual workareas               0 bytes
maximum PGA used for manual workareas             0 bytes
over allocation count                             0             (f)
bytes processed                          1.2039E+12 bytes
extra bytes read/written                  107754496 bytes
cache hit percentage                          99.99 percent     (g)

Main statistics to look at :
(a) aggregate PGA auto target : This gives the amount of PGA memory Oracle can use for work areas running in automatic mode. This part of memory represent the tunable part of PGA memory,i.e. memory allocated for intensive memory SQL operators  like sorts, hash-join, group-by, bitmap merge and bitmap index create. This memory  part can be shrinked/expanded in function of the system load. Other parts of  PGA memory are known as untunable, i.e. they require a size that can't be negociated  (e.g. context information for each session, for each open/active cursor,  PL/SQL or Java memory). 
So, the aggregate PGA auto target should not be small compared to the value of  PGA_AGGREGATE_TARGET. You must ensure that enough PGA memory is left for work areas running in automatic mode.
(b) Global memory bound – This statistic measures the max size of a work area, and Oracle recommends that whenever this statistics drops below one megabyte, then you should increase the value of the pga_aggregate_target parameter
(c) total PGA in used: This gives the total PGA memory in use. The detail of this value can be found in the PGA_USED_MEM column of the v$process view. At this time it will be very useful to use the following query to see allocated, used and maximum memory for all connections to Oracle.  We can see the RAM demands of each of the background processes and we also have detailed information about individual connections.Note that it is possible to join the v$process view with the v$sql_plan table to take a closer look at the RAM memory demands of specific connections.
select substr(program,1,30) program, pga_used_mem, pga_alloc_mem, pga_max_mem
from v$process;
(d)Total PGA allocated – This statistic display the high-water mark of all PGA memory usage on the database.  You should see this value approach the value of pga_aggregate_target as usage increases.
(e) total PGA used for auto workarea: This gives the actual tunable PGA memory used by the system. This statistic monitors RAM consumption or all connections that are running in automatic memory mode.  Remember, not all internal processes may use the automatic memory feature.  For example, Java and PL/SQL will allocate RAM memory, and this will not be counted in this statistic.  Hence, we can subtract value to the total PGA allocated to see the amount of memory used by connections and the RAM memory consumed by Java and PL/SQL
(f) over allocation count: Over-allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET is too small to accommodate the untunable PGA memory part plus the minimum memory required to execute the work area workload. When this happens,  Oracle cannot honor the initialization parameter PGA_AGGREGATE_TARGET, and extra PGA memory needs to be allocated. Over allocation count is the number of time the system was detected in this state since database startup. This count should ideally be equal to zero.
(g) cache hit percentage: This metric is computed by Oracle to reflect the performance of the PGA memory component. It is cumulative from instance start-up. A value of 100% means that all work areas executed by the system since instance start-up have used an optimal amount of PGA memory. This is, of course, ideal but rarely happens except maybe for pure OLTP systems. In reality, some work areas run one-pass or even multi-pass, depending on the overall size of the PGA memory. When a work area cannot run optimally, one or more extra passes is performed over the input data. This reduces the cache hit percentage in proportion to the size of the input data and the number of extra passes performed. this value if computed from the "total bytes processed" and "total extra bytes read/written" statistics available in the same view using the following formula:
 
                                        total bytes processed * 100
PGA Cache Hit Ratio =  ------------------------------------------------------
                                       (total bytes processed + total extra bytes read/written)

 
- V$SQL_WORKAREA_HISTOGRAM (Oracle92 only)
This view shows the number of work areas executed with optimal memory size, one- pass memory size, and multi-pass memory size since instance start-up. Statistics in this view are subdivided into buckets that are defined by the optimal memory requirement of the work area. Each bucket is identified by a range of optimal  memory requirements specified by the values of the columns LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE.  The following query shows statistics for all nonempty buckets:

SELECT low_optimal_size/1024 AS low_kb,
       (high_optimal_size+1)/1024 AS high_kb,
       ROUND(100*optimal_executions/total_executions) AS "Opt_Executions",
       ROUND(100*onepass_executions/total_executions) AS "OnePass_Execut",
       ROUND(100*multipasses_executions/total_executions) AS "MultiPass_Execut"
 FROM v$sql_workarea_histogram
 WHERE total_executions != 0
 ORDER BY low_kb;
 
LOW_KB HIGH_KB Opt_Executions     OnePass_Execut     MultiPass_Execut
------ ------- ------------------ ------------------ ----------------------
8      16      156255             0                   0
16     32      150                0                   0
32     64      89                 0                   0
64     128     13                 0                   0
128    256     60                 0                   0
256    512     8                  0                   0
512    1024    657                0                   0
1024   2048    551                16                  0
2048   4096    538                26                  0
4096   8192    243                28                  0
8192   16384   137                35                  0
16384  32768   45                 107                 0
32768  65536   0                  153                 0
65536  131072  0                  73                  0
131072 262144  0                  44                  0
262144 524288  0                  22                  0
 
The query result shows that, in the 1024 KB to 2048 KB bucket, 551 work areas used an optimal amount of memory, while 16 ran in one-pass mode and none ran in multi-pass mode. It also shows that all work areas under 1 MB were able to run in optimal mode. You can also use V$SQL_WORKAREA_HISTOGRAM to find the percentage of times work areas were executed in optimal, one-pass, or multi-pass mode since start-up. Example :
SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
               onepass_count, round(onepass_count*100/total, 2) onepass_perc,
               multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM  (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
               sum(OPTIMAL_EXECUTIONS) optimal_count,
               sum(ONEPASS_EXECUTIONS) onepass_count,
               sum(MULTIPASSES_EXECUTIONS) multipass_count
        FROM   v$sql_workarea_histogram
        WHERE  low_optimal_size > 64*1024);   ---- for 64 K optimal size 

 
3- The Third and last step is tuning the PGA_AGGREGATE_TARGET. In Oracle 9i Release 2 we have 2 new views that help us in this task 
 - V$PGA_TARGET_ADVICE
 - V$PGA_TARGET_ADVICE_HISTOGRAM
 
By examining these two views, you will be able to determine how key PGA statistics will be impacted if you change the value of PGA_AGGREGATE_TARGET.
To enable automatic generation of PGA advice performance views, make sure the following parameters are set:
- PGA_AGGREGATE_TARGET
- STATISTICS_LEVEL. Set this to TYPICAL (the default) or ALL; setting this parameter to BASIC turns off generation of PGA performance advice views.
 
The content of these PGA advice performance views is reset at instance start-up or when PGA_AGGREGATE_TARGET is altered.
V$PGA_TARGET_ADVICE view predicts how the statistics cache hit percentage and over allocation count in V$PGASTAT will be impacted if you change the value of  the initialization parameter PGA_AGGREGATE_TARGET. The following select statement can be used to find this information 
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;

TARGET_MB  CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
63         23             367
125        24             30
250        30             3
375        39             0       --Lowest value that could be assigned
500        58             0
600        59             0
700        59             0
800        60             0
900        60             0
1000       61             0
1500       67             0
2000       76             0
3000       83             0
4000       85             0
 
From the above results we should set the PGA_AGGREGATE_TARGET parameter to a value where we avoid any over allocation, so the lowest PGA_AGGREGATE_TARGET value we can set is 375 ( where ESTD_OVERALLOC_COUNT is 0)
After eliminating over-allocations, the goal is to maximize the PGA cache hit percentage, based on your response-time requirement and memory constraints.
 
V$PGA_TARGET_ADVICE_HISTOGRAM view predicts how the statistics displayed by the performance view V$SQL_WORKAREA_HISTOGRAM will be impacted if you change the value of the initialization parameter PGA_AGGREGATE_TARGET. You can use that dynamic view to view detailed information on the predicted number of optimal, one-pass and multi-pass work area executions for the set of PGA_AGGREGATE_TARGET values you use for the prediction.  

Oracle Advice
Oracle9i r2 now has three predictive utilities:

• Data Cache advice - The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size. Keep in mind that the data from STATSPACK can provide similar data as v$db_cache_advice, and most Oracle tuning professionals use STATSPACK and v$db_cache_advice to monitor the effectiveness of their data buffers.
• Shared Pool advice - This advisory functionality has been extended in Oracle9i release 2 to include a new advice called v$shared_pool_advice, and there is talk to expending the advice facility to all SGA RAM areas in future releases of Oracle.
  
• PGA advice - Oracle9i has introduced a new advisory utility dubbed v$pga_target_advice.  This utility will show the marginal changes in optimal, one-pass, and multipass PGA execution for different sizes of pga_aggregate_target, ranging from 10% to 200% of the current value.

These advisory utilities are extremely important for the Oracle DBA who must adjust the sizes of the RAM areas to meet current processing demands.

Using Data Cache Advice with v$db_cache_advice
You can have Oracle estimate what cache hit ratios would be like if buffer caches were larger or smaller.

• Set DB_CACHE_ADVICE to ON. This is a dynamic parameter, so you don't have to bounce the instance to turn this feature on and off. By default this parameter is OFF
  
• Query v$db_cache_advice for the desired buffer pool and block size.
  
• Oracle maintains physical I/O estimates for 20 cache sizes ranging from 10% to 200% of current size.
  
• This mechanism is supposed to be more accurate and use less overhead than the v$current_bucket view provided back in Oracle8.

The following query can be used to perform the cache advice function, once the db_cache_advice has been enabled and the database has run long enough to give representative results
cache_advice.sql
-- ****************************************************************
-- Display cache advice. Example for 304MB Size
-- ****************************************************************
--# db_cache_advice.sql
col size_for_estimate for 999999 head 'Cache Size (MB)'
col estd_physical_read_factor for 999.90 head 'Estd Phys|Read Factor'
col estd_physical_reads for 999,999,999 head 'Estd Phys| Reads'
SELECT name, block_size, size_for_estimate, estd_physical_read_factor, estd_physical_reads
  FROM V$DB_CACHE_ADVICE
  WHERE advice_status = 'ON';

The output from the script is shown below.  Note that the values range from 10 percent of the current size to double the current size of the db_cache_size.
                                Estd Phys       Estd Phys
NAME                 BLOCK_SIZE Cache Size (MB) Read Factor        Reads
-------------------- ---------- --------------- ----------- ------------
DEFAULT                    8192             112        4.03   22,276,175  10% SIZE
DEFAULT                    8192             224        2.69   14,840,036
DEFAULT                    8192             336        1.49    8,241,584
DEFAULT                    8192             448        1.37    7,584,065
DEFAULT                    8192             560        1.27    7,009,869
DEFAULT                    8192             672        1.20    6,644,218
DEFAULT                    8192             784        1.11    6,153,303
DEFAULT                    8192             896        1.06    5,859,825
DEFAULT                    8192            1008        1.03    5,684,688
DEFAULT                    8192            1120        1.02    5,628,375
DEFAULT                    8192            1200        1.00    5,523,760   CURRENT SIZE
DEFAULT                    8192            1232         .99    5,446,959
DEFAULT                    8192            1344         .97    5,383,602
DEFAULT                    8192            1456         .96    5,314,650
DEFAULT                    8192            1568         .95    5,271,983
DEFAULT                    8192            1680         .94    5,200,616
DEFAULT                    8192            1792         .92    5,082,878
DEFAULT                    8192            1904         .90    4,956,648
DEFAULT                    8192            2016         .88    4,863,947
DEFAULT                    8192            2128         .85    4,668,080
DEFAULT                    8192            2240         .68    3,763,293   2X TIMES SIZE

From the above listing we see that increasing the db_cache_size from 1200MB to 1232MB would result in approximately 100,000 less physical reads.

Using Shared Pool Advice to size your Shared Pool properly
This advisory functionality has been extended in Oracle9i release 2 to include a new advice called v$shared_pool_advice, and there is talk to expending the advice facility to all SGA RAM areas in future releases of Oracle.
The v$shared_pool_advice show the marginal difference in SQL parses as the shared pool changes in size from 10% of the current value to 200% of the current value. The Oracle documentation contains a complete description for the set-up and use of shared pool advice, and it is very simple to configure. Once it is installed, you can run a simple script to query the v$shared_pool_advice view and see the marginal changes in SQL parses for different shared_pool sizes
-- *************************************************************
-- Display shared pool advice. Example for current Size = 96MB
-- *************************************************************
set lines  100
set pages  999
column        c1     heading 'Pool |Size(M)'
column        c2     heading 'Size|Factor'
column        c3     heading 'Est|LC(M)  '
column        c4     heading 'Est LC|Mem. Obj.'
column        c5     heading 'Est|Time|Saved|(sec)'
column        c6     heading 'Est|Parse|Saved|Factor'
column c7     heading 'Est|Object Hits'   format 999,999,999
SELECT
   shared_pool_size_for_estimate  c1,
   shared_pool_size_factor        c2,
   estd_lc_size                   c3,
   estd_lc_memory_objects         c4,
   estd_lc_time_saved             c5,
   estd_lc_time_saved_factor      c6,
   estd_lc_memory_object_hits     c7
FROM
   v$shared_pool_advice;
                                                   Est        Est
                                                  Time      Parse
     Pool        Size        Est     Est LC      Saved      Saved          Est
   Size(M)     Factor    LC(M)    Mem. Obj.      (sec)     Factor  Object Hits
---------- ---------- ---------- ---------- ---------- ---------- ------------
        48         .5         48      20839    1459645          1  135,756,032
        64      .6667         63      28140    1459645          1  135,756,101
        80      .8333         78      35447    1459645          1  135,756,149
        96          1         93      43028    1459645          1  135,756,253
       112     1.1667        100      46755    1459646          1  135,756,842
       128     1.3333        100      46755    1459646          1  135,756,842
       144        1.5        100      46755    1459646          1  135,756,842
       160     1.6667        100      46755    1459646          1  135,756,842
       176     1.8333        100      46755    1459646          1  135,756,842
       192          2        100      46755    1459646          1  135,756,842

Here we see the statistics for the shared pool in a range from 50% of the current size to 200% of the current size. These statistics can give you a great idea about the proper size for the shared_pool_size. If you are automatic the SGA region sizes with automated "alter system" commands, creating this output and writing a program to interpret the results is a great way to ensure that the shared pool and library cache always have enough RAM.

The Oracle Enterprise Manager Shared Pool Sizing Advisor guides you in finding the optimal size for the shared pool, based on the relative change in parse time savings. The SGA Advisor is available from Enterprise Manager by selecting:
Databases > Database Name > Instance > Configuration > Memory tab > Advice in the SGA section.
 

Using PGA Advice Utility
The new statistics to help you monitor the performance of the PGA memory component for a particular value of PGA_AGGREGATE_TARGET are based on two concepts: work areas groups and a computed PGA cache hit percentage value. By grouping work areas based on their optimal memory requirement, statistics can be collected for each group based on the number of optimal, one-pass, and multi-pass executions for each of these work area groups. So now you can more accurately predict how much memory is required across the instance to maximize the number of optimal executions.
The PGA cache hit percentage summarizes statistics on work area executions into a single measure of PGA performance for a given value of PGA_AGGREGATE_TARGET. The PGA cache hit percentage is derived from the number of work areas, which run optimal, the number of passes for the non-optimal work areas, and the sizes of the work areas.
The new view, v$sql_workarea_histogram, enables you to study the nature of the work area workload over a chosen time period. The work areas are split into 33 groups based on their optimal memory requirements. Each group is defined by the lower bound on its optimal memory requirement, displayed in the low_optimal_size column, and its upper bound, displayed in the high_optimal_size column. For each group, the view accumulates the number of executions using optimal, one-pass, multi-pass memory since instance start up. These are displayed in the optimal_executions, onepass-executions, and multipasses_executions columns respectively. The total_executions column contains the sum of the other three execution counts. To determine if you have set a good value for the PGA target size, query the v$sql_workarea_histogram view using a statement such as the following, which converts the low_optimal_size and high_optimal_size column values to kilobytes:
SELECT low_optimal_size/1024 AS low_kb,
       (high_optimal_size+1)/1024 AS high_kb,
       ROUND(100*optimal_executions/total_executions) AS "Opt_Executions",
       ROUND(100*onepass_executions/total_executions) AS "OnePass_Execut",
       ROUND(100*multipasses_executions/total_executions) AS "MultiPass_Execut"
 FROM v$sql_workarea_histogram
 WHERE total_executions != 0
 ORDER BY low_kb;

    LOW_KB    HIGH_KB Opt_Executions OnePass_Execut MultiPass_Execut
---------- ---------- -------------- -------------- ----------------
         8         16            100              0                0
        16         32            100              0                0
        32         64            100              0                0
        64        128            100              0                0
       128        256            100              0                0
       256        512            100              0                0
       512       1024            100              0                0
      1024       2048            100              0                0
      2048       4096            100              0                0
      4096       8192            100              0                0
      8192      16384            100              0                0
     16384      32768            100              0                0
     32768      65536             17             83                0
     65536     131072              0            100                0
    131072     262144              0            100                0
    262144     524288              0            100                0
    524288    1048576              0            100                0

Oracle9i also introduces a new parameter called WORKAREA_SIZE_POLICY. When this parameter is set to automatic (default), all Oracle connections will benefit from the shared PGA memory. When WORKAREA_SIZE_POLICY is set to manual, connections will allocate memory according to the values for the sort_area_size parameter. Although it would be ideal for all work areas to execute in the optimal size, this goal is typically achieved by over-allocating memory to the PGA. If you graph the results of your query as a histogram, you can quickly identify the location in the graph where work groups begin to use one-pass, or even multi-pass, sizes
If you monitor the PGA space consumption with the various views provided for that purpose (v$sql_workarea_histogram, v$pgastat, v$sql_workarea, and v$sql_workarea_active), you may decide that you need to reset your PGA_AGGREGATE_TARGET initialization parameter value to make better use of your available memory. To help you determine by what factor you should change the parameter value, you can use two new views(v$pga_target_advice and v$pga_target_advice_histogram) that accumulate advice statistics to help you tune the PGA_AGGREGATE_TARGET value.
The views are only populated if PGA_AGGREGATE_TARGET is set to a non-zero value that enables auto management of the PGA. Also the initialization parameter STATISTICS_LEVEL value must be set to Typical or All. . Further, the view contents are refreshed when you shut down the instance or when you change the value of PGA_AGGREGATE_TARGET dynamically.
Rows in these two views correspond to setting the PGA_AGGREGATE_TARGET from 0.125 to 8 times its current value. However, if these values are either less than 10MB or more than 256GB, they will not be included in the output.
To begin monitoring and tuning the PGA target size, you should issue query against the v$pga_target_advice view similar to:

-- ************************************************
-- Display pga target advice
-- ************************************************
column c1 heading 'Estimated|Target(M)'
column c2 heading 'Estimated|Cache Hit %'
column c3 heading 'Estimated|Over-Alloc'
column c4 heading 'Size|Factor'
SELECT ROUND(pga_target_for_estimate /(1024*1024)) c1,
       pga_target_factor c4,
              estd_pga_cache_hit_percentage c2,
              estd_overalloc_count c3
FROM v$pga_target_advice;

Estimated  Size    Estimated     Estimated
Target(M)  Factor  Cache Hit %  Over-Alloc
---------- ------- ----------- -----------
188           .125          93           0
376            .25          95           0
752             .5          96           0
1128           .75          97           0
1504             1          98           0
1805           1.2          8            0
2106           1.4          98           0
2406           1.6          98           0
2707           1.8          98           0
3008             2          98           0
4512             3          98           0
6016             4          98           0
9024             6          98           0
12032            8          98           0

The output from the query contains estimated statistics based on the multiples of the current PGA_AGGREGATE_TARGET value. Assuming that your query produced the output shown above with the PGA_AGGREGATE_TARGET value set to 1500MB, follow these steps to evaluate the results:
Step 1: Identify the first row with a value of zero in the "Estimated Over-Alloc" column. The rows over this one are for PGA_AGGREGATE_TARGET values (shown in the target_mb column) that are too small for the minimum PGA memory needs. In this case, this is the fifth row, which contains data for the current PGA_AGGREGATE_TARGET value, 500MB. Had the target_mb column value in the first row with a zero estd_overallocation_count been larger than the current setting, you should increase the PGA_AGGREGATE_TARGET parameter to at least this size.
Step 2: Examine the rows with PGA values larger than the minimum required to find the first pair of adjacent rows with values in the cache_hit_percent column that are within a few percentage points of each other. These rows indicate where, were you to graph the values, you would see an inflection point (sometimes referred to as a “knee”) in the curve. The optimal value for the PGA_AGGREGATE_TARGET parameter is at this inflection point, represented by the target_mb column value in the first of these two rows. Based on the above results, you should set the PGA_AGGREGATE_TARGET to 3000MB if you have sufficient memory. If you have even more memory available, you should assign it to some other use, such as one of the SGA components, rather than increasing the PGA target size.

To refine your analysis, you can look at the contents of the v$pga_target_advice_histogram view. This view is based on the v$sql_workarea_histogram view discussed earlier and contains rows for each of the same 33 work area groups used in that view. However, as with the v$pga_target_advice view, estimated data for each value is created for 14 multiples of the current PGA_AGGREGATE_TARGET setting. Therefore the v$pga_target_advice_histogram view potentially contains 14 different sets of values for each of the 33 work area groups, giving a possible total of 462 rows.

In addition to the columns corresponding to the work area lower and upper bounds and execution count columns in v$sql_workarea_histogram, v$pga_target_advice_histogram contains columns showing the size of PGA_AGGREGATE_TARGET used for the estimates in the row, the factor (multiple) of the current PGA_AGGREGATE_TARGET size this represents, whether advice is currently off or on (based on the setting of the STATISTICS_LEVEL parameter), and the number of work areas not included in the estimates due to space limitations in the instance. You would typically execute queries against the v$pga_target_advice_histogram view in the same way, and over the same period, as you do for your queries against v$pga_target_advice. This way, you can check if a new value for PGA_AGGREGATE_TARGET that you identify by the method just discussed would result in reasonable optimal, one-pass, and multi-pass mode processing. You can select the appropriate rows by querying an appropriate value for the pga_target_factor column.
The following statement would generate output based on the value of 3000MB (that is, 6 times the current size) identified in the previous step.
SELECT low_optimal_size/1024 AS low_kb,
       (high_optimal_size+1)/1024 AS high_kb,
       estd_optimal_executions AS optimal,
       estd_onepass_executions AS onepass,
       estd_multipasses_executions AS mpass
FROM v$pga_target_advice_histogram
WHERE pga_target_factor = 6
    AND estd_total_executions != 0
ORDER BY low_kb;

The Power of Cursor Sharing in 9i
Oracle9i allows existing applications to improve SQL reuse by setting the CURSOR_SHARING initialization parameter dinamically:

• EXACT - (Default) only statements with an exact text match will share the same SQL area.
• FORCE - Replace literal values with bind variables without checking for the existence of histograms or skewed data 
• SIMILAR - Use literal value only if it would make a difference to the execution plan to do so. For example, where there are histograms present or highly skewed data

In an ideal world the application should be written to encourage cursor sharing, but existing applications may gain reduced memory usage, faster parses, and reduced latch contention when using this option.

 

CASE statements and expressions
CASE statements and expressions are a shorthand way of representing IF/THEN choices with multiple alternatives The CASE expression was first added to SQL in Oracle8i. Oracle9i extends its support to PL/SQL to allow CASE to be used as an expression or statement:

• Value Match CASE Expression
• Searched CASE Expression
• Value Match CASE Statement
• Searched CASE Statement

Value Match CASE Expression
The CASE expression is a more flexible version of the DECODE function. In its simplest form it is used to return a value when a match is found:
SELECT ename, empno,
  (CASE deptno
         WHEN 10 THEN 'Accounting'
         WHEN 20 THEN 'Research'
         WHEN 30 THEN 'Sales'
         WHEN 40 THEN 'Operations'
         ELSE 'Unknown'
   END) department
FROM emp
ORDER BY ename;

The value match CASE expression is also supported in PL/SQL:
SET SERVEROUTPUT ON
DECLARE
  deptno     NUMBER := 20;
  dept_desc  VARCHAR2(20);
BEGIN
  dept_desc := CASE deptno
                 WHEN 10 THEN 'Accounting'
                 WHEN 20 THEN 'Research'
                 WHEN 30 THEN 'Sales'
                 WHEN 40 THEN 'Operations'
                 ELSE 'Unknown'
               END;
  DBMS_OUTPUT.PUT_LINE(dept_desc);
END;
/

Searched CASE Expression
A more complex version is the searched CASE expression where a comparison expression is used to find a match. In this form the comparison is not limited to a single column:
SELECT ename, empno,
  (CASE
        WHEN sal < 1000 THEN 'Low'
        WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'
        WHEN sal > 3000 THEN 'High'
        ELSE 'N/A'
  END) salary
FROM emp
ORDER BY ename;

The searched CASE expression is also supported in PL/SQL:
SET SERVEROUTPUT ON
DECLARE
  sal       NUMBER := 2000;
  sal_desc  VARCHAR2(20);
BEGIN
  sal_desc := CASE
                           WHEN sal < 1000 THEN 'Low'
                           WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'
                           WHEN sal > 3000 THEN 'High'
                           ELSE 'N/A'
                    END;
  DBMS_OUTPUT.PUT_LINE(sal_desc);
END;
/

New Date/Time Types
The new datatype TIMESTAMP records time values including fractional seconds. New datatypes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE allow you to adjust date and time values to account for time zone differences. You can specify whether the time zone observes daylight savings time, to account for anomalies when clocks shift forward or backward. New datatypes INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH represent differences between two date and time values, simplifying date arithmetic. Examples:
TIMESTAMP (fractional_seconds_precision)
Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6.
TIMESTAMP (fractional_seconds_precision) WITH TIME ZONE
All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. 
TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE
All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:
Data is normalized to the database time zone when it is stored in the database.
When the data is retrieved, users see the data in the session time zone.
INTERVAL YEAR (year_precision) TO MONTH
Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2.
INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)
Stores a period of time in days, hours, minutes, and seconds, where day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2. fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.

SELECT SYSDATE,
       TO_CHAR(SYSDATE,
       'MM/DD/YYYY HH24:MI:SS') sysdate_prior9i,
       TO_CHAR(SYSDATE,
       'MM/DD/YYYY HH24:MI:SS.SSSSSSSSSSSS') sysdate_9i
FROM DUAL;

SYSDATE   SYSDATE_PRIOR9I     SYSDATE_9I
--------- ------------------- --------------------------------
21-MAY-03 05/21/2003 12:23:01 05/21/2003 12:23:01.445814458101

Oracle9i DataGuard (previously called Standby)
DataGuard maintains one or more synchronized copies of a customers production data. An Oracle9i DataGuard configuration consists of a collection of loosely connected systems, that combine the primary database and physical standby databases into a single, easily managed disaster recovery solution. Often, the sites in a DataGuard configuration are dispersed geographically and connected by Oracle*Net. As the primary database is modified, the physical standby database is sent log information generated by changes made to the primary database. These changes are applied to the standby database, which runs in managed recovery mode. While the primary database is open and active, a standby database is either performing a recovery or open for reporting access. Should something go wrong with primary, a standby database can be activated to replace it. A new background process DMON monitors the primary and standby databases and can be configured either by OEM using the new DataGuard Manager tool or through the command line using the new DGMGRL utility. DMON processes run on both primary and standby servers. There are 2 new terms of reference to learn:

• 'No data loss mode' is the ability to guarantee recovery of 100% of all committed transactions. This generally means that LGWR, not ARCH is now responsible for transferring the redo stream to the standby server(s).
• 'No data divergence' is essentially the same as no data loss mode with the added security of guaranteeing that primary and standby databases must be able to be kept in synch. That means that if redo logs cannot be transmitted from Primary to Secondary, then no more redo is allowed to be generated. In fact the primary will be shutdown to prevent this.

alter database add standby logfile group 1 '/u01/oradata/SID1/SID1_g1.rdo' size 80M;

 
Oracle standby server can now run in 4 data protection modes as follows:

The last mode there, delayed protection is exactly how the old form of standby database behaved. It cannot guarantee no data loss because ARCH is responsible for transmitting redo logs that already contain committed transactions.
 

Database switchover

• shutdown database normal (not abort)
• Check all archive logs have been transmitted to standby node(s)
• Check that the primary's online redo logs are intact
• alter database commit to switchover to physical standby;
• shutdown normal;
• startup nomount;
• alter database mount standby database;

• alter database commit to switchover to physical primary;
• shutdown normal;
• startup;

• recover managed standby database parallel n;
• recover managed standby database [disconnect from session];

Oracle9i LogMiner
Have you ever wondered who was responsible for changing the salary table to zero? Would it be useful to locate the offending SQL statement and be provided with SQL required to correct it? This is a great tool for undoing a single erroneous transaction without having to go through a database restore.
Every change made to an Oracle database by default generates undo and redo information which is accumulated in Oracle redo log files. Oracle9i LogMiner is an integrated feature of the Oracle9i that provides DBA's and auditors with the infrastructure required for relational access to Oracle's redo stream. Some significant enhancements to LogMiner for Oracle9i generated log files include:

• A new LogMiner Viewer GUI in addition to the command line interface
• The ability to translate DML associated with Index Clusters
• The ability to mine for changes by value
  
• Support for chained and migrated rows
  
• Support for direct path inserts
  
• Ability to use an online dictionary
  
• Ability to extract the data dictionary into the redo log files to seamlessly integrate DDL changes
  
• DDL statement tracking
  
• Ability to skip log corruptions
  
• Ability to specify that only committed transactions be displayed
  
• Generate SQL_REDO and SQL_UNDO with primary key information to help the DBA undo changes

The online data dictionary can be extracted into the redo log stream. This enables off-line analysis and provides a snapshot of the data dictionary that matches the database objects in logs created at that time. When mining logs in the same database that generated it, the user can choose to use the online data dictionary for SQL reconstruction and internal identifier to name mapping which would otherwise be a manual process.
The user can group DML statements into transactions with a COMMITTED_DATA_ONLY option which directs LogMiner to group DML statements into complete transactions. Only committed transactions will be returned in the commit SCN order. When the DDL_DICT_TRACKING option is enabled and LogMiner is run in the context of an open database, it will automatically apply DDL statements executed in the original redo stream to its internal dictionary. This enables correct reconstruction of correct SQL statements on tables whose definition has been altered or were missing when the original dictionary dump was captured.  LogMiner automatically versions the metadata kept in the database.
New DDL statements have been added to Oracle's vocabulary to allow for logging of additional column values in case of updates. The extra information can be used either to identify the updated row logically or to capture a before-row image. This allows a DBA or auditor to use the additional column information to construct more complete statements to undo changes or to create SQL statements for applying to a different databases.
A powerful new capability allows for queries that can filter out rows based on actual data values of the columns updated. For instance it is possible to write a query for a postal database that identifies all lottery winners who moved to 90210 after making it big in Redwood Shores.
LogMiner improves data availability by providing a window into all changes made to a database. It enables auditing of database changes and reduces the time and effort needed for data recovery.
 

Oracle9i Flashback Query
Oracle9i's flashback query provides users with the capability of viewing data in the past. In the first release of Oracle9i, the only method available to invoke flashback query was to execute the system supplied package DBMS_FLASHBACK. Here is an example that goes back five minutes:
    EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME (SYSDATE - (5/(24*60)));
The above statement sends the session back in time for the duration of that session or until the EXECUTE DBMS_FLASHBACK.DISABLE is executed. Standard date and time SQL functions are used to determine the time in the past the data will be retrieved from. The drawback was that data could not be updated during a flashback query enabled session. Savvy programmers were able to save historical data by placing it into a cursor and then dumping the contents of the cursor into a worktable after the FLASHBACK.DISABLE procedure was executed.
In Oracle9i Release 2, the AS OF timestamp clause has been added to the SELECT statement to enable flashback query on a specific table or set of tables. Developers are able to specify the AS OF clause for a single-table, multiple-tables (joins) as well as specify different times for different tables. The AS OF timestamp clause can also be used inside INSERT or CREATE TABLE AS SELECT statements. Here is an example of a SELECT statement using the AS OF timestamp clause:
     UPDATE emp.employee_payroll_table SET emp_salary =     
           (SELECT emp_salary FROM emp.employee_payroll_table
               AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)
               WHERE emp_last_name = 'FOOT')
      WHERE emp_last_name = 'FOOT';

The statement above uses the SYSTIMESTAMP value with an INTERVAL function to update the emp.employee_payroll_table with data that is 24 hours old.
The AS OF timestamp clause (and its DBMS_FLASHBACK.ENABLE_AT_TIME counterpart) maps the timestamp value to a SCN value. Oracle records the SCN-TIMESTAMP mapping at five-minute intervals. This means that the time you may actually retrieve the data from could be rounded down by up to five minutes. For example, you could access a table using a timestamp that is a few minutes after the table is created and receive an error because flashback query used a SCN value that is lower than the actual time when the table was created.

Using Flashback Query
Before Flashback Query functionality can be used by ordinary users, some actions are required from the database administrators:

• Initialization parameter UNDO_MANAGEMENT=AUTO must be set in the init.ora file.
• Another initialization parameter UNDO_RETENTION must specify a time period (in seconds) for which old data has to be preserved in the database.
  
• EXECUTE privilege must be granted on the DBMS_FLASHBACK package to the users, roles, and applications that need to perform Flashback Queries.

Once these actions have been performed, users can access the old image of data that was modified by a DML statement. Before this data can be accessed, users have to call the package DBMS_FLASHBACK.ENABLE_AT_TIME(param) or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(param) to enable flashback query. Once flashback query is enabled, all queries performed will apply to the state of the database at the time specified by the input parameter to the package. After all the required queries have been performed on the past data, Flashback Query feature is disabled by calling package DBMS_FLASHBACK.DISABLE. From this point on all queries will access the current data in the database.

Flashback Query Applications
The Flashback Query feature can be used for:

• Undoing users errors, such as, incorrect deletes or updates after they have been committed without necessitating intervention from the database administrators or causing any database outage.
• Creating applications with built-in end user self-service error correction capabilities.

These applications of Flashback Query liberate database administrators from the task of correcting user errors and empower the users to recover from their errors by giving access to previously unavailable data through a simple, completely non-intrusive interface. This not only enhances database administrator and user productivity but also reduces overall database administration costs.
 

Reorganize Tables and Indexes Online. Rename Columns and Constraints
One of the most exciting new online features in Oracle9i is the online table redefinition feature using the DBMS_REDEFINITION package. The new feature allows DBA's to redefine tables online:

• Modify the physical attributes or storage parameters of a table
• Move a heap table or IOT to a different tablespace
• Add support for parallel queries
  
• Add or drop partitioning support
  
• Recreate a heap table or IOT to reduce fragmentation
  
• Change a heap table to IOT and vice versa
  
• Add, drop, or rename columns in a table (easy as ALTER TABLE xx RENAME COLUMN xxx TO yyy;)
• Rename a Constraint (using ALTER TABLE tablename RENAME CONSTRAINT Old_constraint_name TO New_Constraint_name;)
  
• Transform data in a table

Renaming Columns and Constraints
In addition to renaming tables and indexes Oracle9i Release 2 allows the renaming of columns and constraints on tables. In this example I have a table and the following PK:

SQL> DESC test1
 Name                 Null?    Type
 -------------------- -------- --------------------
 COL1                 NOT NULL NUMBER(10)
 COL2                 NOT NULL VARCHAR2(50)

SQL> SELECT constraint_name FROM   user_constraints
  3  WHERE  table_name      = 'TEST1'
  4  AND    constraint_type = 'P';

CONSTRAINT_NAME
------------------------------
TEST1_PK

SQL> SELECT index_name, column_name FROM   user_ind_columns
  3  WHERE  table_name = 'TEST1';

INDEX_NAME            COLUMN_NAME
--------------------  --------------------
TEST1_PK              COL1

Redefine a Table OnLine
When a table is redefined online, it is accessible by all read and write operations during the redefinition process. Administrators have control over when to switch from the original to the newly redefined table. The switch process is very brief and is independent of the size of the table or the complexity of the redefinition. The redefinition process effectively creates a new table and improves its data block layout efficiency.
The new Oracle9i online table redefinition feature improves data availability, database performance, response time and disk space utilization.
Additionally, during an application upgrade, administrators can start the redefinition process, then periodically synchronize the new image of the table so that the new image of the table includes the latest changes to the original table. This reduces the amount of time to complete the final redefinition step. Administrators can also validate and use the data in the new image of the table before completing the redefinition process. This is a very useful feature for administrators, who must ensure that the upgrade process goes smoothly. Prior to executing this package, the EXECUTE privilege must be granted on the DBMS_REDEFINITION package or the EXECUTE_CATALOG_ROLE must be granted to the schema.
There are several restrictions that you should recognize before attempting to redefine a table online:

• The table being redefined must have a primary key.
• The source table (the one being redefined) and the target table must have the same primary key column.
• The table cannot be part of a cluster.
• The table cannot be an overflow table for an IOT.
• The table cannot have user-defined types.
• The table cannot have a BFILE column or a LONG column. The table may, however, contain LOB columns.
• The table cannot belong to the SYS or SYSTEM schema.
• If a new column is being added, it cannot be defined NOT NULL.
• The table may not have materialized views or have materialized view logs defined on the table.
• Both tables in the redefinition process must belong to the same schema.

There are five basic steps to redefine a table:
1. Create a new image of the table with all of the desired attributes.
2. Start the redefinition process.
3. Create any triggers, indexes, grants and constraints on the new image of the table.
4. Optionally synchronize and validate data in the new image of the table periodically.
5. Complete the redefinition of the table
 
Creation Script:                                        dbmshord.sql
Called By:                                                catproc.sql script
Located in :                                              $ORACLE_HOME/rdbms/admin Directory
Grant Privilege on Package to Schema:     EXECUTE Privilege or EXECUTE_CATALOG_ROLE

DBMS_REDEFINITION Package Example
Create Employee Table
CREATE TABLE s_employee
(employee_id         NUMBER(7)     CONSTRAINT s_employee_id_nn         NOT NULL,
 employee_last_name  VARCHAR2(25)  CONSTRAINT s_employee_last_name_nn  NOT NULL,
 employee_first_name VARCHAR2(25),
 userid              VARCHAR2(8),
 start_date          DATE,
 comments            VARCHAR2(255),
 manager_id          NUMBER(7),
 title               VARCHAR2(25),
 department_id       NUMBER(7),
 salary              NUMBER(11, 2),
 commission_pct      NUMBER(4, 2),
 CONSTRAINT s_employee_id_pk PRIMARY KEY (employee_id),
 CONSTRAINT s_employee_userid_uk UNIQUE (userid),
 CONSTRAINT s_employee_commission_pct_ck
    CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20)));

25 Records Inserted into Employee Table
Assume the Following Desired:
    - Remove COMMENTS Column
    - Add FULL_NAME Column
    - Change DEPARTMENT_ID Column Name to DEPT_ID
    - Increase Salary of All Employees by 50%

Step 1: Ensure Table Can be Redefined
Execute CAN_REDEF_TABLE Procedure, If Execution Succeeds without Error, Then can Redefine
    EXECUTE dbms_redefinition.can_redef_table('plsql_user','s_employee')
    Success: s_employee Table Can be Redefined

Illustration of a Table that Cannot Be Redefined
CREATE TABLE temp (temp   VARCHAR2(10));
EXECUTE dbms_redefinition.can_redef_table('plsql_user','temp')
begin dbms_redefinition.can_redef_table('plsql_user','temp'); end;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "PLSQL_USER"."TEMP" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 236
ORA-06512: at line 1

Set of Criteria Checked to Ensure Redefinition Allowed
Step 2: Create Temporary Table with New Structure
CREATE TABLE temp_s_employee
(employee_id         NUMBER(7)     CONSTRAINT s_employee_id_nn2 NOT NULL,
 employee_last_name  VARCHAR2(25)  CONSTRAINT s_employee_last_name_nn2 NOT NULL,
 employee_first_name VARCHAR2(25),
 employee_full_name  VARCHAR2(51),
 userid              VARCHAR2(8),
 start_date          DATE,
 manager_id          NUMBER(7),
 title               VARCHAR2(25),
 dept_id             NUMBER(7),
 salary              NUMBER(11, 2),
 commission_pct      NUMBER(4, 2),
 CONSTRAINT s_employee_id_pk2 PRIMARY KEY (employee_id),
 CONSTRAINT s_employee_userid_uk2 UNIQUE (userid),
 CONSTRAINT s_employee_commission_pct_ck2
    CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20)));

Step 3: Start the Redefinition Process
Execute START_REDEF_TABLE Procedure, map Existing Columns to Temporary Table Columns

BEGIN
   dbms_redefinition.start_redef_table('plsql_user',
   's_employee','temp_s_employee',
   'employee_id employee_id,
   employee_last_name employee_last_name,
   employee_first_name employee_first_name,
   employee_first_name||'' ''||employee_last_name employee_full_name,
   userid userid,
   start_date start_date,
   manager_id manager_id,
   title title,
   department_id dept_id,
   salary * 1.5 salary,
   commission_pct commission_pct');
END;
/

Step 4: Start the Redefinition Process
If Singe Quote Desired, Use 2 Single Quotes (Line 7)
Standard SELECT Capabilities Valid in Column Mapping
Once Step 3 Complete, 25 Records Inserted into Temporary Table

 
Step 5: Complete the Redefinition Process
Execute FINISH_REDEF_TABLE Procedure
Synchronizes the Redefinition and Applies the Temporary Architecture and Contents to the Existing Table. Prior to Executing the FINISH_REDEF_TABLE Procedure, Option to Execute SYNC_INTERIM_TABLE Procedure to Synchronize Table Contents and Reduce the Completion Process Time

DBMS_REDEFINITION Package Example
BEGIN
   dbms_redefinition.sync_interim_table('plsql_user',  's_employee','temp_s_employee');
END;
/
BEGIN
    dbms_redefinition.finish_redef_table('plsql_user', 's_employee','temp_s_employee');
END;
/

DESC s_employee
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPLOYEE_ID                     NOT NULL NUMBER(7)
 EMPLOYEE_LAST_NAME              NOT NULL VARCHAR2(25)
 EMPLOYEE_FIRST_NAME                      VARCHAR2(25)
 EMPLOYEE_FULL_NAME                       VARCHAR2(51)
 USERID                                   VARCHAR2(8)
 START_DATE                               DATE
 MANAGER_ID                               NUMBER(7)
 TITLE                                    VARCHAR2(25)
 DEPT_ID                                  NUMBER(7)
 SALARY                                   NUMBER(11,2)
 COMMISSION_PCT                           NUMBER(4,2)

SELECT *  FROM   s_employee WHERE  employee_id = 1;

EMP_ID EMP_L_NAME EMP_F_NAME EMP_FULL_NAME    USERID
------ ---------- ---------- ---------------- --------
1      VELASQUEZ  CARMEN     CARMEN VELASQUEZ cvelasqu

START_DATE MANAGER_ID TITLE     DEPT_ID SALARY COMMISSION_PCT
---------- ---------- --------- ------- ------ --------------
03-MAR-90             PRESIDENT 50        3750

Step 6: Drop the Temporary Table

Aborting a Redefinition
When you invoke START_REDEF_TABLE, there's the chance that something might go wrong. You might, for example, run out of tablespace while copying data from the original table to the target. Or you might suddenly realize that you've created your target table incorrectly. Either way, you'll need to abort the redefinition process and start over. To abort a redefinition, invoke the ABORT_REDEF_TABLE procedure. The following aborts the redefinition for my example:
BEGIN
   DBMS_REDEFINITION.ABORT_REDEF_TABLE ('gennick','gnis','gnis_redef');
END;
/
Be sure to call ABORT_REDEF_TABLE in the event that START_REDEF_TABLE fails with an error. The abort procedure removes the materialized view and materialized view log created by START_REDEF_ TABLE and does other needed cleanup. You can also call ABORT_REDEF_TABLE anytime before invoking FINISH_REDEF_ TABLE if you simply change your mind about the redefinition.
There are two things ABORT_REDEF_TABLE does not do: It does not delete any of the data inserted into the target table by START_REDEF_TABLE, and it does not drop the target table. When you abort a redefinition, you'll need to deal with the target table and any data it contains. 

Database Resource Manager
One of the most critical challenges for database administrators is to maintain a given performance level with limited hardware resources. Traditionally, it has been up to the operating system (OS) to regulate the resource management among the various applications running on a system including Oracle databases. However, since the OS can not distinguish one Oracle user/session from another, it can not perform any resource management activities among different users or applications sharing a database.

The Database Resource Manager, introduced in Oracle8i, provides database administrators the capability to distribute system resources among various users and applications sharing a database in a manner consistent with business priorities. Using this tool, a DBAs can divide the various users and applications using a database into different resource consumer groups and allocate CPU resources to each one of them in terms of percentages by creating a resource plan.

A Resource Plan can help ensure that system resources are first made available to certain critical business operations before other users or applications can use them. It is also possible to cap the resource consumption of a group of users or an application so that the critical operations are not starved for resources when they need them. The ability to allocate resources at multiple levels and create nested resource plans provides database administrators with an extremely powerful and flexible mechanism to specify how the unused resources should be divided among different resource consumer groups.

Oracle9i features a significantly enhanced Database Resource Manager with new capabilities to enable automatic and proactive management of database workload so as to ensure compliance with service level objectives. Using Oracle9i Database Resource Manager, a database administrator can

• Guarantee certain users or applications a minimum amount of CPU resources regardless of the database workload or number of users
• Limit the degree of parallelism for a given set of users or an application
  
• Instruct the Oracle9i database to proactively detect long running operations and scale back their resource consumption in order to prevent performance degradation for OLTP users
  
• Limit the number of concurrently active long operations
  
• Prevent the execution of unacceptably long running resource intensive operations

These capabilities of the Database Resource Manager allow DBAs to create resource management policies to guarantee measured database services to enterprise applications and users. Oracle9i Database Resource Manager makes it extremely easy to deliver predictable service levels with little intervention while providing a high degree of flexibility to support changing business needs.
   

Resumable Statements
Large operations such as batch updates or data loads can encounter out of space failures after executing for a long period of time, sometimes when they are just about to complete. Under such a circumstance, database administrators are left with no other choice but to re-execute the failed job under a close supervision in order to ensure its successful completion. 
Oracle9i introduces a new feature called "Resumable Space Allocation" which allows users to avoid such failures by instructing the database to suspend any operations that encounter an out of space error rather than aborting them. This provides an opportunity for users or administrators to fix the problem that caused the error while the operation is suspended and automatically resume its execution once the problem has been resolved. By allowing administrators to intervene in the middle of the execution of an operation, Resumable Space Allocation obviates the need of dividing a large job into smaller sub-jobs in order to limit the impact of any failure. Additionally, it will also enable application developers to write applications without worrying about running into space related errors.

A statement can be executed in the "resumable" mode when explicitly specified by using the ALTER SESSION ENABLE RESUMABLE command. Virtually any kind of operation be it a PL/SQL stored procedure, Java stored procedure, queries, SQL*loader, export/import, DML (such as UPDATE, INSERT) and DDL (CREATE TABLE AS SELECT&iquest;., CREATE INDEX , INDEX REBUILD, ALTER TABLE MOVE PARTITION etc..) can all be run as a "resumable" statement. A "resumable" operation will be suspended whenever it encounters one of the following types of failures:

• Out of space condition: The operation can not acquire any more space in a tablespace or when the tablespace can not extend itself by acquiring additional disk space from the OS.
• MAX Extents Reached: The number of extents in a table, index, temp segment, rollback segment, cluster, LOB, table partition, index partition equals the maximum number of extents defined.
  
• User space quota is exceeded.

Once the operation is suspended, a warning to that effect will be written in the alert log file. A trigger can also be used on a new event called "AFTER SUSPEND" either to generate a notification or take corrective actions. Any transactions executed within the trigger will automatically be executed as an autonomous transaction and can therefore include operations such as inserts into a user table for error logging purposes. Users may also access the error data using the "DBMS_RESUMABLE" package and the DBA(USER)_RESUMABLE view.

When the problem that caused the failure is fixed, the suspended statement automatically resumes execution. If the operation encounters a transient problem which may disappear automatically after some time such as temporary tablespace shortage, no administrator intervention may be required to resume its execution. A "resumable" query running out of temporary space may resume automatically with absolutely no user or administrator intervention once other active queries complete. A "resumable" operation may be suspended and resumed multiple times during its execution.

Every "resumable" operation has a time-out period associated with it. The default value of time-out period is 2 hours but can be set to any value using the ALTER SESSION ENABLE RESUMABLE TIMEOUT <time-out period in seconds> command. A suspended operation will automatically be aborted if the error condition is not fixed within "time-out" period. An administrator can abort a suspended operation any time using DBMS_RESUMABLE.ABORT() procedure.
 
When you suspend a transaction, a log is maintained in the alert log. We can use a view, DBA_RESUMABLE, through which we can monitor the progress of the statement and indicate whether the statement is currently executing or suspended. Example:

In the example below we will do the following;
1.      Create a Tablespace with a small size (1MB) datafile
CREATE TABLESPACE TBS_RESUME DATAFILE 'E:\ORACLE9I\EXAMPLES\RESUME\TEST_RESUME01.DBF' SIZE 1M;

2.      Create a table which will use the tablespace
CREATE TABLE TAB_RESUME TABLESPACE TBS_RESUME AS
     SELECT * FROM EMP_EXT WHERE (1=2);

3.      Switch on Resumable mode with Timeout as 1 minute
ALTER SESSION ENABLE RESUMABLE TIMEOUT 60 NAME 'PROBLEM WITH TABLESPACE: TBS_RESUME';

4.      Insert very large data in the table. The data to be inserted should be greater than 1MB
INSERT INTO TAB_RESUME  (SELECT * FROM EMP_EXT);
Since the data is greater than 1MB the process hangs.

5.      Check the alert log for error. Do not fix the error
Error as in alert.log
statement in resumable session
'PROBLEM WITH TABLESPACE:  TBS_RESUME' was suspended due to
ORA-01653: unable to extend table SJM.TAB_RESUME by 32 in tablespace TBS_RESUME

Please note that, the alert log displays the text, PROBLEM WITH TABLESPACE: TBS_RESUME, which we specified alongside the NAME clause when firing the ALTER SESSION command

Error as displayed on SQL Prompt after timeout period
SQL> INSERT INTO TAB_RESUME  ( SELECT * FROM EMP_EXT );
INSERT INTO TAB_RESUME  ( SELECT * FROM EMP_EXT )
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01653: unable to extend table SJM.TAB_RESUME by 32 in tablespace TBS_RESUME
Since the TIMEOUT specified is 60 seconds we do not get enough time to fix the problem.

6.      Switch on Resumable mode with Timeout as 60 minutes
ALTER SESSION ENABLE RESUMABLE  TIMEOUT 3600 NAME  'PROBLEM WITH TABLESPACE: TBS_RESUME'
With 60 minutes, we will have lots of time to fix the problem so that the transaction can be resumed.

7.      Insert very large data in the table. The data to be inserted should be greater than 1MB
INSERT INTO TAB_RESUME  (SELECT * FROM EMP_EXT);

8.      Check the alert log for error. Fix the error by adding one datafile
Error as in alert.log
statement in resumable session
'PROBLEM WITH TABLESPACE:  TBS_RESUME' was suspended due to
ORA-01653: unable to extend table SJM.TAB_RESUME  by 32 in tablespace TBS_RESUME

To fix the problem, add one more datafile to the tablespace. Start another sqlplus session and issue the following command
ALTER TABLESPACE TBS_RESUME ADD DATAFILE 'E:\ORACLE9I\EXAMPLES\RESUME\TEST_RESUME02.DBF' SIZE 10M;

9.      Check the statement status in the first sqlplus session.
SQL> INSERT INTO TAB_RESUME  (SELECT * FROM EMP_EXT);
56644 rows created.
The statement does not hang. It completes the transaction and comes back on the SQL prompt

10.  Check alert.log for any messages.
The alert log shows that the statement resumed after the problem was fixed.

statement in resumable session 'PROBLEM WITH TABLESPACE: TAB_RESUME' was resumed

11.  Check dba_resumable view
SQL> SELECT SQL_TEXT, START_TIME,RESUME_TIME FROM DBA_RESUMABLE;
SQL_TEXT                                START_TIME      RESUME_TIME
------------------------------------------    ----------------------  -----------------
INSERT INTO TAB_RESUME (SELECT * FROM EMP_EXT)    01/30/03 10:24:33  01/30/03 10:28:01

 

Undo Tablespaces or SMU (System Managed Undo)
Oracle Database keeps records of actions of transactions, before they are committed and Oracle needs this information to rollback or Undo the Changes to the database. These records in Oracle are called Rollback or Undo Records. Until Oracle 8i, Oracle uses Rollback Segments to manage the Undo Data. Starting with 9i, the old rollback segment way is referred to as Manual Undo Management Mode and the new Undo Tablespaces method as the Automatic Undo Management Mode. Although both rollback Segments and Undo Tablespaces are supported in Oracle 9i, both modes cannot be used. System Rollback segment exists in both modes. Since we are all familiar with the manual mode, lets look at the features of the Automatic Undo Management (Undo Tablespaces )
Oracle9i Enterprise Manager allows you to create a new UNDO tablespace for a database. In addition,  under instance management, a new "Undo" tab displays the name of the active undo tablespace and the current undo retention time. Administrators can modify the retention time based on their largest transaction time and immediately view the space required for the undo tablespace.  Undo generation rates are calculated based on statistics available for undo space consumption for the current instance.  The "Undo Space Estimate" graph indicates the space requirements based on a maximum undo generation rate and on an average undo generation rate and relates them to the current Undo setting.

Init.ora Parameters for Automatic Undo Management
UNDO_MANAGEMENT : This parameter sets the mode in which oracle manages the Undo Information. The default value for this parameter is MANUAL so that all your old init.ora files can be used without any changes. To set the database in an automated mode, set this value to AUTO. (UNDO_MANAGEMENT = AUTO)

UNDO_TABLESPACE : This parameter defines the tablespaces that are to be used as Undo Tablespaces. If no value is specified Oracle grabs the first available Undo Tablespace or if there are none present, Oracle will use the system rollback segment to startup. This value is dynamic and can be changed online (UNDO_TABLESPACE = UNDO)

UNDO_RETENTION : This value specifies the amount of time, Undo is kept in the tablespace. This applies to both committed and uncommitted transactions since the introduction of FlashBack Query feature in Oracle needs this information to create a read consistent copy of the data in the past. Default value is 900 Secs (UNDO_RETENTION = 500)

UNDO_SUPRESS_ERRORS : This is a good thing to know about in case your code has the alter transaction commands that perform manual undo management operations. Set this to true to suppress the errors generated when manual management SQL operations are issued in an automated management mode.

Creating and Managing Undo Tablespaces :
Undo tablespaces use syntax that is similar to regular tablespaces except that they use the keyword UNDO. These tablespaces can be created during the database creation time or can be added to an existing database using the create UNDO Tablespace command

Create DATABASE uday controlfile ...........
UNDO Tablespace undo_tbs0 datafile '/vol1/data/uday/undotbs0.dbf' ...

Create UNDO Tablespace undo_tbs1
datafile '/vol1/data/uday/undotbs1.dbf' size 25m autoextend on;

All operations like Renaming a data file, Adding a datafile, Online /Offline Swith or Start Backup / End Backup Switch can be made using the regular alter tablespace command. All other operations are managed by Oracle in the automated management mode.

Shrinking the Tablespace
alter system checkpoint;
create undo tablespace UNDOTMEP datafile 'C:\ORACLE\ORA92\DIE\UNDOTMEP.DBF' size 100M AUTOEXTEND OFF;
alter system set undo_tablespace =UNDOTMEP scope=both;
drop tablespace UNDOTBS1 INCLUDING CONTENTS AND DATAFILES; 
alter system checkpoint;
-- Delete the database file for that tablespace if needed
create undo tablespace UNDOTBS1 datafile 'C:\ORACLE\ORA92\DIE\UNDOTBS1.DBF' size 600M AUTOEXTEND ON NEXT 5 M MAXSIZE 1000M;
alter system set undo_tablespace =UNDOTBS1 scope=both;
drop tablespace UNDOTMEP INCLUDING CONTENTS AND DATAFILES;
alter system checkpoint;
-- Delete the database file for that tablespace if needed

Monitoring :
v$UNDOSTAT : This view contains statistics for monitoring the effects of transaction execution on Undo Space in the current instance. These are available for space usage, transaction concurrency and length of query operations. This view contains information that spans over a 24 hour period and each row in this view contains data for a 10 minute interval specified by the BEGIN_TIME and END_TIME.
If you decide to use RESUMABLE OPERATIONS (avisa de errores x espacio), you can wait for the event to be on the alert log file or I can query the DBA_RESUMABLE and USER_RESUMABLE views.

Defining Size
When you are working with UNDO (instead of ROLLBACK), there are two important things to consider:

• The size of the UNDO tablespace
• The UNDO_RETENTION parameter

There are two ways to proceed to optimize your resources.
You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed.  If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity.
This tip help you get the information you need whatever the method you choose. It was tested on Oracle9i (9.2.0.4, 9.2.0.5).

set serverout on size 1000000
set feedback off
set heading off
set lines 132
declare
  cursor get_undo_stat is
         select d.undo_size/(1024*1024) "C1",
                substr(e.value,1,25)    "C2",
                (to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "C3",
                round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec)))             "C4"
           from (select sum(a.bytes) undo_size
                   from v$datafile      a,
                        v$tablespace    b,
                        dba_tablespaces c
                  where c.contents = 'UNDO'
                    and c.status = 'ONLINE'
                    and b.name = c.tablespace_name
                    and a.ts# = b.ts#)  d,
                v$parameter e,
                v$parameter f,
                (select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec from v$undostat)  g
          where e.name = 'undo_retention'
            and f.name = 'db_block_size';
begin
dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) || 'To optimize UNDO you have two choices :'); dbms_output.put_line('====================================================' || chr(10));
  for rec1 in get_undo_stat loop
      dbms_output.put_line('A) Adjust UNDO tablespace size according to UNDO_RETENTION :' || chr(10));
      dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',60,'.')|| ' : ' || TO_CHAR(rec1.c1,'999999') || ' MB');
      dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (' || ltrim(TO_CHAR(rec1.c2/60,'999999')) || ' MINUTES) ',60,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MB');
      dbms_output.put_line(chr(10));
      dbms_output.put_line(chr(10));
      dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :' || chr(10));
      dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',60,'.') || ' : ' || TO_CHAR(rec1.c2/60,'999999') || ' MINUTES');
      dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (' || ltrim(TO_CHAR(rec1.c1,'999999'))
|| ' MEGS) ',60,'.') || ' : ' || TO_CHAR(rec1.c4/60,'999999') || ' MINUTES');
  end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/

New Parameters for Buffer Cache Sizing and Multiple DB_BLOCK_SIZE
The Buffer Cache consists of independent sub-caches for buffer pools and for multiple block sizes. The parameter db_block_size determines the primary block size.
Databases can now have multiple block sizes.

• Every database has a "standard" block size specified by db_block_size.
• The SYSTEM and temporary tablespaces use the standard block size.
• Application tablespaces can use other block sizes.
  
• All partitions of a table or index must use the same block size.
  
• The SGA has a separate buffer cache for each block size.
  
• One nice use for this feature is transporting tablespaces between databases that use different block sizes.

The following are the Size parameters which define the sizes of the caches for buffers for the primary block size:
    DB_CACHE_SIZE
    DB_KEEP_CACHE_SIZE
    DB_RECYCLE_CACHE_SIZE

The db_keep_cache_size and db_recycle_cache_size are independent of db_cache_size.
These parameters are specified in units of memory rather than in units of buffers (as is the case in Oracle8i, or below).

Initialization Parameters Affected
The following parameters are automatically computed:
    DB_BLOCK_LRU_LATCHES - The number of LRU latches in each buffer pool for
        each block size will be equal to the half the number of CPUs.

    DB_WRITER_PROCESSES - The number of DBWR's will be equal to 1/8th the
        number of CPUs.

The following parameters have been deprecated and have been maintained only for backward compatibility:

    DB_BLOCK_BUFFERS
    BUFFER_POOL_KEEP
    BUFFER_POOL_RECYCLE

These parameters cannot be combined with the dynamic SGA feature parameters.Setting these along with the Dynamic SGA parameters would error out.
For example, if db_block_buffers as well as db_cache_size are set, then startup would error out as follows:
    SQL> startup pfile=initv09.ora
    ORA-00381: cannot use both new and old parameters for buffer cache size specification
 

Transport tablespaces with different block sizes between databases
Tablespace transportation between databases owning different block sizes is allowed in Oracle9i.
Oracle9i offers the possibility to plug into a 9i database any tablespace transported from 8i databases of any block size.

Plug an 8i tablespace of 2K db block size into a 9i database of 4K db block size
1. Creation of the tablespace in the 8.1.5 database (db_block_size=2048)
   SQL> create tablespace test_tts datafile '/ora/ora815/oradata/V815/tts01.dbf' size 100k;
   Tablespace created.

   SQL> create table test (c number) tablespace test_tts;
   Table created.

   SQL> insert into test values (1);
   1 row created.

   SQL> commit;
   Commit complete.

   SQL> execute sys.dbms_tts.transport_set_check(ts_list => 'TEST_TTS', incl_constraints => true);
   PL/SQL procedure successfully completed.

   The TRANSPORT_SET_VIOLATIONS view can be used to check for any violations:
   SQL> SELECT * FROM sys.transport_set_violations;
   no rows selected

2. Assuming no violations are produced we are ready to proceed by switching the tablespace to read only mode:
   SQL> alter tablespace test_tts read only;
   Tablespace altered.

3. Export the 8.1.5 tablespace for transportation
  $ exp sys/manager transport_tablespace=y tablespaces=TEST_TTS file=TEST_TTS

4. Ftp the export dump file and datafiles to the 9i database server

5. Import the 8.1.5 tablespace into the 9i database (db_block_size=4096)

   $ imp 'system/manager@oracle9i as sysdba' transport_tablespace=y tablespaces=TEST_TTS datafiles='/oradata/V900/V900/tts01.dbf' file=TEST_TTS

6. The following settings must be prepared before plugging the tablespace owning a block size different from the 9i database block size.
    SQL> alter system set db_2k_cache_size=10m;
   System altered.

   $ imp transport_tablespace=y tablespaces=TEST_TTS datafiles='/oradata/V900/V900/tts01.dbf'

    SQL>  select tablespace_name, block_size from dba_tablespaces;

   TABLESPACE_NAME                BLOCK_SIZE
   ------------------------------ ----------
   SYSTEM                               4096
   UNDOTBS                              4096
   EXAMPLE                              4096
   INDX                                 4096
   TEMP                                 4096
   TOOLS                                4096
   USERS                                4096
   TEST_TTS                             2048

   Execute the following sentence in both databases:
   SQL> alter tablespace test_tts read write;
   Tablespace altered.

 
Plug an 9i tablespace of 8K block size into another 9i database of 4K db block size
1/ Creation of an 8K block size tablespace in the 9.0.0 database (db_block_size=4096)
   SQL> create tablespace tts_8k datafile '/export/home1/ora900/oradata/V900/tts.dbf' size 200k blocksize 8k;
   Tablespace created.

   SQL> create table oe.test (c number) tablespace tts_8k;
   Table created.

   SQL> insert into oe.test values (1);
   1 row created.

   SQL> commit;
   Commit complete.

2/ Prepare the 9.0.0 8K tablespace to be transported to another 9i database
   SQL> alter tablespace tts_8k read only;
   Tablespace altered.

3/ Export the 9.0.0 8K tablespace for transportation
   $ NLS_LANG=american_america.WE8ISO8859P1
   $ export NLS_LANG
   $ exp "system/manager@oracle1 as sysdba" transport_tablespace=y tablespaces=TTS_8K file=/tmp/djeunot/expdat.dmp

4/ Ftp the export dump file and datafiles to the target 9i database server

5/ Import the 9.0.0 8K tablespace into the target 9i database (db_block_size=4096)

   SQL> alter system set db_8k_cache_size =8m;
   System altered.

   $ NLS_LANG=american_america.WE8ISO8859P1
   $ export NLS_LANG
   $ imp 'sustem/manager@oracle2 as sysdba'  transport_tablespace=y tablespaces=TTS_8K datafiles='/oradata/V900/V900/tts_8k.dbf' file=/tmp/expdat.dmp

   SQL>  select tablespace_name, block_size from dba_tablespaces;

   TABLESPACE_NAME                BLOCK_SIZE
   ------------------------------ ----------
   SYSTEM                               4096
   UNDOTBS                              4096
   EXAMPLE                              4096
   INDX                                 4096
   TEMP                                 4096
   TOOLS                                4096
   USERS                                4096
   TEST_TTS                             2048
   TTS_8K                               8192

Note
The restrictions that existed in 8i regarding character sets when transporting tablespaces from one database to another still remain.
The target database must have the same character set as the source database.

Multi-Table Insert
Until Oracle 8i, you had to run throught the data multiple times or write procedural code to perform inserts into multiple tables in one pass. 9i's Multi Table Insert feature provides a feature to insert data into multiple tables in one pass. The multitable insert feature allows the INSERT . . . SELECT statement to use multiple tables as targets. In addition, it can distribute data among target tables based on logical attributes of the new rows. Multitable insert thus enables a single scan and transformation of source data to insert data into multiple tables, sharply increasing performance.
Prior to Oracle9i, such processing could be done in two different ways. It could be done through SQL with an independent INSERT . . . SELECT statement for each table. This approach meant processing the same source data and the transformation workload once for each target. If the SELECT statement itself used a major transformation (which it often does, e.g., GROUP BY), then the transformed data was either recomputed for each scan or materialized beforehand in a temporary table to improve performance. Alternatively, this type of work could be performed in procedural programs: every row would be examined to determine how to handle the insertion.
Both of these techniques had significant drawbacks. If the insert was performed through SQL, multiple scans and transforms of the source data would hurt performance. Creating a temporary table to hold precomputed transformation results could consume large amounts of disk space, and the temporary table would still be scanned multiple times. The procedural programming approach could not use the high-speed access paths directly available in SQL, so it also faced performance handicaps.
Example
The following example statement inserts new customer information from the customers_new table into two tables, customers and customers_special. If a customer has a credit limit greater than 4500, the row is inserted into customers_special. All customer rows are inserted into table customers.

The operation in Oracle8i could be implemented as follows:
INSERT INTO customers_special (cust_id,cust_credit_limit)
SELECT cust_id, cust_credit_limit
FROM customers_new
WHERE cust_credit_limit >=4500;
INSERT INTO customers
SELECT * FROM customers_new;

The same operation in Oracle9i could be implemented as follows:
INSERT FIRST
WHEN cust_credit_limit >=4500 THEN
   INTO customers_special VALUES(cust_id,cust_credit_limit)
   INTO customers
ELSE
   INTO customers
SELECT * FROM customers_new;

Command format for the multi-table insert.

INSERT {all|first}
  [WHEN {condition1}] THEN
     INTO {table_name} VALUES ....
  [WHEN {condition2}] THEN
     INTO {table_name} VALUES ....
  [ELSE  INTO {table_name} VALUES ....
…
{select_statement};

Before getting to some examples, let's take a closer look at the different parts of the multi-table insert.
insert {all|first}
Since this is an insert statement, you start the command with the INSERT keyword. Immediately after this keyword, you want to specify whether this is an "all insert" or a "first insert" with the appropriate keyword.
To specify an all insert, you use the keyword ALL. An all insert will check every line of data against all of the conditions. If there aren't any conditions, the data will be inserted into every table listed. Otherwise, it will only be inserted into the tables where the conditions are met.
To specify a first insert, you use the keyword FIRST. A first insert will check every line of data against all of the conditions as well. However, it will stop once one of the conditions is met. If you specify a first insert, you need at least one condition.

[when {condition}]
This line of the multi-table insert is the condition line. Since you can create an insert without any conditions, this line is optional. However, if you do include one or more conditions, you have to do so in the format shown. Each condition line starts with the WHEN keyword followed by the actual condition statement. This statement is a Boolean value that generally relates to the line of data being processed. For instance, if you were inserting customer data and put the condition line "when customer_id < 100", then only the customers that had an id less than 100 would be subject to the into statement(s) that follow.
You can have as many condition lines as you need. If you have a first insert, you can also use the ELSE keyword to create a special condition. This condition will only be met if all of the other conditions have failed. It's a catch-all condition.

VALUES
The third line is just the VALUES keyword. As you'll see in a moment, a multi-table insert must receive the data from a select statement. For a regular insert, when the data comes from a select statement, you don't include this keyword. However, for this version of the insert statement, it's required.

As with the conditions, you can include any number of INTO clauses. The only limitation is that you need at least one INTO clause for each condition or at least one for the entire multi-table insert if you don't have any conditions. The last statement is:

{select_statement};
This line of the multi-table insert determines what data will be inserted into the tables. As mentioned earlier, this data must come from a select statement, and you can make the statement as simple or as complex as you want to so long as it's valid.
When creating the statement, you should remember that you'll be using the column names in the INTO clause. In order to keep things simple, you should always name any column that's not a single field. For instance, if you combine two fields as part of the select statement, you should give that column a name. You can then refer to that field by that name in the INTO clause.

Unconditional insert
The most basic type of multi-table insert is the unconditional insert. The name of the insert gives the details. It's unconditional–in other words, there will be no condition lines. Instead, every row of data that's returned by the select statement is inserted into the various tables.
You'd use this type of insert when you need to quickly split all of the data up onto separate tables. In order to better illustrate this, let's look at an example. Using the customer_load table identified earlier, I want to create an unconditional insert that will insert all of the data into the customer and customer_address tables
The unconditional insert example.
insert all
  into customer
    ( customer_id, first_name, middle_initial,
      last_name, birth_date, gender, married )
  values
    ( customer_id, customer_first_name,
      customer_middle_initial, customer_last_name,
      customer_birth_date, customer_gender, married )
  into customer_address
    ( customer_id, address1, address2, city,
      state, zip )
  values
    ( customer_id, address1, address2, city,
      state, zip )
  select customer_id, customer_first_name,
    customer_middle_initial, customer_last_name,
    customer_birth_date, customer_gender, married,
    address1, address2, city, state, zip
  from customer_load;
This example has two INTO clauses. The first one inserts records into the customer table. The second one inserts records into the customer_address table. Since the primary key on the customer table is the foreign key on the customer_address table, they must be inserted in this order. Otherwise, you'll get a constraint error, unless you're working with deferred foreign keys.

Conditional all insert
The conditional all insert table is the next type of multi-table insert. Conditional simply means that there will be one or more condition lines. There could be more, but there will be at least one.
The word "all" in the name of the insert means that the keyword ALL was used, making this an all insert. This type of insert will compare each of the data rows from the select statement to every condition. When a condition is met, the INTO clause under that condition is executed.
The conditional all insert example.
insert all
  when married in ('Y','N') then
    into customer
      ( customer_id, first_name, middle_initial,
        last_name, birth_date, gender, married )
    values
      ( customer_id, customer_first_name,
        customer_middle_initial, customer_last_name,
        customer_birth_date, customer_gender, married )
    into customer_address
      ( customer_id, address1, address2, city,
        state, zip )
    values
      ( customer_id, address1, address2, city,
        state, zip )
  when married = 'Y' then
    into customer_spouse
      ( customer_id, first_name, middle_initial,
        last_name, birth_date, gender )
    values
      ( customer_id, spouse_first_name,
        spouse_middle_initial, spouse_last_name,
        spouse_birth_date, spouse_gender )
  select customer_id, customer_first_name,
    customer_middle_initial, customer_last_name,
    customer_birth_date, customer_gender, married,
    spouse_first_name, spouse_middle_initial,
    spouse_last_name, spouse_birth_date, spouse_gender,
    address1, address2, city, state, zip
  from customer_load; 

This example has two conditions. The first condition checks to see if the married field is either 'Y' or 'N'. This field is a flag that's always one or the other. In other words, this condition will always be true. You always want to insert into the customer and customer_address tables. The customer table has the important customer_id field that's used as a foreign key on all of the other tables.
The second condition checks to see if the married field is 'Y'. If this is the case, then the customer is married and you want to insert the spouse information into the customer_spouse table. You wouldn't want to always insert this row since, if the customer isn't married, all of the spouse information would be null. Not only is it a poor practice to insert null rows, but some of the fields on the customer_spouse table might have a not null constraint.

Conditional first insert
The conditional first insert table is similar in many ways to the previous type. It will also have one or more condition lines. The major difference is the FIRST keyword. When this keyword is used, the INTO clause of the first true condition will be executed and none of the others.
The conditional first insert example.
insert first
  when married = 'N' then
    into customer
      ( customer_id, first_name, middle_initial,
        last_name, birth_date, gender, married )
    values
      ( customer_id, customer_first_name,
        customer_middle_initial, customer_last_name,
        customer_birth_date, customer_gender, married )
    into customer_address
      ( customer_id, address1, address2, city,
        state, zip )
    values
      ( customer_id, address1, address2, city,
        state, zip )
  when married = 'Y' then
    into customer
      ( customer_id, first_name, middle_initial,
        last_name, birth_date, gender, married )
    values
      ( customer_id, customer_first_name,
        customer_middle_initial, customer_last_name,
        customer_birth_date, customer_gender, married )
    into customer_address
      ( customer_id, address1, address2, city,
        state, zip )
    values
      ( customer_id, address1, address2, city,
        state, zip )
    into customer_spouse
      ( customer_id, first_name, middle_initial,
        last_name, birth_date, gender )
    values
      ( customer_id, spouse_first_name,
        spouse_middle_initial, spouse_last_name,
        spouse_birth_date, spouse_gender )
  select customer_id, customer_first_name,
    customer_middle_initial, customer_last_name,
    customer_birth_date, customer_gender, married,
    spouse_first_name, spouse_middle_initial,
    spouse_last_name, spouse_birth_date, spouse_gender,
    address1, address2, city, state, zip
  from customer_load; 

As with the previous example, this one has two conditions. The first condition checks to see if the married field is set to 'N'. In other words, it checks to see if the customer isn't married. If the condition is true and the customer isn't married, you insert into the customer and customer_address tables. You don't insert into the customer_spouse table.
The second condition checks to see if the married field is Y' (the customer is married). You could replace this condition with an ELSE statement if you wanted to. For this situation, either would work. If this condition is met and the customer is married, you insert information into the customer, customer_address, and customer_spouse tables.

Pivoting the insert
In addition to the three main types of multi-table inserts, you can also use the pivoting sub-type. A pivoting insert isn't truly a separate type of insert. Instead, it's a variation of the other three types. This type of insert is used to insert multiple rows of data into the same table.
Remember that in the conditional first insert, I used the same INTO clause multiple times. However, since only one of the conditions could be true, the INTO clause was only executed once. Therefore, each table will have a single INSERT clause. For a pivoting insert, this isn't the case. Each table could have multiple INSERT clauses.
On the customer_load table, there's one column for each phone type–phone_number, cell_number, and fax_number. In this example, I want to pivot those three columns into three inserts of the customer_phone table.
The pivoting insert.
insert all
  when married in ('Y','N') then
    into customer
      ( customer_id, first_name, middle_initial,
        last_name, birth_date, gender, married )
    values
      ( customer_id, customer_first_name,
        customer_middle_initial, customer_last_name,
        customer_birth_date, customer_gender, married )
    into customer_address
      ( customer_id, address1, address2, city,
        state, zip )
    values
      ( customer_id, address1, address2, city,
        state, zip )
  when married = 'Y' then
    into customer_spouse
      ( customer_id, first_name, middle_initial,
        last_name, birth_date, gender )
    values
      ( customer_id, spouse_first_name,
        spouse_middle_initial, spouse_last_name,
        spouse_birth_date, spouse_gender )
  when phone_number is not null then
    into customer_phone
      ( customer_id, phone_type, phone_number )
    values
      ( customer_id, 'Phone', phone_number )
  when fax_number is not null then
    into customer_phone
      ( customer_id, phone_type, phone_number )
    values
      ( customer_id, 'Fax', fax_number)
  when cell_number is not null then
    into customer_phone
      ( customer_id, phone_type, phone_number )
    values
      ( customer_id, 'Cell', cell_number )
  select customer_id, customer_first_name,
    customer_middle_initial, customer_last_name,
    customer_birth_date, customer_gender, married,
    spouse_first_name, spouse_middle_initial,
    spouse_last_name, spouse_birth_date,
    spouse_gender, address1, address2, city, state,
    zip, phone_number, fax_number, cell_number
  from customer_load;
The last three conditions show the pivoting insert. In the first condition, the phone_number field is checked on the customer_load table to make sure it's not null (in other words, if it exists). If this field does exist, the INTO clause loads that data into the customer_phone table. If it doesn't exist, you don't want to insert an empty row. The phone_type field isn't populated from the customer_load table. Instead, you use a constant, 'Phone', as the type.
The second and third conditions do the exact same thing, except for the fax_number and cell_number fields. The phone_type fields for these two INTO clauses are set to 'Fax' and 'Cell', respectively. Each row of data in the customer_load table can create up to three rows of data in the customer_phone table.

Performance issues
Another reason to use a multi-table insert is the performance gains. In a six inserts statement (for example), each statement uses a similar select statement to provide the data for the insert. This means that each insert will have to complete a full access on the customer_load table. Since this is a loader table, it could have a huge amount of data on it. Cycling through this potentially large amount of data could be resource-intensive.
Fortunately, we can get around this problem. Regardless of the number of INTO clauses or conditions, a multi-table insert will only need to do one full access

The restrictions on multitable INSERTs are:

• Multitable inserts can only be performed on tables, not on views or materialized views.
• You cannot perform a multitable insert via a DB link.
• You cannot perform multitable inserts into nested tables.
• The sum of all the INTO columns cannot exceed 999.
• Sequences cannot be used in the subquery of the multitable insert statement.

 
MERGE Statement (Insert or Update data)
The MERGE statement can be used to conditionally insert or update data depending on its presence in one Statement. This method reduces table scans and can perform the operation in parallel.
Consider the following example where data from the HR_RECORDS table is merged into the EMPLOYEES table:

SELECT * FROM  DEPTBIG;
DEPTNO     DNAME         LOC
---------  ------------- ----------
10         ACCOUNTING    NEW YORK
20         RESEARCH      DALLAS
30         SALES         CHICAGO
40         RESEARCH      BOSTON

SELECT     * FROM     DEPT2;
DEPTNO  DNAME       LOC
------- ----------- ----------------
40      OPERATIONS  CHICAGO     (to update)
50      OPERATIONS  CHICAGO     (to insert)

MERGE INTO deptbig dest
   USING (select deptno, dname, loc from dept2) orig
   ON (dest.deptno = orig.deptno and .....)
   WHEN MATCHED THEN
       update set a.loc = b.loc
   WHEN NOT MATCHED THEN
     insert (dest.deptno, dest.dname, dest.loc)
        values  (orig.deptno, orig.dname, orig.loc);
2 rows merged.

SELECT    * FROM     DEPTBIG;
DEPTNO  DNAME       LOC
------- ----------- ----------------
10      ACCOUNTING  NEW YORK
20      RESEARCH    DALLAS
30      SALES       CHICAGO
40      RESEARCH    CHICAGO  (was updated)
50      OPERATIONS  CHICAGO  (new record)

Another Example

Trial Recovery
Some problems that may occur during media recovery are not "recoverable". For example, if a redo log was somehow corrupted and recovery could not pass changes in the redo stream, the recovered database becomes useless.  This is known as "stuck recovery".   When problems such as stuck recovery occur, you have a difficult choice. If the block is relatively unimportant, and if the problem is isolated, then it is better to corrupt the block. But if the problem is not isolated, then it may be better to open the database with the RESETLOGS option.  This means that one must restore the backup and recover the database again to an SCN before the point where the corruption occurred. For a large database, restoring a backup and recovering the database can take a long time.

To address this problem, the concept of Trial Recovery is introduced. Trial recovery applies redo in a way similar to normal media recovery, but it never writes its changes to disk, and it always rolls back its changes at the end of the test. Trial recovery is designed to allow a DBA to peek ahead in the redo stream to see if there are additional problems.

Trial Recovery provides database administrators the following enhancements so that almost all practical problems during media recovery are recoverable.

• If media recovery of a full database encounters a problem, recovery always leaves behind a consistent database that can be opened read-only or with resetlogs.
• Instruct media recovery to mark a data block as being corrupted and therefore ignore its inconsistency in order to proceed with recovery. The block will be inaccessible but the rest of the recovery process will continue.
  
• Invoke Trial Recovery in order to investigate if a recovery problem is an isolated problem.

The database provides a lot of information about the problem in the alert logs. The alert log indicates if recovery is capable of recovering past the problem by marking as corrupted the data block causing the problem. The alert log also provides information about the block: its file name, file number, block number, block type, data object number, and so on.  You can then investigate the impact of corrupting the problematic block according to the information provided in the alert logs. The database can be opened read-only, provided that all of the database files have been recovered to the same point in time, and query the database to see to what table this data block belongs.  Trial Recovery allows you to assess the entire extent of damage.

How Trial Recovery Works
By default, if a trial recovery encounters a stuck recovery or similar problem, then it always marks the data block as corrupt in memory when this action can allow recovery to proceed. Oracle writes errors generated during trial recovery to alert files. Oracle clearly marks these errors as test run errors.
Like normal media recovery, trial recovery can prompt you for archived log filenames and ask you to apply them. Trial recovery ends when:

1. Oracle runs out of the maximum number of buffers in memory that trial recovery is permitted to use
2. An unrecoverable error is signaled, that is, an error that cannot be resolved by corrupting a data block
   
3. You cancel or interrupt the recovery session
   
4. The next redo record in the redo stream changes the control file
   
5. All requested redo has been applied

When trial recovery ends, Oracles removes all effects of the test run from the system--except the possible error messages in the alert files. If the instance fails during trial recovery, then Oracle removes all effects of trial recovery from the system because trial recovery never writes changes to disk.
 
 

RMAN NEW
Recovery Manager automates backup and recovery by querying information in the recovery catalog, the database's control file, and any datafiles affected by the operations requested. Recovery Manager decides the most efficient method of executing the requested backup, restore, or recovery operation and then issues these steps to the Oracle server. Recovery Manager and the server automatically identify modifications to the structure of the database, and dynamically adjust the current operation to cater to the changes.

KEY FEATURES OF RECOVERY MANAGER
- Automation of backup, restore and recovery operations
- Block media recovery
- Whole database backups or backups of any logical unit: control file, datafile, tablespace or archive log
- Offline and Online backups
- Integrated with 3rd Party Media Management Vendors to backup to tape
- Two types of backup: image copies to disk or Recovery Manager backup sets
- Intelligent management of the archived redo logs for both backup and recovery
- Proxy Copy Backup Accelerator for fast copy technology at the storage subsystem level
- Corrupt block detection
- Tablespace Point-in-Time Recovery support
- Ability to catalog on disk operating system backups
- Integration with Oracle Enterprise Manager's Backup Manager GUI
- Incremental backups at the Oracle block level for high performance backup and recovery
- Omission of empty blocks during backup for optimization
- Recoverability redundancy when both incrementals and archiving are used
- No extra redo is generated during online database backup
- Intelligent buffering with multiplexing of multiple input files for high speed tape streaming
- Support for Oracle Real Application Clusters backup and recovery
- Ability to recover through unrecoverable operations, when using incremental backups
- O/S-independent scripting language
 

Persistent RMAN Configurations
A CONFIGURE command has been introduced in Oracle 9i, that lets you configure various features including automatic channels, parellelism, backup options etc., that can persist across sessions and be available to any session. For example, the CONFIGURE command may be used to store channel configurations and RMAN will automatically assignt the channels as needed without having to specify the channel allocation as a part of the script. These automatic allocations and options can be overridden by commands in an RMAN command file.

Control File Auto Backups
Many of the database administrators stayed away from using RMAN citing the reliance of RMAN on control files / recovery catalog for backup and recovery options. Now you need not worry about a lost recovery catalog or a lost control file. Control File Auto Backups give the dba a way to restore the backup repository contained in the control file when a control file and the recovery catalog are both lost. For some of us using the nocatalog option this comes in very handy when we have to recover without having the control file that was used for the RMAN backup.

To use this feature, CONFIGURE CONTROLFILE AUTOBACKUP should be set to ON, and RMAN will automatically perform a control file autobackup after every BACKUP or COPY command is issued whether its in a RUN Block or at the command prompt. This control file backup occurs in addition to any control file backup that has been created as a part of the database backup.

Block Media Recovery
RMAN introduces a new blockrecover command to recovery individual datablocks within a data file. This reduces the Mean Time to Recovery ( MTTR ) for a large datafile when individual blocks within the file are reported as corrupt. Without this feature, even if a single block is corrupted the dba has to restore the whole datafile and recovery using the archived redo logs that were generated since that backup was created.

This procedure is useful when a trace file or standard output reveals that a small number of blocks within a datafile are corrupt. A major restriction is that you can only recover from a full RMAN backup and incremental backups cannot be used for this type of recovery. Also only complete recovery can be performed on individual blocks cand you cannot stop the redo application while the recovery is in progress.

Block Media recovery can be performed by using a command at the RMAN command prompt similar to the one given below.

BLOCKRECOVER DATAFILE 6 BLOCK 23 DATAFILE 12 BLOCK 9;

Other Enhancements
To make the job of the DBA more error free, Oracle enhance the CHANGE, CROSSCHECK, DELETE and LIST commands. Also RMAN automatically switches out the online redo log before backing up the archived redo logs to guarantee that the backups can be restored to a consistent state. One cool feature I like is the "NOT BACKED UP SINCE " clause that allows RMAN to backup only those files that were not backed up after a specified time. So, if a backup fails partway through, you can restart it and back up only those files that were not previously backed up. To help you preserve your current server side parameter file (SPFILE), RMAN can backup your SPFILE automatically. RMAN will do this if the instance was started with the SPFILE and the RMAN operation includes an automatic backup of the control file.
For backups made with the autobackup feature enabled, RMAN generates a separate backup piece to hold the SPFILE and control file backup. You can manually backup the SPFILE used to start your instance with the BACKUP command. If CONTROLFILE AUTOBACKUP is ON then RMAN will automatically back up the control file and server parameter file after structural changes to the database. The target database records the autobackup in the alert log. Unlike the RMAN BACKUP DATABASE command, the RESTORE DATABASE command does not restore the SPFILE (nor the control file) automatically.
During an RMAN recovery operation, archived log files may need to be retrieved from a backup set and stored on disk. You can direct RMAN to delete the archived logs after reading them, however space problems can occur because the process to restore the archive logs to disk is faster than the operation to apply the archived logs to the database. In previous releases, if the target disk location ran out of space, the whole recovery operation would fail.
To avoid this problem in Oracle9i Database Release 2, specify the maximum disk space available for the archive backup files when you use RECOVER…DELETE command in RMAN. This causes RMAN to delete used files when the next part of the backup set would exceed the specified storage. Include the MAXSIZE parameter in the RECOVER…DELETE command to indicate how much space you have available in bytes, with an optional K, M, or G. The example shows a recovery operation for which 100KB of disk space is made available for the archived logs.
If MAXSIZE is too small to hold a full archive log, RMAN will terminate the recovery operation with an error. If MAXSIZE is smaller than a backupset containing the required archived logs, the backup set will be read more than once, increasing the overall recovery time. In this case, RMAN will issue a warning advising you to increase the value of MAXSIZE.

 

Self Managing Rollbacks and Files
Oracle9i databases are capable of managing their own undo (Rollback) segments -no longer will administrators need to carefully plan and tune the number and sizes of rollback segments or bother about how to strategically assign transactions to a particular rollback segment. Oracle9i also allows administrators to allocate their undo space in a single undo tablespace with the database taking care of issues such as undo block contention, consistent read retention and space utilization.

Oracle9i also introduces the concept of "Oracle Managed Files" which simplifies database administration by eliminating the need for administrators to directly manage the files comprising an Oracle database. Oracle9i now internally uses standard file system interfaces to create and delete files as needed. While administrators still need to be involved in space planning and administration, this feature automates the routine task of creation and deletion of database files.
Oracle uses the standard operating system (OS) file system interfaces internally to create and delete files as needed for tablespaces, online logs and controlfiles. DBAs only need to specify the location of these files using new initialization parameters. Oracle then ensures creation of a file with a unique name and delete it when the corresponding object is dropped. OMF reduces errors caused by administrators specifying incorrect file names, reduces disk space wasted in obsolete files, and simplifies creation of test and development databases. It also makes development of portable third party applications easier since it eliminates the need to put OS specific file names in SQL scripts. 

Locally managed tablespaces offer two options for extent management, Auto Allocate and Uniform (ASSM).  In the Auto Allocate option, Oracle determines the size of each extent allocated without DBA intervention, whereas in the Uniform option, the DBA specifies a size and all extents allocated in that tablespaces are of that size.  We recommend using the Auto Allocate option because even though it might result in objects having multiple extents, the user need not worry about it since locally managed tablespaces can handle a large number of extents (over 1000 per object) without any noticeable performance impact. With the SEGMENT SPACE MANAGEMENT AUTO option the DBA no longer needs to tune the PCTUSED, FREELIST and FREELIST GROUPS segment attributes. The following example illustrates how to create a tablespace with Automatic Segment Space Management.

CREATE TABLESPACE data DATAFILE '/u02/oracle/data/data01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;

Syntax
The LOCAL option of the EXTENT MANAGEMENT clause specifies that a tablespaces is to be locally managed.
Extent_management_clause:
    [EXTENT MANAGEMENT
         {DICTIONARY | LOCAL
                {AUTOALLOCATE | UNIFORM [SIZE integer [K|M] }}]
where:
DICTIONARY specifies that the tablespace is managed using dictionary tables (this is the default)
LOCAL specifies that tablespace is locally managed with a bitmap
AUTOALLOCATE specifies that the tablespace is system managed (users cannot specify an extent size)
UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes (use K or M to specify the extent size in kilobytes or megabytes. The default size is one megabyte. If you specify, LOCAL, you cannot specify DEFAULT storage_clause, MINIMUM EXTENT or TEMPORARY.

If you use ASSM, you reduce the chances to have buffer busy waits (several users trying to access a specific block to perform an insert). On the other hand,  tests indicates that full table scans are going to take from 11 to 23% longer. But anyway, we HIGHLY recommend to use it.
All objects created in the above tablespace will use the automatic segment space management feature to manage their free space.  This means that Oracle uses bitmaps to manage space within segments instead of using freelists, the traditional method. A new column called SEGMENT_SPACE_MANAGEMENT has been added to the DBA_TABLESPACES view to indicate the segment space management mode used by a tablespace (it will show AUTO). In Summary:
- Free space within a segment is maintained using bitmaps
- Eliminates the necessity to tune parameters such as FREELISTS, FREELIST GROUPS and PCTUSED
- Improves space utilization especially for objects with rows of highly varying sizes
- Improves concurrency of INSERT operations
- Better multi-instance behavior in terms of performance/space utilization

Once a table or index is allocated in this tablespace, the values for PCTUSED for individual objects will be ignored, and Oracle9i will automatically manage the FREELISTS for the tables and indexes inside the tablespace. For objects created in this tablespace, the NEXT extent clause is now obsolete because of the locally-managed tablespace (except when a table is created with MINEXTENTS and NEXT). The INITIAL parameter is still required because Oracle cannot know in advance the size of the initial table load. When using Automatic Space Management, the minimum value for INITIAL is three blocks

While the parameter DB_CREATE_FILE_DEST specifies the default location of datafiles, the parameter DB_CREATE_ONLINE_LOG_DEST_<n>, where n is any integer between 1 and 5, decides the default location for copies of online logs and controlfiles. If neither of the last two parameters are set, all the files (datafiles, controlfiles and online logs) will be created at the destination specified by the DB_CREATE_FILE_DEST parameter. Oracle Managed datafiles, created by default, will be 100 MB in size and will be auto extensible with unlimited maximum size. The default size of Oracle Managed online logs will also be 100MB.

* Oracle Managed Files

• Eliminates the need to directly manage the Oracle Database files
• DBAs specify operation in terms of database objects; Oracle automatically creates and deletes OS files as needed
  
• Failed DDL operations automatically delete partially created underlying files
  
• Administrators can specify different locations for datafiles and online redo log/control files
  
• Redo logs and control files can be multiplexed across five destinations
  
• Third Party Applications need not embed OS specific file names in their scripts

* Default Temporary Tablespace

• DBAs will now be able to assign a default temporary tablespace to a database.
  
• Prevents the use of SYSTEM tablespace for storing temporary data

In Oracle9i, the database can be assigned a DEFAULT TEMPORARY TABLESPACE. This feature is designed for database users to have a default temporary tablespace automatically assigned other than the SYSTEM tablespace.

Restrictions on the DEFAULT TEMPORARY TABLESPACE:

1. The DEFAULT TEMPORARY TABLESPACE must be of TEMPORARY

2. The DEFAULT TEMPORARY TABLESPACE cannot be converted to PERMANENT once it has been defined as the DEFAULT TEMPORARY TABLESPACE

3. Before dropping the DEFAULT TEMPORARY TABLESPACE, create another one

4. The DEFAULT TEMPORARY TABLESPACE cannot be taken OFFLINE

To have a TEMP tablespace as a default tablespace for the database use:
  alter database default temporary tablespace TEMP;

* Delete Datafiles

• Datafiles can automatically be deleted when dropping a tablespace with the new INCLUDING CONTENTS AND DATAFILES clause. Example:
           DROP TABLESPACE xxxxx INCLUDING CONTENTS AND DATAFILES;
  
• Eliminates proliferation of obsolete files

Specific Information on Oracle Managed Files and its setup is HERE 

 
List Partitioning
Oracle started providing the facility to divide a large table into partitions right from Oracle8. But the paritioning is primarly based on the range. That means, Oracle determines into which partition a row is placed based on the range to which the value in the column belongs.
The following example shows how to create a partition based on the rate of the product.

create table  sales_history
( pid  number(5),
  qty  number(3),
  rate number(5),
  dp   date,
  state char(2)
)
partition  by range(rate)
(  partition  low      values less than (1000),
   partition  normal   values less than(5000),
   partition  high     values less than (maxvalue)
);

The above command creates SALES_HISTORY table and then places a row into either of the three partitions based on the value of RATE column.
Now, starting from Oracle9i database, Oracle supports list partition in addition to range partition. In list partitioning, a table is divided into partitions based on a list of values. For example the following command creates SALES_HISTORY table with four partitions based on the values of STATE column.

create table  sales_history
( pid   number(5),
  qty   number(3),
  rate  number(5),
  dp    date,
  state char(2)
)
partition by list (state)
(  partition  south   values('AP','TN','KE'),
   partition  north   values('DE','JK','PU'),
   partition  west    values('MA','PU'),
   partition  east    values('WB','SI')
);

If a rows contains the value AP or TN or KE in STATE column then the row is placed in SOUTH partition. Similarly if the value of STATE is DE or JK or PU then the row is placed in NORTH partition and so on.
Though the overall concept of partition and its benefits are the same between range and partitions, the possible of dividing table into partitions based on discrete values of the column is new in Oracle9i database.

External Tables
The External Table feature allows for flat files, which reside outside the database, to be accessed just like relational tables within the database: the flat-file data can be queried and joined to other tables using standard SQL. Data access can be serial or parallel for maximum scalability. From a user's point of view, the main difference between an external table and a regular table is that the external table is read-only. To understand the value of external tables, consider that a data warehouse always contains data from external data sources. Prior to Oracle9i, performing complex transformations within Oracle required that the external data be transformed using one of two possible strategies:

• Load the data into Oracle and store it in a staging table. When the transformations were finished the table could be deleted. This approach devoted disk space, administrative resources and processing time to data that was already available on disk.
  

• Transform the external data in flat files outside the database. Since some transformations require data from the data warehouse, this process requires not only flat-file manipulation but also database access. When the transformations were completed, the transformed data is loaded into the data warehouse. This approach also requires redundant data storage and processing time.

Oracle9i's external tables avoid the problems inherent in the approaches described above: external tables provide a whole new model for loading and transforming external data. The data is no longer transformed outside the database, nor must it be stored in a staging table. Instead, the external data is presented to the database as a virtual table, enabling full data processing inside the database engine. This seamless integration of external data access with the transformation phase is generally referred to as "pipelining." With pipelining, there is no interruption of the data stream, so every ETL process can show increased performance.
The structure of the external table is not enforced until the time a query is issued against it. Only the fields accessed by a SQL query on the External table are examined. This means that only the data elements accessed in the SQL query are actually loaded by the SQL*Loader process. In other words, if an invalid data element is not accessed it will not be caught by the SQL*Loader process. However, if a different query exposes this field, an error will occur on those records with invalid data. This could result in a loss of records depending on the “REJECT LIMIT” parameter.

Oracle uses the CREATE TABLE..ORGANIZATION EXTERNAL syntax to store metadata about the external table:
-- Create directory object to data location
CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\Oracle\External_Tables';

-- Create the external table
-- Files must exist in the specified location
CREATE TABLE employees_ext
  (empno NUMBER(8), first_name VARCHAR2(30), last_name VARCHAR2(30))
  ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_tables
      ACCESS PARAMETERS
      (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
      )
      LOCATION ('employees1.txt','employees2.txt')
    )
  PARALLEL 5
  REJECT LIMIT 200;

-- Query the table
SELECT * FROM employees_ext;

Another Example (DO NOT use the --comments inside the create table statement):
CREATE OR REPLACE DIRECTORY DIR1 AS 'C:\Temp';
GRANT READ ON DIRECTORY DIR1 to fg82_blink_dev;
GRANT WRITE ON DIRECTORY DIR1 to fg82_blink_dev;

-- Create the external table
-- Files must exist in the specified location
CREATE TABLE ext_table
  (documentid VARCHAR2(14),
   decision VARCHAR2(6),
   Comments VARCHAR2(30)
  )
  ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY DIR1
      ACCESS PARAMETERS
      (
--      RECORDS FIXED 120
      RECORDS DELIMITED BY NEWLINE
      LOAD WHEN ("DOCUMENTID" = "1" OR "DOCUMENTID" = "0")
      badfile DIR1:'Upload_Except.bad'
--    NOBADFILE
      DISCARDFILE DIR1:'Upload_Except.dis'
--    NODISCARDFILE
      logfile DIR1:'Upload_Except.log'
--    NOLOGFILE
      FIELDS
--      FIELDS TERMINATED BY ','
      MISSING FIELD VALUES ARE NULL
--    REJECT ROWS WITH ALL NULLS FIELDS
       (
         documentid position(67:80) char,
         decision position(84:89) char(6) NULLIF decision=BLANKS,
         comments position(90:119) char
       )
      )
      LOCATION ('Upload_Except.txt')
    )
 REJECT LIMIT UNLIMITED NOPARALLEL;


-- Query the table
SELECT * FROM ext_table;

------------------
--Query Locations
------------------
SQL> select * from dba_directories ;

OWNER DIRECTORY_NAME DIRECTORY_PATH
----- -------------- ------------------------
SYS   ADMIN_DAT_DIR  e:\external_tables\data
SYS   ADMIN_DAT_DIR  e:\external_tables\bad
SYS   ADMIN_DAT_DIR  e:\external_tables\log


SQL> select * from dba_external_locations where table_name='ADMIN_EXT_EMPLOYEES';

OWNER     TABLE_NAME          LOCATION      DIRECTORY_OWNER DIRECTORY_NAME
--------- ------------------  ------------  --------------- --------------
SCOTT     ADMIN_EXT_EMPLOYEES empxt1.dat    SYS             ADMIN_DAT_DIR
SCOTT     ADMIN_EXT_EMPLOYEES empxt2.dat    SYS             ADMIN_DAT_DIR

Another example using it to write to it:
External tables can also be used to export data out of Oracle. The following example uses the EMP and DEPT tables in the SCOTT schema to show how all employees working in the MARKETING and ENGINEERING departments can be exported in parallel using the default degree of parallelism.
#
# Export employees working in the departments 'MARKETING' and 'ENGINEERING'
#
CREATE TABLE emp_ext
     (empno NUMBER, ename VARCHAR2(100), ...)
     ORGANIZATION EXTERNAL
     (
          TYPE ORACLE_INTERNAL
          DEFAULT DIRECTORY private2
          LOCATION (export_dir:'/emp_data/emp1.exp',
                    export_dir:'/emp_data/emp2.exp')
     ...)
     PARALLEL
     AS SELECT e.*, d.dname
        FROM emp e, dept d
        WHERE e.deptno = d.deptno
           AND  d.dname IN ('MARKETING', 'ENGINEERING');

At this point, you will have an external table that contains all of the records corresponding to the MARKETING and ENGINEERING departments and that can be used by other programs if desired. Once changes are made to the data, it can be reimported to the external table specification and then moved easily from there back into its parent tables. Notice that the TYPE becomes ORACLE_INTERNAL, rather than ORACLE_LOADER ,and that no conversion specification is required.
#
# Re-import the employees that are working in the 'ENGINEERING' department
#
CREATE TABLE emp_import
     PARALLEL
     AS SELECT *
        FROM emp_ext
        WHERE dname = 'ENGINEERING';
This example illustrates how the external table feature can help to export (and import) a selective set of records. 

Some Notes

• Performance is impacted by the amount of parsing it must perform. Fixed width files do not require the level of parsing that delimited files do. Fixed width files will load FASTER.
• The external table can be queried in parallel. Just like a normal table, the external table parallel degree can be set in the DDL or in a hint added to the query. I recommend to perform the paralled degree at the DDL level. If you use hints perform the following:
  select /* PARALLEL(EXTERNAL_TABLE_NAME,3) */ xxx from external_table_name;
• If we remove the "LOAD WHEN" criteria, it will try to load the header record.
  

Syntax
 RECORD {FIXED integer | VARIABLE integer | DELIMITED BY {NEWLINE | string}}
  {
    CHARACTERSET string
    |DATA IS {LITTLE | BIG} ENDIAN
    |STRING SIZES ARE IN {BYTES | CHARACTERS}
    |LOAD WHEN condition
    |{NOBADFILE |  BADFILE [directory name : ]filename}
    |{NODISCARDFILE | DISCARDFILE [directory name : ] filename}
    |{NOLOGFILE | LOGFILE [directory name : ] filename}
    |DATE_CACHE 0
    |SKIP integer
  }

More information on
http://www.csis.gvsu.edu/GeneralInfo/Oracle/server.920/a96652/part3.htm
http://www.quest-pipelines.com/newsletter-v4/0303_C.htm

If you get the error:
Date conversion cache disabled due to overflow (default size: 1000)

You can use the option:
      DATE_CACHE 0

Performance Manager
Oracle Enterprise Manager Performance Pack provides an enhanced real-time diagnostics interface with a single graphical Performance Overview for "at-a-glance" Oracle performance assessment. The Overview chart includes both host and database performance metrics organized by CPU, memory and I/O resource usage.  Top resources using sessions and database contention are also displayed.  Chart threshold flags display the status of  database and system performance allowing DBAs to immediately identify performance exceptions.  Trend arrows provide a quick check on whether performance is improving or degrading.

The Diagnostics Pack also provides a set of pre-defined event tests, known as Advanced Events, used to automatically monitor conditions and detect problems for key elements of the Oracle environment.  The Advanced Events can be configured to check one or more conditions for a target system.  Thresholds,  number of occurrences, as well as event notification plans and corrective actions can be customized.  In addition, with version 9i, administrators can investigate an event directly from the Console's event viewer through direct access to the Performance Manager charts, diagnostic help and advice, and historical data related to metrics being monitored by the event.
 

First-N rows Optimization
Oracle9i has added a new optimization mode that uses the cost based optimizer to optimize selection of the first 'n' rows where 'n' can equal 1, 10, 100, or 1000.
Fast response optimization is suitable for Online Transaction Processing users. Typically, OLTP users are interested in seeing the first few rows, and they seldom are interested in seeing the entire query result, especially when the result size is large. For such users, it makes sense to optimize the query to produce the first few rows as fast as possible, even if the time to produce the entire query result is not minimized.
More information Note:154354.1 Fast response optimization (FIRST_ROWS variants)
This parameter can be set for the whole instance in the init.ora file, at the session level using the ALTER SESSION command or for individual statements as an optimizer hint:
OPTIMIZER_MODE = FIRST_ROWS_100

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;

SELECT /*+ FIRST_ROWS(1000) */ empno, ename, sal, job FROM emp;

 

SQL Aggregation Enhancements
In Oracle8i, aggregation processing was enhanced with the addition of the CUBE and ROLLUP operators. These operators, extensions to SQL's "GROUP BY" clause, enable a single SQL query to calculate certain important sets of aggregation groups efficiently. Oracle9i extends SQL aggregation capabilities further with grouping sets, concatenated grouping sets and composite columns. These features enable SQL queries to specify the exact levels of aggregation needed, simplifying development and significantly enhancing query performance.

Grouping Sets
Grouping sets enable a single query to list multiple sets of columns which will be grouped when calculating aggregates such as sums. Grouping sets are specified by following the GROUP BY keyword with the term "GROUPING SETS" and a column specification list. Here is an example:
     SELECT year, region, product, sum(sales)
     FROM salesTable
     GROUP BY
     GROUPING SETS ((year, region, product),
         (year, product), (region, product));

The SQL above calculates aggregates over exactly 3 groupings: (year, region, product), (year, product), and (region, product). Without grouping sets, the query would need to be specified with either a CUBE operator or a UNION ALL query. Both of these alternative approaches have significant shortcomings:

• The CUBE operator technique wastes processing power by generating five unnecessary groupings. In addition, development is more complex than when using grouping sets, since the five unneeded groupings need to be filtered out of the results.
  

• The UNION ALL technique issues three queries, leading to three data scans where one would be sufficient.
  
• In contrast to the alternative techniques, grouping sets lets the query optimize performance by directly specifying the needed groups.

Concatenated Grouping Sets
The power of grouping sets is enhanced with concatenated grouping sets. Concatenated grouping sets offer a concise way to generate large combinations of groupings. Groupings specified with concatenated grouping sets yield the cross-product of groupings from each grouping set. Note that the concatenation can be performed on groupings specified with CUBE and ROLLUP. The cross-product operation enables even a small number of concatenated groupings to generate a large number of final groups.
Here is an example of concatenated grouping sets:
     GROUP BY GROUPING SETS (month, year),
                          GROUPING SETS (region, country)

The SQL above defines the following groupings:
     (month, region), (month, country), (year, region) and (year, country)

An important use for concatenated grouping sets is to generate the aggregates needed for OLAP data cubes. OLAP data cubes require aggregations along the rollup hierarchies of multiple dimensions, and this can be specified concisely by concatenating ROLLUP operators. The Oracle9i OLAP option takes advantage of this new feature for high performance processing.

Composite Columns in GROUP-BY Expressions
Oracle9i also enables the use of composite columns within GROUP BY expressions. A composite column is a collection of columns, specified within parentheses, that is treated as a single unit during the computation of groupings. For example, by enclosing a subset of the columns in a ROLLUP list within parentheses, some levels will be skipped during ROLLUP. Here is a simple case where (quarter, month) is a composite column:
     GROUP BY ROLLUP (year, (quarter, month), day)

Because of its composite column of (quarter, month), the SQL above never separates the quarter and month columns in its ROLLUP. This means that it never shows a rollup at the (year, quarter) level and thus skips the quarter-level aggregates. Instead the query returns the following groupings:
     (year, quarter, month, day), (year, quarter, month), (year), ()

As with grouping sets, precise specification of the groupings needed means simpler development and faster performance.
Oracle9i's new SQL aggregation features of grouping sets, concatenated grouping sets and composite columns provide high efficiency aggregations for the full range of business intelligence tasks. Oracle products such as the Oracle9i OLAP option are already taking advantage of the features to optimize performance, and users across the spectrum of business intelligence processing will benefits from these features. The SQL aggregation enhancements are just one aspect of Oracle9i business intelligence functionality enabling more productive development and faster query performance.
 

Improving Query Performance with the SQL WITH Clause
The SELECT statements WITH clause, introduced in Oracle9i, provides powerful new syntax for enhancing query performance. It optimizes query speed by eliminating redundant processing in complex queries. Consider a lengthy query which has multiple references to a single subquery block. Processing subquery blocks can be costly, so recomputing a block every time it is referenced in the SELECT statement is highly inefficient. The WITH clause enables a SELECT statement to define the subquery block at the start of the query, process the block just once, label the results, and then refer to the results multiple times.
The WITH clause, formally known as the subquery factoring clause, is part of the SQL-99 standard. The clause precedes the SELECT statement of a query and starts with the keyword "WITH". The WITH is followed by the subquery definition and a label for the result set. The query below shows a basic example of the clause:
WITH channel_summary AS
  ( SELECT channels.channel_desc,
       SUM(amount_sold) AS channel_total
    FROM sales, channels
    WHERE sales.channel_id = channels.channel_id
    GROUP BY channels.channel_desc )
SELECT channel_desc, channel_total
FROM channel_summary
WHERE channel_total >
  ( SELECT SUM(channel_total) * 1/3
    FROM channel_summary );

This query uses the WITH clause to calculate the sum of sales for each sales channel and label the results as channel_summary. Then it checks each channel's sales total to see if any channel's sales are greater than one third of the total sales. By using the new clause, the channel_summary data is calculated just once, avoiding an extra scan through the large sales table.
Although the primary purpose of the WITH clause is performance improvement, it also makes queries easier to read, write and maintain. Rather than duplicating a large block repeatedly through a SELECT statement, the block is localized at the very start of the query. Note that the clause can define multiple subquery blocks at the start of a SELECT statement: when several blocks are defined at the start, the query text is greatly simplified and its speed vastly improved.
 

Another Example:
Here’s a lengthy query that finds all departments whose total salary bill is more than 45% of the entire company’s salary bill:
Select dname, sum(sal) as dept_total
   From emp, dept
   Where emp.deptno=dept.deptno
   Group by dept.dname
   Having sum(sal) >
       (select sum(sal) *0.45
          from emp, dept
         where emp.deptno=dept.deptno)
   order by sum(sal);

Notice in particular that the “sum(sal)” calculation appears several times in this query: which means that the computation of the salary summaries has to be performed multiple times, too.  Well, new in 9i, you can write the same query like this:
With
deptsum as (
    select dname, sum(sal) as dept_total
       from emp, dept
       where emp.deptno=dept.deptno
       group by dept.dname),
  grandtotals as (
    select sum(sal) as grand_total from emp)
select dname, dept_total from deptsum
where dept_total>
    (select (grand_total*.45) from grandtotals)
order by dept_total;

The neat thing here is that we put the summaries up front, giving them easy names to work with (in this case “deptsum” and “grandtotals”).  Each salary summary (one in total and one by department) is then pre-computed as those named SQL blocks are processed –and no matter how many times your main query then references those summaries, they don’t need to be re-computed.

Doing things this way also means rather tidier syntax –your main query just references the potentially complex sub-queries and computations via an alias name as specified in the ‘with’ section.  In effect, the named entities at the top of the ‘with’ section can be referenced as though they were just another table later on in the query.

Index Merge
The index merge allows you to merge two separate indexes and go directly to a table instead. Consider as an example a table with 1,000,000 records. The table is 210 MB.
create index year_idx on test2 ( year );
create index state_idx on test2 ( state );

select /*+ rule index(test2) */ state, year
from test2
where year = ‘1972’
and state = MA

SELECT STATEMENT Optimizer=HINT: RULE
TABLE ACCESS (BY INDEX ROWID) OF ‘TEST2’
INDEX (RANGE SCAN) OF ‘STATE_IDX’ (NON-UNIQUE)

Elapsed time: 23.50 seconds

select /*+ index_join(test2 year_idx state_idx) */
state, year
from test2
where year = ‘1972’
and state = MA

SELECT STATEMENT Optimizer=CHOOSE
VIEW OF ‘index$_join$_001’
HASH JOIN
INDEX (RANGE SCAN) OF ‘YEAR_IDX’ (NON-UNIQUE)
INDEX (RANGE SCAN) OF ‘STATE_IDX’ (NON-UNIQUE)

Elapsed time: 4.76 seconds

Index Skip Scans
"Index skip scans" are a new performance feature in Oracle9i which can provide significant benefits to any database application which uses composite indexes. In releases prior to Oracle9i, a composite index would only be used for a given query if either the leading column (or columns) of the index was included in the WHERE-clause of the query, or (less often) the entire index was scanned.
With Oracle9i, a composite index can be used even if the leading column(s) are not accessed by the query, via a technique called an 'index skip scan'. During a skip scan, the composite index is accessed once for each distinct value of the leading column(s). For each distinct value, the index is searched to find the query's target values. The result is a scan that skips across the index structure.
Index skip scans provide two main benefits. First, index skip scans can improve the performance of certain queries, since queries which previously required table scans may now be able to take advantage of an existing index. Second, index skip scans may allow an application to meet its performance goals with fewer indexes; fewer indexes will require less storage space and may improve the performance of DML and maintenance operations. Index Skip Scan is good if the table is analyzed and is better if the first column has low cadinality.
To illustrate index skip scans, suppose that we have a 2.5-million-row table containing information on automobile registrations:

REGISTERED_OWNERS(
STATE VARCHAR2(2) NOT NULL,
REGISTRATION# VARCHAR2(10) NOT NULL,
FIRST_NAME VARCHAR2(30),
LAST_NAME VARCHAR2(30),
MAKE VARCHAR2(30),
MODEL VARCHAR2(15),
YEAR_MFG NUMBER,
CITY VARCHAR2(30),
ZIP NUMBER)

Furthermore, suppose that this table has a composite index on (STATE, REGISTRATION#). The following query will be able to take advantage of the index using index skip scans:
SELECT first_name, last_name, zip
FROM REGISTERED_OWNERS WHERE registration# = '4FBB428'

Index skip scans can be extremely useful, and they are transparent to users and applications. This feature can improve performance of many database workloads without requiring the addition of any new indexes (and in some cases may even allow indexes to be removed without significantly impacting performance).
 
Monitoring_Index_Usage
By using the 'alter index … monitoring usage' statement you can see if a particular index is being used.
Query v$object_usage as the owner of those indexes to see if the index has been used and during what time period it was monitored, it will show you just whether the index has been used or not; it will not show you how many times the index was used or when it was last used.
To stop monitoring an index, type:
ALTER INDEX index_name NOMONITORING USAGE;

This script activates index monitoring on an entire schema (must be run as System)
set echo off
set feedback off
set pages 0
set verify off
accept idx_owner prompt "Enter the schema name: "
spool monitor_idx.tmp
select 'alter index '||owner||'.'||index_name||' monitoring usage;'
   from dba_indexes
   where owner = UPPER('&idx_owner');
spool off
set echo on
set feedback on
set pages 60
spool monitor_idx.log
@monitor_idx.tmp
spool off

A sample query on V$OBJECT_USAGE confirms that monitoring has been activated (must be run as the OWNER of indexes)
set linesize 92
set pagesize 9999
select substr(index_name,1,25) index_name,
       substr(table_name,1,15) table_name,
       MONITORING, USED, START_MONITORING, END_MONITORING
from v$object_usage
order by used;
INDEX_NAME      TABLE_NAME            MON USED   START_MONITORING    END_MONITORING
-------------   --------------------  --- ----   ------------------- --------------
ITEM_ORDER_IX   ORDER_ITEMS           YES   NO   08/15/2001 11:23:10
INVENTORY_PK    INVENTORIES           YES  YES   08/15/2001 16:51:32
PROD_NAME_IX    PRODUCT_DESCRIPTIONS  YES   NO   08/15/2001 16:50:32
ORDER_P         ORDERS                YES  YES   08/15/2001 17:10:32
PRD_DESC_PK     PRODUCT_DESCRIPTIONS  YES  YES   08/15/2001 17:13:32

Another useful index, is this one, to check ALL the indexes on the DB
select u.name "OWNER",
       io.name "INDEX_NAME",
       t.name "TABLE_NAME",
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') "MONITORING",
       decode(bitand(nvl(ou.flags,0), 1), 0, 'NO', 'YES') "USED",
       ou.start_monitoring "START_MONITORING",
       ou.end_monitoring "END_MONITORING"
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u
where t.obj# =i.bo#
  and io.owner# = u.user#
  and io.obj# = i.obj#
  and u.name not in ('SYS','SYSTEM','XDB','WMSYS','ORDSYS','OUTLN','MDSYS','CTXSYS')
  and i.obj# =ou.obj#(+)
order by u.name, t.name, io.name;

This script stops index monitoring on an entire schema.(must be run as System)
set echo off
set feedback off
set pages 0
set verify off
accept idx_owner prompt "Enter the schema name: "
spool stop_monitor_idx.tmp
select 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
   from dba_indexes
   where owner = UPPER('&idx_owner');
spool off
set echo on
set feedback on
set pages 60
spool stop_monitor_idx.log
@stop_monitor_idx.tmp
spool off

Rebuilding Indexes
Beginning in 9i, you can rebuild your indexes online and compute statistics at the same time. Online index rebuild is also available for reverse key indexes, function-based indexes and key compressed indexes on both regular tables and index organized tables (including secondary indexes). In previous releases, you could either issue one of the following:
    alter index index_name rebuild online
 or
    alter index index_name rebuild compute statistics

However it was not possible to combine these statements into one operation. In 9i, you can issue:
    alter index index_name rebuild compute statistics online;

This allows your current users to be able to access the original index while it is being rebuilt and having statistics get generated against it. When you rebuild your indexes online, the new index is built within your index tablespace while the old index is still being used. The online option specifies that DML operations on the table or partition are allowed during rebuilding of the index. After the new index is completed the old index is dropped. When you rebuild your indexes and do not specify the online option, the database locks the entire table from being used while the index is being rebuilt. This option can also be combined with the nologging attribute to avoid generating redo when rebuilding your indexes (ex: alter index index_name rebuild compute statistics online nologging;)

Rebuilding Tables
A new package DBMS_REDEFINITON has been introduced. Online table rebuilds are achieved by utilizing incrementally maintainable materialized views behind the scenes. This places some interesting restrictions on the online rebuild operation:
The table must have a primary key
- Tables with materialized views and snapshot logs cannot be rebuilt online (ORA-12091 Cannot redefine table with materialized views)
- MVIEW container tables and AQ tables cannot be reorganized online
- IOT overflow tables cannot be reorganized online
- Tables containing user-defined types, bfile or long columns cannot be rebuilt online

To rebuild a table online:
1- Verify that the table can in fact be rebuilt online without falling foul of the above constraints
        DBMS_REDEFINTION.CAN_REDEF_TABLE( 'SCOTT' , 'EMP');
2- Create the 'new' table in the same schema (Eg. with extra column, partitioning or storage)
3- Invoke the packaged procedures required to carry out the online operation:
        exec dbms_redefinition.start_redef_table( ... );   # Begin rebuild
        exec dbms_redefinition.sync_interim_table( ... );  # synch interim mview with original table if required
        exec dbms_redefinition.finish_redef_table( ... );  # End rebuild

Beware of the following gotchas:
     - Constraints must be defined on the holding table as this will become the new table. This means that indexes supporting constraints will have to be renamed
     - Same goes for triggers
     - Foreign key constraints created on the holding table must be DISABLED (they will be automatically enabled when the operation completes)
Finally, there is a fair bit of scope for things to go wrong during the whole procedure. Depending at which point this happens, you could end up with a rogue materialized view hanging around which will prevent you from attempting another rebuild. To tidy things up after a failed rebuild, you can execute the following procedure:
exec dbms_redefinition.abort_redef_table( ... );

Get Object Creation Info
The DBMS_METADATA package is a powerful tool for obtaining the complete definition of a schema object. It enables you to obtain all of the attributes of an object in one pass. The object is described as DDL that can be used to (re)create it. It has several Procedures and Functions. The output can be directed to a Flat File using the UTL_FILE Package or inserted into a Table for later use. Examples:

Table Example
To punch off all table and indexes for the EMP table, we execute dbms_metadata. get_ddl, select from DUAL, and providing all required parameters.
set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_list.sql
select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
spool off;

Another Table Example (Create and GET_DDL)
CREATE TABLE temp (temp   VARCHAR2(10) NOT NULL);

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
    CURSOR select_table IS
      SELECT table_name
        FROM   user_tables
        WHERE table_name = 'TEMP';
    temp1 VARCHAR2(30);
    temp2 VARCHAR2(4000);
 BEGIN
    OPEN select_table;
    FETCH select_table INTO temp1;
    temp2 := SUBSTR(dbms_metadata.get_ddl('TABLE', temp1), 1, 250);
    dbms_output.put_line('SQL: ' || temp2);
 END;
/

Output:
CREATE TABLE "TREZZOJ"."TEMP"
   ( "TEMP" VARCHAR2(10) NOT NULL ENABLE
   ) PCTFREE 10
PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0
  FREELISTS 1 FREELIST GROUPS
 

My Preferred Method:
SET LINESIZE 132 PAGESIZE 0 FEEDBACK off VERIFY off TRIMSPOOL on LONG 1000000
COLUMN ddl_string FORMAT A100 WORD_WRAP
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);
SELECT dbms_metadata.get_ddl('TABLE','CUSTOMERS') ddl_string FROM dual;

If you don't want to get storage parameters or have the constraints as alter tables, you can use one of the 20 different parameters.

Changeable parameters
Parameters for all objects (value in parentheses represents default)
• DEFAULT (N/A). If TRUE, all parameters are reset to the default values.
• INHERIT (FALSE). If TRUE, all parameters are inherited from system-level.
• PRETTY (TRUE). If TRUE, DDL output is formatted with indentations and line feeds.
• SQLTERMINATOR (FALSE). If TRUE, a SQL terminator (; or /) is appended to the DDL statement.
Parameters for TABLE objects
• CONSTRAINTS (TRUE). If TRUE, DDL generates non-referential table constraints.
• CONSTRAINTS_AS_ALTER (FALSE). If TRUE, table constraints are generated as separate ALTER TABLE and CREATE INDEX statements. If FALSE, table constraints are generated as part of the CREATE TABLE statement.
• OID (FALSE). If TRUE, DDL generates the OID clause for object tables.
• REF_CONSTRAINTS (TRUE). If TRUE, DDL generates referential constraints.
• SEGMENT_ATTRIBUTES (TRUE). If TRUE, DDL generates segment attributes (physical attributes, storage attributes, tablespace, logging).
• STORAGE (TRUE). If TRUE, DDL generates the storage clause. Automatically FALSE if SEGMENT_ATTRIBUTES is FALSE.
• TABLESPACE (TRUE). If TRUE, DDL generates tablespace information. Automatically FALSE if SEGMENT_ATTRIBUTES is FALSE.
• SIZE_BYTE_KEYWORD (FALSE). If TRUE, DDL generates the BYTE keyword as part of the size specification of CHAR and VARCHAR2 columns that
use bytes.
Parameters for INDEX objects
• SEGMENT_ATTRIBUTES (TRUE). If TRUE, DDL generates segment attributes (physical attributes, storage attributes, tablespace, logging).
• STORAGE (TRUE). If TRUE, DDL generates storage clause. Automatically FALSE if SEGMENT_ATTRIBUTES is FALSE.
• TABLESPACE (TRUE). If TRUE, DDL generates tablespace information. Automatically FALSE if SEGMENT_ATTRIBUTES is FALSE.
Parameters for TYPE objects
• BODY (TRUE). If TRUE, DDL generates the type body.
• SPECIFICATION (TRUE). If TRUE, DDL generates the type specification.
Parameters for PACKAGE objects
• BODY (TRUE). If TRUE, DDL generates the package body.
• SPECIFICATION (TRUE). If TRUE, DDL generates the package specification.
Parameters for VIEW objects
• FORCE (TRUE). If TRUE, DDL uses the FORCE keyword.

Examples:
--Change all of them to its default value:
exec dbms_metadata.set_transform_param(dbms.session_transfer, 'DEFAULT' );
--Add ; at the end
exec dbms_metadata.set_transform_param(dbms.session_transfer, 'SQLTERMINATOR', true );
--With this parameter on, all constraints will be put into a separate DDL as the CREATE TABLE
exec dbms_metadata.set_transform_param(dbms.session_transfer, 'CONSTRAINTS_AS_ALTER', true );
--With this parameter turned off, the storage and tablespace clauses won’t be displayed
exec dbms_metadata.set_transform_param(dbms.session_transfer, 'SEGMENT_ATTRIBUTES', false );

Schema Example
Now we can modify the syntax to punch a whole schema.  It us easily done by selecting dbms_metadata. get_ddl and specifying USER_TABLES and USER_INDEXES. :
set pagesize 0
set long 90000
set feedback off
set echo off
spool scott_schema.sql
connect scott/tiger;
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_INDEXES u;
spool off;

  SQL*Plus Web Reports
Both Oracle8i and Oracle9i allow the generation of HTML directly from SQL*Plus. This can be used to build complete reports, or output to be embedded in larger reports. The MARKUP HTML ON statement tell SQL*Plus to produce HTML output. The SPOOL ON option of this command indicates that the results should be encapsulated in <html><body>...</body></html> tags. If a complete HTML page is not required the SPOOL OFF option can be used.

The following example displays the contents of the SCOTT.EMP table as a HTML table:
SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
SPOOL emp1.html
SELECT * FROM emp;
SPOOL OFF
SET MARKUP HTML OFF
SET ECHO ON
Alternatively, the script can save as EmpHTMLReport.sql and run from the command line:
sqlplus scott/tiger@w2k1 @EmpHTMLReport.sql

Alternatively, an individual query can be saved to EmpSQL.sql and run with the appropriate command line parameters:
sqlplus -S -M "HTML ON" scott/tiger@w2k1 @EmpSQL.sql>emp3.html



Table Function
A table function is a function that produces a collection of rows that can be queried just like a table. The collection of rows is either a nested table or a VARRAY.  It eliminates the need to store intermediate data into temporary table as the data is directly passed to next stage. The following is an example of table function that returns the list of authors of the given book. The names of authors are stored in AUTHORS column of BOOKS table. Author names are separated by comma (,). Table function returns the list of author names in the form of a table, which can be used just like a table in SELECT.
First let us create required types and table.
create or replace type authors_table  as table of varchar2(30);

The above command creates AUTHORS_TABLE, which is a collection of strings.
create table books
( title varchar2(30),
  authors varchar2(500)
);

BOOKS table contains title and list of authors of the book. Author names are separated by comma.  The following is sample data of BOOKS table.
insert into books values ('uml user guide','grady booch, james runbaugh, ivar jacobson');
insert into books values ('core java','horstmann,cornell');
insert into books values ('oracle8i comp. ref.','kevin loney, george koch');

The following table function takes title and returns the names of author in the form of AUTHORS_TABLE.
create or replace function  getauthors(p_title varchar2) return  authors_table
is
  atab authors_table;
  al varchar2(500);
  p  integer;
  a  varchar2(30);
begin
   atab :=  authors_table();
   select  authors into  al
   from  books where title = p_title;
   p := instr(al,',');
   while p <> 0
   loop
          a := substr(al,1,p -1);
          atab.extend;
          atab(atab.last) := a;
          al := substr( al, p+1);
          p := instr(al,',');
   end loop;
   atab.extend;
   atab( atab.last) := al;
   return atab;
end;
/

Once function is created then it can be called using TABLE keyword as following in SELECT command.
select b.title, a.* from  books b,
     table(getauthors(b.title)) a;

TITLE                          COLUMN_VALUE
------------------------------ ------------------------------
UML User Guide                 Grady Booch
UML User Guide                 James Runbaugh
UML User Guide                 Ivar Jacobson
Core Java                      Horstmann
Core Java                      Cornell
Oracle8i Comp. Ref.            Kevin Loney
Oracle8i Comp. Ref.            George Koch

QUIESCING A Database
Quiescing a database is a powerful feature that helps Oracle DBAs to do certain kinds of work they would not be able to do otherwise, without putting the database in the restricted mode. Using this feature, after logging in to the SYS or SYSTEM account, the DBA can do queries, PL/SQL, and other transactions. For other users, the system will seem to be inactive or in the HALT stage. All of the user's transactions will be kept in a suspended state and will resume automatically, once the DBA puts the system back into a normal state. You can now quiesce the instance (prevent any new transactions from starting) with the ALTER SYSTEM QUIESCE RESTRICTED command. However, transactions already in progress will be allowed to continue; you'll need to wait for these transactions to complete before the instance will be trully quiesced. The database will be quiesed once the user resolves all the active transaction either by COMMIT or ROLLBACK. 

How do you determine what sessions are connected and waiting for UNQUIESCING the database?
To determine this issue the following query:

SELECT SID,EVENT,TOTAL_WAITS,TIME_WAITED "TIME WAITED[100 OF SEC]",
AVERAGE_WAIT FROM V$SESSION_EVENT
WHERE EVENT='wait for possible QUIESCE finish'
/
SID EVENT                              TOTAL_WAITS            Time Waited[100 of Sec] AVERAGE_WAIT
--- ---------------------------------- -----------            ----------------------- ------------
  6 wait for possible QUIESCE finish           412                             126532          307

More Changes
With the introduction of default temporary tablespace, the SYSTEM tablespace is no longer used as the default storage location for temporary data. Oracle9i also allows for better control over database downtime by enabling administrators to specify the mean time to recover (MTTR) from system failures in number of seconds. This feature coupled with more dynamic initialization parameters help administrators further improve database availability.
The Enterprise Manager interface to the MTTR Advisory helps you select the best MTTR time for your needs by reporting the advice statistics in a graphical as well a tabular format. The MTTR Advisor is available from Enterprise Manager by selecting:
Databases > Database Name > Instance > Configuration > Recovery tab > Advice next to Desired mean time to recover.

Oracle9i also supports databases created with multiple block sizes, and allows administrators to configure corresponding 'sub caches' within the buffer cache for each block size. This capability allows administrators to place objects in tablespaces of appropriate block size in order to improve I/O performance, and also allows tablespaces to be transported between different databases, for example, from an OLTP environment to a Data Warehousing environment.

To ease backup and recovery operations, Recovery Manager in Oracle9i enables one time backup configuration, automatic management of backups and archived logs based on a user specified recovery window, restartable backups and restores, and test restore/recovery. Recovery Manager implements a recovery window, a new policy to control when backups expire. This allows administrators to establish a period of time during which it will be possible to discover logical errors and fix the affected objects by doing a database or tablespace point-in-time recovery.
Recovery Manager will also automatically expire backups that are no longer required to restore the database to a point-in-time within the recovery window. These features are designed to reduce the time and effort spent by administrators
in performing routine backup tasks by providing for automation for most commonly performed tasks. The new controlfile autobackup feature also allows for restoring/recovering a database even when a Recovery Manager repository is not available. Recovery Manger in Oracle9i also features enhanced reporting, a more user friendly interface and the ability to specify a highly flexible backup configuration to suit varied requirements depending on the nature of database and business needs.

Database Suspend/Resume - This feature allows you to suspend database activity by stopping I/O to all datafiles and queuing any further I/O requests. When ready, you can then resume database operations and the system will re-activate all pending I/O requests in the order they were queued. This is extremely useful for system backups that involve splitting mirrored disks.

"alter database end backup" command - takes all datafiles out of backup mode in a single command. This new command allows you to script database crash recovery procedures without the need for user intervention.

Scrollable cursors - New cursor type in which you can move both forwards and backwards through the result set. Can use either absolute row numbers or relative row number offsets depending on your position in the result set. Additional memory usage enhancements have been included for greater efficiency.

Compile Invalid Objects for a full Schema
exec dbms_utility.compile_schema('SCHEMA_NAME');

Cached Execution Plans
The V$SQL_PLAN view can be used to view the execution plans of recent SQL statements. It contains similar information to an EXPLAIN PLAN, except the EXPLAIN PLAN is a theoretical execution plan, whereas this view contains the actual execution plans used by the server

Write/Delete Files to the OS
UTL_FILE.FREMOVE, UTL_FILE.FRENAME, UTL_FILE.FCOPY and UTL_FILE.FGETATTR
These are new packages that let you create, delete, etc files directly from the OS. You just need to use one command to define the location of those files. You will need to have 'GRANT CREATE ANY DIRECTORY TO' privilege.  There are some limitations like: UTL_FILE can only see the file system on the server itself.  UTL_FILE is run in the Oracle dedicated/shared server which resides on the server -- it is not running on the client side -- it cannot see the file systems the client can see.  For utl_file to write to a netware volumn -- that volumn would have to be mounted as a unix file system and accessible to the server itself.
A good example is HERE and on http://otn.oracle.com/oramag/utl_file.html

Oracle Shared Server
Oracle Shared Server is the new name for Oracle Multi-threaded Server (MTS) and includes several new features.
In MTS, clients contacted the listener asking for a connection. The listener sent the address of a dispatcher to the client, who then contacted the dispatcher directly to confirm the connection. In Oracle Shared Server the client contacts the listener to ask for a connection resulting in a connection socket being established between the listener and the client. The listener then hands this socket to the relevant dispatcher to allow instant communication between the client and the dispatcher. This process is called a Direct Handoff.

Prior to Oracle9i, dispatchers handled network and database events separately. Network events were handled using the UNIX system call poll() via the network services, while database event were handled by the Virtual Operating System (VOS), an internal abstraction layer not visible to the DBA. Polling and coordinating both event models wasted time. In Oracle9i the Common Event Model has poll() incorporated into the VOS layer on the database so there is no need to poll the network services anymore.

Performance Manager, part of the Diagnostics Pack in Oracle Enterprise Manager (OEM), provides graphical monitoring the shared server. This can be done with an overall view, or detailed to the level of dispatchers, shared server processes and listeners. In addition Performance Manager can view the SQL statements fired for each session and provide help in tuning the Shared Server parameters

Recovery
+ More fault tolerance
+ Ignore corruption and carry on
+ Back out of botched session
+ Selective skipping of blocks
+ Only those that apply
+ Online table re-org
+ Rollback managed in the tablespace
+ Standby database failover and failback
+ Different block size by tablespace

Bitmap join indexes
Bitmap Join Indexes are a new feature in 9i, but you might well ask, “What the hell is a bitmap join index?”The short answer is: it’s an index built on table A based on some value in table B, imagine the following tables:

SALES:  
ORDNO CUSTCODE  VALUE
101        A    103.40
102        A    123.90
103        B    9832.90
104        C    546.75
105        D    789.34

CUSTOMERS:
CUSTCODE    NAME                   LOCATION
A           W.H.Smith & Sons Ltd   Sydney
B           Boots the Chemist      Adelaide
C           Waterhouse             Melbourne
D           Priceline              Sydney
   
Now suppose that you want to know the total value of sales in Sydney.  You obviously can’t get ‘Sydney’ out of the Sales table.  But if you were to join the two tables together via the Custcode, then you could do it. Trouble is, to do that join, we’d have to read the entire Customer table –which, in this case wouldn’t be too bad.  But if there were a million of customers that would be one hell of a full table scan. New in 9i, we can create a bitmap index on the SALES table, based on the Location column in the Customers table.  The syntax would be:
Create bitmap index cust_location
On sales(customers.city)
From sales, customers
Where sales.Custcode = customers.custcode;

That would create a bitmap index looking a bit like this:
Leaf Node 1: SYDNEY 1001
Leaf Node 2: ADELAIDE 0100
Leaf Node 3: MELBOURNE 0010

Now, a select sum(sales.value) from sales, customers where customers.location =’SYDNEY’ can be answered by querying the sales table and the bitmap index containing the location data.  The query doesn’t even need to touch the actual customers table at all –and that’s a potentially huge benefit in the right circumstances.

In effect, therefore, a bitmap join index is rather like a function-based index (which is a b-tree index feature introduced in 8i).  It means that a select for a computed (or implied) value doesn’t need to reference the lookup table itself, but can use the main table and the highly compressed and efficient bitmap index which references the lookup table.

When you start to consider that a bitmap join index can be based on more than 1 table, the advantages can be huge: “test in table A for records that match criterion 1 in table B AND criterion 2 in table C” –all without touching wither tables B or C at all.

Are there any drawbacks?  Yes, naturally: bitmap indexes of any sort and DML don’t exactly sit comfortably together.  What’s worse, with a bitmap join in place, only one of the tables can be updated at one time (simultaneous updates on tables A, B and C will be queued).  What’s more, the joining field (in our case earlier, CUSTCODE)  must be the primary key column (or at least be declared unique).

Materialized views
More sophisticated query rewite
Fast refresh opportunities

Explicitly Named Indexes On Keys
In Oracle9i the index used to support Primary and Unique keys can be defined independently of the constraint itself by using the CREATE INDEX syntax within the USING UNDEX clause of the CREATE TABLE statement:
CREATE TABLE employees
(
  empno NUMBER(6),
  name VARCHAR2(30),
  dept_no NUMBER(2),
  CONSTRAINT emp_pk primary key(empno)
    USING INDEX
    (CREATE INDEX emp_pk_idx ON employee(empno))
);
The constraint can subsequently be dropped without dropping the index using either syntax:
ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX;
ALTER TABLE employees DROP CONSTRAINT empno_pk;

 
Share Locks On Unindexed FKs
In previous versions a share lock was issued on the entire child table while the parent table was being updated if the foreign key between them was unindexed. This had the affect of preventing DML operations on the child table until the parent table transaction was complete.
In Oracle9i this situation has been altered such that a table level share lock is issued and instantly released. This action allows Oracle to check that there are no pending changes on the child table, but the instant release means that DML can resume almost instantly once the parent table update has initiated. If multiple keys are updated Oracle issues a share lock and release on the child table for each row.

PK Lookup During FK Insertion
During insertions foreign key values are checked against the primary keys of referenced tables. This process is optimized in Oracle9i by caching the first 256 PK values of the referenced table on insertion of the second record of a multiple insert. The process is done on the second record to prevent the overhead of managing the cache on a single insert.

Function Based Index Enhancements
Function Based Indexes are now capable of doing an index-only scan. In previous versions this was only possible if the index creation statement explicitly prevented NULL values. Since each built-in operator knows implicitly whether it can produce null values when all it's input parameters are not null, Oracle can deduce if nulls can be produced and therefore decide if index-only scans are possible based on the columns queried using the function based index.

SELECT .. FOR UPDATE Enhancements
Selecting a record for update that is already locked causes the current session to hang indefinitely until the lock is released. If this situation is unacceptable the NOWAIT keyword can be used to instantly return an error if the record is locked. Oracle9i adds more flexibility by allowing the programmer to specify a maximum time limit to wait for a lock before returning an error. This gets round the problem of indefinite waits, but reduces the chances of lock errors being returned:
SELECT * FROM   employees
WHERE  empno = 20
FOR UPDATE WAIT 30;

New Built-In Functions
List of New Functions
asciistr            bin_to_num            coalesce            compose
current_date        current_timestamp     dbtimezone          decompose
existsnode          extract (datetime)    extract (xml)       first
from_tz             group_id              grouping_id         last
localtimestamp      nullif                percentile_cont     percentile_disc
rawtonhex           rowidtonchar          sessiontimezone     sys_connect_by_path   
sys_dburigen        sys_extract_utc       sys_xmlagg          sys_xmlgen
systimestamp        to_char (character)   to_clob             to_dsinterval
to_nchar(character) to_nchar (datetime)   to_nchar (number)   to_nclob
to_timestamp        to_timestamp_tzto     to_yminterval       treat
tz_offset           unistr                width_bucket

New NULLIF Type Functions
Compares given two values and if they are same then return null otherwise returns first expression.
  NULLIF (expr1, expr2)
The following command displays employee number, name, and old job if it is different from current job and current job


New COALESCE_Functions
Returns the first not null value in the list of values. At least one expression out of the given expressions must not be null. The function is extended version of NVL function
Example:
The following query returns the selling price of the product. If dicount is not null then remove discount from the price, otherwise if any offer price is available give at the offer price, otherwise sell the product at the original price.
select  prodid,name, coalesce( price - discount, offerprice, price) "Selling Price"
from  products;

New BIN_TO_NUM Function
Convert the given bit pattern to a number.
The following example returns 15 as binary 1111 is equivalent to 15.
select bin_to_num(1,1,1,1) from dual;
BIN_TO_NUM(1,1,1,1)
-------------------
                 15

New EXTRACT Function
Extracts and returns the value of the specified datetime field from a datatime value.
   EXTRACT ( datatimefield    FROM   datetime)
The following example returns year from the current date.
select  extract( year from sysdate) from dual;
EXTRACT(YEARFROMSYSDATE)
------------------------
                    2002

New Datetime Functions
1.  CURRENT_DATE returns the current date in the session time zone.
2.  CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE with current date & time in session time zone.
3.  DBTIMEZONE returns the time zone offset of the database time zone.
4.  EXTRACT (datetime) returns the specified date or time field from a datetime expression or interval expression.
5.  FROM_TZ converts a TIMESTAMP value in a time zone to a TIMESTAMP WITH TIME ZONE value.
6.  LOCALTIMESTAMP returns a TIMESTAMP value with the current date and time in the session time zone.
7.  SESSIONTIMEZONE returns the time zone offset of the current session?s time zone.
8.  SYS_EXTRACT_UTC extracts the UTC (Coordinated Universal Time) from a TIMESTAMP WITH TIME ZONE.
9.  SYSTIMESTAMP returns a TIMESTAMP WITH TIME ZONE value with system date and time zone of the database.
10.  TO_CHAR (datetime) converts dates or timestamp?s to a VARCHAR2 value.  Note: timestamp conversion added.
11.  TO_DSINTERVAL converts a character string to an INTERVAL DAY TO SECOND data type.
12.  TO_NCHAR (datetime) converts dates, timestamp?s, or interval?s to a NVARCHAR2 string value.
13.  TO_TIMESTAMP converts CHAR, VARCHAR2, NCHAR, or NVARCHAR2 characters to a TIMESTAMP value.
14.  TO_TIMESTAMP_TZ converts a character string to a TIMESTAMP WITH TIME ZONE value.
15.  TO_YMINTERVAL converts a character string to an INTERVAL YEAR TO MONTH data type.
16.  TZ_OFFSET returns the time zone offset for the values on that date.  (Daylight savings time affects time zone offset)

Aggregate and Analytical Functions
1.  FIRST returns the first row of a sorted group, but the needed value is not the sort key.
2.  LAST returns the last row of a sorted group, but the needed value is not the sort key.
• Note: the FIRST and LAST functions eliminate the need for self-joins or views (enabling better performance).
3.  GROUP_ID distinguishes duplicate groups resulting from a GROUP BY specification.
4.  GROUPING_ID returns a number corresponding to the GROUPING function bit vector associated with a row.
5.  PERCENTILE_CONT is an inverse distribution function that assumes a continuous distribution model.
6.  PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model.
7.  WIDTH_BUCKET is used for constructing histograms whose intervals have identical size.

Object Functions
1.  SYS_TYPEID returns the typeid of the most specific type of the operand (must be an object type).
2.  TREAT function changes the declared type of an expression.  Note: needs execute object privilege on the type.

Unicode Functions
1.  COMPOSE takes a string in any data type, and returns a Unicode string in its fully normalized form in the same character set.  For example an 'o' qualified by an umlaut will be returned as o-umlaut.
2.  DECOMPOSE takes a string in any data type, and returns a Unicode string in canonical decomposition in the same character set.  For example o-umlaut is returned as 'o' followed by an umlaut.
3.  INSTRC searches a string for a substring using Unicode complete characters.
4.  INSTR2 searches a string for a substring using UCS2 codepoints.
5.  INSTR4 searches a string for a substring using UCS4 codepoints.
6.  LENGTHC returns the length of a character string using Unicode complete characters.
7.  LENGTH2 returns the length of a character string using UCS2 codepoints.
8.  LENGTH4 returns the length of a character string using UCS4 codepoints.
9.  SUBSTRC returns a portion of a string using Unicode complete characters.
10.  SUBSTR2 returns a portion of a string using UCS2 codepoints.
11.  SUBSTR4 returns a portion of a string using UCS4 codepoints.
12.  UNISTR converts a character string into the database Unicode character set.

Conversion Functions
1.  ASCIISTR takes as its argument a string in any character set and returns an ASCII string in the database character set.
2.  BIN_TO_NUM converts a bit vector (0s and 1s) to its equivalent number.
3.  CAST converts one built-in data type or collection-typed value into another built-in data type or collection-typed value. Note: the Oracle8i SQL Reference had a CAST expression.
4.  DECODE compares an expression to a list of search values.  If a search value is found, the corresponding result is returned.  Note: the Oracle8i SQL Reference had a DECODE expression.
5.  NCHR returns the character having the binary equivalent in the national character set.  Equivalent to CHR function with the USING NCHAR_CS clause.
6.  RAWTONHEX converts raw to an NVARCHAR2 character value containing its hexadecimal equivalent.
7.  ROWIDTONCHAR converts a rowed value to NVARCHAR2 value that is 18 characters long.
8.  TO_CHAR (character) converts NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set.
9.  TO_CLOB converts NCLOB values to CLOB values (converts from national character set to database character set).
10.  TO_NCHAR (character) converts CHAR, VARCHAR2, CLOB, or NCLOB data to the national character set.
11.  TO_NCHAR (number) converts a number to a NVARCHAR2 string value.
12.  TO_NCLOB converts character data from the database character set to the national character set.

Data Segment Compression
Data compression reduces the amount of data being stored (faster full table scans), reduces memory usage (more data per memory block), and increases query performance. It's very good for lookup tables and for reading systems, so it's very beneficial for DW Systems.
Data compression in Oracle9I Release 2 is performed at the block level. Administrators are able to compress entire tables and specific table partitions. The COMPRESS attribute can be specified in the CREATE TABLE, CREATE TABLESPACE and CREATE TABLESPACE …. PARTITION statements. Block compression works by eliminating repeating column values. The more repeating values the columns have the greater the compression ratio becomes.
Administrators are able to achieve higher compression ratios by sorting the rows on a column that has a poor cardinality (high number of repeating values).
Although UPDATE and DELETE operations can be performed on compressed data in Oracle, the performance impact the compress and uncompress operations have on those statements is yet to be determined. Oracle recommends that compressed data be highly considered for data warehouse environments because data block compression is highly optimized for direct-load operations. Note: tables with large amounts of DML operations are not good candidates for compression. Syntax is too easy:
   create table table2 COMPRESS tablespace users as
      select * from table1;
or
  create table (...) compress;
   alter table AA compress;   --this will not reorganize the table, is just for new data
   alter table AA move compress;  -- this will reorganize the table, reducing # of blocks
or
  --Create a Compressed Tablespace so all its objects will be compressed
  create tablespace datacompress
  datafile '.../...../datacompress.dbf' size 10M
  default compress;

To ensure that data is actually compressed, you need to use a proper method to load or insert data into the table. Data compression takes place only during a bulk load or bulk insert process, using one of the following four methods:

• Direct path SQL*Loader
• Serial INSERT with an APPEND hint
• Parallel INSERT
• CREATE TABLE ... AS SELECT

 
Export And Import Enhancements In Oracle9i
A number of new features have been incorporated into the Export and Import utilities including:

• RESUMABLE
• STATISTICS
• TABLESPACES
  
• TABLES Pattern Matching
  
• Reduced Character Set Conversions

RESUMABLE
The RESUMABLE parameter is used to enable and disable resumable space allocation. The default value for the parameter is 'N' so it must be explicitly set to 'Y' for its associated parameters (RESUMABLE_NAME and RESUMABLE_TIMEOUT) to be used.
The RESUMABLE_NAME parameter allows a text name to be assigned to the transaction. This name can subsequently be used to identify suspended transactions when queried using the USER_RESUMABLE or DBA_RESUMABLE views.
The RESUMABLE_TIMEOUT parameter specifies the length of time the export can be suspended for before the underlying transactions aborts.

STATISTICS
During Export this parameter specifies the type of database optimizer statistics to generate when the exported data is imported. The options are ESTIMATE (default), COMPUTE, and NONE. The export file may contain some precalculated statistics along with the ANALYZE statements. These statistics will not be used at export time if a table has columns with system-generated names.
The precalculated optimizer statistics are flagged as questionable at export time if:

• Row errors occur during the export.
• The client and server character sets do not match.
• A QUERY clause is used.
  
• All partitions or subpartitions are not exported.

During Import the parameter options are ALWAYS (default), NONE, SAFE and RECALCULATE. The ALWAYS option imports all statistics, even those that are questionable. The NONE option does not import or recalculate statistics. The SAFE option imports statistics that are not questionable, but recalculates those that are. Finally, the RECACULATE option recaculates all statistics rather than importing them.

TABLESPACES
The TABLESPACES parameter is used to export all tables contained within the specified tablespace or tablespace list. The table indexes are included regardless of their tablespace location. Partitioned tables are included if one or more partitions are located in the specified tablespace. The EXP_FULL_DATABASE role is required to use TABLESPACES parameter.
During Import the TABLESPACES parameter is used in conjunction with the TRANSPORT_TABLESPACE parameter to specify the transportable tablespaces that should be imported. This functionality is unchanged and has no relationship to the TABLESPACES parameter used for export.

TABLES Pattern Matching
The TABLES parameter has been enhanced to allow pattern matching:
TABLES=(MYSCHEMA.ORDER%, MYSCHEMA.EMP%)
In the above example, any tables located in MYSCHEMA whose name begins with ORDER or EMP will be exported.

Reduced Character Set Conversions
All user data in text related datatypes is exported using the character set of the source database. If the character sets of the source and target databases do not match a single conversion is performed.
The export and import process can require up to three conversions for DDL. The DDL is exported using the character set specified by the NLS_LANG environment variable. A character set conversion is performed if this differs from the source database. If the export character set differs from that specified by the import users NLS_LANG parameter another conversion is performed. This conversion cannot be done for multibyte character sets so these must match. A final characterset conversion is needed if the import session and the target database have different character sets. To eliminate possible data loss caused by conversions, make sure all environments have matching character sets.

Enhanced Statistics Gathering
Oracle's query optimizer uses statistics about the objects in the database (such as the number of rows in each table). These statistics are gathered by database administrator's using the DBMS_STATS facility. In Oracle9i it is now possible to automatically determine the appropriate sampling percentage as well as the appropriate columns for histograms. These enhancements simplify the database administrator's task in gathering accurate statistics. There are also new system statistics that are collected by the CBO to use and apprehend CPU and system I/O information. For each plan candidate, the optimizer computes estimates for I/O and CPU costs. Statistics are captured by setting the STATISTICS_LEVEL dynamic initialization parameter to TRUE (default) or ALL. Although a small area of the SGA is used for capturing this information, there is no significant impact on overall database performance.
Note:149560.1 Collect and Display System Statistics (CPU and IO) for CBO usage

You can now use dbms_stats to estimate statistics based on a specified sample size (from memory, earlier versions of dbms_stats only did computation, not estimation, of statistics). DBMS_STATS also has a number of new “method options” available.  Basically, you can specify “REPEAT”, “AUTO” or “SKEWONLY”.
“Repeat” simply repeats the collection method used to calculate statistics previously –if there was no histogram before, there still won’t be.  If there was, there will be.
“Auto” means the package might decide to calculate new histograms if it deems that the data is skewed sufficiently, and if it determines that sufficient prior use of the column as a predicate would warrant such a calculation.
“Skewonly” means the package will create new histograms if it deems the data to be skewed sufficiently, regardless of whether that column has ever been a popular choice for a predicate in the past.

For example:
Execute dbms_stats.gather_schema_stats ( ownname => ‘SCOTT’, method_opt => ‘for all columns size AUTO’)
or…
Execute dbms_stats.gather_schema_stats ( ownname => ‘SCOTT’, method_opt => ‘for all columns size SKEWONLY’)
or…
Execute dbms_stats.gather_schema_stats ( ownname => ‘SCOTT’, method_opt => ‘for all columns size 75’)

System Statistics Example:
Execute dbms_stats.gather_system_stats(interval => 60, stattab =>’sometable’, statid => ‘OLTP daytime’)
That statement will cause statistics to be gathered for a period of 1 hour (60 minutes) after this command is issued, and the statistics will be stored in a table called ‘sometable’, with a unique identifier of ‘OLTP daytime’. 

The table in which statistics will be stored has to be created beforehand, of course.  There’s a procedure to do that, too:
execute dbms_stats.create_stats_table(‘SYS’,’sometable’,’sometablespace’)
Once the statistics have been captured in the named table, they need to be transferred into the data dictionary for them to actually influence the work of the optimiser. 

Statistics Level Collection Parameter
The STATISTICS_LEVEL parameter was introduced in Oracle9i Release 2 (9.2) to control all major statistics collections or advisories in the database. The level of the setting affects the number of statistics and advisories that are enabled:

BASIC: No advisories or statistics are collected.
TYPICAL(default): The following advisories or statistics are collected:

• Buffer cache advisory
• MTTR advisory
• Shared Pool sizing advisory
  
• Segment level statistics
  
• PGA target advisory
  
• Timed statistics

ALL: All of TYPICAL, plus the following:

• Timed operating system statistics
• Row source execution statistics

The parameter is dynamic and can be altered using:
ALTER SYSTEM SET statistics_level=basic;
ALTER SYSTEM SET statistics_level=typical;
ALTER SYSTEM SET statistics_level=all;

Current settings for parameters can be shown using:
SHOW PARAMETER statistics_level
SHOW PARAMETER timed_statistics

Oracle can only manage statistic collections and advisories whose parameter setting is undefined in the spfile. By default the TIMED_STATISTICS parameter is set to TRUE so this must be reset for it to be controled by the statistics level, along with any other conflicting parameters:
ALTER SYSTEM RESET timed_statistics scope=spfile sid='*';

This setting will not take effect until the database is restarted.
At this point the affect of the statistics level can be shown using the following query:
COLUMN statistics_name      FORMAT A30 HEADING "Statistics Name"
COLUMN session_status       FORMAT A10 HEADING "Session|Status"
COLUMN system_status        FORMAT A10 HEADING "System|Status"
COLUMN activation_level     FORMAT A10 HEADING "Activation|Level"
COLUMN session_settable     FORMAT A10 HEADING "Session|Settable"

SELECT statistics_name, session_status, system_status, activation_level, session_settable
FROM   v$statistics_level
ORDER BY statistics_name;

A comparison between the levels can be shown as follows:

SQL> ALTER SYSTEM SET statistics_level=basic;
System altered.

SQL> SELECT statistics_name, session_status, system_status, activation_level, session_settable
     FROM   v$statistics_level
     ORDER BY statistics_name;

                               Session    System     Activation Session
Statistics Name                Status     Status     Level      Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice            DISABLED   DISABLED   TYPICAL    NO
MTTR Advice                    DISABLED   DISABLED   TYPICAL    NO
PGA Advice                     DISABLED   DISABLED   TYPICAL    NO
Plan Execution Statistics      DISABLED   DISABLED   ALL        YES
Segment Level Statistics       DISABLED   DISABLED   TYPICAL    NO
Shared Pool Advice             DISABLED   DISABLED   TYPICAL    NO
Timed OS Statistics            DISABLED   DISABLED   ALL        YES
Timed Statistics               DISABLED   DISABLED   TYPICAL    YES
8 rows selected.

SQL> ALTER SYSTEM SET statistics_level=typical;
System altered.

SQL> SELECT statistics_name, session_status, system_status, activation_level, session_settable
     FROM   v$statistics_level
     ORDER BY statistics_name;

                               Session    System     Activation Session
Statistics Name                Status     Status     Level      Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO
MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO
PGA Advice                     ENABLED    ENABLED    TYPICAL    NO
Plan Execution Statistics      DISABLED   DISABLED   ALL        YES
Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO
Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO
Timed OS Statistics            DISABLED   DISABLED   ALL        YES
Timed Statistics               ENABLED    ENABLED    TYPICAL    YES
8 rows selected.

SQL> ALTER SYSTEM SET statistics_level=all;
System altered.

SQL> SELECT statistics_name, session_status, system_status, activation_level, session_settable
     FROM   v$statistics_level
     ORDER BY statistics_name;

                               Session    System     Activation Session
Statistics Name                Status     Status     Level      Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO
MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO
PGA Advice                     ENABLED    ENABLED    TYPICAL    NO
Plan Execution Statistics      ENABLED    ENABLED    ALL        YES
Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO
Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO
Timed OS Statistics            ENABLED    ENABLED    ALL        YES
Timed Statistics               ENABLED    ENABLED    TYPICAL    YES
8 rows selected.

Oracle provides the V$STATISTICS_LEVEL view to provide information on the status of statistics collection and advisories set by the STATISTICS_LEVEL parameter. V$STATISTICS_LEVEL contains a row for each statistic or advisory being collected.

 
Optimizer Dynamic Sampling
Oracle 9I Release 2 introduces optimizer dynamic sampling to overcome the lack of accurate statistics on the objects being accessed. The optimizer is now able to take a sampling of the data during access path optimization. Administrators are able to activate dynamic sampling and control the size of the dynamic sample taken by using the OPTIMIZER_DYNAMIC_SAMPLING dynamic initialization parameter as a throttle.
The values for OPTIMIZER_DYNAMIC_SAMPLING range from 0 to 10 with 0 telling the cost-based optimizer to not use dynamic sampling and the value 10 telling the optimizer to sample all blocks in the table. The DYNAMIC SAMPLING hint can be used at the statement level to override the system setting defined by OPTIMZER_DYNAMIC_SAMPLING.
Oracle recommends that dynamic sampling only be used when the time required to do the sample is a small fraction of the statement's total execution time. It's a safe assumption that dynamic sampling will not be used in many OLTP systems but it may find a home in a few decision support and data warehouse environments.

Locally Managed System Tablespace
Locally managed tablespaces allow the Oracle system to automatically manage an object's extents. Oracle states that locally managed tablespaces provide increases in the concurrency and speed of space operations and generally have a positive impact on application performance.
Locally managed tablespaces track all extent information in the tablespace itself, using bitmaps, resulting in the following benefits:
  * Improved concurrency and speed of space operations, because space allocations and deallocations predominantly modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues
  * Improved performance, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated
  * Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do  not generate any undo or redo.
  * Simplified space allocation when the AUTOALLOCATE clause is specified, appropriate extent size is automatically selected
  * Reduced user reliance on the data dictionary because necessary information is stored in file headers and bitmap blocks

In Oracle9i Release 2, administrators are now able to create a locally managed SYSTEM tablespace. The EXTENT MANAGEMENT LOCAL clause can be used to create a locally managed SYSTEM tablespace during database creation or administrators can use the stored procedure DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL to migrate existing dictionary managed tablespaces to locally managed. Once you create or migrate to a locally managed SYSTEM tablespace, you are unable to create any dictionary-managed tablespaces in the database.
Administrators must be careful when using the aforementioned package to migrate SYSTEM tablespaces from dictionary managed to locally managed. All tablespaces (except those containing UNDO segments) must be in READ ONLY mode for the migration process to successfully execute. If Oracle finds any tablespaces in READ WRITE mode, the error message states that the tablespaces must be placed in READ ONLY mode.
The problem is that if the administrator places any dictionary managed tablespaces in READ ONLY mode, they will be unable to place them in READ WRITE mode after the SYSTEM tablespace is converted to locally managed. Administrators desiring to migrate their SYSTEM tablespaces to locally managed must migrate all READ WRITE dictionary managed tablespaces to locally managed tablespaces BEFORE MIGRATING THE SYSTEM TABLESPACE.
The LOCALLY MANAGED value is the default setting in the Storage tab for all tablespaces, including the SYSTEM tablespace.

Migrate Tablespaces from Dictionary to Local Management In 9.2
Remember that all the tablespaces MUST be migrated BEFORE you migrate the SYSTEM tablespace.
Use the DBMS_SPACE_ADMIN package as follows:
   SQL> execute sys.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TABLESPACE_NAME');
 
This operation requires that:
  --> Migrate all other dictionary tablespaces to locally managed before migrating the SYSTEM tablespace (otherwise inescapable situation)
  --> The database has a default temporary tablespace that is not SYSTEM:
   --> There are no rollback segments in dictionary-managed tablespaces
  --> There is at least one online rollback segment in a locally managed tablespace, or if using automatic undo management, an undo tablespace is online.
  --> All tablespaces other than the tablespace containing the undo space (that is, the tablespace containing the rollback segment or the undo tablespace) are in read-only mode:
  --> There is a cold backup of the database
  --> The system is in restricted mode:

Oracle Net Trace Assistant
Oracle administrators use tracing to provide a detailed description of the operations performed by Oracle's internal components. The trace information is sent to an output trace file, which can then be investigated to provide an insight into the events that are causing the problem. Oracle provides tracing mechanisms for both client and server communication programs.
The information in these trace files is sometimes so cryptic that it is unusable. Oracle9i Release 2 introduces the Oracle Net Trace assistant to help administrators decipher information contained in the trace files. The TRCASST [options] <tracefilename> command can be used to provide a more readable trace

The parameter LOG_CHECKPONT_INTERVAL and FAST_START_IO_TARGET is not used any more, is better to use FAST_START_MTTR_TARGET.
With these parameter DBA's can specify the average recovery time (in seconds) from instance crashes . This feature is implemented by limiting the number of dirty buffers and the number of redo records generated between the most recent redo record and the last checkpoint. The database server essentially adjusts the checkpoint rate to meet the specified recovery target. If FAST_START_MTTR_TARGET parameter is set to too low a value, then the database will be writing to disk very frequently and this could easily result in system slowdown.  The parameter should therefore be adjusted such that the system is not doing excessive checkpoints but at the same time, the recovery time is still within acceptable limits.  Oracle9i R2 has an advisory called Recovery Cost Estimate for determining the performance impact for various settings of the FAST_START_MTTR_TARGET.  This feature greatly simplify the determination of the right value for this parameter. Use at least 300 for this value (the maximum is 3600 = 1hour). Use show parameter fast to see its values and select * from V$MTTR_TARGET_ADVICE for recommendations.
The Enterprise Manager interface to the MTTR Advisory helps you select the best MTTR time for your needs by reporting the advice statistics in a graphical as well a tabular format. The MTTR Advisor is available from Enterprise Manager by selecting:
Databases > Database Name > Instance > Configuration > Recovery tab > Advice next to Desired mean time to recover.

DBNEWID (Change the Internal Database Identifier)
DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database. This utility is also used in creating logical standby databases.
Prior to the introduction of the DBNEWID utility, you could manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, you could not give the database a new identifier (DBID). The DBID is an internal, unique identifier for a database. Because Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. The DBNEWID utility solves this problem by allowing you to change any of the following:
·    The DBID of a database
·    The DBNAME of a database
·    Both the DBNAME and DBID of a database

The actions to perform are:
    * Backup the database.
    * Mount the database after a clean shutdown:
          SHUTDOWN IMMEDIATE
          STARTUP MOUNT

    * Invoke the DBNEWID utility (nid) from the OS specifying the new DBNAME from the command line using a user with SYSDBA privilege:
          nid TARGET=sys/password@TSH1 DBNAME=TSH2

      Assuming the validation is successful the utility prompts for confirmation before performing the actions. Typical output may look something like:
          C:\oracle\920\bin>nid TARGET=sys/password@TSH1 DBNAME=TSH2
          DBNEWID: Release 9.2.0.3.0 - Production
          Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
          Connected to database TSH1 (DBID=1024166118)
          Control Files in database:
              C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL
              C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL
              C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL
          Change database ID and database name TSH1 to TSH2? (Y/[N]) => Y
            .....

Virtual Indexes
Virtual Indexes are another undocumented feature used by Oracle. Virtual indexes, as the name suggests are pseudo-indexes that will not behave the same way that normal indexes behave, and are meant for a very specific purpose.
As per Oracle, this functionality is not intended for standalone usage. It is part of the Oracle Enterprise Manger Tuning Pack (Virtual Index Wizard). The virtual index wizard functionality allows the user to test a potential new index prior to actually building the new index in the database. It allows the CBO to evaluate the potential new index for a selected SQL statement by building an explain plan that is aware of the potential new index. This allows the user to determine if the optimizer would use the index, once implemented.
I do not see much use of Virtual Indexes in a development area where we can create and drop indexes while testing. However, this feature could prove handy if a query or group of queries have to be tested in production (for want of simulation or urgency!), to determine if a new index will improve the performance, without impacting existing or new sessions.

First, note that virtual indexes are only enabled be for a session that sets a certain parameter:
Alter Session SET "_use_nosegment_indexes" = TRUE;

Now, create the virtual index, using the special keyword Nosegment:
Create index Index_V on TABLE(Columns) NOSEGMENT compute statistics;

With the index in place, simply use explain plan as usual to see if the new index gets selected. Note: Don’t forget to drop the index when your testing is complete!
Then just :
set autotrace on
select * from xxx where aaaa=111;

And you can see your new plan.

Say goodbye
Server Manager
bstat/estat
Connect internal
Traditional temporary tablespaces
Incremental export
ANALYZE
LONG data type
NCHAR
Rollback segment management
Initialization parameters: ROLLBACK_SEGMENTS, LOG_CHECKPOINT_INTERVAL, DB_BLOCK_BUFFERS
 
DBMS_XPLAN
The DBMS_XPLAN package is used to format the output of an explain plan. It is intended as a replacement for the utlxpls.sql script.
Create a PLAN_TABLE if it does not already exist:
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT INSERT, UPDATE, DELETE, SELECT ON sys.plan_table TO public;

Next we explain an SQL statement:
conn scott/tiger
EXPLAIN PLAN FOR
SELECT *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

Finally we use the DBMS_XPLAN.DISPLAY function to display the execution plan:
SET LINESIZE 130
SET PAGESIZE 0
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    57 |     3 |
|   1 |  NESTED LOOPS                |             |     1 |    57 |     3 |
|*  2 |   TABLE ACCESS FULL          | EMP         |     1 |    37 |     2 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT        |     1 |    20 |     1 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT     |     1 |       |       |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

Note: cpu costing is off

The DBMS_XPLAN.DISPLAY function can accept 3 parameters:

• table_name - Name of plan table, default value 'PLAN_TABLE'.
• statement_id - Statement id of the plan to be displayed, default value NULL.
• format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL'.

EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR
SELECT *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

SET LINESIZE 130
SET PAGESIZE 0
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));

----------------------------------------------------
| Id  | Operation                    |  Name       |
----------------------------------------------------
|   0 | SELECT STATEMENT             |             |
|   1 |  NESTED LOOPS                |             |
|   2 |   TABLE ACCESS FULL          | EMP         |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT        |
|   4 |    INDEX UNIQUE SCAN         | PK_DEPT     |
----------------------------------------------------

To use the autotrace facility
As sys run the following:
@$ORACLE_HOME/sqlplus/admin/plustrce;
@$ORACLE_HOME/rdbms/admin/utlxplan;
grant plustrace to public ;

Then each user must run
@$ORACLE_HOME/rdbms/admin/utlxplan;
and then they can use the Sentence
set autotrace on

DBMS_Utility.get_time

The get_time function in the dbms_utility package is intended  to be used to get the difference in time between two calls to it. Its  advantage is that the time is in hundredths of seconds granularity  instead of seconds.

DECLARE
   v_time    number;
BEGIN
   v_time := dbms_utility.get_time;
   --your code
  dbms_output.put_line('Time Used: ' || (dbms_utility.get_time - v_time) / 100 || ' secs');
END; 

Native Compilation of PL/SQL
The PL/SQL native compiler feature in Oracle9i compiles PL/SQL code for faster access. The greatest benefit is to applications that use stored procedures, triggers etc. and are data intensive; whether the PL/SQL runs in the application server or database server, performance can be very significantly improved. By taking fewer cycles to run a piece of logic, this also enables the Oracle server scalability to improve. This option is perfect for computer intensive operations (like mathematical calculations).
In Oracle9i, a PL/SQL library unit can be compiled as native C code, rather than interpreted as byte code. It is then stored as a shared library in the file system. The process of compiling a PL/SQL function or procedure is very simple:
      ALTER FUNCTION my_func COMPILE;
Compilation results in faster execution of PL/SQL programs. The improvement in execution speed is due to the following factors:
· Elimination of the overhead associated with interpreting byte code
· Better, faster Control flow in native code than in interpreted code
The compiled code corresponding to a PL/SQL program is mapped to a PGA as opposed to SGA to allow better concurrent access. With native compilation, PL/SQL that does not contain SQL references can be 2-10 times faster, though performance remains dependent on a large number of applicationspecific factors.
To turn native compilation on, you need to issue the following statement:
         ALTER SESSION SET plsql_compiler_switches=NATIVE;
This is all you need to do after a simple 1-time setup by your DBA to configure Oracle to run native code securely. This setting is persistent and if the procedure is automatically recompiled it will recompile in native mode. We have measured a reduction in elapsed time for a computationally intensive unit (in single user tests) of about 40% when it’s compiled Native. While for data intensive programs native compilation may give only a marginal performance improvement, we have never seen it give performance degradation. Furthermore, the m-code is loaded into the SGA whereas the dynamically linkable shared library is loaded into regular operating system memory. Thus when the whole database is compiled natively, a higher throughput may be possible due to reduced SGA contention. The simplest way to honor the recommendation above (Oracle recommends that all the PL/SQL library units that are called from a given top-level unit are compiled in the same mode) is to upgrade the whole database so that all PL/SQL library units are compiled NATIVE. A release soon after Oracle9i Database Version 9.2.0 will include such a script together with its partner to downgrade a whole database so that all PL/SQL library units are compiled INTERPRETED. Meanwhile, these are posted on OTN here… http://otn.oracle.com//tech/pl_sql/htdocs/README_2188517.htm

Removing Examples Schemas
Check how to remove them in Metalink Notes:160861.1 and 207560.1

Temporary Tables
The definition of a temporary table persists just like a permanent table, but contains either session-specific or transaction-specific data. Both of these types control how temporary you want the data to be. The session using the temporary table gets bound to the session with the first insert into the table. This binding goes away, and thus the data disappears, by issuing a truncate of the table or by ending either the session or transaction depending on the temporary table type.
The DDL for creating a session-specific temporary table is presented here:
CREATE GLOBAL TEMPORARY TABLE search_results
(search_id NUMBER, result_key NUMBER)
ON COMMIT PRESERVE ROWS;
or
CREATE GLOBAL TEMPORARY TABLE search_results
(search_id NUMBER, result_key NUMBER)
ON COMMIT DELETE ROWS;

Features of temporary tables
- Data is visible only to the session.
- The table definition is visible to all sessions.
- In rolling back a transaction to a save point, the data will be lost but the table definition persists.
- You can create indexes on temporary tables. The indexes created are also temporary, and the data in the index has the same session or transaction scope as the data in the table.
- You can create views that access both temporary and permanent tables.
- You can create triggers on a temporary table.
- You can use the TRUNCATE command against the temporary table. This will release the binding between the session and the table but won’t affect any other sessions that are using the same temporary table.
- The export and import utilities handle the definition of the temporary table, but not the data.

Restrictions
- Temporary tables can’t be index organized, partitioned, or clustered.
- You can’t specify foreign key constraints.
- Columns can’t be defined as either varray or nested tables.
- You can’t specify a tablespace in the storage clause. It will always use the temporary tablespace.
- Parallel DML and queries aren’t supported.
- A temporary table must be either session- or transaction-specific it can’t be both.
- Backup and recovery of a temporary table’s data isn’t available.
- Data in a temporary table can’t be exported using the Export utility.

Stats on Temp Tables
By default, the optimizer will assume that there are N rows in a global temporary table (N is 8,168 in an 8K block size database). Since it would be rare for your global temporary table to have 8,168 rows in real life, you need to give a hand to the optimizer and provide it with realistic statistics. In Oracle 10g, since the Cost-Based Optimizer (CBO) is the only optimizer, it is much more important to have correct statistics. Therefore, the database employs a technique called dynamic sampling, first introduced with Oracle9i Release 2. Dynamic sampling permits the optimizer to take a quick look at the table when statistics are missing. It will sample the data in the table to come up with better estimates of what it is dealing with.
So the three solutions available to you are

1        Using dynamic sampling

2        Using DBMS_STATS.SET_TABLE_STATS

3        Using the CARDINALITY hint

In Oracle 10g, dynamic sampling will work out-of-the-box, because the default setting has been increased from 1 to 2. At level 2, the optimizer will dynamically sample any unanalyzed object referenced in a query processed by the optimizer prior to evaluating the query plan. I can use an ALTER SESSION|SYSTEM command in Oracle9i Release 2 to make it behave the way Oracle 10g does by default, or I can use the dynamic sampling hint as follows:

select /*+ first_rows dynamic_sampling(gtt 2) */ *  from my_temp_table;

The following example adds rows to the GTT table and runs the dynamic sampling hint to have the optimizer sample the unanalyzed objects referenced in the query. Note the increased Card= values:

insert into gtt select rownum from all_objects;
32073 rows created.

set autotrace traceonly explain
select /*+ first_rows dynamic_sampling(gtt 2) */ *   from my_temp_table;

declare
   pragma autonomous_transaction;
begin
   dbms_stats.set_table_stats ( user, 'MY_TEMP_TABLE', numrows=> 12345 );
   commit;
end;
/

Note that a commit in this example could well clear out a global temporary table, undoing all of your work on it! That is why the AUTONOMOUS_TRANSACTION is really important here. The optimizer now believes that the table GTT has 12,345 rows in it and will use that fact whenever it optimizes a query that references that table.

The third solution is the CARDINALITY hint. I include this because it is the only option when using collection variables (not global temporary tables, but rather in-memory tables contained in a PL/SQL collection). The database will not dynamically sample these, nor are they real tables, so no statistics can be stored about them. The only way to communicate to the database the estimated size of this sort of object is to use this hint, as in the following:

select /*+ cardinality( gtt 999 ) */ *   from my_temp_table;

Execution Plan
-----------------------------------
SELECT STATEMENT Optimizer=CHOOSE
(Cost=11 Card=999 Bytes=12987)
  TABLE ACCESS (FULL) OF 'MY_TEMP_TABLE'
  (Cost=11 Card=999 Bytes=12987)

Here I explicitly told the optimizer how many rows it could expect to find in my global temporary table. Note that the CARDINALITY hint is available only in Oracle9i Release 1 and later releases.

Recompiling Invalid Schema Objects
The DBA_OBJECTS view can be used to identify invalid objects using the following query:
COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

DBMS_UTILITY.compile_schema
The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus:
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

UTL_RECOMP
The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads.  The following examples show how these procedures are used:

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

There are a number of restrictions associated with the use of this package including:

• Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
• The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
• The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
• Runnig DDL operations at the same time as this package may result in deadlocks.

utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:

• 0 - The level of parallelism is derived based on the CPU_COUNT parameter.
• 1 - The recompilation is run serially, one object at a time.
• N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.