How to Troubleshoot Oracle Wait Event enq: TX - allocate ITL entry

Problem Description

How to Troubleshoot Oracle Wait Event enq: TX - allocate ITL entry

Explanation

ITL (Interested Transaction List) is related to every Data & Index Block. Before a Transaction begin it need to acquire the ITL on the block where the row need to be modified.

There is a small section in the block header of every DATA & INDEX block which has this ITL information (defined by the INITRANS & MAXTRANS parameter).

The ITL structure is created in the block during segment creation as per the INITRANS value and grows dynamically as concurrency occurs to a maximum of MAXTRANS value.

For every single transaction on that block there will be one ITL entry.

For example, if a INITRANS is 5 then there will be 5 ITL rows/entries in each block of that segment.

A session  waits for this event when it is unable to acquire an ITL entry in a block. The primary cause of ITL waits is that free slots in the ITL structure in the block are not available and it is unable to expand the ITL slots.

The lack of slots can be due to

a.       Low setting of the INITRANS & MAXTRANS, which reserves minimal ITL slots initially and place a hard limit on the number of transactions that can have locks on a block respectively

b.      And/Or, the block is so packed that there is no room for the ITL to grow

Troubleshooting Approach/Solution

Step 1

Find the object and the block related to this Wait Event

select a.sid,a.serial#,a.event,a.sql_id,a.ROW_WAIT_OBJ#, a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,b.owner,b.object_type,b.object_name

from v$session a, dba_objects b

where a.event='enq: TX - allocate ITL entry' and a.ROW_WAIT_OBJ#=b.object_id;

Step 2

Find the INITRANS & MAXTRANS of the object

Table

select owner,table_name,INI_TRANS,MAX_TRANS from dba_tables where owner='SCOTT' and table_name='EMP';

Index

select owner,index_name,INI_TRANS,MAX_TRANS from dba_indexes where owner='SCOTT' and index_name='PK_EMP_IDX';

Step 3

Find the actual ITL slots available in the block header by dumping the block. You need to check the maximum available ITL slots and how many of them are free

              Command to Dump the Block

Alter system dump datafile block ;

              Find the Trace File Name

SELECT value FROM   v$diag_info WHERE  name = 'Default Trace File';

Identify the trace file and check for the ITL section (Note : A sample view of the ITL is provided below)

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

0x02   0x000a.01b.00000232  0x00c158cc.005c.2a  --U-    4  fsc 0x0000.000e593b

Step 4

You may want to suggest the following solution (depending on the scenario) to resolve this issue

a.       Increase INITRANS

b.      Increase PCTFREE

c.       Increase both INITRANS & PCTFREE

Note :

a.       All the above solution need a rebuild to take the impact

b.      Remember ASMM which may be part of your database.

c.       Increasing either INITRANS & PCTFREE is going to impact the block capacity in storing the rows

Oracle - Row Source Operation

Overview

Generally, in Performance Tuning thoery, Row Source & Record set are two different terminology which some time can be mis-perceived or mis-interpretted. According to me, ROW SOURCE is a superset which has more potential information related to performance statistics which helps to diagnose, troubleshoot in query tuning. On the contrary, RECORDSET is simply a set of rows/records fetched in a particular operation of an execution plan.

Even some Performance Tuning document/book talk about OPTIMIZER which decides the execution plan based on the ROW SOURCE. ROW SOURCE that is being referred here is just record set, i.e. number of rows that may result out of an operation in an execution plan.

Having said what is RECORDSET, ROW SOURCE provides additional information related to reads, writes and importantly "Elapsed Time" for each operation in an execution plan. ROW SOURCE contains execution statistics from performance perspective and is actually associated with an execution of SQL Query.

Where can I find this Row Source information?

Usually this Row Source information is accessed by generating a TKPROF output on a session trace file.
Fo eg. You can trace a session using 10046 event.

Note : Focus is retained to ROW SOURCE hence I am not providing more information on how to trace a session.

The below information is a copy paste.



Rows       Row Source Operation
-------     ---------------------------------------------------
0              LOAD TABLE CONVENTIONAL (cr=14 pr=6 pw=0 time=0 us)
199          NESTED LOOPS (cr=0 pr=0 pw=0 time=43281016 us cost=1 size=548 card=1)
39800      MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=103272 us cost=1 size=496 card=1)
199          HASH JOIN (cr=0 pr=0 pw=0 time=3960 us cost=1 size=466 card=1)
199          HASH JOIN (cr=0 pr=0 pw=0 time=594 us cost=1 size=349 card=1)
199          FIXED TABLE FULL X$KCCFN (cr=0 pr=0 pw=0 time=297 us cost=0 size=310 card=1)
400          FIXED TABLE FULL X$KCBFWAIT (cr=0 pr=0 pw=0 time=199 us cost=0 size=3900 card=100)
200          FIXED TABLE FULL X$KCFIO (cr=0 pr=0 pw=0 time=995 us cost=0 size=11700 card=100)
39800      BUFFER SORT (cr=0 pr=0 pw=0 time=34899 us cost=1 size=3000 card=100)
200          FIXED TABLE FULL X$KCCTS (cr=0 pr=0 pw=0 time=99 us cost=0 size=3000 card=100)
199          FIXED TABLE FIXED INDEX X$KCCFE (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=52 card=1)


New Feature in 10g

However, starting from 10g onwards, Oracle has provided the following two views using which we can use to obtain the "Row Source Operation" information without tracing.

a. V$SQL_PLAN_STATISTICS
b. V$SQL_PLAN_STATISTICS_ALL

Note : Remember that Oracle will populate these views with these performance statistics only when using STATISTICS_LEVEL=ALL


Eg.
alter session set STATISTICS_LEVEL=ALL;

Example.
select * from table(dbms_xplan.display_cursor('',NULL));
col a form 999999 head "Rows"
col b form a65 head "Row Source Operation"
select
last_output_rows a,
operation||' '||object_name||' (cr='||to_char(last_cr_buffer_gets)||' pr='||to_char(last_disk_reads)||' pw='||to_char(last_disk_writes)||' time='||to_char(last_elapsed_time)||')' b
from v$sql_plan_statistics_all
where sql_id='' and chil_number=
order by id;


