Archive for the ‘Index’ Category

SPM and Profiles -Part2

Posted by Sumit on November 21, 2011

Converting baselines into profiles

In my last post, we have discussed various workarounds to fix the execution plan of the query if its not picking baseline.

Of the given 3 methods, I was interested in the 3rd one. What got me interested is the idea if we can create profiles from baselines.

The next obvious question is “why you need to do so”.

The 2 reasons are

1. Baselines not getting used. In this case you can create a profile out of baseline and then impose oracle to use this profile.

2. In 11g, we have ADG (active standby database). Guess what!!! Baselines doesn’t work in ADG. So if you fix query on primary, it won’t pick the plan in standby. So you better create a profile. Also you may need to bounce your standby in order for profile to get used (because of another bug).

Before I talk about script to convert baselines into profiles, lets step back and think whats is that thing which is common in baselines and profiles(if at all there is) and what is that thing which makes them different.

Both baselines and profiles store data in form of hints.

Only difference is in case of baselines oracle tries to regenerate the same plan using hints and if it can’t then it will not use the baseline.

However, in case of profile, oracle will use the hints to generate the execution plan which it will use. Therefore, there is no guarantee that oracle will land on same plan again and again and hence plan flip can occur.

I will soon write about hints and how oracle stores them for a given plan.

Anyway, coming back to our point, oracle stores the execution plan in form of hints. Therefore, for each plan it captures or generates, it must store them somewhere. The idea is to get those hints and create the profile.

planprofile.sql:-

Session1>select plan_name from dba_sql_plan_baselines where rownum < 2;

PLAN_NAME
------------------------------
SYS_SQL_PLAN_086da2eaeec7bdef

1 row selected.

Session1>@planprofile
enter sql_id to be fixed:- dgh672sad89j4
baseline plan name:- SYS_SQL_PLAN_086da2eaeec7bdef
enter bad plan hash value to be tuned :- 12345678
'===================OUTPUT========================='

declare
ar_profile_hints sys.sqlprof_attr;
begin
 ar_profile_hints := sys.sqlprof_attr(
 'BEGIN_OUTLINE_DATA',
 'FULL(@"SEL$4" "MRH"@"SEL$4")',
 'NO_ACCESS(@"SEL$3" "from$_subquery$_007"@"SEL$3")',
 'FULL(@"SEL$6" "MRH"@"SEL$6")',
 'NO_ACCESS(@"SEL$5" "from$_subquery$_004"@"SEL$5")',
 'PQ_DISTRIBUTE(@"SEL$F5BB74E1" "RMS"@"SEL$2" NONE BROADCAST)',
 'USE_NL(@"SEL$F5BB74E1" "RMS"@"SEL$2")',
 'LEADING(@"SEL$F5BB74E1" "H"@"SEL$1" "RMS"@"SEL$2")',
 'INDEX_RS_ASC(@"SEL$F5BB74E1" "RMS"@"SEL$2" ("METRICS_SUMMARY95"."MVIEW_NAME" "METRICS_SUMMARY95"."OWNER"))',
 'NO_ACCESS(@"SEL$F5BB74E1" "H"@"SEL$1")',
 'PQ_DISTRIBUTE(@"MRG$1" "H"@"MRG$1" BROADCAST NONE)',
 'PQ_DISTRIBUTE(@"MRG$1" "RMS"@"MRG$1" NONE BROADCAST)',
 'USE_MERGE_CARTESIAN(@"MRG$1" "H"@"MRG$1")',
 'USE_MERGE_CARTESIAN(@"MRG$1" "RMS"@"MRG$1")',
 'LEADING(@"MRG$1" "from$_subquery$_010"@"MRG$1" "RMS"@"MRG$1" "H"@"MRG$1")',
 'NO_ACCESS(@"MRG$1" "H"@"MRG$1")',
 'FULL(@"MRG$1" "RMS"@"MRG$1")',
 'NO_ACCESS(@"MRG$1" "from$_subquery$_010"@"MRG$1")',
 'OUTLINE(@"SEL$2")',
 'OUTLINE(@"SEL$1")',
 'OUTLINE_LEAF(@"MRG$1")',
 'OUTLINE_LEAF(@"SEL$5")',
 'OUTLINE_LEAF(@"SEL$6")',
 'MERGE(@"SEL$2")',
 'OUTLINE_LEAF(@"SEL$F5BB74E1")',
 'OUTLINE_LEAF(@"SEL$3")',
 'OUTLINE_LEAF(@"SEL$4")',
 'OPT_PARAM(''optimizer_index_caching'' 80)',
 'OPT_PARAM(''optimizer_index_cost_adj'' 3)',
 'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')',
 'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
 'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
 'DB_VERSION(''11.1.0.7'')',
 'OPTIMIZER_FEATURES_ENABLE(''11.1.0.7'')',
 'IGNORE_OPTIM_EMBEDDED_HINTS',
 'END_OUTLINE_DATA'
);
for sql_rec in (
 select t.sql_id, t.sql_text
 from dba_hist_sqltext t, dba_hist_sql_plan p
 where t.sql_id = p.sql_id and p.sql_id = 'dgh672sad89j4' and p.plan_hash_value = 12345678 and p.parent_id is null
 ) loop
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
 sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => 'PROFILE_'||sql_rec.sql_id
 );
 end loop;
end;
/

That was simple. Isn’t it??

But i was not satisfied. I wanted to take this a step further. Now i want oracle to use whatever plan I want.

In case oracle had changed plan , I want oracle to use previous plan.

In one of my database, planflip caused cpu spikes. Buffer_gets/exec increased from thousands to millions. The idea was to force old to use previous plan .

oldprofile.sql

</pre>
Sesion1>@oldprofile
enter sql_id:- 9ddv4vbt2zrxy
enter old plan hash value:- 1236769290
enter bad plan hash value to be tuned :- 3891475932
'===================OUTPUT========================='

declare
ar_profile_hints sys.sqlprof_attr;
begin
 ar_profile_hints := sys.sqlprof_attr(
 'BEGIN_OUTLINE_DATA',
 'IGNORE_OPTIM_EMBEDDED_HINTS',
 'OPTIMIZER_FEATURES_ENABLE(''11.1.0.7'')',
 'DB_VERSION(''11.1.0.7'')',
 'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
 'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
 'OPT_PARAM(''_optimizer_connect_by_cost_based'' ''false'')',
 'OPT_PARAM(''optimizer_index_cost_adj'' 3)',
 'OPT_PARAM(''optimizer_index_caching'' 80)',
 'OUTLINE_LEAF(@"SEL$1")',
 'INDEX(@"SEL$1" "CCS"@"SEL$1" ("COMPLETED_CUSTOMER_SHIPMENTS"."CONDITION" "COMPLETED_CUSTOMER_SHIPMENTS"."LAST_UPDATED"))',
 'END_OUTLINE_DATA'
);
for sql_rec in (
 select t.sql_id, t.sql_text
 from dba_hist_sqltext t, dba_hist_sql_plan p
 where t.sql_id = p.sql_id and p.sql_id = '9ddv4vbt2zrxy' and p.plan_hash_value = 3891475932 and p.parent_id is null
 ) loop
 DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
 sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => 'PROFILE_'||sql_rec.sql_id
 );
 end loop;
end;
/

Posted in Baselines, Scripts, Scripts, Uncategorized | Tagged: active data guard, ADG, baseline not used in standby, baselines not getting used, baselines not getting used in ADG, controlling execution plan, converting baseline into profiles, converting profiles into baselines, difference baselines and profiles, difference profiles and baselines, hints oracle, oldprofile.sql, oracle execution plan, oracle execution plans, performance tuning, planprofile.sql, profile 10g, profile not used in standby, similarity baselines and profile, similarity profiles and baselines, SPM 11g, spm bugs, sql profile | 1 Comment »

Cleaning Journal table

Posted by Sumit on May 2, 2011

Journal Table cleanup process

As we have seen in previous posts journal table behaviour, if the rebuild gets cancelled in between and oracle does not gets a latch, it does not clear the journal table automatically.

Also, oracle won’t allow dropping or rebuilding of index.


session1>alter index mytab_idx rebuild online;
alter index mytab_idx rebuild online
*
ERROR at line 1:
ORA-08104: this index object 98701 is being online built or rebuilt

sesion1>drop index mytab_idx;
drop index mytab_idx
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Its the responsibility of the smon to clear the indexes stuck in journal.

However, in many cases dba intervention is required.

There are 2 ways to clear the journal table.

1. By waking up smon

2. By using oracle supplied dbms_repair.online_index_clean package

waking up smon

We can post smon to clear the temporary segments and journal by issuing the following command.


select pid from v$process p, v$bgprocess b where b.paddr = p.addr and name='SMON';

oradebug wakeup <pid>

Using dbms_repair.online_index_clean package

dbms_repair is a oracle supplied package which has many procedures to detect and repair block corruption or rebuild freelists etc.

we can use online_index_clean to clean up the journal table.

Be informed that this procedure also requires initial latch. I have developed a small script ndx-cleanup.sql to clear the journal table. It takes owner and index name as input and must be executed from SYS.


Session1>@ndx-cleanup
Enter value for owner: subhatia_dba
Enter value for index_name: mytab_idx
OBJECT ID OF INDEX IS -----------> 98701
JOURNAL TABLE IS -----------> SYS_JOURNAL_98701
CURRENT INDEX STATUS -----------> VALID
INDEX CLEANUP COMPLETED
JOURNAL TABLE HAS BEEN CLEANED

PL/SQL procedure successfully completed.

Posted in Index, Index rebuild, Journal table | Tagged: clean index, clean journal table, clear index entry, clear journal entry, dbms_repair, dbms_repair.online_index_clean, dbms_repait, error index drop, error index rebuild, index build, index rebuild, online index rebuild, online_index_clean, ORA-08104, ORA-08104: this index object is being online built or rebuilt, oradebug, oradebug wakeup, wakeup | 3 Comments »

Journal table — Behaviour and Issues

Posted by Sumit on April 13, 2011

Journal Tables are like any other IOT tables. I had explained their strcture in the recent post. This post will cover following points

Behaviour of journal table.
Effects of killing index in between.

Please click here to understand the basic structure of journal table.

Behaviour of Journal Table

As discussed earlier, journal tables are created when we issue alter index command and gets dropped only after the job completes successfully or cleans or cancelled successfully. Whenever we kill our session or cancel our job, oracle tries to clean the journal table . If you have killed your job (CTL C), oracle tries to get a latch and then merge the entries and then drop the journal table.

In case it does not, it will try fixed number of times and will then fail. This behaviour is explained in detail in later section. However, we must verify that journal tables are no longer present.

Test Case:-

From session 1 insert a row and issue rebuild comand from session 2 and then hit ctl c to cancel the job.


Session1>insert into mytab select * from mytab where rownum<2;

Session2>alter index mytab_idx rebuild online;
alter index mytab_idx rebuild online
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation<

/*it will error out after 2000 retries or 20000 milli seconds. Discussed later */

Lets check if the journal table exists or not.


sesion2>select object_name,object_type,object_id from dba_objects where object_name='MYTAB_IDX';

OBJECT_NAME          OBJECT_TYPE           OBJECT_ID
-------------------- -------------------- ----------
MYTAB_IDX            INDEX                     98701

1 row selected.

session2>select object_name,object_type from dba_objects where object_name='SYS_JOURNAL_98701';

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
SYS_JOURNAL_98701    TABLE

1 row selected.

As suspected , oracle timesout and left the cleaning part. All the transaction entries will now go into the journal table from now since the trigger has not been changed back to point to the index. Its still pointing to the journal table. As we can see,if the index rebuild job does not perform cleaning, the entries will stuck in journal table and we have to take manual steps to recover it.


session1>select * from SYS_JOURNAL_98701;

session1>insert into mytab(owner,object_name) values ('AFTER INDEX','CANCEL');

1 row created.
session1>commit;

Commit complete.

C0                   C1         C2    OPCODE     PARTNO RID
-------------------- ---------- ----- ---------- ------ ------------------
AFTER INDEX          CANCEL           I               0 D/////AAEAAAwQHAAL

1 row selected.

Effects of killing index in between

As we had already discussed that even after killing index job, oracle sometimes does not able to drop the journal table. Oracle will try exactly 2000 times with avg waitng time of 10 millli second (20,000 milli seconds in all), in case all the blocking sessions are inactive, to acquire a latch to clean the journal table.

Test Case

Lets insert a row without commiting and start rebuilding from next session.


Session1>insert into mytab select * from mytab where rownum<2;

1 row created.

Session2>alter index mytab_idx rebuild online;

Lets check the status from session 3 using our ndx-progress.sql


SESSION3>@ndx-progress
Enter value for sid: 2178

SID              ---->2178
SERIAL           ---->6537
MODULE           ---->SQL*Plus
MACHINE          ---->db-fc-admin-6001.iad6.amazon.com
USERNAME         ---->SUBHATIA_DBA
OSUSER           ---->subhatia
SQL TEXT         ---->alter index mytab_idx rebuild online
SQL ID           ---->7sk4sratkuvq8
JOURNAL NAME     ---->SYS_JOURNAL_98701
TABLE NAME       ---->MYTAB
EVENT_NAME       ---->enq: TX - row lock contention
IDLE SINCE       ---->223
CPU TIME         ---->35995
ELAPSED TIME     ---->219143299
JOURNAL COUNT    ---->0
STAGE            ---->Waiting for first latch.
LONGOPS          ---->INDEX REBUILD NOT STARTED YET
PROGRESS         ---->No Tx started after index rebuild has commited yet. There are 0 rows in journal table
------------SESSIONS BLOCKING FIRST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
2154    9407    INACTIVE                249             0               NONE            9babjv8yq8ru3           SQL*Plus        db-fc-admin-6001.iad6.amazon.com
------------SESSIONS THAT MAY BLOCK LAST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE

NO POTENTIAL BLOCKERS FOUND FOR THE LAST LATCH

As expected, our insert has blocked the index rebuild. Lets cancel the job(ctl c) and check whats happening now using ndx-progress.

Yes, ndx-progress can report that one too (amazing).

click here for ndx-progress


SESSION3>@ndx-progress
Enter value for sid: 2178
Index Rebuild has been cancelled. Cleaning going on
SID              ---->2178
SERIAL           ---->6537
MODULE           ---->SQL*Plus
MACHINE          ---->db-fc-admin-6001.iad6.amazon.com
USERNAME         ---->SUBHATIA_DBA
OSUSER           ---->subhatia
SQL TEXT         ---->alter index mytab_idx rebuild online
SQL ID           ---->7sk4sratkuvq8
JOURNAL NAME     ---->SYS_JOURNAL_98701
TABLE NAME       ---->MYTAB
EVENT_NAME       ---->index (re)build online cleanup
IDLE SINCE       ---->2116
CPU TIME         ---->2164672
ELAPSED TIME     ---->2113894321
JOURNAL COUNT    ---->0
------------SESSIONS BLOCKING CLEANING PROCESS-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
2154    9407    INACTIVE                2145            0               NONE            9babjv8yq8ru3           SQL*Plus        db-fc-admin-6001.iad6.amazon.com
------------CLEANUP PROCESS TIMOUT-------------
EVENT                                   TOTAL WAITS             TOTAL TIMEOUTS          TIME WAITED             AVG WAIT                MAX WAIT
index (re)build online cleanup          1967                    1967                    19799                   10.07                   10                  10

On checking the status, index job was cancelled but journal entry was still there


Session2>alter index mytab_idx rebuild online;
alter index mytab_idx rebuild online
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SESSION3>select event,total_waits,total_timeouts,time_waited,average_wait,max_wait from v$session_event where sid=2178 and event='index (re)build online cleanup';
EVENT                          TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT   MAX_WAIT
------------------------------ ----------- -------------- ----------- ------------ ----------
index (re)build online cleanup        2000           2000       20131        10.07         10
1 row selected.

As we can check, oracle tries for 2000 times but timeout each time. it kept tring for 2000*10 ms and then error out.

Posted in Index, Index rebuild, Journal table | Tagged: cancel index rebuild, clear journal table, clearing index rebuild, index rebuild online, index stuck, journal table, ndx-progress, what are journal tabe | 3 Comments »

Journal Table — What are those

Posted by Sumit on April 12, 2011

Many DBA’s have tendency to hit CTL C or kill the session, when there session does not comes out. Ever tries doing that with index rebuild and you will find that your index is stuck in journal table. This post is intended to describe the nature of journal table and how to clear the mess.

It’s been divided into following sections

Structure and understanding of journal table
Behaviour of journal table.
Effect of killing the index rebuild in between.
Clearing the journal table.

Structure of Journal Table

Oracle creates journal table inside the owner schema as soon as we issue “alter index rebuild online”. It doesn’t matter if oracle has acquired the first latch or not. The entries of all the transactions started after we issue the command will be logged into the journal table. Journal table is the index organized table and their name will essentially be SYS_JOURNAL_<index object_id>.

To understand index rebuilding check the following link.

Index Rebuild Online

The structure is as follows

As you might have noticed, there is no “U” for update in opcode since update essentially is Delete+ Insert. Two entries will be recorded for each update statement in the journal table.

Name                    Null?    Type
C0                      NOT NULL VARCHAR2(30) -->first column of index

C1                      NOT NULL VARCHAR2(128) -->second column of index(composite index)
C2                      NOT NULL VARCHAR2(30) -->third column of index (composite index)
OPCODE                           CHAR(1) -->I for insert, D for delete.
PARTNO                           NUMBER
RID                     NOT NULL ROWID -->rowid from 7th char to end

C0,C1,C2(columns of index) and RID will form the primary key of the journal table and will have a unique index with the name SYS_IOT_TOP_<journal object id>

The columns of journal table(c0,c1,c2) will have same data type as of base table and will always be not null irrespective of constarints on base table.

TEST CASE

I have created a table mytab (select * from dba_objects) and created a composite index mytab_idx on (owner,object_name,subobject_name).

As I started the rebuild, i could see following entries in my schema.


session4> select object_name,object_type,object_id from dba_objects where owner='SUBHATIA_DBA';

OBJECT_NAME                                        OBJECT_TYPE          OBJECT_ID
-------------------------------------------------- ------------------- ----------
MYTAB                                              TABLE                    98699
SYS_JOURNAL_98701                                  TABLE                   101871
SYS_IOT_TOP_101871                                 INDEX                   101872
MYTAB_IDX                                          INDEX                    98701

4 rows selected.

As we can confirm the name of the journal table is SYS_JOURNAL_98701 (where 98701 is object id of index) and IOT index is SYS_IOT_TOP_101871 where 101871 is object_id of journal table.

Usage of Journal Tables

When we rebuild index online, oracle does not read from the existing index. Instead it reads the base table. So whats the use of existiing index.

Well, oracle put the existing index into read only mode and records all the new transaction entries into the journal table.

Journal table is like any other IOT table. So whenever, any select statement needs to traverse a row, it gets the rowid from the new journal table and then access the table using that rowid.

Question1) Does journal table is always there in the database

Answer) Journal tables are created as we issue the rebuild command and gets dropped as soon as the command finishes.

Question 2) Does journal table record entries of transactions which starts before the alter index rebuild command was issued.

Answer) No, it does not. It will only capture entries of those transactions that have started after we fired rebuild. Oracle doesn’t know you are going to fire rebuild command.

Question3) Does journal table records entries of transactions started after rebuild only after we commit.

Answer) Journal table is like any other table. In case you performed some DML while index rebuid is going on and does not commit, you will be able to see those entries into the journal table from same session but others won’t. Others will see only if you commit them. Oracle follows basic ACID principle for journal tables too.

Test case

Lets verify all the above points

Are journal tables there forever??

Currently I have only 2 objects in my schema.


session4>select object_name,object_type,object_id from dba_objects where owner='SUBHATIA_DBA';

OBJECT_NAME                                        OBJECT_TYPE          OBJECT_ID
-------------------------------------------------- ------------------- ----------
MYTAB_IDX                                          INDEX                    98701
MYTAB                                              TABLE                    98699

2 rows selected.

Lets insert few rows from 1st session to block the rebuild and issue alter index command from 2nd session

SESSION1>insert into mytab select * from mytab where rownum<5;
4 rows created.
/*alter index from session 2 */
session2>alter index mytab_idx rebuild online;  -->will get stuck

/*lets check again from session 1 */

session1>select object_name,object_type,object_id from dba_objects where owner='SUBHATIA_DBA';

OBJECT_NAME                                        OBJECT_TYPE          OBJECT_ID
-------------------------------------------------- ------------------- ----------
MYTAB                                              TABLE                    98699
SYS_JOURNAL_98701                                  TABLE                   101877
SYS_IOT_TOP_101877                                 INDEX                   101878
MYTAB_IDX                                          INDEX                    98701

4 rows selected.

Does journal table record entries of transactions which starts before the alter index rebuild command was issued??

Check the count of journal table from the session holding the lock.


SESSION1>select count(*) from SYS_JOURNAL_98701;

  COUNT(*)
