What are the new Features in 11g for DBA you use in daily jobs.
1. Parameters Memory_target and Memory_max_target for Tuning
2. Tablespace Enhancements with Compression Option
// 11g compression result is slower transaction throughput but creates less writes because of higher row density on the data block
3. diagnostic_dest Parameter
//(core_dump_dest, background_dump_dest, user_dump_dest) are placed by a single diagnostic_dest parameter
//new initialization parameter diagnostic_dest to specify an alternative location for the diag directory contents.
// $ORACLE_HOME/diag/$ORACLE_SID,
4. Database Replay - Its for testing. - captures actual production workload and replays it on a separate system
5. Active dataguard - Open-read only Mode.
6. RESULT_CACHE
How to use the result cache
Oracle notes that the result_cache functionality can be enabled in two ways:
Alter session - you can issue the command "alter session cache results;" to cache your session data.
PL/SQL - You can create a PL/SQL function using the result_cache keyword to store the result, and use the new relies_on keyword to tell Oracle what tables will trigger an invalidation (should the rows become out-of-date with the current table data).
SQL - You can add the /*+ result_cache */ hint to any SQL statement, and invoking the result_cache hint is far easier than creating a PL/SQL array or materialized view because the syntax is very straightforward:
select /*+ result_cache */
stuff
from tab1 natural join tab2;
Note : alter session cache results;
Which can be useful for lookup table. As commented on a previous post, you can still use the old alter table cache; or the keep buffer pool. It depends.
7. Oracle Automatic Diagnostic repository (ADR) has a new command-line interface dubbed ADRCI, the ADR Command Interface
8. RMAN new 11g features:
A Virtual Private Catalog can now make sure an RMAN user can only see databases they are authorized to use.
Archive log management for Streams and Data Guard
Network aware DUPLICATE
Optimized undo backup
Improved corrupt block detection
9. Data Pump Enhancements to use when you're moving data
Compression and encryption enhancements
Support for XML Schemas and schema-based tables.
Transportable partitions
10. Performance - Some areas that stand out as getting faster include:
Faster Data Guard.
Faster simple SQL operations. Faster SQL with caching of frequently used SQL results.
Faster DML triggers.
Faster upgrades.
Faster PL/SQL and Java (auto native compilation). Caching of frequently used procedures and functions results.
Faster statistics generation.
Faster sort operations.
11. RMAN UNDO bypass - RMAN backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. Now RMAN can bypass those types of tablespace. Great for exporting a tablespace from backup.
EXEC DBMS_STATS.GATHER_TABLE_STATS ('hr', 'employees');