How to interpret this Row Source?

I am taking the below simple example to explain this. This example shows only one operation out of an entire execution plan.

Rows           Row Source Operation
-----------   ---------------------------------------------------
10000000   TABLE ACCESS FULL EMPLOYEE (cr=3000 pr=100000 pw=100 time=21000000)

My interpretation from this above example is
"Oracle took 21 seconds (From 9i onwards this number is represented in microseconds) to fetch 10 million rows"

What is the USE of Row Source?

Generally this ROW SOURCE is used to pin point which OPERATION has consumed more time and is an expensive.OPERATION here means the individual operation in an execution plan.

For Eg.
select OPERATION_ID,OPERATION||' 'OBJECT_NAME
from v$sql_plan where sql_id='ild_number=;

Let us assume a query is running long and you are expected to tune this query. However this is a BIG query running into two pages. It might be difficult to tune this query as you need to know the starting point of the problem. In this situation, Row Source can help to identify the operation which is taking a long time.

You need to find out the Row in the Row Source Operation whose "time" is big and drill down from there.

How DATA Dictionary is created in Oracle?

I feel silly when I presumed the below information was already available in the blog/internet. However later I realized that it is NOT and hence it prompted me to add this blog.

Question : What is DATA Dictionary in an Oracle Database?


Answer : DATA Dictionary is also called METADATA. Oracle documentation tells that METADATA is created by running catalog.sql. While this is partially TRUE, the catalog.sql creates only the view/synonyms and few additional package/procedure/functions..also few more objects. However it certainly doesn't create the actual metadata. These views are created to access the metadata in a readable format. Metadata doesn't reside in the VIEWs rather it resides in the $(dollar) tables. For example, whatever information that you see by querying dba_objects view is majorly sourced from the dollar table called obj$. Likewise dba_tables view is sourced from tab$

In summary all the metadata resides in the DOLLAR table and they are not views.

Few examples,
obj$ - For objects
tab$ - For tables
ts$ - For tablespace
file$ - For datafiles
seg$ - For segments

Question : How & When DATA Dictionary is created in an Oracle Database?


Answer : DATA Dictionary is actually created during the Database creation time. After NOMOUNTING the instance you run the CREATE DATABASE command. This command, after creating the SYSTEM datafile it calls a SQL file ?/rdbms/admin/sql.bsq which creates the metadata

Different ways to invalidate a cursor in memory for Oracle database

Purpose : Whilst you may have a various different situation and need to invalidate a cursor from the oracle cache/memory at the instance level, I always had the need after pinning a SQL profile.

Note : Pinning a plan through SQL profile doesn't call for an explicit hard parsing.

Simple method/way to invalidate a cursor

1. Flush the SHARED_POOL

Eg.
alter system flush shared_pool;

2. Delete or Gather statistics of the related objects (table/index) which are referenced in the cursor (execution plan) using DBMS_STATS

3. Grant or Revoke privilege on the related objects (table) which are referenced in the cursor

Eg.
I always use the following commands together on the tables which are referenced in the SQL query/cursor

grant select on scott.emp to system;
revoke select on scott.emp from system;

4. Alter the related objects (table/index) which are referenced in the cursor using DDL command.

5. You can also PURGE the cursor from the shared pool

Eg.
exec dbms_shared_pool.purge(',','C');

The above methods helped me in the situation which I described in the PURPOSE section.

However the below alternate methods may also be considered depending on your situation.

6. You can even add a hint to the SQL query to create a new sql_id. Hence it will be a fresh parse.

Eg.
select /* query1 */ empno, ename from emp;
select /* query2 */ empno, ename from emp;


7. Ofcourse, always database BOUNCE will never let you down :-) Just an extreme step.

Keywords : parse, hard, soft, invalid cursor, invalidate cursor, memory, cache, oracle

Issues in Service Registration to SCAN listener

Scneario

In a 3 node RAC/Cluster environment, one node (in our case node 2) crashed resulting in relocating the SCAN listener to another available node. However 2 out of 3 SCAN listener shows all the services registered in it except the last one which got relocated.

Hence the client connectivity intermittently experiences ORA-12514 error. i.e. Service not registered/unknown

Cause

Failed over SCAN listener hasn't got all the services registered in it hence whenever a client connection comes to this scan listener it was unable to process/redirect to the correct instance.

Solution

Simply de-register all the services from the remote/scan listener and re-register the same.
This can be achieved using the below simple script which helped us from avoiding a big extended outage.

deregister =>    ALTER SYSTEM SET REMOTE_LISTENER='';
Re-register => ALTER SYSTEM SET REMOTE_LISTENER='plocdb14:50000';

How to pin a desired Plan for a query in Oracle database using SQL Profile

Overview

Starting since 10g people started preferring SQL Profile to pin the plan which seems to be more efficient than the traditional tool - STORED Outline

SQL Profile is more efficient, simple to use, more integrated to AWR.

Personally I feel SQL Profile is more convenient to use and my preferred tool to use as it is very quick and effective.

Methods to use

Method 1 - Below script uses SQL Profile to pin a plan to sqlid provided the plan is either available in memory or in AWR

