Home » RDBMS Server » Performance Tuning » Finding downgraded sql (Oracle
Finding downgraded sql [message #669156] Thu, 05 April 2018 09:50 Go to next message
Messages: 1
Registered: April 2018
Junior Member
We have an isssue that during our load of our datawarehouse some parallel queries are downgraded from for example parallel 4 to serial or to parallel 2. In cloud control we can se that we have downgraded queries but not see which ones. We would also like to find out the alla queries that using parallel and the degree of parallel that the query gets, to investigate which one to change.

so for exampel if we have a request parallel 4 and gets parallel 4 we would like to find out the sql_id for that. and also if we have a request for parallel and it doesn't get the requested paralleldegree we would like to get this sql_id also.

If it possible we would like to get the information for the last 7 days and not only during the loading time.

Best regards,
Re: Finding downgraded sql [message #669157 is a reply to message #669156] Thu, 05 April 2018 09:55 Go to previous messageGo to next message
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

post SQL & results that show requested parallel degree.
post SQL & results that show actual parallel degree.
Re: Finding downgraded sql [message #669158 is a reply to message #669156] Thu, 05 April 2018 10:14 Go to previous message
John Watson
Messages: 8935
Registered: January 2010
Location: Global Village
Senior Member
You could use dbms_server_alert to set a threshold for the metric PX_DOWNGRADED_SEC, I think the message will tell you what you need to know.

You must be operating with parallel_degree_policy=manual, rather than auto. Others may (will!) disagree, but I have often found auto to be much better. In your case you might get a bit of queueing rather than downgrading, but overall you might find the system running much better.
Previous Topic: Is there a way to find out activities due to redo logs
Next Topic: Remove Merge cartesian Join
Goto Forum:

Current Time: Sun May 26 22:35:08 CDT 2024