Home » RDBMS Server » Server Utilities » materialized view with nologging (oracle 10.2 solaris)
materialized view with nologging [message #561294] Fri, 20 July 2012 05:09 Go to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Dear All,

I have a live OLTP system where i have delived a solution for performance tunning , as a part of performance tunning i have created a MV which as below. I have created MV with no logging option as this will be refreshed at 3 min interval. i have compared the AWR report prior to deploy MV and after deployment. After deployment it have increased the redo by 4 times. Is there any way i can reduce the redo and what is wrong with mv with nologging.

CREATE MATERIALIZED VIEW "OVSD"."GSP_RELATEDEVENTS1"  
NOLOGGING 
 REFRESH FORCE  
--START WITH SYSDATE   NEXT SYSDATE + 3/1440
                         AS                         
  SELECT DISTINCT ser_id AS R_SER_ID,
    WM_CONCAT (REL_NAME) AS R_NAME
  FROM sd_servicecalls,
    cdm_serv_evt_relations,
    GSP_EVENTS
  WHERE sre_from = ser_oid
  AND sre_to     = EVENT_TO_OID
  GROUP BY ser_id;
  
  ALTER MATERIALIZED VIEW "OVSD"."GSP_RELATEDEVENTS1" COMPILE;
  
  drop materialized view GSP_RELATEDEVENTS1;
  
  create index ser_id_mv_idx on GSP_RELATEDEVENTS1(r_ser_id);
  
  analyze table GSP_RELATEDEVENTS1 compute statistics


any help will be appriciated
Re: materialized view with nologging [message #561297 is a reply to message #561294] Fri, 20 July 2012 05:59 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
Are you aware that your materialized view is not fast refreshable? So you are building it completely, every three minutes.
Re: materialized view with nologging [message #561300 is a reply to message #561297] Fri, 20 July 2012 06:00 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Yes , my mv is doing complete refresh, but can i do something to reduce redo generated by this. nologging can't reduce the redo?
Re: materialized view with nologging [message #561303 is a reply to message #561300] Fri, 20 July 2012 06:26 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
Quote:
nologging can't reduce the redo?
No.
You need an MV that is fast refreshable, which will never work with your query. Why do you have both DISTINCT and GROUP BY? Why do you have either? Your use of column aliases will cripple the query re-write capability. You will need the ROWIDs in the view and the logs.
There are many restrictions on materialized views with both joins and aggregates. If a three minute refresh is necessary for a complex view, I suspect you may have chosen the wrong solution to the problem. Sorry not to be more positive.
Re: materialized view with nologging [message #561306 is a reply to message #561303] Fri, 20 July 2012 06:48 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Hi , My MV can't be fast refreshable so i have changed it to complete at refresh at 5 min. since my mv has wm_concat function it can't be fast refresh. You have really pointed out distinct and group clause, i have removed the distinct now. The query which is reffering this view is being visit very often and it used to return data in 43 sec . with the implementation of MV data is coming out in less than 2 sec. I can't see any good solution over it so i chose MV. I have increased the refresh time to 5 min as this is transaction system and data is needed immediately and hopeful that it will reduced the redo by 10%. Sir does nologging has any impact on redo or shal i remove it?.
Re: materialized view with nologging [message #561324 is a reply to message #561306] Fri, 20 July 2012 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First you have to understand the SQL you write.
To use DISTINCT with GROUP BY is a silly thing. If you use GROUP BY then all rows are distinct, otherwise this means that 2 rows are not grouped.

Then NEVER use ANALYZE to gather statistics, use DBMS_STATS.

Regards
Michel

[Updated on: Fri, 20 July 2012 08:49]

Report message to a moderator

Re: materialized view with nologging [message #561372 is a reply to message #561324] Fri, 20 July 2012 16:29 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
If the only goal is to reduce redo, then you can create a pl/sql with 2 cursors in it.
Suppose your table is called "a", and the duplicated is called "b".
For simplicity, lets suppose that "b" is just a simple replication of table "a".
Cursor 1 would delete all the rows from "select * from b minus select * from a;"
Cursor 2 would insert all the rows from "select * from a minus select * from b;"
A new row would be inserted with cursor 2. A deleted row would be delete with cursor 1.
A modified rows would be both deleted and inserted.
This way you are replacing redo with selects from the minus commands.
Another option would be to write a trigger that appends to a table whenever
DML occurs on table "a" and then the cursor would apply the changes to "b".
Re: materialized view with nologging [message #561388 is a reply to message #561372] Sat, 21 July 2012 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OP's mview is not a duplicate, it is an aggregate.

Regards
Michel

[Updated on: Mon, 23 July 2012 23:19]

Report message to a moderator

Re: materialized view with nologging [message #561545 is a reply to message #561388] Mon, 23 July 2012 14:50 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Thanks Michel.

I found the following helpful by Tom Kyte about reducing redo for a MV.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:15695764787749
Previous Topic: Migrating using Transportable tablespace
Next Topic: export excel sheet data in database table
Goto Forum:
  


Current Time: Sun Sep 27 13:12:52 CDT 2020