What is happenng inside the DB

To find out all the activities in side database in detail

Login as sys and execute the below query:

select
p.spid os_process_id,
'alter system kill session ' || ''''||s.sid||','||s.serial#||''' immediate;' Kill_command,
nvl (p.pname,'user process') process_name,
nvl(s.username,'BG') USERNAME,
s.sid,
s.serial#,
ss.px_servers_executions,
s.status,
s.osuser,
s.machine,
s.blocking_session,
s.blocking_session_status,
s.state,
s.event,
s.wait_class,
round(s.wait_time_micro/1000000) wait_time_insec,
s.service_name,
s.program,
s.module,
s.logon_time,
s.sql_id current_sql_id,
nvl(s.sql_id,prev_sql_id) last_exc_sql_id,
nvl2(BLOCKING_SESSION,s.sid||' Is Blocked by: '||blocking_Session,'No Block') blocking_status,
ss.sql_text,ss.sql_fulltext
from v$session s,v$process p,v$sqlarea ss
where s.paddr=p.addr
and ss.sql_id(+)=s.sql_id
--and s.username like 'USERNAME'
--and s.osuser != 'OSUSER_NAME'
--and s.machine like '%MACHINTNAME%'
order by 5;

**************

When to rebuild index ?

Index is one of interesting objects in database, which always attract DBAs and Developers to fine tune their queries. Because index is just like salt in food. It should be used optimized quantity. Some time index doesn't show actual expected performance. In those cases we need to check whether index need to be rebuild or not.

There are certain cafeterias for rebuilding an index:

The number of deleted leaf nodes :-
The number of index nodes that have been logically deleted as a result of  row deletes. Remember that Oracle leaves "dead" index nodes in the index when rows are deleted. This is done to speed up SQL deletes, since Oracle does not have to re balance the index tree when rows are deleted.

Index height :-  The number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows. Hence, an Oracle index may have four levels, but only in those areas of the index tree where the massive inserts have occurred. Oracle indexes can support many millions of entries in three levels.

Gets per index access :- The amount of logical I/O that is required to fetch a row with the index. As you may know, a logical "get" is not necessarily a physical I/O since much of the index may reside in the Oracle buffer cache. Unfortunately, Oracle does not make it easy to capture this information. In Oracle we must issue these commands:

ANALYZE INDEX index_name COMPUTE STATISTICS;
ANALYZE INDEX index_name VALIDATE STRUCTURE;

After you analyze the report above, you may want to consider rebuilding any index where the height is more than three levels, since three levels will support millions of index entries.

As you may know, you can easily rebuild an Oracle index with the command:

ALTER INDEX index_name REBUILD;



      ********************