Sybase/ASE 15 and default parallelism on non-partitioned tables

I'm having trouble enabling default parallelism in Sybase/ASE 15. For a non-partitioned table, it doesn't seem to want to use parallelism unless I ask for it with a hint in the query. I believe that I have all the config parameters set up properly so that it will happen, and but it refuses to use parallelism voluntarily.

Scott.

1> select @@version
2> go

 ------------------------------------------------------------------------------------------------------------------------
 Adaptive Server Enterprise/15.0.3/EBF 16554 ESD#1/P/RS6000/AIX 5.3/ase1503/2681/64-bit/FBO/Mon Mar  9 01:35:28 2009

(1 row affected)
1>
1> sp_spaceused SCOTT_CLMD2
2> go
 name        rowtotal reserved   data       index_size unused
 ----------- -------- ---------- ---------- ---------- ------
 SCOTT_CLMD2 63478565 2442676 KB 2442634 KB 0 KB       42 KB

(1 row affected)
(return status = 0)
1> sp_helpindex SCOTT_CLMD2
2> go
Object does not have any indexes.
(return status = 1)
1> set showplan on
2> go
1> set noexec on
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SET OPTION ON.

1> select count(*) FROM SCOTT_CLMD2
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SELECT.

        2 operator(s) under root

       |ROOT:EMIT Operator (VA = 2)
       |
       |   |SCALAR AGGREGATE Operator (VA = 1)
       |   |  Evaluate Ungrouped COUNT AGGREGATE.
       |   |
       |   |   |SCAN Operator (VA = 0)
       |   |   |  FROM TABLE
       |   |   |  SCOTT_CLMD2
       |   |   |  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.



1> exec sp_configure
2> go

Group: Configuration Options


Group: Backup/Recovery

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 allow remote access                      1           0            1            1 switch               dynamic
 max concurrently recovered db            0           0            0            0 number               dynamic
 number of checkpoint tasks               1           0            2            2 number               dynamic
 print recovery information               0           0            0            0 switch               dynamic
 recovery interval in minutes             5           0            5            5 minutes              dynamic
 tape retention in days                   0           0            0            0 days                 dynamic

(1 row affected)

Group: Cache Manager

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 extended cache size                      0           0            0            0 memory pages(2k)     dynamic
 global async prefetch limit             10           0           40           40 percent              dynamic
 global cache partition number            1           0            1            1 number               static
 memory alignment boundary             2048           0         2048         2048 bytes                static
 number of index trips                    0           0            0            0 number               dynamic
 number of oam trips                      0           0            0            0 number               dynamic
 total data cache size                    0     4561540            0      4561540 kilobytes            read-only

Group: Component Integration Services

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 cis bulk insert array size              50           0           50           50 rows                 dynamic
 cis bulk insert batch size               0           0         4096         4096 rows                 dynamic
 cis connect timeout                      0           0            0            0 seconds              dynamic
 cis cursor rows                         50           0           50           50 number               dynamic
 cis idle connection timeout              0           0            0            0 seconds              dynamic
 cis packet size                        512           0          512          512 bytes                dynamic
 cis rpc handling                         0           0            0            0 switch               dynamic
 enable cis                               1           0            1            1 switch               static
 enable file access                       0           0            0            0 switch               dynamic
 enable full-text search                  0           0            0            0 switch               dynamic
 enable snmp                              0           0            0            0 switch               dynamic
 max cis remote connections               0         264            0            0 number               dynamic

Group: DTM Administration

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 dtm detach timeout period                5           0            5            5 minutes              dynamic
 dtm lock timeout period                300           0          300          300 seconds              dynamic
 enable DTM                               0           0            0            0 switch               static
 enable xact coordination                 1           0            1            1 switch               static
 number of dtx participants             500         170          500          500 number               dynamic
 strict dtm enforcement                   0           0            0            0 switch               static
 txn to pss ratio                        16        2556           16           16 number               dynamic
 xact coordination interval              60           0           60           60 seconds              dynamic

Group: Diagnostics

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 average cap size                       200           0          200          200 bytes                static
 caps per ccb                            50           0           50           50 number               static
 dump on conditions                       0           0            0            0 switch               dynamic
 engine memory log size                   0           2            0            0 memory pages(2k)     dynamic
 maximum dump conditions                 10           0           10           10 number               static
 number of ccbs                           0           0            0            0 number               static
 number of dump threads                   1           0            1            1 number               dynamic

Group: Disk I/O

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 allow sql server async i/o               1           0            1            1 switch               static
 disable disk mirroring                   1           0            1            1 switch               static
 disk i/o structures                    256        1922         8192         8192 number               dynamic
 number of devices                       10        #256          150          150 number               dynamic
 number of large i/o buffers              6        3114            6            6 number               dynamic
 page utilization percent                95           0           95           95 percent              dynamic

Group: Error Log

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 event log computer name        LocalSystem           0  LocalSystem  LocalSystem name                 dynamic
 event logging                            1           0            1            1 switch               dynamic
 log audit logon failure                  0           0            0            0 switch               dynamic
 log audit logon success                  0           0            0            0 switch               dynamic

Group: Extended Stored Procedure

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 esp execution priority                   8           0            8            8 id                   dynamic
 esp execution stacksize              65536           0        65536        65536 bytes                static
 esp unload dll                           0           0            0            0 switch               dynamic
 start mail session                       0           0            0            0 switch               dynamic
 start xp server during boot              0           0            0            0 switch               static
 xp_cmdshell context                      1           0            0            0 switch               dynamic

Group: General Information

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 configuration file                       0           0            0 /usr/sybase/ name                 dynamic

Group: Java Services

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 enable java                              0           0            0            0 switch               static
 number of java sockets                   0           0            0            0 number               dynamic
 size of global fixed heap              300           0          300          300 memory pages(2k)     dynamic
 size of process object heap           3000           0         3000         3000 memory pages(2k)     dynamic
 size of shared class heap             3072           0         3072         3072 memory pages(2k)     dynamic

Group: Languages

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 default character set id                 1           0            1            1 id                   static
 default language id                      0           0            0            0 id                   dynamic
 default sortorder id                    50           0           50           50 id                   static
 disable character set conversi           0           0            0            0 switch               static
 enable unicode conversions               1           0            1            1 switch               dynamic

Group: Lock Manager

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 deadlock checking period               500           0          750          750 milliseconds         dynamic
 deadlock retries                         5           0            5            5 number               dynamic
 lock address spinlock ratio            100           0          100          100 ratio                static
 lock hashtable size                   2048          76         2048         2048 bytes                static
 lock scheme                       allpages           0    datapages    datapages name                 dynamic
 lock spinlock ratio                     85           0           85           85 ratio                static
 lock table spinlock ratio               20           0           20           20 ratio                static
 lock wait period                2147483647           0   2147483647   2147483647 seconds              dynamic
 number of locks                      10000       47072       200000       200000 number               dynamic
 page lock promotion HWM                200           0          200          200 number               dynamic
 page lock promotion LWM                200           0          200          200 number               dynamic
 page lock promotion PCT                100           0          100          100 percent              dynamic
 print deadlock information               0           0            0            0 number               dynamic
 read committed with lock                 0           0            0            0 switch               dynamic
 row lock promotion HWM                 200           0          200          200 number               dynamic
 row lock promotion LWM                 200           0          200          200 number               dynamic
 row lock promotion PCT                 100           0          100          100 percent              dynamic

Group: Memory Use

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 additional network memory                0        1800      1843200      1843200 bytes                dynamic
 allocate max shared memory               0           0            0            0 switch               dynamic
 allow resource limits                    0          #4            0            0 switch               static
 audit queue size                       100           2          100          100 number               dynamic
 average cap size                       200           0          200          200 bytes                static
 caps per ccb                            50           0           50           50 number               static
 compression memory size                  0         152            0            0 memory pages(2k)     dynamic
 deadlock pipe max messages               0         185          100          100 number               dynamic
 default network packet size           2048       #1032         2048         2048 bytes                static
 disk i/o structures                    256        1922         8192         8192 number               dynamic
 dynamic allocation on demand             1           0            1            1 switch               dynamic
 enable rep agent threads                 0           0            1            1 switch               dynamic
 errorlog pipe max messages               0         431          200          200 number               dynamic
 event buffers per engine               100        #813         2000         2000 number               static
 executable codesize + overhead           0       60671            0        60671 kilobytes            read-only
 heap memory per user                  4096           0         4096         4096 bytes                dynamic
 lock hashtable size                   2048          76         2048         2048 bytes                static
 lock spinlock ratio                     85           0           85           85 ratio                static
 max SQL text monitored                   0          82         1024         1024 bytes                static
 max memory                           63488     5888000      2944000      2944000 memory pages(2k)     dynamic
 max nesting level                       16           0           16           16 number               static
 max number network listeners             5        1436            5            5 number               dynamic
 max online Q engines                     0           0            0            0 not applicable       static
 max online engines                       1       16918            4            4 number               static
 memory per worker process             1024          40         1024         1024 bytes                dynamic
 messaging memory                       400           0          400          400 memory pages(2k)     dynamic
 number of alarms                        40           6           40           40 number               dynamic
 number of aux scan descriptors         256        #392          256          256 number               dynamic
 number of ccbs                           0           0            0            0 number               static
 number of devices                       10        #256          150          150 number               dynamic
 number of dtx participants             500         170          500          500 number               dynamic
 number of java sockets                   0           0            0            0 number               dynamic
 number of large i/o buffers              6        3114            6            6 number               dynamic
 number of locks                      10000       47072       200000       200000 number               dynamic
 number of mailboxes                     30           2           30           30 number               dynamic
 number of messages                      64           3           64           64 number               dynamic
 number of open databases                12        9232           20           20 number               dynamic
 number of open indexes                 500       44268        14000        14000 number               dynamic
 number of open objects                 500       17680         9000         9000 number               dynamic
 number of open partitions              500       14553        14000        14000 number               dynamic
 number of remote connections            20         125           20           20 number               static
 number of remote logins                 20          83           20           20 number               static
 number of remote sites                  10        2877           10           10 number               static
 number of user connections              25       21553           75           75 number               dynamic
 number of worker processes               0        7174           25           25 number               dynamic
 open index hash spinlock ratio         100           0          100          100 ratio                dynamic
 open index spinlock ratio              100           0          100          100 ratio                dynamic
 partition groups                      1024         556         1024         1024 number               dynamic
 partition spinlock ratio                10          78           10           10 ratio                dynamic
 permission cache entries                15        #458           15           15 number               dynamic
 plan text pipe max messages              0          80          100          100 number               dynamic
 procedure cache size                  7000      926406       425000       425000 memory pages(2k)     dynamic
 process wait events                      0           0            1            1 switch               dynamic
 remote server pre-read packets           3        #123            3            3 number               static
 size of global fixed heap              300           0          300          300 memory pages(2k)     dynamic
 size of process object heap           3000           0         3000         3000 memory pages(2k)     dynamic
 size of shared class heap             3072           0         3072         3072 memory pages(2k)     dynamic
 size of unilib cache                     0         312            0            0 bytes                dynamic
 sql text pipe max messages               0         115          100          100 number               dynamic
 stack guard size                      4096        #668         4096         4096 bytes                static
 stack size                          143360      #23380       143360       143360 bytes                static
 statement cache size                     0           0            0            0 memory pages(2k)     dynamic
 statement pipe max messages              0          48          100          100 number               dynamic
 total data cache size                    0     4561540            0      4561540 kilobytes            read-only
 total logical memory                 63488     5712696      2856024      2856348 memory pages(2k)     read-only
 total physical memory                    0     5712048            0      2856024 memory pages(2k)     read-only
 txn to pss ratio                        16        2556           16           16 number               dynamic
 user log cache size                   2048           0         2048         2048 bytes                static
 user log cache spinlock ratio           20           0           20           20 ratio                dynamic
 wait event timing                        0           0            1            1 switch               dynamic

