O Destruidor WHERE ROWNUM = :valor;

O Destruidor WHERE ROWNUM = :valor;

2 Comments Edit

Boa tarde amigos DBAs.

Hoje venho falar sobre um dos maiores terrores para Performance de um Banco Oracle:

Uma query com WHERE rownum = :valor;

Bom, isso pode parecer algo normal à primeira vista. Claro, uma query onde você quer que seja exibida somente a enésima linha do resultado. Nada muito trabalhoso.

Okay. Agora eu lhes pergunto. Imagine Uma tabela com Milhões de linhas, que é consultada (Join) com uma outra tabela com milhões de linhas, o resultado desse join deve ser ordenado e SOMENTE a primeira linha deve ser capturada e utilizada como subquery para uma query Pai, que é a responsável por trazer os registros que o cliente precisa.



:)
Complicado de entender? Okay.. aqui vai um caso real.
Obs: O nome de todas as tabelas e colunas foram alterados para preservar o cliente.


Em um determinado servidor, identifiquei que de tempos em tempos (Aproximadamente de 15 em 15 minutos, a CPU era utilizada em 98% por um único processo do SO, pertencente ao oracle).

*Linhas do utilitário TOP exibindo os processos que mais consumiam a cpu do servidor:

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
27261 oracle    20   0 5369m 391m 385m R 99.9  0.6   2:46.64 oracle
27279 oracle    18   0 5369m 456m 449m R 98.9  0.7   3:43.36 oracle




Com base nos PIDs encontrados, podemos pesquisar ao que se referem.
Obs: O PID informado pelo SO é correspondente ao SPID da view v$process do oracle.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
column THREADID for a8
column USERNAME for a15
select vp.spid THREADID,
       s.USERNAME, 
       s.STATUS, 
       t.PIECE, 
       t.SQL_TEXT 
  from v$session s, 
       v$sqltext t ,
       v$process vp
 where s.SQL_ADDRESS = t.ADDRESS
     and vp.addr = s.paddr(+)
  and vp.spid in (27261, 27279)
 order by s.sid, t.PIECE;

Aqui o resultado obtido:
(Query exibindo as atividades relacionadas ao PID)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
THREADID USERNAME STATUS   PIECE SQL_TEXT
-------- -------- -------- ----- ----------------------------------------------------------------
27279    USRWEB   ACTIVE       0 SELECT main.ID_CANCEL_REASON as IDT1_0_, main.TX_CANCEL_REASON
27279    USRWEB   ACTIVE       1 as TX2_43_0_ FROM cancel_request_reason main, (
27279    USRWEB   ACTIVE       2    SELECT cpd.id_cancel_req_reason, cpd.id_cancel_req
27279    USRWEB   ACTIVE       3     FROM cancel_request_detail cpd, cancel_request
27279    USRWEB   ACTIVE       4  cp, cancel_request_detail_dept cdct   WHERE   (cpd.id_cancel_req
27279    USRWEB   ACTIVE       5 = cp.id_cancel_req OR cdct.id_cancel_req
27279    USRWEB   ACTIVE       6 = cp.id_cancel_req) AND            cp.id_online_request
27279    USRWEB   ACTIVE       7  = :"SYS_B_0" AND            rownum = :"SYS_B_1") ma
27279    USRWEB   ACTIVE       8 in_1_ WHERE main.ID_CANCEL_REASON = main_1_.
27279    USRWEB   ACTIVE       9 id_cancel_req_reason
27261    USRWEB   ACTIVE       0 SELECT main.ID_CANCEL_REASON as IDT1_0_, main.TX_CANCEL_REASON
27261    USRWEB   ACTIVE       1 as TX2_43_0_ FROM cancel_request_reason main, (
27261    USRWEB   ACTIVE       2    SELECT cpd.id_cancel_req_reason, cpd.id_cancel_req
27261    USRWEB   ACTIVE       3     FROM cancel_request_detail cpd, cancel_request
27261    USRWEB   ACTIVE       4  cp, cancel_request_detail_dept cdct   WHERE   (cpd.id_cancel_req
27261    USRWEB   ACTIVE       5 = cp.id_cancel_req OR cdct.id_cancel_req
27261    USRWEB   ACTIVE       6 = cp.id_cancel_req) AND            cp.id_online_request
27261    USRWEB   ACTIVE       7  = :"SYS_B_0" AND            rownum = :"SYS_B_1") ma
27261    USRWEB   ACTIVE       8 in_1_ WHERE main.ID_CANCEL_REASON = main_1_.
27261    USRWEB   ACTIVE       9 id_cancel_req_reason

