oracle 10g new features
TRANSCRIPT
![Page 1: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/1.jpg)
1
Oracle 10g Database Engine New Features For Techies
ORACLE 10g
![Page 2: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/2.jpg)
2
• What’s behind the 10g buzzwords• Manageability enhancements• Performance tuning enhancements• Exp/imp with Data Pump• Parameters, views• SQL and PL/SQL• Recovery and Flashback• Wrap-up • Questions
Agenda
![Page 3: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/3.jpg)
3
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– etc..
![Page 4: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/4.jpg)
4
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
![Page 5: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/5.jpg)
5
Basic ParametersCOMPATIBLECONTROL_FILESDB_BLOCK_SIZEDB_CREATE_FILE_DESTDB_CREATE_ONLINE_LOG_DESTDB_DOMAINDB_NAMEDB_RECOVERY_FILE_DESTDB_RECOVERY_FILE_DEST_SIZEINSTANCE_NUMBERJOB_QUEUE_PROCESSESLOG_ARCHIVE_DEST_nLOG_ARCHIVE_DEST_STATE_n
NLS_LANGUAGENLS_TERRITORYOPEN_CURSORSPROCESSESREMOTE_LISTENERREMOTE_LOGIN_PASSWORDFILEROLLBACK_SEGMENTSSESSIONSSHARED_SERVERSSTAR_TRANSFORMATION_ENABLEDUNDO_MANAGEMENTUNDO_TABLESPACE
![Page 6: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/6.jpg)
6
Manageability• SYSAUX tablespace for system utilities
Module Tablespace
SYS objects SYSTEM (includes AUD$ and FGA_LOG$ tables)
Oracle Data Mining ODM
OEM Repository OEM_REPOSITORY
Oracle OLAP CWMLITE
Oracle interMedia SYSTEM
Oracle Spatial SYSTEM
Oracle Ultra Search DRSYS
RMAN Recovery Catalog TOOLS
Sample Schemas Example
Workspace Manager SYSTEM
Oracle XML DB XDB_RESINFO
Auto. Workload Repository New in 10g
SYSAUX
![Page 7: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/7.jpg)
7
Online Operations• Some functionality there already in 8i, 9i
– Online index build, rebuild– Online index organized table rebuild– Analyze/validate structure online
• Online table redefinition– dbms_redefinition package starting from 9i– change table physical structure online– allows adding, changing, removing columns
online
• Rolling database upgrade– Requires Logical Standby database
![Page 8: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/8.jpg)
8
Online Segment Shrink• Can be recommended by Segment Advisor
• alter table enable row movement;• alter table t1 shrink space cascade;
TABLE HEADER 01010101
110110101100110100001101 10111101
1100110110101111111011010000110110111101
High Water Mark
TABLE HEADER 11001101
00001101
11001101101011111110110100001101
11001101101011111110110100001101
High Water Mark Free Space
Free Space
![Page 9: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/9.jpg)
9
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
PROD ARC1
![Page 10: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/10.jpg)
10
Temporary Tablespace Groups• Can create several temporary tablespaces
and assign them to a tablespace group• Can assign the tablespace group as users
temporary tablespace• Parallel Query and Parallel execution slaves
can spread temporary segments on different tablespaces– alter tablespace temp1 tablespace group t;– create temporary tablespace temp2 tempfile
'temp2_01.dbf' size 100m tablespace group t;– alter tablespace temp1 tablespace group '';
![Page 11: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/11.jpg)
11
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
• CREATE BIGFILE TABLESPACE bigtbsDATAFILE '/u02/oracle/data/bigtbs01.dbf‘SIZE 50G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
8 000 000 000 000 000 000 bytes
![Page 12: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/12.jpg)
12
Who Needs This?!• Who could possibly outgrow previous
database size limits?• 9i limits 65534 files per database• 222-1=4,194,303 blocks per datafile• 32kB max blocksize (not on all platforms)• Total max size of 9i database:
– 65534*4194303*32 kB≈8055 TB≈8 Petabytes!– 38000*4194303*16 kB≈2.5 Petabytes!– Practical size limit in reality is even smaller
• CERN in Switzerland: 5-8 PB data per year
![Page 13: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/13.jpg)
13
Crossplatform Transportable TSRMAN> CONVERT TABLESPACE sales_1,sales_22> TO PLATFORM ’Microsoft Windows NT’3> FORMAT ’/temp/%U’;...Transporting Tablespaces Between Databasesinput 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
![Page 14: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/14.jpg)
14
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
PRODData
Warehouse
Data Pump
DataPumpClient
Data Pump
DataPumpClient
![Page 15: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/15.jpg)
15
Data Pump• 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
![Page 16: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/16.jpg)
16
Data Pump Options• 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• table_exists_action
– skip, append, truncate, replace
![Page 17: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/17.jpg)
17
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
DB Flashback Log
ServerProcess
ServerProcessServer
Process
![Page 18: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/18.jpg)
18
FlashBack Database
Configuration parameters:• DB_RECOVERY_FILE_DEST• DB_RECOVERY_FILE_DEST_SIZE• DB_FLASHBACK_RETENTION_TARGETCommands:• ALTER DATABASE FLASHBACK ON;• ALTER DATABASE FLASHBACK OFF;• ALTER TABLESPACE test1 FLASHBACK OFF;• ALTER TABLESPACE test1 FLASHBACK ON;
![Page 19: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/19.jpg)
19
FlashBack Options
Flashback Query:• exec dbms_flashback.enable_at_time(…);• select * from table;• exec dbms_flashback.disable;• select * from table AS OF timestamp ‘…’;Flashback Table:• FLASHBACK TABLE t1 TO SCN 12345;• FLASHBACK TABLE t1 TO TIMESTAMP '2003-
03-03 12:05:00';
![Page 20: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/20.jpg)
20
Flashback Row HistorySELECT 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
![Page 21: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/21.jpg)
21
Flashback Transaction Historyselect xid, start_scn, commit_scn, operation,
undo_sql, table_namefrom 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
![Page 22: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/22.jpg)
22
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$1TO BEFORE DROP RENAME TO scott.emp;
• PURGE RECYCLEBIN;• DROP TABLE X PURGE;
![Page 23: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/23.jpg)
23
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
![Page 24: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/24.jpg)
24
Advisor Information• DBA_ADVISOR_FINDINGS• DBA_ADVISOR_RECOMMENDATIONS• DBA_ADVISOR_RATIONALE• And many more…• All the information is accessible through
DBA_ and V$ data dictionary views• The reasons and rationale behind
recommendations can be seen
![Page 25: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/25.jpg)
25
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
![Page 26: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/26.jpg)
26
Performance Troubleshooting (2)• Event, P1, P2, P3 columns in V$SESSION for
quick diagnosis– the same information that in V$SESSION_WAIT
• dbms_monitor package– set tracing and statistics gathering in sessions
based on sessionid, client_id, module and action attributes
– RAC aware, monitoring remains active until explicitly disabled
• trcsess tool for gathering sessions SQL from in shared server environment– Sql trace (10046) reports module, action,
client_id information to trace file
![Page 27: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/27.jpg)
27
Improved Timing and Statistics• Old methods
– V$SESSION_EVENT, V$SYSTEM_EVENT– V$SYSSTAT, V$SESSTAT, V$FILESTAT, – Contained cumulative information since startup
• New opportunities– V$EVENTMETRIC, V$SESSMETRIC, V$SYSMETRIC,
V$FILEMETRIC– V$SYSMETRIC_HISTORY,
V$SESSION_WAIT_HISTORY– Contain same statistics, but sampled over
interval, also history views
![Page 28: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/28.jpg)
28
SQLPLUS ChangesImprovements• 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
Deimprovements• No graphical sqlplusw.exe in Windows• COPY command deprecated
![Page 29: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/29.jpg)
29
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
![Page 30: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/30.jpg)
30
Conclusion• This was probably < 1% of all new features• Lot’s of new features = lot’s of new bugs• Automatic for small to medium systems• Big & heavily loaded systems still need care
![Page 31: Oracle 10g New Features](https://reader033.vdocuments.us/reader033/viewer/2022061200/547802bab4af9f32138b45a5/html5/thumbnails/31.jpg)
31
Oracle 10g Database Engine
Thank you!