PROMPT ============================== SQL Profile Inputs ==============================
set serveroutput on
ACCEPT dst_sql_id       PROMPT "Enter Destination SQL_ID: "
ACCEPT src_sql_id       PROMPT "Enter Source SQL_ID: "
ACCEPT plan_hash_value  PROMPT "Enter Source PLAN_HASH_VALUE: "
ACCEPT force_match      PROMPT "Enter FORCE_MATCH option (TRUE/FALSE), with TRUE equivalent to CURSOR_SHARING of FORCE: "
PROMPT ================================================================================
DECLARE
   c_prof_name_prefix  CONSTANT  VARCHAR2(5)  := 'SQLT';
   c_category          CONSTANT  VARCHAR2(10)  := 'DEFAULT';
   c_validate          CONSTANT  BOOLEAN       := TRUE;
   c_replace           CONSTANT  BOOLEAN       := TRUE;
   l_dst_sql_id           VARCHAR2(20)      := '&&dst_sql_id';
   l_src_sql_id           VARCHAR2(20)      := '&&src_sql_id';
   l_plan_hash_value      NUMBER            := &&plan_hash_value;
   l_plan_hash_value_chk  NUMBER;
   l_force_match_str      VARCHAR2(10)      := '&&force_match';
   l_profile_hints        SYS.SQLPROF_ATTR;
   l_dst_sql_text         CLOB;
   l_src_sql_text         CLOB;
   l_signature            NUMBER;
   l_force_match          BOOLEAN;
   l_profile_name         VARCHAR2(30)      := c_prof_name_prefix || '_' || l_dst_sql_id || '_' || l_plan_hash_value;
   l_profile_desc         VARCHAR2(50);
   err_dst_sql_id           EXCEPTION;
   err_src_sql_id           EXCEPTION;
   err_src_plan_hash_value  EXCEPTION;
   CURSOR sc_dst_sql_text IS
   SELECT sql_text
     FROM (SELECT sql_fulltext  sql_text
             FROM gv$sql
            WHERE sql_id = l_dst_sql_id
            UNION ALL
           SELECT sql_text
             FROM dba_hist_sqltext
            WHERE sql_id = l_dst_sql_id
          )
    WHERE ROWNUM = 1;
   CURSOR sc_src_sql_text IS
   SELECT sql_text
     FROM (SELECT sql_fulltext  sql_text
             FROM gv$sql
            WHERE sql_id = l_src_sql_id
            UNION ALL
           SELECT sql_text
             FROM dba_hist_sqltext
            WHERE sql_id = l_src_sql_id
          )
    WHERE ROWNUM = 1;
   CURSOR sc_src_plan_hash_value IS
   SELECT plan_hash_value
     FROM gv$sql_plan
    WHERE sql_id = l_src_sql_id
      AND plan_hash_value = l_plan_hash_value
      AND other_xml IS NOT NULL
    UNION
   SELECT plan_hash_value
     FROM dba_hist_sql_plan
    WHERE sql_id = l_src_sql_id
      AND plan_hash_value = l_plan_hash_value
      AND other_xml IS NOT NULL;
   CURSOR sc_profile_attr IS
   SELECT category
        , type
        , created
        , last_modified
        , status
        , force_matching
        , signature
        , description
     FROM dba_sql_profiles
    WHERE name = l_profile_name;
   CURSOR sc_prof_hints IS
   SELECT SUBSTR(attr_val, 1, 120) hint
     FROM sys.sqlprof$attr
    WHERE signature = l_signature
    ORDER BY category
           , attr#;
BEGIN
   OPEN sc_dst_sql_text;
   FETCH sc_dst_sql_text INTO l_dst_sql_text;
   CLOSE sc_dst_sql_text;
   IF l_dst_sql_text IS NULL THEN
      RAISE err_dst_sql_id;
   END IF;
   OPEN sc_src_sql_text;
   FETCH sc_src_sql_text INTO l_src_sql_text;
   CLOSE sc_src_sql_text;
   IF l_src_sql_text IS NULL THEN
      RAISE err_src_sql_id;
   END IF;
   OPEN sc_src_plan_hash_value;
   FETCH sc_src_plan_hash_value INTO l_plan_hash_value_chk;
   CLOSE sc_src_plan_hash_value;
   IF l_plan_hash_value_chk IS NULL THEN
      RAISE err_src_plan_hash_value;
   END IF;
   SELECT EXTRACTVALUE(VALUE(sub1), '/hint')  AS outline_hints
     BULK COLLECT
     INTO l_profile_hints
     FROM XMLTABLE('/*/outline_data/hint'
                   PASSING (SELECT xmltype(other_xml)  AS xmlval
                              FROM (SELECT other_xml
                                      FROM gv$sql_plan
                                     WHERE sql_id = l_src_sql_id
                                       AND plan_hash_value = l_plan_hash_value
                                       AND other_xml IS NOT NULL
                                     UNION ALL
                                    SELECT other_xml
                                      FROM dba_hist_sql_plan
                                     WHERE sql_id = l_src_sql_id
                                       AND plan_hash_value = l_plan_hash_value
                                       AND other_xml IS NOT NULL
                                   )
                             WHERE ROWNUM = 1
                           )
                  ) sub1;
   IF UPPER(l_force_match_str) = 'TRUE' THEN
      l_force_match := TRUE;
   ELSE
      l_force_match := FALSE;
   END IF;
   l_signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(l_dst_sql_text);
   l_profile_desc := l_profile_name || '_' || l_signature;
   DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text     => l_dst_sql_text
                                 , profile      => l_profile_hints
                                 , name         => l_profile_name
                                 , description  => l_profile_desc
                                 , category     => c_category
                                 , validate     => c_validate
                                 , replace      => c_replace
                                 , force_match  => l_force_match
                                  );
   DBMS_OUTPUT.PUT_LINE('SQL Profile ' || l_profile_name || ' successfully created');
   DBMS_OUTPUT.PUT_LINE('................................................................................');
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE('Destination SQL Text');
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE(l_dst_sql_text);
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE('Source SQL Text');
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE(l_src_sql_text);
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE('Attributes');
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   FOR x IN sc_profile_attr LOOP
      DBMS_OUTPUT.PUT_LINE('Category:        ' || x.category);
      DBMS_OUTPUT.PUT_LINE('Type:            ' || x.type);
      DBMS_OUTPUT.PUT_LINE('Created:         ' || x.created);
      DBMS_OUTPUT.PUT_LINE('Last Modified:   ' || x.last_modified);
      DBMS_OUTPUT.PUT_LINE('Status:          ' || x.status);
      DBMS_OUTPUT.PUT_LINE('Force Matching:  ' || x.force_matching);
      DBMS_OUTPUT.PUT_LINE('Signature:       ' || x.signature);
      DBMS_OUTPUT.PUT_LINE('Description:     ' || x.description);
   END LOOP; 
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE('Hints');
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   FOR x IN sc_prof_hints LOOP
      DBMS_OUTPUT.PUT_LINE(x.hint);
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('================================================================================');
EXCEPTION
   WHEN err_dst_sql_id THEN
      DBMS_OUTPUT.PUT_LINE('Error:  Destination SQL_ID "' || l_dst_sql_id || '" not found.');
   WHEN err_src_sql_id THEN
      DBMS_OUTPUT.PUT_LINE('Error:  Source SQL_ID "' || l_src_sql_id || '" not found.');
   WHEN err_src_plan_hash_value THEN
      DBMS_OUTPUT.PUT_LINE('Error:  Source PLAN_HASH_VALUE "' || l_plan_hash_value || '" not found.');
   WHEN OTHERS THEN
      RAISE;