Formatei a query para que ficasse legível:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT main.ID_CANCEL_REASON as IDT1_0_, 
        main.TX_CANCEL_REASON as TX2_43_0_ 
  FROM USRWEB.cancel_request_reason main, 
       (SELECT cpd.id_cancel_req_reason, 
                cpd.id_cancel_req
            FROM USRWEB.cancel_request_detail cpd, 
                 USRWEB.cancel_request cp, 
                   USRWEB.cancel_request_detail_dept cdct   
           WHERE (cpd.id_cancel_req = cp.id_cancel_req OR cdct.id_cancel_req = cp.id_cancel_req)
             AND cp.id_online_request = :"SYS_B_0" 
             AND rownum = :"SYS_B_1") main_1_ 
 WHERE main.ID_CANCEL_REASON = main_1_.id_cancel_req_reason;

Como você pode observar, durante a execução desta query, o consumo de CPU chega em 99% apenas para o processo em execução.

Executei manualmente essa query, inclusive passando os valores como bind variables e constatei o comportamento bem como o tempo levado para resposta da mesma.

Isso ocorre, basicamente, devido à condição AND rownum = :"SYS_B_1", que faz com que o Oracle primeiro resolva toda a query, ordene o resultado e te retorne o valor da linha especificada pela bind variable SYS_B_1.

Também, ao utilizarmos rownum em uma condição where, o ORACLE força a utilização do plano de ação FIRST_ROWS ao invés de ALL_ROWS, o que diminui drasticamente a performance da query e elimina a execução em paralelismo.

Após algum tempo analisando essa query, consegui chegar a um tempo de execução de 6 segundos ao invés dos 7 minutos que a query leva originalmente.

Abaixo segue a execução da query como ela está construída, e com a modificação proposta.

(Query como foi escrita originalmente)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
SQL> SELECT main.ID_CANCEL_REASON as IDT1_0_,
            main.TX_CANCEL_REASON as TX2_43_0_
       FROM USRWEB.cancel_request_reason main,
            (SELECT cpd.id_cancel_req_reason,
                    cpd.id_cancel_req
               FROM USRWEB.cancel_request_detail cpd,
                    USRWEB.cancel_request cp,
                    USRWEB.cancel_request_detail_dept cdct
              WHERE (cpd.id_cancel_req = cp.id_cancel_req OR cdct.id_cancel_req = cp.id_cancel_req)
                AND cp.id_online_request = :"SYS_B_0"
                AND rownum = :"SYS_B_1") main_1_
      WHERE main.ID_CANCEL_REASON = main_1_.id_cancel_req_reason;


   IDT1_0_ TX2_43_0_
---------- ----------------------------------------------------------------------------------------------------
        14 Incorrect Credit

Elapsed: 00:06:25.01

(Query re-escrita para termos ganho de performance)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
SQL> SELECT main.ID_CANCEL_REASON as IDT1_0_,
            main.TX_CANCEL_REASON as TX2_43_0_
       FROM USRWEB.cancel_request_reason main,
            (select * from
            (SELECT cpd.id_cancel_req_reason,
                    cpd.id_cancel_req,
                    ROW_NUMBER() OVER (ORDER BY id_online_request) as rn
               FROM USRWEB.cancel_request_detail cpd,
                    USRWEB.cancel_request cp,
                    USRWEB.cancel_request_detail_dept cdct
              WHERE (cpd.id_cancel_req = cp.id_cancel_req OR cdct.id_cancel_req = cp.id_cancel_req)
                AND cp.id_online_request = :"SYS_B_0")
      where rn = :"SYS_B_1") main_1_
      WHERE main.ID_CANCEL_REASON = main_1_.id_cancel_req_reason;

   IDT1_0_ TX2_43_0_
---------- ----------------------------------------------------------------------------------------------------
        14 Incorrect Credit

Elapsed: 00:00:06.04


Como pode notar, após a modificação da query, a mesma caiu de 6.25 Minutos para 6.04 Segundos.

Executei novamente a query original algumas vezes em seguida, bem como a query modificada para eliminarmos qualquer possibilidade de o resultado da query modificada estar em cache.
Todas as vezes que rodei a query original, ela levou em torno de 6 a 7 minutos para concluir, e a query modificada em torno de 5 a 6 segundos.


