instance caging – a sample

Instance caging was introduced in 11.2 and desinged to limit the cpu-usage of an instance.

This makes sence when you run several instances on one machine.

In the past, while one instance makes trouble in cpu usage, all others instance got problems as well 😦

Instance caging based on the oracle resource manager.

It makes no sense to throttle a instance permanent down – the system should be well balanced and this feature should be an emergency button.

here’s an example:

index usage monitoring / invisible indexes

In an application lifecycle some indexes will be added to a table, again and again.

But after a while nobody knows which index is really used or not.

Just delete the index ? hmmm who knows 😉

A simple option is to use index monitoring.

alter index <index_name> monitoring usage;

init.ora – parameters

here some issues regarding init.ora parameters

show hidden parameters:

online rebuild index fails ora-8104

When you kill a session which is rebuilding an index this will cause an ora-8104.
SQL> ALTER INDEX I_TEST  REBUILD ONLINE ;
ALTER INDEX I_TEST  REBUILD ONLINE
*
ERROR at line 1:
ORA-08104: this index object 11040 is being online built or rebuilt

Solution:

sql plan management – how to fix an execution plan

in the past stored outlines were used to fix execution plans
in 11g the next level to control execution plans is implemented
its possible to force the optimizer to use one or more execution plans for a statement, uncontrolled plan changes are disabled.

take a closer look at the following examples – test it!

stored outlines – fixed execution plan

stored outlines are designed to force a specific sql-statement to use a fixed execution plan.
This execution plan will be used even the table or the environment changes.
Using stored outlines is dependend on the hash_value (in 10g) / sql_id (in 11g).

In 11g a new feature was implemented – the sql plan managment.
This is a great extension to guarantee save plans.
See more in sql plan managment

reorganize tables online with dbms_redefinition

Sometimes it’s neccessary to change table structures or to rebuild tables.

Unfortunately you wont get a downtime to do so…. even business as usual 😉

With the package dbms_redefinition you can modify your tables online (with only a view exclusive locks)

---

short summary :

1. check for redefinition

2. create a interim table (with your new definition)

3. duplicate original table to interim table

in background a mlog-table (if rowid is used) will be updated while both tables are “in use”

4. create dependent objects (e.g. indexes, views …)

5. resync tables

6. redefinition finish

7. drop interim table

9. possible problems

Generate DDL commands of existing objects

With dbms_metadata it’s easy to generate DDL commands of existing objects.

TABLES

SET LONG 20000 PAGESIZE 0 HEADING off
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;