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;

Recover Database from ORA-00333: redo log read error

In development environment, it is very common scenario that we have multiple databases in a single machine by using VMware (i.e, each VMware contains one database). Again those machines doesn't have consistant power backup. Therefore we have to face power failure or VMware hang-up. So, we are forced to restart the machine while databases are still up & running. After restarting the machine, we have mostly got he following error:

   ORA-00333: redo log read error block count .

Here are the steps to overcome the error

SQL> startup
ORACLE instance started.
Total System Global Area ***** bytes
Fixed Size               ***** bytes
Variable Size            ***** bytes
Database Buffers         ***** bytes
Redo Buffers             ***** bytes
Database mounted.
 
 ORA-00333: redo log read error block *Number* count *Number*

Step 1: As the Db is in mount mode, We can query v$log & v$logfile to identify the status of log file group and their member.

 SQL> select l.status, member from v$logfile inner join v$log l using (group#); 

 STATUS  MEMBER
 ------------- --------------------------------------
 CURRENT /oracle/fast_recovery_area/redo01.log
 INACTIVE /oracle/fast_recovery_area/redo02.log
 INACTIVE /oracle/fast_recovery_area/redo03.log

Step 2: Recover the database using ackup controlfile.

 SQL> recover database using backup controlfile;

ORA-00279: change  generated at  needed for thread 1
ORA-00289: suggestion : /oracle/fast_recovery_area/archivelog/o1_mf_1_634_%u_.arc
ORA-00280: change  for thread 1 is in sequence #
Specify log: {=suggested | filename | AUTO | CANCEL}

Step3: Give 'CURRENT' log file member along with location as input. If it does not work give other log file members along with location in input prompt. In our case we give 

/oracle/fast_recovery_area/redo01.log

Log applied.
Media recovery complete.

Step 4: Open the database with reset logfile

SQL> alter database open resetlogs;

Database altered.

Manage SYS.AUD$ table

From 11g, Oracle by default enable auditing with 'DB' option.  That means audit information will store in database table. With this settings Oracle will audit following activities:

ALTER ANY PROCEDURE, ALTER ANY TABLE, ALTER DATABASE,ALTER PROFILE, ALTER SYSTEM, ALTER USER, AUDIT SYSTEM, CREATE ANY JOB, CREATE ANY LIBRARY, CREATE ANY PROCEDURE, CREATE ANY TABLE, CREATE EXTERNAL JOB, CREATE PUBLIC DATABASE LINK, CREATE SESSION, CREATE USER, DATABASE LINK, DROP ANY PROCEDURE, DROP ANY TABLE, DROP PROFILE
DROP USER, EXEMPT ACCESS POLICY, GRANT ANY OBJECT PRIVILEGE, GRANT ANY PRIVILEGE, GRANT ANY ROLE, PROFILE, PUBLIC SYNONYM ROLE, SYSTEM AUDIT

Apart from lot of benefits,  auditing may raise performance issues and the reasons are:

• Too much activity is being audited
• AUD$ table still placed in the SYSTEM tablespace
• Oracle bugs.

1. Too much is being audited

The more activity you audit the more audit records will generate.  You need to restrict unnecessary auditing because it cost your resources and hamper performance. To see which activities are being audited

  

SELECT  * FROM DBA_PRIV_AUDIT_OPTS UNION SELECT * FROM DBA_STMT_AUDIT_OPTS;

2. AUD$ table still placed in SYSTEM tablespace:

By default AUD$ table lies in SYSTEM tablespace.  You will face space managment issue if you not move AUD$ table in SYSAUX tablespace along with indexes.  Use Oracle recommended package DBMS_AUDIT  to purge old unnecessary entries. Periodically shrink / truncate AUD$ table.

 BEGIN

  DBMS_AUDIT_MGMT.set_audit_trail_location(

    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,

    audit_trail_location_value => 'SYSAUX');

END;

/

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/

CREATE TABLE AUD_BACKUP AS SELECT * FROM AUD$;

TRUNCATE AUD$;

INSERT INTO AUD$ SELECT * FROM AUD_BACKUP;

DROP TABLE AUD_BACKUP PURGE;

3. Oracle bugs

Well there are couple of  known bugs, so apply latest patch.

Resources:

1.  http://juliandontcheff.wordpress.com/2011/05/12/auditing-vs-performance-in-the-oracle-database 
2. Oracle support - [ID 1375419.1, 1080112.1, 1375419.1]
3. http://www.oracle-base.com/articles/11g/auditing-enhancements-11gr2.php#purging_audit_trail_records 

Alert: ASH performed an emergency flush

Alert

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 123...7 bytes.

Cause

This alert indicate sudden increase of database active sessions,  that fill-up ASH buffer faster then usual and therefore raise this alert. Actually this is not a problem but an indication of more  ASH buffer is needed to  support peak activity on DB.

Solution

 _ASH_SIZE is a hidden parameter and oracle not suggested to set such parameter without consulting their support. There is a meta link doc ( id 1385872.1) suggest we can increase 50% of current  ASH buffer, if we repeatedly receive this alert.   To monitor use this 

SELECT total_size,awr_flush_emergency_count FROM v$ash_info;

Oracle Wallet: secure external password storage

Some times we need database connection from shell script stored on file system. This can be a security issue, if the script contains database connection credential. To nullify this problem oracle provide a solution called wallet. Oracle wallet is a client-side secure external password container where DB login credentials are stored. Using this shell scripts can connect to DB using the "/@db_alias" syntax.

Step 1 : Set location for wallet

we Like to put the wallet files in $ORACLE_HOME/network/admin. Thus the location will be '/oracle/product/11.2.0/dbhome_1/network/admin'. Add following lines to sqlnet.ora

WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/oracle/product/11.2.0/dbhome_1/network/admin)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0


