TIP #43: Effective DOP.

Many of us use parallel query to get results ASAP.
However the big question is if query runs with determined DOP (Degree Of Parallelism) or Oracle downgrades it to lower DOP because of low in resources.
Doug Burns has a fully analysis in his blog. (http://oracledoug.com/serendipity/index.php?/archives/1231-How-can-I-tell-the-actual-DOP-used-for-my-Parallel-Query.html).

From reading Doug `s blog and different articles , I think that the best way to determine whether or not a session is using determined DOP is to run the following query.

select n.name, s.value
from v$sesstat s, v$statname n
s.statistic# = n.statistic#
n.name like 'Parallel%'
and sid=&sid_val;

If Parallel operations not downgraded <>0 , it means that session is running with determined DOP. However if Parallel operations downgraded to serial <>0 , it means that query is not running in parallel.
Parallel operations downgraded 75 to 99 pct , Parallel operations downgraded 50 to 75 pct, Parallel operations downgraded 25 to 50 pct and Parallel operations downgraded 1 to 25 pct determines if DOP is downgraded to lower value and in what percentage.

No comments: