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 🙂

[VolkerBarth]

I'm by no means a plan viewer expert, so treat this as just a few wild guesses (until the real experts like Glenn will tell the truth):

• Is there a particular reason to use a static cache size (-ca 0)?
• The "after restart plan" seems to run against an empty cache, whereas the "before restart" does not. Therefore it seems reasonable that the after restart query has to do much more disk reads - particularly in case your index (whose definition I do not know) may be able to fulfill much of the WHERE clauses predicates. It's an index-only retrieval, I would think.
• What does happen when you "preload" the index pages into the cache (by calculating the index average value or the like)?
• As the WHERE clause seems to contain JOIN conditions - does the behaviour differ when you code the query with INNER JOIN conditions?
• Some testing with query hints (FORCE INDEX ...) may also be worthwhile.

Sorry, if this does sound more like a lot of questions than like an answer...

EDIT:

For the 2nd point, there's a strong hint in the 12.0.1 docs - look at the boldly emphasized part:

While there can be advantages to parallel query execution in such an environment, the optimizer I/O cost model for a single device makes it difficult for the optimizer to choose a parallel table or index scan unless the table data is fully resident in the cache. However, if you calibrate disk subsystem using the ALTER DATABASE CALIBRATE PARALLEL READ statement, the optimizer can cost the benefits of parallel execution with greater accuracy. The optimizer is likely to choose execution plans with parallelism when the disk subsystem has multiple platters.