END;
/


Method 2 - There is also a backdoor method to create a SQL profile if the plan is not available in either place, which can be done using SQLT, which again uses IMPORT_SQL_PROFILE with manually created SQLPROF_ATTR (i.e. SQL Profile Attribute)

Below example need to properly converted according to your scenario.

SET DEF ^ ECHO ON TERM ON LIN 2000 TRIMS ON SERVEROUT ON SIZE 1000000 TIM OFF SQLP SQL>;
SPO SQLT_2wskdsyt6w2ty_p4150230984.log;
SET ECHO OFF;
PRO
PRO ... Generating manual custom SQL Profile for SQL text provided
PRO
SET TERM OFF HEA ON TIM OFF;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
VAR signature NUMBER;
DECLARE
  sql_txt VARCHAR2(32767);
  h       SYS.SQLPROF_ATTR;
BEGIN
  sql_txt := q'[SELECT  /*YANTRA*/    YFS_ORDER_HEADER.ORDER_HEADER_KEY  FROM YFS_ORDER_HEADER YFS_ORDER_HEADER          WHERE PAYMENT_STATUS IN ( :"SYS_B_00",:"SYS_B_01",:"SYS_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11")  AND AUTHORIZATION_EXPIRATION_DATE <= :"SYS_B_12"  AND ENTERPRISE_KEY = :"SYS_B_13"  AND DOCUMENT_TYPE = :"SYS_B_14"  AND NOT EXISTS ( SELECT :"SYS_B_15" FROM YFS_ORDER_HOLD_TYPE WHERE YFS_ORDER_HOLD_TYPE.ORDER_HEADER_KEY = YFS_ORDER_HEADER.ORDER_HEADER_KEY AND HOLD_TYPE IN ( :"SYS_B_16",:"SYS_B_17" ) AND STATUS < :"SYS_B_18") ]';
  h := SYS.SQLPROF_ATTR(
      q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
      q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]',
      q'[OPT_PARAM('optimizer_dynamic_sampling' 1)]',
      q'[OPT_PARAM('optimizer_index_cost_adj' 50)]',
      q'[OUTLINE_LEAF(@"SEL$5DA710D3")]',
      q'[UNNEST(@"SEL$2")]',
      q'[OUTLINE(@"SEL$1")]',
      q'[OUTLINE(@"SEL$2")]',
      q'[INDEX(@"SEL$5DA710D3" mailto:%22YFS_ORDER_HEADER%22@%22SEL$1" ("YFS_ORDER_HEADER"."ENTERPRISE_KEY" "YFS_ORDER_HEADER"."DOCUMENT_TYPE" "YFS_ORDER_HEADER"."PAYMENT_STATUS" "YFS_ORDER_HEADER"."AUTHORIZATION_EXPIRATION_DATE" "YFS_ORDER_HEADER"."ORDER_HEADER_KEY"))]',
      q'[INDEX_RS_ASC(@"SEL$5DA710D3" mailto:%22YFS_ORDER_HOLD_TYPE%22@%22SEL$2" ("YFS_ORDER_HOLD_TYPE"."ORDER_HEADER_KEY" "YFS_ORDER_HOLD_TYPE"."ORDER_LINE_KEY" "YFS_ORDER_HOLD_TYPE"."HOLD_TYPE"))]',
      q'[LEADING(@"SEL$5DA710D3" mailto:%22YFS_ORDER_HEADER%22@%22SEL$1" mailto:%22YFS_ORDER_HOLD_TYPE%22@%22SEL$2%22)]',
      q'[USE_NL(@"SEL$5DA710D3" mailto:%22YFS_ORDER_HOLD_TYPE%22@%22SEL$2%22)]'
  );
  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
    sql_text    => sql_txt,
    profile     => h,
    name        => 'SQLT_2wskdsyt6w2ty_p4150230984',
    description => 'SQLT 2wskdsyt6w2ty p4150230984'||:signature,
    category    => 'DEFAULT',
    validate    => TRUE,
    replace     => TRUE,
    force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
 

Key points to consider for Oracle RAC/Cluster in Linux

1. Ensure Max. Open Files is set to 65k for Oracle Cluster.
Reference
Oracle Bug # 8429716
Oracle Metalink Note id # 858279.1

You may see timeout errors like below in the crsd.log file.
Important : Oracle Metalink note id # 732086.1 & Bug 6196746 may misguide you.

2010-12-07 03:14:54.776: [ CRSEVT][1494178112] CAAMonitorHandler :: 0:Action Script /u01/crs/oracle/product/crs/bin/racgwrap(check) timed out for

ora.dlp34db.vip! (timeout=60)
2010-12-07 03:14:54.776: [ CRSAPP][1494178112] CheckResource error for ora.dlp34db.vip error code = -2
2010-12-07 03:14:54.846: [ CRSEVT][1492076864] CAAMonitorHandler :: 0:Could not join /u01/crs/oracle/product/crs/bin/racgwrap(check)
category: 1234, operation: scls_process_join, loc: childcrash, OS error: 0, other: Abnormal termination of the child


2. Increase DIAGWAIT. This avoids failure in dumping log information prior to Node Eviction by giving sufficient time.
Eventually it helps to troubleshoot the root cause of Node Eviction
Reference
Oracle Metalink Note id # 559365.1

crsctl set css diagwait 13 -force

3. Ensure HUGEPAGE is set appropriately.
Oracle Metalink Note Reference
Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration [ID 401749.1]
HugePages on 64-bit Linux [ID 361468.1]
HugePages on Linux: What It Is... and What It Is Not... [ID 361323.1]

4. Additional settings to fetch server level core information to diagnose the Server crash due to Oracle Cluster
a. Enable CSSD core by following Oracle Metalink Note 605449.1
b. Configure kdump by following Oracle Metalink Note 452067.1

