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]

Tried to upload the query plans - but received a message that upload is available only for users with at least 100 reputation points. I see that I have only 51 :(.

[VolkerBarth]

141 points - now you might try again:)