Group: Meta-Data Caches

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 number of open databases                12        9232           20           20 number               dynamic
 number of open indexes                 500       44268        14000        14000 number               dynamic
 number of open objects                 500       17680         9000         9000 number               dynamic
 number of open partitions              500       14553        14000        14000 number               dynamic
 open index hash spinlock ratio         100           0          100          100 ratio                dynamic
 open index spinlock ratio              100           0          100          100 ratio                dynamic
 open object spinlock ratio             100           0          100          100 ratio                dynamic
 partition groups                      1024         556         1024         1024 number               dynamic
 partition spinlock ratio                10          78           10           10 ratio                dynamic

Group: Monitoring

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 SQL batch capture                        0           0            1            1 switch               dynamic
 deadlock pipe active                     0           0            1            1 switch               dynamic
 deadlock pipe max messages               0         185          100          100 number               dynamic
 enable monitoring                        0           0            1            1 switch               dynamic
 enable stmt cache monitoring             0           0            0            0 switch               dynamic
 errorlog pipe active                     0           0            1            1 switch               dynamic
 errorlog pipe max messages               0         431          200          200 number               dynamic
 max SQL text monitored                   0          82         1024         1024 bytes                static
 object lockwait timing                   0           0            1            1 switch               dynamic
 per object statistics active             0           0            1            1 switch               dynamic
 performance monitoring option            0           0            0            0 switch               dynamic
 plan text pipe active                    0           0            0            0 switch               dynamic
 plan text pipe max messages              0          80          100          100 number               dynamic
 process wait events                      0           0            1            1 switch               dynamic
 sql text pipe active                     0           0            1            1 switch               dynamic
 sql text pipe max messages               0         115          100          100 number               dynamic
 statement pipe active                    0           0            1            1 switch               dynamic
 statement pipe max messages              0          48          100          100 number               dynamic
 statement statistics active              0           0            1            1 switch               dynamic
 wait event timing                        0           0            1            1 switch               dynamic

Group: Network Communication

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 additional network memory                0        1800      1843200      1843200 bytes                dynamic
 allow remote access                      1           0            1            1 switch               dynamic
 allow sendmsg                            0           0            0            0 switch               dynamic
 default network packet size           2048       #1032         2048         2048 bytes                static
 max network packet size               2048           0         8192         8192 bytes                static
 max number network listeners             5        1436            5            5 number               dynamic
 number of remote connections            20         125           20           20 number               static
 number of remote logins                 20          83           20           20 number               static
 number of remote sites                  10        2877           10           10 number               static
 remote server pre-read packets           3        #123            3            3 number               static
 send doneinproc tokens                   1           0            1            1 switch               dynamic
 syb_sendmsg port number                  0           0            0            0 id                   static
 tcp no delay                             1           0            1            1 switch               static

Group: O/S Resources

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 max async i/os per engine             4096           0         4096         4096 number               static
 max async i/os per server       2147483647           0   2147483647   2147483647 number               static
 max native threads per engine           50           0           50           50 number               dynamic
 o/s file descriptors                     0           0            0        65534 number               read-only
 tcp no delay                             1           0            1            1 switch               static

Group: Physical Resources


Group: Physical Memory

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 additional network memory                0        1800      1843200      1843200 bytes                dynamic
 allocate max shared memory               0           0            0            0 switch               dynamic
 compression memory size                  0         152            0            0 memory pages(2k)     dynamic
 dynamic allocation on demand             1           0            1            1 switch               dynamic
 heap memory per user                  4096           0         4096         4096 bytes                dynamic
 lock shared memory                       0           0            0            0 switch               static
 max memory                           63488     5888000      2944000      2944000 memory pages(2k)     dynamic
 messaging memory                       400           0          400          400 memory pages(2k)     dynamic
 shared memory starting address           0           0            0            0 not applicable       static
 total logical memory                 63488     5712696      2856024      2856348 memory pages(2k)     read-only
 total physical memory                    0     5712048            0      2856024 memory pages(2k)     read-only

Group: Processors

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 max online Q engines                     0           0            0            0 not applicable       static
 max online engines                       1       16918            4            4 number               static
 number of Q engines at startup           0           0            0            0 number               static
 number of engines at startup             1           0            4            4 number               static

Group: Query Tuning

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 abstract plan cache                      0           0            0            0 switch               dynamic
 abstract plan dump                       0           0            0            0 switch               dynamic
 abstract plan load                       0           0            0            0 switch               dynamic
 abstract plan replace                    0           0            0            0 switch               dynamic
 allow backward scans                     1           0            1            1 switch               dynamic
 auto query tuning                        0           0            0            0 switch               dynamic
 capture missing statistics               0           0            1            1 switch               dynamic
 cost of a cpu unit                    1000           0         1000         1000 number               dynamic
 cost of a logical io                     2           0            2            2 number               dynamic
 cost of a physical io                   25           0           25           25 number               dynamic
 enable compatibility mode                0           0            0            0 switch               dynamic
 enable literal autoparam                 0           0            0            0 switch               dynamic
 enable merge join                        2           0            2            2 switch               dynamic
 enable query tuning mem limit            1           0            1            1 switch               dynamic
 enable query tuning time limit           1           0            1            1 switch               dynamic
 enable sort-merge join and JTC           0           0            0            0 switch               dynamic
 max buffers per lava operator         2048           0         2048         2048 number               dynamic
 max parallel degree                      1           0            3            3 number               dynamic
 max query parallel degree                1           0            1            1 number               dynamic
 max repartition degree                   1           0            1            1 number               dynamic
 max resource granularity                10           0           10           10 number               dynamic
 max scan parallel degree                 1           0            2            2 number               dynamic
 memory per worker process             1024          40         1024         1024 bytes                dynamic
 metrics elap max                         0           0            0            0 milliseconds         dynamic
 metrics exec max                         0           0            0            0 milliseconds         dynamic
 metrics lio max                          0           0            0            0 logical pages        dynamic
 metrics pio max                          0           0            0            0 logical pages        dynamic
 min pages for parallel scan            200           0          200          200 logical pages        dynamic
 mnc_full_index_filter                    2           0            2            2 switch               dynamic
 number of histogram steps               20           0          200          200 number               dynamic
 number of worker processes               0        7174           25           25 number               dynamic
 optimization goal              allrows_mix           0  allrows_mix  allrows_mix name                 dynamic
 optimization timeout limit              10           0           10           10 percent              dynamic
 procedure deferred compilation           1           0            1            1 switch               dynamic
 prod-consumer overlap factor            20           0           20           20 percent              dynamic
 query tuning plan executions             3           0            3            3 number               dynamic
 sampling percent                         0           0            0            0 percent              dynamic
 sproc optimize timeout limit            40           0           40           40 percent              dynamic

Group: Rep Agent Thread Administration

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 enable rep agent threads                 0           0            1            1 switch               dynamic

