DBMS_XPLAN: você sabe usar esse extraordinário pacote?

novembro 8, 2015 V. J.A. Tuning Tools

O pacote DBMS_XPLAN fornece uma maneira fácil para exibir o plano de execução em vários formatos pré-definidos, essa flexibilidade e o nível de detalhes estatísticos que o pacote oferece o torna uma ferramenta imprescindível num processo de tuning de uma instrução SQL.

Você pode usar o pacote para exibir uma estimativa de plano a partir do comando “Explain Plan”, ou para exibir um plano real que foi executado pelo otimizador e as informações foram armazenadas em “SQL Cursors”, ou ainda para exibir um plano que foi executado a alguns dias e as informações estão armazenadas no Automatic Workload Repository (AWR).

Visão geral

O pacote DBMS_XPLAN fornece cinco funções de tabela:

DISPLAY – para formatar e exibir o conteúdo de uma “plan table”.
DISPLAY_AWR – para formatar e exibir o conteúdo do plano de execução de uma instrução SQL armazenado no AWR.
DISPLAY_CURSOR – para formatar e exibir o conteúdo do plano de execução de qualquer cursor na memória da instância do banco.
DISPLAY_SQL_PLAN_BASELINE – para apresentar um ou mais planos de execução para a instrução SQL identificado pelo “SQL handle”.
DISPLAY_SQLSET – para formatar e exibir o conteúdo do plano de execução de instruções armazenadas em um “SQL tuning set”.

Neste post vamos explorar em mais detalhes a função DISPLAY_CURSOR, pois ela permite a visualização do plano de execução de uma instrução SQL que realmente foi utilizada pelo otimizador. Algumas ferramentas utilizadas por profissionais de Tuning podem em algumas situações exibir um plano diferente do plano de execução utilizado pelo otimizador.

Função DISPLAY_CURSOR

A DBMS_XPLAN.DISPLAY_CURSOR pode ser usada para obter mais informações estatísticas sobre o consumo real de recursos a nível de operação do plano de execução.

Para isso precisamos utilizar o “hint” GATHER_PLAN_STATISTICS na instrução SQL (a partir do 10g em diante), ou alterar o parâmetro STATISTICS_LEVEL para ALL a nível de sessão (não é recomendado utilizar esse parâmetro a nível de sistema pois ele pode causar uma sobrecarga na base).

Assim que uma instrução SQL é executada (com êxito, cancelada ou com erro) o “Child Cursor” é atualizado com os dados estatísticos para cada operação do plano de execução utilizado pelo otimizador e essas informações podem ser acessadas via V$SQL_PLAN_STATISTICS e V$SQL_PLAN_STATISTICS_ALL.

Sintaxe

1
2
3
4

DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN NUMBER DEFAULT 0,
format IN VARCHAR2 DEFAULT 'TYPICAL');

Parâmetros

SQL_ID:

Especifique o SQL_ID da instrução SQL no “cursor cache”. Você pode recuperar esse código consultando a coluna SQL_ID nas visões V$SQL ou V$SQLAREA. Caso este parâmetro não seja informado (NULL) será exibido o plano de execução do ultimo cursor executado pela sessão.

CURSOR_CHILD_NO:

Especifique o “Child Number” do Cursor a ser exibido. Se esse parâmetro não for fornecido, o plano de execução de todos os cursores correspondentes ao parâmetro sql_id informado serão exibidos. O CURSOR_CHILD_NO só pode ser especificado se o sql_id foi informado.

FORMAT:

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

https://dbtimewizard.com.br/mentoria

Este parâmetro controla o nível de detalhe do plano que será exibido e na versão 11g podemos informar 5 valores:

Basic: Exibe o minimo de informações sobre o plano de execução: O ID da operação, o nome da operação e o nome do objeto que será tratado pela operação. [veja exemplo…]

Typical: Exibe as informações mais relevantes do plano de execução, se o parâmetro “Format” for omitido (null) esse será o valor considerado. [veja exemplo…]

Serial: Exibe quase as mesmas informações que o formato “Typical” com exceção das informações das operações de paralelismo que serão omitidas mesmo que o plano utilize o paralelismo na sua execução. [veja exemplo…]

Advanced: Exibe as mesmas informações que o formato “Typical” e inclui algumas sessões de informações não estatísticas sobre o plano de execução. Esse formato não esta documentado nos manuais da Oracle. [veja exemplo…]

Aos formatos acima podemos adicionar mais informações estatísticas, para tanto devemos adicionar uma das três palavras chaves abaixo:

MEMSTATS: Exibe estatística referente a estimativa da quantidade de espaço a ser utilizado na PGA para operações de SORT, HASH JOIN, BITMAP MERGE ou BITMAP CREATE. Essas informações só serão exibidas se as operações listadas anteriormente forem utilizadas no plano de execução. [veja exemplo…]