A modificação proposta está no seguinte:

Ao invés de utilizarmos o tradicional rownum como filtro para a subquery, é extremamente mais vantajoso usarmos a Função ROW_NUMBER(), utilizando como critério de ordem a coluna id_online_request, que é a chave primária da tabela.

Para isso, dividi a subquery em uma segunda subquery para que pudéssemos ter o valor retornado pela função ROW_NUMBER() e utilizei este valor como cláusula where para a mesma.


Essencialmente, a lógica da query é a mesma, porém, quando olhamos para o plano de execução da mesma, notamos a diferença.

No plano de execução para a query com a modificação, notamos que o oracle acrescenta uma ação WINDOW NOSORT STOPKEY que faz com que a execução da mesma seja finalizada assim que um valor é encontrado para o Oracle, eliminando todo o processamento restante oriundo da subquery em sí, tornando a execução MUITO MAIS RÁPIDA.

Este é o plano de execução para a query MODIFICADA para utilizar ROW_NUMBER()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 3866720052

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                              |    36M|  1691M|  7266   (9)| 00:01:28 |
|*  1 |  HASH JOIN                        |                              |    36M|  1691M|  7266   (9)| 00:01:28 |
|   2 |   TABLE ACCESS FULL               | cancel_request_reason        |     8 |   184 |     3   (0)| 00:00:01 |
|*  3 |   VIEW                            |                              |    36M|   897M|  7021   (5)| 00:01:25 |
|*  4 |    WINDOW NOSORT STOPKEY          |                              |    36M|   828M|  7021   (5)| 00:01:25 |
|   5 |     CONCATENATION                 |                              |       |       |            |          |
|*  6 |      HASH JOIN                    |                              |    36M|   828M|  7000   (5)| 00:01:25 |
|   7 |       INDEX FAST FULL SCAN        | IDX_CARD_DEPT_CAN_DET_001    |   954 |  4770 |     2   (0)| 00:00:01 |
|   8 |       MERGE JOIN CARTESIAN        |                              |  5727K|   103M|  6756   (2)| 00:01:22 |
|   9 |        TABLE ACCESS BY INDEX ROWID| cancel_request               |     1 |    11 |     5   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN          | CANC_REQUEST_ORA_001         |     1 |       |     3   (0)| 00:00:01 |
|  11 |        BUFFER SORT                |                              |  4789K|    36M|  6751   (2)| 00:01:22 |
|  12 |         TABLE ACCESS FULL         | cancel_request_detail        |  4789K|    36M|  6751   (2)| 00:01:22 |
|  13 |      NESTED LOOPS                 |                              |   868 | 20832 |    20   (0)| 00:00:01 |
|  14 |       NESTED LOOPS                |                              |    18 |   342 |     8   (0)| 00:00:01 |
|  15 |        TABLE ACCESS BY INDEX ROWID| cancel_request               |     1 |    11 |     5   (0)| 00:00:01 |
|* 16 |         INDEX RANGE SCAN          | CANC_REQUEST_ORA_001         |     1 |       |     3   (0)| 00:00:01 |
|  17 |        TABLE ACCESS BY INDEX ROWID| cancel_request_detail        |    15 |   120 |     3   (0)| 00:00:01 |
|* 18 |         INDEX RANGE SCAN          | IDX_CANC_REQ_DET_003         |    16 |       |     2   (0)| 00:00:01 |
|* 19 |       INDEX FAST FULL SCAN        | IDX_CARD_DEPT_CAN_DET_001    |    48 |   240 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MAIN"."ID_CANCEL_REASON"="from$_subquery$_003"."id_cancel_req_reason")
   3 - filter("RN"=TO_NUMBER(:SYS_B_1))
   4 - filter(ROW_NUMBER() OVER ( ORDER BY "id_online_request")<=TO_NUMBER(:SYS_B_1))
   6 - access("CDCT"."id_cancel_req"="CP"."id_cancel_req")
  10 - access("CP"."id_online_request"=TO_NUMBER(:SYS_B_0))
  16 - access("CP"."id_online_request"=TO_NUMBER(:SYS_B_0))
  18 - access("CPD"."id_cancel_req"="CP"."id_cancel_req")
  19 - filter(LNNVL("CDCT"."id_cancel_req"="CP"."id_cancel_req"))

