Controlling parallel queries

We have an Oracle 8.0.4 system running on a Sun box.

We do not have Parallel Server or the Partitioning Option (they cost a lot of money).

We do use partitioned views. Typically each week's data is separated out into a new table, and then joined together as a 'UNION ALL' view. This is fine.....except we have 108 weeks worth of data, and so any query that needs to access all the data starts up up to 108 parallel queries without us specifying parallel degree n. This assumes parllel degree 1. Even specifying NOPARALLEL as a hint only means that we do not get multiple parallel tasks per table. We still get one per table (if we can get them).

This means that sometimes we can run a query very quickly (when the parallel tasks can be started) or sometimes quite slowly (when some other query is already using all the parallel tasks). This means it is very hard to tell how long a query is likely to take with any degree of accuracy.

How do other sites deal with this sort of problem? Or are we unique?

email rpurdam_at_hotmail.com    Received on Fri Sep 18 1998 - 09:22:31 CDT