SQLID level Tracing Feature in 11g

Now since Oracle 11g onwards you can trace all sessions by filtering a particular SQL ID or
set of SQL IDs

For example

alter session/system set events 'sql_trace [sql:g3yc1js3g2689|7ujay4u33g337] wait=true | false, bind=true | false, planstat=never | first_execution | all_executionss|level 12';

alter session/system set events 'sql_trace [sql:g3yc1js3g2689|7ujay4u33g337] off';

Trace will be generated upon running that particular SQL ID

Keywords : SQL ID, sqlid trace

Solution for long running query against DBA_EXTENTS to find the corrupted segment

Problem

You are running a query against DBA_EXTENTS to find the segment information (most probably you would be using
this to find a CORRUPTED segment/object) for a particular FILE_ID and BLOCK_ID.

Query might look something like this.

select owner,segment_name,segment_type
from dba_extents
where file_id =
and between block_id and (block_id+(blocks-1));

This query might run longer and sometimes you may lose your patience waiting for it.

Solution

• Dump the block and take the object id using the below command

alter system dump datafile block ;
Eg.alter system dump datafile 1811 block 20000;

FYI, this command generate a trace file in the user_dump_dest directory

• Grep the object number from the trace file.

Locate the trace file and grep the object id which will be presented in HEX value.

grep "seg/obj" op01csdb01_ora_13143.trc

seg/obj: 0xe024a2 csc: 0x878.a5583a53 itc: 50 flg: E typ: 1 - DATA

• Find the object information from DBA_OBJECTS

select owner,object_id,data_object_id,object_name,subobject_name

from dba_objects

where (object_id=to_number('E024A2', 'xxxxxx')

or

data_object_id=to_number('E024A2', 'xxxxxx')

);

Note : the count of 'x' should be equal to the number of characters in the source.

Eg. E024A2 - is 6 character string, hence i used 'xxxxxx'

Keywords : query; dba_extents; long; slow; corrupt ; segment; object

How to create duplicate database from a primary when the FULL backup happens out of standby

Scenario

1. You want to create a DUPLICATE database to perform PITR out of Primary PRODUCTION database. Duplicate/auxiliary instance is created in a different host.

2. FULL/DYNAMIC backup of primary production database goes out of STANDBY

Assumption

1. Backup of Production databae controlfile exists

Facts

In this scenario, I used the following command to perform PITR recovery in RETEK database.
1. Production database
a. op01rtdb01/2
b. primnode1/primnode2
2. Standby database
a. Op99rtdb01
b. standbynode1
c. Policy : rdc_oradb_qa
d. Netbackup master server : nbmstrnode1

Note : FULL database backup is performed out of op99rtdb01

3. Duplicate/Auxiliary database
a. op01drdb01
b. auxnode1

Commands

PFA the general commands for this sort PITR. This document would also cover the directory creation and other steps.
Otherwise, the key steps are

1. Login to auxnode1 as “oracle” unix user

2. . oraenv (type op01drdb01)

3. Startup nomount

rman

connect target SYS/@op01rtdb01 Note : I am connecting only to primary production database, though the FULL backup happens out of OP99RTDB01.

connect catalog rman10g/@op01emdb02

CONNECT AUXILIARY /

run
{
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=standbynode1,NB_ORA_CLASS=rdc_oradb_qa,NB_ORA_SERV=nbmstrnode1)';
allocate auxiliary channel t2 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=standbynode1,NB_ORA_CLASS=rdc_oradb_qa,NB_ORA_SERV=nbmstrnode1)';
allocate auxiliary channel t3 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=standbynode1,NB_ORA_CLASS=rdc_oradb_qa,NB_ORA_SERV=nbmstrnode1)';
allocate auxiliary channel t4 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=standbynode1,NB_ORA_CLASS=rdc_oradb_qa,NB_ORA_SERV=nbmstrnode1)';
set until time "to_date('2009-10-09 02:00:00','YYYY-MM-DD hh24:mi:ss')";
DUPLICATE TARGET DATABASE TO OP01DRDB
db_file_name_convert=('/u01/app/oracle/admin/oralink/op01rtdb/','/u01/app/oracle/admin/oralink/op01drdb/')
skip tablespace ARIDAT03,RTKIDX06,RTKIDXEX01,RTKIDXEX03,OUTLN01,RTKIDX02,RTKDAT02,ARIDAT02,RTKIDX08,RTKDATEX03,RTKDAT03,RTKIDX04,RTKIDX01,RTKDAT04,PMIDAT01,TOOLS01,ARIIDX02,
ARIIDX03,AUDDAT01,ARIDAT01,RTKDAT01,RTKDAT06,RTKIDX03,RTKIDXEX02,RTKIDX07,ARIIDX01,RTKIDX05,RTKDAT07,USERS01,RTKDAT05,RTKDATEX02,RTKDAT08
LOGFILE
GROUP 1 ('/u01/app/oracle/admin/oralink/op01drdb/op01drdb01_0101a.rdo','/u01/app/oracle/admin/oralink/op01drdb/op01drdb01_0101b.rdo') SIZE 511M,
GROUP 2 ('/u01/app/oracle/admin/oralink/op01drdb/op01drdb01_0201a.rdo','/u01/app/oracle/admin/oralink/op01drdb/op01drdb01_0201b.rdo') SIZE 511M,
GROUP 3 ('/u01/app/oracle/admin/oralink/op01drdb/op01drdb01_0301a.rdo','/u01/app/oracle/admin/oralink/op01drdb/op01drdb01_0301b.rdo') SIZE 511M REUSE;
}

Note : To get the value of the parameter marked in RED, you need to go to op99rtdb01/standbynode1:/usr/openv/netbackup/logs/user_ops/dbext/logs
Grep for rdc_oradb_qa and CAT any of the file.

standbynode1:[op99rtdb01]:/usr/openv/netbackup/logs/user_ops/dbext/logs>more 709.0.1264176793
Backup started Fri Jan 22 10:13:13 2010


