instance caging – a sample

12. February 2012 Leave a comment

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:

Read more…

some sql scripting hints

4. November 2011 Leave a comment

generate dynamic filenames

to generate dynamic spool filenames use (name will be set at the select):

col spoolfile new_val spoolfile
select 'log_' ||to_char(sysdate, 'yymmdd_hh24miss' ) spoolfile from dual;

spool &spoolfile

to be continued 😉

Categories: Uncategorized

easy, fast cloning ORACLE_HOME

20. October 2011 1 comment

updated 27.4.2012

Building a master-installation for cloing has multiple benefits

  1. a new Oracle installation can be done very fast
  2. all clones from the master are identical – e.g. no one off is missing
  3. the installation can be scripted very easy, no creation of responsefile necessary

Quick overview

create tarfile from master

extract tarfile to the new location

start clone

that’s all 😉 Read more…

generate dumps – overview

13. September 2011 Leave a comment

Some nice – important dump options 🙂

and still under constuction

find sga ID

in Unix environments with mutiple instances running within the same os-use (e.g. oracle) you cannot see which memory segment is allocated to which instance.

sqlplus  / as sysdba
oradebug setmypid
oradebug ipc
oradebug tracefile_name

the column Shmid shows the dependend memory structures

Read more…

index usage monitoring / invisible indexes

4. August 2011 1 comment

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;

Read more…

Categories: administration Tags: ,

init.ora – parameters

here some issues regarding init.ora parameters

show hidden parameters:

Read more…

AWR – automatic workload repository

17. July 2011 1 comment

to change the collection interval and retention policy

exec dbms_workload_repository.modify_snapshot_settings ( retention => 20160, -
interval => 30);

example above will change to 14 days (value in minutes) with 30mins snapshots

retention (default  8 days) and interval (default 1 hour)  in minutes

be aware – increasing the retention time or increase the frequence of snapshots the sysaux tablespace will increase also !

to show the current settings or the latest snapshot

col systimestamp format a35
col most_recent_snap_time format a25
col snap_interval format a17
col retention format a17

select systimestamp, most_recent_snap_time, snap_interval, retention
 from wrm$_wr_control 
  where dbid = (select dbid from v$database);

create manually a snapshot

exec dbms_workload_repository.create_snapshot

enable/disable Diagnostic&Tuning pack

Read more…

Categories: diagnostic Tags: ,

invalidate cursor in shared pool

Sometimes it’s necessary to invalidate a specific cursor in shared pool.

In the past you had to flush the complete shared pool – which caues ALL sql statements were flushed 😦

In the package dbms_shared_pool you can flush a SINGLE cursor.

Read more…

online rebuild index fails ora-8104

When you kill a session which is rebuilding an index this will cause an ora-8104.
ERROR at line 1:
ORA-08104: this index object 11040 is being online built or rebuilt


Read more…

Categories: administration Tags: ,

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!
Read more…