Skip to Content
0

How to change change group_hashing server wide default in ASE?

Mar 03, 2017 at 05:40 PM

235

avatar image

While getting users off compatibility_mode we're having some problems with old group by's having been written without the proper order by clause. So group_hashing could cause results to be returned in a non-sorted order

So we want to turn off group_hashing in production while users fix this.

I can do it for the current session using "set group_hashing off"

But I can't seem to figure out how to do it server wide.

I tried using sp_optgoal to save to a new "optimization goal" (ie a new set of optimization defaults), but it didn't like me. Here are the recommended commands from the sp_optgoal web page:

-- Enables all optimizer changes through the current release 
-- (ie. reset to defaults)
SET PLAN OPTLEVEL ase_current;
-- Set the optimization goal (set of optimizer defaults) 
-- for the current session
SET PLAN OPTGOAL allrows_mix;
-- Change this optimizer setting for the current session
SET group_hashing 0;
-- Create the new optimization goal. Doesn't work. Gives erroneous error:
--  "The length of the user defined goal name must be smaller than 12"
sp_optgoal ‘new_optgoal‘, 'save';
-- Tell the server to use the new optgoal sp_configure "optimization goal", 1, ‘new_optgoal‘;

ASE version is 16.0 SP02 PL03

Thanks in advance

Ben

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
Bret Halford
Mar 04, 2017 at 01:06 AM
1

Traceflag 450 might also be an option, as discussed on page 4 of

https://assets.cdn.sap.com/sapcom/docs/2016/09/f69a1674-8a7c-0010-82c7-eda71af511fa.pdf


(I'm away from the office at the moment and can't test to verify if it is still working).

-bret

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Cool.

Not sure about side effects -- if any -- of reverting to pre-ASE 15 behavior to use TF450 ?

Avinash

0
Ben Slade Jun 02, 2017 at 07:31 PM
1

I figured out a better way to set the group_hashing default to 'off' without using the -T450 trace flag (this example is using sqsh with a semicolon terminator option):

> -- Verify group_hashing is on 
> sp_configure "optimization goal"; -mvert | grep "Run Value"
Run Value:       allrows_mix
> -- first int in sp_options show output is currentsetting, 1=on
> sp_options show; | grep 'group_hashing' 
 group_hashing  1              0                 39
> -- Now make the change
> SET PLAN OPTLEVEL ase_current;
> SET PLAN OPTGOAL allrows_mix;
> set group_hashing 0;
> sp_optgoal 'MyOptGoal', 'save';   -- Create new opt goal. Note, 12 character limit
> sp_configure "optimization goal", 1, "MyOptGoal";
-- Log out and log back in and verify the change
> sp_options show; | grep 'group_hashing'
 group_hashing  0              0                 39

These steps come from ASE 16.0 > Reference Manual: Procedures 16.0 > System Procedures -> sp_optgoal

Note that doing it this way, permits turning group_hashing on for an individual session or for inside a stored proc using the "set <query_option> 1" command.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

FYI, not directly related to your question but some info I'll share on group_hashing that I've gathered over the years with comparison to legacy versions.


Grouping on 12.5.4

Group by clauses on 12.5.4 were very straightforward, they always used a group inserting operator which was very straightforward.

  • A worktable is always created and a clustered index built on the group by columns.
  • As each row is passed, it is checked to see if the key already exists in the index. If it does, the aggregate values are amended. If it doesn't a new row is inserted.

