ORA-14097 - when using materialized view and partition table

This one was an interesting issue which came up few days back. I spent quite sometime before I solved it.

Issue was - A developer came upto me and told that he is getting "ORA-14097 - : column type or size mismatch in ALTER TABLE EXCHANGE PARTITION" while exchanging partitions.

This guy is working on a warehouse design in which huge data loads will happen on base tables through out the day and there are mviews based on which it mines and these mviews' data should be moved fact tables at midnight 12.

Now we strategize in a way that at midnight when base tables will be truncated for fresh load of next day, we exchange the partitions of these mviews with temporary partitioned tables and use "Insert /*+ append */" to move it to final fact tables. We could not have directly exchanged partitions of fact table as they might not be partitioned by day.

Now the above is all background. While investigating the issue, I was perplexed for some time when he showed me the issue. He created a temporary partitioned table using "create table as select .. the mview where 1=2" and while doing exchange partition he was getting ORA-14097.

Let me give you a simple test case:

SQL> create table test(a number);

Table created.

SQL> create materialized view log on test with rowid including new values;

Materialized view log created.

SQL> create materialized view test_mv refresh fast on demand with rowid as select * from test;

Materialized view created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('test_mv','F')

PL/SQL procedure successfully completed.

Now both table and mview have on erow each.

Let's try and exchange partition of this mview with another table.

SQL> select partition_name from user_tab_partitions where table_name='TEST_PART';

PARTITION_NAME
------------------------------
SYS_P3446

SQL> alter table test_part exchange partition SYS_P3446 with table test_mv;
alter table test_part exchange partition SYS_P3446 with table test_mv
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION


I used CTAS, there is no question abt data type and column order.

SQL> desc test_part
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

SQL> desc test_mv
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER


After doing some research, i got through this metalink article: 72332.1

According to that : "If a table has a FUNCTIONAL index on it there is an extra hidden column in COL$ which will cause an EXCHANGE to fail. Comparing USER_TAB_COLUMNS will not show this, nor will USER_UNUSED_COL_TABS but COL$ will show the difference."

SQL> select col#, name
2 from sys.col$
3 where obj# in
4 (select object_id from user_objects where object_name = 'TEST_MV');

COL# NAME
---------- ------------------------------
0 M_ROW$$
1 A

Now there you go - this M_ROW$$ was creating the problem for me.

Old saying - identifying is problem is 80% of tak that entails solving it.

Now - i created test_mv using primary key (not using rowid) and the whole exchange process worked fine!

For those who donot have PK in their tables can consider having a separate column which can be seeded using a sequence and treat that as PK to combat this issue.

Though it was trivial issue and solution. it kept me thinking for some time!

Query result cache in 11g

In 11g its possible to store the result of the SQL in a special section of shared pool called RESULT CACHE.

As the name suggests, this cache stores results. This makes subsequent SQLs run damn fast resulting in better performance.

To me, this features sounds like Materialized view - because those also when introduced boosted the performance of queries which used to take a lot of time while computing the result. The resultant data of mview was stored in table (i.e. segment/disk). And this case the resultant data is stored in memory. Though memory not being persistent, this feature still helps in subsequent runs of the same query.

The feature is broadly governed by init.ora parameter - RESULT_CACHE_MODE - which can be changed using "alter system" or "alter session". The default value of this parameter is MANUAL. You can switch it to FORCE (not recommended).

Now after this parameter has taken effect, you can modify your SQL to see its affect.

SQL> select /*+ result_cache */ deptno,sum(sal) from emp group by deptno;

DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750

NOw run this query again - with hint
and this time with autotrace traconly explain option:

SQL> set autotrace traceonly exp
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | bw8bd7rpb6h0sg1rcrwyx3rz0x | | | | |
| 2 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Here you see data being fetched from result cache.

There are some dynamic views which can give more insight.

SQL> select table_name from dict where lower(table_name) like '%result%';

TABLE_NAME
------------------------------
DBA_METHOD_RESULTS
USER_METHOD_RESULTS
ALL_METHOD_RESULTS
V$CLIENT_RESULT_CACHE_STATS
GV$RESULT_CACHE_DEPENDENCY
GV$RESULT_CACHE_MEMORY
GV$RESULT_CACHE_OBJECTS
GV$RESULT_CACHE_STATISTICS
V$RESULT_CACHE_DEPENDENCY
V$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS

TABLE_NAME
------------------------------
V$RESULT_CACHE_STATISTICS
GV$CLIENT_RESULT_CACHE_STATS
CLIENT_RESULT_CACHE_STATS$



I used V$RESULT_CACHE_OBJECTS:

1* select id,status,block_count,name from V$RESULT_CACHE_OBJECTS
SQL> /

ID STATUS BLOCK_COUNT
---------- --------- -----------
NAME
----------------------------------------------------------------------------

0 Published 1
SCOTT.EMP

1 Published 1
select /*+ result_cache */ deptno,sum(sal) from emp group by deptno


Similar to the hint "result_cache" - there is another hint - "no_result_cache"

>> no_result_cache is to be used when you have set database parameter: RESULT_CACHE_MODE to FORCE - which means oracle will try to cache the result of all queries.

Ex: select /*+ no_result_cache */ deptno,sum(sal) from emp group by deptno

To see the memory utlization - there are few dynamic views, database functions available.

SQL> set serverout on
SQL> exec dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 3488K bytes (3488 blocks)
Maximum Result Size = 174K bytes (174 blocks)
[Memory]
Total Memory = 136340 bytes [0.064% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 131200 bytes [0.061% of the Shared Pool]
....... Overhead = 65664 bytes
....... Cache Memory = 64K bytes (64 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 35 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 34 blocks
................... SQL = 34 blocks (34 count)

PL/SQL procedure successfully completed.

There is again an init.ora parameter governing the size of max result cache size i.e result_cache_max_size

This value is also seen in dbms_result_cache.memory_report output in "Maximum Cache Size " header.

Conclusion: SQL Result cache seems to be a great feature which can immensly improve the sql performance of many applications SQLs. I wish there are no major bugs in this :)

Note: Its just the begining that I have started looking into these features. If you happen to research something which I may have missed, please add to the note.