OraFAQ Forum: Performance Tuning » Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals)

Home » RDBMS Server » Performance Tuning » Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) (Oracle 11)

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

Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) [message #659461]

Wed, 18 January 2017 07:45

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

Report message to a moderator

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

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.

Report message to a moderator

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

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

Attachment: Result_of_query.PNG
(Size: 33.98KB, Downloaded 1735 times)

Report message to a moderator

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

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!

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

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

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

Report message to a moderator

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

John Watson
Messages: 8966
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.

Report message to a moderator

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

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 ?

Report message to a moderator

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

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)

Report message to a moderator

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

John Watson
Messages: 8966
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?

Report message to a moderator

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

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 06 07:33:47 CST 2025