RE: 11.2.0.3 SQL Profile Confusion

> However I'm confused why it would even recommend a parallel query profile
> based on the above information?
>
> Also, when I tested it out, elapsed time jumped from 1.8 Seconds to 26.845
> seconds per execution and used 32 parallel processors!

IMO, in most situations, it's not sensible to run in parallel SQL statements that have a response time of few seconds.

I already saw cases where the STA advise such SQL profiles. Hence, you are not alone ;-) I would probably delete them.

> (I definitely understand this - way too many parallel processes and the overhead
> involved increased the total execution time).

This is probably because I/O calibration statistics are not in place. Otherwise the DOP would be much lower. If you want to keep the SQL profile, either you run I/O calibration or you setup the resource manager to cap the DOP.

> Anyone want to help me understand why it came up with a Parallel SQL
> Profile recommendation in the first place?

To do so you have to provide lot of information...

HTH
Chris Antognini

Troubleshooting Oracle Performance, Apress 2008 http://top.antognini.ch

--
http://www.freelists.org/webpage/oracle-l