Pages

Saturday, December 11, 2010

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

No comments:

Post a Comment