{"id":870,"date":"2016-11-02T22:11:26","date_gmt":"2016-11-03T00:11:26","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=870"},"modified":"2019-11-11T18:53:50","modified_gmt":"2019-11-11T21:53:50","slug":"exadata-como-obter-as-metricas-do-smart-scan-de-uma-consulta","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/exadata-como-obter-as-metricas-do-smart-scan-de-uma-consulta\/","title":{"rendered":"Exadata: Como obter as m\u00e9tricas do Smart Scan de uma consulta?"},"content":{"rendered":"

Quando uma instru\u00e7\u00e3o SQL \u00e9 executada numa base Oracle utilizando uma plataforma tradicional (Unix-like, Microsoft,…) somos capazes de obter as estat\u00edsticas desta execu\u00e7\u00e3o com detalhes suficientes para determinar onde est\u00e1 o gargalo desta instru\u00e7\u00e3o atrav\u00e9s do pacote DBMS_XPLAN. Na plataforma Exadata muitas fun\u00e7\u00f5es s\u00e3o executadas no Storage Server durante a execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL e as estat\u00edsticas destas fun\u00e7\u00f5es n\u00e3o est\u00e3o dispon\u00edveis no pacote DBMS_XPLAN, portanto quando analisamos uma plano de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL executada na plataforma Exadata muitas perguntas ficam sem resposta se utilizarmos exclusivamente este pacote:<\/span><\/p>\n

1) Qual a quantidade de I\/O f\u00edsico real que foi realizada?<\/span>
\n2) Quanto desse I\/O foi leitura e quanto foi grava\u00e7\u00e3o?<\/span>
\n3) Qual a quantidade de I\/O que foi evitada devido ao recurso Flash Cache?<\/span>
\n4) Qual a quantidade de I\/O que foi evitada devido ao recurso Storage Indexes?<\/span>
\n5) Qual foi o trafego total pelo Interconnect?<\/span>
\n6) Qual a quantidade de dados que foi totalmente processada no Storage Server?<\/span>
\n7) Qual a quantidade de dados que foi retornada para o Database Server pelo Smart Scan?<\/span><\/p>\n

Neste artigo vamos fazer uma demonstra\u00e7\u00e3o de uma ferramenta desenvolvida por Tanel Poder<\/a><\/strong> que fornece respostas para todos estas perguntas e permite a visualiza\u00e7\u00e3o de todas estas informa\u00e7\u00f5es para instru\u00e7\u00f5es SQL executadas na plataforma Exadata, possibilitando uma analise de performance completa e a identifica\u00e7\u00e3o de eventuais gargalos.<\/span><\/p>\n

Roteiro da demonstra\u00e7\u00e3o<\/span><\/h2>\n

Para demonstrar a utiliza\u00e7\u00e3o da ferramenta EXADATA SNAPPER vamos realizar a seguintes tarefas:<\/span><\/p>\n

1) Baixar e instalar o EXADATA SNAPPER numa inst\u00e2ncia Oracle na plataforma Exadata<\/span><\/p>\n

2) Criar uma tabela na inst\u00e2ncia<\/span><\/p>\n

3) Executar uma consulta na tabela com algumas fun\u00e7\u00f5es do Exadata desativada a n\u00edvel de sess\u00e3o<\/span><\/p>\n

4) Executar a consulta novamente com as fun\u00e7\u00f5es ativadas a n\u00edvel de sess\u00e3o<\/span><\/p>\n

Baixar e instalar o EXADATA SNAPPER<\/span><\/h3>\n

1) Para instala\u00e7\u00e3o do EXADATA SNAPPER acesse o site http:\/\/blog.tanelpoder.com\/files\/<\/a><\/strong> e clique no item 2. Download all my TPT scripts as a .zip file<\/span><\/p>\n

2) Descompacte o arquivo “tpt_public.zip” em um diret\u00f3rio no seu computador<\/span><\/p>\n

3) Dentro desse diret\u00f3rio ser\u00e1 criado o diret\u00f3rio “scripts” que contem outros diret\u00f3rios, acesse o diret\u00f3rio exadata onde se encontra o script “exasnapper_install_latest.sql”<\/span><\/p>\n

