BLU 的效率问题
db2licm -l
Product name: "DB2 Advanced Enterprise Server Edition"
License type: "Terabyte"
Expiry date: "Permanent"
Product identifier: "db2aese"
Version information: "10.5"
Enforcement policy: "Soft Stop"
Product name: "DB2 Advanced Enterprise Server Edition"
License type: "Terabyte"
Expiry date: "Permanent"
Product identifier: "db2aese"
Version information: "10.5"
Enforcement policy: "Soft Stop"
数据库启动后， 列表数据访问很慢 （1分钟）， 行表数据访问正常（1秒钟）， count 倒是很快。
行表
>># time db2 "select * from richard.CMRM_PROD_DIM_Rfetch first 10 rows only with ur"
10 record(s) selected.
real 0m0.09s
user 0m0.01s
sys 0m0.00s
列表
>># time db2 "select * from richard.CMRM_PROD_DIM fetch first 10 rows only with ur"
10 record(s) selected.
real 0m50.76s
user 0m0.00s
sys 0m0.01s
行表
>># time db2 "select count(1) from richard.CMRM_PROD_DIM_R with ur"
1
-----------
11075304
1 record(s) selected.
real 3m34.39s
user 0m0.01s
sys 0m0.01s
列表
>># time db2 "select count(1) from richard.CMRM_PROD_DIM with ur"
1
-----------
11075304
1 record(s) selected.
real 0m0.39s
user 0m0.00s
sys 0m0.00s
5同行回答
都要读全部的表，FIRST X ROWS ONLY 没什么作用。收起
浏览706
fetch first 10 rows only with ur 需要 1 分钟 总 是 有问题。
EXPLAIN 很简单
Statement:
select *
from richard.CMRM_PROD_DIM
fetch first 10 rows only
with ur
Statement Isolation Level = Uncommitted Read
Intra-Partition Parallelism Degree = 16
Section Code Page = 1208
Estimated Cost = 7.201596
Estimated Cardinality = 10.000000
CDE Subquery
| Tables Referenced:
| | 1: RICHARD.CMRM_PROD_DIM ID = 2,45
| #Output Columns = 121
Return Data to Application
| #Columns = 121
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
10
RETURN
( 1)
7.2016
|
10
CTQ
( 2)
7.2016
|
*
|
1.10753e+07
Table:
RICHARD
CMRM_PROD_DIM收起
EXPLAIN 很简单
Statement:
select *
from richard.CMRM_PROD_DIM
fetch first 10 rows only
with ur
Statement Isolation Level = Uncommitted Read
Intra-Partition Parallelism Degree = 16
Section Code Page = 1208
Estimated Cost = 7.201596
Estimated Cardinality = 10.000000
CDE Subquery
| Tables Referenced:
| | 1: RICHARD.CMRM_PROD_DIM ID = 2,45
| #Output Columns = 121
Return Data to Application
| #Columns = 121
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
10
RETURN
( 1)
7.2016
|
10
CTQ
( 2)
7.2016
|
*
|
1.10753e+07
Table:
RICHARD
CMRM_PROD_DIM收起
浏览743