----------
         0

1 row selected.

Does journal table records entries of transactions started after rebuild only after we commit??

Lets open a new session and insert few rows without committing.


session4>insert into mytab select * from mytab where rownum<5;

4 rows created.

session4>select count(*) from SYS_JOURNAL_98701;

  COUNT(*)
----------
         4

1 row selected.

/*lets query from some another session*/

SESSION1>select count(*) from SYS_JOURNAL_98701;

  COUNT(*)
----------
         0

1 row selected.

/*Lets commit and again check count from another session */

SESSION1>select count(*) from SYS_JOURNAL_98701;

  COUNT(*)
----------
         4

1 row selected.

Understanding of Journal Table

When we say, journal table hold entries then the next obvious question is what entries. How does it store and what does it mean.Moreover, is there any mechanism to understand those.

The answer is YES, we can.

Lets try out this test case.

Insert a row from session1 to block the index rebuild and start rebuilding index from another session.

SESSION1>insert into mytab select * from mytab where rownum<5;

4 rows created.
/* Start rebuild from Session 2. Your session will stuck here even if its a pretty small index*/
session2>alter index mytab_idx rebuild Online;
/*Open a new session and insert a row */
session3>insert into mytab(owner,object_name,subobject_name) values('SUMIT','RANDOM','RANDOM');
1 row created.
session3>SELECT * FROM SYS_JOURNAL_98701;

C0                             C1                   C2                             O     PARTNO RID
------------------------------ -------------------- ------------------------------ - ---------- ------------------
SUMIT                          RANDOM               RANDOM                         I          0 D/////AAEAAAwQHAAU

 /*Let's update a row as well */
session3>update mytab set owner='DBA' where owner='SUMIT' AND object_name='RANDOM';

1 row updated.

session3>SELECT * FROM SYS_JOURNAL_98701;

C0         C1         C2         O     PARTNO RID
---------- ---------- ---------- - ---------- ------------------
DBA        RANDOM     RANDOM     I          0 D/////AAEAAAwQHAAU
SUMIT      RANDOM     RANDOM     D          0 D/////AAEAAAwQHAAU

2 rows selected.

Here we can see 2 entries. But there should be 3. Remember update=D+I.

Oracle knew we have updated the row which we just inserted and the entry was already there. So it removes the earlier entry and added 2 new entries for update only.

Now, the entry for Sumit is marked as “D” wich was earlier “I” and a new entry “DBA” has been added as “I”.

Last question, why oracle has not stored initial 6 letters of rowid and can we trace it back.

Yes, we can trace it back. We can use following query (but it is a bit slower)


session3>select owner,object_name,SUBOBJECT_NAME from mytab where substr(rowid,7)='AAEAAAwQHAAU';

OWNER                OBJECT_NAME                                        SUBOBJECT_NAME
-------------------- -------------------------------------------------- ------------------------------
DBA                  RANDOM                                             RANDOM

1 row selected.

Oracle is not showing inital 6 letters because those always refer to object id.

We can use this method which is relatively faster.


session3>select substr(rowid,1,6) from  mytab  where rownum<2;

SUBSTR(ROWID,1,6)
------------------
AAAYGL

1 row selected.
session3>select owner,object_name,SUBOBJECT_NAME from mytab where rowid='AAAYGLAAEAAAwQHAAU';

OWNER                OBJECT_NAME                                        SUBOBJECT_NAME
-------------------- -------------------------------------------------- ------------------------------
DBA                  RANDOM                                             RANDOM

1 row selected.

Posted in Index, Index rebuild, Journal table | Tagged: index rebuild, index stuck, journal table in oracle, journal tables, oracle journal table, what are journal tables | 1 Comment »

Online Index Rebuild oracle 11g–>Behind the scene

Posted by Sumit on April 5, 2011

Index are used to speed up the select statements.
But over a period of time, they may get fragmented because of constant DML’s occurring on the table. These bloated indexes may slow down the select or may cause plan flips too.

One way to avoid all these is to rebuild indexes.However, many of us might have faced issues while rebuilding indexes. Sometimes, it causes performance issues (due to enq TM contention) or the command won’t come out.

Ever wonder, why the index rebuilding is suddenly taking such a long time. Why my session is not coming out or exactly where is it stuck.

This post is intended to explore the internals of online index rebuild in oracle11g. I have developed a customized script which will tell exactly where the session is stuck.

http://fordba.wordpress.com/2011/03/30/ndx-progress-sql/

The script is just an anonymous plsql block and does not perform any DDL/DML.

It queries various v$ views to present the current picture before you and does not cause or have any adverse impact

For online index rebuild oracle requires 2 latches. One at the beginning and one at the end of the operation.

Online Index Rebuild Stages (No Parallel)

During Online index rebuild oracle performs following in the given order :-

Create the journal table (inside index owner schema) SYS_JOURNAL_<objectid_index>
Wait for the first latch (enq: TX – row lock contention)
As soon as it gets latch, will perform db file sequential read to merge journal entries
Will start scattered read of the base table
Will again perform the sequential read to merge the journal entries which got generated during scattered read.
Will perform direct path read temp.
wait for the end latch

The good thing about index rebuild in 11g is , it does not cause locking for the DML’s (enq TM contention, which was there in 10g).

Test Case

Environment:- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production

1. Create a new table and insert few records.

SESSION1>CREATE TABLE mytab as select * from dba_objects;
Table created.

SESSION1>insert into mytab select * from mytab;
12459 rows created.

SESSION1>/
12459 rows created.
SESSION1>create index mytab_idx on myt(owner,OBJECT_NAME,SUBOBJECT_NAME);

Index created.

You may want to insert more rows to grow the size of index.

2. Insert a new records into the table.

SESSION1>insert into mytab select * from mytab where rownum<10;
9 rows created.

3. Open a new session (note down its sid) and issue alter index command.

SESSION2>select sid from v$mystat where rownum<2;
SID
----------
2195
1 row selected.
SESSION2>ALTER INDEX MYTAB_IDX REBUILD ONLINE;

4. Open a new session to see exactly whats happening.

SESSION3>@ndx-progress.sql
Enter value for sid: 2195
SQL TEXT IS      ---->ALTER INDEX MYTAB_IDX REBUILD ONLINE
SQL ID IS        ---->drs3mtb5w9knb
JOURNAL TABLE IS ---->SYS_JOURNAL_98701
TABLE NAME IS    ---->MYTAB
EVENT_NAME IS    ---->enq: TX - row lock contention
JOURNAL COUNT    ---->0
STAGE            ---->Waiting for first latch.
PROGRESS         ---->No Tx started after index rebuild has commited yet. There are 0 rows in journal table
------------SESSIONS BLOCKING FIRST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
2161    19807   INACTIVE                425             0               NONE            9babjv8yq8ru3           SQL*Plus        db-fc-admin-6001.iad6.amazon.com
------------SESSIONS THAT MAY BLOCK LAST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
NO POTENTIAL BLOCKERS FOUND FOR THE LAST LATCH

5. In the above output, you can see the session is waiting on enq: TX – row lock contention and has not acquired the first latch.

It will keep on waiting for all the transactions which have started before issueing index rebuild.

For any transactions started after index rebuild comand issued, they will cause locking at the last stage when end latch is required.

In case they commit, there entries will go into the journal table.

Lets open another session and insert few rows and wait for 30 seconds(This is the limit i set in my script to find potential blockers)

SESSION4>insert into mytab select * from mytab where rownum<101;
100 rows created.

6. Lets check whats happening again. The journal count is still 0, since session 4 has not commited yet and the session is appearing on potential blockers list

SESSION3>/
Enter value for sid: 2195
SQL TEXT IS      ---->ALTER INDEX MYTAB_IDX REBUILD ONLINE
SQL ID IS        ---->drs3mtb5w9knb
JOURNAL TABLE IS ---->SYS_JOURNAL_98701
TABLE NAME IS    ---->MYTAB
EVENT_NAME IS    ---->enq: TX - row lock contention
JOURNAL COUNT    ---->0
STAGE            ---->Waiting for first latch.
PROGRESS         ---->No Tx started after index rebuild has commited yet. There are 0 rows in journal table
------------SESSIONS BLOCKING FIRST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
2161    19807   INACTIVE                1015            0               NONE            9babjv8yq8ru3           SQL*Plus        db-fc-admin-6001.iad6.amazon.com
------------SESSIONS THAT MAY BLOCK LAST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
2164    36539   INACTIVE                130             0               NONE            9babjv8yq8ru3           SQL*Plus        db-fc-admin-6001.iad6.amazon.com

7. Lets commit from session 4 and see the difference in count of journal table. It will disappear from potential blockers list as it has committed and there will be entries into the journal table

SESSION3>/
Enter value for sid: 2195
SQL TEXT IS      ---->ALTER INDEX MYTAB_IDX REBUILD ONLINE
SQL ID IS        ---->drs3mtb5w9knb
JOURNAL TABLE IS ---->SYS_JOURNAL_98701
TABLE NAME IS    ---->MYTAB
EVENT_NAME IS    ---->enq: TX - row lock contention
JOURNAL COUNT    ---->100
STAGE            ---->Waiting for first latch.
PROGRESS         ---->Few Tx started after index rebuild command have been commited and there is/are 100 rows into journal table
------------SESSIONS BLOCKING FIRST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
2161    19807   INACTIVE                1134            0               NONE            9babjv8yq8ru3           SQL*Plus        db-fc-admin-6001.iad6.amazon.com

------------SESSIONS THAT MAY BLOCK LAST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
NO POTENTIAL BLOCKERS FOUND FOR THE LAST LATCH

8. So far we have observed that index is stuck. Lets insert few more rows (100k approx) so that we could observe step3 else we wont able to catch the initial db file sequential read merging and check the status.

SESSION4>insert into mytab select * from mytab where rownum<100001;
100000 rows created.
SESSION4>commit;
Commit complete.

——————————-check from session 3———————-

SESSION3>/
Enter value for sid: 2195
SQL TEXT IS      ---->ALTER INDEX MYTAB_IDX REBUILD ONLINE
SQL ID IS        ---->drs3mtb5w9knb
JOURNAL TABLE IS ---->SYS_JOURNAL_98701
TABLE NAME IS    ---->MYTAB
EVENT_NAME IS    ---->enq: TX - row lock contention
JOURNAL COUNT    ---->100100
STAGE            ---->Waiting for first latch.
PROGRESS         ---->Few Tx started after index rebuild command have been commited and there is/are 100100 rows into journal table
------------SESSIONS BLOCKING FIRST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
2161    19807   INACTIVE                1717            0               NONE            9babjv8yq8ru3           SQL*Plus        db-fc-admin-6001.iad6.amazon.com
------------SESSIONS THAT MAY BLOCK LAST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
NO POTENTIAL BLOCKERS FOUND FOR THE LAST LATCH

