where can i find out information about what Degree of Parallelism was actually used for the sql execution?
say i have DOP of 16 for the session set using alter session parallel ddl parallel 16. when i run the ddl (or dml) how and where in execution plan can i see how it uses the parallelism. I see in Operation column an "Other XML" branch which has info like
"derived_cpu_dop" id="1" and a value of 0
...
"dop" note="y" with a value of 16
... etc
how do i read these? does the derived cpu dop of 0 means no parallelism was used at all but what os "dop" "note="y" = 16 means - did sqldev used it to execute the sql or are these just info of what i requested? I want to see how my sql was run and how much parallelism used?
no matter how much dop i use, my execution time is not improving at all. so i am suspecting dop is not set and actually that there is some resource cap on DOP that is set on my account which either limits dop or disabled it.
and also, even the explain plan in sqldeveloper shows that i should get lower and lower cost - it is not real - so i have tried the auto trace to get the exact execution plan . Just wondering how to read the output and interpret it because i am not sure if auto trace in sqlDev is also accurate or may be it does not work when there is some sort of resource limitation or cap on DOP.
Any help how I can use sqldev to find out or some link to documentation on this?
btw, i was also trying to use th Real Time SQL monitor, but not luck there too as, i get the first screen full (rather slow to render) then it seems to completely freeze and i cannot scroll down the page. Also I can filter on user column it seems (so i am seeing a whole list of sql on the page that is not mine - i want to just filter my own sql). It complete froze and i have to kill the whole sqldev instance
Thank you.