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

[Message index]

		Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) By: saipradyumn on Wed, 18 January 2017 07:45
		Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) By: BlackSwan on Wed, 18 January 2017 08:03
		Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) By: saipradyumn on Wed, 18 January 2017 08:27
		Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) By: BlackSwan on Wed, 18 January 2017 08:40
		Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) By: saipradyumn on Thu, 19 January 2017 01:48
		Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) By: Michel Cadot on Thu, 19 January 2017 02:03
		Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) By: saipradyumn on Thu, 19 January 2017 02:12
		Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) By: John Watson on Thu, 19 January 2017 02:25
		Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) By: saipradyumn on Thu, 19 January 2017 03:01
		Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) By: michael_bialik on Thu, 19 January 2017 03:17
		Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) By: John Watson on Thu, 19 January 2017 03:18
		Re: Avoiding the -ve execute to parse for MV refresh(highest parse calls total than execution totals) By: saipradyumn on Thu, 19 January 2017 03:37

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 20:25:39 CDT 2024