Parallel query option

 One of our database is used for a data warehouse and the queries tend to be big, complex multi-table joins – 10 tables is not uncommon. Suspecting that parallel query option might help performance, I am just starting to look at that, so this may be the question of someone looking to buy a clue . ..

I ran an Oracle Expert (from the OEM Tuning Pack) analysis on the DB. In recommended reducing parallel_min_servers to zero. The justification was “This instance is currently configured to utilize parallel query, and is creating query servers at instance startup. THE DEGREE OF PARALLELISM IN THE DICTIONARY IS NOT CONFIGURED TO ALLOW PARALLEL QUERY PROCESSING. (emphasis mine) At the same time, there is no evidence that hints are being provided through the SQL statements.”

OK, so I queried dba_tables to see what the values of DEGREE, and INSTANCES was for the application tables. I was surprised to see that several (though by no means all) of the tables had DEGREE values of greater than one, while some had vaules of ‘default.”

So I guess I have a couple of questions. First, what do I look at to see if we are actually getting parallel processing, and to what degree? Second, if we’re NOT getting it, what more do I need to do to enable it?

--
Ed Stevens
(Opinions are not necessarily those of my employer)


Sent via Deja.com http://www.deja.com/
Before you buy.