Friday 2 November 2007

Innovation in Oracle 11g Database

I have attended the Ascot, UK (26/09/2007) Presentation by Andrew Sutherland, vice president, technology for Oracle EMEA and I am posting a summary of notes I have taken in that event.

Oracle 11g Innovations

Real Application Testing : Test workloads are typically simulated and are not accurate and complete representations of real world.

Database Replay : The ability to accurately and realistically rerun actual production workloads, including online user and batch workloads as well as cuncurrency dependencies and timing on a test system. No need to understand the application and write scripts to simulate load. DBAs now can test without the need to duplicate the application infrastructure/hardware. Cuts down testing cycles and time taken for tests.

Used for Database Upgrades, patches, parameter, schema changes , etc
Configuration changes such as conversion from a single instance to RAC, ASM, etc.
Storage, network, interconnect changes. Operating system, hardware migrations, patches, upgrades, parameter changes

SQL Performance Analyzer : A tool measuring the impact of environment changes on SQL execution plans in the database. A kind of a tool which integrates together previous SQL Tuning Sets (STS) and SQL Tuning Advisor (STA) tools. It produces a report outlining the benefit on the workload introduced by the change. So it gives a picture of the SQL Execution plans before the change and after the change,including the statements which regressed, seperately. The advantage of this tool, compared to the home grown approaches that dba’s have evolved over the years, is that it takes the number of executions into consideration while measuring the impact. Oracle 11G SQL Performance Analyzer takes all these factors into account while predicting the overall performance improvement and regressions and allows the oracle 11g database administrator to fix the SQL or fix the regression through SQL Plan Baselines.

Database upgrade Configuration changes to the operating system, hardware, or database Database initialization parameter changes Schema changes, for example, adding new indexes or materialized views Gathering optimizer statistics SQL tuning actions, for example, creating SQL profiles

New Datatypes

XML, 3-D data, RFID (Radio Frequency ID), MP3, JPEG….

Binary XML -(No need to store the XML as CLOB anymore) “… If you’re mostly just going to pump the XML back out, binary seems to be the way to go. …” Binary XML allows you to store XML totally unparsed but still accessible via XPath. No encoding checks are done on loading. With no (or at least reduced) parsing, validation or conversions, you will have much less IO and much less CPU meaning much faster load times. With the combination of Secure Files this is a killer!

XMLIndex, that is a new way to improve access performance of your XML. In the past, your options were Xpath function based indexes (using extract or extractvalue) or Oracle Text. Both of those have some limitations that XML Index tries to address.

Secure Files The New BLOBs. Performance is the same as Unix File handling performance. This feature introduces a completely reengineered large object (LOB) data type to dramatically improve performance, manageability, and ease of application development. The new implementation also offers advanced, next-generation functionality such as intelligent compression, de-duplication of LOB segments and transparent encryption.

Managing Data Growth

Data Growth Facts:

DW is bogged down. The increase in the ammount of data of a system stores, is faster than the decrease of the cost of the disks. So buying cheap disks will not solve the data issues.

30 years ago the largets database was about 30GB DB has now become a 300TB DB.
Data Growth Solution:

Ability to comresss in the OLTP environments as well. Better compress ratios. Advanced Compression (Not gZIP). The compression algorithm in Oracle works with symbolic tables. It is a Logical Compression rather than a Physical one! Before compression was only for DWs now is for OLTPs.

ILM (Information Life Cycle Management) old, not-so-old, current data in different storage media with partitioning and advanced compression.

Secure Files, more efficiency with BLOBs.
Automating partitioning adds partitions on the go.

More Partitioning Options In 10g you could only do range-list and range-hash combinations now you can do many.

Higher Quality of Service

All performance figures have increased, better, faster. Faster backups, ZLIB algorithm for compression. Parallel backup of the same datafile.
MVs become “CUBE” organized MVs. PIVOT operator instead of DECODE. The new MODEL clause, MVs refresh without logs.
MMA (Maximum Availability Architecture)

Recovery Advisor (RMAN) RMAN Advisor, repairs and previews RMAN OEM GUI.
Flashback Transaction, & Dependecies

Total Recall (Flashback Data Archive, “Time Travel”) with SQL such as “AS OF” The main difference between flashback and Total Recall is that with Total Recall data will be permanently stored in an archive tablespace and will the only ages out after a user defined retention time. Oracle Total Recall enables administrators to easily maintain historical archives of changed data for compliance and business intelligence. Oracle Total Recall provides a secure, efficient, easy-to-use and application-transparent solution for long-term access to changed data.
Oracle Active Data Guard - No need to stop continous redo apply like in 10g when reading Standby Copy.

By the way and here is what is installed by default when you install Oracle 11g:

Overview of new components during Oracle 11g Release 1 (11.1) Linux x86 install:

Oracle Apex (HTMLDB) installed by default with 11g.
Oracle Configuration Manager is an available install option.
Oracle Database vault is an available install option.
Oracle Real Application Testing installed by default.
Orace SQL Developer is installed by default.
Oracle Warehouse Builder is installed by default (basic feautures only).
Oracle XML DB is installed by default for all database instances.
Oracle Ultra Search is integrated within the database, suppose installs by default.
Oracle HTTP Server available as option on seperate media.
Oracle Data Mining installs by default on Oracle Enterprise Edition.


Oracle Real Application Testing;
Oracle Advanced Compression;
Oracle Total Recall; and,
Oracle Active Data Guard.

Detailed Oracle 11g New Features Guide

No comments: