
Summary of Changes in Oracle7, Release 7.3
This appendix provides an overview of the changes in the Oracle7 Server, Release 7.3. The topics included in this appendix are:
Administration Enhancements
This section contains the following topics:
Standby Database
Standby Database supports the capability of maintaining a duplicate, or standby, database of your primary, online production database at a remote site. Standby Database thus enables recovery from production site disasters.
The operational features of the standby database are as follows:
- The standby database is copied from the primary, or current, production database and is duplicated on physical hardware such as disk or CPU.
- The standby database should be used only as a disaster recovery system.
- The standby database is mounted, but not open, and is in constant recovery mode.
- As the primary database archives its redo logs, they are transferred to the remote site and applied to the standby database.
- In the event of catastrophic failure of the primary database, the standby database can be taken out of recovery mode and opened for online use.
Standby Database requires the use of the following new and/or changed SQL statements:
ALTER DATABASE CREATE STANDBY CONTROLFILE AS <filename>
ALTER DATABASE MOUNT STANDBY DATABASE[PARALLEL]
ALTER DATABASE RECOVER STANDBY DATABASE
ALTER DATABASE ACTIVATE STANDBY DATABASE
Standby Database requires the use of the following new SQLDBA statement:
RECOVER STANDBY DATABASE
Note: Standby Database will operate only on Oracle7, Release 7.3 or higher of the database.
For more information about Standby Database, see Oracle7 Server SQL Reference, the Oracle7 Server Administrator's Guide, and Oracle7 Parallel Server Concepts & Administration.
Resilvering Enhancement
Many operating systems provide mirrored disk support. A typical mirrored disk support system uses several disks that are maintained as identical copies of each other. The failure of any one disk is not catastrophic because the remaining disks continue to operate without any loss of data. The disks are kept in synchronization by performing duplicate writes.
However, it is possible that some of the writes may not be completed due, for example, to a system failure of some sort. All of the disks in the mirrored system will continue to operate properly but would not, in the case of incompleted writes, be exact mirror images of each other. The usual recourse to resolve this problem is to recopy the entire mirror from one disk, which is very expensive and time consuming. In parallel server configurations, where each node in a cluster is responsible for issuing multiple writes to the mirror, the death of any node in the cluster requires the resilvering of all mirrored disks in the cluster.
The Resilvering Enhancement eliminates the need for the complete recovery of a mirrored file when there are failures that could have left the mirror out of sync due to writes by Oracle. The Resilvering Enhancement also allows Oracle to use other files in a mirror to repair corrupted data that does not produce hardware-detectable errors.
Additional Information: Control of mirroring is port specific. For more information about mirroring and your specific platform, see your operating system-specific Oracle documentation.
Media Recovery Usability
The Media Recovery Usability enhancement provides you with fixed tables and views that contain the information regarding a media recovery. There are two new views:
V$RECOVERY_FILE_STATUS, contains one row for each file that is a potential candidate of the recover command that was issued. For example, if the recover command was RECOVER DATAFILE, then V$RECOVER_FILE_STATUS will contain one row for each datafile named in the command; if the recover command was RECOVER DATABASE, then V$RECOVER_FILE_STATUS will contain one row for each datafile that is online.
V$RECOVERY_STATUS contains
- information about the log that recovery needs next from the database administrator.
- information about why recovery rejected the last log name the user supplied and the status of the most recently processed log.
For more information about the Media Recovery Usability enhancement, see the Oracle7 Server Administrator's Guide.
Dynamic Initialization Parameters
Dynamic Initialization Parameters allows the modification of initialization parameters while an instance is running. Traditionally, the only way initialization parameters could be modified has been to change their values within the INIT.ORA parameter file, shut down the instance, and restart the instance using the modified parameter file.
Initialization parameters may now be specified dynamically using the ALTER SESSION SET and ALTER SYSTEM SET commands.
The ALTER SESSION command can be used to change the value of an initialization parameter for the duration of a session or until the next execution of the ALTER SESSION command. The required syntax is
ALTER SESSION SET <parameter name> = <value>
The ALTER SYSTEM command can be used to change the global value of an initialization parameter. New sessions will see the changed value of the initialization parameter. The required syntax is:
ALTER SYSTEM SET <parameter name> = <value>
The following are the dynamic initialization parameters available with Oracle7, Release 7.3:
For more information about Dynamic Initialization Parameters, see Oracle7 Server Reference and Oracle7 Server Tuning.
Fast Recreate Index
Fast Recreate Index allows users to create an index using an existing index as the data source. Essentially, this allows the user to change an index's storage characteristics, if desired.
The semantics of the CREATE index command remain unchanged
with the fast recreate index command. If a user wishes to create a
new index where the columns to be indexed are a subset of the columns of an existing index on the same table, the CREATE INDEX command can use the existing index to retrieve the rows of the index for fast operation.
Fast Recreate Index introduces the REBUILD option to the ALTER INDEX DML statement. The REBUILD option allows the user to recreate an existing index. In the process of recreation, the storage characteristics and tablespace where the index resides can be changed. Recreating an existing index also removes intrablock space fragmentation. The syntax of the extended ALTER INDEX command is
ALTER INDEX <indexname> REBUILD
[PARALLEL <integer> | NOPARALLEL]
[RECOVERABLE | UNRECOVERABLE]
[TABLESPACE <tablespace name>]
[<extent> specs]
All of the clauses after REBUILD are optional.
For more information about Fast Recreate Index, see the Oracle7 Server Administrator's Guide and Oracle7 Server Tuning.
Direct Path Export
Direct Path Export provides a fast path for the extraction of data from tables that significantly improves the overall performance of the Export Utility.
The Export Utility now provides two data paths for exporting table data.
The conventional path uses the SQL statement "SELECT* FROM table" to extract data from database tables. This path is used by all Oracle tools and applications. Data is transferred to the buffer cache; the EXPORT client then writes the data to the EXPORT dump file.
The direct path bypasses the buffer cache and transfers data to the user's private buffer cache. Contention with other users is thereby avoided.
Direct Path Export can be invoked
- from the command line using a PARAMETER file
- from the command line using the EXP command with the DIRECT parameter specified
For more information about Direct Path Export, see Oracle7 Server Utilities.
Space Management Enhancements
This section describes space management enhancements that are available with Release 7.3.
Unlimited Extents
In Release 7.2 and earlier releases of Oracle7, the number of extents that could be allocated to a single segment was limited by the database block size. The entire extent map had to fit within half of the segment header block. For a 2 Kb block, the maximum number of extents per segment was 121.
The constraint on the number of extents made a number of common database management operations more difficult than they would otherwise have been if no constraint existed. Unlimited Extents removes the constraint of an upper limit to the number of possible extents.
The following are changes in space management:
- MAXEXTENTS is no longer limited by the number of extents that fit into a single database block.
- A new keyword, UNLIMITED, is now supported as a valid value for MAXEXTENTS.
- Unlimited Extents generates incompatible disk data structures that must be corrected if migration operations are to be performed.
- Dictionary tables cannot be altered to have MAXEXTENTS greater than the allowed block maximum. In other words, dictionary tables cannot be converted to unlimited format.
- Rollback segments have to be offline to be converted to unlimited format from limited format and vice versa.
- Extents for rollback segments in unlimited formats are required to have, at least, 4 blocks.
- The default storage clause for newly created tablespaces is the same as it was in Release 7.2. Existing tablespaces and newly created tablespaces need to have their default storage clauses changed manually.
- Since Unlimited Extents allows unlimited extents, old Version 6 SQL scripts may, when run under Release 7.3, have MAXEXTENTS greater than the past allowed maximum. Offending values for existing tables are, therefore, all reset to the allowed Release 7.2 maximum the first time the system is started in Release 7.3 compatible mode.
For more information about Unlimited Extents, see the Oracle7 Server Administrator's Guide.
Tablespace Coalesce
Tablespace Coalesce improves space management by providing a new command, ALTER TABLESPACE <tablespace> COALESCE. The new command coalesces all available free space (extents) in the tablespace into larger, contiguous extents on a per file basis. A new catalog view, DBA_FREE_SPACE_COALESCED displays statistics pertaining to coalesceable extents for tablespaces.
The syntax for the ALTER TABLESPACE COALESCE command is
ALTER TABLESPACE <tablespace> COALESCE;
DBA_FREE_SPACE_COALESCED has the following columns:
Column
| Comments
|
TABLESPACE_NAME
| the name of the tablespace
|
TOTAL_EXTENTS
| the number of free extents
|
EXTENTS_COALESCED
| the number of free extents that are coalesced
|
PERCENT_EXTENTS_COALESCED
| the percentage of coalesced free extents
|
TOTAL_BYTES
| the total number of free bytes
|
BYTES_COALESCED
| the number of coalesced free bytes
|
TOTAL_BLOCKS
| the number of free Oracle blocks
|
BLOCKS_COALESCED
| the number of coalesced free Oracle blocks
|
PERCENT_BLOCKS_COALESCED
| the percentage of coalesced free Oracle blocks
|
Table D - 1. Columns in the DBA_FREE_SPACE_COALESCED View
Note: The lower the percentage of coalesced entries or blocks, the more fragmented the space and the greater the need to issue the ALTER TABLESPACE COALESCE command.
For more information about Tablespace Coalesce, see Oracle7 Server SQL Reference and the Oracle7 Server Administrator's Guide.
Deallocation of Unused Space
Deallocation of Unused Space provides the ability to release unused space from a segment and return it to the database system. The DBMS_SPACE package, available in Release 7.2, allowed users to compute that amount of unused space in a specific segment. Deallocation of Unused Space provides additional functionality by enabling users to actually release the unused space (or some portion of it).
An ALTER command, to release the unused space, is now provided for each user segment type, TABLE, INDEX, and CLUSTER.
The ALTER syntax for deallocating space from the table segment is
ALTER TABLE <tablename> DEALLOCATE UNUSED [KEEP (integer)]
The ALTER syntax for deallocating space from the index segment is
ALTER INDEX <indexname> DEALLOCATE UNUSED [KEEP (integer)]
The ALTER syntax for deallocating space from the cluster segment is
ALTER INDEX <clustername> DEALLOCATE UNUSED [KEEP (integer)]
For more information about Deallocation of Unused Space, see Oracle7 Server SQL Reference and the Oracle7 Server Administrator's Guide.
Sort Segment
Sort Segment improves concurrency of multiple sort operations. The enhanced performance is not entirely automatic and must be enabled by the user. The user must do the following:
- define temporary tablespaces
- monitor Sort Segment performance
Defining Temporary Tablespaces
A tablespace can be defined as temporary during creation, or it can be made temporary later. The CREATE TABLESPACE command is expanded to include the following options:
CREATE TABLESPACE <tablespace> TEMPORARY
CREATE TABLESPACE <tablespace> PERMANENT
Specifying TEMPORARY defines the tablespace as a temporary tablespace. All sorts in a temporary tablespace share a single Sort Segment and allocate space using the Sort Segment table. However, no permanent objects can be stored in the temporary tablespace.
Specifying PERMANENT allows the permanent objects to be stored in the tablespace. But if this tablespace is used for sorting, no caching is done, so sort performance may suffer.
The default setting is PERMANENT.
A setting for an existing tablespace can be altered as follows:
ALTER TABLESPACE <tablespace> TEMPORARY
ALTER TABLESPACE <tablespace> PERMANENT
Specifying TEMPORARY makes this tablespace a temporary tablespace. The tablespace may not contain any permanent objects; otherwise, an error is generated.
Specifying PERMANENT allows future creation of permanent objects.
A temporary status is reflected in the new CONTENTS column of the DBA_TABLESPACES view. The column shows a value of "PERMANENT" for permanent tablespaces and a value of "TEMPORARY" for temporary tablespaces.
Monitoring Sort Segment Performance
Sort Segment performance can be monitored using some of the new dynamic performance tables and some new values in the existing dynamic tablespaces.
Table
| Comments
|
V$LATCH
| The Sort Extent Pool latch is reflected in this table.
|
V$LATCHNAME
| The Sort Extent Pool latch is reflected in this table. The latch name is "sort extent pool".
|
V$SORT_SEGMENTS
| This is a new table. It contains information about every Sort Segment created in the given instance.
|
Table D - 2. Changed and New Tables for Sort Segment
For more information about Sort Segment, see Oracle7 Parallel Server Concepts & Administration, Oracle7 Server Tuning, and the Oracle7 Server Administrator's Guide.
Sort Direct Writes
Sort Direct Writes provides an automatic tuning method for deriving the size and number of direct write buffers based upon the sort area size. The memory for the buffers is taken from the sort area, so only one tuning parameter is necessary. In addition, an optimizer cost model is provided.
Performance Benefits of Sort Direct Writes
You can set the initialization parameter SORT_DIRECT_WRITES to increase sort performance if memory and temporary space are abundant on your system and you perform many large sorts to disk.
For Release 7.3 and greater, the default value of SORT_DIRECT_WRITES is AUTO. If the initialization parameter is unspecified or set to AUTO, the database automatically allocates direct write buffers if the SORT_AREA_SIZE is ten times the minimum direct write buffer configuration.
Performance Tradeoffs of Sort Direct Writes
Sort Direct Writes causes each Oracle process that sorts to allocate
(SORT_WRITE_BUFFERS)*(SORT_WRITE_BUFFER_SIZE)
bytes of memory in addition to the memory already allocated for the sort area. You must ensure that your operating system has enough free memory available to accommodate the increased allocation.
Sorts that use direct writes tend to consume more temporary segment space on disk. A good rule of thumb is that the total memory allocated for direct write buffers should be less than one-tenth of the memory allocated for the sort area. If the minimum configuration of the direct write buffers is greater than one-tenth of your sort area, you should not trade sort area for direct write buffers.
Warning: Using the default SORT_DIRECT_WRITES mode of AUTO causes the database to use the one-tenth rule to decide whether to use direct writes and it allocates the direct write buffers out of a portion of the total sort area, ignoring the settings for SORT_DIRECT_WRITE_BUFFERS and SORT_WRITE_BUFFER_SIZE.
Initialization Parameter Files for Sort Direct Writes
The following is a list of the initialization parameters that are used for Sort Direct Writes:
SORT_DIRECT_WRITES
Default value for Release 7.2: FALSE
Default value for Release 7.3: AUTO
SORT_WRITE_BUFFER_SIZE
Default value: O/Dependent
Range of values: 32 kilobytes to 64 kilobytes
SORT_WRITE_BUFFERS
Default value: O/S dependent
Range of values: 2 to 8
Compatibility and Migration of Sort Direct Writes
If you upgrade to Release 7.3, SORT_DIRECT_WRITES is initially set in AUTO mode by default. Because the direct writes use large buffers (typically 32 kilobytes to 64 kilobytes), the space map function in the sort splits extents into buffer-sized chunks in order to exploit large multiblock writes. The non-direct write case uses only 4 kilobytes. This change in space allocation may result in a 10% to 15% increase in temporary space usage.
For more information about Sort Direct Writes, see Oracle7 Server Reference, the Oracle7 Server Administrator's Guide, and Oracle7 Server Tuning.
Query Execution Enhancements
This section contains the following topics:
Hash Join
Previous releases and versions of Oracle have employed two join algorithms: Nested loops and Sort-Merge. Hash Join improves the performance of join operations, especially in decision support applications. The performance improvement is applicable to both serial queries and parallel queries.
Three, new initialization parameters are available with Release 7.2.2 that must be used with Hash Join. The three initialization parameters are session parameters, that is, their values may be altered using the ALTER SESSION command.
Initialization Parameter
| Use
|
HASH_JOIN_ENABLED
| A boolean operator. If using hash joins produces less than ideal results, you can turn it off by setting it to FALSE. The default value is TRUE.
|
HASH_AREA_SIZE
| Specifies the maximum amount of memory, in bytes, to be used for the hash join. If not specified, hash join uses twice the SORT_AREA_SIZE value.
|
HASH_MULTIBLOCK_IO_COUNT
| Determines how many blocks hash join should read and write at once. If not specified, hash join uses the value for DB_FILE_MULTIBLOCK_
READ_COUNT.
|
Table D - 3. Initialization Parameters used with Hash Join
USE_HASH is a "hint" that increases the probability of the optimizer selecting hash join as the optimal method for joining each specified table with another row source. The syntax is
USE_HASH (table ...)
where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join.
For more information about Hash Join, see Oracle7 Server Tuning and Oracle7 Server SQL Reference.
Histograms
Histograms enables Oracle's cost based optimizer to generate better query evaluation plans for Oracle applications, end user applications, and ad hoc queries.
One of the fundamental capabilities of any cost-based optimizer (CBO) is the ability to determine the selectivity of predicates that appear in queries. Oracle's CBO, in releases earlier than 7.3, provided support for accurate selectivity estimates under the assumption that the attribute domains, in other words, a table's columns, were uniformly distributed. However, most attribute domains are not uniformly distributed. Histograms enables the CBO to describe the distributions of non-uniform domains by utilizing height balanced histograms on specified attributes.
Histograms are useful only when they reflect the current data distribution of a given column. If the data distribution is not static, the histogram should be updated frequently. The data need not be static as long as the distribution remains constant. Histograms are expensive and should be used only when they substantially improve query plans. Histograms are not useful for columns with the following characteristics:
- All predicates on the column use bind variables.
- The column data is uniformly distributed.
- The column is not used in WHERE clauses of queries.
- The column is unique and is used only with equality predicates.
Histograms has required modification of
- the data dictionaries to store the histograms efficiently,
- the cost-based optimizer to utilize the histogram effectively, and
Histograms can be viewed using the following views:
The ANALYZE command and the cost-based optimizer will not work unless the proper upgrade and downgrade procedures are followed. The following upgrade and downgrade scripts must run:
Script
| Use
|
CAT7301.SQL
| upgrade from release 7.2 to release 7.3
|
CAT7301D.SQL
| downgrade from release 7.3 to release 7.2
|
Table D - 4. Upgrade and Downgrade Scripts
For more information about Histograms, see Oracle7 Server Tuning.
Updatable Join Views
Updatable Join Views provides support for inserts, updates, and deletes on unambiguous join views.
Basic Concepts
The following three definitions are basic to the use of Updatable Join Views:
Join View
| A join view is a view with more than one table (or view) in its FROM clause and with none of the following constructs used in it: DISTINCT, AGGREGATION, GROUP_BY, START_WITH, CONNECT_BY, and set operations such as UNION, UNION ALL, MINUS, and INTERSECT.
|
Join Column
| A join column is any column of a table in the FROM clause that is used in a WHERE clause expression that has columns from some other table in the FROM clause.
|
Key Preserved Table
| A table is said to have its keys preserved through a join if every key of the table is also a key of the result of the join. Such a table is called a key preserved table (with respect to the join).
|
Rules for Insert, Update, and Delete on Join Views
The following rules apply to insert, update, and delete operations on join views:
General
| Any insert, update, or delete statement on a join view can modify only one underlying base table at a time.
|
Insert
| An insert may not, explicitly or implicitly, refer to the columns of a non-key preserved table. If the join is defined with the WITH CHECK OPTION, then it may not be inserted into.
|
Update
| All updatable columns of a join view must map to columns of a key preserved table. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are non-updatable.
|
Delete
| Rows from a join view can be deleted provided there is exactly one table in the join whose keys are being preserved. If the view is defined with the WITH CHECK OPTION and the key preserved table is repeated, then the rows cannot be deleted from the view.
|
Table D - 5. Insert, Update, and Delete Rules
New Catalog Views
A new family of catalog views, UPDATABLE_COLUMNS is provided with Release 7.3. The new views are {USER|ALL|DBA}_UPDATABLE_COLUMNS, each of which has the following columns:
Column Name
| Comments
|
OWNER
| Owner of this table or view
|
TABLE_NAME
| Name of this table or view
|
COLUMN_NAME
| Name of the column in the table or view
|
UPDATABLE
| Is the column updatable? YES or NO
|
Table D - 6. New Catalog Views to Support Updatable Join Views
For more information about Updatable Join Views, see the Oracle7 Server Administrator's Guide, Oracle7 Server Concepts, and the Oracle7 Server Application Developer's Guide.
Changes to Data Dictionary Tables
The following table shows the changes in data dictionary tables that have been made to support Compiled Triggers:
Table
| Changes
|
ERROR$
| now contains errors generated while compiling triggers
|
OBJ$
| now keeps the appropriate status of a trigger object
|
IDL$
| now contains pcode and debug code for trigger objects
|
DEPENDENCY$
| now keeps dependencies for trigger objects
|
Table D - 7. Data Dictionary Table Changes for Compiled Triggers
For more information about Compiled Triggers, see Oracle7 Server SQL Reference, Oracle7 Parallel Server Concepts & Administration, and the Oracle7 Server Application Developer's Guide.
Sort Big Keys
Sort Big Keys removes query restrictions that existed in Release 7.2. The following restrictions have been removed in Release 7.3:
- There are no longer any restrictions on the size of SELECT list items or ORDER BY keys that previously existed in Release 7.2.
- Size restrictions are eliminated for all DISTINCT operations and set operators such as MINUS.
- For cases of aggregate operations, the total size of all DISTINCT and non-DISTINCT aggregate workspaces may not exceed
32 kilobytes (this is a cursor memory limit.)
- There is no limit on the combined size of the GROUP BY key and any individual DISTINCT aggregate. However, the total size of the GROUP BY expression and the sum of the sizes of the non-DISTINCT aggregates may not exceed a single sort block, which is a single database block minus some overhead.
- The MIN/MAX operations on VARCHAR expressions that exceed 255 bytes are converted to MIN/MAX DISTINCT.
For more information about Sort Big Keys, see the Oracle7 Server Administrator's Guide, Oracle7 Server SQL Reference, and Oracle7 Server Tuning.
Scalability and Performance Enhancements
This section contains the following topics:
Remote Dependencies in a PL/SQL Environment
Remote Dependencies in a PL/SQL Environment provides the following enhancements:
An extra level of flexibility in the model for managing remote dependencies: Prior to Release 7.3, the model for managing remote dependencies between stored procedures was based on timestamps. With the new model for managing remote dependencies in Release 7.3, you now have a way to control the management of remote dependencies. Table D - 8 summarizes these new modes of control. Improved performance - avoiding unnecessary recompilations: With the SIGNATURE MODE (see Table D - 8), compatible changes to a referenced unit no longer cause the invalidation of those dependent units that are remote, but will continue to cause the invalidation of local dependent units. Unnecessary recompilations of dependent units across the network are thus prevented, which improves performance. Improved performance - smaller library units: The size of library units has decreased. Package bodies are substantially smaller. Package specifications, especially those containing subprograms with a large number of parameters, are also smaller. Ability to allow client-side tools, such as Oracle Forms and Oracle Procedure Builder, to upgrade to PL/SQL, Version 2: Client-side tools, such as Oracle Forms and Oracle Procedure Builder are built with PL/SQL Version 1 on the client-side. PL/SQL Version 1 does not inherently support a dependency management model to track dependencies from client-side PL/SQL library units to server-side PL/SQL library units.
While PL/SQL Releases 2.0 through 2.2 do have a strong dependency management model to track such dependencies, the model is too restrictive: client-side applications built with Oracle Forms or Oracle Procedure Builder cannot be installed at a user's site without requiring a recompilation of the client-side PL/SQL library units immediately upon installation. The new Remote Dependencies feature, with the SIGNATURE mode, has relaxed some of these restrictions, allowing client-side installations to proceed without requiring a recompilation of library units on the client-side.
REMOTE_
DEPENDENCIES_MODE Parameter
All library units in Oracle7, Release 7.2 (and earlier) databases have timestamps associated with them. Prior to Release 7.3, timestamps were used to control dependencies between procedures across the network. With Release 7.3, all library units continue to have timestamps associated with them. However, timestamp mismatches are now ignored if the user requests that invalidation be based on signatures by using the REMOTE_DEPENDENCIES_MODE parameter, which is new with Release 7.3.
The REMOTE_DEPENDENCIES_MODE parameter is relevant only during RPC calls and is applicable on the remote end of the RPC call. The REMOTE_DEPENDENCIES_MODE parameter can be set to have the following values:
Value
| Comments
|
TIMESTAMP
| invalidation of remote dependents will happen based on mismatch of timestamps
|
SIGNATURE
| invalidation of remote dependents will happen based on mismatch of signatures
|
Table D - 8. Values for the REMOTE_DEPENDENCIES_MODE Parameter
The REMOTE_DEPENDENCIES_MODE parameter can be set in any of the following ways:
- as an initialization parameter in the INIT.ORA file. The syntax is
REMOTE_DEPENDENCIES_MODE=<value>
- at the system level using the ALTER SYSTEM command. The syntax is
ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE=<value>
- at the session level using the ALTER SESSION command. The syntax is
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE=<value>
where value can be either TIMESTAMP or SIGNATURE.
For more information about Remote Dependencies in a PL/SQL Environment,, see the Oracle7 Server Application Developer's Guide, Oracle7 Server SQL Reference, and the PL/SQL User's Guide and Reference.
Fast Transaction Rollback and XA Recovery Enhancements
The Fast Transaction Rollback and XA Recovery enhancements provide the following new functionality:
- The time required for transaction recovery is reduced by recovering multiple transactions in parallel.
- The remaining parts of the database, in other words, data that is not locked by the transactions requiring recovery, are now available very quickly.
- Correct behavior for XA recovery operations is now provided.
Fast Transaction Rollback now allows the database to be opened for connections as soon as cache recovery is completed.
XA Recovery Enhancements
There are two enhancements to XA recovery in Release 7.3:
- An option is added to make the XA_RECOVER call wait for instance recovery.
- The XA Info string has a new clause called OPS_FAILOVER. If OPS_FAILOVER is set to T or t for a given XA resource manager connection, any XA_RECOVER call issued from that connection will wait for instance recovery to compete. The syntax of OPS_FAILOVER is
OPS_FAILOVER=T or OPS_FAILOVER=t.
The default value for OPS_FAILOVER is
OPS_FAILOVER=F or FALSE
When OPS_FAILOVER is set to TRUE, the XA_RECOVER call waits until SMON has finished cache recovery, has identified the in-doubt transactions, and added them to the PENDING_TRAN$ table that contains a list of in-doubt transactions.
For more information about Fast Transaction Rollback and XA Recovery, see the Oracle7 Server Application Developer's Guide and Oracle7 Server Distributed Systems, Volume I.
LRU Latch Scalability
LRU Latch Scalability provides LRU scalability with large SMP machines. The major benefits are
- low LRU contention under large SMP configurations
- the elimination of the need for non-preemptive operating system scheduling under heavy loads
Changes required by LRU Latch Scalability are the following:
- A new initialization parameter, DB_BLOCK_LRU_LATCHES, configures the buffer cache. DB_BLOCK_LRU_LATCHES specifies an advisory upper bound value for the desired number of sets.
- The number of sets used by the instance as a new field is now exported in the V$PARAMETER view. Note that the number of sets displayed is the number of sets used by the system and may not be the same as the value requested by the DB_BLOCK_LRU_LATCHES parameter.
For more information about LRU Latch Scalability, see Oracle7 Server Tuning and Oracle7 Parallel Server Concepts & Administration.
Serializable Transaction Isolation
Serializable Transaction Isolation allows application developers to employ a more flexible tool when designing application transactions that must have a consistent view of their data throughout the duration of those transactions.
The capability of designing application transactions that must have a consistent view of their data throughout the duration of those transactions is already possible for query-only application transactions using the existing SET TRANSACTION READ ONLY command. The new isolation level provided by Serializable Transaction Isolation preserves the transaction-consistent view of data that is provided by SET TRANSACTION READ ONLY. Serializable Transaction Isolation also allows transactions that use it to execute DML statements and allows such transactions to see their own changes while shielding them from visibility of other transactions' changes either in-flight or committed.
There are modifications to the SET TRANSACTION and ALTER SESSION commands.
The SQL command syntax for the SET TRANSACTION command is extended as follows:
SET TRANSACTION ISOLATION_LEVEL SERIALIZABLE
or
SET TRANSACTION ISOLATION_LEVEL READ COMMITTED
The SQL command syntax for the ALTER SESSION command is extended as follows:
ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE
or
ALTER SESSION SET ISOLATION_LEVEL=READ COMMITTED
For more information about Serializable Transaction Isolation, the SET TRANSACTION command, and the ALTER SESSION command, see the Oracle7 Server Administrator's Guide and Oracle7 Server SQL Reference.
Parallel Server Enhancements
This section contains the following topics:
Fine Grained Locking
Fine Grained Locking provides a more efficient method for locking in a multinode configuration.
Fine Grained Locking provides the following capabilities:
- Parallel server locking configurations are now allowed that can reduce the number of database blocks protected by a single lock, allowing a one-to-one mapping from lock to data block, if desired.
- Configurations of locks that require more locks than can reasonably be held by the instance at any one time are now allowed.
- Instances are now allowed to release locks that are not necessary to protect data currently in the buffer cache.
Warning: Fine Grained Locking allows the database administrator to configure PCM locks by specifying a set of releasable DBA and hashed locks to protect the blocks in data files. Since releasable locks are expensive (they are acquired and released for each use) certain benchmarks may show a decreased level of performance when run in this mode. However, other types of access to the database will improve with releasable DBA locks. The database administrator should try to configure the locks to match this type of use for each data file.
The parameters that may be used with Fine Grained Locking are summarized in the following table.
Parameter
| Use
|
GC_DB_LOCKS
| specifies the total number of locks that protect data blocks
|
GC_FILES_TO_LOCKS
| gives the mapping of hashed locks to blocks within each datafile
|
GC_DEFAULT_LOCKS
| specifies the translation to use for files that are not mentioned in GC_FILES_TO_LOCKS
|
GC_ROLLBACK_LOCKS
| specifies the number of hashed locks for each UNDO segment
|
GC_SAVE_ROLLBACK_LOCKS
| similar to GC_ROLLBACK_LOCKS
|
GC_SEGMENTS
| specifies the number of hashed locks to create for the segment header class of blocks
|
GC_TABLESPACES
| specifies the number of hashed locks for save-undo segment headers
|
GC_ROLLBACK_SEGMENTS
| specifies the number of hashed locks for undo segment headers (transaction tables)
|
Table D - 9. Parameters for the Fine Grained Locking
For more information about Fine Grained Locking, see Oracle7 Parallel Server Concepts & Administration.
Additional Information: Fine Grained Locking is not available on all platforms. For information regarding the use of Fine Grained Locking on your platform, see your operating system-specific Oracle documentation.
Instance Registration
Instance Registration provides a simple, straightforward way to retrieve and store information about multiple instances of a database running the Oracle Parallel Server. Previously, there was no generic mechanism in the Oracle Parallel Server that permitted multiple instances to retrieve information about each other.
Parallel query will probably use Instance Registration the most. When servers are spawned to parallelize an operation, information is needed about remote instances to determine how many users to start and on which instances to start them. Using Instance Registration, parallel query is now able to determine the address of the servers and which instances are most powerful and least loaded.
Instance Registration now provides a generic interface that retrieves and stores information to be used for the following purposes:
- cross instance communication
- detecting the existence and availability of remote servers participating in parallel operations
- load balancing information for parallel operations
- local instance number assignment and identification
For more information about Instance Registration, see Oracle7 Parallel Server Concepts & Administration.
Delayed-Logging Block Clean Out
Delayed-Logging Block Cleanout provides the following Block Cleanout improvements:
- The current block is not dirtied.
- No redo logs are generated when the current block is cleaned out during reads.
- Block cleanout during current reads is faster and less costly because the block is changed directly and no redo log is generated.
- For the Parallel Server, Delayed-Logging Block Cleanout does not cause data or index block pings during current reads because the blocks are not dirtied.
- Delayed-Logging Block Cleanout introduces a No-IO Delayed Block Cleanout to be done by DBWR when writing DIRTY, but not yet cleanout, blocks to disk.
The basic idea of Delayed-Logging Block Cleanout is to not regenerate redo records when cleaning out the current block during reads. The block is cleaned out in the usual way, but a new no-logging flag is set for the block and for every ITL that has been cleaned out. The block is marked CLEANOUT (a new state for DBWR) but not DIRTY, and no redo record is generated at this time.
Note: Delayed-Logging Block Cleanout improves system performance, especially when running OPS.
For more information about Delayed-Logging Block Cleanout, see Oracle7 Parallel Server Concepts & Administration.
Parallel Query Affinity
Parallel Query Affinity provides the following new functionality:
- a new mechanism for slave allocation that takes into account a balance between disk transfer rate and CPU processing rate for user's queries
- the physical proximity of data is taken into account when assigning work to query slaves on machines that have preferred access to local disks
The following initialization parameters are now obsolete:
- PARALLEL_DEFAULT_SCANSIZE
- PARALLEL_DEFAULT_MAX_SCANS
A new initialization parameter replaces the two obsolete parameters. The new initialization parameter, PARALLEL_MIN_PERCENT, allows the user to specify the minimum fraction of parallel query slaves desired. The user can specify an integer number in the range of
0 to 100 for PARALLEL_MIN_PERCENT.
For more information about Parallel Query Affinity, see Oracle7 Server Tuning.
Load Balancing in Listener
Prior to Release 7.3, no coherent mechanism was available to manage the load of numerous instances that constituted an Oracle Parallel Server (OPS). Load Balancing in Listener now provides load balancing in the SQL*Net network listener among multiple instances.
The user is now provided with the following functionality:
- a GUI-based Network Manager tool that permits multiple users per database instance.
- a new initialization parameter, MTS_LISTENER_ADDRESS, that sets the configuration for each port to which that database will connect.
GUI Network Manager Tool
Oracle Network Manager is a graphical tool that is used for configuring and maintaining a SQL*Net network, including the Listener.
MTS_LISTENER_
ADDRESS Parameter
The MTS_LISTENER_ADDRESS parameter sets the configuration for each port to which the database will connect; it's syntax is
MTS_LISTENER_ADDRESS=<addr>
where addr is an address to which the listener will listen for connection requests for a specific protocol.
For more information about Load Balancing in Listener, see Oracle7 Parallel Server Concepts & Administration.
Serviceability Enhancements
This section contains the following topics:
DB_VERIFY
DB_VERIFY, which is available with Release 7.3, is an external command-line utility that performs physical data structure integrity checks on offline databases. Checking is limited, as follows:
- Only physical data structure integrity is verified.
- Only transaction managed blocks are checked.
The benefits of performing an offline check are
- One can ensure that a backup database (or datafile) is valid before being restored.
- An existing database can be checked without being brought online by using an external utility.
- Significant performance improvement can be achieved by using an external tool as compared with the online verification capabilities of pre-7.3 releases.
- The time required for verification is comparable to the time required for copying the file.
- Verification provides informative reports that describe the nature of the problem.
DB_VERIFY provides the following functionality:
- Verifications can be performed on either a file or a piece of a file.
- Verifications perform physical data integrity checking of all cache managed blocks that support such checking.
- Verifications perform minimal logical data structure integrity checking of all cache managed blocks; such checking is limited in scope and does not rely upon the data dictionary.
Note: If corruption is detected, the statistical information provided by DB_VERIFY should be communicated to Oracle World Wide Support for further analysis.
Additional Information: The name and location of DB_VERIFY is dependent on your operating system. See your operating system-specific Oracle documentation for the location of DB_VERIFY for your system.
For more information about DB_VERIFY, see the Oracle7 Server Administrator's Guide and Oracle7 Server Utilities.
Transaction Trace Facility
The Transaction Trace Facility enhancement provides database users, database administrators, and application developers with information about
- the status of a transaction (active, in doubt, and so forth)
- the session to which a transaction belongs
- the parent of a transaction
- the type of the transaction (initiated, recursive, and so forth)
- the starting time of the transaction
- the starting location of the transaction
- the amount of UNDO information the transaction has generated
- the number of data blocks the transaction has updated
- the objects the transaction has updated
- the number of CR changes the transaction has made
The following dynamic performance tables have been changed to support the Transaction Trace Facility:
For more information about the Transaction Trace Facility and the modifications to the dynamic performance tables, see Oracle7 Server Reference.
Tuning Enhancements
This section contains the following topics:
EXPLAIN PLAN changes
The enhancement to EXPLAIN PLAN improves the readability and usefulness of EXPLAIN PLAN output.
A new CHAR column, OTHER_TAG, which describes the function of the SQL text in the OTHER column, has been added to the EXPLAIN PLAN table for Release 7.3. The values for OTHER_TAG are
SERIAL
| The SQL is the text of a locally executed, serial query plan.
|
SERIAL_FROM_REMOTE
| The SQL shown will be executed at a remote site.
|
PARALLEL_COMBINED_
WITH_PARENT
| The parent of this operation is a DFO that performs both operations in the parallel execution plan.
|
PARALLEL_COMBINED_
WITH_CHILD
| The child of this operation is a DFO that performs both operations in the parallel execution plan.
|
PARALLEL_TO_SERIAL
| The SQL is the top level of the parallel plan.
|
PARALLEL_TO_PARALLEL
| The SQL is executed and outputs it in parallel.
|
PARALLEL_FROM_SERIAL
| This operation consumes data from a serial operation and outputs it in parallel.
|
Several new columns have been added for the OPTIMIZER:
For more information about the changes to EXPLAIN PLAN, see Oracle7 Server Tuning.
Oracle TRACE(tm)
Oracle TRACE(tm), often referred to simply as TRACE(tm), is a software product that collects performance data for any application--most notably, transaction processing and database applications. It monitors performance by gathering and reporting event-based data from layered products and application programs that contain calls to TRACE(tm) routines. TRACE(tm) is designed to operate with minimal performance impact on the system and can be used in both development and production environments.
TRACE(tm) differs from other collector software in that it is event based, whereas most other collectors are timer based. Timer-based collectors gather data at specified time intervals, at random places within your code. An event-based collector gathers data at predefined locations in your program code when that code is executed.
The advantage of event-based collectors is that you can determine the actual frequency of the execution of events, rather than an average or estimated frequency. Also, event-based collectors give you the ability to collect and report on the resources used by specific events in an application.
TRACE(tm) users include application developers, application performance analysts, database administrators, system managers, and capacity planners. They use TRACE(tm) to assist them in pinpointing the reasons for an application's poor performance. General reasons for poor performance can be any of the following:
- poorly or incorrectly designed databases
- not enough servers to handle user requests
- inefficient database queries
- actual use of the application differs from the intended use
- inadequate hardware resources
Finding specific causes for these general problems requires data about the application's resource use and response time. TRACE(tm) collects a variety of such data from all layers of an application--the user interface, the processing engine, and the database. TRACE(tm) is unique in that it can collect information from each of these layers, transcending the proprietary and industry standard application programming interfaces (APIs). Each layer that logs TRACE(tm) information can be tied to the layer above it, which allows you to track a transaction throughout its lifetime.
For more information about Oracle TRACE(tm) parameters, see Oracle7 Server Tuning and Oracle7 Server Reference.
Antijoins
An antijoin is a form of join with reverse logic; instead of returning rows when there is a match, (according to the join predicate), between the left and right side, an antijoin returns those rows from the left side for which there is no match on the right. The behavior of an antijoin is exactly that of a NOT EXISTS subquery with the right side of the antijoin corresponding to the subquery.
Release 7.3 introduces Antijoin. The following list summarizes the new functionality and important facts about Antijoin:
- The hash and sort-merge antijoins are introduced as alternative evaluation techniques available for NOT IN subqueries.
- Hash and sort-merge antijoins can only be invoked explicitly through hints or initialization parameters in the INIT.ORA file.
- The new antijoins are parallelizable.
Restrictions on the Use of Antijoin Methods
Release 7.3 can use hash and sort-merge antijoins to evaluate NOT IN subqueries provided that certain conditions are met. Assume that the subquery predicate is of the form
(cola1, cola2, ,colan) NOT IN
(SELECT colb1, colb2, ,colbn FROM ...)
The following conditions must hold in order for the subquery to be transformed into a hash or sort-merge antijoin:
- All of cola1...colan must be simple references to columns; colb1...colbn must be either simple references to columns or aggregate functions (MIN, MAX, SUM, COUNT, or AVG) applied directly to a simple column provided that the subquery contains a GROUP BY. No other expressions are allowed.
- All of cola1...colan and colb1...colbn must not be NULL. Currently, this is checked for each column by either the presence of a NOT NULL constraint or a NOT NULL predicate on the columns at the topmost logical level of the appropriate WHERE clause ANDed with any other predicates in that Where clause. That is, NOT NULL predicates should be in the WHERE clause of the subquery for columns referred to in colb1...colbn, and in the WHERE clause of the surrounding query for columns cola1...colan.
- The subquery must not have any correlation predicates, in other words, predicates that reference anything in surrounding query blocks.
- The WHERE clause of the surrounding query must not have ORs at the topmost logical level. That is, the subquery must not be part of a logical expression that is ORed with some other logical expression.
- Antijoins can be used only with the cost based optimizer.
How to Invoke Antijoin Methods
If invoked by a hint, the hint is put in the NOT IN subquery and must be either of the following antijoins:
- MERGE_AJ for sort merge antijoins
- HASH_AJ for hash antijoins
The antijoin transformation can also be invoked based on the setting of a new initialization parameter, ALWAYS_ANTI_JOIN. If the parameter ALWAYS_ANTI_JOIN is set to either MERGE or HASH, the transformation to the corresponding antijoin type takes place wherever it is legal.
If the antijoin transformation takes place, the antijoin appears as a join in the explain plan output with the word "ANTI" in the options column of the PLAN_TABLE. The right side of the antijoin appears as a view in the query plan.
For more information about Antijoins, see Oracle7 Server Tuning.
Advanced Replication Enhancements
This section contains the following topics:
Object Groups
Release 7.3 introduces the idea of an object group which replaces the schema as the logical unit of distribution in Oracle's Advanced Replication feature.
An object group is a set of replicated objects. The replicated objects may reside in one or more schemas, but any replicated object can belong in, at most, one object group. Instead of replicating schemas, users now replicate object groups. Release 7.3 identifies the objects and schemas that need to be replicated. Object groups provide the following benefits:
- Replication administration is simplified by letting users define configurations and execute replication commands at a higher level than was previously possible.
- Instead of replicating individual objects, users are now able to group objects that participate in an application function and replicate them as a group.
Modifications to RepCat Tables
The following table lists the RepCat tables used by Advanced Replication and indicates which tables were changed in Release 7.3.
RepCat Table
| Comments
|
REPCAT$ REPCAT
| no change
|
REPCAT$ REPOBJECT
| a new column has been added: GNAME. Its type is VARCHAR2(30)
|
REPCAT$ REPPROP
| no change
|
REPCAT$ REPSCHEMA
| no change
|
REPCAT$ DDL
| no change
|
REPCAT$ GENERATED
| no change
|
REPCAT$ REPCATLOG
| a new column has been added: GNAME. Its type is VARCHAR2(30)
|
Table D - 10. Advanced Replication Table Changes
Modifications to RepCat Views
Views associated with the modified table now include the GNAME column, as shown in the following table:
RepCat View
| Comments
|
REPCAT_REPCAT
| no change; gname=sname
|
USER_REPCAT
|
|
ALL_REPCAT
|
|
DBA_REPCAT
|
|
REPCAT_REPOBJECT
| add gname column; gname=NVL(gname,sname)
|
USER_REPOBJECT
|
|
ALL_REPOBJECT
|
|
DBA_REPOBJECT
|
|
REPCAT_REPSCHEMA
| no change; gname=sname
|
USER_REPSCHEMA
|
|
ALL_REPSCHEMA
|
|
DBA_REPSCHEMA
|
|
REPCAT_REPPROP
| no change
|
USER_REPPROP
|
|
ALL_REPPROP
|
|
DBA_REPPROP
|
|
REPCAT_REPCATLOG
| add gname column; gname=NVL(gname, sname)
|
USER_REPCATLOG
|
|
ALL_REPCATLOG
|
|
DBA_REPCATLOG
|
|
Table D - 11. Advance Replication View Changes
Changes to RepCat API Procedures
In Release 7.3, the PL/SQL procedures used to create, maintain, and drop repschemas are modified to operate on object groups. There are new Release 7.3 _REPGROUP() procedures that have been added to replace Release 7.2 _REPSCHEMA() procedures. The following procedures, which are new in Release 7.3, check database compatibility:
In Release 7.3, The PL/SQL procedures used to create, maintain, and drop repschemas are modified to operate on object groups. The following is a list of Release 7.2 procedures that have been converted to operate on object group names (GNAME) instead of replication schema names (sname).
- REMOVE_MASTER_DATABASE (both)
For more information about Object Groups, see Oracle7 Server Distributed Systems, Volume II.
Synchronous Propagation
Release 7.3 introduces synchronous propagation of transactions. Every delete, update, or insert on a replicated table triggers a synchronous RPC to each remote, synchronous site. Synchronous propagation utilizes two-phase commit for distributed transactions. In addition to Release 7.2 configurations, in which the method of propagation was globally asynchronous, users can now create configurations with global synchronous communication or mix propagation methods.
New Procedures
Release 7.3 introduces three new procedures that ensure that the method of propagation between any two master sites is symmetric. The three procedures are:
- ALTER_DATABASE_PROPAGATION
ALTER_OBJECT_PROPAGATION
ALTER_OBJECT_PROPAGATION (SNAME IN VARCHAR2,
ONAME IN VARCHAR2,
TYPE IN VARCHAR2,
HOW IN VARCHAR2,
DEST_BLINK IN VARCHAR2, := `',
SOURCE_DBLINK IN VARCHAR2 := `')
ALTER_OBJECT_PROPAGATION alters the propagation method for an object between two sites. If SOURCE_DBLINK is NULL, the local database is assumed to be the source site. If both SOURCE_DBLINK and DEST_DBLINK are NULL, all sites in the object's replication environment are altered. This procedure must be executed from the master definition site if SOURCE_DBLINK is not the local database.
Exceptions:
ALTER_GROUP_PROPAGATION
ALTER_GROUP_PROPAGATION (gname IN VARCHAR2,
HOW IN VARCHAR2,
DEST_DBLINK IN VARCHAR2 := `',
SOURCE_DBLINK IN VARCHAR2 := `')
ALTER_GROUP_PROPAGATION alters the propagation method for all replicated objects in an object group between two sites. If SOURCE_DBLINK is NULL, the local database is assumed to be the source site. If both SOURCE_DBLINK and DEST_DBLINK are NULL, all sites in the group's replication environment are altered. This procedure must be executed from the master definition site if SOURCE_DBLINK is not the local database.
Exceptions:
ALTER_DATABASE_PROPAGATION
ALTER_DATABASE_PROPAGATION (HOW IN VARCHAR2,
DEST_DBLINK IN VARCHAR2 := `',
SOURCE_DBLINK IN VARCHAR2 := `')
ALTER_DATABASE_PROPAGATION alters the propagation method for all replicated objects between two sites. If SOURCE_DBLINK is NULL, the local database is assumed to be the source site. If both SOURCE_DBLINK and DEST_DBLINK are NULL, all sites in the replication environment are altered. This procedure must be executed from the master definition site if SOURCE_DBLINK is not the local database. Exceptions are the following:
For more information about Synchronous Propagation, see Oracle7 Server Distributed Systems, Volume II.
Replicated Table Comparison
Table Comparison of the Advanced Replication option enables you to determine, in a running system, if two replicated tables are either the same or different and, if different, the nature of the difference. Specifically, Table Comparison does the following:
- The differences between tables is reported precisely. The exact rows that are different are reported.
- The result of rectifying differences between tables is that each compared table must be exactly equivalent to the reference table. Exact equivalence means that the table at each site has the same shape and the same content. The shape of a table refers to the number of columns, their column names, and the column data types. The content of a table refers to the number of rows and the actual values for each column on a row.
Table Comparison uses set difference to determine which rows are different. You can improve the performance of table comparison by setting the following initialization parameters in the INIT.ORA file:
Initialization Parameter Setting
| Comments
|
SORT_DIRECT_WRITES=TRUE
| If set to TRUE, each sort allocates additional buffers in memory for direct writes.
|
SORT_WRITE_BUFFERS
| Specifies the number of buffers.
|
SORT_WRITE_BUFFER_SIZE
| Specifies the size of the buffers.
|
SORT_AREA_SIZE=1000000
| The system default is 64000. This increases the size of the area where rows are sorted and will improve performance dramatically.
|
Table D - 12. Initialization Parameter Settings for Table Comparison
For more information about Table Comparison, see Oracle7 Server Distributed Systems, Volume II.
Interface Enhancements
This section contains the following topics:
Thread Safety, OCI
Thread Safety, OCI allows developers of Oracle applications to use Oracle interfaces or embedded SQL in a multi-threading environment. Implementation of thread safety now makes OCI code reentrant and allows multiple threads of a user program to make OCI calls without having any side effect from one thread to another.
The principal benefits of Thread Safety, OCI are
- Multiple threads can use Oracle library calls with the same result as if they were executed serially.
- Users who do not use the thread-safety property do not pay the performance penalty for thread-safe libraries.
- Cursors to statements in a session are shareable serially. This means that connections can be shared among threads; however, two threads should never share the same connection at the same time.
Using Thread Safety, OCI
You must inform the OCI layer that your environment is single-threaded or multi-threaded in order to use the services of the OCI layer. Therefore, you must execute an OCI process initialization call, OPINIT before any other OCI calls are issued. If the OPINIT call is skipped then, for backward compatibility, a single-threaded environment is assumed. The syntax of the OPINIT call is
SWORD OPINIT (ub4 MODE);
The allowed values for the MODE parameter are
OCIEVDEF
| for single-threaded environments
|
OCIEVTSF
| for multi-threaded environments
|
A new logon call, OLOG, must now be used instead of the ORLON or OLON calls. The syntax of OLOG is
SWORD OLOG (struct cda_def*lda,
ub1*hda,
text*uid,
sword uidl,
text*pswd,
sword pswdl,
text*conn,
sword conn1,
ub4 MODE);
For more information about Thread Safety, OCI, see the Programmer's Guide to the Oracle Call Interface.
Thread Safety, Pro*
Thread Safety, Pro* enables Pro*C and Pro*Ada application developers to write applications that operate in a preemptive threads environment, for example DCE and OS/2, by providing a thread-safe, Pro* runtime library and generating thread-safe code. The following applications are now possible:
- A multi-threaded application with persistent threads: In this case, multiple threads each establish and maintain one or more connections to the database. Since threads are expected to be persistent, once a thread dies, there is no way for a different thread to resume processing of any pending statements.
- A multi-client configuration with transient threads: In this case, a client is analogous to an application. There may be multiple clients executing database calls. A non-persistent thread is used to process a unit of work for the client. Once a thread dies or is suspended, its runtime context may be used by a different thread.
Embedded SQL Statements
New embedded SQL statements that support Thread Safety, Pro* are summarized in Table D - 13.
Embedded SQL Statement
| Definition
|
EXEC SQL ENABLE THREADS
| This statement is required for correct process initialization. It should be called only once and before any threads are spawned. It does nor require any host variables.
|
SQL_CONTEXT:ctx1
| This is a user program variable. It must appear in the DECLARE section for those languages that require a DECLARE section, such as COBOL and FORTRAN. Its scope and visibility are determined by the placement in your program and the host language programming rules.
|
EXEC SQL CONTEXT:ctx1
| This statement is a precompiler directive. It tells the precompiler which runtime context to use on subsequent executable SQL statements.
|
EXEC SQL CONTEXT ALLOCATE:ctx1
| This function initializes the SQLLIB runtime context that is referenced in an EXEC SQL CONTEXT USE statement. In a multi-threaded application, this call should be executed once for each thread. In a multi-client configuration, this call should be executed once per client.
|
EXEC SQL CONTEXT FREE:ctx1
| This function will free all memory associated with a runtime context and put a null pointer in your program variable.
|
Table D - 13. Embedded SQL Statements
New Command Line Option, THREADS=YES
A new command line option, THREADS=YES is a precompiler option that is required for any program requiring multi-threaded support. It is allowed only on the command line and in a configuration file. If the THREADS=YES option is in effect, the precompiler generates an error if it encounters any executable SQL statements and no context is visible.
For more information about Thread Safety, Pro*, see the Programmer's Guide to the Pro*Ada Precompiler and the Programmer's Guide to the Oracle Pro*C/C++ Precompiler.
Piecewise Binds and Defines for String and Raw Data
With Release 7.3, a long column can now be inserted, updated, or fetched, in pieces, by an application program.
The following table summarizes the functionality of four new functions that support Piecewise Binds and Defines for String and Raw Data.
Function
| Definition
|
OBINDPS (OCI BIND Piecewise)
| This function associates the address of a program variable with a placeholder in a SQL or PL/SQL statement.
|
ODEFINPS
(OCI DEFINE Piecewise)
| This function defines an output variable for a specified SELECT list item in a SQL query. It registers a context pointer that is returned, at runtime, to the application when TTC has to provide data incrementally to the application.
|
OGETPI
(OCI GET Piece Information)
| This function returns information about the next piece needed by, or to be given to, the TTC layer.
|
OSETPI
(OCI SET Piece Information)
| This function sets information about the next piece to be provided to, or to be fetched from, the TTC layer.
|
Table D - 14. New Functions for Support of Piecewise Binds and Defines
Warning: The functions defined in Table D - 14 are only compatible with Release 7.3 servers and beyond. If a Release 7.3 application attempts to use any of the functions shown in Table D - 14 against a Release 7.2 or earlier server, error message ORA-01551 is likely to be generated. At that point, you must restart the execution.
For more information about Piecewise Binds and Defines for String and Raw Data, see the Programmer's Guide to the Oracle Call Interface.
Binding/Defining Arrays of Structures in OCI
The previous OCI interface for performing multi-row operations forced the user to allocate a set of parallel arrays, one per column being inserted or fetched. The task of the host language programmer was, therefore, complicated because related data that should have been ideally a part of a single array of structures or records was split across several parallel arrays of scalars.
With Release 7.3, you may now place related scalars in a single structure. In other words, you can now perform inserts from, or fetches into, arrays of structures or records.
Two new calls to OCI are provided. The following table summarizes their functionality.
Function
| Definition
|
OBINDPS
| binds fields in arrays of structures or records
|
ODEFINPS
| defines the fields
|
Table D - 15. Functions for Binding/Defining Arrays of Structures in OCI
Compatibility and Migration Issues
The following list summarizes the compatibility and migration issues associated with Binding/Defining Arrays of Structures in OCI:
- Existing user applications require no modification to work with Binding/Defining Arrays of Structures in OCI.
- There are no interoperability problems since the changes made are only on the client side.
- Since this feature is supported only in the deferred mode, applications that are linked with non-deferred UPI cannot take advantage of this feature.
For more information about Binding/Defining Arrays of Structures in OCI, see the Programmer's Guide to the Oracle Call Interface and the Oracle7 Server Application Developer's Guide.
UNSAFE_NULL_
FETCH, Pro*
UNSAFE_NULL_FETCH provides a new precompiler command line option that allows applications precompiled by Pro*COBOL, Release 1.8, Pro*FORTRAN, Release 1.8, and Pro*C, Release 2.2 with DBMS=V7 to FETCH null values into host variables that lack indicator variables without generating the ORA-01405 error message, "FETCHED column value is NULL", at runtime.
The new precompiler command line option is
UNSAFE_NULL_FETCH=YES/NO
The default value for option UNSAFE_NULL_FETCH is NO.
Restrictions on the USE of UNSAFE_NULL_FETCH
The following list explains the restrictions on the use of UNSAFE_NULL_FETCH:
- UNSAFE_NULL_FETCH is intended only for use with DBMS=V7. If used with command line option DBMS=V6 or DBMS=NATIVE, the UNSAFE_NULL_FETCH option generates a precompilation error.
- Source code precompiled with DBMS=V6 always behaves as though it has been precompiled with UNSAFE_NULL_FETCH=YES.
- Source code precompiled with DBMS=NATIVE behaves as though UNSAFE_NULL_FETCH=NO when the database is running in V7 mode and as though UNSAFE_NULL_FETCH=YES when the database is running in V6 compatibility mode.
- The UNSAFE_NULL_FETCH option cannot be used in combination with MODE=ANSI, because ANSI requires an error to be returned when a null value is returned to a host variable that does not have an indicator variable. This combination of options results in an error message at precompilation.
- Application code precompiled with UNSAFE_NULL_FETCH=NO generates error message ORA-01405 when FETCHing null values into host variables that do not have indicator variables.
- Application code precompiled with UNSAFE_NULL_FETCH=YES does not generate error message ORA-01405 when FETCHing null values into host variables that do not have indicator variables.
The net effect of the previously listed restrictions is that using UNSAFE_NULL_FETCH=YES suppresses error message ORA-01405 that otherwise is generated by the database when running in V7 mode. When UNSAFE_NULL_FETCH=NO, ORA-01405 is not suppressed.
For more information about UNSAFE_NULL_FETCH, see the Programmer's Guide to the Pro*Ada Precompiler, the Pro*COBOL Supplement to the Oracle Precompilers Guide, and the Programmer's Guide to the Oracle Pro*C/C++ Precompiler.
PL/SQL Enhancements
This section contains the following topics:
PL/SQL Tables of Records and Call-by-Reference in PL/SQL
PL/SQL Tables of Records and Call-by-Reference in PL/SQL provide the following new functionality in Release 7.3:
- record types with scalar fields are now supported as elements of PL/SQL tables
- new operators on PL/SQL tables
- call-by-reference parameters for passing PL/SQL tables as arguments to subprograms
The major benefit of PL/SQL Tables of Records and Call-by-Reference in PL/SQL is the improved capability to handle bulk data and composite data. For example, it is now easier for users to
- move collections of data between client side applications and stored programs
- use PL/SQL tables variables to move data into and out of relational tables
- manipulate PL/SQL tables in stored programs
New Operations on PL/SQL Tables
The following new built-in functions and procedures are provided with Release 7.3:
Function or Procedure
| Comments
|
COUNT
| the number of elements the PL/SQL table contains
|
FIRST
| the index of the first element in the PL/SQL table
|
LAST
| the index of the last element of the PL/SQL table
|
EXISTS
| verifies that the PL/SQL table contains an elements at the given index
|
NEXT
| the next (higher) index
|
PRIOR
| the previous (lower) index
|
DELETE
| remove the element of the PL/SQL table at the given index
|
Table D - 16. Built-in Functions and Procedures in Release 7.3
Syntax for Passing Arguments "by Reference"
The keyword BYREF is supported as a parameter mode, instead of IN/OUT, on parameters to which arguments will be passed by reference.
RPC for PL/SQL Tables of Records
Release 7.3 permits PL/SQL-to-PL/SQL RPC of PL/SQL tables of records from clients (that contain a PL/SQL executor) to server and from server to server.
Note: The record types permitted as elements of index tables are records that do not have fields that are records or index tables.
For more information about PL/SQL Tables of Records and Call-by-Reference in PL/SQL, see theOracle7 Server Application Developer's Guide.
PL/SQL File I/O
PL/SQL File I/O allows PL/SQL developers to read and write OS files using the same API on both client and server. Specifically, the following capabilities are now provided:
- It is now possible to write warnings, errors, and debug information outside of the database transaction cycle.
- It is now possible to read upgrade scripts on the server from a client Server Manager process and then execute those upgrade scripts.
- A file I/O process is now available that writes processing status and error messages to concurrent log files. The concurrent log files are stored in different product-specific subdirectories of a central directory.
Access to PL/SQL File I/O is provided through the UTL_FILE package. The available file operations are as follows:
FUNCTION FOPEN
FUNCTION FOPEN (LOCATION in VARCHAR2
FILENAME in VARCHAR2
OPEN_MODE in VARCHAR2) RETURN
UTL_FILE.FILE_TYPE;
where
LOCATION
| is the operating system-specific string that specifies the directory or area in which to open the file
|
FILENAME
| is the name of the file, including extension, without any directory information
|
OPEN_MODE
| is a string that specifies how the file is to be opened
|
Function FOPEN returns a file handle that is used in subsequent file operations.
FUNCTION IS_OPEN
FUNCTION IS_OPEN (FILE in FILE_TYPE) RETURN boolean;
where
FILE
| is the value returned by FOPEN
|
FILE_TYPE
| the contents of the file handle object (FILE_TYPE) are not visible to the user
|
Function IS_OPEN tests a file handle to determine if it identifies an open file.
PROCEDURE FCLOSE
PROCEDURE FCLOSE (FILE IN OUT FILE_TYPE)
where
FILE
| is the value returned by an FOPEN operation
|
PROCEDURE FCLOSE closes the open file identified by FILE.
PROCEDURE FCLOSE_ALL
PROCEDURE FCLOSE_ALL
This procedure closes all open files for the session. This is intended as an emergency cleanup procedure, to be used when a PL/SQL program exits on an exception.
PROCEDURE GET_LINE
PROCEDURE GET_LINE (FILE IN FILE_TYPE
BUFFER OUT VARCHAR2)
where
FILE
| is the value returned by an FOPEN operation
|
BUFFER
| holds the read text
|
PROCEDURE GET_LINE reads a line of text from the open file identified by FILE and places the text in the output BUFFER.
PROCEDURE PUT
PROCEDURE PUT (FILE IN FILE_TYPE
BUFFER IN VARCHAR2)
where
FILE
| is the value returned by an FOPEN operation
|
BUFFER
| is the text to be written
|
PROCEDURE PUT writes the text string BUFFER to the open file identified by FILE.
PROCEDURE NEW_LINE
PROCEDURE NEW_LINE (FILE IN FILE_TYPE
LINES IN NATURAL := 1)
where
FILE
| is the value returned by an FOPEN operation
|
LINES
| is the number of line terminators to be written
|
PROCEDURE NEW_LINE writes LINES number of line terminators to the file identified by FILE. Default is a single line terminator.
PROCEDURE PUT_LINE
PROCEDURE PUT_LINE (FILE IN FILE_TYPE
BUFFER IN VARCHAR2)
where
FILE
| is the value returned by an FOPEN operation
|
LINES
| is the text to write
|
PROCEDURE PUT_LINE writes text string BUFFER to the file identified by FILE, then writes a line terminator, in other words, calls PUT, then NEW_LINE.
PROCEDURE PUTF
PROCEDURE PUTF (FILE IN FILE_TYPE
FORMAT IN VARCHAR2
ARG1 IN VARCHAR2
[ARG5 IN VARCHAR2])
where
FILE
| is the value returned by an FOPEN operation
|
FORMAT
| is the limited printf style format string
|
ARG1...ARG5
| are the text substitution arguments
|
PROCEDURE PUTF formats the arguments ARG1...ARG5 according to the FORMAT string. The formatted text is then written to the file identified by FILE.
PROCEDURE FFLUSH
PROCEDURE FFLUSH (FILE IN FILE_TYPE)
where
FILE
| is the value returned by an FOPEN operation
|
PROCEDURE FFLUSH writes all pending data to a file. Normally, data written to a file may be buffered until enough bytes have accumulated. PROCEDURE FFLUSH forces the write to occur immediately.
For more information about PL/SQL File I/O, see the PL/SQL User's Guide and Reference and the Oracle7 Server Application Developer's Guide.
Fetch from Cursor Variable
Release 7.3 provides an extension to the cursor variable feature introduced in PL/SQL, Release 2.2. The new functionality available with PL/SQL, Release 2.3 and Oracle7 Server, Release 7.3 is as follows:
- The Cursor Variable feature is now extended to all clients of PL/SQL.
- Complete functionality of PL/SQL cursors for cursor variables is now available.
- A set of semantics is now available that is compatible with existing PL/SQL types and semantics for database cursors. The new semantics set is extensible to semantics of objects and object references, which is a feature anticipated for Object PL/SQL in Oracle8.
- Weak REF CURSOR type is now available.
- FETCH from a cursor variable and explicit cursor attributes are now available.
- Self-contained execution within PL/SQL is now available. (The Release 2.2 requirement for a bound host cursor variable is removed.)
- Client-Server communication within PL/SQL is now possible.
- Relaxed parameter mode (IN) on CLOSE of a cursor variable is now possible.
For more information about Fetch from Cursor Variable, see the PL/SQL User's Guide and Reference and the Oracle7 Server Application Developer's Guide.
Data Dictionary Changes
This section contains the following topics:
- Dynamic Performance Tables
This section describes the changes to the Oracle7, Release 7.3 data dictionary. See Oracle7 Server Reference for descriptions of all standard tables and views that are available to developers.
Data Dictionary Views
The following table shows data dictionary views that are new in Oracle7, Release 7.3.
New Views
ALL_HISTOGRAMS
| DEFCALL
|
ALL_UPDATABLE_COLUMNS
| REPCAT$_REPOBJECT
|
DBA_FREE_SPACE_COALESCED
| USER_HISTOGRAMS
|
DBA_HISTOGRAMS
| USER_UPDATABLE_COLUMNS
|
DBA_UPDATABLE_COLUMNS
|
|
Dynamic Performance Tables
This section lists the dynamic performance tables that are changed or new in Oracle7, Release 7.3.
Changed Views
V$CACHE_LOCK
| V$SQLAREA
|
V$SQL
|
|
New Views
V$LATCH
| V$ROLLSTAT
|
V$LATCHNAME
| V$SESSTAT
|
V$LIBRARYCACHE
| V$SORT_SEGMENT
|
V$LOCKED_OBJECT
| V$SYSSTAT
|
V$RECOVERY_FILE_STATUS
| V$TRANSACTION
|
V$RECOVERY_STATUS
|
|
Initialization Parameter Changes
This section lists initialization parameters that are obsolete, changed, or new in Oracle7, Release 7.3.
Dynamic Initialization Parameters
HASH_AREA_SIZE
| HASH_MULTIBLOCK_IO_COUNT
|
HASH_JOIN_ENABLED
|
|
Obsolete Initialization Parameters
INIT_SQL_FILES
| SEQUENCE_CASH_HASH_
BUCKETS
|
LOG_ENTRY_PREBUILD_
THRESHOLD
| SESSION_CACHED_CURSORS
|
PARALLEL_DEFAULT_MAX_
SCANSIZE
| SMALL_TABLE_THRESHOLD
|
PARALLEL_DEFAULT_
SCANSIZE
|
|
Changed Initialization Parameters
COMPATIBLE
|
|
DB_BLOCK_LRU_STATISTICS
| SORT_DIRECT_WRITES
|
PARALLEL_DEFAULT_MAX_
SCANS
|
|
New Initialization Parameters
ALWAYS_ANTI-JOIN
| ORACLE_TRACE_
COLLECTION_SIZE
|
DB_BLOCKS_LRU_LATCHES
| ORACLE_TRACE_ENABLE
|
DB_FILE_STANDBY_
NAME_CONVERT
| ORACLE_TRACE_
FACILITY_NAME
|
DELAYED_LOGGING_
BLOCK_CLEANOUTS
| ORACLE_TRACE_
FACILITY_PATH
|
GC_DEFAULT_LOCKS
| PARALLEL_MIN_PERCENT
|
GC_RELEASABLE_LOCKS
| REMOTE_DEPENDENCIES_
MODE
|
LOG_FILE_STANDBY_
NAME_CONVERT
| SHARED_POOL_
RESERVED_MIN_ALLOC
|
MAX_TRANSACTION_
BRANCHES
| SHARED_POOL_
RESERVED_SIZE
|
MTS_MULTIPLE_LISTENERS
| SORT_WRITE_BUFFER_SIZE
|
ORACLE_TRACE_
COLLECTION_NAME
| SORT_WRITE_BUFFERS
|
ORACLE_TRACE_
COLLECTION_PATH
| UTL_FILE_DIR
|