10:14:11 INF - Backup id = standbynode1_1264176842
10:14:11 INF - Backup time = 1264176842
10:14:11 INF - Policy name = rdc_oradb_qa
10:14:11 INF - Snapshot = 0
10:14:12 INF - Frozen image = 0
10:14:12 INF - Backup copy = 0
10:14:12 INF - Master server = nbmstrnode1

Key words : create ; auxiliary ; duplicate ; standby ; dataguard ; full ; backup ;

Tune your Query using Oracle SQL Profile

SQL Profiles are more preferred than OUTLINE as it is simple to use and manage.
You may find my below steps useful to start with SQL Profile. I used this to tune a single query to generate & apply SQL Profile to improve the execution plan.

Note : Here I am trying to generate SQL Profile for one single SQL statement. If you want to generate SQL Profile for multiple SQL statements then you need to create a SQL Tuning Set and then run this.
a. Create a new STS
b. Load the STS
c. Select the STS to review the contents
d. Update the STS if necessary
e. Create a tuning task with the STS as input

Overall Logical Steps to create a SQL Profile through SQL Tuning Advisor

1) You use the CREATE_TUNING_TASK Functions to create a tuning task for tuning a single statement or a group of SQL statements.
2) The EXECUTE_TUNING_TASK Procedure executes a previously created tuning task.
3) The REPORT_TUNING_TASK Function displays the results of a tuning task.
4) You use the SCRIPT_TUNING_TASK Function to create a SQL*PLUS script which can then be executed to implement a set of Advisor recommendations

1. Creating a SQL Tuning Task

Method 1

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ * ' 'FROM employees e, locations l, departments d ' 'WHERE e.department_id = d.department_id AND ' 'l.location_id = d.location_id AND ' 'e.employee_id < :bnd';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 1800,
task_name => 'my_sql_tuning_task',
description => 'Task to tune a query on a specified employee');
END;


Method 2

DECLARE
my_task_name VARCHAR2(30);
my_sqlid varchar2(30);
my_planhashvalue number;
BEGIN
my_sqlid := 'a59zbsyutxfg1';
my_planhashvalue := 343073668;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => my_sqlid,
plan_hash_value => my_planhashvalue,
scope => 'COMPREHENSIVE',
task_name => 'my_sql_tuning_task',
time_limit => 1800,
description => 'Task to tune a query');
END;


Method 3

DECLARE
my_task_name VARCHAR2(30);
my_beginsnap number;
my_endsnap number;
my_sqlid varchar2(30);
my_planhashvalue number;
BEGIN
my_sqlid := 'ffpyzcn9809s9';
my_planhashvalue := 2377894994;
my_beginsnap := 20134;
my_endsnap := 20136;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap => my_beginsnap,
end_snap => my_endsnap,
sql_id => my_sqlid,
plan_hash_value => my_planhashvalue,
scope => 'COMPREHENSIVE',
task_name => 'my_sql_tuning_task',
time_limit => 1800,
description => 'Task to tune a query');
END;


2. Executing a SQL Tuning Task

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;


3. Checking the Status of a SQL Tuning Task

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';

4. Checking the Progress of the SQL Tuning Advisor

SELECT sofar, totalwork
FROM V$ADVISOR_PROGRESS
WHERE task_name = 'my_sql_tuning_task';


5. Displaying the Results of a SQL Tuning Task

SET LONG 9999999
SET LONGCHUNKSIZE 99999999
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task') FROM DUAL;

6. Accept SQL Profile

The above report might advise SQL Profile if found anything.It can then be implemented using.

execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task', replace => TRUE);

6. How to manage(enable/disable) a SQL PROFILE

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name =>'SYS_SQLPROF_014845b914845b98',
attribute_name => 'STATUS',
value => 'DISABLED');
END;


BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name =>'SYS_SQLPROF_01487a4c1487a4cb',
attribute_name => 'STATUS',
value => 'ENABLED');
END;



Keywords : Oracle; SQL profile ;sqlprofile ; tune; query

Quick Commands to copy DATAFILE or ARCHIVELOG file from ASM to unix filesystem

In Oracle 10g,

RMAN Solution

I. To Copy Datafile from ASM to filesystem

run{
allocate channel c1 type disk;
copy datafile '+DATA01/datafile/dat01.577.687959027' to /tmp/dat01.577.687959027';
}

II. To Copy archive log file from ASM to filesystem

backup as copy archivelog from sequence 23676 until sequence 23680 format '/tmp/archivelog/informatica%h_%e_resetlogs.arc';

PLSQL Solution using DBMS_FILE_TRANSFER

Create a directory object to point the source

create or replace directory SDIR as '+DATA01/datafile/';

Note : In my example, the database datafile which i wanted to copy from ASM to filesystem was residing in '+DATA01/datafile/'

Create a directory object to point the destination

create or replace directory DDIR as '/tmp';

Copy the file

BEGIN
dbms_file_transfer.copy_file(source_directory_object =>'SDIR',
source_file_name => 'dat01.577.687959027',
destination_directory_object => 'DDIR',
destination_file_name => 'dat01.dbf');
END;

In Oracle 11g,

Apart from the above compatible commands, ASMCMD command line utility has been enhanced with "cp" copy command.

ASMCMD[+]>cp +DATA01/datafile/dat01.577.687959027 /tmp/dat01.dbf

Keywords : copy; file; asm; filesystem; datafile; archive; archivelog

Unit size in DBA_HIST_TBSPC_SPACE_USAGE

Question : What is the unit of the below columns in DBA_HIST_TBSPC_SPACE_USAGE?
a. tablespace_size
b. tablespace_maxsize
c. tablespace_usedsize

Answer : They are reprensted in no. of blocks. In other words to get the actual size from this view you need to multiply the values with the DBA_TABLESPACES.BLOCK_SIZE

I used this simple query ( you can tune this query if you see it running slow) to get the tablespace growth report on weekly basis.

