This article presents new feature of 11g – Flashback Data Archive.

This functionality allows to save all transactions executed on a table for longer period than just UNDO_RETENTION parameter and UNDO tablespace. In 11g you can create dedicated space to keep changed data(DML operations, DDL operations) for longer specified retention period.

Historical records can be reviewed using standard flashback sql queries feature.

Continue reading →

Consolidation is very popular word today to save costs:

• buy one huge server with a lot of CPU, memory, huge storage, fast network

• put as many as possible databases on it

• utilize it as efficient as possible

But consolidation brings problem how to share resources between databases:

• CPU (this article is related only to this resource)

• storage

• memory

• network

As default an Oracle database can see/use all CPUs on a host. In 11.2G there is new option called “Instance Caging” which enables to limit number of CPUs which can be seen/used by a database.

Continue reading →

In case you don’t have budget to buy Exadata you can still buy huge number of flash disks and put on them part of your database. But what should be stored on flash disks(very fast) and what on magnetic disks(very slow) ?

It’s not your businesses to know let decide database 🙂

Continue reading →

This article presents new features related to table compression in Oracle 11g. It’s not presenting compression related to Exadata.

Compression option is extended in 11g and have following features:

• requires compatibility 11.1 for new functionality
• extended COMPRESS caluse: COMPRESS [BASIC | FOR OLTP]
• BASIC – default, bulk-load operations from prior releases
• FOR OLTP – it’s new used for OLTP + direct loads
• BASIC and FOR OLTP are not supported for tables with more than 255 columns
• only available for heap organized tables
• compression can be different for each partition
• COMPRESS [BASIC|FOR OLTP] is not used for BasicFile LOBs
• new columns in views DBA/ALL/USER_TABLES and DBA/ALL/USER_TAB_PARTITIONS – COMPRESS, COMPRESS_FOR
• columns can’t be dropped for compressed tables only set as unused

Continue reading →

This article presents new features of 11g added to opatch.

It’s very nice feature of 11g for applying “online” bug fixes on live databases:

• install online patch
• enable online patch
• disable online patch

This functionality is provided with commonly know opatch command-line utility and new type of patches “online”.

Continue reading →

UTL_MATCH is very interesting package which implements quite advanced algorithms to match two strings. You can use them to quickly match name like First Names or Last Names.

Two basic algorithms are implemented here:

“Edit Distance” – is a measure of Similarity between two strings, s1 and s2. The distance is the number of insertions, deletions or substitutions required to transform s1 to s2

“Jaro-Winkler algorithm” is another way of calculating Edit distance between two strings. It is a String Comparator measure that gives values of partial agreement between two strings. The string comparator accounts for length of strings and partially accounts for typical human errors made in alphanumeric strings

Continue reading →

This article presents new feature of 11g Real time sql monitoring.

Real time sql monitoring introduced in Oracle 11G enables to monitor performance of SQL statements while they are running. Statistics about monitored queries are visible in new views.

Continue reading →

This article presents how result cache can be used together with PL/SQL.

it’s important to read following article

SQL Result Cache 11g

Let’s prepare simple function that is using result cache functionality

create or replace function sleep_fnc(id number)
return number
result_cache
is
begin
  dbms_lock.sleep(5);

  return 5;
end;
/

FUNCTION SLEEP_FNC compiled

Continue reading →

Before reading this article I suggest to read article about deterministic functions

Deterministic functions

SQL Result cache is nice addition to deterministic functions.

It enables to:

• cache the result of a query or query block for future reuse

• cached results are available for all sessions unless they are staled

SQL Result cache are especially usefull for warhouses were users read many rows and return few rows. Returned rows are saved in SGA and can be reused if the same query is executed once again in the same or other session. Cached SQL results become stale when data in the objects being accessed by the query is modified.

NOTE: for RAC configuration each instance has got own private result cache and can’t be used by other instances. However invalidations work accross instances.

Continue reading →