OPTIMIZER_FEATURES_ENABLE: Downgrade Oracle optimizer functionality

After database up gradation or patched, you may find some under performing SQL statements. Due to one or more bug fixes Oracle optimizer my generate different execution plans for those SQL statements,  hence performance degraded . You need reasonable amount of time to tune them but unable to afford it in production environment! You have to do something quick ..........

In such situation, you can downgrade the optimizer functionality to a previous version or patch set; so that  optimizer just act like it's previous behavior. When you finish SQL optimization/tuning, you can switch to the upgraded or patched  optimizer version.

OPTIMIZER_FEATURES_ENABLE - An Initialization Parameter which can be altered at the system or session level to facilitate down gradation of oracle optimizer.

Current version (after up gradation or patched): 11.2.0.3

SQL> ALTER SYSTEM SET optimizer_features_enable='11.2.0.2' scope=both;
or
SQL> ALTER SESSION SET optimizer_features_enable='11.2.0.2';

Switch to current version
SQL> ALTER SYSTEM SET optimizer_features_enable='11.2.0.3' scope=both;

Measure Table Size in ORACLE

Sometimes it is required to measure how much space a tables occupy in Oracle. The size sum-up table and its co-related indexes, partitions, lobs, lob partitions. here are two SQL scripts, first one listed all tables own by a specific user and second one  include tablespace too.

SELECT segment_name, sum(size_mb) size_mb
FROM (
SELECT CASE WHEN x.segment_type in ('LOBSEGMENT','LOB PARTITION') THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.segment_name= x.segment_name)
  WHEN x.segment_type='LOBINDEX' THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in('INDEX','INDEX PARTITION','INDEX SUBPARTITION') THEN 
  (SELECT y.table_name FROM dba_indexes y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in ('TABLE SUBPARTITION', 'TABLE PARTITION','TABLE')
  THEN x.segment_name END segment_name
,round(sum(x.bytes)/(1024*1024),2) size_mb 
FROM dba_segments x 
WHERE x.owner ='SCOTT'
GROUP BY x.segment_name, x.segment_type
)
GROUP BY segment_name
ORDER BY size_mb DESC;


SELECT segment_name, tablespace_name, sum(size_mb) size_mb
FROM (
SELECT CASE WHEN x.segment_type in ('LOBSEGMENT','LOB PARTITION') THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.segment_name= x.segment_name)
  WHEN x.segment_type='LOBINDEX' THEN 
  (SELECT table_name FROM dba_lobs y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in('INDEX','INDEX PARTITION','INDEX SUBPARTITION') THEN 
  (SELECT y.table_name FROM dba_indexes y WHERE y.index_name = x.segment_name)
  WHEN x.segment_type in ('TABLE SUBPARTITION', 'TABLE PARTITION','TABLE')
  THEN x.segment_name END segment_name
, x.tablespace_name
, round(sum(x.bytes)/(1024*1024),2) size_mb 
FROM dba_segments x 
WHERE x.owner ='SCOTT'
GROUP BY x.segment_name, x.tablespace_name,x.segment_type
)
GROUP BY segment_name, tablespace_name
ORDER BY size_mb DESC;