Step 2: Set DB alias

Add the following lines to listerer.ora

ora_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA.DB1.ORACLE.COM) ) )


Step 3: Create Wallet

mkstore -wrl '/oracle/product/11.2.0/dbhome_1/network/admin' -create

You will ask to enter password. The password length must be 8+ containing alpha-numeric characters.


Step 4: Add database login credentials into wallet

mkstore -wrl '/oracle/product/11.2.0/dbhome_1/network/admin' -createCredential ora_db scott tiger

This will ask for password conformation and you shout give the same password which you gave when creating wallet.


Step 5: Listing credentials present in wallet 

mkstore -wrl '/oracle/product/11.2.0/dbhome_1/network/admin' -listCredential


Step 6: Connect Db using wallet

sqlplus /@ora_db

Oracle Recovery Manager (RMAN) Overview

In general, backup & recovery combines several strategies & procedures which are protecting database against data lose and reconstructing the database after any kind of data loss.


There are two types of backup strategies:

1. Physical Backup – are backups of the physical files used in restoring &recovering database, such as datafiles, controlfiles and archived redo logs. 
2. Logical Backup – contains logical data exported from database with oracle utilities (i.e, datapump) and stored in a binary file.

Physical backup is the foundation of any sound backup & recovery strategy. Logical backup is a useful supplement to physical backup but not ensure sufficient protection against data loss without physical backup. Unless otherwise specified, the term backup refers to physical backup.


Oracle provides two backup & recovery solutions –

• Recovery Manager (RMAN) managed backup & recovery.
• User managed backup & recovery.

RMAN can take backups of –

1. Datafiles
2. Controlfiles
3. Redo logs / archived redo logs
4. Serve parameter file (SPFiles)

A database recovery involved two tasks:

• Restore- retrieve datafiles or controlfiles from backup.
• Recover- apply archived & on-line redo log changes on restored datafiles ( it is also known as media recovery)

DBMS_XMLGEN: SAve SQL query result in XML

DBMS_XMLGEN is a useful Oracle package that gives you a query result in XML file. Couple of days ago I have got a task to export all tables of an schema in different XML files. Here  is the PL/SQL code


grant read,write on directory DUMP_DIR to scott;
grant execute on DBMS_XMLGEN to scott;
grant execute on utl_file to scott;

set serveroutput on;
set echo on;
set timing on;
spool export_schema_xml.log;
DECLARE
  qryCtx DBMS_XMLGEN.ctxHandle;
  l_output utl_file.file_type;
  l_amount NUMBER default 4000;
  l_offset NUMBER(38) default 1;
  l_length NUMBER(38);
  sqltext VARCHAR2 (4000 CHAR);
  result CLOB;
  l_buffer VARCHAR2 (4000 CHAR);
BEGIN
FOR x IN (SELECT x.table_name FROM user_tables x)
LOOP     
  l_offset :=1;
  sqltext := 'SELECT * FROM '||x.table_name;
  qryCtx :=  dbms_xmlgen.newContext (sqltext);
  DBMS_XMLGEN.SETCONVERTSPECIALCHARS (qryCtx,FALSE);
  result :=  DBMS_XMLGEN.getXML(qryCtx);
  l_output := utl_file.fopen('DUMP_DIR', x.table_name||'.xml', 'w', 32760);
  l_length:=nvl(dbms_lob.getlength(result),0);
  WHILE ( l_offset < l_length AND l_length > 0 )
  LOOP
    DBMS_LOB.READ (result, l_amount, l_offset, l_buffer);
    utl_file.put (l_output, l_buffer);
    utl_file.fflush(l_output);
      utl_file.fflush(l_output);
    l_offset := l_offset + l_amount;
  END LOOP;
  utl_file.fflush(l_output);
  utl_file.fclose(l_output);
  DBMS_XMLGEN.CLOSECONTEXT (qryCtx);
  dbms_output.put_line(x.table_name||' Data Exported');
END LOOP;
END;
/

spool off;

ORA-03113: end-of-file on communication channel

ERROR
ORA-03113: end-of-file on communication channel
Process ID: 28105
Session ID: 130 Serial number: 5

Cause: This error may pop-up when you trying to start-up a database after executing 'shutdown abort' or unexpected shutdown of db due to powe failure. This error occurs when oracle fail to archive online redo log file.

Action: if your db is in archivelog mode then bring the db in noarchivelog mode and startup the db. Then, execute a normal shutdown and bring back the db in archivelog mode.

SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

Finding Duplicate SQL

The presence of duplicate SQL indicate that there are some SQL statements which doesn't have Bind variables. These duplicate SQL can raise performance issue because they will increase the number of hard parse in database.

ORACLE 10g introduced two new columns in v$sql view, which can help to identifing duplicate SQL more accurately. Those two new columns are:

1. force_matching_signature 
2. exact_matching_signature

exact_matching_signature - If two or more SQL has same value in this column, ORACLE assumes they are same after making some cosmetic adjustments (removing white space, uppercasing all keywords etc) to them. The is simmiler, when parameter cursor_sharing is set to EXACT.

force_matching_signature - the same value in this column (excluding 0) marks SQLs that ORACLE will consider they are same when it replaces all literals with binds (that is, if cursor_sharing=FORCE).



SELECT sql_text , count(1)
FROM v$sql
WHERE force_matching_signature > 0
  AND force_matching_signature <> exact_matching_signature
GROUP BY sql_text
HAVING count(1) > 10
ORDER BY 2;