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]

Just another observation: All query estimates expect much more rows in the result set (3.7e+06) than there are actually (simply 1). This might prevent intra-query parallelism if the following doc quote holds here:

A query is more likely to use parallelism if the query processes a lot more rows than are returned.

The fact that your query does use a particular group by TYPE_NAME defined via a CASE expression might make it more difficult for the optimizer to pre-calculate the result sets's cardinality - apparently, group by a case expression with two branches can only return two rows at maximum...

As TYPE_NAME is simply dependent on one column of one table, how would the query perform if you turn the definition of TYPE_NAME in a derived table (based on S) and join that derived table with SU?

SELECT
    CASE WHEN S.SGSN IN ( -371269922, -555819298 ) THEN 'Home' ELSE 'Roaming' END TYPE_NAME,
       SUM(SU.OCTETS_IN), 
       SUM(SU.OCTETS_OUT), 
       SUM(SU.OCTETS_IN + SU.OCTETS_OUT) 
FROM   
    SDR_USAGE_STAT_HRS_1_7 SU,
    SDR_STAT_HRS_1_7 S
WHERE  SU.START_TIME BETWEEN '2012-01-06 15:00:00.000' and '2012-01-07 03:00:00.000' 
AND  S.SUBSCRIBER_ID = SU.SUBSCRIBER_ID 
AND S.SESSION_KEY = SU.SESSION_KEY 
AND S.SUBSESSION_KEY = SU.SUBSESSION_KEY 
AND SU.DEVICE_ID = 4 
GROUP BY TYPE_NAME