Re: what's new in 7.1?
Date: 6 Oct 1994 21:58:38 GMT
Message-ID: <steve#m#_dave-061094145205_at_17.25.26.138>
In article <35sm30$ei5_at_news1.shell>, sjs_at_shell.portal.com (Steve - Schow)
wrote:
>
> What are the new features of Oracle 7.1?
>
> Thanks in advance
> --
> --------------------------------------------------------------
> Steve Schow | But you don't need to use the claw
> sjs_at_corp.portal.com | if you pick the pear with the big
> 408-973-9111 | paw paw. Have I given you a clue?
Steve,
Here are my notes from going over the 7.1 doc.
Steve Hoffman Consulting at Apple Computer
Here are my notes from looking over the 7.1 doc:
New Oracle7 Features Introduced with Release 7.1
¥ Server Manager: graphical DBA tool ¥ User-defined PL/SQL functions in SQL ¥ Dynamic SQL in stored procedures ¥ Reference column aliases in ORDER BY ¥ Secrue remote database administration ¥ Parallel recovery ¥ Multiple same-type triggers ¥ Read-only Tablespaces ¥ SQLCODE return status in precompilers ¥ Secure client/server password protection Oracle7 Features Available with the Distributed Option ¥ Distributed update trnsactions ¥ Remote procedure calls ¥ Transparent two-phase commit ¥ Table replication (snapshot refresh groups) Oracle7 Features Available with the Parallel Server Option ¥ Clustered systems support ¥ Autmatic recovery ¥ Parallel cache management ¥ On-line reconfiguration Oracle7 Features Available with the parallel Query Option ¥ Parallel query execution ¥ Parallel index creation ¥ Parallel data loading ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Making a table read only.
The database must be open with no active transactions. Open the database in DBA mode to assure this.
The parameter COMPATIBLE must be at 7.1.0 or greater.
Issue the ALTER TABLESPACE tsname READ;
First run a query against all the blocks in the tablespace. This will
prevent delayed block cleanout from occuring everytime the data is
accessed.
SELECT 'SELECT COUNT(*) FROM ' || TABLENAME|| ';'
FROM dba_tables
WHERE tablespace_name = uppper('&tablespace_name')
/
Do somthing similar for the clusters and indexes. Yes, I verified the index delayed block cleanout.
Special recovery considerations:
You can do hot backup without issuing the ALTER TABLESPACE BEGIN BACKUP
command.
Whenever a tablespace is altered READ or back to READ WRITE a backup of the
control file should be taken.
If you do not have a backed up control file that has the tablespace in the same state as it should be you can create a control file but if you create a control file you better not have any read-write tablespaces that were at one time read only.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Parallel recovery
You can now recover a database in parallel. This is good for multiple disk
databases especially if they are running on platforms that do not support
asynch io.
There is automatic multi-threading from the single session so the only thing required to access parallel recover is to set the init.ora parameter RECOVERY_PARALLELISM to the number of disks with database files. Likewise it can be set with the RECOVER command for media recovery. RECOVER DATABASE PARALLEL (DEGREE N);
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Database connnection security
Encription of SQL*Net us/pw
On the client side set the environment variable ORA_ENCRYPT_LOGIN to TRUE
On the server side, set the init.ora parameter DBLINK_ENCRYPT_LOGIN to
TRUE.
Continue on page 5-4 of the addendum.
Secure Connection for database administration.
You can now connect as sys (formally internal) without validating at the OS. This is good for SQL*Net connections and for shops with multiple users using the same account.
The way this works is to have an external password file. This file is created with the orapwd utility. Once this is created the file must be referenced by setting the init.ora parameter REMOTE_LOGIN_PASSWORDFILE from the default of NONE to either EXCLUSIVE or SHARED.
The roles SYSOPER and SYSDBA are created. SYSOPER can start/stop/recover/backup a database. SYSDBA is god-like similar to INTERNAL. Now the connect string has an AS clause so you can do CONNECT SCOTT/TIGER_at_T:TONKA:PLAY AS SYSDBA; Note: this will connect me as SYS not SCOTT.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
THE PARALLEL QUERY.
One process acts as the query coordinator dispatching the execution of a
statement to several query servers during the EXECUTION stage of the sql
statment. The query coordinator decides if the query warrents
parallelism.
To decide if the parallel query will be used the query coordinator looks for these particular sql operations:
¥ AGGREGATE (GROUP BY) ¥ MERGE JOIN ¥ NESTED LOOPS ¥ SORT (GROUP BY) ¥ SORT (JOIN) ¥ SORT (ORDER BY) ¥ SORT (UNIQUE) ¥ TABLE ACCESS (FULL)
To decide the degree of parallelism the query requests the coordinator
looks at:
¥ hints in the query
The syntax is
PARALLEL (tablename, number1|default [, number2|defatult ])
where number2 is for parallel server and how many instances the table is
split accross and the default means the query coordinator should look to
the init.ora parameters.
NOPARALLEL (tablename)
¥ the table definition
The CREATE and ALTER TABLE/CLUSTER now have a new clause
PARALLEL (DEGREE|INSTANCES integer|DEFAULT)|NOPARALLEL CACHE|NOCACHE
The cache/nocache has to do with wether blocks will be read into the hot or
cold end of the LRU on full table access.
¥ init.ora parameters
The DEFAULT values are determined by the lower of the following two
numbers: PARALLEL_DEFAULT_MAX_SCANS or #of blocks in
table/PARALLEL_DEFAULT_SCANSIZE
server processes available
This is dynamic bounded by
PARALLEL_MIN_SERVERS, PARALLEL_MAX_SERVERS & PARALLEL_SERVER_IDLE_TIME
V$PQ_SYSSTAT contains monitoring statistics.
If one table has a higher level, ie DEPT is 6 and EMP is 5, the higher, 6
is used.
If the request is too large for the query server pool a smaller number is
granted.
Taking a parsed execution plan the query coordinator determines the degree of parallelsm for each opeartion. Since a parent and a child can be active you could have twice the number of processes active as the degree of parallesm. (WHAT ABOUT PARENTS WITH MULTIPLE KIDS) If I do a full table scan and a sort. I may have 4 processes scanning, passing data to 4 process sorting. If this were a more complicated query the full table scanning processes would become available for other operations once they were finished with the scan.
EXPLAIN PLAN additions:
The plan table has 2 new columns OBJECT_NODE which acts as a row key value
and OTHER which lists where input data comes from, referencing
OBJECT_NODE. Parallel Index Creation.
Similar to the parallel query. One set of processes scan the table,
passing rowids and key values to another set of processes. This second
set of processes sort the data and pass the results to the coordinator
process which builds the B* tree index.
By default the degree of parallelism comes from the table definition but
that can be overiden within the CREATE INDEX statement.
Storage clause note: when creating an index in parallel the storage clause refers to each of the subindexes created. This will eventually be trimmed down by the coordintator process.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Parallel Data Loading:
You can now use the direct path loader through multiple sqlload sessions.
This is done by giving each of the sessions a different set of data to load
and by specifying PARALLEL=TRUE.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dynamic SQL
Cool stuff
You can now do dynamic SQL following the OCI model in PL/SQL. This is
accomplished by adding a slew of procedures to the DBMS_SQL package.
These procedures read like OCI calls, there is a open_cursor, parse,
bind_variable, execute, close etc... This should open the door to DDL
within PL/SQL which should be quite a DBA aid.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Snapshot Refresh
You can now have refresh groups that will refresh multiple snapshots.
This is accomplished with the DBMS_REFRESH package.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Oracle Names
Adminstration aid to SQL*Net. It provides storage of the SQL*Net connect
data.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Triggers
You can now have multiple triggers of the same type on the same table.
The order of two triggers of the same type is random. If the order is
important you will still have to combine the triggers into one.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Caching tables
You can now set the SMALL_TABLE_THRESHOLD on a per table basis. This is
controlled with additions to the create/alter table/cluster. This can also
be controlled on a per statement basis with the HINT CACHE or NOCACHE.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Caching Session cursors.
If more then 3 parse requeset have been issued on a given statement Oracle
moves the cursor to the session cursor cache. Subsequent requests by the
same session to parse that SQL statement will find the cursor in the
session cursor cache. The # of cursors that can fit in the session cache
is determined by the SESSION_CACHED_CURSORS init.ora parameter or set
dynamically with the ALTER SESSION SET SESSION_CACHED_CURSORS command.
Cursors are loaded following a LRU algorithm. See 'session cursor cache
hits' in the V$SESSTAT view for hit ratios.
Received on Thu Oct 06 1994 - 22:58:38 CET