Group: SQL Server Administration

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 SQL Perfmon Integration                  1           0            1            1 switch               static
 allow nested triggers                    1           0            1            1 switch               static
 allow resource limits                    0          #4            0            0 switch               static
 allow updates to system tables           0           0            0            0 switch               dynamic
 cpu accounting flush interval          200           0          200          200 clock ticks          dynamic
 cpu grace time                         500           0         1000         1000 clock ticks          dynamic
 deadlock retries                         5           0            5            5 number               dynamic
 default database size                    3           0            3            3 megabytes            dynamic
 default exp_row_size percent             5           0            5            5 percent              dynamic
 default fill factor percent              0           0            0            0 percent              dynamic
 enable DTM                               0           0            0            0 switch               static
 enable HA                                0           0            0            0 switch               static
 enable housekeeper GC                    1           0            1            1 switch               dynamic
 enable job scheduler                     0           0            0            0 switch               dynamic
 enable metrics capture                   0           0            0            0 switch               dynamic
 enable pci                               0           0            0            0 switch               static
 enable real time messaging               0           0            0            0 switch               dynamic
 enable semantic partitioning             0           0            0            0 switch               dynamic
 enable sql debugger                      1           0            1            1 switch               static
 enable webservices                       0           0            0            0 switch               dynamic
 enable xml                               0           0            0            0 switch               dynamic
 event buffers per engine               100        #813         2000         2000 number               static
 histogram tuning factor                 20           0           10           10 number               dynamic
 housekeeper free write percent           1           0           20           20 percent              dynamic
 i/o accounting flush interval         1000           0         1000         1000 clock ticks          dynamic
 i/o batch size                         100           0          100          100 number               dynamic
 i/o polling process count               10           0           10           10 number               dynamic
 identity burning set factor           5000           0         5000         5000 number               static
 identity grab size                       1           0            1            1 number               dynamic
 identity reservation size                1           0            1            1 number               dynamic
 job scheduler interval                   1           0            1            1 seconds              dynamic
 job scheduler tasks                     32           0           32           32 number               dynamic
 license information                      0           0            0            0 number               dynamic
 max pci slots                            0           0            0            0 number               dynamic
 maximum job output                   32768           0        32768        32768 bytes                dynamic
 number of alarms                        40           6           40           40 number               dynamic
 number of aux scan descriptors         256        #392          256          256 number               dynamic
 number of large i/o buffers              6        3114            6            6 number               dynamic
 number of mailboxes                     30           2           30           30 number               dynamic
 number of messages                      64           3           64           64 number               dynamic
 number of open databases                12        9232           20           20 number               dynamic
 number of open objects                 500       17680         9000         9000 number               dynamic
 number of pre-allocated extent           2           0            2            2 number               dynamic
 number of sort buffers                 500           0        22500        22500 number               dynamic
 page lock promotion HWM                200           0          200          200 number               dynamic
 page lock promotion LWM                200           0          200          200 number               dynamic
 page lock promotion PCT                100           0          100          100 percent              dynamic
 pci memory size                      32768           0        32768        32768 memory pages(2k)     dynamic
 percent database for history            20           0           20           20 percent              dynamic
 percent database for output             30           0           30           30 percent              dynamic
 percent history free                    30           0           30           30 percent              dynamic
 percent output free                     50           0           50           50 percent              dynamic
 print deadlock information               0           0            0            0 number               dynamic
 procedure cache size                  7000      926406       425000       425000 memory pages(2k)     dynamic
 row lock promotion HWM                 200           0          200          200 number               dynamic
 row lock promotion LWM                 200           0          200          200 number               dynamic
 row lock promotion PCT                 100           0          100          100 percent              dynamic
 rtm thread idle wait period            600           0          600          600 seconds              dynamic
 runnable process search count         2000           0         2000         2000 number               dynamic
 size of auto identity column            10           0           10           10 bytes                dynamic
 sql server clock tick length        100000           0       100000       100000 microseconds         static
 statement cache size                     0           0            0            0 memory pages(2k)     dynamic
 sysstatistics flush interval             0           0            0            0 minutes              dynamic
 text prefetch size                      16           0           16           16 logical pages        dynamic
 time slice                             100           0          100          100 milliseconds         dynamic
 upgrade version                      15000           0        15000        15000 id                   dynamic

Group: Security Related

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 allow procedure grouping                 1           0            1            1 switch               dynamic
 audit queue size                       100           2          100          100 number               dynamic
 auditing                                 0           0            0            0 switch               dynamic
 check password for digit                 0           0            0            0 switch               dynamic
 curread change w/ open cursors           1           0            1            1 switch               dynamic
 current audit table                      1           0            1            1 id                   dynamic
 enable encrypted columns                 0           2            0            0 switch               dynamic
 enable ldap user auth                    0           0            0            0 not applicable       dynamic
 enable logins during recovery            1           0            1            1 switch               dynamic
 enable pam user auth                     0           0            0            0 number               dynamic
 enable row level access                  0           0            0            0 switch               dynamic
 enable ssl                               0           0            0            0 switch               static
 maximum failed logins                    0           0            0            0 number               dynamic
 minimum password length                  6           0            5            5 bytes                dynamic
 msg confidentiality reqd                 0           0            0            0 switch               dynamic
 msg integrity reqd                       0           0            0            0 switch               dynamic
 net password encryption reqd             0           0            0            0 switch               dynamic
 restricted decrypt permission            0           0            0            0 switch               dynamic
 secure default login                 guest           0        guest        guest not applicable       dynamic
 select on syscomments.text               1           0            1            1 switch               dynamic
 suspend audit when device full           1           0            1            1 switch               dynamic
 systemwide password expiration           0           0            0            0 days                 dynamic
 unified login required                   0           0            0            0 switch               dynamic
 use security services                    0           0            0            0 switch               static

Group: Unicode

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 default unicode sortorder           binary           0       binary       binary name                 static
 default xml sortorder               binary           0       binary       binary name                 static
 enable surrogate processing              1           0            1            1 switch               dynamic
 enable unicode conversions               1           0            1            1 switch               dynamic
 enable unicode normalization             1           0            1            1 switch               dynamic
 size of unilib cache                     0         312            0            0 bytes                dynamic

Group: User Environment

 Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
 ------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
 default network packet size           2048       #1032         2048         2048 bytes                static
 max nesting level                       16           0           16           16 number               static
 number of user connections              25       21553           75           75 number               dynamic
 permission cache entries                15        #458           15           15 number               dynamic
 session tempdb log cache size         2048           0         2048         2048 bytes                static
 stack guard size                      4096        #668         4096         4096 bytes                static
 stack size                          143360      #23380       143360       143360 bytes                static
 user log cache size                   2048           0         2048         2048 bytes                static
 user log cache spinlock ratio           20           0           20           20 ratio                dynamic
(return status = 0)

Open in new window

JonahGroupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

alpmoonCommented:
ASE 15 is more conversative to use parallelism comparing with 12.5. 12.5 uses parallelism whenever it is available even in the case it could cause a worse performance.

If you want parallelism on a non-partitioned table, you may need to force it in ASE 15.
0
JonahGroupAuthor Commented:
We're moving from v12.5 to v15.0. We have a few hundred ETL jobs that issue queries against ASE in a nightly batch run. For the most part, ASE 12.5 uses parallelism appropriately and not excessively, based on how we have it configured. And parallelism is sorely needed for us because ASE i/o throughput is so dismal (relative to oracle). So we would rather not modify hundreds of queries (some dynamically constructed), in order to force parallelism.

So it all boils down to this question:
What are the appropriate conditions for the optimizer to choose parallelism on a non-partitioned table?

I find it hard to believe that the answer to this question is NEVER -- It is never appropriate to use parallelism on a non-partitioned table, under *any* circumstances.  I would be willing make whatever configuration adjustments are needed to avoid broad code changes.

Here is the ASE 15 manual on parallel query processing...
http://infocenter.sybase.com/help/topic/com.sybase.dc00743_1500/pdf/qp_abstrpln.pdf
It is confusing in some areas, and contains blatant inaccuracies. For example, it claims:

"In versions earlier than 15.0, Adaptive Server did not try to scan an
unpartitioned table in parallel using a hash-based scan unless a force option was
used."  (chap.4, pg.129)

We know and depend on the opposite to be true, in v12.5. Given this, I'm wondering what Sybase information I can trust.


0
alpmoonCommented:
This sentence must be a document bug. I remember one case Sybase 12.5.3 was scanning a tiny table in parallel. It was causing performance problem because the actual cost becomes much higher in a small table when many users are running that query concurrently due to merge buffer contention. It has been fixed in ASE 15.0.1 by putting a size limit.

I think one option you can try is changing 'optimization goal' to allrows_dss. You can change it server level during those batch processes or just change it session level ('set plan optgoal allrows_dss'). That setting allows more usage of parallel ism. It may cause some other query plan changes as well. You may benefit it in some cases and it might be problematic in some cases. So you should try and find out whether server level change is better for you or just session level change.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Joe WoodhousePrincipal ConsultantCommented:
I don't think optimisation goal is the key here, parallel query is enabled when opt_goal is set to "allows_mix" as it is here.

I suspect this is a statistics issue. ASE's optimiser does everything for a reason, if it is refusing parallel when the option exists (as it seems to from your configuration), then it is because the optimiser thinks it would be slower than a serial table scan.

What sorts of update statistics are you running and how often? My experience with upgrades from 12.5.x to 15.0.x is that ASE becomes a lot fussier about getting good statistics...

Of course, if the real issue is "Sybase I/O is too slow", parallel query is actually a pretty poor answer, because parallel query causes more I/O and will flood any system that's already struggling to deliver. I would ask obvious questions about indexing (and good stats so the optimiser agrees the indexes will help), as well as more complicated questions about some of the more obscure aspects of ASE tuning.

For example I notice your global async prefetch limit is 40, which seems an awfully high number to me. Remember this parameter says "reserve this percentage of every data cache for APFs only and not allow it to be used for regular I/Os"... do your sp_sysmons show that asynchronous prefetching is so successful in this ASE as to warrant burning 40% of data cache on them? I regard 20% as a high number here and actually have never seen it set this high ever.

What platform are you on? I ask because I note "max async i/os per engine" is 4096 on your platform, and sometimes you can get good results by increasing this number. Are you using file devices or raw partitions? What sort of RAID is under these devices?

I'd be looking hard at all of those issues before I got upset about not getting parallel query. Like I said, parallel forces more I/O and there are so many other I/O relevant things to look into.

Anyway, I'm wandering off your question, which is why are you getting parallel in 12.5 and not in 15? My guess is the optimiser statistics. For example if you only run the basic "update statistics" in 12.5, this may well just not be good enough for 15. If that's the case, try "update index statistics" instead.

