| Finding downgraded sql [message #669156] |
Thu, 05 April 2018 09:50  |
 |
LarsFranzen
Messages: 1
Registered: April 2018
|
Junior Member |
|
|
Hi,
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,
Lasr
|
|
|
|
|
|
| Re: Finding downgraded sql [message #669158 is a reply to message #669156] |
Thu, 05 April 2018 10:14  |
John Watson
Messages: 8963
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