9. Insert few more rows again from session 4, to ensure we block the last latch too.

SESSION4>insert into mytab select * from mytab where rownum<100;
99 rows created.

10. Lets commit from session 1 to release initial lock and check status from session 3.

SESSION3>/
Enter value for sid: 2195
SQL TEXT IS      ---->ALTER INDEX MYTAB_IDX REBUILD ONLINE
SQL ID IS        ---->drs3mtb5w9knb
JOURNAL TABLE IS ---->SYS_JOURNAL_98701
TABLE NAME IS    ---->MYTAB
EVENT_NAME IS    ---->db file sequential read
JOURNAL COUNT    ---->0
STAGE            ---->First latch acquired.db file sequential read going on. Direct path read pending after this step.
PROGRESS         ---->All Tx started before index rebuild have been commited (first latch acquired and cleared) and no entries into journal table now.

As we can see, oracle always merge the journal table entries before performing scatterd read(FTS on base table).

Once that gets completed, oracle will start scattered read. Scattered read does not need any lock, so at this time our session 4 wont lock the index rebuild. it will lock during last phase.

SESSION3>/
Enter value for sid: 2195
SQL TEXT IS      ---->ALTER INDEX MYTAB_IDX REBUILD ONLINE
SQL ID IS        ---->drs3mtb5w9knb
JOURNAL TABLE IS ---->SYS_JOURNAL_98701
TABLE NAME IS    ---->MYTAB
EVENT_NAME IS    ---->db file scattered read
JOURNAL COUNT    ---->0
STAGE            ---->First latch acquired. FTS going on
PROGRESS         ---->All Tx started before index rebuild have been commited (first latch acquired and cleared) and no entries into journal table now.

Once that is done,oracle will perform sequential read and direct path read temp to merge entries generated during scattered read and then will wait for end latch.

Enter value for sid: 2195
SQL TEXT IS      ---->ALTER INDEX MYTAB_IDX REBUILD ONLINE
SQL ID IS        ---->drs3mtb5w9knb
JOURNAL TABLE IS ---->SYS_JOURNAL_98701
TABLE NAME IS    ---->MYTAB
EVENT_NAME IS    ---->enq: TX - row lock contention
JOURNAL COUNT    ---->0
STAGE            ---->Operation completed.Waiting for ending latch
PROGRESS         ---->All Tx started before index rebuild have been commited (first latch acquired and cleared) and no entries into journal table now.

------------SESSIONS BLOCKING LAST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
2164    36539   INACTIVE                367             0               NONE            9babjv8yq8ru3           SQL*Plus        db-fc-admin-6001.iad6.amazon.com

Once commit, index rebuild fiishes

Online Index Rebuild Stages (Parallel Clause)

Oracle performs following sequence of events with parallel clause

Create the journal table (inside index owner schema) SYS_JOURNAL_<objectid_index>
Wait for the “first latch” (enq: TX – row lock contention)
As soon as it gets latch, will perform , it will spawn its slave process
Slave process will perform db file sequential read to merge the journal table entries
Each slave process will perform scattered read for its bunch of blocks.
Slave process will again perform the sequential read to merge the journal entries which got generated during scattered read.
slave process job will end here here.
Master process will wait for the “end latch”.

Test Case

Insert few records from the first session to block index rebuild.

SESSION1>insert into mytab select * from mytab where rownum<101;
100 rows created.

Open another session and rebuild index with parallel clause

SESSION2>ALTER INDEX MYTAB_IDX REBUILD ONLINE parallel 4;

Checking the status

SESSION3>/
Enter value for sid: 2195
SQL TEXT IS      ---->ALTER INDEX MYTAB_IDX REBUILD ONLINE parallel 4
SQL ID IS        ---->7qr6scnthqh0f
JOURNAL TABLE IS ---->SYS_JOURNAL_98701
TABLE NAME IS    ---->MYTAB
EVENT_NAME IS    ---->enq: TX - row lock contention
JOURNAL COUNT    ---->0
STAGE            ---->Waiting for first latch.
PROGRESS         ---->No transaction started after rebuiding has committed.
------------SESSIONS BLOCKING FIRST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
2161    19807   INACTIVE                152             0               NONE            9babjv8yq8ru3           SQL*Plus        db-fc-admin-6001.iad6.amazon.com
------------SESSIONS THAT MAY BLOCK LAST LATCH ARE-------------
SID     SERIAL  STATUS          LAST_CALL_ET            COMMAND         SQL_ID          PREV_SQL_ID             MODULE          MACHINE
NO POTENTIAL BLOCKERS FOUND FOR THE LAST LATCH

As we commit the first session, parallel process will start the processing.

SESSION3>/
Enter value for sid: 2195
SQL TEXT IS      ---->ALTER INDEX MYTAB_IDX REBUILD ONLINE parallel 4
SQL ID IS        ---->7qr6scnthqh0f
JOURNAL TABLE IS ---->SYS_JOURNAL_98701
TABLE NAME IS    ---->MYTAB
EVENT_NAME IS    ---->PX Deq: Execute Reply
JOURNAL COUNT    ---->0
STAGE            ---->First latch acquired. Parallel processing going on
------------TOTAL SLAVES ARE-------------
SID     SERIAL  DEGREE          REQ_DEGREE              EVENT
2149    46710   4               4               direct path read temp
2154    22192   4               4               PX Deq: Execution Msg
2156    23451   4               4               PX Deq: Execution Msg
2157    2939    4               4               PX Deq: Execution Msg
2159    55050   4               4               direct path read temp
2168    5080    4               4               direct path read temp
2184    46569   4               4               direct path read temp
2193    3495    4               4               PX Deq: Execution Msg
------------LONGOPS SLAVES ARE-------------
SID     SERIAL  TOTALWORK               SOFAR           TIME REMAINING                  ELAPSED SECONDS         OPNAME                  EVENT
2168    5080    40531                   16913           20                              14                      Sort Output             direct path read temp
2149    46710   37403                   16918           17                              14                      Sort Output             direct path read temp
2159    55050   32739                   16872           14                              15                      Sort Output             direct path read temp
2184    46569   30720                   17012           11                              14                      Sort Output             direct path read temp

Posted in Index, Index rebuild | Tagged: 11g index rebuild, enq TM contention, index rebuild, lock index rebuild, online index rebuild, performance index rebuild, troubleshoot index rebuild | 8 Comments »

Oldprofile.sql

Posted by Sumit on April 1, 2011

OldProfile.sql:- This script can be used to force oracle to use the given plan by creating profile over top of it.

/*
Name :- Oldprofile.sql
Db Version :- 11.1.0.7
Author :- Sumit Bhatia
Version :- 1.1
Purpose :- Create a profile to force the old plan
Input :- sql_id, old plan hash value, bad plan hash value
*/
set feedback off
set echo off
set timing off
set pagesize 0
set heading off verify off
accept sql_id prompt ‘enter sql_id:- ‘
accept old_plan_hash_value prompt’enter old plan hash value:- ‘
accept plan_hash prompt’enter bad plan hash value to be tuned :- ‘
prompt ‘===================OUTPUT=========================’
select chr(10) from dual;