IOSTATS: Exibe estatísticas referentes as operações de “IO” executadas para um determinado plano de execução. [veja exemplo…]

ALLSTATS: Exibe todas as informações estatísticas fornecidas por MEMSTATS e IOSTATS. [veja exemplo…]

Alem dessas palavras chave que adicionam mais informações estatísticas ao plano, podemos adicionar ao parâmetro FORMAT mais duas palavras chaves:

LAST: Normalmente os valores estatísticos informados no plano de execução são referentes a todos os cursores daquele plano (Lembrando que a existência de cada cursor na memória tem um tempo limitado que depende da quantidade de transações e do tamanho da área de memória de cada base), se for informada a palavra chave LAST serão exibidos valores estatísticos somente da ultima execução. [veja exemplo…]

ALL: Essa palavra chave permite que sejam exibidas sessões adicionais (Query Block Name e Column Projection) de informações não estatísticas que aparecem logo após o plano de execução. [veja exemplo…]

Cada uma das palavras chave do parâmetro FORMAT apresentam um determinado numero de sessões adicionais listadas logo após o plano de execução, o quadro abaixo mostra quais são as sessões listadas para cada uma das palavras chaves:

Também podemos incluir ou eliminar as informações que serão exibidas no plano de execução gerado pelo pacote DBMS_XPLAN, veja alguns exemplos abaixo:

As palavras chave “BASIC MEMSTATS” não exibem a coluna “COST” no plano de execução se quisermos adicionar esta informação basta adicionar essa palavra chave “BASIC MEMSTATS COST”. [veja exemplo…]

Outra possibilidade seria eliminar informações do plano de execução, podemos por exemplo eliminar as colunas “E-ROWS”, “COST” e “BYTES” do plano de execução informando o parâmetro “FORMAT” da seguinte forma: “TYPICAL IOSTATS -ROWS -COST -BYTES”. [veja exemplo…]

As sessões de informação não estatísticas também podem ser controladas e podemos adicionar ou excluir sessões, por exemplo podemos incluir a sessão “Query Block Name” incluindo a palavra chave “ALIAS”. [veja exemplo…]

Ou ainda podemos excluir sessões de informações não estatísticas como a sessão “Projection” incluindo a palavra chave “-PROJECTION”. [veja exemplo…]

Exemplo “Format Basic”:

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

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'BASIC'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | HASH GROUP BY | |
| 3 | HASH JOIN | |
| 4 | VIEW | |
| 5 | HASH GROUP BY | |
| 6 | HASH JOIN | |
| 7 | PARTITION RANGE ALL| |
| 8 | TABLE ACCESS FULL | SALES |
| 9 | PARTITION RANGE ALL| |
| 10 | TABLE ACCESS FULL | SALES |
| 11 | TABLE ACCESS FULL | CUSTOMERS |
-----------------------------------------------

Exemplo “Format Typical”:

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

Exemplo “Format Serial”:

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

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'SERIAL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2gmbuzy8dzvv2, child number 0
-------------------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 7620 (100)| | | |
| 1 | SORT ORDER BY | | 145 | 6815 | | 7620 (57)| 00:01:32 | | |
| 2 | HASH GROUP BY | | 145 | 6815 | | 7620 (57)| 00:01:32 | | |
|* 3 | HASH JOIN | | 7059 | 323K| | 7618 (57)| 00:01:32 | | |
| 4 | VIEW | | 7059 | 213K| | 7213 (60)| 00:01:27 | | |
| 5 | HASH GROUP BY | | 7059 | 137K| | 7213 (60)| 00:01:27 | | |
|* 6 | HASH JOIN | | 119M| 2281M| 19M| 3292 (11)| 00:00:40 | | |
| 7 | PARTITION RANGE ALL| | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 8 | TABLE ACCESS FULL | SALES | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 9 | PARTITION RANGE ALL| | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 10 | TABLE ACCESS FULL | SALES | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 867K| | 405 (1)| 00:00:05 | | |
---------------------------------------------------------------------------------------------------------------

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