4) Para executar esse script de instala\u00e7\u00e3o o usu\u00e1rio do banco no qual ser\u00e1 instalado o EXASNAPPER precisa ter as seguintes permiss\u00f5es:<\/span><\/p>\n

Privil\u00e9gio SELECT ANY DICTIONARY ou privil\u00e9gio de SELECT nas vis\u00f5es GV$ referenciadas no script;<\/span>
\n Privil\u00e9gio EXECUTE no pacote SYS.DBMS_LOCK<\/span><\/p>\n

5) Utilizando um cliente oracle conectado com o usu\u00e1rio que recebeu os privil\u00e9gios acima, execute o script “exasnapper_install_latest.sql”<\/span><\/p>\n

Criar uma tabela na inst\u00e2ncia<\/span><\/h3>\n

Para realizar a demonstra\u00e7\u00e3o vamos criar um tabela com tamanho de 11GB.<\/span>
\n <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\nSQL ><\/span> ALTER SESSION SET WORKAREA_SIZE_POLICY=<\/span>MANUAL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> ALTER SESSION SET SORT_AREA_SIZE=<\/span>2000000000<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> CREATE TABLE dbtw01 AS<\/span>
\n  2<\/span>     SELECT 'ABC'<\/span>||<\/span>LPAD(<\/span>MOD(<\/span>ROWNUM,<\/span>40617473<\/span>)<\/span>,<\/span>80<\/span>,<\/span>0<\/span>)<\/span> AS<\/span> texto,<\/span>
\n  3<\/span>            mod(<\/span>rownum,<\/span>11<\/span>)<\/span> AS<\/span> situacao,<\/span>
\n  4<\/span>            'XPT'<\/span>||<\/span>LPAD(<\/span>MOD(<\/span>ROWNUM,<\/span>4500<\/span>)<\/span>,<\/span>100<\/span>,<\/span>0<\/span>)<\/span> AS<\/span> texto2,<\/span>
\n  5<\/span>            'XYZ'<\/span>||<\/span>LPAD(<\/span>MOD(<\/span>ROWNUM+<\/span>40000<\/span>,<\/span>787648<\/span>)<\/span>,<\/span>60<\/span>,<\/span>0<\/span>)<\/span> AS<\/span> texto3,<\/span>
\n  6<\/span>            SYSDATE-<\/span>ROUND<\/span><\/a>(<\/span>ROWNUM\/<\/span>30<\/span>,<\/span>0<\/span>)<\/span>                    AS<\/span> data_criacao
\n  7<\/span>       FROM dual CONNECT BY LEVEL<=<\/span>40617473<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span> select round<\/span><\/a>(<\/span>bytes\/<\/span>1024<\/span>\/<\/span>1024<\/span>\/<\/span>1024<\/span>,<\/span>1<\/span>)<\/span> as<\/span> "Tamanho GB"<\/span>
\n  2<\/span>    from user_segments
\n  3<\/span>   where segment_name =<\/span> 'DBTW01'<\/span>;<\/span>
\n
\nTamanho GB
\n----------<\/span>
\n      11<\/span>,<\/span>5<\/span>
\n
\nSQL ><\/span> EXEC<\/span><\/a> DBMS_STATS.<\/span>GATHER_TABLE_STATS(<\/span>OWNNAME=><\/span> USER,<\/span> TABNAME=><\/span> 'DBTW01'<\/span>,<\/span> CASCADE=><\/span> TRUE<\/span>,<\/span> METHOD_OPT=><\/span> 'FOR ALL COLUMNS SIZE SKEWONLY'<\/span>,<\/span> DEGREE=><\/span>18<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Consulta com as fun\u00e7\u00f5es Exadata desativadas<\/span><\/h3>\n

Vamos executar uma consulta na tabela criada e inicialmente vamos desativar algumas fun\u00e7\u00f5es a n\u00edvel de sess\u00e3o utilizando par\u00e2metros n\u00e3o documentos.<\/span><\/p>\n

 <\/p>\n

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
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
<\/div><\/td>
SQL ><\/span> SET TAB OFF;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Permite a exibi\u00e7\u00e3o das estat\u00edsticas de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> --<\/span> Desativa a fun\u00e7\u00e3o Storage Index do<\/span> Exadata
\nSQL ><\/span> ALTER SESSION SET "_kcfis_storageidx_disabled"<\/span> =<\/span> TRUE<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> --<\/span> Desativa a leitura de blocos direta sem a passagem pelo Buffer Cache
\nSQL ><\/span> ALTER SESSION SET "_serial_direct_read"<\/span> =<\/span> FALSE<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> COL NAME FOR<\/span> A300
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Defini\u00e7\u00e3o das vari\u00e1veis que ser\u00e3o utilizadas no EXASNAPPER
\nSQL ><\/span> variable b NUMBER;<\/span>
\nSQL ><\/span> variable e NUMBER;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera um snapshot das estatisticas e atualiza a variavel "b"<\/span> com o valor do<\/span> snap_id antes da execu\u00e7\u00e3o da consulta
\nSQL ><\/span> EXEC<\/span><\/a> :<\/span>b :=<\/span> exasnap.<\/span>begin_snap;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> SELECT \/* DBTW001 *\/<\/span> situacao,<\/span> COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde
\n  2<\/span>    FROM dbtw01
\n  3<\/span>   WHERE data_criacao BETWEEN SYSDATE-<\/span>120<\/span> AND SYSDATE-<\/span>30<\/span>
\n  4<\/span>      OR data_criacao BETWEEN SYSDATE-<\/span>720<\/span> AND SYSDATE-<\/span>630<\/span>
\n  5<\/span>      OR data_criacao BETWEEN SYSDATE-<\/span>1320<\/span> AND SYSDATE-<\/span>1230<\/span>
\n  6<\/span>      OR data_criacao BETWEEN SYSDATE-<\/span>1920<\/span> AND SYSDATE-<\/span>1830<\/span>
\n  7<\/span>      OR data_criacao BETWEEN SYSDATE-<\/span>2520<\/span> AND SYSDATE-<\/span>2430<\/span>
\n  8<\/span>   GROUP BY situacao;<\/span>
\n
\n  SITUACAO       QTDE
\n----------<\/span> ----------<\/span>
\n         1<\/span>       1228<\/span>
\n         6<\/span>       1227<\/span>
\n         2<\/span>       1227<\/span>
\n         4<\/span>       1228<\/span>
\n         5<\/span>       1228<\/span>
\n         8<\/span>       1228<\/span>
\n         3<\/span>       1227<\/span>
\n         7<\/span>       1227<\/span>
\n         9<\/span>       1227<\/span>
\n        10<\/span>       1226<\/span>
\n         0<\/span>       1227<\/span>
\n
\n11<\/span> linhas selecionadas.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>04.79<\/span>
\nSQL ><\/span> --<\/span> Gera um snapshot das estatisticas e atualiza a variavel "e"<\/span> com o valor do<\/span> snap_id ap\u00f3s da execu\u00e7\u00e3o da consulta
\nSQL ><\/span> EXEC<\/span><\/a> :<\/span>e :=<\/span> exasnap.<\/span>end_snap;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW001%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nfmwugmvdrxtuc            1<\/span>
\n
\nSQL ><\/span> --<\/span> Gera o relat\u00f3rio do<\/span> plano de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  fmwugmvdrxtuc,<\/span> child number 1<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW001 *\/<\/span> situacao,<\/span> COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde   FROM dbtw01  WHERE
\ndata_criacao BETWEEN SYSDATE-<\/span>120<\/span> AND SYSDATE-<\/span>30<\/span>     OR data_criacao
\nBETWEEN SYSDATE-<\/span>720<\/span> AND SYSDATE-<\/span>630<\/span>     OR data_criacao BETWEEN
\nSYSDATE-<\/span>1320<\/span> AND SYSDATE-<\/span>1230<\/span>     OR data_criacao BETWEEN SYSDATE-<\/span>1920<\/span>
\nAND SYSDATE-<\/span>1830<\/span>     OR data_criacao BETWEEN SYSDATE-<\/span>2520<\/span> AND
\nSYSDATE-<\/span>2430<\/span>  GROUP BY situacao
\n
\nPlan hash<\/span><\/a> value:<\/span> 1822618879<\/span>
\n
\n----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                  |<\/span> Name   |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT           |<\/span>        |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   191K(<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     11<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>04.67<\/span> |<\/span>    1504K|<\/span>   1504K|<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY             |<\/span>        |<\/span>      1<\/span> |<\/span>     11<\/span> |<\/span>   121<\/span> |<\/span>   191K  (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>54<\/span>:<\/span>11<\/span> |<\/span>     11<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>04.67<\/span> |<\/span>    1504K|<\/span>   1504K|<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS STORAGE FULL|<\/span> DBTW01 |<\/span>      1<\/span> |<\/span>  13799<\/span> |<\/span>   148K|<\/span>   191K  (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>54<\/span>:<\/span>11<\/span> |<\/span>  13500<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>04.67<\/span> |<\/span>    1504K|<\/span>   1504K|<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> storage(<\/span>(<\/span>(<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>120<\/span> AND "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>30<\/span>)<\/span> OR (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>720<\/span> AND
\n              "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>630<\/span>)<\/span> OR (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>1320<\/span> AND "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>1230<\/span>)<\/span> OR
\n              (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>1920<\/span> AND "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>1830<\/span>)<\/span> OR (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>2520<\/span> AND
\n              "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>2430<\/span>)<\/span>)<\/span>)<\/span>
\n       filter(<\/span>(<\/span>(<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>120<\/span> AND "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>30<\/span>)<\/span> OR (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>720<\/span> AND
\n              "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>630<\/span>)<\/span> OR (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>1320<\/span> AND "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>1230<\/span>)<\/span> OR
\n              (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>1920<\/span> AND "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>1830<\/span>)<\/span> OR (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>2520<\/span> AND
\n              "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>2430<\/span>)<\/span>)<\/span>)<\/span>
\n
\n
\n31<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Examinando o plano de execu\u00e7\u00e3o verificamos que a tabela foi lida utilizando a opera\u00e7\u00e3o “TABLE ACCESS STORAGE FULL”, esta opera\u00e7\u00e3o n\u00e3o garante que os dados foram recuperados do Storage Server utilizando o Smart Scan, mas quando presente indica que o Storage Server pode utilizar Smart Scan.<\/span><\/p>\n

Da mesma forma devemos interpretar a exist\u00eancia da cl\u00e1usula “storage()” na se\u00e7\u00e3o “Predicate Information”.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> --<\/span> Gera o relat\u00f3rio com as<\/span> estat\u00edsticas da consulta no Storage Server
\nSQL ><\/span> SELECT *<\/span> FROM TABLE(<\/span>exasnap.<\/span>display_snap(<\/span>:<\/span>b,<\/span> :<\/span>e)<\/span>)<\/span>;<\/span>
\n
\nNAME
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n--<\/span> ExaSnapper v0.<\/span>81 BETA by Tanel Poder @<\/span> Enkitec -<\/span> The Exadata Experts (<\/span> http:<\/span>\/\/www.enkitec.com )<\/span>
\n---------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nDB_LAYER_IO                    DB_PHYSIO_BYTES               |<\/span>##################################################|          11753 MB           2007 MB\/sec
\n<\/span>DB_LAYER_IO                    DB_PHYSRD_BYTES               |<\/span>##################################################|          11753 MB           2007 MB\/sec
\n<\/span>DB_LAYER_IO                    DB_PHYSWR_BYTES               |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nAVOID_DISK_IO                  PHYRD_FLASH_RD_BYTES          |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nAVOID_DISK_IO                  PHYRD_STORIDX_SAVED_BYTES     |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nREAL_DISK_IO                   SPIN_DISK_IO_BYTES            |<\/span>##################################################|          11753 MB           2007 MB\/sec
\n<\/span>REAL_DISK_IO                   SPIN_DISK_RD_BYTES            |<\/span>##################################################|          11753 MB           2007 MB\/sec
\n<\/span>REAL_DISK_IO                   SPIN_DISK_WR_BYTES            |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nREDUCE_INTERCONNECT            PRED_OFFLOADABLE_BYTES        |<\/span>##################################################|          11753 MB           2007 MB\/sec
\n<\/span>REDUCE_INTERCONNECT            TOTAL_IC_BYTES                |<\/span>                                                  |<\/span>              2<\/span> MB              0<\/span> MB\/<\/span>sec
\nREDUCE_INTERCONNECT            SMART_SCAN_RET_BYTES          |<\/span>                                                  |<\/span>              2<\/span> MB              0<\/span> MB\/<\/span>sec
\nREDUCE_INTERCONNECT            NON_SMART_SCAN_BYTES          |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nCELL_PROC_DEPTH                CELL_PROC_DATA_BYTES          |<\/span>##################################################|          11753 MB           2007 MB\/sec
\n<\/span>CELL_PROC_DEPTH                CELL_PROC_INDEX_BYTES         |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nCLIENT_COMMUNICATION           NET_TO_CLIENT_BYTES           |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nCLIENT_COMMUNICATION           NET_FROM_CLIENT_BYTES         |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\n
\n18<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

No relat\u00f3rio gerado pelo EXASNAPPER temos as informa\u00e7\u00f5es detalhadas do que ocorreu no STORAGE SERVER:<\/span><\/p>\n

DB_LAYER_IO – DB_PHYSIO_BYTES<\/span><\/strong> \u00a0<\/span><\/p>\n

Quantidade de I\/O em bytes que o banco de dados espera realizar com Storage Server baseado em seu dicionario de dados.<\/span><\/p>\n

DB_LAYER_IO – DB_PHYSRD_BYTES<\/strong><\/span><\/p>\n

Quantidade de bytes que o banco de dados espera recuperar do Storage Server baseado em seu dicionario de dados.<\/span><\/p>\n

REAL_DISK_IO – SPIN_DISK_IO_BYTES <\/span><\/strong><\/p>\n

Quantidade de I\/O f\u00edsico em bytes que o Storage Server realizou durante a execu\u00e7\u00e3o consulta.<\/span><\/p>\n

REAL_DISK_IO – SPIN_DISK_RD_BYTES <\/strong><\/span><\/p>\n

Quantidade de leitura f\u00edsica em bytes realizada pelo Storage Server durante a execu\u00e7\u00e3o consulta.<\/span><\/p>\n

REDUCE_INTERCONNECT – PRED_OFFLOADABLE_BYTES <\/strong><\/span><\/p>\n

Quantidade de bytes que pode ser processado a n\u00edvel de Storage Server durante a execu\u00e7\u00e3o consulta.<\/span><\/p>\n

REDUCE_INTERCONNECT – TOTAL_IC_BYTES <\/span><\/strong><\/p>\n

Quantidade de bytes que trafegou pelo INTERCONNECT entre o Banco de dados e o Storage Server.<\/span><\/p>\n

REDUCE_INTERCONNECT – SMART_SCAN_RET_BYTES <\/strong><\/span><\/p>\n

Quantidade de bytes que foi retornada para o banco de dados como resultado do processamento do Smart Scan.<\/span><\/p>\n

CELL_PROC_DEPTH – CELL_PROC_DATA_BYTES <\/strong><\/span><\/p>\n

Quantidade de bytes de dados processados no Storage Server.<\/span><\/p>\n

 
\n <\/p>\n

Consulta com as fun\u00e7\u00f5es Exadata ativadas<\/span><\/h3>\n

Agora vamos executar a mesma consulta novamente, s\u00f3 que desta vez vamos ativar as fun\u00e7\u00f5es que haviam sido desativadas na primeira execu\u00e7\u00e3o.<\/span><\/p>\n

 <\/p>\n

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
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
<\/div><\/td>
SQL ><\/span> SET TAB OFF;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Permite a exibi\u00e7\u00e3o das estat\u00edsticas de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> --<\/span> Ativa a fun\u00e7\u00e3o Storage Index do<\/span> Exadata
\nSQL ><\/span> ALTER SESSION SET "_kcfis_storageidx_disabled"<\/span> =<\/span> FALSE<\/span>;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> --<\/span> Permite a leitura de blocos direta sem a passagem pelo Buffer Cache
\nSQL ><\/span> ALTER SESSION SET "_serial_direct_read"<\/span> =<\/span> AUTO;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span> COL NAME FOR<\/span> A300
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Defini\u00e7\u00e3o das vari\u00e1veis que ser\u00e3o utilizadas no EXASNAPPER
\nSQL ><\/span> variable b NUMBER;<\/span>
\nSQL ><\/span> variable e NUMBER;<\/span>
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera um snapshot das estatisticas e atualiza a variavel "b"<\/span> com o valor do<\/span> snap_id antes da execu\u00e7\u00e3o da consulta
\nSQL ><\/span> EXEC<\/span><\/a> :<\/span>b :=<\/span> exasnap.<\/span>begin_snap;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nDecorrido:<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>00.84<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW003 *\/<\/span> situacao,<\/span> COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde
\n  2<\/span>    FROM dbtw01
\n  3<\/span>   WHERE data_criacao BETWEEN SYSDATE-<\/span>120<\/span> AND SYSDATE-<\/span>30<\/span>
\n  4<\/span>      OR data_criacao BETWEEN SYSDATE-<\/span>720<\/span> AND SYSDATE-<\/span>630<\/span>
\n  5<\/span>      OR data_criacao BETWEEN SYSDATE-<\/span>1320<\/span> AND SYSDATE-<\/span>1230<\/span>
\n  6<\/span>      OR data_criacao BETWEEN SYSDATE-<\/span>1920<\/span> AND SYSDATE-<\/span>1830<\/span>
\n  7<\/span>      OR data_criacao BETWEEN SYSDATE-<\/span>2520<\/span> AND SYSDATE-<\/span>2430<\/span>
\n  8<\/span>   GROUP BY situacao;<\/span>
\n
\n  SITUACAO       QTDE
\n----------<\/span> ----------<\/span>
\n         1<\/span>       1228<\/span>
\n         6<\/span>       1227<\/span>
\n         2<\/span>       1227<\/span>
\n         4<\/span>       1228<\/span>
\n         5<\/span>       1228<\/span>
\n         8<\/span>       1228<\/span>
\n         3<\/span>       1227<\/span>
\n         7<\/span>       1227<\/span>
\n         9<\/span>       1227<\/span>
\n        10<\/span>       1226<\/span>
\n         0<\/span>       1227<\/span>
\n
\n11<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span> --<\/span> Gera um snapshot das estatisticas e atualiza a variavel "e"<\/span> com o valor do<\/span> snap_id ap\u00f3s da execu\u00e7\u00e3o da consulta
\nSQL ><\/span> EXEC<\/span><\/a> :<\/span>e :=<\/span> exasnap.<\/span>end_snap;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%DBTW003%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n6cbrtk6rxvaz5            1<\/span>
\n
\nSQL ><\/span> --<\/span> Gera o relat\u00f3rio do<\/span> plano de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  6cbrtk6rxvaz5,<\/span> child number 1<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW003 *\/<\/span> situacao,<\/span> COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span> qtde   FROM dbtw01  WHERE
\ndata_criacao BETWEEN SYSDATE-<\/span>120<\/span> AND SYSDATE-<\/span>30<\/span>     OR data_criacao
\nBETWEEN SYSDATE-<\/span>720<\/span> AND SYSDATE-<\/span>630<\/span>     OR data_criacao BETWEEN
\nSYSDATE-<\/span>1320<\/span> AND SYSDATE-<\/span>1230<\/span>     OR data_criacao BETWEEN SYSDATE-<\/span>1920<\/span>
\nAND SYSDATE-<\/span>1830<\/span>     OR data_criacao BETWEEN SYSDATE-<\/span>2520<\/span> AND
\nSYSDATE-<\/span>2430<\/span>  GROUP BY situacao
\n
\nPlan hash<\/span><\/a> value:<\/span> 1822618879<\/span>
\n
\n----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                  |<\/span> Name   |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span> Reads  |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT           |<\/span>        |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>   191K(<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     11<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span> |<\/span>    1504K|<\/span>   1504K|<\/span>
\n|<\/span>   1<\/span> |<\/span>  HASH<\/span><\/a> GROUP BY             |<\/span>        |<\/span>      1<\/span> |<\/span>     11<\/span> |<\/span>   121<\/span> |<\/span>   191K  (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>54<\/span>:<\/span>11<\/span> |<\/span>     11<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span> |<\/span>    1504K|<\/span>   1504K|<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS STORAGE FULL|<\/span> DBTW01 |<\/span>      1<\/span> |<\/span>  13799<\/span> |<\/span>   148K|<\/span>   191K  (<\/span>2<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>54<\/span>:<\/span>11<\/span> |<\/span>  13500<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.05<\/span> |<\/span>    1504K|<\/span>   1504K|<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> storage(<\/span>(<\/span>(<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>120<\/span> AND "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>30<\/span>)<\/span> OR (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>720<\/span> AND
\n              "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>630<\/span>)<\/span> OR (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>1320<\/span> AND "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>1230<\/span>)<\/span> OR
\n              (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>1920<\/span> AND "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>1830<\/span>)<\/span> OR (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>2520<\/span> AND
\n              "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>2430<\/span>)<\/span>)<\/span>)<\/span>
\n       filter(<\/span>(<\/span>(<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>120<\/span> AND "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>30<\/span>)<\/span> OR (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>720<\/span> AND
\n              "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>630<\/span>)<\/span> OR (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>1320<\/span> AND "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>1230<\/span>)<\/span> OR
\n              (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>1920<\/span> AND "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>1830<\/span>)<\/span> OR (<\/span>"DATA_CRIACAO"<\/span>>=<\/span>SYSDATE@!-<\/span>2520<\/span> AND
\n              "DATA_CRIACAO"<\/span><=<\/span>SYSDATE@!-<\/span>2430<\/span>)<\/span>)<\/span>)<\/span>
\n
\n
\n31<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Comparando os dois planos de execu\u00e7\u00e3o acima verificamos que quase todas as informa\u00e7\u00f5es geradas nos relat\u00f3rios dos planos de execu\u00e7\u00e3o s\u00e3o id\u00eanticas, a unica exce\u00e7\u00e3o \u00e9 o tempo real de execu\u00e7\u00e3o da consulta que diminuiu de 4,67 segundos para 5 cent\u00e9simos de segundo. Para descobrir o por que desta redu\u00e7\u00e3o dr\u00e1stica no tempo de execu\u00e7\u00e3o da consulta precisamos analisar as informa\u00e7\u00f5es geradas pelo EXASNAPPER.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> --<\/span> Gera o relat\u00f3rio com as<\/span> estat\u00edsticas da consulta no Storage Server
\nSQL ><\/span> SELECT *<\/span> FROM TABLE(<\/span>exasnap.<\/span>display_snap(<\/span>:<\/span>b,<\/span> :<\/span>e)<\/span>)<\/span>;<\/span>
\n
\nNAME
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n--<\/span> ExaSnapper v0.<\/span>81 BETA by Tanel Poder @<\/span> Enkitec -<\/span> The Exadata Experts (<\/span> http:<\/span>\/\/www.enkitec.com )<\/span>
\n---------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nDB_LAYER_IO                    DB_PHYSIO_BYTES               |<\/span>##################################################|          11753 MB          10750 MB\/sec
\n<\/span>DB_LAYER_IO                    DB_PHYSRD_BYTES               |<\/span>##################################################|          11753 MB          10750 MB\/sec
\n<\/span>DB_LAYER_IO                    DB_PHYSWR_BYTES               |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nAVOID_DISK_IO                  PHYRD_FLASH_RD_BYTES          |<\/span>                                                  |<\/span>              1<\/span> MB              1<\/span> MB\/<\/span>sec
\nAVOID_DISK_IO                  PHYRD_STORIDX_SAVED_BYTES     |<\/span>##################################################|          11744 MB          10742 MB\/sec
\n<\/span>REAL_DISK_IO                   SPIN_DISK_IO_BYTES            |<\/span>                                                  |<\/span>              8<\/span> MB              7<\/span> MB\/<\/span>sec
\nREAL_DISK_IO                   SPIN_DISK_RD_BYTES            |<\/span>                                                  |<\/span>              8<\/span> MB              7<\/span> MB\/<\/span>sec
\nREAL_DISK_IO                   SPIN_DISK_WR_BYTES            |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nREDUCE_INTERCONNECT            PRED_OFFLOADABLE_BYTES        |<\/span>##################################################|          11753 MB          10750 MB\/sec
\n<\/span>REDUCE_INTERCONNECT            TOTAL_IC_BYTES                |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nREDUCE_INTERCONNECT            SMART_SCAN_RET_BYTES          |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nREDUCE_INTERCONNECT            NON_SMART_SCAN_BYTES          |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nCELL_PROC_DEPTH                CELL_PROC_DATA_BYTES          |<\/span>                                                  |<\/span>              9<\/span> MB              8<\/span> MB\/<\/span>sec
\nCELL_PROC_DEPTH                CELL_PROC_INDEX_BYTES         |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nCLIENT_COMMUNICATION           NET_TO_CLIENT_BYTES           |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\nCLIENT_COMMUNICATION           NET_FROM_CLIENT_BYTES         |<\/span>                                                  |<\/span>              0<\/span> MB              0<\/span> MB\/<\/span>sec
\n
\n18<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

No relat\u00f3rio gerado pelo EXASNAPPER temos as informa\u00e7\u00f5es detalhadas do que ocorreu no STORAGE SERVER:<\/span><\/p>\n

DB_LAYER_IO – DB_PHYSIO_BYTES <\/span><\/strong><\/p>\n

Quantidade de I\/O em bytes que o banco de dados espera realizar com Storage Server baseado em seu dicionario de dados.<\/span><\/p>\n

DB_LAYER_IO – DB_PHYSRD_BYTES <\/strong><\/span><\/p>\n

Quantidade de bytes que o banco de dados espera recuperar do Storage Server baseado em seu dicionario de dados.<\/span><\/p>\n

AVOID_DISK_IO – PHYRD_FLASH_RD_BYTES <\/strong><\/span><\/p>\n

Quantidade de bytes lida direto do Flash Cache.<\/span><\/p>\n

AVOID_DISK_IO – PHYRD_STORIDX_SAVED_BYTES <\/strong><\/span><\/p>\n

Quantidade de bytes que n\u00e3o foi lida devido a utiliza\u00e7\u00e3o da fun\u00e7\u00e3o Storage Indexes.<\/span><\/p>\n

REAL_DISK_IO – SPIN_DISK_IO_BYTES <\/strong><\/span><\/p>\n

Quantidade de I\/O f\u00edsico em bytes que o Storage Server realizou durante a execu\u00e7\u00e3o consulta.<\/span><\/p>\n

REAL_DISK_IO – SPIN_DISK_RD_BYTES <\/strong><\/span><\/p>\n

Quantidade de leitura f\u00edsica em bytes realizada pelo Storage Server durante a execu\u00e7\u00e3o consulta.<\/span><\/p>\n

CELL_PROC_DEPTH – CELL_PROC_DATA_BYTES <\/strong><\/span><\/p>\n

Quantidade de bytes de dados processada no Storage Server.<\/span><\/p>\n

Conclus\u00e3o<\/span><\/h2>\n

\"Exasnapper<\/a><\/p>\n

Comparando as estat\u00edsticas dos relat\u00f3rios EXASNAPPER das duas execu\u00e7\u00f5es da consulta, conseguimos entender claramente por que a segunda execu\u00e7\u00e3o foi muito mais r\u00e1pida:<\/span><\/p>\n

1) Na primeira execu\u00e7\u00e3o da consulta podemos verificar atrav\u00e9s da categoria REAL_DISK_IO que o Storage Server fez a leitura f\u00edsica de 11.753 MB de dados e na segunda execu\u00e7\u00e3o se observarmos a categoria AVOID_DISK_IO verificamos que 11.744 MB simplesmente foram ignorados pelo Storage Server devido a a\u00e7\u00e3o da fun\u00e7\u00e3o Storage Indexes que evita a leitura de blocos f\u00edsicos quando estes n\u00e3o contem os dados que atendem aos crit\u00e9rios do filtro da cl\u00e1usula WHERE. Os demais blocos de dados da segunda execu\u00e7\u00e3o (9 MB) foram acessados da seguinte forma:<\/span><\/p>\n