select ‘declare’ from dual;
select ‘ar_profile_hints sys.sqlprof_attr;’ from dual;
select ‘begin’ from dual;
select chr(9)||’ar_profile_hints := sys.sqlprof_attr(‘ from dual;
select chr(9)||chr(9)||”’BEGIN_OUTLINE_DATA”,’ from dual;

SELECT chr(9)||chr(9)||””||regexp_replace(extractvalue(value(d), ‘/hint’),””,”””)||”’,’
from
xmltable(‘/*/outline_data/hint’
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id like nvl(‘&sql_id’,sql_id)
and plan_hash_value=&old_plan_hash_value
and other_xml is not null
and rownum < 2
)
)
d;

select chr(9)||chr(9)||”’END_OUTLINE_DATA”’||chr(10)||’);’||chr(10)||’for sql_rec in (‘||chr(10)||chr(9)||’select t.sql_id, t.sql_text’||chr(10)||chr(9)||’ from dba_hist_sqltext t, dba_hist_sql_plan p’ ||chr(10)||chr(9)|| ‘where t.sql_id = p.sql_id and p.sql_id = ”&sql_id” and p.plan_hash_value = &plan_hash and p.parent_id is null’||chr(10)||chr(9)||chr(9)||’) loop’||chr(10)||chr(9)||’ DBMS_SQLTUNE.IMPORT_SQL_PROFILE(‘||chr(10)||chr(9)||chr(9)||’sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => ”PROFILE_”||sql_rec.sql_id’||CHR(10)||chr(9)||chr(9)||chr(9)||chr(9)||chr(9)||’);’||chr(10)||chr(9)||’end loop;’||chr(10)||’end;’||chr(10)||’/’ FROM DUAL;
prompt ‘===================OUTPUT=========================’
set heading on
set pages 500
undef sql_id
undef old_plan_hash_value
undef plan_hash

Posted in Baselines, Scripts, Scripts, Uncategorized | Tagged: How to create profile, how to create profile oracle, how to create sql profile, manually create sql profile, oldprofile.sql, oracle sql profile, planprofile.sql, plan_hash_value | 1 Comment »

planprofile.sql

Posted by Sumit on April 1, 2011

Profile.sql:- This script can be used to convert baseline into profile. Output can be directly pasted to create a profile.

/*
Name :- PlanProfile.sql
Db Version :- 11.1.0.7
Author :- Sumit Bhatia
Version :- 1.1
Purpose :- Create a profile from baseline
Input :- sql_id, baseline plan name, bad plan hash value
*/
set feedback off
set echo off
set timing off
set pagesize 0 lines 500
set heading off verify off
accept sql_id prompt ‘enter sql_id to be fixed:- ‘
accept plan prompt’baseline plan name:- ‘
accept plan_hash prompt’enter bad plan hash value to be tuned :- ‘
prompt ‘===================OUTPUT=========================’
select chr(10) from dual;

select ‘declare’ from dual;

select ‘ar_profile_hints sys.sqlprof_attr;’ from dual;

select ‘begin’ from dual;
select chr(9)||’ar_profile_hints := sys.sqlprof_attr(‘ from dual;
select chr(9)||chr(9)||”’BEGIN_OUTLINE_DATA”,’ from dual;
SELECT chr(9)||chr(9)||””||regexp_replace(extractValue(value(h),’.'),””,”””)||”’,’ FROM sys.sqlobj$data od, sys.sqlobj$ so, table(xmlsequence(extract(xmltype(od.comp_data),’/outline_data/hint’))) h WHERE so.name = ‘&plan’ AND so.signature = od.signature AND so.category = od.category AND so.obj_type = od.obj_type AND so.plan_id = od.plan_id;

Posted in Baselines, Scripts, Scripts, Uncategorized | Tagged: Baselines, converting baselines into profiles, oldprofile.sql, oracle baselines 11g, planprofile.sql, profile oracle, profiles | 1 Comment »

Profile.sql

Posted by Sumit on April 1, 2011

Profile.sql:- This script can be used to tune bad performing queries. Output can be directly paste to create a profile

/*
Name :- Profile.sql
Db Version :- 11.1.0.7
Author :- Sumit Bhatia
Version :- 1.1
Purpose :- Create a profile
Input :- Hinted sql_id, child number, original sql_id and original plan_hash_value
*/
set feedback off
set echo off
set timing off
set pagesize 0
set heading off verify off
accept sql_id prompt 'enter hinted sql_id:- '
accept child_no prompt'enter child number:- '
accept orig_sql_id prompt 'enter original sql_id:- '
accept plan_hash prompt'enter bad plan hash value to be tuned :- '
prompt '===================OUTPUT========================='
select chr(10) from dual;

select 'declare' from dual;
select 'ar_profile_hints sys.sqlprof_attr;' from dual;
select 'begin' from dual;
select chr(9)||'ar_profile_hints := sys.sqlprof_attr(' from dual;
select chr(9)||chr(9)||'''BEGIN_OUTLINE_DATA'',' from dual;

SELECT chr(9)||chr(9)||''''||regexp_replace(extractvalue(value(d), '/hint'),'''','''''')||''','
 from
 xmltable('/*/outline_data/hint'
 passing (
 select
 xmltype(other_xml) as xmlval
 from
 v$sql_plan
 where
 sql_id like nvl('&sql_id',sql_id)
 and child_number = &child_no
 and other_xml is not null
 )
 )
d;

select chr(9)||chr(9)||'''END_OUTLINE_DATA'''||chr(10)||');'||chr(10)||'for sql_rec in ('||chr(10)||chr(9)||'select t.sql_id, t.sql_text'||chr(10)||chr(9)||' from dba_hist_sqltext t, dba_hist_sql_plan p' ||chr(10)||chr(9)|| 'where t.sql_id = p.sql_id and p.sql_id = ''&orig_sql_id'' and p.plan_hash_value = &plan_hash and p.parent_id is null'||chr(10)||chr(9)||chr(9)||') loop'||chr(10)||chr(9)||' DBMS_SQLTUNE.IMPORT_SQL_PROFILE('||chr(10)||chr(9)||chr(9)||'sql_text => sql_rec.sql_text,profile => ar_profile_hints,name => ''PROFILE_''||sql_rec.sql_id'||CHR(10)||chr(9)||chr(9)||chr(9)||chr(9)||chr(9)||');'||chr(10)||chr(9)||'end loop;'||chr(10)||'end;'||chr(10)||'/' FROM DUAL;
prompt '===================OUTPUT========================='
set heading on
set pages 500
undef sql_id
undef child_no
undef plan_hash

Posted in Scripts, Scripts, Uncategorized | Tagged: ar_profile_hints sys.sqlprof_attr, create profile, dba scripts, How to create profile, import profile, learn to tune queries, Profile, regexp_replace(extractvalue, sql tuning, Tune queries, use of sql profile, xmltable, xmltype | 1 Comment »

ndx-cleanup.sql

Posted by Sumit on March 30, 2011

This script must be executed as SYS and is used to cleanup the journal table.


declare

/* Name :- ndx-cleanup.sql */
/* DB Version :- 11.1.0.7 */
/* Author :- Sumit Bhatia */
/* Version :- 1.1
/* Purpose :- To cleanup the journal entries */
/* Input :- owner and index name */

/*declare variables*/
index_name dba_objects.object_name%type;
index_owner dba_objects.owner%type;
index_id dba_objects.object_id%type;
index_status dba_objects.status%type;
journal_name dba_objects.object_name%type;
retval boolean;
wait_for_lock binary_integer;

begin
/* INITIALIZE */
journal_name:='NONE_NONE';

/*check if index exists*/
 begin
select object_name,owner,object_id,status into index_name,index_owner,index_id,index_status from dba_objects where owner=upper('&owner') and object_name=upper('&index_name') and object_type='INDEX';
 exception
 WHEN NO_DATA_FOUND THEN
 dbms_output.put_line('INDEX NOT FOUND');
 raise;
 end;

/*check if index is stuck in journal*/
 begin
 select object_name into journal_name from dba_objects where object_name=concat('SYS_JOURNAL_',index_id);
 exception
 WHEN NO_DATA_FOUND THEN
 journal_name:='NONE_NONE';
 end;

dbms_output.put_line('OBJECT ID OF INDEX IS -----------> '||index_id);
if journal_name ='NONE_NONE'
then
 dbms_output.put_line('No journal table found. Index is clean');
else
 dbms_output.put_line('JOURNAL TABLE IS -----------> '||journal_name);
 dbms_output.put_line('CURRENT INDEX STATUS -----------> '||index_status);

 wait_for_lock := NULL;
 retval:=sys.dbms_repair.online_index_clean(index_id);
 case
 when retval then dbms_output.put_line('INDEX CLEANUP COMPLETED');
 when not retval then dbms_output.put_line('INDEX COULD NOT BE CLEANED');
 end case;

 commit;
end if;

/*confirm that journal entry has been cleared */
begin
 select object_name into journal_name from dba_objects where object_name=concat('SYS_JOURNAL_',index_id);
 exception
 WHEN NO_DATA_FOUND THEN
 dbms_output.put_line('NO JOURNAL TABLE FOUND');
 end;

end;
/

Posted in Index, Index rebuild, Journal table, Scripts, Scripts | Tagged: clear journal entry, clear journal table, ndx-cleanup, stuck index rebuild | 2 Comments »

ndx-progress.sql

Posted by Sumit on March 30, 2011

This script does not have any DDL/DML and it just queries V$ views to pull the data based on various if-else condition.It in indented to work with 11g

declare
/* Name       :-    ndx-progress.sql */
/* DB Version :-    11.1.0.7  */
/* Author     :-    Sumit Bhatia */
/* Version    :-    1.1
/* Purpose    :-    To track the progress of index and find blocking sessions */
/* Input      :-    Sid of the session runing index rebuild */
/*declare local variables */
/*capture the session information*/
 mysid                v$session.sid%type;
 myserial             v$session.serial#%type;
 mylastcall           v$session.last_call_et%type;
 mymodule             v$session.module%type;
 mymachine            v$session.machine%type;
 myusername           v$session.username%type;
 myosuser             v$session.osuser%type;
 v_command            v$session.command%type;
 v_sql_id             v$session.sql_id%type;
 v_event              v$session.event%type;
 v_sql_text           v$sql.sql_text%type;
 v_cpu_time           v$sql.cpu_time%type;
 v_elapsed_time       v$sql.elapsed_time%type;
 v_table_name         dba_objects.object_name%type;
 v_index_name         dba_objects.object_name%type; 
 v_journal_name       dba_objects.object_name%type;
 v_journal_index      dba_objects.object_name%type; 
 v_index_id           dba_objects.object_id%type; 
 v_opname             v$session_longops.opname%type;
 v_table_owner        dba_objects.owner%type;
 v_index_owner        dba_objects.owner%type;
 v_total_waits        v$session_event.total_waits%type;
 v_total_timeouts     v$session_event.total_timeouts%type;
 v_time_waited        v$session_event.time_waited%type;
 v_average_wait       v$session_event.average_wait%type;
 v_max_wait           v$session_event.max_wait%type;
 
 
/*find if rebuilding or cleaning is in progress*/
 operation_id         number; 
 
/* longops details */
    longops_count        number; --find if session is there in longops
 
 
/*capture the current state*/ 
 status_id            number;
 event_id             number;
 opname_id            number;
 
/*Description of current stage*/
 stage                varchar2(4000);
 progress             varchar2(4000);
    
 
/* Parallel and Slave process tracking */ 
 
 parallel_id          number;    --to find if parallelism is used or not
    parallel_check       number;    --number of parallel process spawned by this sid as per longops
 slave_count          number;    --number of slave spawned as per px_session
 
/*journal table details*/
 count_journal        number; --to find number of rows in journal table
 
 
/* declare type variables to find for blocking and parallel sessions */
 type type_sid is table of v$session.sid%type;
 type type_serial is table of v$session.serial#%type;
 type type_status is table of v$session.status%type;
 type type_last_call_et is table of v$session.last_call_et%type;
 type type_command is table of v$session.command%type;
 type type_module is table of v$session.module%type;
 type type_machine is table of v$session.machine%type;
 type type_sql_id is table of v$session.sql_id%type;
 type type_prev_sql_id is table of v$session.prev_sql_id%type; 
 type type_event is table of v$session_wait.event%type;
 type type_opname is table of v$session_longops.opname%type;
 type type_sofar is table of v$session_longops.sofar%type;
 type type_totalwork is table of v$session_longops.totalwork%type;
 type type_time_remaining is table of v$session_longops.time_remaining%type;
 type type_elapsed_seconds is table of v$session_longops.elapsed_seconds%type;
 type type_degree is table of v$px_session.degree%type;
 type type_req_degree is table of v$px_session.req_degree%type;
 
 
/* declare blocking variables*/
 blocking_sid type_sid;
 blocking_serial type_serial;
 blocking_status type_status;
 blocking_last_call_et type_last_call_et;
 blocking_command type_command;
 blocking_module type_module;
 blocking_machine type_machine;
 blocking_sql_id type_sql_id;
 blocking_prev_sql_id type_prev_sql_id; 
 
 
 
 /* Declare slave process */
 slave_sid type_sid;
 slave_serial type_serial;
    slave_event type_event;
 slave_totalwork type_totalwork;
 slave_sofar type_sofar;
 slave_opname type_opname;
 slave_degree type_degree;
 slave_req_degree type_req_degree;
 slave_time_remaining type_time_remaining;
 slave_elapsed_seconds type_elapsed_seconds;
begin
/* initialize */
/* operation_id =0 -->index build in progress
   operation_id =1 -->index cleanup in progress
*/
/*
 parallel_id =0 -->no parallel
 parallel_id =1 -->parallel 1 clause used
 parallel_id =2 --> parallel clause being used
*/ 
/* 
   status_id =0    -->waiting for first latch
   status_id =1    -->first latch acquired and sequential read going on to merge journal table entries before FTS.
   status_id =2    -->FTS going on
   status_id =3    -->Sequential Read going on after FTS to merge journal table entries.
   status_id =4    -->Direct path read temp going on
   status_id =10   -->waiting for ending latch   
   status_id =11   -->parallel processing going on
*/
/*
  event_id =0      -->enq: TX - row lock contention
  event_id =1      -->db file scattered read
  event_id =2      -->db file sequential read
  event_id =3      -->direct path read temp
  event_id =11     -->PX Deq: Execute Reply
  event_id =100    -->others 
*/ 

/*
  opname_id =0    -->Not started
  opname_id =1    -->FULL Scan
  opname_id =2    -->Sort output
  opname_id =3    -->Operation completed
  opname_id =4    -->Row id Scan
  opname_id =100  -->others
  
*/
/* GATHER SESSION INFO AND SQL INFO */
  select sid,serial#,last_call_et,sql_id ,event,command,module,machine,username,osuser into mysid,myserial,mylastcall,v_sql_id,v_event,v_command,mymodule,mymachine,myusername,myosuser from v$session where sid=&sid;
  select sql_text,cpu_time,elapsed_time into v_sql_text,v_cpu_time,v_elapsed_time from v$sql where sql_id=v_sql_id and rownum<2;
  select count(*) into parallel_check from v$sql where sql_id=v_sql_id and upper(sql_text) like '% PARALLEL %';
  
/* check if index cleanup is running or index rebuild in running */
 
 if v_event<>'index (re)build online cleanup'
 then
  operation_id:=0;
 else
  operation_id:=1;
 end if; 
 /*if index rebuild is in progress then....*/
 if operation_id=0 
 then
  
  /*extract object info */
  select object_name ,substr(object_name,13) into v_journal_name,v_index_id from v$locked_object lo,dba_objects do,v$lock l where lo.OBJECT_ID = do.OBJECT_ID AND l.SID = lo.SESSION_ID and l.sid=mysid and object_name like '%SYS_JOURNAL%' group by object_name,substr(object_name,13);
  select object_name into v_table_name from v$locked_object lo,dba_objects do,v$lock l where lo.OBJECT_ID = do.OBJECT_ID AND l.SID = lo.SESSION_ID and l.sid=mysid and object_name not like '%SYS_JOURNAL%' group by object_name;
  select object_name,owner into v_index_name,v_index_owner from dba_objects where object_id=v_index_id;
  
  /*check if parallel processing is going on */
  if parallel_check=0 
  then
   parallel_id:=0;
  else
   parallel_id:=2;
  end if;
  
  /*print object info*/
      dbms_output.put_line(CHR(9));
   dbms_output.put_line('SID              ---->'||mysid);
   dbms_output.put_line('SERIAL           ---->'||myserial);
   dbms_output.put_line('MODULE           ---->'||mymodule);
   dbms_output.put_line('MACHINE          ---->'||mymachine);
   dbms_output.put_line('USERNAME         ---->'||myusername);
   dbms_output.put_line('OSUSER           ---->'||myosuser);
   dbms_output.put_line('SQL TEXT         ---->'||v_sql_text);
   dbms_output.put_line('SQL ID           ---->'||v_sql_id);
   dbms_output.put_line('JOURNAL NAME     ---->'||v_journal_name);
   dbms_output.put_line('TABLE NAME       ---->'||v_table_name);
   dbms_output.put_line('EVENT_NAME       ---->'||v_event);
   dbms_output.put_line('IDLE SINCE       ---->'||mylastcall);
   dbms_output.put_line('CPU TIME         ---->'||v_cpu_time);
   dbms_output.put_line('ELAPSED TIME     ---->'||v_elapsed_time);
   
  if parallel_id=0 /*no parallel is used */
  then
/*SECTION FOR NO PARALLEL*/  
   /*check if session is present in longops */
   select nvl(count(*),0) into longops_count from v$session_longops where sid=mysid and serial#=myserial and sql_id=v_sql_id;
   
   /*if session is present in longops capture its last operation */
   if longops_count>0
   then
    select  opname into v_opname from (select opname  from v$session_longops where sid=mysid and serial#=myserial and sql_id=v_sql_id order by opname) where rownum <2;
   else
    v_opname:='INDEX REBUILD NOT STARTED YET';
   end if;
   
   /*check event info*/
   if v_event='enq: TX - row lock contention'
   then
    event_id:=0;
   elsif v_event='db file scattered read'
   then 
    event_id:=1;
   elsif v_event='db file sequential read'
   then
    event_id:=2;
   elsif   v_event='direct path read temp'
   then
    event_id:=3;   
   else
    event_id:=100;
   end if; 
   
   
   /*Compute the operation stage*/
   if v_opname ='Table Scan' and event_id <>0       --scattered read going on
   then
    opname_id:=1;
   elsif v_opname='Sort Output' and event_id <> 0   --sort output going on
   then
    opname_id:=2;
   elsif v_opname ='Sort Output' and event_id =0    --sort output completed and waiting for last latch
   then
    opname_id:=3;
   elsif v_opname='INDEX REBUILD NOT STARTED YET' and event_id=0       --first latch not acquired
   then
    opname_id:=0;
   else
    opname_id:=100; 
   end if;
   
   execute immediate 'select count(*) from '||v_index_owner||'.'||v_journal_name into count_journal;
      dbms_output.put_line('JOURNAL COUNT    ---->'||count_journal);
   
   /* compute status*/
   if event_id=0 and opname_id>0 /*enq Tx row contention and there is entry into longops then its last latch */
   then
    status_id:=10;
    stage:='Operation completed.Waiting for ending latch';
    progress:='Tx started now will not cause any blocking or entry into journal table';
   elsif event_id=0 and opname_id=0
   then
    status_id:=0; /*enq Tx row contention and no entry into longops then its waiting for first latch */
    stage:='Waiting for first latch.';
   elsif event_id=1
   then
    status_id:=2;
    stage:='First latch acquired. FTS going on';
   elsif event_id=2
   then
        /* Check if session has already perfomred scattered reador not */
    select count(*) into longops_count from v$session_longops where sid=mysid and serial#=myserial and sql_id=v_sql_id and opname='Table Scan';
    
    /*if there is no scattered read then journal table is getting merged */
    if longops_count =0
    then
     status_id:=1;
     stage:='first latch acquired and sequential read going on to merge journal table entries before FTS.';
    /*else FTS completed and sequential read to merge journal entries captured during scattered read*/
    else
     status_id:=3;
     stage:='Sequential Read going on after FTS to merge journal table entries.';
    end if;
   elsif event_id =3
   then
    status_id:=4;
    stage:='First latch acquired.FTS and sequential read completed.Direct path read temp going on.';  
   elsif event_id=100
   then
    status_id:=10;
       stage:='waiting for other wait event';
   end if;
   
   /*check what happened to Transaction during index rebuild */
   if status_id=0 and count_journal=0
   then
    progress:='No Tx started after index rebuild has commited yet. There are 0 rows in journal table';
   elsif status_id=0 and count_journal >0
   then
    progress:='Few Tx started after index rebuild command have been commited and there is/are '||count_journal||' rows into journal table';
   elsif status_id=1 and count_journal=0
   then
    progress:='Completed merging journal table entries before starting scattered read';
   elsif status_id=1 and count_journal>0
   then
    progress:='merging journal table entries before starting scattered read. There are still '||count_journal||' rows to be merged';
   elsif status_id=2 and count_journal=0
   then
    progress:='No Tx commited since Scattered read started. 0 rows in journal table';
   elsif status_id=2 and count_journal>0
   then
    progress:='Tx have committed while scattered read going on.There are still '||count_journal||' rows to be merged once FTS complete.';
   elsif status_id=3 and count_journal=0
   then
    progress:='Merging of journal entries just completed after FTS.';
   elsif status_id=3 and count_journal>0
   then
    progress:='Merging of journal entries after FTS is going on.There are still '||count_journal||' rows to be merged once FTS complete.';
   elsif status_id=4 and count_journal=0
   then
    progress:=' Sorting going on. This is last step and there are 0 rows in journal table';
   elsif status_id=4 and count_journal>0
   then
    progress:=' Sorting going on. Some transactions have again committed during this and again have to do sequential read. Currently '|| count_journal|| ' rows in journal';
   elsif status_id=10 and count_journal=0
   then
    progress:=' Waiting for Tx to complete. Entries wont go into journal table now.';
   elsif status_id=10 and count_journal>0
   then
    progress:=' Waiting for Tx to complete. Currently '|| count_journal|| ' rows in journal';
   end if;
   
   /*print status info */
   dbms_output.put_line('STAGE            ---->'||stage);
   dbms_output.put_line('LONGOPS          ---->'||v_opname);
   dbms_output.put_line('PROGRESS         ---->'||progress);
   
   /*if first latch is not acquired then what sesisons are blocking it */
   if status_id=0
   then
    select 
     s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
    bulk collect into 
     blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
    from
     v$transaction t,v$session s
    where 
     t.addr=s.taddr
    and
     s.sid in 
     (
      select 
       l.sid 
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where 
             (l.type='TM' and l.lmode=3 )   
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name
      INTERSECT   
      select
       l.sid
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where   
       (l.type='TX' and l.lmode=6 )
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name    
      INTERSECT
      select
       l.sid
      from  
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type not in ('OD','DL') and l.lmode IN (4,3))
      and
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       (do.object_name=v_table_name)  
     )
    and 
    t.start_time < (select start_time from v$transaction,v$session where sid  = mysid and serial#=myserial and addr=taddr);
    dbms_output.put_line(CHR(9));
    dbms_output.put_line(CHR(9));
    dbms_output.put_line('------------SESSIONS BLOCKING FIRST LATCH ARE-------------');
    dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');
    for i in blocking_sid.first ..blocking_sid.last 
    loop
     dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
    end loop;
    
    /* check potential blockers for ending latch */
    
    select 
     s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
    bulk collect into 
     blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
    from
     v$transaction t,v$session s
    where 
     t.addr=s.taddr
    and
     s.sid in 
     (
      select 
       l.sid 
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type='TM' and l.lmode=3 )   
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and 
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name
      INTERSECT   
      select
       l.sid
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where   
       (l.type='TX' and l.lmode=6 )
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name    
      INTERSECT
      select
       l.sid
      from  
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type not in ('OD','DL') and l.lmode IN (4,3))
      and
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       (do.object_name=v_table_name)  
     )
    and 
    t.start_time > (select start_time from v$transaction,v$session where sid  = mysid and serial#=myserial and addr=taddr)
    and s.last_call_et > 300;
     
     /*INNER BLOCK for exception handling */
     BEGIN
      dbms_output.put_line(CHR(9));
      dbms_output.put_line(CHR(9));
      dbms_output.put_line('------------SESSIONS THAT MAY BLOCK LAST LATCH ARE-------------');
      dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');
      for i in blocking_sid.first ..blocking_sid.last 
      loop
       dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
       dbms_output.put_line(CHR(9));
       dbms_output.put_line(CHR(9));
      end loop;
     EXCEPTION
     WHEN OTHERS THEN
         dbms_output.put_line(CHR(9));
      dbms_output.put_line('NO POTENTIAL BLOCKERS FOUND FOR THE LAST LATCH');
      dbms_output.put_line(CHR(9));
      dbms_output.put_line(CHR(9));
     END;                   
   elsif status_id=10
   then
    select 
     s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
    bulk collect into 
     blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
    from
     v$transaction t,v$session s
    where 
     t.addr=s.taddr
    and
     s.sid in 
     (
      select 
       l.sid 
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type='TM' and l.lmode=3 )   
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and 
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name
      INTERSECT   
      select
       l.sid
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where   
       (l.type='TX' and l.lmode=6 )
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name    
      INTERSECT
      select
       l.sid
      from  
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type not in ('OD','DL') and l.lmode IN (4,3))
      and
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       (do.object_name=v_table_name)  
     )
    and 
    t.start_time > (select start_time from v$transaction,v$session where sid  = mysid and serial#=myserial and addr=taddr);    
    dbms_output.put_line(CHR(9));
    dbms_output.put_line(CHR(9));
    dbms_output.put_line('------------SESSIONS BLOCKING LAST LATCH ARE-------------');
    dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');
    for i in blocking_sid.first ..blocking_sid.last 
    loop
     dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
    end loop;    
   end if;
/* NO PARALLEL SECTION ENDS HERE */   
  else
/* SECTION FOR PARALLEL PROCESSING STARTED  */  
   /*check event info of parallel process*/
   if v_event='enq: TX - row lock contention'
   then
    event_id:=0;
   elsif v_event='db file scattered read' /*this event will only occur if parallel 1 is used */
   then 
    event_id:=1;
   elsif v_event='db file sequential read'
   then
    event_id:=2;
   elsif   v_event='direct path read temp'
   then
    event_id:=3;   
   elsif   v_event='PX Deq: Execute Reply'
   then
    event_id:=11;   
   else
    event_id:=100;
   end if; 
   
   execute immediate 'select count(*) from '||v_index_owner||'.'||v_journal_name into count_journal;
   dbms_output.put_line('JOURNAL COUNT    ---->'||count_journal);
   
   if event_id=0  /*in case of enq Tx contention */
   then
    /*check if child process is present in in longops or not */
    select count(*) into longops_count from v$session_longops where qcsid=mysid and sql_id=v_sql_id;
    
    if longops_count > 0 /*if there were child slaves earlier then its waiting on last latch */
    then
     status_id:=10;
     stage:='Operation completed.Waiting for ending latch';
    else
     select count(*) into longops_count from v$session_longops where sid=mysid and serial#=myserial and sql_id=v_sql_id; /*check if paralel 1 clause being used */
      if longops_count > 0
      then
       status_id:=10;
       stage:='Operation completed.Waiting for ending latch';
      else
          status_id:=0;
       stage:='Waiting for first latch.';
      end if;
    end if;
   elsif event_id=1
   then
    status_id:=2;
    stage:='Parallel 1 clause being used. Scattered read going on';
   elsif event_id=2
   then
    select count(*) into longops_count from v$session_longops where qcsid=mysid and sql_id=v_sql_id; /*if there were child earlier it means its merging journal after FTS*/
    if longops_count > 0
    then
     status_id:=3;
     stage:='Sequential Read going on for merging journal table entries after FTS';
    else
     select count(*) into longops_count from v$session_longops where sid=mysid and serial#=myserial and sql_id=v_sql_id; 
     if longops_count > 0
      then
       status_id:=3;
       stage:='Parallel 1 clause used. Sequential Read going on to merge journal table entries after FTS';
     else
          status_id:=0;
       stage:='Sequential Read going on to merge journal entries before FTS.';
     end if;
    end if;
   elsif event_id=3
   then
    status_id:=4;
    stage:='Direct Path read temp going on after merging journal table entries';
   elsif event_id=11
   then
    status_id:=11;
    stage:='First Latch acquired.Parallel Processing going on'; 
   end if; 
   
   /* Check the progress*/
   if status_id=0 and count_journal=0
   then
    progress:='No Tx started after index rebuild has commited yet. There are 0 rows in journal table';
   elsif status_id=0 and count_journal >0
   then
    progress:='Few Tx started after index rebuild command have been commited and there is/are '||count_journal||' rows into journal table';
   elsif status_id=1 and count_journal=0
   then
    progress:='Completed merging journal table entries before starting scattered read';
   elsif status_id=1 and count_journal>0
   then
    progress:='merging journal table entries before starting scattered read. There are still '||count_journal||' rows to be merged';
   elsif status_id=2 and count_journal=0
   then
    progress:='No Tx commited since Scattered read started. 0 rows in journal table';
   elsif status_id=2 and count_journal>0
   then
    progress:='Tx have committed while scattered read going on.There are still '||count_journal||' rows to be merged once FTS complete.';
   elsif status_id=3 and count_journal=0
   then
    progress:='Merging of journal entries just completed after FTS';
   elsif status_id=3 and count_journal>0
   then
    progress:='Merging of journal entries after FTS is going on.There are still '||count_journal||' rows to be merged once FTS complete.';
   elsif status_id=4 and count_journal=0
   then
    progress:=' Sorting going on. This is last step and there are 0 rows in journal table';
   elsif status_id=4 and count_journal>0
   then
    progress:=' Sorting going on. Some transactions have again committed during this and again have to do sequential read. Currently '|| count_journal|| ' rows in journal';
   elsif status_id=10 and count_journal=0
   then
    progress:=' Waiting for Tx to complete. Entries wont go into journal table now.';
   elsif status_id=10 and count_journal>0
   then
    progress:=' Waiting for Tx to complete. Currently '|| count_journal|| ' rows in journal';
   elsif status_id=11 and count_journal=0
   then
    progress:=' check slave process opname for current status';
   elsif status_id=11 and count_journal>0
   then
    progress:=' check slave process opname for current status. Few Tx hhave again commited while parallel proceesing going on. Currently '|| count_journal|| ' rows in journal';
   end if;
   
   dbms_output.put_line('STAGE            ---->'||stage);
   dbms_output.put_line('PROGRESS         ---->'||progress);
   
   /* Check the locking */
   if status_id=0
   then
    select 
     s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
    bulk collect into 
     blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
    from
     v$transaction t,v$session s
    where 
     t.addr=s.taddr
    and
     s.sid in 
     (
      select 
       l.sid 
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where 
             (l.type='TM' and l.lmode=3 )   
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name
      INTERSECT   
      select
       l.sid
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where   
       (l.type='TX' and l.lmode=6 )
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name    
      INTERSECT
      select
       l.sid
      from  
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type not in ('OD','DL') and l.lmode IN (4,3))
      and
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       (do.object_name=v_table_name)  
     )
    and 
    t.start_time < (select start_time from v$transaction,v$session where sid  = mysid and serial#=myserial and addr=taddr);
    dbms_output.put_line(CHR(9));
    dbms_output.put_line(CHR(9));
    dbms_output.put_line('------------SESSIONS BLOCKING FIRST LATCH ARE-------------');
    dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');
    for i in blocking_sid.first ..blocking_sid.last 
    loop
     dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
    end loop;
    
    /* check potential blockers for ending latch */
    
    select 
     s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
    bulk collect into 
     blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
    from
     v$transaction t,v$session s
    where 
     t.addr=s.taddr
    and
     s.sid in 
     (
      select 
       l.sid 
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type='TM' and l.lmode=3 )   
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and 
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name
      INTERSECT   
      select
       l.sid
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where   
       (l.type='TX' and l.lmode=6 )
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name    
      INTERSECT
      select
       l.sid
      from  
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type not in ('OD','DL') and l.lmode IN (4,3))
      and
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       (do.object_name=v_table_name)  
     )
    and 
    t.start_time > (select start_time from v$transaction,v$session where sid  = mysid and serial#=myserial and addr=taddr)
    and s.last_call_et > 300;
     
     /*INNER BLOCK for exception handling */
     BEGIN
      dbms_output.put_line(CHR(9));
      dbms_output.put_line(CHR(9));
      dbms_output.put_line('------------SESSIONS THAT MAY BLOCK LAST LATCH ARE-------------');
      dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');
      for i in blocking_sid.first ..blocking_sid.last 
      loop
       dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
       dbms_output.put_line(CHR(9));
       dbms_output.put_line(CHR(9));
      end loop;
     EXCEPTION
     WHEN OTHERS THEN
         dbms_output.put_line(CHR(9));
      dbms_output.put_line('NO POTENTIAL BLOCKERS FOUND FOR THE LAST LATCH');
      dbms_output.put_line(CHR(9));
      dbms_output.put_line(CHR(9));
     END;
   elsif status_id=10
   then
    select 
     s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
    bulk collect into 
     blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
    from
     v$transaction t,v$session s
    where 
     t.addr=s.taddr
    and
     s.sid in 
     (
      select 
       l.sid 
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type='TM' and l.lmode=3 )   
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and 
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name
      INTERSECT   
      select
       l.sid
      from 
       v$locked_object lo,dba_objects do,v$lock l
      where   
       (l.type='TX' and l.lmode=6 )
      and 
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       do.object_name=v_table_name    
      INTERSECT
      select
       l.sid
      from  
       v$locked_object lo,dba_objects do,v$lock l
      where 
       (l.type not in ('OD','DL') and l.lmode IN (4,3))
      and
       lo.OBJECT_ID = do.OBJECT_ID
      and
       l.SID = lo.SESSION_ID
      and
       (do.object_name=v_table_name)  
     )
    and 
    t.start_time > (select start_time from v$transaction,v$session where sid  = mysid and serial#=myserial and addr=taddr);    
    dbms_output.put_line(CHR(9));
    dbms_output.put_line(CHR(9));
    dbms_output.put_line('------------SESSIONS BLOCKING LAST LATCH ARE-------------');
    dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');
    for i in blocking_sid.first ..blocking_sid.last 
    loop
     dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
    end loop;
   elsif status_id=11
   then
    select p.sid,p.serial#,p.degree,p.req_degree,s.event bulk collect into slave_sid,slave_serial,slave_degree,slave_req_degree,slave_event from v$px_session p,v$session s where p.qcsid=mysid and p.sid<>mysid and p.sid=s.sid and p.serial#=s.serial#;
    dbms_output.put_line(CHR(9));
    dbms_output.put_line(CHR(9));
    dbms_output.put_line('------------TOTAL SLAVES ARE-------------');
    dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'DEGREE'||CHR(9)||CHR(9)||'REQ_DEGREE'||CHR(9)||CHR(9)||'EVENT');
    
    for i in slave_sid.first ..slave_sid.last 
    loop
     dbms_output.put_line(slave_sid(i)||CHR(9)||slave_serial(i)||CHR(9)||slave_degree(i)||CHR(9)||CHR(9)||slave_req_degree(i)||CHR(9)||CHR(9)||slave_event(i));
    end loop;    
    
/*    select count(*) into parallel_count from v$session_longops where qcsid=mysid and sql_id=v_sql_id; */
    select count(*) into longops_count from v$session_longops where qcsid=mysid and sql_id=v_sql_id; 
    
    BEGIN
     if longops_count=0
     then
       dbms_output.put_line('------------NO ENTRY IN LONGOPS YET FOR ANY SLAVE PROCESS-------------');
     else
      select l.sid,l.serial#,l.opname,l.totalwork,l.sofar,nvl(l.time_remaining,-100),nvl(l.elapsed_seconds,-100),s.event bulk collect into slave_sid,slave_serial,slave_opname,slave_totalwork,slave_sofar,slave_time_remaining,slave_elapsed_seconds,slave_event from v$session_longops l,v$session s where l.qcsid=mysid and l.qcsid<>l.sid and l.sql_id=v_sql_id and l.sofar<>l.totalwork and l.sid=s.sid and l.serial#=s.serial# and l.sid in (select sid from v$px_session);
      dbms_output.put_line(CHR(9));
      dbms_output.put_line(CHR(9));
      dbms_output.put_line('------------LONGOPS SLAVES ARE-------------');
      dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'TOTALWORK'||CHR(9)||CHR(9)||'SOFAR'||CHR(9)||CHR(9)||'TIME REMAINING'||CHR(9)||CHR(9)||CHR(9)||'ELAPSED SECONDS'||CHR(9)||CHR(9)||'OPNAME'||CHR(9)||CHR(9)||CHR(9)||'EVENT');
    
      for i in slave_sid.first ..slave_sid.last 
      loop
       dbms_output.put_line(slave_sid(i)||CHR(9)||slave_serial(i)||CHR(9)||slave_totalwork(i)||CHR(9)||CHR(9)||CHR(9)||slave_sofar(i)||CHR(9)||CHR(9)||slave_time_remaining(i)||CHR(9)||CHR(9)||CHR(9)||CHR(9)||slave_elapsed_seconds(i)||CHR(9)||CHR(9)||CHR(9)||slave_opname(i)||CHR(9)||CHR(9)||slave_event(i));
      end loop;    
     end if;
     EXCEPTION
     WHEN OTHERS THEN
      dbms_output.put_line('------------NO ENTRY IN LONGOPS YET FOR ANY SLAVE PROCESS-------------');
     end;
   end if;
  /* parallel */ 
  end if;
/*PARALLEL PROCESSING SECTION ENDS HERE */  
 else
/* SECTION FOR INDEX CLEANUP */ 
  /* In case its a cleaning process*/
  dbms_output.put_line('Index Rebuild has been cancelled. Cleaning going on');
  select o.object_name,i.index_name,i.owner,o.owner into v_table_name,v_index_name,v_index_owner,v_table_owner from dba_indexes i,dba_objects o,v$locked_object l,dba_tables t where o.object_name = t.table_name and o.object_id = l.object_id and i.table_name=t.table_name and session_id=mysid;
  select serial#,sql_id,event into myserial,v_sql_id,v_event from v$session where sid=mysid;
  select sql_text into v_sql_text from v$sql where sql_id=v_sql_id and rownum<2;
  select 'SYS_JOURNAL_'||object_id into v_journal_name from dba_objects where object_name=v_index_name;
  execute immediate 'select count(*) from '||v_index_owner||'.'||v_journal_name into count_journal;
   dbms_output.put_line('SID              ---->'||mysid);
   dbms_output.put_line('SERIAL           ---->'||myserial);
   dbms_output.put_line('MODULE           ---->'||mymodule);
   dbms_output.put_line('MACHINE          ---->'||mymachine);
   dbms_output.put_line('USERNAME         ---->'||myusername);
   dbms_output.put_line('OSUSER           ---->'||myosuser);
   dbms_output.put_line('SQL TEXT         ---->'||v_sql_text);
   dbms_output.put_line('SQL ID           ---->'||v_sql_id);
   dbms_output.put_line('JOURNAL NAME     ---->'||v_journal_name);
   dbms_output.put_line('TABLE NAME       ---->'||v_table_name);
   dbms_output.put_line('EVENT_NAME       ---->'||v_event);
   dbms_output.put_line('IDLE SINCE       ---->'||mylastcall);
   dbms_output.put_line('CPU TIME         ---->'||v_cpu_time);
   dbms_output.put_line('ELAPSED TIME     ---->'||v_elapsed_time);
   dbms_output.put_line('JOURNAL COUNT    ---->'||count_journal);
    
  select 
   s.sid,s.serial#,s.status,s.last_call_et,s.command,s.module,s.machine,nvl(s.sql_id,'NONE'),NVL(s.prev_sql_id,'NONE') 
  bulk collect into 
   blocking_sid,blocking_serial,blocking_status,blocking_last_call_et,blocking_command,blocking_module,blocking_machine,blocking_sql_id,blocking_prev_sql_id
  from
   v$transaction t,v$session s
  where 
   t.addr=s.taddr
  and
   s.sid in 
     (
      select
       l.session_id
      from
       dba_indexes i,dba_objects o,v$locked_object l,dba_tables t
      where 
       o.object_name = t.table_name
      and 
       i.index_name =v_index_name
      and 
       i.owner = v_index_owner
      and 
       o.owner= v_table_owner
      and 
       o.object_id = l.object_id
      and 
       i.table_name=t.table_name
     );
  /*INNER BLOCK for exception handling */
  BEGIN
   dbms_output.put_line(CHR(9));
   dbms_output.put_line(CHR(9));
   dbms_output.put_line('------------SESSIONS BLOCKING CLEANING PROCESS-------------');
   dbms_output.put_line('SID'||CHR(9)||'SERIAL'||CHR(9)||'STATUS'||CHR(9)||CHR(9)||'LAST_CALL_ET'||CHR(9)||CHR(9)||'COMMAND'||CHR(9)||CHR(9)||'SQL_ID'||CHR(9)||CHR(9)||'PREV_SQL_ID'||CHR(9)||CHR(9)||'MODULE'||CHR(9)||CHR(9)||'MACHINE');   
       
   for i in blocking_sid.first ..blocking_sid.last 
    loop
     dbms_output.put_line(blocking_sid(i)||CHR(9)||blocking_serial(i)||CHR(9)||blocking_status(i)||CHR(9)||CHR(9)||blocking_last_call_et(i)||CHR(9)||CHR(9)||blocking_command(i)||CHR(9)||CHR(9)||blocking_sql_id(i)||CHR(9)||CHR(9)||blocking_prev_sql_id(i)||CHR(9)||CHR(9)||blocking_module(i)||CHR(9)||blocking_machine(i));
     dbms_output.put_line(CHR(9));
    end loop;
   select event,total_waits,total_timeouts,time_waited,average_wait,max_wait into v_event,v_total_waits,v_total_timeouts,v_time_waited,v_average_wait,v_max_wait from v$session_event where sid=mysid and event='index (re)build online cleanup';
   dbms_output.put_line(chr(9));
   dbms_output.put_line('------------CLEANUP PROCESS TIMOUT-------------');
   dbms_output.put_line('EVENT'||CHR(9)||CHR(9)||CHR(9)||CHR(9)||chr(9)||'TOTAL WAITS'||CHR(9)||CHR(9)||'TOTAL TIMEOUTS'||CHR(9)||CHR(9)||'TIME WAITED'||CHR(9)||CHR(9)||'AVG WAIT'||CHR(9)||CHR(9)||'MAX WAIT');
      dbms_output.put_line(v_event||CHR(9)||CHR(9)||v_total_waits||CHR(9)||CHR(9)||CHR(9)||v_total_timeouts||CHR(9)||CHR(9)||CHR(9)||v_time_waited||CHR(9)||CHR(9)||CHR(9)||v_average_wait||CHR(9)||CHR(9)||CHR(9)||v_max_wait);
  EXCEPTION
   WHEN OTHERS THEN
    dbms_output.put_line(CHR(9));
    dbms_output.put_line('NO POTENTIAL BLOCKERS FOUND. Cleaning GOING ON SMOOTHLY');
    dbms_output.put_line(CHR(9));
    dbms_output.put_line(CHR(9));
  END;  
 end if;
 dbms_output.put_line(CHR(9)); 
EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line(CHR(9)); 
 dbms_output.put_line('UNKNOWN CONDITION. CHECK IF JOB COMPLETES.');
 dbms_output.put_line(CHR(9));  
end;
/

Posted in Index, Index rebuild, Scripts, Scripts | Tagged: enq TM contention, index lock, index rebuild, index rebuild 11g, index rebuilod online, index scripts, index stuck, index taking long time | 6 Comments »

Oracle Site for DBA

Categories

Archives

Recent Posts

Visitors

Follow Blog via Email

Blog Stats

Top Clicks

Top Rated

Cluster

NewsCast

Archive for the ‘Index’ Category

Converting baselines into profiles

Share this:

Like this:

Journal Table cleanup process

waking up smon

Using dbms_repair.online_index_clean package

Share this:

Like this:

Behaviour of Journal Table

Test Case:-

Effects of killing index in between

Test Case

Share this:

Like this:

Many DBA’s have tendency to hit CTL C or kill the session, when there session does not comes out. Ever tries doing that with index rebuild and you will find that your index is stuck in journal table. This post is intended to describe the nature of journal table and how to clear the mess.

Structure of Journal Table

TEST CASE

Usage of Journal Tables

Test case

Understanding of Journal Table

Share this:

Like this:

Online Index Rebuild Stages (No Parallel)

Test Case

Online Index Rebuild Stages (Parallel Clause)

Test Case

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Follow “For DBA”