sql - sistemas - Falta STOPKEY por partición en el plan de Oracle para la paginación por índice local
sql server pagination (4)
Cuando utiliza variables de enlace, Oracle se ve obligado a usar la eliminación dinámica de particiones en lugar de la eliminación estática de particiones . El resultado de esto es que Oracle no sabe en el momento del análisis a qué particiones se accederá, ya que esto cambia según las variables de entrada.
Esto significa que cuando se utilizan valores literales (en lugar de variables de enlace), sabemos a qué particiones accederá su índice local. Por lo tanto, la count stopkey se puede aplicar a la salida del índice antes de que podamos eliminar las particiones.
Cuando se usan variables de enlace, el partition range iterator tiene que averiguar a qué particiones está accediendo. Luego tiene una verificación para asegurar que la primera de sus variables en las operaciones entre sí tenga un valor más bajo que la segunda (la operación de filter en el segundo plan).
Esto se puede reproducir fácilmente, como muestra el siguiente caso de prueba:
create table tab (
x date,
y integer,
filler varchar2(100)
) partition by range(x) (
partition p1 values less than (date''2013-01-01''),
partition p2 values less than (date''2013-02-01''),
partition p3 values less than (date''2013-03-01''),
partition p4 values less than (date''2013-04-01''),
partition p5 values less than (date''2013-05-01''),
partition p6 values less than (date''2013-06-01'')
);
insert into tab (x, y)
select add_months(trunc(sysdate, ''y''), mod(rownum, 5)), rownum, dbms_random.string(''x'', 50)
from dual
connect by level <= 1000;
create index i on tab(x desc, y desc) local;
exec dbms_stats.gather_table_stats(user, ''tab'', cascade => true);
explain plan for
SELECT * FROM (
SELECT rowid FROM tab
where x between date''2013-01-01'' and date''2013-02-02''
and y between 50 and 100
order by x desc, y desc
)
where rownum <= 5;
SELECT * FROM table(dbms_xplan.display(null, null, ''BASIC +ROWS +PARTITION''));
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 1 | | |
| 3 | SORT ORDER BY STOPKEY | | 1 | | |
| 4 | PARTITION RANGE ITERATOR| | 1 | 2 | 3 |
| 5 | COUNT STOPKEY | | | | |
| 6 | INDEX RANGE SCAN | I | 1 | 2 | 3 |
--------------------------------------------------------------------
explain plan for
SELECT * FROM (
SELECT rowid FROM tab
where x between to_date(:st, ''dd/mm/yyyy'') and to_date(:en, ''dd/mm/yyyy'')
and y between :a and :b
order by x desc, y desc
)
where rownum <= 5;
SELECT * FROM table(dbms_xplan.display(null, null, ''BASIC +ROWS +PARTITION''));
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 1 | | |
| 3 | SORT ORDER BY STOPKEY | | 1 | | |
| 4 | FILTER | | | | |
| 5 | PARTITION RANGE ITERATOR| | 1 | KEY | KEY |
| 6 | INDEX RANGE SCAN | I | 1 | KEY | KEY |
---------------------------------------------------------------------
Como en su ejemplo, la segunda consulta solo puede filtrar las particiones a una key en tiempo de análisis, en lugar de las particiones exactas como en el primer ejemplo.
Este es uno de esos casos raros donde los valores literales pueden proporcionar un mejor rendimiento que las variables de enlace. Debes investigar si esto es una posibilidad para ti.
Finalmente, dices que quieres 20 filas de cada partición. Su consulta como stands no hará esto, solo le devolverá las primeras 20 filas de acuerdo con su pedido. Para 20 filas / partición, necesita hacer algo como esto:
select rd from (
select rowid rd,
row_number() over (partition by trx_id order by create_ts desc) rn
from OUT_SMS
where TRX_ID between ? and ?
and CREATE_TS between ? and ?
order by CREATE_TS DESC, TRX_ID DESC
) where rn <= 20
ACTUALIZAR
La razón por la que no obtiene la count stopkey del count stopkey tiene que ver con la operación de filter en la línea 4 del plan "incorrecto". Puede ver esto más claramente si repite el ejemplo anterior, pero sin partición.
Esto te da los siguientes planes:
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | VIEW | |
|* 3 | SORT ORDER BY STOPKEY| |
|* 4 | TABLE ACCESS FULL | TAB |
----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
4 - filter("X">=TO_DATE('' 2013-01-01 00:00:00'', ''syyyy-mm-dd
hh24:mi:ss'') AND "X"<=TO_DATE('' 2013-02-02 00:00:00'', ''syyyy-mm-dd
hh24:mi:ss'') AND "Y">=50 AND "Y"<=100)
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | VIEW | |
|* 3 | SORT ORDER BY STOPKEY| |
|* 4 | FILTER | |
|* 5 | TABLE ACCESS FULL | TAB |
----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
4 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B) AND
TO_DATE(:ST,''dd/mm/yyyy'')<=TO_DATE(:EN,''dd/mm/yyyy''))
5 - filter("Y">=TO_NUMBER(:A) AND "Y"<=TO_NUMBER(:B) AND
"X">=TO_DATE(:ST,''dd/mm/yyyy'') AND "X"<=TO_DATE(:EN,''dd/mm/yyyy''))
Como puede ver, hay una operación de filter adicional cuando utiliza las variables de enlace que aparecen antes del sort order by stopkey . Esto sucede después de acceder al índice. Esto está verificando que los valores de las variables permitirán que se devuelvan datos (la primera variable entre las que tiene realmente tiene un valor más bajo que la segunda). Esto no es necesario cuando se usan literales porque el optimizador ya sabe que 50 es menos de 100 (en este caso). Sin embargo, no sabe si: a es menor que: b en tiempo de análisis.
¿Por qué exactamente esto es que no lo sé. Podría ser un diseño intencional por parte de Oracle; no tiene sentido realizar una comprobación de la tecla de parada si los valores establecidos para las variables dan como resultado cero filas, o simplemente un descuido.
Hay siguiente tabla particionada:
CREATE TABLE "ERMB_LOG_TEST_BF"."OUT_SMS"(
"TRX_ID" NUMBER(19,0) NOT NULL ENABLE,
"CREATE_TS" TIMESTAMP (3) DEFAULT systimestamp NOT NULL ENABLE,
/* other fields... */
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ERMB_LOG_TEST_BF"
PARTITION BY RANGE ("TRX_ID") INTERVAL (281474976710656)
(PARTITION "SYS_P1358" VALUES LESS THAN (59109745109237760) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ERMB_LOG_TEST_BF");
CREATE INDEX "ERMB_LOG_TEST_BF"."OUT_SMS_CREATE_TS_TRX_ID_IX" ON "ERMB_LOG_TEST_BF"."OUT_SMS" ("CREATE_TS" DESC, "TRX_ID" DESC)
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
(PARTITION "SYS_P1358"
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ERMB_LOG_TEST_BF");
Tengo una consulta de SQL, que selecciona 20 registros ordenados por fecha y transacción:
select rd from (
select /*+ INDEX(OUT_SMS OUT_SMS_CREATE_TS_TRX_ID_IX) */ rowid rd
from OUT_SMS
where TRX_ID between 34621422135410688 and 72339069014638591
and CREATE_TS between to_timestamp(''2013-02-01 00:00:00'', ''yyyy-mm-dd hh24:mi:ss'')
and to_timestamp(''2013-03-06 08:57:00'', ''yyyy-mm-dd hh24:mi:ss'')
order by CREATE_TS DESC, TRX_ID DESC
) where rownum <= 20
Oracle ha generado el siguiente plan:
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 240 | | 4788K (1)| 00:05:02 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 312M| 3576M| | 4788K (1)| 00:05:02 | | |
|* 3 | SORT ORDER BY STOPKEY | | 312M| 9G| 12G| 4788K (1)| 00:05:02 | | |
| 4 | PARTITION RANGE ITERATOR| | 312M| 9G| | 19 (0)| 00:00:01 | 1 | 48 |
|* 5 | COUNT STOPKEY | | | | | | | | |
|* 6 | INDEX RANGE SCAN | OUT_SMS_CREATE_TS_TRX_ID_IX | 312M| 9G| | 19 (0)| 00:00:01 | 1 | 48 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
3 - filter(ROWNUM<=20)
5 - filter(ROWNUM<=20)
6 - access(SYS_OP_DESCEND("CREATE_TS")>=HEXTORAW(''878EFCF9F6C5FEFAFF'') AND
SYS_OP_DESCEND("TRX_ID")>=HEXTORAW(''36F7E7D7F8A4F0BFA9A3FF'') AND
SYS_OP_DESCEND("CREATE_TS")<=HEXTORAW(''878EFDFEF8FEF8FF'') AND
SYS_OP_DESCEND("TRX_ID")<=HEXTORAW(''36FBD0E9D4E9DBD5F8A6FF'') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))<=TIMESTAMP'' 2013-03-06 08:57:00,000000000'' AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))<=72339069014638591 AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))>=34621422135410688 AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))>=TIMESTAMP'' 2013-02-01 00:00:00,000000000'')
Funciona perfectamente.
Por cierto, la tabla OUT_SMS está particionada por el campo TRX_ID y OUT_SMS_CREATE_TS_TRX_ID_IX es un índice local (CREATE_TS DESC, TRX_ID DESC) en cada partición.
Pero si convierto esta consulta a declaración preparada :
select rd from (
select /*+ INDEX(OUT_SMS OUT_SMS_CREATE_TS_TRX_ID_IX) */ rowid rd
from OUT_SMS
where TRX_ID between ? and ?
and CREATE_TS between ? and ?
order by CREATE_TS DESC, TRX_ID DESC
) where rownum <= 20
Oracle genera el siguiente plan:
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 240 | 14743 (1)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 1964 | 23568 | 14743 (1)| 00:00:01 | | |
|* 3 | SORT ORDER BY STOPKEY | | 1964 | 66776 | 14743 (1)| 00:00:01 | | |
|* 4 | FILTER | | | | | | | |
| 5 | PARTITION RANGE ITERATOR| | 1964 | 66776 | 14742 (1)| 00:00:01 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | OUT_SMS_CREATE_TS_TRX_ID_IX | 1964 | 66776 | 14742 (1)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
3 - filter(ROWNUM<=20)
4 - filter(TO_TIMESTAMP(:RR,''yyyy-mm-dd hh24:mi:ss'')<=TO_TIMESTAMP(:T,''yyyy-mm-dd hh24:mi:ss'') AND
TO_NUMBER(:ABC)<=TO_NUMBER(:EBC))
6 - access(SYS_OP_DESCEND("CREATE_TS")>=SYS_OP_DESCEND(TO_TIMESTAMP(:T,''yyyy-mm-dd hh24:mi:ss'')) AND
SYS_OP_DESCEND("TRX_ID")>=SYS_OP_DESCEND(TO_NUMBER(:EBC)) AND
SYS_OP_DESCEND("CREATE_TS")<=SYS_OP_DESCEND(TO_TIMESTAMP(:RR,''yyyy-mm-dd hh24:mi:ss'')) AND
SYS_OP_DESCEND("TRX_ID")<=SYS_OP_DESCEND(TO_NUMBER(:ABC)))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))>=TO_NUMBER(:ABC) AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("TRX_ID"))<=TO_NUMBER(:EBC) AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))>=TO_TIMESTAMP(:RR,''yyyy-mm-dd hh24:mi:ss'') AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATE_TS"))<=TO_TIMESTAMP(:T,''yyyy-mm-dd hh24:mi:ss''))
La operación COUNT STOPKEY desaparece del plan. Esta operación debe realizarse después de que se analice el índice para obtener 20 filas de cada partición, como la primera consulta.
¿Cómo puedo redactar una declaración preparada para que COUNT STOPKEY esté en el plan?
Puedo confirmar que el problema en cuestión sigue siendo un problema en Oracle 12.1.0.2.0.
E incluso los límites de eliminación de particiones codificados no son suficientes.
Aquí está la tabla de prueba en mi caso:
CREATE TABLE FR_MESSAGE_PART (
ID NUMBER(38) NOT NULL CONSTRAINT PK_FR_MESSAGE_PART PRIMARY KEY USING INDEX LOCAL,
TRX_ID NUMBER(38) NOT NULL, TS TIMESTAMP NOT NULL, TEXT CLOB)
PARTITION BY RANGE (ID) (PARTITION PART_0 VALUES LESS THAN (0));
CREATE INDEX IX_FR_MESSAGE_PART_TRX_ID ON FR_MESSAGE_PART(TRX_ID) LOCAL;
CREATE INDEX IX_FR_MESSAGE_PART_TS ON FR_MESSAGE_PART(TS) LOCAL;
La tabla se rellena con varios millones de registros de datos de producción de OLTP durante varios meses. Cada mes pertenece a una partición separada.
Los valores de clave principal de esta tabla siempre incluyen la parte del tiempo en bits más altos que permite usar la ID para la partición de rango. Todos los mensajes heredan bits de tiempo mayor de TRX_ID . Esto asegura que todos los mensajes que pertenecen a la misma operación de negocios siempre caen en la misma partición.
Comencemos con la consulta codificada para seleccionar una página de los mensajes más recientes para un período de tiempo determinado con los límites de eliminación de partición aplicados:
select * from (select * from FR_MESSAGE_PART
where TS >= DATE ''2017-11-30'' and TS < DATE ''2017-12-02''
and ID >= 376894993815568384 and ID < 411234940974268416
order by TS DESC) where ROWNUM <= 40;
Pero, después de haber recopilado estadísticas de la tabla, el optimizador de Oracle todavía estima falsamente que la clasificación de dos particiones mensuales completas sería más rápida que un análisis de rango durante dos días según el índice local existente:
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 26200 | | 103K (1)| 00:00:05 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 803K| 501M| | 103K (1)| 00:00:05 | | |
|* 3 | SORT ORDER BY STOPKEY | | 803K| 70M| 92M| 103K (1)| 00:00:05 | | |
| 4 | PARTITION RANGE ITERATOR| | 803K| 70M| | 86382 (1)| 00:00:04 | 2 | 3 |
|* 5 | TABLE ACCESS FULL | FR_MESSAGE_PART | 803K| 70M| | 86382 (1)| 00:00:04 | 2 | 3 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=40)
3 - filter(ROWNUM<=40)
5 - filter("TS"<TIMESTAMP'' 2017-12-01 00:00:00'' AND "TS">=TIMESTAMP'' 2017-11-29 00:00:00'' AND
"ID">=376894993815568384)
El tiempo de ejecución real aparece en un orden de magnitud más largo que el estimado en el plan.
Así que tenemos que aplicar una sugerencia para forzar el uso del índice:
select * from (select /*+ FIRST_ROWS(40) INDEX(FR_MESSAGE_PART (TS)) */ * from FR_MESSAGE_PART
where TS >= DATE ''2017-11-30'' and TS < DATE ''2017-12-02''
and ID >= 376894993815568384 and ID < 411234940974268416
order by TS DESC) where ROWNUM <= 40;
Ahora el plan usa el índice, pero aún implica una clasificación lenta de dos particiones completas:
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 26200 | | 615K (1)| 00:00:25 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 803K| 501M| | 615K (1)| 00:00:25 | | |
|* 3 | SORT ORDER BY STOPKEY | | 803K| 70M| 92M| 615K (1)| 00:00:25 | | |
| 4 | PARTITION RANGE ITERATOR | | 803K| 70M| | 598K (1)| 00:00:24 | 2 | 3 |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FR_MESSAGE_PART | 803K| 70M| | 598K (1)| 00:00:24 | 2 | 3 |
|* 6 | INDEX RANGE SCAN | IX_FR_MESSAGE_PART_TS | 576K| | | 2269 (1)| 00:00:01 | 2 | 3 |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=40)
3 - filter(ROWNUM<=40)
5 - filter("ID">=376894993815568384)
6 - access("TS">=TIMESTAMP'' 2017-11-30 00:00:00'' AND "TS"<TIMESTAMP'' 2017-12-02 00:00:00'')
Después de algunos intentos de búsqueda de referencias de Oracle y Google, se encontró que también tenemos que especificar explícitamente la dirección descendente para la exploración de rango de índice con INDEX_DESC o INDEX_RS_DESC sugerencia:
select * from (select /*+ FIRST_ROWS(40) INDEX_RS_DESC(FR_MESSAGE_PART (TS)) */ * from FR_MESSAGE_PART
where TS >= DATE ''2017-11-30'' and TS < DATE ''2017-12-02''
and ID >= 376894993815568384 and ID < 411234940974268416
order by TS DESC) where ROWNUM <= 40;
Por fin, esto proporciona un plan rápido con COUNT STOPKEY por partición que escanea las particiones en orden descendente y ordena solo un máximo de 40 filas de cada partición:
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 26200 | | 615K (1)| 00:00:25 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 803K| 501M| | 615K (1)| 00:00:25 | | |
|* 3 | SORT ORDER BY STOPKEY | | 803K| 70M| 92M| 615K (1)| 00:00:25 | | |
| 4 | PARTITION RANGE ITERATOR | | 803K| 70M| | 598K (1)| 00:00:24 | 3 | 2 |
|* 5 | COUNT STOPKEY | | | | | | | | |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FR_MESSAGE_PART | 803K| 70M| | 598K (1)| 00:00:24 | 3 | 2 |
|* 7 | INDEX RANGE SCAN DESCENDING | IX_FR_MESSAGE_PART_TS | 576K| | | 2269 (1)| 00:00:01 | 3 | 2 |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=40)
3 - filter(ROWNUM<=40)
5 - filter(ROWNUM<=40)
6 - filter("ID">=376894993815568384)
7 - access("TS">=TIMESTAMP'' 2017-11-30 00:00:00'' AND "TS"<TIMESTAMP'' 2017-12-02 00:00:00'')
filter("TS">=TIMESTAMP'' 2017-11-30 00:00:00'' AND "TS"<TIMESTAMP'' 2017-12-02 00:00:00'')
Esto es muy rápido, pero el costo estimado del plan todavía es demasiado alto.
Hasta ahora tan bueno. Ahora intentemos hacer la consulta parametrizada para ser utilizada en nuestro marco de ORM personalizado:
select * from (select /*+ FIRST_ROWS(40) INDEX_RS_DESC(FR_MESSAGE_PART (TS)) */ * from FR_MESSAGE_PART
where TS >= :1 and TS < :2
and ID >= :3 and ID < :4
order by TS DESC) where ROWNUM <= 40;
Pero luego COUNT STOPKEY por partición desaparece del plan como se indica en la pregunta y se confirma en la otra respuesta:
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 26200 | 82349 (1)| 00:00:04 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 153 | 97K| 82349 (1)| 00:00:04 | | |
|* 3 | SORT ORDER BY STOPKEY | | 153 | 14076 | 82349 (1)| 00:00:04 | | |
|* 4 | FILTER | | | | | | | |
| 5 | PARTITION RANGE ITERATOR | | 153 | 14076 | 82348 (1)| 00:00:04 | KEY | KEY |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FR_MESSAGE_PART | 153 | 14076 | 82348 (1)| 00:00:04 | KEY | KEY |
|* 7 | INDEX RANGE SCAN DESCENDING | IX_FR_MESSAGE_PART_TS | 110K| | 450 (1)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=40)
3 - filter(ROWNUM<=40)
4 - filter(TO_NUMBER(:4)>TO_NUMBER(:3) AND TO_TIMESTAMP(:2)>TO_TIMESTAMP(:1))
6 - filter("ID">=TO_NUMBER(:3) AND "ID"<TO_NUMBER(:4))
7 - access("TS">=TO_TIMESTAMP(:1) AND "TS"<TO_TIMESTAMP(:2))
filter("TS">=TO_TIMESTAMP(:1) AND "TS"<TO_TIMESTAMP(:2))
Luego traté de retirarme a los límites de eliminación de partición alineados mensualmente pero aún así retener los límites de marca de tiempo parametrizados para minimizar el deterioro de la caché del plan .
select * from (select /*+ FIRST_ROWS(40) INDEX_RS_DESC(FR_MESSAGE_PART (TS)) */ * from FR_MESSAGE_PART
where TS >= :1 and TS < :2
and ID >= 376894993815568384 and ID < 411234940974268416
order by TS DESC) where ROWNUM <= 40;
Pero todavía tiene plan lento:
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 26200 | | 83512 (1)| 00:00:04 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 61238 | 38M| | 83512 (1)| 00:00:04 | | |
|* 3 | SORT ORDER BY STOPKEY | | 61238 | 5501K| 7216K| 83512 (1)| 00:00:04 | | |
|* 4 | FILTER | | | | | | | | |
| 5 | PARTITION RANGE ITERATOR | | 61238 | 5501K| | 82214 (1)| 00:00:04 | 3 | 2 |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FR_MESSAGE_PART | 61238 | 5501K| | 82214 (1)| 00:00:04 | 3 | 2 |
|* 7 | INDEX RANGE SCAN DESCENDING | IX_FR_MESSAGE_PART_TS | 79076 | | | 316 (1)| 00:00:01 | 3 | 2 |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=40)
3 - filter(ROWNUM<=40)
4 - filter(TO_TIMESTAMP(:2)>TO_TIMESTAMP(:1))
6 - filter("ID">=376894993815568384)
7 - access("TS">=TO_TIMESTAMP(:1) AND "TS"<TO_TIMESTAMP(:2))
filter("TS">=TO_TIMESTAMP(:1) AND "TS"<TO_TIMESTAMP(:2))
@ChrisSaxon, en su respuesta aquí, ha mencionado que el STOPKEY COUNT anidado STOPKEY COUNT tiene algo que ver con el filter(TO_TIMESTAMP(:2)>TO_TIMESTAMP(:1)) operación que valida que el límite superior sea realmente más grande que el inferior.
Teniendo esto en cuenta, intenté engañar al oprimizador transformando TS between :a and :b en equivalente :b between TS and TS + (:b - :a) . ¡Y esto funcionó!
Después de una investigación adicional de la causa raíz de este cambio, descubrí que simplemente reemplazar TS >= :1 and TS < :2 con TS + 0 >= :1 and TS < :2 ayuda a lograr un plan de ejecución óptimo.
select * from (select /*+ FIRST_ROWS(40) INDEX_RS_DESC(FR_MESSAGE_PART (TS)) */ * from FR_MESSAGE_PART
where TS + 0 >= :1 and TS < :2
and ID >= 376894993815568384 and ID < 411234940974268416
order by TS DESC) where ROWNUM <= 40;
El plan ahora cuenta con COUNT STOPKEY por partición y una noción de INTERNAL_FUNCTION("TS")+0 que impidió el filtro de comprobación de límites extra tóxicos, supongo.
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 26200 | | 10120 (1)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 61238 | 38M| | 10120 (1)| 00:00:01 | | |
|* 3 | SORT ORDER BY STOPKEY | | 61238 | 5501K| 7216K| 10120 (1)| 00:00:01 | | |
| 4 | PARTITION RANGE ITERATOR | | 61238 | 5501K| | 8822 (1)| 00:00:01 | 3 | 2 |
|* 5 | COUNT STOPKEY | | | | | | | | |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FR_MESSAGE_PART | 61238 | 5501K| | 8822 (1)| 00:00:01 | 3 | 2 |
|* 7 | INDEX RANGE SCAN DESCENDING | IX_FR_MESSAGE_PART_TS | 7908 | | | 631 (1)| 00:00:01 | 3 | 2 |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=40)
3 - filter(ROWNUM<=40)
5 - filter(ROWNUM<=40)
6 - filter("ID">=376894993815568384)
7 - access("TS"<TO_TIMESTAMP(:2))
filter(INTERNAL_FUNCTION("TS")+0>=:1 AND "TS"<TO_TIMESTAMP(:2))
Tuvimos que implementar los códigos de eliminación de partición + 0 y los límites de eliminación de partición mencionados en Oracle en nuestro marco ORM personalizado. Permite conservar el mismo rendimiento de paginación rápida después de cambiar a tablas particionadas con índices locales.
Pero deseo mucha paciencia y cordura a aquellos que se aventuran a hacer el mismo cambio sin el control completo del código de construcción de SQL.
Parece que Oracle tiene demasiados escollos cuando la partición y la paging se mezclan. Por ejemplo, encontramos que la nueva sintaxis de OFFSET ROWS / FETCH NEXT ROWS ONLY Oracle 12 es casi inutilizable con las tablas particionadas indexadas locales como la mayoría de las funciones analíticas de ventanas en las que se basa.
La consulta de trabajo más corta para recuperar una página detrás de la primera es
select * from (select * from (
select /*+ FIRST_ROWS(200) INDEX_RS_DESC(FR_MESSAGE_PART (TS)) */* from FR_MESSAGE_PART
where TS + 0 >= :1 and TS < :2
and ID >= 376894993815568384 and ID < 411234940974268416
order by TS DESC) where ROWNUM <= 200) offset 180 rows;
Este es un ejemplo del plan de ejecución real después de ejecutar dicha consulta:
SQL_ID c67mmq4wg49sx, child number 0
-------------------------------------
select * from (select * from (select /*+ FIRST_ROWS(200)
INDEX_RS_DESC("FR_MESSAGE_PART" ("TS")) GATHER_PLAN_STATISTICS */ "ID",
"MESSAGE_TYPE_ID", "TS", "REMOTE_ADDRESS", "TRX_ID",
"PROTOCOL_MESSAGE_ID", "MESSAGE_DATA_ID", "TEXT_OFFSET", "TEXT_SIZE",
"BODY_OFFSET", "BODY_SIZE", "INCOMING" from "FR_MESSAGE_PART" where
"TS" + 0 >= :1 and "TS" < :2 and "ID" >= 376894993815568384 and "ID" <
411234940974268416 order by "TS" DESC) where ROWNUM <= 200) offset 180
rows
Plan hash value: 2499404919
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 640K(100)| | | | 20 |00:00:00.01 | 322 | | | |
|* 1 | VIEW | | 1 | 200 | 130K| | 640K (1)| 00:00:26 | | | 20 |00:00:00.01 | 322 | | | |
| 2 | WINDOW NOSORT | | 1 | 200 | 127K| | 640K (1)| 00:00:26 | | | 200 |00:00:00.01 | 322 | 142K| 142K| |
| 3 | VIEW | | 1 | 200 | 127K| | 640K (1)| 00:00:26 | | | 200 |00:00:00.01 | 322 | | | |
|* 4 | COUNT STOPKEY | | 1 | | | | | | | | 200 |00:00:00.01 | 322 | | | |
| 5 | VIEW | | 1 | 780K| 487M| | 640K (1)| 00:00:26 | | | 200 |00:00:00.01 | 322 | | | |
|* 6 | SORT ORDER BY STOPKEY | | 1 | 780K| 68M| 89M| 640K (1)| 00:00:26 | | | 200 |00:00:00.01 | 322 | 29696 | 29696 |26624 (0)|
| 7 | PARTITION RANGE ITERATOR | | 1 | 780K| 68M| | 624K (1)| 00:00:25 | 3 | 2 | 400 |00:00:00.01 | 322 | | | |
|* 8 | COUNT STOPKEY | | 2 | | | | | | | | 400 |00:00:00.01 | 322 | | | |
|* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| FR_MESSAGE_PART | 2 | 780K| 68M| | 624K (1)| 00:00:25 | 3 | 2 | 400 |00:00:00.01 | 322 | | | |
|* 10 | INDEX RANGE SCAN DESCENDING | IX_FR_MESSAGE_PART_TS | 2 | 559K| | | 44368 (1)| 00:00:02 | 3 | 2 | 400 |00:00:00.01 | 8 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(''12.1.0.2'')
DB_VERSION(''12.1.0.2'')
OPT_PARAM(''optimizer_dynamic_sampling'' 0)
OPT_PARAM(''_optimizer_dsdir_usage_control'' 0)
FIRST_ROWS(200)
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$4")
NO_ACCESS(@"SEL$4" "from$_subquery$_004"@"SEL$4")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")
INDEX_RS_DESC(@"SEL$3" "FR_MESSAGE_PART"@"SEL$3" ("FR_MESSAGE_PART"."TS"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_004"."rowlimit_$$_rownumber">180)
4 - filter(ROWNUM<=200)
6 - filter(ROWNUM<=200)
8 - filter(ROWNUM<=200)
9 - filter("ID">=376894993815568384)
10 - access("TS"<:2)
filter((INTERNAL_FUNCTION("TS")+0>=:1 AND "TS"<:2))
Observe cuánto son mejores las filas y el tiempo reales obtenidos que las estimaciones del optimizador.
Actualizar
Tenga en cuenta que incluso este plan óptimo podría reducir la lentitud en el escaneo completo del índice local en caso de que el límite inferior de eliminación de la partición fuera demasiado bajo, ya que la partición más baja no contiene suficientes registros para coincidir con los filtros de consulta.
Las consultas "ENTRE" de Tuning de rleishman declaran :
El problema es que un índice solo puede escanear en una columna con un predicado de rango (<,>, LIKE, BETWEEN). Entonces, incluso si un índice contenía las columnas lower_bound y upper_bound, la exploración del índice devolverá todas las filas que coincidan con lower_bound <=: b, y luego filtrará las filas que no coincidan con upper_bound> =: b.
En el caso de que el valor buscado esté en algún lugar en el medio, el escaneo de rango devolverá la mitad de las filas en la tabla para encontrar una sola fila. En el peor de los casos donde las filas más buscadas están en la parte superior (valores más altos), el escaneo de índice procesará casi todas las filas de la tabla para cada búsqueda.
Esto significa que, desafortunadamente, Oracle no tiene en cuenta el límite inferior de un filtro de escaneo de rango hasta que alcanza la condición STOPKEY COUNT o escanea toda la partición.
Por lo tanto, tuvimos que limitar las heurísticas de límite de eliminación de partición inferiores al mismo mes en que cae el límite del período de marca de tiempo inferior. Esto se defiende contra las exploraciones de índice completo a expensas del riesgo de no mostrar algunos mensajes de transacciones retrasadas en la lista. Pero esto puede resolverse fácilmente extendiendo el período de tiempo proporcionado si es necesario.
También he intentado aplicar el mismo + 0truco para forzar un plan óptimo con el enlace de límites de eliminación de partición dinámica:
select * from (select /*+ FIRST_ROWS(40) INDEX_RS_DESC(FR_MESSAGE_PART (TS)) */ * from FR_MESSAGE_PART
where TS+0 >= :1 and TS < :2
and ID >= :3 and ID+0 < :4
order by TS DESC) where ROWNUM <= 40;
Entonces, el plan aún se mantiene STOPKEY COUNTpor partición, pero la eliminación de la partición se pierde para el límite superior, como se puede observar en la Pstartcolumna de la tabla del plan:
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 26200 | 9083 (1)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 153 | 97K| 9083 (1)| 00:00:01 | | |
|* 3 | SORT ORDER BY STOPKEY | | 153 | 14076 | 9083 (1)| 00:00:01 | | |
| 4 | PARTITION RANGE ITERATOR | | 153 | 14076 | 9082 (1)| 00:00:01 | 10 | KEY |
|* 5 | COUNT STOPKEY | | | | | | | |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FR_MESSAGE_PART | 153 | 14076 | 9082 (1)| 00:00:01 | 10 | KEY |
|* 7 | INDEX RANGE SCAN DESCENDING | IX_FR_MESSAGE_PART_TS | 11023 | | 891 (1)| 00:00:01 | 10 | KEY |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=40)
3 - filter(ROWNUM<=40)
5 - filter(ROWNUM<=40)
6 - filter("ID">=TO_NUMBER(:3) AND "ID"+0<TO_NUMBER(:4))
7 - access("TS"<TO_TIMESTAMP(:2))
filter(INTERNAL_FUNCTION("TS")+0>=:1 AND "TS"<TO_TIMESTAMP(:2))
Puedo reproducir sus hallazgos en 11.2.0.3. Aquí está mi caso de prueba:
SQL> -- Table with 100 partitions of 100 rows
SQL> CREATE TABLE out_sms
2 PARTITION BY RANGE (trx_id)
3 INTERVAL (100) (PARTITION p0 VALUES LESS THAN (0))
4 AS
5 SELECT ROWNUM trx_id,
6 trunc(SYSDATE) + MOD(ROWNUM, 50) create_ts
7 FROM dual CONNECT BY LEVEL <= 10000;
Table created
SQL> CREATE INDEX OUT_SMS_IDX ON out_sms (create_ts desc, trx_id desc) LOCAL;
Index created
[static plan]
SELECT rd
FROM (SELECT /*+ INDEX(OUT_SMS OUT_SMS_IDX) */
rowid rd
FROM out_sms
WHERE create_ts BETWEEN systimestamp AND systimestamp + 10
AND trx_id BETWEEN 1 AND 500
ORDER BY create_ts DESC, trx_id DESC)
WHERE rownum <= 20;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 1 | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | | |
| 4 | PARTITION RANGE ITERATOR| | 1 | 2 | 7 |
|* 5 | COUNT STOPKEY | | | | |
|* 6 | INDEX RANGE SCAN | OUT_SMS_IDX | 1 | 2 | 7 |
---------------------------------------------------------------------------
[dynamic]
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 1 | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | | |
|* 4 | FILTER | | | | |
| 5 | PARTITION RANGE ITERATOR| | 1 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | OUT_SMS_IDX | 1 | KEY | KEY |
----------------------------------------------------------------------------
Como en su ejemplo, el predicado ROWNUM se inserta dentro del escaneo de rango de índice de partición en el primer caso, no en el segundo. Cuando se usan variables estáticas, el plan muestra que Oracle solo obtiene 20 filas por partición, mientras que al usar variables dinámicas, Oracle buscará todas las filas que satisfagan la cláusula WHERE en cada partición. No pude encontrar una configuración o una configuración de estadísticas donde el predicado pudiera ser empujado cuando se usan variables de enlace.
Esperaba que pudieras usar filtros dinámicos con límites estáticos más amplios para jugar con el sistema, pero parece que el predicado ROWNUM no se usa dentro de particiones individuales tan pronto como hay variables dinámicas presentes:
SELECT rd
FROM (SELECT /*+ INDEX(OUT_SMS OUT_SMS_IDX) */
rowid rd
FROM out_sms
WHERE nvl(create_ts+:5, sysdate) BETWEEN :1 AND :2
AND nvl(trx_id+:6, 0) BETWEEN :3 AND :4
AND trx_id BETWEEN 1 AND 500
AND create_ts BETWEEN systimestamp AND systimestamp + 10
ORDER BY create_ts DESC, trx_id DESC)
WHERE rownum <= 20
Plan hash value: 2740263591
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Pstart| Pstop |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 1 | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | | |
|* 4 | FILTER | | | | |
| 5 | PARTITION RANGE ITERATOR| | 1 | 2 | 7 |
|* 6 | INDEX RANGE SCAN | OUT_SMS_IDX | 1 | 2 | 7 |
----------------------------------------------------------------------------
Si esta consulta es importante y su rendimiento es crítico, podría transformar el índice en un índice global. Aumentará el mantenimiento de la partición, pero la mayoría de las operaciones de partición se pueden usar en línea con versiones recientes de Oracle. Un índice global funcionará como con la tabla estándar no particionada en este caso:
SQL> drop index out_sms_idx;
Index dropped
SQL> CREATE INDEX OUT_SMS_IDX ON out_sms (create_ts DESC, trx_id desc);
Index created
SELECT rd
FROM (SELECT
rowid rd
FROM out_sms
WHERE create_ts BETWEEN :1 AND :2
AND trx_id BETWEEN :3 AND :4
ORDER BY create_ts DESC, trx_id DESC)
WHERE rownum <= 20
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)|
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 1 | 12 | 2 (0)|
|* 3 | FILTER | | | | |
|* 4 | INDEX RANGE SCAN| OUT_SMS_IDX | 1 | 34 | 2 (0)|
------------------------------------------------------------------------
¿Es SQL dinámico una opción? De esa manera, podría "inyectar" los valores de los filtros TRX_ID y CREATE_TS eliminando el uso de variables de enlace. Tal vez entonces el plan generado incluiría COUNT STOPKEY.
Con SQL dinámico quise que usted construyera el SQL dinámicamente y luego lo invocara con EXECUTE IMMEDIATE u OPEN. Al utilizar esto, puede utilizar sus filtros directamente sin variables de enlace. Ejemplo:
v_sql VARCHAR2(1000) :=
''select rd from (
select /*+ INDEX(OUT_SMS OUT_SMS_CREATE_TS_TRX_ID_IX) */ rowid rd
from OUT_SMS
where TRX_ID between '' || v_trx_id_min || '' and '' || v_trx_id_maxb || ''
and CREATE_TS between '' || v_create_ts_min|| '' and '' || v_create_ts_max || ''
order by CREATE_TS DESC, TRX_ID DESC
) where rownum <= 20'';
Luego invocarlo usando:
EXECUTE IMMEDIATE v_sql;
o incluso:
OPEN cursor_out FOR v_sql;