select /*+ parallel(c, 5) */
to_char(c.BEGIN_INTERVAL_TIME,'MON'),
to_char(c.BEGIN_INTERVAL_TIME,'WW'),
b.name,
round(max(a.tablespace_size*d.block_size)/1024/1024) tblsize_in_MB,
round(max(a.tablespace_maxsize*d.block_size)/1024/1024) totalsize_in_MB,
round(max(a.tablespace_usedsize*d.block_size)/1024/1024) usedsize_in_MB
from DBA_HIST_TBSPC_SPACE_USAGE a, v$tablespace b,
DBA_HIST_SNAPSHOT c, dba_tablespaces d
where a.tablespace_id=b.ts#
and a.snap_id=c.snap_id
and b.name=d.tablespace_name
and b.name in ('USERS')
group by to_char(c.BEGIN_INTERVAL_TIME,'MON'),
to_char(c.BEGIN_INTERVAL_TIME,'WW'),
b.name
order by 3,2,1;


Keywords : DBA_HIST_TBSPC_SPACE_USAGE, size, unit

STORED OUTLINE in Oracle 10g

Scope of this document

1. This applies to Oracle 10g
2. How to use STORED Outline to fix a performance issue?
3. Ho to change the plan in the outline after generating it?
4. Some Pros & Cons of STORED OUTLINE
   

This document also highlight some of the key facts that an Oracle DBA should be aware of while using STORED OUTLINE.

Facts

1. When the desired execution plan is occuring for a SQL statement which is not identical in SQL text (whitespacing and case differences ignored) to the original SQL statement, then a Stored Outline for that SQL is not usable as the signature of the Stored Outline does not match
2. In other words, STORED OUTLINE are driven by the SIGNATURE of an inidivual SQL statement.
3. To create private stored outline do not use the procedure DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES in Oracle10g and Oracle11g
   
4. DBMS_OUTLN.CREATE_OUTLINE procedure is hard parsing and using the optimizer environment of the session executing the procedure instead of using the one from the library cache. This is a BUG. Hence the stored outline created out of this procedure might differ from the original plan.
   
5. Unlike the traditional way, creating the stored outline in the lower environment and do export/import to get the outline to production, Oracle 10g has got the new way of creating it directly using the SQL ID, SQL_HASH_VALUE, SQL_CHILD_NUMBEr from the memory.
   
6. STORED OUTLINES doesnot work when you try it as SYS user.
   
7. Important : Though outlines are used and you follow all the steps correctly, NOT ALWAYS the execution plan is imposed as expected/desired.
   
8. When swapping the outline (to replace the BAD plan with a GOOD plan) ensure the HINTCOUNT column is taken care.
   
9. Generally you may have read CURSOR_SHARING = FORCE disables the use of stored outlines. Actually the interpretation should be the other way.
   i.e.
   STORED OUTLINE creation also captures the essential optimizer paramaters which is also assessed before overwriting a plan.
   If there is a mismatch then STORED OUTLINE will not be used for a SQL statement.
   For Eg.
   If OL1 is created with CURSOR_SHARING=FORCE, then a session with CURSOR_SHARING=EXACT/SIMILAR would not be able to use the outline OL1.
   In this scenario STORED OUTLINE is disabled.
   
10. PRIVATE outlines are automatically created in SYSTEM.OL$ table and this is a local temporary table. Hence the records vanishes the moment you exit the session
    
11. To use stored outline for your SQL statements a. alter session set use_stored_outlines=true; b. Ensure CURSOR_SHARING is NOT set to FORCE
    

Scenario

I have a single query which is using a BAD plan in the production database.You have the SQL_ID, SQL_HASH_VALUE & SQL_CHILD_NUMBER of this query which is using the BAD plan.

On the other side you either tune it by adding HINTS or have another version of this same query which is runinng fine in the same production database. What do I mean by another version of this same query?

For example, I ran the same SQL statement from another SQL prompt after substituting the BIND variables with the literalsand confirmed the execution plan was GOOD.

Important Note : This GOOD one was having different SQL_ID & SQL_HASH_VALUE

Goal of this article,

a. From the same database, create the OUTLINE out of the SQL statement which is having the GOOD plan

b. Make the original query adopt the above outline
Remember, all these steps are done in the same production database and out of the buffer cache.

Assumptions:

a. connect to a schema with CREATE ANY OUTLINE privilege

b. Perform these tasks as any schema other than SYS

c. Using DEFAULT category

d. The schema should have privilege to query v$sql & v$session

e. HINTCOUNT is same for both GOOD & BAD plan

Overall Steps

a) Generate the outline, name it OL1, for the BAD plan using the seperate SQL_HASH_VALUE & SQL_CHILD_NUMBER.

b) Generate an another outline, name it OL2, for the GOOD plan using seperate SQL_HASH_VALUE & SQL_CHILD_NUMBER.
Eg.
GOOD : select * from emp; ===> OL1

BAD : select * from emp where ename='scott'; ===> OL2

Method 1

c) Update/Edit the outline OL2 by replacing with the values of OL1, so that both OL1 & OL2 should look alike.

Method 2

c) Rename the outline OL2 to OL1 and viceversa.

Method 3

c) Create a new private outline "PRIVOL2" out of OL2

d) Edit the private outline

f) resynchronize the private Stored Outline

g) Replace the original outline OL2 with this edited private outline "PRIVOL2"

Method 4

c) Create a new private outline "PRIVOL1" out of OL1

d) Create a new private outline "PRIVOL2" out of OL2

e) Edit the private outline

f) resynchronize the private Stored Outline

g) Replace the original outline OL2 with this edited private outline "PRIVOL2"

Detailed Steps

create table emp (empno number, ename varchar2(10)) tablespace users01;

create index empidx on emp(empno,ename) tablespace users01;

begin
for i in 1..200 loop
insert into emp values (i,'blake');
end loop;
commit;
end;
/

begin
for i in 1..200 loop
insert into emp values (i,'scott');
end loop;
commit;
end;
/

execute DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT', tabname => 'EMP', estimate_percent => 100, cascade => TRUE);

Open two seperate sqlplus session as SCOTT user
In Session 1 : select distinct sid from v$mystat; ----- Lets assume the SID # 514

In Session 1 : select * from emp;

In session 2 :

select sid,serial#,sql_id,sql_child_number,sql_hash_value,prev_sql_id,prev_hash_value,prev_child_number from v$session where sid=514;

In session 1 : select * from emp where ename='scott';

In session 2 :

select sid,serial#,sql_id,sql_child_number,sql_hash_value,prev_sql_id,prev_hash_value,prev_child_number from v$session where sid=514;