I also see your default number of histogram steps is 200, while your histogram tuning factor is set to 10. This means you might get as many as 2000 steps in some of your stats, and the feeling seems to be that may be too many except on very large tables (let's talk 100M+ rows as a minimum here). 1000 steps seems to be the sweet spot, and we seem to get better results with a larger tuning factor and a smaller explicit number of steps. That lets ASE use more steps where they'll do some good, and keep the step count lower when more won't help.
0
alpmoonCommented:
Another option to allow more parallelism is explained in Performance Tuning guide. I think you should try that as well:

max resource granularity
Use max resource granularity to specify the percentage of total memory that Adaptive Server can allocate to a single query. You can set the parameter at the server or session level.

For example, to set max resource granularity to 35 percent at the server level, enter:

sp_configure “max resource granularity”, 35
To set max resource granularity to 35 percent at the session level, enter:

set resource_granularity 35
The value of this parameter can affect the query optimizer’s choice of operators for a query. If max resource granularity is set low, many hash- and sort-based operators cannot be chosen. max resource granularity also affects the scheduling algorithm.

0
JonahGroupAuthor Commented:
Thanks for your comments all. Sybase i/o being what I consider slow is another can of worms, and I'll post another 500 point question for this. So I won't get into answering all the questions related to that right now.

Very often in our nightly batch run, we have a single query running, the i/o subsystem very under-utilized, and we have lots of cpu to burn. So for very large non-partitioned tables, where the query covers most of the rows, I think we're doomed without parallelism. Serial access will extend our critical path for the night beyond the time we have.

I tried playing with "max resource granularity" without success (values tried: 2, 5, 10, 40). Still no parallelism.

The flavour of statistics gathered for the table was indeed "update index statistics". But using this on a table that doesn't have indexes might not be ideal, so I've now used "update all statistics". The histogram tuning factor does seem a bit high (200). I think maybe our DBA got the idea from one of the sybase docs, as I recall seeing that recommendation too somewhere.

I tried new stats on all columns with as few as possible histograms first...

Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
histogram tuning factor                 20           0            1            1 number               dynamic
number of histogram steps               20           0            3            3 number               dynamic

Then I tried to hit the step/bucket sweet spot as you described (50x20=1000)...

Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
histogram tuning factor                 20           0           50           50 number               dynamic
number of histogram steps               20           0           20           20 number               dynamic

But the query plan remains the same, no parallelism. Below is the optdiag output for the table, after "update all statistics" using the 50/20 settings above. I've truncated the step/bucket details for the column with 1700 steps. (I guess these histogram parameters are just guidelines for the optimizer.)

--------------------------------------------------------------------

OptDiag/15.0.3/EBF 16554 ESD#1/P/RS6000/AIX 5.3/ase1503/2681/64-bit/OPT/Mon Mar  9 00:10:13 2009
Adaptive Server Enterprise/15.0.3/EBF 16554 ESD#1/P/RS6000/AIX 5.3/ase1503/2681/64-bit/FBO/Mon Mar  9 01:35:28 2009

Server name:                            "FARESIT"

Specified database:                     "HST"
Specified table owner:                  "dbo"
Specified table:                        "SCOTT_CLMD2"
Specified column:                       not specified

Table owner:                            "dbo"
Table name:                             "SCOTT_CLMD2"

Statistics for table:                   "SCOTT_CLMD2"

     Data page count:                   1221317
     Empty data page count:             0
     Data row count:                    63478565.0000000000000000
     Forwarded row count:               0.0000000000000000
     Deleted row count:                 0.0000000000000000
     Data page CR count:                153266.0000000000000000
     OAM + allocation page count:       4810
     First extent data pages:           0
     Data row size:                     34.4147168103122709
     Parallel join degree:              0.0000000000000000
     Unused page count:                 1
     OAM page count:                    20

  Derived statistics:                   
     Data page cluster ratio:           0.9994376092497839
     Space utilization:                 0.8934676351743338
     Large I/O efficiency:              0.9960787018611021

Statistics for column:                  "CLCL_ID"
Last update of column statistics:       Apr  6 2010  3:50:38:210PM

     Range cell density:                0.0000000417143812
     Total density:                     0.0000000417143805
     Range selectivity:                 default used (0.33)
     In between selectivity:            default used (0.25)
     Unique range values:               0.0000000267900052
     Unique total values:               0.0000000267900050
     Average column width:              default used (12.00)

Histogram for column:                   "CLCL_ID"
Column datatype:                        char(12)
Requested step count:                   20
Actual step count:                      23
Sampling Percent:                       0
Out of range Histogram Adjustment is ON.

     Step     Weight                    Value

        1     0.00000000       <=       "00000000010/"
        2     0.00000002       <=       "000000000100"
        3     0.05553769       <=       "E00147998700"
        4     0.05553566       <=       "E00369221101"
        5     0.05553582       <=       "E00583875700"
        6     0.05553571       <=       "E00796415000"
        7     0.05553545       <=       "E01007852600"
        8     0.05553525       <=       "E01214404400"
        9     0.05553440       <=       "E01417308000"
       10     0.05553303       <=       "E01606051300"
       11     0.05502996       <=       "E01790750800"
       12     0.00000000        <       "E01790750900"
       13     0.00000003        =       "E01790750900"
       14     0.05518765       <=       "E01975485700"
       15     0.05518853       <=       "E02159031300"
       16     0.05518850       <=       "E02337804000"
       17     0.05518620       <=       "E02516994900"
       18     0.05518668       <=       "E02697368000"
       19     0.05518625       <=       "M00134887400"
       20     0.05518468       <=       "M00322686200"
       21     0.05518425       <=       "M00517382000"
       22     0.05518118       <=       "V00321144400"
       23     0.00401306       <=       "X00000021100"

Statistics for column:                  "CLMD_LOCK_TOKEN"
Last update of column statistics:       Apr  6 2010  3:09:45:243PM

     Range cell density:                0.0000000000000000
     Total density:                     0.5092110072098804
     Range selectivity:                 default used (0.33)
     In between selectivity:            default used (0.25)
     Unique range values:               0.0000000000000000
     Unique total values:               0.5000000000000000
     Average column width:              default used (2.00)

Histogram for column:                   "CLMD_LOCK_TOKEN"
Column datatype:                        smallint
Requested step count:                   20
Actual step count:                      3
Sampling Percent:                       0
Out of range Histogram Adjustment is ON.

     Step     Weight                    Value

        1     0.00000000        <       0
        2     0.43213615        =       0
        3     0.56786388       <=       1

Statistics for column:                  "CLMD_TYPE"
Last update of column statistics:       Apr  6 2010  3:39:15:716PM

     Range cell density:                0.0000013171786825
     Total density:                     0.3979120402393043
     Range selectivity:                 default used (0.33)
     In between selectivity:            default used (0.25)
     Unique range values:               0.0000008743108796
     Unique total values:               0.0400000000000000
     Average column width:              default used (2.00)

Histogram for column:                   "CLMD_TYPE"
Column datatype:                        char(2)
Requested step count:                   20
Actual step count:                      30
Sampling Percent:                       0
Out of range Histogram Adjustment is ON.

     Step     Weight                    Value

        1     0.00000000       <=       "00"
        2     0.58803082       <=       "01"
        3     0.00000000        <       "02"
        4     0.20146374        =       "02"
        5     0.09283471       <=       "03"
        6     0.04397664       <=       "04"
        7     0.01113496       <=       "05"
        8     0.00684598       <=       "06"
        9     0.00452635       <=       "07"
       10     0.00327919       <=       "08"
       11     0.00214420       <=       "09"
       12     0.00000000        <       "10"
       13     0.00079151        =       "10"
       14     0.00062924       <=       "11"
       15     0.00045614       <=       "12"
       16     0.00036894       <=       "13"
       17     0.00029799       <=       "14"
       18     0.00023852       <=       "15"
       19     0.00016780       <=       "16"
       20     0.00013482       <=       "17"
       21     0.00011152       <=       "18"
       22     0.00000000        <       "AD"
       23     0.02320120        =       "AD"
       24     0.00000000        <       "E1"
       25     0.00429616        =       "E1"
       26     0.00000175       <=       "E3"
       27     0.00000000        <       "R1"
       28     0.01506277        =       "R1"
       29     0.00000350       <=       "R2"
       30     0.00000154       <=       "R3"

Statistics for column:                  "IDCD_ID"
Last update of column statistics:       Apr  6 2010  3:21:01:233PM

     Range cell density:                0.0001413591945909
     Total density:                     0.0036480862685479
     Range selectivity:                 default used (0.33)
     In between selectivity:            default used (0.25)
     Unique range values:               0.0000162126651488
     Unique total values:               0.0000659848234906
     Average column width:              4.4147168103122683

Histogram for column:                   "IDCD_ID"
Column datatype:                        varchar(10)
Requested step count:                   20
Actual step count:                      1702
Sampling Percent:                       0
Out of range Histogram Adjustment is ON.

     Step     Weight                    Value

        1     0.00000000       <=       "ÿÿÿÿÿÿÿÿÿ"
        2     0.00076648        <       "0340"
        3     0.00158784        =       "0340"
        4     0.00024287        <       "0389"

	:::::::  (abbreviated)  ::::::: 

     1700     0.00000000        <       "V8554"
     1701     0.00001549        =       "V8554"
     1702     0.00008343       <=       "v202"

Statistics for column:                  "MEME_CK"
Last update of column statistics:       Apr  6 2010  3:31:35:133PM

     Range cell density:                0.0000033446220062
     Total density:                     0.0000052211372065
     Range selectivity:                 default used (0.33)
     In between selectivity:            default used (0.25)
     Unique range values:               0.0000008245045577
     Unique total values:               0.0000008256377019
     Average column width:              default used (4.00)

Histogram for column:                   "MEME_CK"
Column datatype:                        integer
Requested step count:                   20
Actual step count:                      26
Sampling Percent:                       0
Out of range Histogram Adjustment is ON.

     Step     Weight                    Value

        1     0.00000000       <=       50
        2     0.00000291       <=       51
        3     0.05532141       <=       1549651
        4     0.05530736       <=       3114851
        5     0.05524511       <=       4670152
        6     0.05524933       <=       6224701
        7     0.05523136       <=       7789502
        8     0.05519221       <=       9381051
        9     0.05511774       <=       10956901
       10     0.05545866       <=       12554751
       11     0.03287316       <=       13255302
       12     0.00000000        <       13255351
       13     0.00137153        =       13255351
       14     0.02391781       <=       35245901
       15     0.00000000        <       35245902
       16     0.00000047        =       35245902
       17     0.05523004       <=       57116202
       18     0.05525472       <=       78865701
       19     0.05516566       <=       99764803
       20     0.05518264       <=       112958151
       21     0.05510137       <=       122616351
       22     0.05554321       <=       129137700
       23     0.05548229       <=       132008750
       24     0.05548607       <=       166907701
       25     0.05528287       <=       175040402
       26     0.00198209       <=       177691452

Optdiag succeeded.

Open in new window

0
Joe WoodhousePrincipal ConsultantCommented:
Hmm, I'm stumped for now, the stats seem unlikely to be an issue now.

Agree in your circumstances parallel query sounds appropriate. I'd still be wondering if indexes would help anything here, but if it's a batch job on the entire table and it's picked as the outermost table in any join, a table scan actually is more appropriate than an index scan anyway.

Try setting the "optimisation goal" parameter goal to "allrows_dss" and see if that changes anything?

If not, we'll have to poke around with second-level diagnostics and ask why parallel query wasn't chosen. In 12.5.x we did that with various traceflags that don't seem to exist now in 15.0, I'll have to refresh my memory as to how that works in the more recent versions.
0
JonahGroupAuthor Commented:
I experimented with the optimization goal, but no luck. I will dig into the documentation to see whether we can get the optimizer to reveal the cost calculations it is making. I think that we already know the use of parallelism is going to be more expensive than serial access. I can't imagine a situation where that would not be true. The question is what magic lever tells it that it can ignore the cost, and complete the work sooner. And is there a multiplier config parameter that can be used to fine tune the sensitivity.

1> set plan optgoal allrows_dss
2> go
1> select @@optgoal
2> go

 ------------------------------
 allrows_dss

(1 row affected)
1> set showplan on
2> go
1> set noexec on
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SET OPTION ON.

1> SELECT count(*) FROM SCOTT_CLMD2 WHERE CLMD_LOCK_TOKEN > 10
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SELECT.

        2 operator(s) under root

       |ROOT:EMIT Operator (VA = 2)
       |
       |   |SCALAR AGGREGATE Operator (VA = 1)
       |   |  Evaluate Ungrouped COUNT AGGREGATE.
       |   |
       |   |   |SCAN Operator (VA = 0)
       |   |   |  FROM TABLE
       |   |   |  SCOTT_CLMD2
       |   |   |  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.


1>
0
alpmoonCommented:
I think you can try these option to see more:

set option show long
set option show_parallel long

Also I think you should try "max resource granularity" higher than 40 per cent as well. Or increasing procedure cache size. It doesn't look small, but who knows as it depends on number of sort buffers.

0
JonahGroupAuthor Commented:
Thanks for the suggestions. At this point I'm willing to explore any new paths or hunches. It doesn't appear that the extra "option" detail for parallelism really helps much because it doesn't show anything interesting unless it actually chooses to use parallelism; and that remains the challenge -- making it use parallelism. I didn't increase the procedure cache size or the number of sort buffers because these values are already massive given that I'm all alone on this box, and the table itself is only roughly twice the size of the procedure cache. When I force parallelism, and monitor with sysmon, we don't come anywhere near these upper bounds, so this server has plenty of resources at its disposal.

I've setup some config parameters as follows, and asked for maximum tracing -- not that I actually understand how to read it all; just wanted to have it here for the record.

set plan optgoal allrows_dss
set plan  opttimeoutlimit 80
set resource_granularity 95
set option show long 
set option show_parallel long
dbcc traceon(3604, 317, 302)
set showplan on

Parameter Name                 Default     Memory Used Config Value Run Value    Unit                 Type
------------------------------ ----------- ----------- ------------ ------------ -------------------- ----------
histogram tuning factor                 20           0           50           50 number               dynamic
number of histogram steps               20           0           20           20 number               dynamic
max resource granularity                10           0           95           95 number               dynamic
optimization timeout limit              10           0          100          100 percent              dynamic


1> SELECT count(*) FROM HST..SCOTT_CLMD2
2> go
===================Profile Start==================
  Start to profile Optimizer procedure cache
  allocation pattern and the time spent for
  different phases of the compilation/optimization.
====================================================
The Lop tree:
( project
        ( scalar
                ( scan HST..SCOTT_CLMD2
                )

        )

)

Created the root OptBlock0
Cutting the global Lop tree into OptBlocks...

OptBlock0 - creating Gta0 to stand for ( scalar ( scan HST..SCOTT_CLMD2 )  )
Created the OptBlock1 , child of OptBlock0
OptBlock1 - creating HST..SCOTT_CLMD2 to stand for ( scan HST..SCOTT_CLMD2 )
Phase 1a initialization of OptBlock1 ...
        ... phase 1a done.
Phase 1a initialization of OptBlock0 ...
        ... phase 1a done.
Phase 1b initialization of OptBlock1 ...
        ... phase 1b done.
Phase 1b initialization of OptBlock0 ...
        ... phase 1b done.
Phase Partition initialization of OptBlock0 ...
        ... phase Partition Init done.
Phase Partition initialization of OptBlock1 ...
        ... phase Partition Init done.
Phase 2a initialization of OptBlock0 ...
        ... phase 2a done.
Phase 2a initialization of OptBlock1 ...
        ... phase 2a done.

Creating Initial Statistics for table HST..SCOTT_CLMD2
.....Done creating Initial Statistics for table HST..SCOTT_CLMD2
Phase 2b initialization of OptBlock0 ...
        BEGIN: Dumping partition listw/useful partition info
        END: Dumping partition listw/useful partition info
        ... phase 2b done.
Phase 2b initialization of OptBlock1 ...
        BEGIN: Dumping partition listw/useful partition info
        END: Dumping partition listw/useful partition info
        ... phase 2b done.
        Start merging statistics for table HST..SCOTT_CLMD2
        ..... Done merging statistics for table HST..SCOTT_CLMD2
Hints for tables in optimization block 1
Table: HST..SCOTT_CLMD2
Degrees for HST..SCOTT_CLMD2.Rid :[4]
Degrees for HST..SCOTT_CLMD2.GcaLocalCount*_5:count :[4]
Hints for tables in optimization block 0
OptBlock1
        The Lop tree:
        ( scan HST..SCOTT_CLMD2
        )

        Generic Tables:
                Gtt1( HST..SCOTT_CLMD2 )
                contains:
                         Gc0(HST..SCOTT_CLMD2 ,Rid)
                         Gc5(HST..SCOTT_CLMD2 ,GcaLocalCount*_5:count)

        Generic Columns:
                 Gc0(HST..SCOTT_CLMD2 ,Rid GcTc0  GcOptId=1 GcDt )
                 Gc5(HST..SCOTT_CLMD2 ,GcaLocalCount*_5:count GcTc2  GcOptId=1 GcDt )
                 Gc6(,_virtualconstresult GcTc2  GcOptId=1 GcDt )
        Virtual Generic Columns:
        Predicates:
                Number of Predicate Expressions defined in OptBlock: 0
                Predicate Manager Status: 1
                The Entire Qualification List:
        Transitive Closures:
                Tc0 = {  Gc0(HST..SCOTT_CLMD2 ,Rid GcTc0  GcOptId=1 GcDt )}
                Tc2 = {  Gc5(HST..SCOTT_CLMD2 ,GcaLocalCount*_5:count GcTc2  GcOptId=1 GcDt ) Gc6(,_virtualconstresult GcTc2  GcOptId=1 GcDt )}


OptBlock0
        The Lop tree:
        ( pseudoscan
        )

        Generic Tables:
                Gta0
                contains:
                         Gc1(Gta0 ,_virtualagg)
                         Gc3(Gta0 ,_virtualagg)
                         Gc7(Gta0 ,GcaGlobalCount*_7:sum)

        Generic Columns:
                 Gc1(Gta0 ,_virtualagg GcTc1  GcOptId=0 GcDt )
                 Gc2(,_virtualconstresult GcTc1  GcOptId=0 GcDt )
                 Gc3(Gta0 ,_virtualagg GcTc1  GcOptId=0 GcDt )
                 Gc4(,_virtualconstresult GcTc1  GcOptId=0 GcDt )
                 Gc7(Gta0 ,GcaGlobalCount*_7:sum GcTc1  GcOptId=0 GcDt )
        Virtual Generic Columns:
        Predicates:
                Number of Predicate Expressions defined in OptBlock: 0
                Predicate Manager Status: 1
                The Entire Qualification List:
        Transitive Closures:
                Tc1 = {  Gc1(Gta0 ,_virtualagg GcTc1  GcOptId=0 GcDt ) Gc4(,_virtualconstresult GcTc1  GcOptId=0 GcDt ) Gc3(Gta0 ,_virtualagg GcTc1  GcOptId=0 GcDt ) Gc2(,_virtualconstresult GcTc1  GcOptId=0 GcDt ) Gc7(Gta0 ,GcaGlobalCount*_7:sum GcTc1  GcOptId=0 GcDt )}


... cutting done.


OptCriteria:
advanced_aggregation
append_union_all
bushy_space_search
alternative_greedy_search
distinct_hashing
distinct_sorted
distinct_sorting
group_hashing
group_sorted
hash_join
hash_union_distinct
index_union
merge_join
merge_union_all
merge_union_distinct
multi_table_store_ind
nary_nl_join
nl_join
order_sorting
parallel_query
replicated_partition
streaming_sort
store_index

Optimization Goal: allrows_dss
Session Level Optimization Goal has been set.
Optimization Learning mode disabled.

Optimization Timeout limit: 80%
Session Level Optimization Timeout has been set.
Optimization query tuning time limit enabled.
The compiler will use the current best plan when 100%(Optimization Timeout limit) of average execution time is exceeded.

Optimization query tuning mem limit enabled.
The compiler will use the current best plan when 95%(Max Resource Granularity) of procedure cache is exceeded.

Phase 3 initialization of OptBlock1 ...
        ... phase 3 done.

******************************************************************************
          BEGIN: Search Space Traversal for OptBlock1
******************************************************************************

TreeLogProps({1}) - Proj: {} JoinsToDo: {} UsfOrd: {}
        UsfPartLst ->
                Any :{} Degree(s):[1]
        UsfPartNodeLst ->

Statistics:
Estimated rows =6.347856e+07, Average row size =2, Page Count =62975.77
 Largest Partition Page Count =62975.77, Partition Count =1, Concurrency Threshold =-1
RID-oriented Logical Properties
        TreeLogProps({1}) - Proj: {0} JoinsToDo: {0} UsfOrd: {0}
                UsfPartLst ->
                        Any :{0} Degree(s):[4]
                        Any :{} Degree(s):[1]
                UsfPartNodeLst ->

        Statistics:
        Estimated rows =6.347856e+07, Average row size =2, Page Count =62975.77
         Largest Partition Page Count =62975.77, Partition Count =1, Concurrency Threshold =-1
OptBlock1 Eqc{1} -> Pops added:


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         BEGIN: APPLYING GREEDY SEARCH STRATEGY TO OBTAIN INITIAL PLAN
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
OptBlock1 Eqc{1} -> Pops added:


OptBlock1 Eqc{1}-> priming...
TopLogProps0( HST..SCOTT_CLMD2 )  - TopPred: [Tc{} Pe{}] TopSubst: {}

Estimating selectivity for table 'HST..SCOTT_CLMD2'
    Table scan cost is 6.347856e+07 rows, 1221317 pages,

The table (Datapages) has 6.347856e+07 rows, 1221317 pages,
Data Page Cluster Ratio 0.9994376
    Search argument selectivity is 1.
    using table prefetch (size 16K I/O)
    Large IO selected: The number of leaf pages qualified is > MIN_PREFETCH pages
    in data cache 'default data cache' (cacheid 0) with MRU replacement
    MRU selected:  More than 1/2 of the pool is swept out by the scan
Eqc initialization:
        -> initial Pops:
        ( PopTabScan HST..SCOTT_CLMD2 ) cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.149582e+07 0 }SsRes{ 0.1433824 }O(L1226127,P153869.2,C1.904357e+08) order: none part: Roundrobin :{} Degree(s):[1]

        -> initial Poplets:
        ( PopTabScan HST..SCOTT_CLMD2 ) cost: [C=0 A=0 H=0 1] TsRes{ 0 0 0 0 0 }SsRes{ 0 }order: none part: Roundrobin :{} Degree(s):[1]

