Intra-query parallelism

[Former Member]

Hi

I'm trying to optimize some reports on SQL Anywhere 12.0.1.3324. My test system is relatively strong for SQL Anywhere - 24 CPU cores with 24GB RAM. One of the main problem is that SQL Anywhere doesn't utilize the hardware resources or does it in an inconsistent way. I have found that the same query may use parallelism (Parallel Table Scan, for example) on one execution and doesn't do it on the next one. Note that I'm the only user in the system, there are no concurrent queries and the data is not changed at all. So my questions are:

1. Is there any way to force or hint the optimizer to choose parallel execution plan to utilize the available hardware and decrease response time?
2. Is it possible to make HASH GROUP BY parallel?
3. How can I know why the optimizer chooses or not chooses parallel execution? What factors affect the optimizer's choice?

I have more questions about SQLA intra-query parallelism, but I don't want to ask too many questions in a single post. I can upload query plans if required.

Thanks in advance Leonid Gvirtz http://www.gvirtz-consulting.com

[Former Member]

HashGroupBy can be done in parallel. Although, the optimizer will likely weigh benefits to determine if the overhead of coordinating a parallel effort is greater than the lesser execution time of a serial effort.

A brief introduction to parallelism is available at: http://dcx.sybase.com/index.html#1201/en/dbusage/parallelism.html

O/W, you might be able to post a graphical plan and see if someone can explain some of the optimizer's decisions.

[VolkerBarth]

Some further hints:

• What's the current multiprogramming level (MPL)?
• The influence of the max_query_tasks option, cf. this FAQ.

[Former Member]

Thanks to Volker, now I'm able to upload the query plans.link text

join_6.saplan - refers to the first pair of tables. The execution is serial both before and after the restart of SQLA.

join_7_after_restart.saplan - the (partly) parallel execution plan for the second pair of tables before the restart of SQLA.

join_7_after_restart.saplan - the same query after the restart - the execution became fully serial and much slower.

[VolkerBarth]

FWIW, the "thanks" should go to the forum community - your newly gained reputation points come from several users, of which I might be one ... I just wanted to inform you about that now overcome limitation:)

[Former Member]

I'm new to this kind of forums, still learning 🙂

[Former Member]

I tried a number of things proposed by Volker and Glenn and had some progress, but unfortunately the situation didn't improve too much.

First, I tried the calibration by ALTER DATABASE CALIBRATE PARALLEL READ. It didn't make any measurable difference to the query plan, so I decided to revert it back by ALTER DATABASE RESTORE DEFAULT CALIBRATION.

Then, I tried to figure out how to make the optimizer estimate less disk reads for the plan. After some experiments, I came to solution that can be seen in the attached join_6_parallel.saplan, I just moved the CASE construction to an outer query and received much more parallel execution plan, even HASH GROUP BY is parallel now. But, this is not the end of the story.

Then, I tried to increase the range condition in the query and take the whole day instead of just 3 hours. The query plan looked pretty much the same (parallel), but when I run the query I discovered that query runs in parallel only first 10 seconds or so and then becomes pretty much serial. Reviewing of the detailed plan (see join_6_semi_parallel_whole_day.saplan) reveals that only the most-left branch under the Exchange node actually did the job and others didn't bring any rows. I wonder what can be the reason of that. Does it mean that the optimizer changed its mind during the actual processing of the query? Is it normal/expected?

In the meanwhile, I'm considering completely different approach - DIY (Do It Yourself) parallelism. If the query can be easily split into a number of parts that return relatively small result sets each, then it is possible to run the resulted sub-queries in parallel in separate connections and then combine the results. If the original query is wrapped in a stored procedure, as in my case, it is even possible to implement this approach transparently to the calling application by creating a set of events inside the procedure, triggering them (events will work asynchronously) and then combine the results. I tried DIY parallelism for a query like mentioned in join_6_semi_parallel_whole_day.saplan, but spanning 3 days - and the results are quite impressive as compared to intra-query parallelism.

[VolkerBarth]

I have not checked the new plans - however, is the relevant data "cached" during your new tests?

Glenn seems to have confirmed my suspicion that the massive need to disk reads prevents parallel operation - so have you tried to "pre-cache" the relevant data? Or is this unrealistic, as the query would usually access data that is not cached?

[Former Member]

The behavior is consistent, I run the queries a number of times. The rate of caching of the data is good, the actual number of disk reads is much fewer than the estimated one and not so high in absolute values. In a real life we cannot expect that the data will be perfectly cached. This is true even for most of OLTP systems, but here I'm dealing with a kind of data warehouse application.

[VolkerBarth]

Well, then I don't have any further suggestions - but the real experts may have...