Exemplo “Format Advanced”:

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'ADVANCED'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2gmbuzy8dzvv2, child number 0
-------------------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 7620 (100)| | | |
| 1 | SORT ORDER BY | | 145 | 6815 | | 7620 (57)| 00:01:32 | | |
| 2 | HASH GROUP BY | | 145 | 6815 | | 7620 (57)| 00:01:32 | | |
|* 3 | HASH JOIN | | 7059 | 323K| | 7618 (57)| 00:01:32 | | |
| 4 | VIEW | | 7059 | 213K| | 7213 (60)| 00:01:27 | | |
| 5 | HASH GROUP BY | | 7059 | 137K| | 7213 (60)| 00:01:27 | | |
|* 6 | HASH JOIN | | 119M| 2281M| 19M| 3292 (11)| 00:00:40 | | |
| 7 | PARTITION RANGE ALL| | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 8 | TABLE ACCESS FULL | SALES | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 9 | PARTITION RANGE ALL| | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 10 | TABLE ACCESS FULL | SALES | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 867K| | 405 (1)| 00:00:05 | | |
---------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
4 - SEL$73523A42 / S@SEL$1
5 - SEL$73523A42
8 - SEL$73523A42 / A@SEL$2
10 - SEL$73523A42 / B@SEL$2
11 - SEL$1 / C@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$73523A42")
ELIMINATE_OBY(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
NO_ACCESS(@"SEL$1" "S"@"SEL$1")
FULL(@"SEL$1" "C"@"SEL$1")
LEADING(@"SEL$1" "S"@"SEL$1" "C"@"SEL$1")
USE_HASH(@"SEL$1" "C"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$1")
FULL(@"SEL$73523A42" "A"@"SEL$2")
FULL(@"SEL$73523A42" "B"@"SEL$2")
LEADING(@"SEL$73523A42" "A"@"SEL$2" "B"@"SEL$2")
USE_HASH(@"SEL$73523A42" "B"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$73523A42")
END_OUTLINE_DATA
*/

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1) SUM("TOT_SOLD_A")+SUM("TOT_SOLD_B")[22], "CUST_STATE_PROVINCE"[VARCHAR2,40]
2 - "CUST_STATE_PROVINCE"[VARCHAR2,40], SUM("TOT_SOLD_B")[22], SUM("TOT_SOLD_A")[22]
3 - (#keys=1) "TOT_SOLD_B"[NUMBER,22], "TOT_SOLD_A"[NUMBER,22], "CUST_STATE_PROVINCE"[VARCHAR2,40]

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 - "S"."CUST_ID"[NUMBER,22], "TOT_SOLD_A"[NUMBER,22], "TOT_SOLD_B"[NUMBER,22]
5 - "A"."CUST_ID"[NUMBER,22], SUM("B"."AMOUNT_SOLD")[22], SUM("A"."AMOUNT_SOLD")[22]
6 - (#keys=1) "A"."CUST_ID"[NUMBER,22], "A"."AMOUNT_SOLD"[NUMBER,22], "B"."AMOUNT_SOLD"[NUMBER,22]
7 - "A"."CUST_ID"[NUMBER,22], "A"."AMOUNT_SOLD"[NUMBER,22]
8 - "A"."CUST_ID"[NUMBER,22], "A"."AMOUNT_SOLD"[NUMBER,22]
9 - "B"."CUST_ID"[NUMBER,22], "B"."AMOUNT_SOLD"[NUMBER,22]
10 - "B"."CUST_ID"[NUMBER,22], "B"."AMOUNT_SOLD"[NUMBER,22]
11 - "C"."CUST_ID"[NUMBER,22], "CUST_STATE_PROVINCE"[VARCHAR2,40]

Exemplo “Format TYPICAL MEMSTATS”:

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

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'TYPICAL MEMSTATS'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2gmbuzy8dzvv2, child number 0
-------------------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | OMem | 1Mem | O/1/M | Max-Tmp |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 7620 (100)| | | | 141 |00:00:50.26 | | | | |
| 1 | SORT ORDER BY | | 1 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 141 |00:00:50.26 | 27648 | 27648 | 1/0/0| |
| 2 | HASH GROUP BY | | 1 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 141 |00:00:50.26 | 1487K| 1190K| 1/0/0| |
|* 3 | HASH JOIN | | 1 | 7059 | 323K| | 7618 (57)| 00:01:32 | | | 7059 |00:00:50.26 | 1539K| 1539K| 1/0/0| |
| 4 | VIEW | | 1 | 7059 | 213K| | 7213 (60)| 00:01:27 | | | 7059 |00:00:50.23 | | | | |
| 5 | HASH GROUP BY | | 1 | 7059 | 137K| | 7213 (60)| 00:01:27 | | | 7059 |00:00:50.23 | 2772M| 41M| 1/0/0| |
|* 6 | HASH JOIN | | 1 | 119M| 2281M| 19M| 3292 (11)| 00:00:40 | | | 172M|00:00:25.16 | 44M| 9683K| | 18432 |
| 7 | PARTITION RANGE ALL| | 1 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.18 | | | | |
| 8 | TABLE ACCESS FULL | SALES | 28 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.09 | | | | |
| 9 | PARTITION RANGE ALL| | 1 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.21 | | | | |
| 10 | TABLE ACCESS FULL | SALES | 28 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.11 | | | | |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 867K| | 405 (1)| 00:00:05 | | | 55500 |00:00:00.01 | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

Exemplo “Format TYPICAL IOSTATS”:

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

Exemplo “Format TYPICAL ALLSTATS”:

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

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'TYPICAL ALLSTATS'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2gmbuzy8dzvv2, child number 0
-------------------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | O/1/M | Max-Tmp |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 7620 (100)| | | | 141 |00:00:50.26 | 10399 | 2130 | 2130 | | | | |
| 1 | SORT ORDER BY | | 1 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 141 |00:00:50.26 | 10399 | 2130 | 2130 | 27648 | 27648 | 1/0/0| |
| 2 | HASH GROUP BY | | 1 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 141 |00:00:50.26 | 10399 | 2130 | 2130 | 1487K| 1190K| 1/0/0| |
|* 3 | HASH JOIN | | 1 | 7059 | 323K| | 7618 (57)| 00:01:32 | | | 7059 |00:00:50.26 | 10399 | 2130 | 2130 | 1539K| 1539K| 1/0/0| |
| 4 | VIEW | | 1 | 7059 | 213K| | 7213 (60)| 00:01:27 | | | 7059 |00:00:50.23 | 8942 | 2130 | 2130 | | | | |
| 5 | HASH GROUP BY | | 1 | 7059 | 137K| | 7213 (60)| 00:01:27 | | | 7059 |00:00:50.23 | 8942 | 2130 | 2130 | 2772M| 41M| 1/0/0| |
|* 6 | HASH JOIN | | 1 | 119M| 2281M| 19M| 3292 (11)| 00:00:40 | | | 172M|00:00:25.16 | 8942 | 2130 | 2130 | 44M| 9683K| | 18432 |
| 7 | PARTITION RANGE ALL| | 1 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.18 | 4471 | 0 | 0 | | | | |
| 8 | TABLE ACCESS FULL | SALES | 28 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.09 | 4471 | 0 | 0 | | | | |
| 9 | PARTITION RANGE ALL| | 1 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.21 | 4471 | 0 | 0 | | | | |
| 10 | TABLE ACCESS FULL | SALES | 28 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.11 | 4471 | 0 | 0 | | | | |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 867K| | 405 (1)| 00:00:05 | | | 55500 |00:00:00.01 | 1457 | 0 | 0 | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

Exemplo “Format TYPICAL IOSTATS LAST”:

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'TYPICAL IOSTATS'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2gmbuzy8dzvv2, child number 0
-------------------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | Writes |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | | | 7620 (100)| | | | 282 |00:01:49.81 | 20798 | 4260 | 4260 |
| 1 | SORT ORDER BY | | 2 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 282 |00:01:49.81 | 20798 | 4260 | 4260 |
| 2 | HASH GROUP BY | | 2 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 282 |00:01:49.81 | 20798 | 4260 | 4260 |
|* 3 | HASH JOIN | | 2 | 7059 | 323K| | 7618 (57)| 00:01:32 | | | 14118 |00:01:49.80 | 20798 | 4260 | 4260 |
| 4 | VIEW | | 2 | 7059 | 213K| | 7213 (60)| 00:01:27 | | | 14118 |00:01:49.75 | 17884 | 4260 | 4260 |
| 5 | HASH GROUP BY | | 2 | 7059 | 137K| | 7213 (60)| 00:01:27 | | | 14118 |00:01:49.75 | 17884 | 4260 | 4260 |
|* 6 | HASH JOIN | | 2 | 119M| 2281M| 19M| 3292 (11)| 00:00:40 | | | 345M|00:00:58.32 | 17884 | 4260 | 4260 |
| 7 | PARTITION RANGE ALL| | 2 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 1837K|00:00:00.40 | 8942 | 0 | 0 |
| 8 | TABLE ACCESS FULL | SALES | 56 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 1837K|00:00:00.20 | 8942 | 0 | 0 |
| 9 | PARTITION RANGE ALL| | 2 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 1837K|00:00:00.45 | 8942 | 0 | 0 |
| 10 | TABLE ACCESS FULL | SALES | 56 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 1837K|00:00:00.24 | 8942 | 0 | 0 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 2 | 55500 | 867K| | 405 (1)| 00:00:05 | | | 111K|00:00:00.02 | 2914 | 0 | 0 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'TYPICAL IOSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2gmbuzy8dzvv2, child number 0
-------------------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | Writes |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 7620 (100)| | | | 141 |00:00:57.47 | 10399 | 2130 | 2130 |
| 1 | SORT ORDER BY | | 1 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 141 |00:00:57.47 | 10399 | 2130 | 2130 |
| 2 | HASH GROUP BY | | 1 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 141 |00:00:57.47 | 10399 | 2130 | 2130 |
|* 3 | HASH JOIN | | 1 | 7059 | 323K| | 7618 (57)| 00:01:32 | | | 7059 |00:00:57.46 | 10399 | 2130 | 2130 |
| 4 | VIEW | | 1 | 7059 | 213K| | 7213 (60)| 00:01:27 | | | 7059 |00:00:57.44 | 8942 | 2130 | 2130 |
| 5 | HASH GROUP BY | | 1 | 7059 | 137K| | 7213 (60)| 00:01:27 | | | 7059 |00:00:57.44 | 8942 | 2130 | 2130 |
|* 6 | HASH JOIN | | 1 | 119M| 2281M| 19M| 3292 (11)| 00:00:40 | | | 172M|00:00:31.64 | 8942 | 2130 | 2130 |
| 7 | PARTITION RANGE ALL| | 1 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.19 | 4471 | 0 | 0 |
| 8 | TABLE ACCESS FULL | SALES | 28 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.09 | 4471 | 0 | 0 |
| 9 | PARTITION RANGE ALL| | 1 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.23 | 4471 | 0 | 0 |
| 10 | TABLE ACCESS FULL | SALES | 28 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.13 | 4471 | 0 | 0 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 867K| | 405 (1)| 00:00:05 | | | 55500 |00:00:00.01 | 1457 | 0 | 0 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

Exemplo “Format TYPICAL IOSTATS ALL”:

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'TYPICAL IOSTATS ALL'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2gmbuzy8dzvv2, child number 0
-------------------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | Writes |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | | | 7620 (100)| | | | 282 |00:01:49.81 | 20798 | 4260 | 4260 |
| 1 | SORT ORDER BY | | 2 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 282 |00:01:49.81 | 20798 | 4260 | 4260 |
| 2 | HASH GROUP BY | | 2 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 282 |00:01:49.81 | 20798 | 4260 | 4260 |
|* 3 | HASH JOIN | | 2 | 7059 | 323K| | 7618 (57)| 00:01:32 | | | 14118 |00:01:49.80 | 20798 | 4260 | 4260 |
| 4 | VIEW | | 2 | 7059 | 213K| | 7213 (60)| 00:01:27 | | | 14118 |00:01:49.75 | 17884 | 4260 | 4260 |
| 5 | HASH GROUP BY | | 2 | 7059 | 137K| | 7213 (60)| 00:01:27 | | | 14118 |00:01:49.75 | 17884 | 4260 | 4260 |
|* 6 | HASH JOIN | | 2 | 119M| 2281M| 19M| 3292 (11)| 00:00:40 | | | 345M|00:00:58.32 | 17884 | 4260 | 4260 |
| 7 | PARTITION RANGE ALL| | 2 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 1837K|00:00:00.40 | 8942 | 0 | 0 |
| 8 | TABLE ACCESS FULL | SALES | 56 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 1837K|00:00:00.20 | 8942 | 0 | 0 |
| 9 | PARTITION RANGE ALL| | 2 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 1837K|00:00:00.45 | 8942 | 0 | 0 |
| 10 | TABLE ACCESS FULL | SALES | 56 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 1837K|00:00:00.24 | 8942 | 0 | 0 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 2 | 55500 | 867K| | 405 (1)| 00:00:05 | | | 111K|00:00:00.02 | 2914 | 0 | 0 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
4 - SEL$73523A42 / S@SEL$1
5 - SEL$73523A42
8 - SEL$73523A42 / A@SEL$2
10 - SEL$73523A42 / B@SEL$2
11 - SEL$1 / C@SEL$1

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1) SUM("TOT_SOLD_A")+SUM("TOT_SOLD_B")[22], "CUST_STATE_PROVINCE"[VARCHAR2,40]
2 - "CUST_STATE_PROVINCE"[VARCHAR2,40], SUM("TOT_SOLD_B")[22], SUM("TOT_SOLD_A")[22]
3 - (#keys=1) "TOT_SOLD_B"[NUMBER,22], "TOT_SOLD_A"[NUMBER,22], "CUST_STATE_PROVINCE"[VARCHAR2,40]
4 - "S"."CUST_ID"[NUMBER,22], "TOT_SOLD_A"[NUMBER,22], "TOT_SOLD_B"[NUMBER,22]
5 - "A"."CUST_ID"[NUMBER,22], SUM("B"."AMOUNT_SOLD")[22], SUM("A"."AMOUNT_SOLD")[22]
6 - (#keys=1) "A"."CUST_ID"[NUMBER,22], "A"."AMOUNT_SOLD"[NUMBER,22], "B"."AMOUNT_SOLD"[NUMBER,22]
7 - "A"."CUST_ID"[NUMBER,22], "A"."AMOUNT_SOLD"[NUMBER,22]
8 - "A"."CUST_ID"[NUMBER,22], "A"."AMOUNT_SOLD"[NUMBER,22]
9 - "B"."CUST_ID"[NUMBER,22], "B"."AMOUNT_SOLD"[NUMBER,22]
10 - "B"."CUST_ID"[NUMBER,22], "B"."AMOUNT_SOLD"[NUMBER,22]
11 - "C"."CUST_ID"[NUMBER,22], "CUST_STATE_PROVINCE"[VARCHAR2,40]

Exemplo “Format BASIC MEMSTATS COST”:

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'BASIC MEMSTATS'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | O/1/M | Max-Tmp |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 141 |00:00:50.95 | | | | |
| 1 | SORT ORDER BY | | 1 | 145 | 141 |00:00:50.95 | 27648 | 27648 | 1/0/0| |
| 2 | HASH GROUP BY | | 1 | 145 | 141 |00:00:50.95 | 1487K| 1190K| 1/0/0| |
|* 3 | HASH JOIN | | 1 | 7059 | 7059 |00:00:50.95 | 1539K| 1539K| 1/0/0| |
| 4 | VIEW | | 1 | 7059 | 7059 |00:00:50.93 | | | | |
| 5 | HASH GROUP BY | | 1 | 7059 | 7059 |00:00:50.93 | 2772M| 41M| 1/0/0| |
|* 6 | HASH JOIN | | 1 | 119M| 172M|00:00:25.41 | 44M| 9683K| | 18432 |
| 7 | PARTITION RANGE ALL| | 1 | 918K| 918K|00:00:00.18 | | | | |
| 8 | TABLE ACCESS FULL | SALES | 28 | 918K| 918K|00:00:00.08 | | | | |
| 9 | PARTITION RANGE ALL| | 1 | 918K| 918K|00:00:00.21 | | | | |
| 10 | TABLE ACCESS FULL | SALES | 28 | 918K| 918K|00:00:00.12 | | | | |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 55500 |00:00:00.01 | | | | |
----------------------------------------------------------------------------------------------------------------------------

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

35 rows selected.

Elapsed: 00:00:00.12
curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'BASIC MEMSTATS COST'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | OMem | 1Mem | O/1/M | Max-Tmp |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7620 (100)| 141 |00:00:50.95 | | | | |
| 1 | SORT ORDER BY | | 1 | 145 | 7620 (57)| 141 |00:00:50.95 | 27648 | 27648 | 1/0/0| |
| 2 | HASH GROUP BY | | 1 | 145 | 7620 (57)| 141 |00:00:50.95 | 1487K| 1190K| 1/0/0| |
|* 3 | HASH JOIN | | 1 | 7059 | 7618 (57)| 7059 |00:00:50.95 | 1539K| 1539K| 1/0/0| |
| 4 | VIEW | | 1 | 7059 | 7213 (60)| 7059 |00:00:50.93 | | | | |
| 5 | HASH GROUP BY | | 1 | 7059 | 7213 (60)| 7059 |00:00:50.93 | 2772M| 41M| 1/0/0| |
|* 6 | HASH JOIN | | 1 | 119M| 3292 (11)| 172M|00:00:25.41 | 44M| 9683K| | 18432 |
| 7 | PARTITION RANGE ALL| | 1 | 918K| 525 (2)| 918K|00:00:00.18 | | | | |
| 8 | TABLE ACCESS FULL | SALES | 28 | 918K| 525 (2)| 918K|00:00:00.08 | | | | |
| 9 | PARTITION RANGE ALL| | 1 | 918K| 525 (2)| 918K|00:00:00.21 | | | | |
| 10 | TABLE ACCESS FULL | SALES | 28 | 918K| 525 (2)| 918K|00:00:00.12 | | | | |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 405 (1)| 55500 |00:00:00.01 | | | | |
-----------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

Exemplo “Format TYPICAL IOSTATS -ROWS -COST -BYTES”:

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'TYPICAL IOSTATS'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2gmbuzy8dzvv2, child number 0
-------------------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | Writes |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 7620 (100)| | | | 141 |00:00:50.95 | 10399 | 2130 | 2130 |
| 1 | SORT ORDER BY | | 1 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 141 |00:00:50.95 | 10399 | 2130 | 2130 |
| 2 | HASH GROUP BY | | 1 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 141 |00:00:50.95 | 10399 | 2130 | 2130 |
|* 3 | HASH JOIN | | 1 | 7059 | 323K| | 7618 (57)| 00:01:32 | | | 7059 |00:00:50.95 | 10399 | 2130 | 2130 |
| 4 | VIEW | | 1 | 7059 | 213K| | 7213 (60)| 00:01:27 | | | 7059 |00:00:50.93 | 8942 | 2130 | 2130 |
| 5 | HASH GROUP BY | | 1 | 7059 | 137K| | 7213 (60)| 00:01:27 | | | 7059 |00:00:50.93 | 8942 | 2130 | 2130 |
|* 6 | HASH JOIN | | 1 | 119M| 2281M| 19M| 3292 (11)| 00:00:40 | | | 172M|00:00:25.41 | 8942 | 2130 | 2130 |
| 7 | PARTITION RANGE ALL| | 1 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.18 | 4471 | 0 | 0 |
| 8 | TABLE ACCESS FULL | SALES | 28 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.08 | 4471 | 0 | 0 |
| 9 | PARTITION RANGE ALL| | 1 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.21 | 4471 | 0 | 0 |
| 10 | TABLE ACCESS FULL | SALES | 28 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.12 | 4471 | 0 | 0 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 867K| | 405 (1)| 00:00:05 | | | 55500 |00:00:00.01 | 1457 | 0 | 0 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

35 rows selected.

Elapsed: 00:00:00.06
curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'TYPICAL IOSTATS -ROWS -COST -BYTES'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2gmbuzy8dzvv2, child number 0
-------------------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | Writes |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 141 |00:00:50.95 | 10399 | 2130 | 2130 |
| 1 | SORT ORDER BY | | 1 | 00:01:32 | | | 141 |00:00:50.95 | 10399 | 2130 | 2130 |
| 2 | HASH GROUP BY | | 1 | 00:01:32 | | | 141 |00:00:50.95 | 10399 | 2130 | 2130 |
|* 3 | HASH JOIN | | 1 | 00:01:32 | | | 7059 |00:00:50.95 | 10399 | 2130 | 2130 |
| 4 | VIEW | | 1 | 00:01:27 | | | 7059 |00:00:50.93 | 8942 | 2130 | 2130 |
| 5 | HASH GROUP BY | | 1 | 00:01:27 | | | 7059 |00:00:50.93 | 8942 | 2130 | 2130 |
|* 6 | HASH JOIN | | 1 | 00:00:40 | | | 172M|00:00:25.41 | 8942 | 2130 | 2130 |
| 7 | PARTITION RANGE ALL| | 1 | 00:00:07 | 1 | 28 | 918K|00:00:00.18 | 4471 | 0 | 0 |
| 8 | TABLE ACCESS FULL | SALES | 28 | 00:00:07 | 1 | 28 | 918K|00:00:00.08 | 4471 | 0 | 0 |
| 9 | PARTITION RANGE ALL| | 1 | 00:00:07 | 1 | 28 | 918K|00:00:00.21 | 4471 | 0 | 0 |
| 10 | TABLE ACCESS FULL | SALES | 28 | 00:00:07 | 1 | 28 | 918K|00:00:00.12 | 4471 | 0 | 0 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 00:00:05 | | | 55500 |00:00:00.01 | 1457 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

Exemplo “Format TYPICAL ALIAS”:

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID 2gmbuzy8dzvv2, child number 0
-------------------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 7620 (100)| | | |
| 1 | SORT ORDER BY | | 145 | 6815 | | 7620 (57)| 00:01:32 | | |
| 2 | HASH GROUP BY | | 145 | 6815 | | 7620 (57)| 00:01:32 | | |
|* 3 | HASH JOIN | | 7059 | 323K| | 7618 (57)| 00:01:32 | | |
| 4 | VIEW | | 7059 | 213K| | 7213 (60)| 00:01:27 | | |
| 5 | HASH GROUP BY | | 7059 | 137K| | 7213 (60)| 00:01:27 | | |
|* 6 | HASH JOIN | | 119M| 2281M| 19M| 3292 (11)| 00:00:40 | | |
| 7 | PARTITION RANGE ALL| | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 8 | TABLE ACCESS FULL | SALES | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 9 | PARTITION RANGE ALL| | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 10 | TABLE ACCESS FULL | SALES | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 867K| | 405 (1)| 00:00:05 | | |
---------------------------------------------------------------------------------------------------------------

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'TYPICAL ALIAS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID 2gmbuzy8dzvv2, child number 0
-------------------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 7620 (100)| | | |
| 1 | SORT ORDER BY | | 145 | 6815 | | 7620 (57)| 00:01:32 | | |
| 2 | HASH GROUP BY | | 145 | 6815 | | 7620 (57)| 00:01:32 | | |
|* 3 | HASH JOIN | | 7059 | 323K| | 7618 (57)| 00:01:32 | | |
| 4 | VIEW | | 7059 | 213K| | 7213 (60)| 00:01:27 | | |
| 5 | HASH GROUP BY | | 7059 | 137K| | 7213 (60)| 00:01:27 | | |
|* 6 | HASH JOIN | | 119M| 2281M| 19M| 3292 (11)| 00:00:40 | | |
| 7 | PARTITION RANGE ALL| | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 8 | TABLE ACCESS FULL | SALES | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 9 | PARTITION RANGE ALL| | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 10 | TABLE ACCESS FULL | SALES | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 867K| | 405 (1)| 00:00:05 | | |
---------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
4 - SEL$73523A42 / S@SEL$1
5 - SEL$73523A42
8 - SEL$73523A42 / A@SEL$2
10 - SEL$73523A42 / B@SEL$2
11 - SEL$1 / C@SEL$1

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

Exemplo “Format TYPICAL IOSTATS ALL -PROJECTION”:

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
45
46
47
48

curso01@ODBSRV11 > SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('2gmbuzy8dzvv2', NULL, 'TYPICAL IOSTATS ALL -PROJECTION'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2gmbuzy8dzvv2, child number 0
-------------------------------------
SELECT /* TESTE010 */ CUST_STATE_PROVINCE,
SUM(TOT_SOLD_A)+SUM(TOT_SOLD_B) US FROM SH.CUSTOMERS C,
(SELECT A.CUST_ID, SUM(A.AMOUNT_SOLD) TOT_SOLD_A, SUM(B.AMOUNT_SOLD)
TOT_SOLD_B FROM SH.SALES A, SH.SALES B
WHERE A.CUST_ID = B.CUST_ID GROUP BY A.CUST_ID ORDER BY
:"SYS_B_0") S WHERE C.CUST_ID = S.CUST_ID GROUP BY
CUST_STATE_PROVINCE ORDER BY :"SYS_B_1" DESC

Plan hash value: 1062552367

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | Writes |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 7620 (100)| | | | 141 |00:00:50.95 | 10399 | 2130 | 2130 |
| 1 | SORT ORDER BY | | 1 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 141 |00:00:50.95 | 10399 | 2130 | 2130 |
| 2 | HASH GROUP BY | | 1 | 145 | 6815 | | 7620 (57)| 00:01:32 | | | 141 |00:00:50.95 | 10399 | 2130 | 2130 |
|* 3 | HASH JOIN | | 1 | 7059 | 323K| | 7618 (57)| 00:01:32 | | | 7059 |00:00:50.95 | 10399 | 2130 | 2130 |
| 4 | VIEW | | 1 | 7059 | 213K| | 7213 (60)| 00:01:27 | | | 7059 |00:00:50.93 | 8942 | 2130 | 2130 |
| 5 | HASH GROUP BY | | 1 | 7059 | 137K| | 7213 (60)| 00:01:27 | | | 7059 |00:00:50.93 | 8942 | 2130 | 2130 |
|* 6 | HASH JOIN | | 1 | 119M| 2281M| 19M| 3292 (11)| 00:00:40 | | | 172M|00:00:25.41 | 8942 | 2130 | 2130 |
| 7 | PARTITION RANGE ALL| | 1 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.18 | 4471 | 0 | 0 |
| 8 | TABLE ACCESS FULL | SALES | 28 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.08 | 4471 | 0 | 0 |
| 9 | PARTITION RANGE ALL| | 1 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.21 | 4471 | 0 | 0 |
| 10 | TABLE ACCESS FULL | SALES | 28 | 918K| 8973K| | 525 (2)| 00:00:07 | 1 | 28 | 918K|00:00:00.12 | 4471 | 0 | 0 |
| 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 867K| | 405 (1)| 00:00:05 | | | 55500 |00:00:00.01 | 1457 | 0 | 0 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1
4 - SEL$73523A42 / S@SEL$1
5 - SEL$73523A42
8 - SEL$73523A42 / A@SEL$2
10 - SEL$73523A42 / B@SEL$2
11 - SEL$1 / C@SEL$1

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

3 - access("C"."CUST_ID"="S"."CUST_ID")
6 - access("A"."CUST_ID"="B"."CUST_ID")

Conclusão:

O pacote “DBMS_XPLAN” como verificamos acima é muito versátil e gera informações que são muito uteis no trabalho de ajuste de desempenho das instruções SQL utilizadas pelas aplicações, se você não utilizava esta ferramenta ou se utilizava de forma limitada, passe a utilizar todos os recursos que ela oferece para tornar o seu trabalho de tuning mais eficaz.