Eagerly enforcing...
        the cheapest Pop:
        ( PopTabScan HST..SCOTT_CLMD2 ) cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.149582e+07 0 }SsRes{ 0.1433824 }O(L1226127,P153869.2,C1.904357e+08) order: none part: Roundrobin :{} Degree(s):[1]
        ... Pop enforcers:
        ... PopLet enforcers:
        ... done eager enforcement.

Eqc competition ...
        initial old Pops:
        initial new Pops:
        ( PopTabScan HST..SCOTT_CLMD2 ) cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.149582e+07 0 }SsRes{ 0.1433824 }O(L1226127,P153869.2,C1.904357e+08) order: none part: Roundrobin :{} Degree(s):[1]

        pruned new against total        0
        pruned new against old  0
        pruned old against new  0
        kept old Pops:
        kept new Pops:
        ( PopTabScan HST..SCOTT_CLMD2 ) cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.149582e+07 0 }SsRes{ 0.1433824 }O(L1226127,P153869.2,C1.904357e+08) order: none part: Roundrobin :{} Degree(s):[1]

        ... done Eqc competition.
        ... done Eqc priming.

OptBlock1 Eqc{1} -> Pops added:

        ( PopTabScan HST..SCOTT_CLMD2 ) cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.149582e+07 0 }SsRes{ 0.1433824 }O(L1226127,P153869.2,C1.904357e+08) order: none part: Roundrobin :{} Degree(s):[1]


 -> Poplets added:

( PopTabScan HST..SCOTT_CLMD2 ) cost: [C=0 A=0 H=0 1] TsRes{ 0 0 0 0 0 }SsRes{ 0 }order: none part: Roundrobin :{} Degree(s):[1]



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         DONE: APPLYING GREEDY SEARCH STRATEGY TO OBTAIN INITIAL PLAN
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         BEGIN: APPLYING EXHAUSTIVE SEARCH STRATEGY TO OBTAIN BEST PLAN
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         DONE: APPLYING EXHAUSTIVE SEARCH STRATEGY TO OBTAIN BEST PLAN
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The best plan found in OptBlock1 :

( PopTabScan cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.149582e+07 0 }SsRes{ 0.1433824 }O(L1226127,P153869.2,C1.904357e+08) props: [order: none Round:1{}] Gtt1( HST..SCOTT_CLMD2 ) ) cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.149582e+07 0 }SsRes{ 0.1433824 }O(L1226127,P153869.2,C1.904357e+08) order: no


        The best Poplets found in OptBlock1 ( HST..SCOTT_CLMD2 ) :
        ( PopTabScan cost: [C=0 A=0 H=0 1] TsRes{ 0 0 0 0 0 }SsRes{ 0 }props: [order: none Round:1{}] Gtt1( HST..SCOTT_CLMD2 ) ) cost: [C=0 A=0 H=0 1] TsRes{ 0 0 0 0 0 }SsRes{ 0 }order: none part: Roundrobin :{} Degree(s):[1]


******************************************************************************
          DONE: Search Space Traversal for OptBlock1
******************************************************************************

Phase 3 initialization of OptBlock0 ...
        ... phase 3 done.

******************************************************************************
          BEGIN: Search Space Traversal for OptBlock0
******************************************************************************

TreeLogProps({0}) - Proj: {1} JoinsToDo: {} UsfOrd: {}
        UsfPartLst ->
                Any :{} Degree(s):[1]
        UsfPartNodeLst ->

Statistics:
Estimated rows =1, Average row size =6, Page Count =1
 Largest Partition Page Count =1, Partition Count =1, Concurrency Threshold =-1
OptBlock0 Eqc{0} -> Pops added:


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         BEGIN: APPLYING GREEDY SEARCH STRATEGY TO OBTAIN INITIAL PLAN
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
OptBlock0 Eqc{0} -> Pops added:


Statistics for rows returned to client...
Estimated rows :1    Estimated row width :6
Estimated client cost is :1.603906

OptBlock0 Eqc{0}-> priming...
TopLogProps0( Gta0 )  - TopPred: [Tc{} Pe{}] TopSubst: {1}

<<<Matching partitioning for PopScalar>>>( PopScalar ( PopTabScan HST..SCOTT_CLMD2 ) ) cost: [C=1.603906 A=0 H=1.603906 1] TsRes{ 0 0 0 0 0 }SsRes{ 0 }order: none

        child:( PopTabScan HST..SCOTT_CLMD2 ) cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.149582e+07 0 }SsRes{ 0.1433824 }O(L1226127,P153869.2,C1.904357e+08) order: none part: Roundrobin :{} Degree(s):[1]
        child partition:Roundrobin :{} Degree(s):[1]
2Phase-scalar agg match..applied
Eqc initialization:
        -> initial Pops:
        ( PopScalar ( PopTabScan HST..SCOTT_CLMD2 ) ) cost: [C=2.862852e+07 A=7926478 H=3.6555e+07 1] TsRes{ 3846731 3846731 0 2.785918e+07 0 }SsRes{ 0.1433824 }O(L0,P0,C6.363354e+07) order: none part: Roundrobin :{} Degree(s):[1]

        -> initial Poplets:
Eagerly enforcing...
        the cheapest Pop:
        ( PopScalar ( PopTabScan HST..SCOTT_CLMD2 ) ) cost: [C=2.862852e+07 A=7926478 H=3.6555e+07 1] TsRes{ 3846731 3846731 0 2.785918e+07 0 }SsRes{ 0.1433824 }O(L0,P0,C6.363354e+07) order: none part: Roundrobin :{} Degree(s):[1]
        ... Pop enforcers:
        ... PopLet enforcers:
        ... done eager enforcement.

Eqc competition ...
        initial old Pops:
        initial new Pops:
        ( PopScalar ( PopTabScan HST..SCOTT_CLMD2 ) ) cost: [C=2.862852e+07 A=7926478 H=3.6555e+07 1] TsRes{ 3846731 3846731 0 2.785918e+07 0 }SsRes{ 0.1433824 }O(L0,P0,C6.363354e+07) order: none part: Roundrobin :{} Degree(s):[1]

        pruned new against total        0
        pruned new against old  0
        pruned old against new  0
        kept old Pops:
        kept new Pops:
        ( PopScalar ( PopTabScan HST..SCOTT_CLMD2 ) ) cost: [C=2.862852e+07 A=7926478 H=3.6555e+07 1] TsRes{ 3846731 3846731 0 2.785918e+07 0 }SsRes{ 0.1433824 }O(L0,P0,C6.363354e+07) order: none part: Roundrobin :{} Degree(s):[1]

        ... done Eqc competition.
        ... done Eqc priming.

