Home » RDBMS Server » Performance Tuning » Materialized view option (suggestion) (Oracle 11.2.0.4, Linux x86 64)
Materialized view option (suggestion) [message #637715] Fri, 22 May 2015 03:34 Go to next message
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.
Re: Materialized view option (suggestion) [message #637717 is a reply to message #637715] Fri, 22 May 2015 03:57 Go to previous messageGo to next message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, 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 Go to previous messageGo to next message
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.
Re: Materialized view option (suggestion) [message #637719 is a reply to message #637718] Fri, 22 May 2015 04:57 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
I would enable result caching for the query. Much easier.
Re: Materialized view option (suggestion) [message #637721 is a reply to message #637719] Fri, 22 May 2015 05:58 Go to previous messageGo to next message
hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA
Member
yes, that would also be great option, many thanks
Re: Materialized view option (suggestion) [message #637730 is a reply to message #637721] Fri, 22 May 2015 09:34 Go to previous message
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
Previous Topic: SQL execution takes long time
Next Topic: How Can I Tune this Query
Goto Forum:
  


Current Time: Mon Oct 22 00:22:45 CDT 2018