Downsides

    • It is blocking operator (i.e. all rows from the base table must be processed before the query can start passing results (or pass to the next operator).
    • Creation of a worktable even if there is an index on the group by columns.
    • No query is able to do any kind of eager aggregation, see here for details on this.

    Upsides

      • It will always return rows ordered by the group by columns.
      • It is very easy to predict behaviour.
      • It is very simple for a coder to understand the behaviour and it requires no additional DBA knowledge.
      • The optimizer estimates have no bearing at all on any properties of the operator so plans can happily be forced without any requirement for additional statistics.
      • No transactions are taken out and so a long-running group by statement will never be the cause of a log suspend.

      Grouping on 15.x/16.x

      • Grouping on 15.x is exponentially more complicated.
      • It can provide huge improvements but can also go very awry.
      • It is important to understand the fundamentals so you can get the most out of the new methods.
      • It is unlikely that SAP will make any further significant changes which would improve the flaws outlined on this page.
      • TF 450 can be used (but is *not* advised) to force all group bys to use group_inserting but be aware this is not always guaranteed to work, particularly if using abstract plan operators. It is *legacy* support only.

      Hash Vector Aggregate(group_hashing)

      • Gory details are here

      Upsides

        • Can be very efficient in terms of the grouping operation itself (see below for caveats).
        • Will avoid a physical worktable if it fits in the assigned tempdb's cache.

        Downsides (many)

        It is also a blocking operator

        • Meaning all child rows must be processed before it can pass the first row to the next operator in the plan (which may be a further processing operator or an emit operator).

        In order to be efficient it relies on accurate estimates during optimization.

        • The expected number of rows flowing through the operator will determine the hash layout in terms of partitions and buckets.
        • If you are forcing a query to avoid creation of stats, be aware that inaccurate estimates will mean a forced query does not mean a performant query plan.
        • If the estimates are very low but the actuals much higher this can lead to an exceptionally poorly performing operator as it has to scan very long hash chains when doing key comparison.
        • This can easily lead to a situation where an identical query plan from a user perspective can perform very differently dependant on statistics. This situation could never happen on ASE 12.5.4.
        • The number of hash buckets is a fixed part of the query plan and is not visible in the showplan output itself, not in the set statistics plancost output (TF 9529 is your friend here)

        It uses private buffers from the assigned tempdb's data cache as well as regular buffers from that cache (not a problem itself)

        • The hash table is built using private buffers (dependant on configuration), these are buffers assigned a dummy page with a ptnid=-1 and taken from the assigned tempdb's cache.
        • The idea here is that the hashing operation tries to stay entirely in-memory.
        • The number of buffers it can assign is the lower of 'max buffers per lava operator' and ('max resource granularity * size of assigned tempdb's cache)
        • The hash layout as mentioned above is fixed, but the private buffers it can use is a dynamic runtime part of the plan. If you adjust the cache or increase 'max buffers per lava operator' an existing stored plan can use more or less buffers accordingly.
        • When the private buffers run out it spills the worktable to the temporary database, this is supposed to be a bad thing for performance. In reality the operator can perform better with spilt overflow pages that when it uses solely private buffers.
        • This may be due to the private buffer allocation during each hash key iteration or just that the handling of the operation using the overflow pages is more efficient.
          • set statistics plancost examples:
        • set statistics plancost examples:
            /
                      HashVectAgg
                        Max
                      (VA = 1)
                      r:3.102e+06 er:3.102e+06
                      l:106004 el:270278
                      p:96662 ep:270274
                      bufct: 50000
          
          Execution Time 421.
          Adaptive Server cpu time: 42191 ms.  Adaptive Server elapsed time: 43102 ms.
          
                       /
                      HashVectAgg
                        Max
                      (VA = 1)
                      r:3.102e+06 er:3.102e+06
                      l:123031 el:270278
                      p:111521 ep:270274
                      bufct: 500
          
          Adaptive Server cpu time: 13944 ms.  Adaptive Server elapsed time: 16022 ms.
          
        • These two examples are for the same query plan on the same data, the first with 50000 buffers available, the second with 500. As you can see the 500 run is more efficient despite the fact that it says it is performing physical i/o. The reason for this is that there is in fact no physical i/o at all. It is by design that should a 'spilt' worktable from a hashing operation be able to fit in the cache of the assigned tempdb then it will stay entirely in the cache.
        • For this reason it is actually better to run with a low number for 'max buffers per lava operator' if indeed you have big enough tempdb caches..*however*, see the section below for the downside to this.
        • If the estimates (as per the previous point) are significantly out (i.e. too low) then the impact of having too many buffers gets exponentially worse.

        Spilt hashing operations cause a $sort_local transaction is the assigned tempdb.

        • This is a big problem and has already can caus unexpected log suspends in production.
        • When the hashing operation requires overflow pages it opens a $sort_local transaction which records all the extent allocations for the worktable. Even though this stays entirely in-memory it is required so as to keep track of the actual pages that may physically be required in the temporary database should it not fit in the tempdb's cache. It's basically a minimally logged transaction.
        • This transaction itself appears to flush immediately to the tempdb's transaction log even if it would fit entirely in the tempdb PLC (controlled by 'session tempdb log cache')
        • There was fix in SP121, CR 750937 that was supposed to minimize when these transaction appear but it appears to make no difference to this operator.

        If the query is used in an cursor the $sort_local transaction remains open for the lifetime of the cursor.

        • This is the case even if the cursor is insensitive, see Syb15IssueSortLocalStaysOpenInCursor (internal link) for details. The $sort_local creation for insensitive cursors has been corrected in 15.7 SP138 via CR 791652 but only under TF 11821.

        It will not return results in any predictable order.

        • Previously on 12.5.4 a group by would always be sorted. Hash vector aggregate is never sorted.
        • As such, you must provide an order by clause should you require ordered data. If you do need to add an order by clause then it is better to then either force the query to use a group_sorted operator or if a temporary table, create an index on the order by columns which should then ensure it will always pick group_sorted.
        • If performing an insert into a table from a group by query it is very important to make sure the data types correspond between the select list columns and the columns being inserted into. Without this you can end up with multiple sorts within a given query plan. See below group_sorted for full details of this.

        It will use more CPU

        • A no brainer really, but even when the hashing is very efficient it is also very CPU intensive, something to be wary of with multiple concurrent operations.

        Summary

        Hash vector aggregate performance depends on:

        • Data types
        • Row counts in tables.
        • Optimizer statistics (single column and column groups).
        • Cfg parameter 'max buffers per lava operator'.
        • Cfg parameter 'max resource granularity'
        • Size of allocated tempdb's cache.
        • Size of allocated tempdb.
        • CPU resources.

        Group sorted

        • This is the best new grouping operator in 15.x in terms of overall management and likelihood to go wrong. See here for the gory details.

        Upsides

        • It is a non-blocking operator meaning as soon as one key value is complete it can passed to the next operator, if that operator is the emit, then this means the query will finish simultaneously with the grouping operator.
        • It requires no tempdb resources (directly, but the preceding sort may).
        • It requires no additional buffers above that required to the sort (which will be procedure cache buffers) - unless the worktable spills.
        • It will provide sorted results.
        • The construction of the operator does not depend on the optimization estimates.

        Downsides

        If there is no supporting index for the group by clause, it will be sorted prior to the operator.

          • This will use sort procedure cache buffers (ModuleID=11, MEMC_SOLM_1 & MEMC_LESORTOP_1) (not constrained by 'number of sort buffers').
          • However, it would have needed to sort on 12.5.4 as well.
          1
          Avinash Kothare Mar 03, 2017 at 06:30 PM
          0

          How about using login trigger ?

          Craft one to set the options you need. Once your work is done disable it.

          sp_logintrigger with no arguments to see what you are using as of now.

          If one exists you can add the "set options " you need

          If one does not exist create a stored procedure and then configure that procedure as login trigger.

          sp_logintrigger <your proc name>

          HTH

          Avinash

          Show 1 Share
          10 |10000 characters needed characters left characters exceeded

          Thanks for the suggestion, but that would affect all logins for the server and I don't know what kind of overhead it might have for poorly written processes that do lots of logins, or other bug side effects.

          FYI, I used this command to set a login trigger for a specific login:

          sp_modifylogin <login>,'login script',<proc name>
          0
          Ben Slade Mar 07, 2017 at 04:01 PM
          0

          Re: Traceflag 450 might also be an option

          Yes, that seem to work:

          # optimization goal of "allrows_mix" turns on group_hashing as a default
          > sp_configure "optimization goal"; -mvert | grep "Run Value"
          Run Value:       allrows_mix
          
          # Verify that group_hashing query optimizer setting is on, and traceflag 450 is not set
          # (Note, dbcc traceflags stacktraced for me in ASE 16 SP02 PL03/EBF 25704) > sp_options show; | egrep 'currentsetting|group_hashing|^ 450' name currentsetting defaultsetting scope group_hashing 1 1 39 name currentsetting defaultsetting scope
          (trace flags would be displayed here if set. None set) # Test that the query does do group hashing by default ("HASH VECTOR AGGREGATE") > set option show_abstract_plan on;
          > set showplan,noexec on;
          # group_hashing is used with query on col1 group by count where col1 is not an index column: > select col1, cnt=count(*) from mytable group by col1 having count(*)>10; QUERY PLAN FOR STATEMENT 1 (at line 1). Optimized using Serial Mode STEP 1 The type of query is SELECT. 3 operator(s) under root |ROOT:EMIT Operator (VA = 3) | | |RESTRICT Operator (VA = 2)(0)(0)(0)(4)(0) | | | | |HASH VECTOR AGGREGATE Operator (VA = 1) | | | GROUP BY | | | Evaluate Grouped COUNT AGGREGATE. | | | Using Worktable1 for internal storage. | | | Key Count: 1 | | | | | | |SCAN Operator (VA = 0) | | | | FROM TABLE | | | | mytable | | | | Table Scan. | | | | Forward Scan. | | | | Positioning at start of table. | | | | Using I/O Size 16 Kbytes for data pages. | | | | With MRU Buffer Replacement Strategy for data pages. The Abstract Plan (AP) of the final query execution plan:
          ( group_hashing ( i_scan mykeycol mytable ) ) ( prop mytable ( parallel 1 ) ( prefetch 16 ) ( mru ) ) # set 450 switch to turn off group hashing > set showplan,noexec off;
          > set option show_abstract_plan off; > set switch serverwide on 450 with override; # Verify the 450 traceflag is on (note, the group_hashing option still shows on, but is ignored) > sp_options show; | egrep 'currentsetting|group_hashing|^ 450' name currentsetting defaultsetting scope group_hashing 1 1 39 name currentsetting defaultsetting scope 450 1 0 8 # Verify group hashing is not used with 450 trace flag is set (should see "GROUP INSERTING") > set option show_abstract_plan on; > set showplan,noexec on; > select col1, cnt=count(*) from mytable group by col1 having count(*)>10; The Abstract Plan (AP) of the final query execution plan: ( group_inserting ( i_scan mykeycol mytable ) ) ( prop mytable ( parallel 1 ) ( prefetch 16 ) ( mru ) ) QUERY PLAN FOR STATEMENT 1 (at line 1). Optimized using Serial Mode STEP 1 The type of query is SELECT. 3 operator(s) under root |ROOT:EMIT Operator (VA = 3) | | |RESTRICT Operator (VA = 2)(0)(0)(0)(4)(0) | | | | |GROUP INSERTING Operator (VA = 1) | | | GROUP BY | | | Evaluate Grouped COUNT AGGREGATE. | | | Using Worktable1 for internal storage. | | | | | | |SCAN Operator (VA = 0) | | | | FROM TABLE | | | | mytable | | | | Table Scan. | | | | Forward Scan. | | | | Positioning at start of table. | | | | Using I/O Size 16 Kbytes for data pages. | | | | With MRU Buffer Replacement Strategy for data pages. # Set things back to normal > set showplan,noexec off; > set option show_abstract_plan off; > set switch serverwide off 450;

          Is the only way to set the 450 traceflag as the serverwide default is to use the -T flag on startup? (in the RUN_* file)

          Thanks
          Ben

          Share
          10 |10000 characters needed characters left characters exceeded
          Ben Slade Mar 08, 2017 at 03:22 PM
          0
          Just FYI, running a test query using group_hashing versus non-group_hashing shows group_hashing to be about 3 times faster (in my test hitting cached data). The test table had ~7 million rows, the group by field was varchar(255) with an average length of 10 chars.
          Share
          10 |10000 characters needed characters left characters exceeded
          Ben Slade Mar 08, 2017 at 03:23 PM
          0

          Also, once you set the 450 flag, the "set group_hashing" option is a silent noop. Ie, you can't turn on group_hashing for a session even if you want to.

          I wish there was a way to change the default for group_hashing without using the non-overridable -T450 startup flag.

          Later edit:
          See the "I figured out a better way to set the group_hashing default to 'off'" comment above

          Share
          10 |10000 characters needed characters left characters exceeded