Symptom
Optimizer always chooses a parallel execution plan although serial processing is faster than parallel.
1.In parallel mode
Optimized using Deferred Parallel Mode
Executed in parallel by coordinating process and 18 worker processes.
STEP 1
The type of query is SELECT.
5 operator(s) under root
|ROOT:EMIT Operator (VA = 5)
|
| |TOP Operator (VA = 4)
| | Top Limit: 50
| |
| | |EXCHANGE Operator (VA = 3) (Merged)
| | |Executed in parallel by 18 Producer and 1 Consumer processes.
| | |
| | | |EXCHANGE:EMIT Operator (VA = 2)
| | | |
| | | | |RESTRICT Operator (VA = 1)(4)(0)(0)(0)(0)
| | | | |
| | | | | |SCAN Operator (VA = 0)
| | | | | | FROM TABLE
| | | | | | <partitioned table>
| | | | | | Table Scan.
| | | | | | Forward Scan.
| | | | | | Positioning at start of table.
| | | | | | Executed in parallel with a 18-way hash scan.
| | | | | | Using I/O Size 16 Kbytes for data pages.
| | | | | | With LRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 1.
Adaptive Server cpu time: 100 ms.
==================== Lava Operator Tree ====================
Emit
(VA = 5)
r:50 er:9.284e+07
cpu: 0
/
Top
(VA = 4)
r:50 er:9.284e+07
l:0 el:9.488e+07
p:0 ep:0
/
Exchange
P(18) C(1)
(VA = 3)
r:50 er:9.284e+07
l:0 el:9.488e+07
p:0 ep:0
/
ExchangeEmit
(VA = 2)
r:58 er:9.284e+07
l:0 el:9.488e+07
p:0 ep:0
/
Restrict
(4)(0)(0)(0)(0)
(VA = 1)
r:62 er:9.284e+07
/
TableScan
<partitioned table>
(VA = 0)
r:62 er:9.284e+07
l:29 el:5
p:26 ep:2
============================================================
Table: <partitioned table> scan count 5, logical reads: (regular=29 apf=0 total=29), physical reads: (regular=15 apf=11 total=26), apf IOs used=6
Total writes for this command: 0
Execution Time 150.
Adaptive Server cpu time: 14971 ms. Adaptive Server elapsed time: 14981 ms.
(50 rows affected)
Execution Time (ms.): 14879 Clock Time (ms.): 15042
2.In serial mode
QUERY PLAN FOR STATEMENT 1 (at line 2).
Optimized using Parallel Mode
STEP 1
The type of query is SELECT.
3 operator(s) under root
|ROOT:EMIT Operator (VA = 3)
|
| |TOP Operator (VA = 2)
| | Top Limit: 50
| |
| | |RESTRICT Operator (VA = 1)(4)(0)(0)(0)(0)
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | <partitioned table>
| | | | Table Scan.
| | | | Forward Scan.
| | | | Positioning at start of table.
| | | | Using I/O Size 16 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.
Parse and Compile Time 0.
Adaptive Server cpu time: 0 ms.
|CHK|BASEYM |STAT|SABH |CUSTNO |CO_NAME |NEW_DEBTCD|INDCD |CR_DT |CLO_ST_DT|CLO_END_DT|FIN_DT |FIN_BS_DT|SYS_DT |BRCD |PAR_BRCD|APP_TYPE_MON|APP_TYPE|COL_TYPE|FIN_TERM |ASSETS |SALES |EXPOSURE |TOTLOAN |CARD_LMT |KIUPLOAN|FIRST_CO|FAMILY_CO|CNC_RSN|KIUPAMT |WCUNO |YYYYMMDD|SUB_COL_TYPE|EX_KIUP_LOAN|B2B_HIST_YN|EX_COL_TYPE|WBCS_RLNM_ALTR_NO|CAQ_SUB_CNT|RM_DCD|CSMN_TEAM_CD|CRD_SMS_FEE_OTST_PRNC_AMT|
|---|----------|----|----------|----------|----------------------------------------------------------------------------------------------------|----------|------|--------|---------|----------|--------|---------|--------|-------|--------|------------|--------|--------|-------------|-----------------------|-----------------------|-----------------------|-----------------------|-----------------------|--------|--------|---------|-------|-----------------------|----------|--------|------------|------------|-----------|-----------|-----------------|-----------|------|------------|-------------------------|
... Á¤»óÃâ·Â ...
==================== Lava Operator Tree ====================
Emit
(VA = 3)
r:50 er:9.085e+07
cpu: 0
/
Top
(VA = 2)
r:50 er:9.085e+07
l:0 el:5
p:0 ep:2
/
Restrict
(4)(0)(0)(0)(0)
(VA = 1)
r:50 er:9.085e+07
/
TableScan
<partitioned table>
(VA = 0)
r:50 er:9.085e+07
l:5 el:5
p:3 ep:2
============================================================
Table: <partitioned table> scan count 1, logical reads: (regular=5 apf=0 total=5), physical reads: (regular=0 apf=3 total=3), apf IOs used=1
Total writes for this command: 0
Execution Time 0.
Adaptive Server cpu time: 1 ms. Adaptive Server elapsed time: 2 ms.
(50 rows affected)
Execution Time (ms.): 60 Clock Time (ms.): 62
Read more...
Environment
SAP Adaptive Server Enterprise (ASE) 16.0 SP03 PL11 ONE-OFF/EBF 30260 for HP-UX
Product
Keywords
CR#827342, CR827342, 827342 , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem
About this page
This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.