Is my Materialized View Log being used?

We have a large number of production databases that are being used as the source for Materialized Views.

Often the developers just add an MV Log to a new table on the chance that it might be used. As we all know MV Logs or MLogs are used to record changed rows within a table, so that the MV based upon that source table, can perform a fast refresh.

I needed a way to locate all MLOGs and work out if they are still or have even been used. The solution to this is quite simple.

The object SYS.MLOG$ contains various details of the MLOGs usage. The information is better at 11g but ok at 10g.

The following query lists all tables who's name starts with MLOG$, and then added the SYS.MLOG$ details. I've used an outer join to still show tables that have never been used as MV Logs. The v$database is only there because I was doing a lot.

select db.name, tabs.owner, tabs.table_name, mlog.master, mlog.log, mlog.youngest
from sys.mlog$ mlog, v$database db, dba_tables tabs
where tabs.owner = mlog.mowner(+) and tabs.table_name = mlog.log(+)
and tabs.table_name like 'MLOG$%' and tabs.table_name not in ('MLOG$','MLOG_REF$')
order by 2,3;

Let me know what you think of my solution.

Happyjohn

Resumable Timeouts caused by ORA-01652 but Tablespace is not full

* when you have a resumable timeout set so that SQL waiting for tablespace resources can pause until that space becomes available.

I had this interesting problem the other day, and while the solution is reasonably obvious, it didn't occur to me immediately at the time.

You can detect resumable pauses with this statement...

select user_id, instance_id, status, name, error_msg, suspend_time, resume_time from dba_resumable;

This then shows one or more sessions where the resumable wait has cleared or an error is display showing a wait in progress. At my company we monitor for these waits extending to long and then report them to call-out.

In this case, as always, this is the error message shown. There were 2 sessions waiting on a large data warehouse.

ORA-01652: unable to extend temp segment by string in tablespace cma_ts1

Earlier in the day, I had got the same message and when I checked, the tablespace was indeed almost completely full. It was on ASM and the single file in the tablespace was 1.5Tb. Because it's a production database I didn't hang around and increased the datafile to 2Tb.

ALTER DATABASE DATAFILE '+DATA_DG/dgb_tsm1/datafile/cma_ts1.1145.851597989' AUTOEXTEND ON MAXSIZE 2000G
database datafile '+DATA_DG/DGB_TSM1/DATAFILE/CMA_TS1.1145.851597989' altered.

So with an additional 500Gb, I figured we'd be ok for some time. However 3 hours later I was called out again. Same error.

However now, only 91% of the data file was being used. This equates to 180Gb still free.

Of course what I didn't immediately spot was that just checking the size of the free space isn't enough. You must also consider the amount of space being requested by running processes.

This statement gives you the tablespace usage details.

select file_name, file_id, round(user_bytes/1024/1024,2) "Used(Mb)", round(user_bytes/1024/1024/1024,2) "Used(Gb)", status, autoextensible
from dba_data_files c
where tablespace_name = 'CMA_TS1' order by file_id desc;

And this the percent used. (There are other ways).

select tablespace_name, used_percent, tablespace_size, used_space, (tablespace_size - used_space) free
from dba_tablespace_usage_metrics
where tablespace_name = 'CMA_TS1';

Having obtained the space used figures, take a look at the amount of space currently being requested by the running processes.

select segment_name, bytes/1048576/1024 from dba_segments where tablespace_name='CMA_TS1'
order by 2 desc;

If the segment name is a number rather than an object name, then this represents those temporary segments we're looking for. In my case there were 2 (as expected), and these were sized at 337Gb and 240Gb. Clearly neither of these next extent requirements would fit into the free space available.

I could have added more diskspace, but the prudent thing to do was to locate the session owners and have a quiet word with them. Having done this and explained the realities of initial and next extents, we killed the processes and the scripts were rewritten.

HappyJohn