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:
some sql scripting hints
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 😉
easy, fast cloning ORACLE_HOME
updated 27.4.2012
Building a master-installation for cloing has multiple benefits
- a new Oracle installation can be done very fast
- all clones from the master are identical – e.g. no one off is missing
- 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
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
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
AWR – automatic workload repository
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
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.
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!
Read more…