Sunday, April 29, 2007

Killing Long Running Jobs

Hi all,

I have often seen DBA's bouncing their database to get rid of stubborn jobs. In this post, I describe a way to remove the long running jobs without bouncing your database.

First, we need to identify the long running jobs, once we know what jobs are stubborn, we will mark these jobs as 'BROKEN'. We then kill the sessions linked with the jobs, and optionally kill the OS processes associated with these sessions. Next, we mark the jobs back as 'UNBROKEN' and finally run the job.

Note: Before marking the jobs as Broken or killing the session, run the following SQL’s and store the output of each query.

1. Find all the Long Running Jobs

Use the following query to get a detailed information on long running jobs

select djr.Sid,
dj.Log_User,
dj.Job,
dj.Broken,
dj.Failures,
dj.Last_Date,
dj.Last_Sec,
dj.This_Date,
dj.This_Sec,
dj.Next_Date,
dj.Next_Sec,
dj.Interval,
dj.What
from Dba_Jobs dj,
Dba_Jobs_Running djr
where dj.job = djr.job;

2. Mark the Jobs as "BROKEN'

Using the below query, I create a script to mark all the long running jobs as broken.

select ‘Exec DBMS_JOB.BROKEN(‘j.job ‘,TRUE);’
from dba_jobs dj,
dba_jobs_running djr
where dj.job = djr.job;

3. Kill Oracle Sessions

Using the below SQL query, you can kill all the sessions associated with the long running jobs.

select 'ALTER SYSTEM KILL SESSION ''' s.sid','s.serial#''';'
from dba_jobs dj, dba_jobs_running djr, v$process p , v$session s
where dj.job = djr.job
and p.addr = s.paddr
and djr.sid = s.sid;

4. Kill the OS Sessions

Optionally, you may kill the OS sessions to speedup PMON in cleaning the killed session undo data.

select 'Kill -9 ' p.spid
from dba_jobs dj, dba_jobs_running djr, v$process p , v$session s
where dj.job = djr.job
and p.addr = s.paddr
and djr.sid = s.sid;

5. Mark the jobs as Unbroken

Wait till all the sessions marked as ‘KILLED’ have been really killed and disappear from the V$SESSION view. Next, mark all the jobs as unbroken using the below query.

select ‘Exec DBMS_JOB.BROKEN(‘||j.job|| ‘,FALSE);’
           from dba_jobs dj, 
                  dba_jobs_running djr
         where dj.job = djr.job; 

6. Manually Run the jobs

Now that all the long running jobs have disappeared and its time to put the jobs back in the job queue.

select ‘Exec DBMS_JOB.RUN(‘||j.job|| ‘);’
           from dba_jobs dj, 
                  dba_jobs_running djr
         where dj.job = djr.job; 

7. Query ALL_JOBS views

Query the ALL_JOBS view to see that all the jobs are back in motion.

This way you will put the jobs back in trace without the need to recycle the database.

Happy reading !!!

Tuesday, April 03, 2007

Virtual Indexes in Oracle

In the world of virtualization, almost everything is getting virtualized like Virtual Machines, Virtual IP Address, and Virtual reality and so on. There’s one more addition to it “Virtual Indexes”. This is an undocumented feature of Oracle and the virtual indexes are also referred to as “Fake Indexes”.

Virtual Indexes are pseudo-indexes which do not exist as a segment in database. They can be used to test an index usage prior to actually creating one. Virtual indexes allows the CBO to evaluate the index for a SQL statement by building an explain plan that is aware of the new virtual index. This allows a DBA to answer “if an index in created would the optimizer actually use it?” question.

The virtual indexes are very helpful when you have to test against a table with huge data.

Creating a Virtual Index

Create a table using the following statement:
SQL> create table v_test as select object_id rno, object_name name from all_objects;
Table created.

You use the “NOSEGMENT” clause with the CREATE INDEX statement to create a Virtual Index.

SQL> create index v_ind on v_test(rno) NOSEGMENT;
Index created.

Query the USER_SEGMENTS view to verify whether the index segment is created or not.

SQL> select segment_name, bytes from user_segments where segment_name = 'V_IND';
no rows selected
SQL> 
SQL> select segment_name, bytes from user_segments where segment_name = 'V_TEST';
SEGMENT_NAME BYTES
--------------- ----------
V_TEST 196608

As seen above, you have a segment created for the table but none for the index.

Using of Virtual Indexes By setting the hidden parameter “_use_nosegment_indexes“, you inform the optimizer to use any underlying virtual indexes while generating an Explain Plan output.

Note: Hidden parameters should be used after consulting Oracle Support.
SQL> alter session set "_use_nosegment_indexes" = true;

Now, use the Explain Plan command to generate the plan using the following query:

SQL> explain plan for select * from v_test where rno = 5;
Explained.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 1019049159
--------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 30 5 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID V_TEST 1 30 5 (0) 00:00:01
* 2 INDEX RANGE SCAN V_IND 17 1 (0) 00:00:01
--------------------------------------------------------------------------------------

It is evident from the above explain plan output that our newly created virtual index can benefit us. It is also clear that the CBO knows about the virtual index existence.

Now, let us trace our SQL and find out whether the virtual index will actually be used by the CBO to fetch the data.

I will use both 10046 and 10053 extended trace events to trace the SQL.

Tracing using 10046

To differentiate my trace file from other existing trace files and to easily identify the trace file, I set tracefile_identifier parameter for my current session, then enable the tracing, run the SQL statement and turn off the tracing as shown below:

SQL> alter session set tracefile_identifier='T10046';
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> select * from v_test where rno =5;
SQL> alter session set events '10046 trace name context off';
SQL> exit

I have pasted the section of the trace file in which we are interested i.e., the Execution Plan. Following is the excerpt of the trace file:

=================================================
:
:
WAIT #1: nam='SQL*Net message from client' ela= 2275 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=906714236
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=10769 op='TABLE ACCESS FULL V_TEST (cr=39 pr=0 pw=0 time=69 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=906718527
:
:
=================================================

From the above report it’s clear that CBO is using a “FULL TABLE SCAN” on V_TEST to execute the query. The CBO is intelligent enough to discard the fake index and look for an otherwise optimal plan.

Tracing using 10053

Similarly, I trace the same SQL statement using the 10053 extended trace.
SQL> alter session set tracefile_identifier=’T10053’;
SQL> alter session set events '10053 trace name context forever, level 1';
SQL> alter session set "_use_nosegment_indexes" = true;
SQL> select * from v_test where rno = 5;
SQL> alter session set events '10053 trace name context off';
SQL> exit

It is evident from the trace output shown below, that the CBO is opting for a “FULL TABLE SCAN” as it is aware that V_IND is a virtual index with no segments.

============
Plan Table
============
-------------------------------------+-----------------------------------+
Id Operation Name Rows Bytes Cost Time
-------------------------------------+-----------------------------------+
0 SELECT STATEMENT 11
1 TABLE ACCESS FULL V_TEST 1 21 11 00:00:01
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter("RNO"=5)

Conclusion:

As I mentioned earlier, this is undocumented, so use it at your own risk. Virtual indexes should be used when testing queries before creating an index on a huge table as opposed to creating a real index which might take hours depending on the size of the data.

When using the Explain plan, CBO tests the virtual index usage and will let you know whether it will be fruitful to create an index or not. But while actually executing the query it will opt for an optimal execution plan after discarding the virtual index.

Regards