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 🙂

[MCMartin]

Regarding 3.: The optimizer considers the current workload of the database server and will not use parallel execution if the workload is high. So the usage of a parallel strategy for a query is non deterministic.

[Former Member]

I agree with you that intra-query parallelism is non-deterministic, as it should be. The question is how much it is non-deterministic and at which point non-deterministic becomes unstable. On my test systems, I'm the only user and the only connection to the database, the test servers (a strong system mentioned above and my laptop) are dedicated for SQL Anywhere, so load from other processes is quite stable (zero). Also, I perform all my tests for a number of times, so the effect of physical reads etc. should not play here.

So far, I have found that I can never know for sure whether parallel strategy will be used by the optimizer or not, even though all relevant factors, at least those known to me, remain constant. One of the last problematic scenarios:

I have two pairs of almost identical tables. The structure (columns, indexes, etc.) is exactly the same and the data is only slightly different. Actually, the data represents measurements of some kind from different days. The same query worked in parallel for one pair of tables and serially for the second one. This behavior was quite consistent. I didn't succeed to understand from the query plans why there is such a difference in processing of so similar tables. The difference in response time is quite significant.

Then, SQLA server was restarted, with no relation to my tests. Suddenly, I discovered that the query that used to run in parallel now runs serially - and quite consistently! So, I'm looking for a way to understand why. I'm going to try to attach query plans shortly.

Of course, I understand that SQLA is probably not the best product for a data warehouse and Sybase IQ will most probably do this job much better. But, that's what my customer has and they want try produce the best from SQLA before considering other technologies. They have very positive experience with SQLA until now, by the way.

Thanks for your help

[Former Member]

The optimizer's cost model takes into account a variety of factors that include:

• the number of concurrently executing requests
• the number of available CPU cores at optimization time
• the number of worker threads available
• the estimated I/O and CPU cost

to determine the best cost-based strategy for any parallelizable query.

For the three query plans you included, all of the plans differ in the amount of table and/or index data that is in cache. With the plan "join_7_before_restart" all index and table pages are all nearly 100% resident in the buffer pool at optimization time; the availability of idle CPU cores, and available worker threads (from the current pool of 50) leads to the choosing of a parallel plan.

The two other plans have significant portions of the data (either table or index) not resident in the buffer pool. This causes the optimizer to try to determine the most cost-efficient strategy involving the reads of so much data; in the case of join_6, it's an index scan of the SU table, whereas in join_7_after_restart it's a sequential scan.

You may see quite different behaviour if you calibrate your disk subsystem for parallel reads, which should improve the optimizer's estimate of parallel read performance for those queries that involve large tables that are not fully resident in the buffer pool.