Home » RDBMS Server » Performance Tuning » Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) (Oracle 11)
Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659461] Wed, 18 January 2017 07:45 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel ,

In Our production environment we have 11 materialized view which are refreshing by the DBA_JOBS for each one minute by using FAST refresh.


The queries which are firing as a apart of materialized view refresh from the corresponding log tables( with the help of dbms_mview.refresh method )are having the highest parse calls total than the the executions total.This resulting to the negative value to the execute_to_parse

The result of the following query is giving queries which are related to MV Refresh.


select  ROUND( 100*(1-( st.parse_calls_total / st.executions_total )),2) execute_to_parse,
sq.sql_text , st.executions_total , st.parse_calls_total , st.*
from  SYS.dba_hist_sqlstat  st  , SYS.dba_hist_sqltext  sq
where executions_total < parse_calls_total
and  st.sql_id   = sq.sql_id 
and executions_total <>0 
order by  st.snap_id  desc 


DBA Team identified that because of this reason its decreasing the performance of the database.

Could you please suggest any solution to avoid this


Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659463 is a reply to message #659461] Wed, 18 January 2017 08:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Wed, 18 January 2017 05:45


Could you please suggest any solution to avoid this


What exactly is "this"?

If you could wave magic wand & reduce CPU time for "this" to zero, how much faster would the application be?
post SQL & RESULT to show quantifiable answer.
Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659466 is a reply to message #659463] Wed, 18 January 2017 08:27 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Blackswan

Thanks for your reply.

Here my intention is parse calls total are greater that executions totals for mv related refresh queries. How can I avoid this situation to improve the performance ?

Please find the attached screen shot for reference

Thanks
SaiPrayumn
Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659468 is a reply to message #659466] Wed, 18 January 2017 08:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

How many total SQL parse are done daily?
If you totally eliminated the parses you highlighted, how much faster will the application? Please quantify.
You suffer from Compulsive Tuning Disorder!
Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659487 is a reply to message #659468] Thu, 19 January 2017 01:48 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member


Hi Blackswan ,

with the help of DBA Team we came to know that the problem is with MV Refresh. Now i am analyzing that part .
Here my question is what will happen if the refresh it self take the more time than time interval which is defined for that Refresh.

Thanks
SaiPradyumn


Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659488 is a reply to message #659487] Thu, 19 January 2017 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And BlackSwan's question is "is there actually a problem?".

Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659489 is a reply to message #659488] Thu, 19 January 2017 02:12 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Hi Blackswan/Michel ,

I am interested to learn and solve the issues .I got this task from my manager .
Am I going in wrong direction ?. I am not understanding why you are asking that kind of questions

Thanks
SaiPradyumn
Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659490 is a reply to message #659487] Thu, 19 January 2017 02:25 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
saipradyumn wrote on Thu, 19 January 2017 07:48


<snip>

Here my question is what will happen if the refresh it self take the more time than time interval which is defined for that Refresh.

Thanks
SaiPradyumn


You'll see that refresh job is always running. As soon as it completes, it starts again.
Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659491 is a reply to message #659490] Thu, 19 January 2017 03:01 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks John for valuable response.

for those refresh queries parse calls totals is greater than executions totals in DBA_HIST_SQLSTAT.
I am just thinking is this reason for this ?
If we increase the time interval is there any benefit ?
Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659492 is a reply to message #659491] Thu, 19 January 2017 03:17 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
You are trying to solve non-existing problem. By your data you got about 200 parse operations it's not what's causing performance problems (if any exist)
Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659493 is a reply to message #659491] Thu, 19 January 2017 03:18 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
NO, of course not. You need to do some analysis of the problem. ie, what is the problem? What are your users complaining about?
Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659494 is a reply to message #659493] Thu, 19 January 2017 03:37 Go to previous message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Hi John ,

Thanks for your continuous support.
My issue is need to identify the why parse calls totals are greater than the execution totals for mv related queries
in attached attached screen shot

Pelase help me to understand that .

SaiPradyumn

Previous Topic: SGA AND PGA TUNING
Next Topic: query Dead Slow
Goto Forum:
  


Current Time: Thu Mar 28 03:32:47 CDT 2024