OraFAQ Forum: Performance Tuning » Materialized view option (suggestion)

Home » RDBMS Server » Performance Tuning » Materialized view option (suggestion) (Oracle 11.2.0.4, Linux x86 64)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Materialized view option (suggestion) [message #637715]

Fri, 22 May 2015 03:34

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Hi,

In my production environment, one of the query takes only 20 seconds to complete.
But the number of execution are very large around 5000 executions per day, because of which it always appears in AWR top SQL scripts.

So, can I suggest application team to create M-VIEWS so that they can directly fetch M-Views instead of tables every time ?

Please correct me if I am wrong.
Many thanks in advance.

Report message to a moderator

Re: Materialized view option (suggestion) [message #637717 is a reply to message #637715]

Fri, 22 May 2015 03:57

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

What is the query on? A table? A view? How often does the data change? Maybe you should read up on Materialized Views.

[Edit: link]

[Updated on: Fri, 22 May 2015 03:59]

Report message to a moderator

Re: Materialized view option (suggestion) [message #637718 is a reply to message #637717]

Fri, 22 May 2015 04:08

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

It's SELECT query on TABLES onle not on VIEWS. Data change frequency not more its once daily.

Report message to a moderator

Re: Materialized view option (suggestion) [message #637719 is a reply to message #637718]

Fri, 22 May 2015 04:57

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

I would enable result caching for the query. Much easier.

Report message to a moderator

Re: Materialized view option (suggestion) [message #637721 is a reply to message #637719]

Fri, 22 May 2015 05:58

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

yes, that would also be great option, many thanks

Report message to a moderator

Re: Materialized view option (suggestion) [message #637730 is a reply to message #637721]

Fri, 22 May 2015 09:34

bpeasland
Messages: 51
Registered: February 2015
Location: United States

Member

I agree with John. If possible, pursue the Result Caching option. That was my first thought before I got to his reply. Result Caching may not work for all situations though.

MV's are another option, but like any option, it has its strengths and its weaknesses. I rarely query a MV directly. Instead, I have Oracle access it via Query Rewrite. Once this regularly running query is parsed, Oracle should start using it anyway.

Cheers,
Brian

Report message to a moderator

Previous Topic:	SQL execution takes long time
Next Topic:	How Can I Tune this Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 19:55:16 CDT 2024