{"id":790,"date":"2016-06-19T21:53:54","date_gmt":"2016-06-20T00:53:54","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=790"},"modified":"2019-11-11T18:55:33","modified_gmt":"2019-11-11T21:55:33","slug":"qual-e-o-segredo-da-alta-performace-do-exadata","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/qual-e-o-segredo-da-alta-performace-do-exadata\/","title":{"rendered":"Qual \u00e9 o segredo da alta performace do Exadata?"},"content":{"rendered":"

Com o crescimento dos neg\u00f3cios de uma empresa e a automatiza\u00e7\u00e3o de seus processos, aumenta o volume de dados que precisam ser armazenados, esse aumento na quantidade de dados acaba muitas vezes prejudicando o tempo de resposta das aplica\u00e7\u00f5es. Se a sua empresa possui Banco de dados com tamanhos em Terabytes, os usu\u00e1rios das aplica\u00e7\u00f5es reclamam de performance, voc\u00ea j\u00e1 realizou um bom trabalho de tuning na sua plataforma e o problema de performance tem impacto nos neg\u00f3cios da empresa, voc\u00ea deveria considerar a possibilidade de utilizar a solu\u00e7\u00e3o Exadata.<\/span><\/p>\n

Neste artigo vamos falar um pouco sobre a arquitetura do Exadata e porque sua performance \u00e9 muito superior \u00e0s arquiteturas tradicionais.<\/span><\/p>\n

Arquiteturas Tradicionais<\/span><\/h2>\n

Numa arquitetura tradicional normalmente instalamos o Banco de dados Oracle (Inst\u00e2ncias L\u00f3gicas) em servidores com sistema operacional Unix-like ou Microsoft e os dados s\u00e3o armazenados em servidores Storage. Nessa arquitetura os dados solicitados por uma consulta precisam ser transferidos do Storage para os servidores de banco, onde s\u00e3o\u00a0tratados pelas inst\u00e2ncias para gerar o resultado final da consulta, quando essa consulta precisa acessar tabelas com grandes volumes de dados a\u00a0transfer\u00eancia de dados torna-se um gargalo, pois ela \u00e9 feita pela rede.<\/span><\/p>\n

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

Arquitetura Exadata<\/span><\/h2>\n

A arquitetura do Exadata foi concebida para resolver esse problema de gargalo de transfer\u00eancia de dados do Storage para o servidor de Banco, para atingir esse objetivo a Oracle utilizou duas estrat\u00e9gias:<\/span><\/p>\n

1.<\/strong> Diminuir a quantidade de dados que s\u00e3o transferidos do Storage para o Banco.<\/span>
\n2.<\/strong> Aumentar a velocidade de transfer\u00eancia de dados entre Storage e o Banco.<\/span><\/p>\n

Para diminuir a quantidade de dados transferidos do Storage para o Banco foi necess\u00e1rio agregar algumas fun\u00e7\u00f5es do banco de dados ao Storage, com isso uma parte das tarefas realizadas durante a execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL passaram a ser executadas no servidor Storage por processos “Cell offloading”. Na gera\u00e7\u00e3o X5-2 os Servidores Storage do Exadata realizam 7 fun\u00e7\u00f5es que numa arquitetura tradicional seriam realizadas pelo servidor de banco de dados, as duas fun\u00e7\u00f5es que mais contribuem para a redu\u00e7\u00e3o da quantidade de dados que s\u00e3o transferidos do Storage para o banco de dados s\u00e3o:<\/span><\/p>\n

1.<\/strong> Column Projection<\/strong>: O servidor Storage envia para o banco somente as colunas que fazem parte da lista da clausula “SELECT”, ou seja, se a tabela acessada possui 50 colunas e a consulta tem somente 5\u00a0colunas na clausula “SELECT”, somente essas 5 colunas ser\u00e3o transferidas do Storage para o Banco.<\/span><\/p>\n

2.<\/strong> Predicate Filtering<\/strong>: O servidor Storage restringe\u00a0os registros que ser\u00e3o enviados ao servidor de banco baseado nos filtros da cla\u00fasula WHERE da instru\u00e7\u00e3o SQL, numa arquitetura tradicional os registros que n\u00e3o s\u00e3o filtrados por um \u00edndice s\u00e3o enviados para o Buffer Cache onde s\u00e3o filtrados pelo banco.<\/span><\/p>\n

Para aumentar a velocidade de transfer\u00eancia de dados entre o Storage e o Banco, a arquitetura Exadata disp\u00f5em de 3 recursos:<\/span><\/p>\n

1.<\/strong> Uma rede InfiniBand<\/strong> que prove uma capacidade de transfer\u00eancia de dados do Storage para o Banco de 40 Gb\/Sec, numa arquitetura tradicional a melhor op\u00e7\u00e3o dispon\u00edvel \u00e9 uma rede Fiber Channel que prove uma capacidade de transfer\u00eancia de 16 Gb\/sec.<\/span><\/p>\n

2.<\/strong> Os servidores de Storage disp\u00f5em de \u00e1reas de mem\u00f3ria flash<\/strong> chamadas ‘Cell Flash Cache’ que diminuem a necessidade de acesso mec\u00e2nico aos discos aumentando a velocidade de acesso aos dados.<\/span><\/p>\n

3.<\/strong> A fun\u00e7\u00e3o Storage Index<\/strong> tambem contribui para o aumento da velocidade de transfer\u00eancia dos dados, pois diminui o numero de blocos f\u00edsicos acessados\u00a0para leitura dos dados no Storage.<\/span><\/p>\n

Smart Scan<\/span><\/h2>\n

A arquitetura de Hardware do Exadata foi concebida para prover alta disponibilidade e alta performance, mas na parte de performance o destaque n\u00e3o esta no Hardware e sim no Software, as duas fun\u00e7\u00f5es mencionadas acima que visam a redu\u00e7\u00e3o do volume de dados transferidos do Storage para o Banco de dados s\u00e3o tamb\u00e9m conhecidas como “Smart Scan”, elas s\u00e3o as respons\u00e1veis pelo fant\u00e1stico desempenho de performance do Exadata. Para demostrar isso vamos simular uma consulta no Exadata utilizando somente os recursos de Hardware e em seguida vamos executar a mesma consulta utilizando tambem o “Smart Scan”.<\/span><\/p>\n

Para realizar esta simula\u00e7\u00e3o vamos criar uma tabela de 5 GB de dados e n\u00e3o vamos utilizar \u00edndice:<\/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
<\/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> create table dbtw01 as<\/span>
\n  2<\/span>     select 'DBTimeWizard - Performance and Tuning'<\/span> as<\/span> produto,<\/span>
\n  3<\/span>            mod(<\/span>rownum,<\/span>5<\/span>)<\/span>                            as<\/span> codigo,<\/span>
\n  4<\/span>            mod(<\/span>rownum,<\/span>1000<\/span>)<\/span>                         as<\/span> cliente_id ,<\/span>
\n  5<\/span>            5000<\/span>                                     as<\/span> total_vendas,<\/span>
\n  6<\/span>            trunc(<\/span>sysdate -<\/span> 9999<\/span> +<\/span> mod(<\/span>rownum,<\/span>10000<\/span>)<\/span>)<\/span> as<\/span> data_venda
\n  7<\/span>       from dual connect by level<=<\/span>2e7<\/span>;<\/span>
\n
\nTable created.<\/span>
\n
\nSQL ><\/span> alter table dbtw01 nologging;<\/span>
\n
\nTable altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> insert \/*+ append *\/<\/span> into dbtw01 select *<\/span> from dbtw01;<\/span>
\n
\n20000000<\/span> rows created.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> commit;<\/span>
\n
\nCommit complete.<\/span>
\n
\nSQL ><\/span> insert \/*+ append *\/<\/span> into dbtw01 select *<\/span> from dbtw01;<\/span>
\n
\n40000000<\/span> rows created.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> commit;<\/span>
\n
\nCommit complete.<\/span>
\n
\nSQL ><\/span>
\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       5.1<\/span>
\n
\nSQL ><\/span>
\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>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span> alter table dbtw01 logging;<\/span>
\n
\nTable altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select count<\/span><\/a>(<\/span>1<\/span>)<\/span> from DBTW01;<\/span>
\n
\n  COUNT<\/span><\/a>(<\/span>1<\/span>)<\/span>
\n----------<\/span>
\n  80000000<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Simula\u00e7\u00e3o sem Smart Scan<\/span><\/h3>\n

Para desativar o Smart Scan vamos utilizar o par\u00e2metro “cell_offload_processing=false”, e para garantir que o resultado da consulta n\u00e3o seja afetado por dados armazenados no buffer do banco de dados vamos executar um comando para limpar o Buffer Cache.<\/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
<\/div><\/td>
SQL ><\/span> alter system<\/span><\/a> flush<\/span><\/a> buffer_cache;<\/span>
\n
\nSystem<\/span><\/a> altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> alter session set cell_offload_processing=<\/span>false<\/span>;<\/span>
\n
\nSession altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select \/* NO_SMART_SCAN *\/<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span> from DBTW01 where codigo=<\/span>1<\/span>;<\/span>
\n
\n  COUNT<\/span><\/a>(<\/span>*<\/span>)<\/span>
\n----------<\/span>
\n  16000000<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>   FROM v$sql<\/span>
\n  3<\/span>  WHERE sql_text LIKE '%NO_SMART_SCAN%'<\/span>
\n  4<\/span>    AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\nf60zgw6916cum               0<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/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  f60zgw6916cum,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* NO_SMART_SCAN *\/<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span> from DBTW01 where codigo=:<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2690647991<\/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>   182K(<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>14.80<\/span> |<\/span>     671K|<\/span>    671K|<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>            |<\/span>        |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>     3<\/span> |<\/span>            |<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>14.80<\/span> |<\/span>     671K|<\/span>    671K|<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS STORAGE FULL|<\/span> DBTW01 |<\/span>      1<\/span> |<\/span>     16M|<\/span>    46M|<\/span>   182K  (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>36<\/span>:<\/span>35<\/span> |<\/span>     16M|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>14.25<\/span> |<\/span>     671K|<\/span>    671K|<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> filter(<\/span>"CODIGO"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n19<\/span> rows selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> col SQL_TEXT for<\/span> a80;<\/span>
\nSQL ><\/span> select SQL_ID,<\/span>
\n  2<\/span>         IO_CELL_OFFLOAD_ELIGIBLE_BYTES eligible,<\/span>
\n  3<\/span>         IO_INTERCONNECT_BYTES actual,<\/span>
\n  4<\/span>         sql_text
\n  5<\/span>    from v$sql<\/span>
\n  6<\/span>   where SQL_ID in (<\/span>'&m_sql_id'<\/span>)<\/span>;<\/span>
\n
\nSQL_ID             ELIGIBLE     ACTUAL SQL_TEXT
\n----------------<\/span> ----------<\/span> ----------<\/span> --------------------------------------------------------------------------------<\/span>
\nf60zgw6916cum             0<\/span> 5502640128<\/span> select \/* NO_SMART_SCAN *\/<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span> from DBTW01 where codigo=:<\/span>"SYS_B_0"<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select name,<\/span> value
\n  2<\/span>    from v$mystat<\/span> s,<\/span> v$statname<\/span> n
\n  3<\/span>   where s.<\/span>statistic# = n.statistic#
\n<\/span>  4<\/span>     and name like '%storage index%'<\/span>;<\/span>
\n
\nNAME                                                                  VALUE
\n----------------------------------------------------------------<\/span> ----------<\/span>
\ncell physical IO bytes saved by storage index                             0<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span> select round<\/span><\/a>(<\/span>bytes\/<\/span>1024<\/span>\/<\/span>1024<\/span>\/<\/span>1024<\/span>,<\/span>1<\/span>)<\/span> "Tamanho GB"<\/span> from user_segments where segment_name =<\/span> 'DBTW01'<\/span>;<\/span>
\n
\nTamanho GB
\n----------<\/span>
\n       5.1<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span> select round<\/span><\/a>(<\/span>(<\/span>(<\/span>671<\/span>*<\/span>1024<\/span>)<\/span>*<\/span>8124<\/span>)<\/span>\/<\/span>1024<\/span>\/<\/span>1024<\/span>\/<\/span>1024<\/span>,<\/span>1<\/span>)<\/span> "Buffers GB"<\/span> from dual;<\/span>
\n
\nBuffers GB
\n----------<\/span>
\n       5.2<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Simula\u00e7\u00e3o com Smart Scan<\/span><\/h3>\n

Agora vamos ativar o Smart Scan utilizando o par\u00e2metro “cell_offload_processing=true”, vamos limpar o Buffer Cache do banco e executar a mesma consulta novamente.<\/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
<\/div><\/td>
SQL ><\/span> alter system<\/span><\/a> flush<\/span><\/a> buffer_cache;<\/span>
\n
\nSystem<\/span><\/a> altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> alter session set cell_offload_processing=<\/span>true<\/span>;<\/span>
\n
\nSession altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\nSQL ><\/span> select \/* WITH_SMART_SCAN *\/<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span> from DBTW01 where codigo=<\/span>1<\/span>;<\/span>
\n
\n  COUNT<\/span><\/a>(<\/span>*<\/span>)<\/span>
\n----------<\/span>
\n  16000000<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>   FROM v$sql<\/span>
\n  3<\/span>  WHERE sql_text LIKE '%WITH_SMART_SCAN%'<\/span>
\n  4<\/span>    AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID           CHILD_NUMBER
\n----------------<\/span> ------------<\/span>
\n5tz9g9gga7asa               0<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/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  5tz9g9gga7asa,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nselect \/* WITH_SMART_SCAN *\/<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span> from DBTW01 where
\ncodigo=:<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2690647991<\/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>   182K(<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.48<\/span> |<\/span>     671K|<\/span>    671K|<\/span>
\n|<\/span>   1<\/span> |<\/span>  SORT<\/span><\/a> AGGREGATE<\/span><\/a>            |<\/span>        |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>     3<\/span> |<\/span>            |<\/span>          |<\/span>      1<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>01.48<\/span> |<\/span>     671K|<\/span>    671K|<\/span>
\n|*<\/span>  2<\/span> |<\/span>   TABLE ACCESS STORAGE FULL|<\/span> DBTW01 |<\/span>      1<\/span> |<\/span>     16M|<\/span>    46M|<\/span>   182K  (<\/span>1<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>36<\/span>:<\/span>35<\/span> |<\/span>     16M|<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.95<\/span> |<\/span>     671K|<\/span>    671K|<\/span>
\n----------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> storage(<\/span>"CODIGO"<\/span>=:<\/span>SYS_B_0)<\/span>
\n       filter(<\/span>"CODIGO"<\/span>=:<\/span>SYS_B_0)<\/span>
\n
\n
\n21<\/span> rows selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select SQL_ID,<\/span>
\n  2<\/span>         IO_CELL_OFFLOAD_ELIGIBLE_BYTES eligible,<\/span>
\n  3<\/span>         IO_INTERCONNECT_BYTES actual,<\/span>
\n  4<\/span>         100<\/span>*<\/span>(<\/span>IO_CELL_OFFLOAD_ELIGIBLE_BYTES-<\/span>IO_INTERCONNECT_BYTES)<\/span>\/<\/span>IO_CELL_OFFLOAD_ELIGIBLE_BYTES "IO_SAVED_%"<\/span>,<\/span>
\n  5<\/span>         sql_text
\n  6<\/span>    from v$sql<\/span>
\n  7<\/span>   where SQL_ID in (<\/span>'&m_sql_id'<\/span>)<\/span>;<\/span>
\n
\nSQL_ID             ELIGIBLE     ACTUAL IO_SAVED_%<\/span> SQL_TEXT
\n----------------<\/span> ----------<\/span> ----------<\/span> ----------<\/span> --------------------------------------------------------------------------------<\/span>
\n5tz9g9gga7asa    5502566400<\/span>  191218504<\/span> 96.5249215<\/span> select \/* WITH_SMART_SCAN *\/<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span> from DBTW01 where codigo=:<\/span>"SYS_B_0"<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select name,<\/span> value
\n  2<\/span>    from v$mystat<\/span> s,<\/span> v$statname<\/span> n
\n  3<\/span>   where s.<\/span>statistic# = n.statistic#
\n<\/span>  4<\/span>     and name like '%storage index%'<\/span>;<\/span>
\n
\nNAME                                                                  VALUE
\n----------------------------------------------------------------<\/span> ----------<\/span>
\ncell physical IO bytes saved by storage index                             0<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Nesta segunda execu\u00e7\u00e3o utilizando o Smart Scan podemos observar duas mudan\u00e7as no plano de execu\u00e7\u00e3o:<\/span><\/p>\n

1. O tempo de dura\u00e7\u00e3o da consulta caiu de 14,8 segundos para 1,5 segundos.<\/span>
\n2. Na se\u00e7\u00e3o “Predicate Information” aparece a informa\u00e7\u00e3o de que o filtro da cla\u00fasula WHERE foi aplicado a n\u00edvel de Storage.<\/span><\/p>\n

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

A alta performace do Exadata se deve ao fato de que todos os componentes de Hardware e Software foram desenhados para trabalhar juntos em perfeita sintonia, isso s\u00f3 foi poss\u00edvel pois todos os componentes da arquitetura Exadata pertencem ao mesmo fornecedor. Nessa arquitetura o componente mais importante na melhoria da performance \u00e9 o Software Storage Cell que permitiu a execu\u00e7\u00e3o de algumas fun\u00e7\u00f5es do banco direto no servidor Storage eliminando muito trabalho desnecess\u00e1rio.<\/span><\/p>\n

Refer\u00eancias<\/span><\/h2>\n

http:\/\/www.oracle.com\/technetwork\/database\/exadata\/exadata-technical-whitepaper-134575.pdf<\/a><\/strong><\/span><\/p>\n

 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

Com o crescimento dos neg\u00f3cios de uma empresa e a automatiza\u00e7\u00e3o de seus processos, aumenta o volume de dados que precisam ser armazenados, esse aumento na quantidade de dados acaba muitas vezes prejudicando o tempo de resposta das aplica\u00e7\u00f5es. Se a sua empresa possui Banco de dados com tamanhos em Terabytes, os usu\u00e1rios das aplica\u00e7\u00f5es reclamam de performance, voc\u00ea j\u00e1<\/p>\n","protected":false},"author":2,"featured_media":791,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[142],"tags":[150,146,40,143,148,147,145,144,149],"class_list":["post-790","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-exadata","tag-alta-performace","tag-cell_offload_processing","tag-column-projection","tag-exadata","tag-infiniband","tag-predicate-filtering","tag-smart-scan","tag-storage-cell","tag-storage-index"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/790","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=790"}],"version-history":[{"count":18,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/790\/revisions"}],"predecessor-version":[{"id":2093,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/790\/revisions\/2093"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/791"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=790"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=790"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=790"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}