OptBlock0 Eqc{0} -> Pops added:

        ( PopScalar ( PopTabScan HST..SCOTT_CLMD2 ) ) cost: [C=2.862852e+07 A=7926478 H=3.6555e+07 1] TsRes{ 3846731 3846731 0 2.785918e+07 0 }SsRes{ 0.1433824 }O(L0,P0,C6.363354e+07) order: none part: Roundrobin :{} Degree(s):[1]



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         DONE: APPLYING GREEDY SEARCH STRATEGY TO OBTAIN INITIAL PLAN
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         BEGIN: APPLYING EXHAUSTIVE SEARCH STRATEGY TO OBTAIN BEST PLAN
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         DONE: APPLYING EXHAUSTIVE SEARCH STRATEGY TO OBTAIN BEST PLAN
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The best plan found in OptBlock0 :

( PopScalar cost: [C=2.862852e+07 A=7926478 H=3.6555e+07 1] TsRes{ 3846731 3846731 0 2.785918e+07 0 }SsRes{ 0.1433824 }O(L0,P0,C6.363354e+07) props: [order: none Round:1{}] ( PopTabScan cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.149582e+07 0 }SsRes{ 0.1433824 }O(L1226127,P153869.2,C1.904357e+08) props: [order: none Round:1{}] Gtt1( HS
2.149582e+07 0 }SsRes{ 0.1433824 }O(L1226127,P153869.2,C1.904357e+08) order: none part: Roundrobin :{} Degree(s):[1]
) cost: [C=2.862852e+07 A=7926478 H=3.6555e+07 1] TsRes{ 3846731 3846731 0 2.785918e+07 0 }SsRes{ 0.1433824 }O(L0,P0,C6.363354e+07) order: none part: Roundrobin :{} Degree(s):[1]


        The best Poplets found in OptBlock0 ( Gta0 ) :

******************************************************************************
          DONE: Search Space Traversal for OptBlock0
******************************************************************************

TopLogProps1({0})  - TopPred: [Tc{} Pe{}] TopSubst: {}
The Abstract Plan (AP) of the final query execution plan:
( scalar_agg ( t_scan [HST..SCOTT_CLMD2] ) ) ( prop [HST..SCOTT_CLMD2] ( parallel 1 ) ( prefetch 16 ) ( mru ) )
To experiment with the optimizer behavior, this AP can be modified and then passed to the optimizer using the PLAN clause: SELECT/INSERT/DELETE/UPDATE ... PLAN '( ... )'.
Pop Tree before final partitioning:
( PopEmit cost: [C=1.603906 A=0 H=1.603906 1] TsRes{ 0 0 0 0 0 }SsRes{ 0 }props: [order: none Round:1{}] ( PopScalar cost: [C=2.862852e+07 A=7926478 H=3.6555e+07 1] TsRes{ 3846731 3846731 0 2.785918e+07 0 }SsRes{ 0.1433824 }O(L0,P0,C6.363354e+07) props: [order: none Round:1{}] ( PopTabScan cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.14
}] Gtt1( HST..SCOTT_CLMD2 ) ) cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.149582e+07 0 }SsRes{ 0.1433824 }O(L1226127,P153869.2,C1.904357e+08) order: none part: Roundrobin :{} Degree(s):[1]
) cost: [C=2.862852e+07 A=7926478 H=3.6555e+07 1] TsRes{ 3846731 3846731 0 2.785918e+07 0 }SsRes{ 0.1433824 }O(L0,P0,C6.363354e+07) order: none part: Roundrobin :{} Degree(s):[1]
) cost: [C=1.603906 A=0 H=1.603906 1] TsRes{ 0 0 0 0 0 }SsRes{ 0 }order: none part: Roundrobin :{} Degree(s):[1]
Pop Tree after final partitioning:
( PopEmit cost: [C=1.603906 A=0 H=1.603906 1] TsRes{ 0 0 0 0 0 }SsRes{ 0 }props: [order: none Round:1{}] ( PopScalar cost: [C=2.862852e+07 A=7926478 H=3.6555e+07 1] TsRes{ 3846731 3846731 0 2.785918e+07 0 }SsRes{ 0.1433824 }O(L0,P0,C6.363354e+07) props: [order: none Round:1{}] ( PopTabScan cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.14
}] Gtt1( HST..SCOTT_CLMD2 ) ) cost: [C=2.226517e+07 A=6335638 H=2.860081e+07 1] TsRes{ 3846731 3846731 0 2.149582e+07 0 }SsRes{ 0.1433824 }O(L1226127,P153869.2,C1.904357e+08) order: none part: Roundrobin :{} Degree(s):[1]
) cost: [C=2.862852e+07 A=7926478 H=3.6555e+07 1] TsRes{ 3846731 3846731 0 2.785918e+07 0 }SsRes{ 0.1433824 }O(L0,P0,C6.363354e+07) order: none part: Roundrobin :{} Degree(s):[1]
) cost: [C=1.603906 A=0 H=1.603906 1] TsRes{ 0 0 0 0 0 }SsRes{ 0 }order: none part: Roundrobin :{} Degree(s):[1]
        Optimization counters:
        Created Pops    : 5
        Destructed Pops : 0


The best global plan (Pop tree) :

PARALLEL:
        number of worker processes = 25
        max parallel degree = 3
        min(configured,set) parallel degree = 3
        min(configured,set) hash scan parallel degree = 3
        max repartition degree = 4
        resource granularity (percentage) = 95


FINAL PLAN ( total cost = 3.170591e+07)
        Path: 2.862852e+07
        Work: 7926478
        Est: 3.6555e+07
( PopEmit
        proj: {{ Gta0._virtualagg _virtualconstresult Gta0._virtualagg _virtualconstresult Gta0.GcaGlobalCount*_7:sum } }
        pred: [Tc{} Pe{}]
        subs: {}
        cost: [
                path: 1.603906
                work: 0
                est: 1.603906
        ]
        I/O estimate : [
                rowcount=1
                averagewidth=6
                pages=1
                prefetchpages=1
                lio=0 pio=0 cpu=0
                total lio=0 total pio=0 total cpu=0
        ]
        Cache Strategy: [
                prefetch=YES
                iosize=16384 Bytes
                bufreplace=LRU
        ]
        order: none part: Round Ptn, Degree(s):[ 1 ], TcId's:[ ]

        ( PopScalar
                proj: {{ Gta0._virtualagg _virtualconstresult Gta0._virtualagg _virtualconstresult Gta0.GcaGlobalCount*_7:sum } }
                pred: [Tc{} Pe{}]
                subs: {Gta0._virtualagg }
                cost: [
                        path: 2.862852e+07
                        work: 7926478
                        est: 3.6555e+07
                ]
                I/O estimate : [
                        rowcount=1
                        averagewidth=6
                        pages=1
                        prefetchpages=1
                        lio=0 pio=0 cpu=6.363354e+07
                        total lio=0 total pio=0 total cpu=0
                ]
                Cache Strategy: [
                        prefetch=YES
                        iosize=16384 Bytes
                        bufreplace=LRU
                ]
                order: none part: Round Ptn, Degree(s):[ 1 ], TcId's:[ ]

                ( PopTabScan table: Gtt1( HST..SCOTT_CLMD2 )

                        proj: {}
                        pred: [Tc{} Pe{}]
                        subs: {}
                        cost: [
                                path: 2.226517e+07
                                work: 6335638
                                est: 2.860081e+07
                        ]
                        I/O estimate : [
                                rowcount=6.347856e+07
                                averagewidth=2
                                pages=1226127
                                prefetchpages=1226127
                                lio=1226127 pio=153869.2 cpu=1.904357e+08
                                scanlio=0 scanpio=0 scancpu=0
                        ]
                        Cache Strategy: [
                                prefetch=YES
                                iosize=16384 Bytes
                                bufreplace=MRU
                        ]
                        order: none part: Round Ptn, Degree(s):[ 1 ], TcId's:[ ]
                         )
        )
)

The global CgpGlobal contains:
  [1] _CgpVrangeCt = 1

The Code Generation Pop tree:

==========CgpPop Tree (use CgpPopPrint(id) to browse a single node)=============


                    Emit
                    (0x82dc53b0)

           /
          Scalar
          (0x82dc5480)

 /
TabScan
(0x82dc5560)
HST..SCOTT_CLMD2

=====================================


