Monday, July 27, 2015

Gather statistics on spatial index

When you run dbms_stats.gather_table_stats on tables with spatial indexes or dbms_stats.gather_index_stats directly on the spatial index the routine may return without an error even if no statistics gathering took place. You can verify this by looking at the LAST_ANALYZED column in DBA_INDEXES:

select owner,index_name,last_analyzed
from dba_indexes 
where index_type='DOMAIN';

Another way to verify if statistics gathering for these indexes took place is to look for tables with names that start with MDXT:

select owner,table_name 
from dba_tables
where table_name like 'MDXT_%';

The user that owns these indexes must be granted CREATE TABLE directly and not through a role. 

You can find some details about these tables in Doc ID 1610877.1 on My Oracle Support.  In case you have many tables that starts with MDXT you can see what are actually needed by running this:

select sdo_index_owner, index_name, sdo_index_table 
from mdsys.all_sdo_index_info;

MDXT-tables whose number does not appear among the tables listed in sdo_index_table (name starts with MDRT) can be dropped according to the note at MOS.

Sunday, July 5, 2015

Orphan Processes in the database

When you need to find the OS-process on the database server for an oracle session (dedicated server) you can join v$session with v$process:

select p.spid
from v$session s join v$process p on(s.paddr=p.addr)
where s.sid=42;

But if you kill a session with 'alter system kill session ...'  the link between these views are broken because the value in v$session.addr changes. In order to look for these orphan processes run this query:

select spid, program from v$process 
    where program!= 'PSEUDO' 
    and addr not in (select paddr from v$session)
    and addr not in (select paddr from v$bgprocess)
    and addr not in (select paddr from v$shared_server);

You may check with OS tools like ps on Linux to see that these are indeed dead processes or with strace to see what they are doing and eventually kill them.