Home » RDBMS Server » Performance Tuning » Oracle 9i materialized view refresh problems
Oracle 9i materialized view refresh problems [message #184453] Wed, 26 July 2006 10:36 Go to next message
radhakrishnane
Messages: 4
Registered: July 2006
Junior Member
hi, i'm trying to create a materialized view in oracle 9i to speed up some of our reports. It creates ok and the 'refresh state' goes to 'FRESH' (seen from enterprise manager console). But, in 5 mins, when the mv is set to automatically refresh, both the 'refresh state' and 'compile state' goes to 'NEEDS_COMPILE'. I have created mv_logs on all the schema/tables that the mv uses. Below is the sql i used to create the mv. TIA.

CREATE MATERIALIZED VIEW "SCHEMA1"."MV_TEST"
BUILD IMMEDIATE
REFRESH FORCE
START WITH to_date('18-Jul-2006 11:00:00 AM','dd-Mon-yyyy HH:MI:SS AM')
NEXT sysdate + 5/1440
AS
select 'TEXT' type, to_char(actiondate,'YYYY') TRANS_YEAR, substr(get_desc(trans_type),30) DESC, decode(trans_type,'A', get_qty(table_b.param1,table_c.param2), get_diff_qty(table_b.param1,table_c.param2,table_a.param3)) QTY
from table_a, table_b, table_c
where table_a.trans_type in ('A','B')
and table_a.trans_type = table_b.trans_id
and table_a.actiondate = table_c.transdate (+)
and table_a.trans_type = table_c.type_id (+)
and table_a.fld1 = table_c.fldc (+)
and table_c.fldd (+) = 'value01'
union all
select 'TEXT' type, to_char(actiondate,'YYYY') TRANS_YEAR, substr(get_desc(trans_type),30) DESC, decode(trans_type,'A', get_qty(table_b.param1,table_d.param2), get_diff_qty(table_b.param1,table_d.param2,table_a.param3)) QTY
from table_a, table_b, table_d
where table_a.trans_type in ('C','D')
and table_a.trans_type = table_b.trans_id
and table_a.actiondate = table_d.transdate (+)
and table_a.trans_type = table_d.type_id (+)
and table_a.fld1 = table_d.fldc (+)
and table_d.fldd (+) = 'value01'
Re: Oracle 9i materialized view refresh problems [message #184596 is a reply to message #184453] Thu, 27 July 2006 03:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This Mv hasn't specified a refresh option of ON COMMIT or ON DEMAND.

The default is ON DEMAND, so you don't have an auto refreshing MV.
Re: Oracle 9i materialized view refresh problems [message #184690 is a reply to message #184596] Thu, 27 July 2006 08:28 Go to previous messageGo to next message
radhakrishnane
Messages: 4
Registered: July 2006
Junior Member
thanks. no, i didnt specify to refresh it automatically on each commit, instead i specified it to refresh every 5 mins. when i specify on commit, it gives me ora-12054 error! tia.
Re: Oracle 9i materialized view refresh problems [message #184696 is a reply to message #184690] Thu, 27 July 2006 08:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Sorry - missed that bit.

Acording to the docs:

NEEDS_COMPILE - Some object upon which the materialized view depends has changed (other than normal DML changes). An ALTER MATERIALIZED VIEW...COMPILE statement is required to validate this materialized view.

Where are you getting the 'refresh state' from?

It looks like something is altering your table after the view is created.
Re: Oracle 9i materialized view refresh problems [message #184729 is a reply to message #184453] Thu, 27 July 2006 10:26 Go to previous messageGo to next message
radhakrishnane
Messages: 4
Registered: July 2006
Junior Member
thanks for the help.

i'm using enterprise manager to view the refresh state.

after i create the mv, the refresh state is 'fresh'. and then, i just add a record to one of the tables and immediately the refresh state goes to 'needs_compile' and so does the compile state.

one other thing i noticed is that the 'can use log' column (on the enterprise manager) for this mv says 'no'. i have set up refresh logs on all the tables that the mv uses; not sure if that 'can use log' has anything to do with the refresh log.

pls bear with me if i'm asking basic questions. tia.
Re: Oracle 9i materialized view refresh problems [message #184814 is a reply to message #184729] Fri, 28 July 2006 02:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm a bit in the dark here too.

Some questions:

1) What is the STATUS of this view in ALL_OBJECTS when its COMPILE_STAT is NEEDS_COMPILE?

2) What version of Oracle are you running

3)i) If you compile the view, add some data, commit (so the status goes to 'NEEDS_COMPILE', can you still select the original data from the view?

ii) When the next refresh has happened, can you now see the new data in the view?

Re: Oracle 9i materialized view refresh problems [message #184872 is a reply to message #184814] Fri, 28 July 2006 07:31 Go to previous messageGo to next message
radhakrishnane
Messages: 4
Registered: July 2006
Junior Member
thks for the reply.
the status of that mv in all_objects in 'invalid'.
i'm running oracle 9i (9.2.0.4.0)
yeah, even when the mv is in needs_compile state, i can still select my old records; but the one i just added does not appear.
and, when i manually compile & refresh it again, i can see my new data there.

thanks for your help.
Re: Oracle 9i materialized view refresh problems [message #184879 is a reply to message #184872] Fri, 28 July 2006 08:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The switching to 'Needs Compile' is described in metalink as normal behaviour (Doc Id 264036.1 )

I can reproduce the problem, but when my views refresh, the compile state switches backto VALID and the new data is visible.

Can you just check with
select mview_name,last_refresh,start_with from user_mviews

and ee if your mview is refreshing itself at all.
icon4.gif  Re: Oracle 9i materialized view refresh problems [message #203861 is a reply to message #184879] Thu, 16 November 2006 12:05 Go to previous messageGo to next message
jacquesh
Messages: 6
Registered: November 2006
Junior Member
Hello,

do you have found a solution at your problem ?

I have the same problem and after search long hours, no result.

If you have found a solution, please post a solution Wink)

thanks.
Re: Oracle 9i materialized view refresh problems [message #204112 is a reply to message #184453] Fri, 17 November 2006 21:03 Go to previous messageGo to next message
jacklhg
Messages: 1
Registered: April 2006
Junior Member
To make ON COMMIT fast refresh possible,
I think you can first try to add Rowids of all the tables in the FROM list to the SELECT list of the query.




To find why fast refresh is not allowed by yourself, you may try this:

SQL>EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW('YOUR_MV_NAME');
SQL>select capability,possible from mv_capabilities_table where mivew_name='YOUR_MV_NAME';


The following is for your information:
Restrictions on Fast Refresh on Materialized Views with Joins Only
Defining queries for materialized views with joins only and no aggregates have the following restrictions on fast refresh:

All restrictions from "General Restrictions on Fast Refresh".

They cannot have GROUP BY clauses or aggregates.

Rowids of all the tables in the FROM list must appear in the SELECT list of the query.

Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.


Re: Oracle 9i materialized view refresh problems [message #204158 is a reply to message #204112] Sat, 18 November 2006 05:11 Go to previous messageGo to next message
jacquesh
Messages: 6
Registered: November 2006
Junior Member
ok,
thanks for your help. I'll try it next week.
Razz
Re: Oracle 9i materialized view refresh problems [message #204159 is a reply to message #184453] Sat, 18 November 2006 05:16 Go to previous message
jacquesh
Messages: 6
Registered: November 2006
Junior Member
The real problem is about NEEDS_COMPILE value in ALL_MVIEWS ?

From a complex query with sub-select, analytic function (LAG, LEAD,

...) I've create a materialized view with query rewrite option enabled.

my problem as follow : Each refresh of MV failed with an error in
ALL_MVIEWS ; the column 'COMPILE_STAT' is set to NEEDS_COMPILE.

If I try a manual refresh it's ok, when i compile view via an alter
mat. view ... it's ok...

any idea to fix this 'bug' ??

thanks.
Previous Topic: oracle reads from buttom to top
Next Topic: Regarding parse_calls in v$sqlarea
Goto Forum:
  


Current Time: Tue Dec 06 06:18:01 CST 2016

Total time taken to generate the page: 0.09475 seconds