( Emit
===Begin Emit Node (name="Emit")===
  [1] Resdom List:
RESDOM (!:0x182db3e18) (TokClass: MARK)   (resname:  colid:0  coltype:0x38 (INT4) (hierarchy:15)  colen:4  colstat: 0x0 (0x00)  cstatus2: (0x00000000)  cstatus3: (0x0000)  colprec:0  colscale:0  coloff:0  resutypeid:7 (INT)  resorder:0  resstat2: (0x0000)  resstat3: (0x0000)  resstat4: (0x0000)  resstat5: (0x0000)  resstat6: (0x00) resomnistat: (0x0000)  resvarno:-1 r
   VAR (R:0x182db86e8) (TokClass: GVAR)   (varname:_virtualconstresult  right:0x182dbaab8  varno:-1  colstat: 0x0 (0x00)  cstatus2: (0x00000000)  cstatus3: (0x0000)  coltype:0x38 (INT4) (hierarchy:15)  colen:4  colprec:0  colscale:0  varjtcid:-1  varstat:0x0 ((0x0000))  varstat2:0x0 ((0x00000000))  varlevel:0  varsubq:0)
      AOPCNT (R:0x182dbaab8) (TokClass: AGOP)   (result:0x182db3228  parent:0x182db2b98  combine:0x0  colstat: 0x0 (0x00)  coltype:0x38 (INT4) (hierarchy:15)  colen:16  colprec:0  colscale:0  aoptype:0x38 (INT4), (hierarchy:15), aoputypeid:7  aopstat:0x5 (0x0004 (RIGHTCHILD), 0x0001 (COUNTSTAR))  aoplen:4  aopprec:0  aopscale:0)
         INT4 (R:0x182dbab28) (TokClass: SYB_CONST)   (L: 0x0 ) (type:56 (INT4) prec:0 scale:0 newtype:0 (GENERIC_TOKEN)len:16  maxlen:16 cvarno -1 constat: (0x0001 (COUNTSTAR)) constat2: (0x80 (CONSTANT VALUE KNOWN)) constat3: (0x00) constat4: (0x00) constat5: (0x00)  value (182dbab70):        0)


RESDOM


      \
      VAR
      (-1,0)
      _virtualconstresult
            \
            AOPCNT


                  \
                  INT4
                  0
  [2] Average Row Size = 6
  [3] Row Count = 1
  [4] Logical IO = 0
  [5] Physical IO = 0
  [6] CPU cost = 0

===End Emit Node===


( Scalar
===Begin Scalar Node (name="Scalar")===
  [1] Number of Aggregate =  1
  [2] Subst List Count =  1
  [3] Projection Cols Size =  1
  [4] Having Pred Size =  0
  [5] Get Only One Row = 0
  [6] Aggregates:
    <BEGIN LIST OF SCALAR EXPRESSIONS>
    <SCALAR ELEMENT 1>
AOPCNT (!:0x182dbaab8) (TokClass: AGOP)   (result:0x182db3228  parent:0x182db2b98  combine:0x0  colstat: 0x0 (0x00)  coltype:0x38 (INT4) (hierarchy:15)  colen:16  colprec:0  colscale:0  aoptype:0x38 (INT4), (hierarchy:15), aoputypeid:7  aopstat:0x5 (0x0004 (RIGHTCHILD), 0x0001 (COUNTSTAR))  aoplen:4  aopprec:0  aopscale:0)
   INT4 (R:0x182dbab28) (TokClass: SYB_CONST)   (L: 0x0 ) (type:56 (INT4) prec:0 scale:0 newtype:0 (GENERIC_TOKEN)len:16  maxlen:16 cvarno -1 constat: (0x0001 (COUNTSTAR)) constat2: (0x80 (CONSTANT VALUE KNOWN)) constat3: (0x00) constat4: (0x00) constat5: (0x00)  value (182dbab70):      0)


AOPCNT

      \
      INT4
      0
    <END LIST OF SCALAR EXPRESSIONS>
  [7] Subst List:
    <BEGIN LIST OF SCALAR EXPRESSIONS>
    <SCALAR ELEMENT 1>
VAR (!:0x182db86e8) (TokClass: GVAR)   (varname:_virtualconstresult  right:0x182dbaab8  varno:-1  colstat: 0x0 (0x00)  cstatus2: (0x00000000)  cstatus3: (0x0000)  coltype:0x38 (INT4) (hierarchy:15)  colen:4  colprec:0  colscale:0  varjtcid:-1  varstat:0x0 ((0x0000))  varstat2:0x0 ((0x00000000))  varlevel:0  varsubq:0)
   AOPCNT (R:0x182dbaab8) (TokClass: AGOP)   (result:0x182db3228  parent:0x182db2b98  combine:0x0  colstat: 0x0 (0x00)  coltype:0x38 (INT4) (hierarchy:15)  colen:16  colprec:0  colscale:0  aoptype:0x38 (INT4), (hierarchy:15), aoputypeid:7  aopstat:0x5 (0x0004 (RIGHTCHILD), 0x0001 (COUNTSTAR))  aoplen:4  aopprec:0  aopscale:0)
      INT4 (R:0x182dbab28) (TokClass: SYB_CONST)   (L: 0x0 ) (type:56 (INT4) prec:0 scale:0 newtype:0 (GENERIC_TOKEN)len:16  maxlen:16 cvarno -1 constat: (0x0001 (COUNTSTAR)) constat2: (0x80 (CONSTANT VALUE KNOWN)) constat3: (0x00) constat4: (0x00) constat5: (0x00)  value (182dbab70):   0)


VAR
(-1,0)
_virtualconstresult
      \
      AOPCNT


            \
            INT4
            0
    <END LIST OF SCALAR EXPRESSIONS>
  [8] Projection Cols:
    <BEGIN LIST OF SCALAR EXPRESSIONS>
    <SCALAR ELEMENT 1>
VAR (!:0x182db86e8) (TokClass: GVAR)   (varname:_virtualconstresult  right:0x182dbaab8  varno:-1  colstat: 0x0 (0x00)  cstatus2: (0x00000000)  cstatus3: (0x0000)  coltype:0x38 (INT4) (hierarchy:15)  colen:4  colprec:0  colscale:0  varjtcid:-1  varstat:0x0 ((0x0000))  varstat2:0x0 ((0x00000000))  varlevel:0  varsubq:0)
   AOPCNT (R:0x182dbaab8) (TokClass: AGOP)   (result:0x182db3228  parent:0x182db2b98  combine:0x0  colstat: 0x0 (0x00)  coltype:0x38 (INT4) (hierarchy:15)  colen:16  colprec:0  colscale:0  aoptype:0x38 (INT4), (hierarchy:15), aoputypeid:7  aopstat:0x5 (0x0004 (RIGHTCHILD), 0x0001 (COUNTSTAR))  aoplen:4  aopprec:0  aopscale:0)
      INT4 (R:0x182dbab28) (TokClass: SYB_CONST)   (L: 0x0 ) (type:56 (INT4) prec:0 scale:0 newtype:0 (GENERIC_TOKEN)len:16  maxlen:16 cvarno -1 constat: (0x0001 (COUNTSTAR)) constat2: (0x80 (CONSTANT VALUE KNOWN)) constat3: (0x00) constat4: (0x00) constat5: (0x00)  value (182dbab70):   0)


VAR
(-1,0)
_virtualconstresult
      \
      AOPCNT


            \
            INT4
            0
    <END LIST OF SCALAR EXPRESSIONS>
  [9] Having Pred:
    <BEGIN LIST OF SCALAR EXPRESSIONS>
    <END LIST OF SCALAR EXPRESSIONS>
  [10] Average Row Size = 6
  [11] Row Count = 1
  [12] Logical IO = 0
  [13] Physical IO = 0
  [14] CPU cost = 6.363354e+07

===End Scalar Node===


( TabScan
===Begin Table Scan Node (name="TabScan")===
  [1] Scan on "HST..SCOTT_CLMD2"
  [2] Subst List Count =  0
  [3] Sarg Pred Count =  0
  [4] Mixed Datatype Sarg Pred Count =  0
  [5] Filter Pred Count = 0
  [6] Expensive Pred Count =  0
  [7] Projection Cols Count =  0
  [8] Subst List:
    <BEGIN LIST OF SCALAR EXPRESSIONS>
    <END LIST OF SCALAR EXPRESSIONS>
  [9] Sarg Pred:
    <BEGIN LIST OF SCALAR EXPRESSIONS>
    <END LIST OF SCALAR EXPRESSIONS>
  [10] Mixed Datatype Sarg Pred:
    <BEGIN LIST OF Mixed Datatype Sargs>
    <END LIST OF Mixed Datatype Sargs>
  [11] Filter Pred:
    <BEGIN LIST OF SCALAR EXPRESSIONS>
    <END LIST OF SCALAR EXPRESSIONS>
  [12] Expensive pred:
    <BEGIN LIST OF SCALAR EXPRESSIONS>
    <END LIST OF SCALAR EXPRESSIONS>
  [13] Runtime Partition Elimination pred:
    <BEGIN LIST OF SCALAR EXPRESSIONS>
    <END LIST OF SCALAR EXPRESSIONS>
  [14] Projection Cols:
    <BEGIN LIST OF SCALAR EXPRESSIONS>
    <END LIST OF SCALAR EXPRESSIONS>
  [15] Average Row Size = 2
  [16] Row Count = 63478565
  [17] Logical IO = 1226127
  [18] Physical IO = 153869.2
  [19] CPU cost = 1.904357e+08

===End Table Scan Node===


HST..SCOTT_CLMD2) ) )

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is SELECT.

        2 operator(s) under root

       |ROOT:EMIT Operator (VA = 2)
       |
       |   |SCALAR AGGREGATE Operator (VA = 1)
       |   |  Evaluate Ungrouped COUNT AGGREGATE.
       |   |
       |   |   |SCAN Operator (VA = 0)
       |   |   |  FROM TABLE
       |   |   |  HST..SCOTT_CLMD2
       |   |   |  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.



 -----------
    63478565

(1 row affected)
1>

Open in new window

0
alpmoonCommented:
I would suggest to log a technical case with  Sybase tech support. I am guessing that it is a bug. It might be fixed in 15.0.3 ESD#3, even though I haven't seen such a bug fix in the fixed bug list. However, there is a similar bug for index creation with "parallel sort" fixed in ESD#3. In ESD#1, it requires "with consumers" clause and in ESD#3 that bug has been fixed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joe WoodhousePrincipal ConsultantCommented:
Concur with alpmoon, I don't see anything misconfigured that would prevent parallel query. Time to talk to Sybase Tech Support.
0
JonahGroupAuthor Commented:
Very well then. I'll initiate a support call, and keep this question open for a few days. I'll post more comments if they have anything interesting to say.
0
JonahGroupAuthor Commented:
Thanks for the help guys. Sybase tech support was not able to get default parallelism to happen on non-partitioned tables with any combination of configurations. However, they have proven to me that ESD#4 resolves the problem. It's coming out in June/2010, but they're trying to get a pre-release for me.

Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.