|
|
|
 |
 |
Archives of the TeradataForumMessage Posted: Mon, 07 Feb 2011 @ 17:44:44 GMT
Hi, For the last 2 weeks we are having problems with our loading and to be precisely with the execution of our programmed reports. They are lasting much more than usual, but the amount of data didn4t increase that much. We think that we were able to detect the point of the consults where the problem is (it is way the slower): We do an All-AMPs JOIN step from Spool 32237 (Last Use) by way of an all-rows scan, which is joined to table FAC_MOVIMIENTOS_ALMACEN. Spool 32237 and table FAC_MOVIMIENTOS_ALMACEN are joined using a product join enhanced by Dynamic Partition Elimination. The result goes into Spool 32239, which is built locally on the AMPs. This step is performed in parallel. The explain for one of the consults would be:
1 0:00,00 0:00,05 0 1 First, lock DIM_VSE_PROD."pseudo table"
for read on a row hash.
2 0:00,00 0:00,06 0 1 Next, we lock DIM_VSE_PROD."pseudo table"
for read on a row hash.
3 0:00,00 0:00,06 0 1 We lock DIM_VSE_PROD."pseudo table"
for read on a row hash.
4 0:00,00 0:00,03 0 1 We lock DIM_VSE_PROD."pseudo table"
for read on a row hash.
5 0:00,00 0:04,85 0 1 We lock DIM_VSE_PROD."pseudo table"
for read on a row hash.
6 0:00,00 0:00,07 0 1 We lock DIM_VSE_PROD."pseudo table"
for read on a row hash.
7 0:00,00 0:00,06 0 1 We lock DIM_VSE_PROD."pseudo table"
for read on a row hash.
8 0:00,00 0:04,20 0 40 We lock DIM_VSE_PROD.DIM_MODELO_COLOR
for read, we lock DIM_VSE_PROD.DIM_MODELO
for read, we lock DIM_VSE_PROD.DIM_TIENDA
for read, we lock DIM_VSE_PROD.DIM_TEMPORADA
for read, we lock DIM_VSE_PROD.DIM_DIA for read,
we lock DIM_VSE_PROD.FAC_MOVIMIENTOS_ALMACEN
for read and we lock DIM_VSE_PROD.DIM_GENERO
for read.
9 0:00,01 0:12,96 280 2400 We do an All-AMPs RETRIEVE step from
DIM_VSE_PROD.DIM_TEMPORADA by way of an all-rows
scan into Spool 32232, which is duplicated on
all AMPs. This step begins a parallel block
of steps.
9 0:00,27 0:19,84 28604 286037 We do an All-AMPs RETRIEVE step from
DIM_VSE_PROD.DIM_MODELO_COLOR by way of an
all-rows scan into Spool 32233, which is
built locally on the AMPs. This step ends
a parallel block of steps.
10 0:00,35 11:31,70 25640 5413120 We do an All-AMPs JOIN step from Spool 32232
(Last Use) by way of an all-rows scan, which
is joined to table DIM_MODELO. Spool 32232 and
table DIM_MODELO are joined using asingle
partition hash join. The result goes into
Spool 32234, which is duplicated on all AMPs.
11 0:00,01 0:32,52 160 160 We do an All-AMPs RETRIEVE step from
DIM_VSE_PROD.DIM_GENERO by way of an all-rows
scan into Spool 32235, which is duplicated on
all AMPs. This step begins a parallel block
of steps.
11 0:00,02 0:27,31 267 284910 We do an All-AMPs JOIN step from Spool 32233
(Last Use) by way of an all-rows scan, which is
joined to Spool 32234. Spool 32233 and Spool 32234
are joined using a merge join . The result goes
into Spool 32236, which is built locally on the AMPs.
This step is performed in parallel.
11 0:00,01 0:35,76 40 40 We do an All-AMPs RETRIEVE step from
DIM_VSE_PROD.DIM_TEMPORADA by way of an all-rows scan
into Spool 32237, which is duplicated on all AMPs.
This step ends a parallel block of steps.
12 0:00,03 24:50,36 10680 11396400 We do an All-AMPs JOIN step from Spool 32235 (Last Use)
by way of an all-rows scan, which is joined to
Spool 32236. Spool 32235 and Spool 32236 are joined
using a merge join . The result goes into Spool 32238,
which is duplicated on all AMPs. This step begins a
parallel block of steps.
12 0:04,89 0:53,22 12495470 66195750 We do an All-AMPs JOIN step from Spool 32237 (Last Use)
by way of an all-rows scan, which is joined to table
FAC_MOVIMIENTOS_ALMACEN. Spool 32237 and table
FAC_MOVIMIENTOS_ALMACEN are joined using a product join
enhanced by Dynamic Partition Elimination. The result
goes into Spool 32239, which is built locally on the AMPs.
This step is performed in parallel.
12 0:00,01 0:30,24 16080 16080 We do an All-AMPs RETRIEVE step from DIM_VSE_PROD.DIM_DIA
by way of an all-rows scan into Spool 32240, which is
duplicated on all AMPs. This step ends a parallel block
of steps.
13 0:00,87 10:25,32 17323 64347890 We do an All-AMPs JOIN step from Spool 32238 (Last Use)
by way of an all-rows scan, which is joined to
Spool 32239. Spool 32238 and Spool 32239 are joined
using asingle partition hash join . The result goes into
Spool 32241, which is built locally on the AMPs.
14 0:00,04 1890 We do an All-AMPs JOIN step from Spool 32240 (Last Use)
by way of an all-rows scan, which is joined to Spool 32241.
Spool 32240 and Spool 32241 are joined using a merge join .
The result goes into Spool 32242, which is redistributed
by hash code to all AMPs.
15 0:00,03 1890 We do an All-AMPs JOIN step from DIM_VSE_PROD.DIM_TIENDA
by way of an all-rows scan, which is joined to Spool 32242.
table DIM_TIENDA and Spool 32242 are joined using a
merge join . The result goes into Spool 32231, which is
built locally on the AMPs.
16 0:00,06 1418 We do a SUM step to aggregate from Spool 32231 (Last Use)
by way of an all-rows scan. Aggregate Intermediate Results
are computed globally, then placed in Spool 32243.
17 0:00,02 1418 We do an All-AMPs RETRIEVE step from Spool 32243 (Last Use)
by way of an all-rows scan into Spool 32229, which is built
locally on the AMPs.
The fact table:
CREATE MULTISET TABLE DIM_VSE_PROD.FAC_MOVIMIENTOS_ALMACEN ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
id_temporada_movimiento SMALLINT,
id_destino INTEGER,
id_origen INTEGER COMPRESS (0 ,15216 ,11907 ,13522 ,-2 ),
id_item INTEGER,
id_modelo_color INTEGER,
id_modelo INTEGER,
id_divisa INTEGER COMPRESS (9 ,27 ,32 ,34 ,37 ,60 ,63 ,80 ,82 ,87 ),
id_tipo_movimiento_dwh SMALLINT COMPRESS (1 ,3 ,4 ,5 ,7 ,9 ,13 ,14 ,16 ,91 ),
fec_movimiento DATE FORMAT 'YY/MM/DD',
imp_por_impuesto DECIMAL(10,4),
num_traspaso VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
num_ampliacion VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
val_acum INTEGER COMPRESS (1 ,2 ,3 ,4 ,6 ,-6 ,-4 ,-3 ,-2 ,-1 ),
imp_pvp_tienda_eur DECIMAL(15,3),
imp_pvp_tienda_div DECIMAL(15,3),
valor_factura DECIMAL(15,3),
imp_precio_fact_dev_eur DECIMAL(15,3),
imp_precio_fact_dev_div DECIMAL(15,3),
fec_modificacion DATE FORMAT 'YY/MM/DD') PRIMARY INDEX ( id_destino ,id_item )
PARTITION BY id_temporada_movimiento + 1 INDEX idxMA1 ( id_destino ) INDEX idxMA2 (id_item );
We would appreciate any help in this subject. Thanks Fina Rodrmguez Posada
| ||||||||||||||||||||||||||||||||||||||||||||||
| | ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
| Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
| Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||