From the above exercise we got SQL ID, SQL HASH VALUE and CHILD NUMBER of both GOOD & BAD sql statement.

GOOD : select * from emp; ===> OL1

BAD : select * from emp where ename='scott'; ===> OL2

SQL_ID SQL_HASH_VALUE SQL_PLAN_HASH_VALUE SQL_CHILD_NUMBER SQL_TEXT============== ============== =================== ================ =====================================a2dk8bdn0ujx7 1745700775 2872589290 0 select * from emp;7h38thzz3wug9 4265503209 818394873 0 select * from emp where ename='badri'
select * from TABLE(dbms_xplan.display_cursor('a2dk8bdn0ujx7',0));
Plan hash value: 2872589290
-------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time -------------------------------------------------------------------------- 0 SELECT STATEMENT 2 (100) 1 TABLE ACCESS FULL EMP 60 540 2 (0) 00:00:01

select * from TABLE(dbms_xplan.display_cursor('7h38thzz3wug9',0));
Plan hash value: 818394873
--------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------- 0 SELECT STATEMENT 1 (100) * 1 INDEX FULL SCAN EMPIDX 20 180 1 (0) 00:00:01

Goal : To make the BAD sql statement to use FULL TABLE SCAN instead of INDEX FULL SCAN.
Steps Common to ALL methods

1) Create outline using the SQL HASH VALUE & CHILD NUMBER

alter session set create_stored_outlines=true;

exec dbms_outln.CREATE_OUTLINE(1745700775,0);

exec dbms_outln.CREATE_OUTLINE(4265503209,0);

alter session set create_stored_outlines=false;

2) Query them to confirm the presence
col ol_name form a30

col sql_text form a40

col category form a10

col hint_text form a75

set lines 200
select ol_name,hintcount,HASH_VALUE,CATEGORY,HASH_VALUE2,SIGNATURE,sql_text from outln.ol$;

select ol_name,hint#,hint_text from outln.ol$hints order by 1,2;

3) Rename them for easy use

alter outline SYS_OUTLINE_09061700365898670 rename to OL1;

commit;

alter outline SYS_OUTLINE_09061700365899172 rename to OL2;

commit;

Method 1

UPDATE outln.ol$hints a SET a.hint_text=(select b.hint_text from outln.ol$hints b where b.ol_name='OL1' and a.hint#=b.hint#) where a.ol_name='OL2'; and a.hint#=1;

commit;

Method 2

UPDATE outln.ol$hints SET ol_name = decode(ol_name, 'OL1','OL2','OL2','OL1') WHERE ol_name in ('OL1','OL2');

commit;

Method 3

create private outline PRIVOL2 from OL2;

commit;

update system.ol$hints set hint_text='FULL(@"SEL$1" http://www.blogger.com/'mailto: where ol_name='PRIVOL1' and hint#=6;

commit;

execute dbms_outln_edit.refresh_private_outline('PRIVOL2');

create or replace outline OL2 from private PRIVOL2;

Method 4

create private outline PRIVOL1 from OL1;

commit;

create private outline PRIVOL2 from OL2;

commit;

UPDATE system.ol$hints SET ol_name = decode(ol_name, 'PRIVOL1','PRIVOL2','PRIVOL2','PRIVOL1') WHERE ol_name in ('PRIVOL1','PRIVOL2');

commit;

execute dbms_outln_edit.refresh_private_outline('PRIVOL1');

commit;

execute dbms_outln_edit.refresh_private_outline('PRIVOL2');

commit;

create or replace outline OL2 from private PRIVOL2;

Keywords:

stored; outline; create;memory ; cache; edit;swap;dbms_outln.CREATE_OUTLINE

SELECT/DML operation on a Table HANGS

You would wonder why an operation on a particular table hangs for ever whereas the table has got just hundred thousand records.In this context operation means SELECT/INSERT/UPDATE/DELETE.
We had a scenario in our production environment where users were unable to transact on a particular table. As a first step, we confirmed that this HANG is specific only to the table and not for the entire Database. Subsequently we started with our below approach

a. Checked and confirmed that this table had the recent statistics which was generated on the day begining.

b. Opened a new session to run the query
"select count(*) from table_name

;"

We expected this to do a FTS (Full Table Scan).

c. Wait Events

We checked for any wait events in the database for that session as well as for the whole database, using the below command.
SELECT EVENT,COUNT(*) FROM V$SESSION_WAIT WHERE WAIT_TIME=0 GROUP BY EVENT;
We couldn't spot out any particular event affecting this hang as all where regular wait events. This particular session was waiting for DB FILE SEQUENTIAL READ wait event which we felt was an obvious though we expected to wait for DB FILE SCATTERED READ. as it should do a FTS.

d. Optimizer issue : DELETE STATISTICS

Cancelled the previous session, deleted the table statistics using DBMS_STATS.DELETE_TABLE_STATS and then ran the same query again. No change the same behaviour and same wait event.

e. 10046 event trace

Hence we decided to cancel the above session inorder to open a new one and to start doing 10046 trace to find out where it goes.

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
followed by the select count(*).

Trace file started displaying the wait event "DB FILE SEQUENTIAL READ" on the undo tablespace file. Here we got to know that server process is scanning undo blocks which would probably mean that it is doing a CONSISTENT READ. But neither a transactions is happening on that table nor a rollback at the background. After spotting the last few data blocks, which were before the server process decides to scan undo blocks, dumped those and found nothing suspecious.

ALTER SYSTEM DUMP DATAFILE file_no BLOCK block_number;

f. SYSTEMSTATE dump

Finally we decided to take a systemstate dump on the HANGING process.

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 266';

From the trace file we got a hint. The trace file got a blockdump which belongs to the table on which the problem was reported. The spurious fact which we identified from the blockdump was an active transaction in the ITL list whilst there is no active TXN on that table. We could easily guess that PMON could have failed to clean somewhere the failed txns. Hence we the solution was to clear the ITL.

g. Solution

We just ran a simple update followed by a rollback which resolved the problem.

UPDATE table_name set date_field=sysdate where ROWNUM <>

ROLLBACK;

Regards Badri