38 rows selected.

Este é o plano de execução para a query Original:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 3966394659

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |    36M|   897M|  7265   (9)| 00:01:28 |
|*  1 |  HASH JOIN                         |                              |    36M|   897M|  7265   (9)| 00:01:28 |
|   2 |   TABLE ACCESS FULL                | cancel_request_reason        |     8 |   184 |     3   (0)| 00:00:01 |
|   3 |   VIEW                             |                              |    36M|   103M|  7020   (5)| 00:01:25 |
|   4 |    COUNT                           |                              |       |       |            |          |
|   5 |     CONCATENATION                  |                              |       |       |            |          |
|*  6 |      FILTER                        |                              |       |       |            |          |
|*  7 |       HASH JOIN                    |                              |    36M|   828M|  7000   (5)| 00:01:25 |
|   8 |        INDEX FAST FULL SCAN        | IDX_CARD_DEPT_CAN_DET_001    |   954 |  4770 |     2   (0)| 00:00:01 |
|   9 |        MERGE JOIN CARTESIAN        |                              |  5727K|   103M|  6756   (2)| 00:01:22 |
|  10 |         TABLE ACCESS BY INDEX ROWID| cancel_request               |     1 |    11 |     5   (0)| 00:00:01 |
|* 11 |          INDEX RANGE SCAN          | CANC_REQUEST_ORA_001         |     1 |       |     3   (0)| 00:00:01 |
|  12 |         BUFFER SORT                |                              |  4789K|    36M|  6751   (2)| 00:01:22 |
|  13 |          TABLE ACCESS FULL         | cancel_request_detail        |  4789K|    36M|  6751   (2)| 00:01:22 |
|* 14 |      FILTER                        |                              |       |       |            |          |
|  15 |       NESTED LOOPS                 |                              |   868 | 20832 |    20   (0)| 00:00:01 |
|  16 |        NESTED LOOPS                |                              |    18 |   342 |     8   (0)| 00:00:01 |
|  17 |         TABLE ACCESS BY INDEX ROWID| cancel_request               |     1 |    11 |     5   (0)| 00:00:01 |
|* 18 |          INDEX RANGE SCAN          | CANC_REQUEST_ORA_001         |     1 |       |     3   (0)| 00:00:01 |
|  19 |         TABLE ACCESS BY INDEX ROWID| cancel_request_detail        |    15 |   120 |     3   (0)| 00:00:01 |
|* 20 |          INDEX RANGE SCAN          | IDX_CANC_REQ_DET_003         |    16 |       |     2   (0)| 00:00:01 |
|* 21 |        INDEX FAST FULL SCAN        | IDX_CARD_DEPT_CAN_DET_001    |    48 |   240 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MAIN"."ID_CANCEL_REASON"="MAIN_1_"."id_cancel_req_reason")
   6 - filter(ROWNUM=TO_NUMBER(:SYS_B_1))
   7 - access("CDCT"."id_cancel_req"="CP"."id_cancel_req")
  11 - access("CP"."id_online_request"=TO_NUMBER(:SYS_B_0))
  14 - filter(ROWNUM=TO_NUMBER(:SYS_B_1))
 18 - access("CP"."id_online_request"=TO_NUMBER(:SYS_B_0))
  20 - access("CPD"."id_cancel_req"="CP"."id_cancel_req")
  21 - filter(LNNVL("CDCT"."id_cancel_req"="CP"."id_cancel_req"))

40 rows selected.

Espero que tenham gostado.

Até o próximo post.

Share this:

SOBRE O AUTOR

Com uma década de experiẽncia em produtos Oracle como DBA e também Developer, pretendo, com esse blog, trocar experiências com você, leitor.

2 comentários:

  1. Mufalani - OCM27 de março de 2012 às 12:39

    Ótimo post... Uma coisa que podemos notar é que em uma query top-N o plano de execução fica "camuflado"... Esperimente na sua primeira query pegar o plano de execução da consulta como um todo e ver como ele é bem menor do que a consulta interna.

    Abs,
    Mufalani

    ResponderExcluir
    Respostas
    1. Unknown27 de março de 2012 às 12:44

      Olá Rodrigo. Muito bem colocado.
      Obrigado pelo comentário... Continue visitando =)

      Excluir

Assinar: Postar comentários ( Atom )