Oracle 10g Database New Features

Oracle 10g Database New Features

Simplicity vs Flexibility

•        Automatic statistics gathering

•        Advisories

•        Automatic tuning

•        Less than 30 basic (init.ora) parameters

–       Basic, advanced, hidden parameters

•        Easier operations

–       alter tablespace rename

–       flashback queries

–       “undrop” table

Manageability

•        SGA_TARGET

–       Sets total size for all SGA components

–       Buffer Cache, Shared Pool, Large Pool, Java Pool

–       Dynamically and automatically adjustable

–       Automatic changes persist in SPFILE

•        PGA_AGGREGATE_TARGET

–       Available since 9i

–       Sets total size target for all server processes

–       sort_area_size, sort_area_retained_size,
hash_area_size, bitmap_merge_area_size

–       Contents automatically managed

Basic Parameters

COMPATIBLE

CONTROL_FILES

DB_BLOCK_SIZE

DB_CREATE_FILE_DEST

DB_CREATE_ONLINE_LOG_DEST

DB_DOMAIN

DB_NAME

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

INSTANCE_NUMBER

JOB_QUEUE_PROCESSES

LOG_ARCHIVE_DEST_n

LOG_ARCHIVE_DEST_STATE_n

NLS_LANGUAGE

NLS_TERRITORY

OPEN_CURSORS

PROCESSES

REMOTE_LISTENER

REMOTE_LOGIN_PASSWORDFILE

ROLLBACK_SEGMENTS

SESSIONS

SHARED_SERVERS

STAR_TRANSFORMATION_ENABLED

UNDO_MANAGEMENT

UNDO_TABLESPACE

Rename Tablespace

•        Useful in Transportable Tablespace scenarios

•        ALTER TABLESPACE prod RENAME to arc1;

–       Can’t rename SYSTEM or SYSAUX

–       Tablespace and all datafiles must be online

–       Can also rename READ ONLY tablespaces

Bigfile Tablespaces

•        Support for sizes up to 8 Exabytes!

–       8 000 000 Terabytes

–       Max 65535 files in database

–       SYSTEM & SYSAUX can’t be bigfile tablespaces

Crossplatform Transportable TS

RMAN> CONVERT TABLESPACE sales_1,sales_2

2> TO PLATFORM ’Microsoft Windows NT’

3> FORMAT ’/temp/%U’;

...

Transporting Tablespaces Between Databases

input datafile fno=00004 name=/u01/oracle/oradata/salesdb/sales_101.dbf

converted datafile=/temp/data_D-10_I-3295731590_TS-EXAMPLE_FNO-4_06ek24vl

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45

Data Pump

•        A server-managed data transportation tool

•        Direct load/extract capabilities

•        Very high performance/efficient with large data sets

•        Replacement for exp/imp

–       old exp/imp remain supported

•        Commands expdp/impdp

•        Can use files or direct network transfer

•        Dynamic configuration, resumable operations

•        Client can detach and reconnect

•        Can be parallelized using PARALLEL

–       Even loads to/from external text files

–       Parallelization level can be changed on the fly for long running jobs

•        Monitored through DBA_DATAPUMP_JOBS

•        Fine-Grained Object Selection

–       exclude=function

–       exclude=procedure

–       exclude=package:”like ‘PAYROLL%’ ”

–       include=table

–       content=metadata_only | data_only | both

–       query=“modify_date > sysdate-1”

•        DDL Transformations, DDL extract

FlashBack Database

•        Flash Recovery Area must be configured

–       Flashback logs are stored there

–       Consisting of old database block images

–       Fast “rollback” of database, no redologs  required

FlashBack Database

Configuration parameters:

•        DB_RECOVERY_FILE_DEST

•        DB_RECOVERY_FILE_DEST_SIZE

•        DB_FLASHBACK_RETENTION_TARGET

Commands:

•        ALTER DATABASE FLASHBACK ON;

•        ALTER DATABASE FLASHBACK OFF;

•        ALTER TABLESPACE test1 FLASHBACK OFF;

•        ALTER TABLESPACE test1 FLASHBACK ON;

Flashback Row History

SELECT versions_xid XID, versions_startscn START_SCN,

versions_endscn END_SCN, versions_operation OPERATION,

empname, salary FROM hr.employees_demo

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

where empno = 111;

XID START_SCN END_SCN OPERATION EMPNAME SALARY

--- --------- ------- --------- ------- ------

0004000700000058 113855        I Tom  927

000200030000002D 113564        D Mike 555

000200030000002E 112670 113564 I Mike 555

3 rows selected

•        Useful for auditing

Flashback Transaction History

select xid, start_scn, commit_scn, operation,
undo_sql, table_name

from dba_transaction_query where xid = ’000200030000002D’;

XID START_SCN COMMIT_SCN OPERATION UNDO_SQL

--- --------- ---------- --------- -------------------------

000200030000002D 112670 113565 D insert into "SCOTT"."EMP"

("EMPNO","EMPNAME","SALARY") values ('111','Mike','655')

000200030000002D 112670 113565 I delete from "SCOTT"."DEPT"

where "DEPTNO" = '20' and "DEPTNAME" = 'Finance'

000200030000002D 112670 113565 D update “SCOTT”.”EMP”

set “SALARY” = ‘555’ where “EMPNO” = ‘111’ and ”EMPNAME” = ‘Mike’ and “SALARY”

= ‘655’

3 rows selected

Table Recovery using Flashback

•        DROP TABLE X;

–       Table is renamed internally, not dropped

–       Indexes & other structures remain

–       Table is purged when out of free space or quota

•        SELECT * FROM RECYCLEBIN;

–       Systemwide recyclebin DBA_RECYCLEBIN

–       Or show recyclebin command in sqlplus

•        FLASHBACK TABLE RB$$3560$TABLE$1
TO BEFORE DROP RENAME TO scott.emp;

•        PURGE RECYCLEBIN;

•        DROP TABLE X PURGE;

Performance Tuning

•        RBO is dead, long live the CBO!

–       Even data dictionary, SYS tables using CBO

–       However, RBO is gone nowhere, it’s available

•        Optimizer able to use run-time statistics

–       exec dbms_stats.gather_system_statistics(…)

–       OPTIMIZER_DYNAMIC_SAMPLING (default: 2)

•        Multiple Advisors

–       SQL Access & Tuning Advisor

–       Memory Advisors (SGA, Shared Pool, etc..)

–       Segment Advisor (Fragmentation, etc..)

–       Undo Advisor

Performance Troubleshooting

•        Automatic Workload Repository

–       Runtime execution statistics are gathered in memory

–       MMON background process flushes stats to disk

•        V$SQL_BIND_CAPTURE

–       Samples bind variables for all sessions

–       Faster than sql_trace (10046 trace on level 4)

–       But doesn’t capture all variable types

–       And doesn’t capture occurences of bindings

•        _cursor_bind_capture_interval defaults to 900 seconds

–       Good for getting samples of database operations

SQLPLUS Changes

Improvements

•        SPOOL CREATE | REPLACE | APPEND

–       also works for SAVE command

•        SHOW RECYCLE BIN

•        SQLPROMPT runtime variable substitution

–       SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER >"

•        glogin.sql and login.sql scripts are executed also on CONNECT

Other

•        dbms_scheduler

–       calendar Expressions: Yearly, Monthly, Weekly, Daily, Hourly, Minutely, Secondely

•        alter system flush buffer_cache;

•        drop database;

–       database must be closed

–       mounted exclusively

–       restricted

•        default user tablespace

–       specifies default tablespace for new users, similar to default temporary tablespace in 9i

* NYOUG General Meeting March 2004