{"id":1578,"date":"2018-05-13T20:51:49","date_gmt":"2018-05-13T23:51:49","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1578"},"modified":"2019-11-11T18:45:25","modified_gmt":"2019-11-11T21:45:25","slug":"voce-tambem-menospreza-esta-questao","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/voce-tambem-menospreza-esta-questao\/","title":{"rendered":"Voc\u00ea tambem menospreza esta quest\u00e3o?"},"content":{"rendered":"

\u00c9 comum pensar que uma instru\u00e7\u00e3o SQL que acessa uma linha numa tabela pequena \u00e9 muito r\u00e1pida e n\u00e3o devemos perder tempo em verificar se \u00e9 mais r\u00e1pido fazer um FULL SCAN na tabela ou utilizar um \u00edndice para fazer o acesso. Muitas aplica\u00e7\u00f5es utilizam tabelas pequenas como par\u00e2metros da aplica\u00e7\u00e3o e dependendo da quantidade de usu\u00e1rios e itera\u00e7\u00e3o destes com o sistema estas tabelas podem ser acessadas milhares de vezes por segundo, para estes casos o tempo e a quantidade de recursos gastos pelo banco para acessar uma linha da tabela s\u00e3o muito importantes e podem fazer muita diferen\u00e7a.<\/span><\/p>\n

Pensando na import\u00e2ncia deste assunto, neste artigo vamos fazer algumas simula\u00e7\u00f5es para responder a seguinte quest\u00e3o:<\/span><\/p>\n

Qual a melhor maneira para acessar uma linha numa tabela pequena:<\/span><\/p>\n

1) Seria atrav\u00e9s de um \u00edndice de chave primaria?<\/span>
\n 2) Seria atrav\u00e9s de um \u00edndice de chave n\u00e3o unica?<\/span>
\n 3) Seria atrav\u00e9s de uma tabela IOT?<\/span>
\n 4) Seria atrav\u00e9s de uma opera\u00e7\u00e3o TABLE FULL SCAN?<\/span><\/p>\n

1) Lendo uma linha usando \u00edndice de chave prim\u00e1ria<\/span><\/h2>\n

 <\/p>\n

Nesta primeira simula\u00e7\u00e3o vamos criar uma tabela pequena com um \u00edndice de chave prim\u00e1ria que ser\u00e1 utilizada no filtro da cl\u00e1usula WHERE para selecionar a linha desejada.<\/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
<\/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>
\nSQL ><\/span> DROP TABLE dbtw_employees purge;<\/span>
\n
\nTabela eliminada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw_employees (<\/span>EMPLOYEE_ID NUMBER(<\/span>6<\/span>,<\/span>0<\/span>)<\/span> NOT NULL<\/span> PRIMARY KEY<\/span><\/a>,<\/span>
\n  2<\/span>                               FIRST_NAME VARCHAR2(<\/span>20<\/span>)<\/span>,<\/span>
\n  3<\/span>                               LAST_NAME VARCHAR2(<\/span>25<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  4<\/span>                               EMAIL  VARCHAR2(<\/span>25<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  5<\/span>                               PHONE_NUMBER  VARCHAR2(<\/span>20<\/span>)<\/span>,<\/span>
\n  6<\/span>                               HIRE_DATE  DATE<\/span><\/a> NOT NULL<\/span>,<\/span>
\n  7<\/span>                               JOB_ID  VARCHAR2(<\/span>10<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  8<\/span>                               SALARY  NUMBER(<\/span>8<\/span>,<\/span>2<\/span>)<\/span>,<\/span>
\n  9<\/span>                               COMMISSION_PCT NUMBER(<\/span>2<\/span>,<\/span>2<\/span>)<\/span>,<\/span>
\n 10<\/span>                               MANAGER_ID NUMBER(<\/span>6<\/span>,<\/span>0<\/span>)<\/span>,<\/span>
\n 11<\/span>                               DEPARTMENT_ID NUMBER(<\/span>4<\/span>,<\/span>0<\/span>)<\/span>)<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> INSERT INTO dbtw_employees SELECT *<\/span> FROM hr.<\/span>employees where rownum <<\/span> 61<\/span>;<\/span>
\n
\n60<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COMMIT;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span>USER,<\/span> tabname=><\/span>'DBTW_EMPLOYEES'<\/span>,<\/span> method_opt=><\/span>\u2019FOR<\/span> ALL COLUMNS SIZE AUTO', cascade=>true);
\n
\nProcedimento PL\/SQL conclu\u00eddo com sucesso.
\n
\nSQL >
\nSQL > ALTER SESSION SET statistics_level=ALL;
\n
\nSess\u00e3o alterada.
\n
\nSQL >
\nSQL > SELECT \/* dbtw001  *\/ first_name, job_id, salary
\n  2    FROM dbtw_employees
\n  3   WHERE employee_id=143;
\n
\nFIRST_NAME           JOB_ID         SALARY
\n-------------------- ---------- ----------
\nRandall              ST_CLERK         2600
\n
\nSQL >
\nSQL > column sql_id new_value m_sql_id
\nSQL > column child_number new_value m_child_no
\nSQL >
\nSQL > SELECT sql_id, child_number
\n  2    FROM v$sql
\n  3   WHERE sql_text LIKE '<\/span>%<\/span>dbtw001%<\/span>'
\n  4     AND sql_text NOT LIKE '<\/span>%<\/span>v$sql<\/span>%<\/span>';
\n
\nSQL_ID        CHILD_NUMBER
\n------------- ------------
\nb5gb48ay5f5sh            0
\n
\nSQL >
\nSQL >
\nSQL > SELECT *
\n  2    FROM TABLE (dbms_xplan.display_cursor ('<\/span>&<\/span>m_sql_id',&m_child_no,'<\/span>typical iostats last'));
\nantigo   2:   FROM TABLE (dbms_xplan.display_cursor ('<\/span>&<\/span>m_sql_id',&m_child_no,'<\/span>typical iostats last'))
\nnovo   2:   FROM TABLE (dbms_xplan.display_cursor ('<\/span>b5gb48ay5f5sh',         0,'<\/span>typical iostats last'))
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\nSQL_ID  b5gb48ay5f5sh, child number 0
\n-------------------------------------
\nSELECT \/* dbtw001  *\/ first_name, job_id, salary   FROM dbtw_employees
\nWHERE employee_id=:"SYS_B_0"
\n
\nPlan hash value: 3344810242
\n
\n----------------------------------------------------------------------------------------------------------------------------------------
\n| Id  | Operation                   | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
\n----------------------------------------------------------------------------------------------------------------------------------------
\n|   0 | SELECT STATEMENT            |                |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       2 |
\n|   1 |  TABLE ACCESS BY INDEX ROWID| DBTW_EMPLOYEES |      1 |      1 |    24 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
\n|*  2 |   INDEX UNIQUE SCAN         | SYS_C00315322  |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
\n----------------------------------------------------------------------------------------------------------------------------------------
\n
\nPredicate Information (identified by operation id):
\n---------------------------------------------------
\n
\n   2 - access("EMPLOYEE_ID"=:SYS_B_0)
\n
\n
\n20 linhas selecionadas.
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Como podemos observar no plano de execu\u00e7\u00e3o da consulta, foram lidos 2 Buffers ou 2 blocos no Buffer Cache para acessar uma linha da tabela.<\/span><\/p>\n

2) Lendo uma linha usando \u00edndice de chave n\u00e3o unica<\/span><\/h2>\n

Agora vamos fazer uma simula\u00e7\u00e3o com mesma tabela s\u00f3 que desta vez vamos utilizar um \u00edndice n\u00e3o \u00fanico no mesmo campo da tabela onde na primeira simula\u00e7\u00e3o havia um \u00edndice de chave primaria.<\/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
<\/div><\/td>
SQL ><\/span> DROP TABLE dbtw_employees purge;<\/span>
\n
\nTabela eliminada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw_employees (<\/span>EMPLOYEE_ID NUMBER(<\/span>6<\/span>,<\/span>0<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  2<\/span>                               FIRST_NAME VARCHAR2(<\/span>20<\/span>)<\/span>,<\/span>
\n  3<\/span>                               LAST_NAME VARCHAR2(<\/span>25<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  4<\/span>                               EMAIL  VARCHAR2(<\/span>25<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  5<\/span>                               PHONE_NUMBER  VARCHAR2(<\/span>20<\/span>)<\/span>,<\/span>
\n  6<\/span>                               HIRE_DATE  DATE<\/span><\/a> NOT NULL<\/span>,<\/span>
\n  7<\/span>                               JOB_ID  VARCHAR2(<\/span>10<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  8<\/span>                               SALARY  NUMBER(<\/span>8<\/span>,<\/span>2<\/span>)<\/span>,<\/span>
\n  9<\/span>                               COMMISSION_PCT NUMBER(<\/span>2<\/span>,<\/span>2<\/span>)<\/span>,<\/span>
\n 10<\/span>                               MANAGER_ID NUMBER(<\/span>6<\/span>,<\/span>0<\/span>)<\/span>,<\/span>
\n 11<\/span>                               DEPARTMENT_ID NUMBER(<\/span>4<\/span>,<\/span>0<\/span>)<\/span>)<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> INSERT INTO dbtw_employees SELECT *<\/span> FROM hr.<\/span>employees where rownum <<\/span> 61<\/span>;<\/span>
\n
\n60<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COMMIT;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE INDEX dbtw_employees_idx on dbtw_employees(<\/span>EMPLOYEE_ID)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span>USER,<\/span> tabname=><\/span>'DBTW_EMPLOYEES'<\/span>,<\/span> method_opt=><\/span>\u2019FOR<\/span> ALL COLUMNS SIZE AUTO', cascade=>true);
\n
\nProcedimento PL\/SQL conclu\u00eddo com sucesso.
\n
\nSQL >
\nSQL > ALTER SESSION SET statistics_level=ALL;
\n
\nSess\u00e3o alterada.
\n
\nSQL >
\nSQL > SELECT \/* dbtw002  *\/ first_name, job_id, salary
\n  2    FROM dbtw_employees
\n  3   WHERE employee_id=143;
\n
\nFIRST_NAME           JOB_ID         SALARY
\n-------------------- ---------- ----------
\nRandall              ST_CLERK         2600
\n
\nSQL >
\nSQL > column sql_id new_value m_sql_id
\nSQL > column child_number new_value m_child_no
\nSQL >
\nSQL > SELECT sql_id, child_number
\n  2    FROM v$sql
\n  3   WHERE sql_text LIKE '<\/span>%<\/span>dbtw002%<\/span>'
\n  4     AND sql_text NOT LIKE '<\/span>%<\/span>v$sql<\/span>%<\/span>';
\n
\nSQL_ID        CHILD_NUMBER
\n------------- ------------
\n93pdp53t14ukg            0
\n
\nSQL >
\nSQL >
\nSQL > SELECT *
\n  2    FROM TABLE (dbms_xplan.display_cursor ('<\/span>&<\/span>m_sql_id',&m_child_no,'<\/span>typical iostats last'));
\nantigo   2:   FROM TABLE (dbms_xplan.display_cursor ('<\/span>&<\/span>m_sql_id',&m_child_no,'<\/span>typical iostats last'))
\nnovo   2:   FROM TABLE (dbms_xplan.display_cursor ('<\/span>93pdp53t14ukg',         0,'<\/span>typical iostats last'))
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\nSQL_ID  93pdp53t14ukg, child number 0
\n-------------------------------------
\nSELECT \/* dbtw002  *\/ first_name, job_id, salary   FROM dbtw_employees
\nWHERE employee_id=:"SYS_B_0"
\n
\nPlan hash value: 3843110344
\n
\n--------------------------------------------------------------------------------------------------------------------------------------------
\n| Id  | Operation                   | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
\n--------------------------------------------------------------------------------------------------------------------------------------------
\n|   0 | SELECT STATEMENT            |                    |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       3 |
\n|   1 |  TABLE ACCESS BY INDEX ROWID| DBTW_EMPLOYEES     |      1 |      1 |    24 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
\n|*  2 |   INDEX RANGE SCAN          | DBTW_EMPLOYEES_IDX |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
\n--------------------------------------------------------------------------------------------------------------------------------------------
\n
\nPredicate Information (identified by operation id):
\n---------------------------------------------------
\n
\n   2 - access("EMPLOYEE_ID"=:SYS_B_0)
\n
\n
\n20 linhas selecionadas.
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Nesta simula\u00e7\u00e3o o resultado foi pior, pois foram necess\u00e1rias a leitura de 3 Buffers contra a leitura de 2 na primeira simula\u00e7\u00e3o.<\/span><\/p>\n

 <\/p>\n

3) Lendo uma linha usando uma tabela IOT<\/span><\/h2>\n

 <\/p>\n

Nesta simula\u00e7\u00e3o vamos utilizar uma tabela IOT (Index-Organized Table) com a mesma estrutura da tabela utilizada nas simula\u00e7\u00f5es anteriores e a mesma quantidade de linhas.<\/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> DROP TABLE dbtw_employees purge;<\/span>
\n
\nTabela eliminada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw_employees (<\/span>EMPLOYEE_ID NUMBER(<\/span>6<\/span>,<\/span>0<\/span>)<\/span> NOT NULL<\/span> PRIMARY KEY<\/span><\/a>,<\/span>
\n  2<\/span>                               FIRST_NAME VARCHAR2(<\/span>20<\/span>)<\/span>,<\/span>
\n  3<\/span>                               LAST_NAME VARCHAR2(<\/span>25<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  4<\/span>                               EMAIL  VARCHAR2(<\/span>25<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  5<\/span>                               PHONE_NUMBER  VARCHAR2(<\/span>20<\/span>)<\/span>,<\/span>
\n  6<\/span>                               HIRE_DATE  DATE<\/span><\/a> NOT NULL<\/span>,<\/span>
\n  7<\/span>                               JOB_ID  VARCHAR2(<\/span>10<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  8<\/span>                               SALARY  NUMBER(<\/span>8<\/span>,<\/span>2<\/span>)<\/span>,<\/span>
\n  9<\/span>                               COMMISSION_PCT NUMBER(<\/span>2<\/span>,<\/span>2<\/span>)<\/span>,<\/span>
\n 10<\/span>                               MANAGER_ID NUMBER(<\/span>6<\/span>,<\/span>0<\/span>)<\/span>,<\/span>
\n 11<\/span>                               DEPARTMENT_ID NUMBER(<\/span>4<\/span>,<\/span>0<\/span>)<\/span>)<\/span>
\n 12<\/span>                               ORGANIZATION INDEX;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> INSERT INTO dbtw_employees SELECT *<\/span> FROM hr.<\/span>employees where rownum <<\/span> 61<\/span>;<\/span>
\n
\n60<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COMMIT;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span>USER,<\/span> tabname=><\/span>'DBTW_EMPLOYEES'<\/span>,<\/span> method_opt=><\/span>\u2019FOR<\/span> ALL COLUMNS SIZE AUTO', cascade=>true);
\n
\nProcedimento PL\/SQL conclu\u00eddo com sucesso.
\n
\nSQL >
\nSQL >   ALTER SESSION SET statistics_level=ALL;
\n
\nSess\u00e3o alterada.
\n
\nSQL >
\nSQL > SELECT \/* dbtw003  *\/ first_name, job_id, salary
\n  2    FROM dbtw_employees
\n  3   WHERE employee_id=143;
\n
\nFIRST_NAME           JOB_ID         SALARY
\n-------------------- ---------- ----------
\nRandall              ST_CLERK         2600
\n
\nSQL >
\nSQL > column sql_id new_value m_sql_id
\nSQL > column child_number new_value m_child_no
\nSQL >
\nSQL > SELECT sql_id, child_number
\n  2    FROM v$sql
\n  3   WHERE sql_text LIKE '<\/span>%<\/span>dbtw003%<\/span>'
\n  4     AND sql_text NOT LIKE '<\/span>%<\/span>v$sql<\/span>%<\/span>';
\n
\nSQL_ID        CHILD_NUMBER
\n------------- ------------
\n4q5p4cbkrm6wj            0
\n
\nSQL >
\nSQL >
\nSQL > SELECT *
\n  2    FROM TABLE (dbms_xplan.display_cursor ('<\/span>&<\/span>m_sql_id',&m_child_no,'<\/span>typical iostats last'));
\nantigo   2:   FROM TABLE (dbms_xplan.display_cursor ('<\/span>&<\/span>m_sql_id',&m_child_no,'<\/span>typical iostats last'))
\nnovo   2:   FROM TABLE (dbms_xplan.display_cursor ('<\/span>4q5p4cbkrm6wj',         0,'<\/span>typical iostats last'))
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\nSQL_ID  4q5p4cbkrm6wj, child number 0
\n-------------------------------------
\nSELECT \/* dbtw003  *\/ first_name, job_id, salary   FROM dbtw_employees
\nWHERE employee_id=:"SYS_B_0"
\n
\nPlan hash value: 4091736222
\n
\n-----------------------------------------------------------------------------------------------------------------------
\n| Id  | Operation         | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
\n-----------------------------------------------------------------------------------------------------------------------
\n|   0 | SELECT STATEMENT  |                    |      1 |        |       |     1 (100)|      1 |00:00:00.01 |       1 |
\n|*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_488246 |      1 |      1 |    24 |     0   (0)|      1 |00:00:00.01 |       1 |
\n-----------------------------------------------------------------------------------------------------------------------
\n
\nPredicate Information (identified by operation id):
\n---------------------------------------------------
\n
\n   1 - access("EMPLOYEE_ID"=:SYS_B_0)
\n
\n
\n19 linhas selecionadas.
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando o plano de execu\u00e7\u00e3o parece que encontramos a vencedora dessa disputa, utilizando a tabela IOT para recuperar uma linha da tabela foi necess\u00e1ria a leitura de 1 Buffer.<\/p>\n

4) Lendo uma linha da tabela usando TABLE FULL SCAN<\/span><\/h2>\n

Por ultimo vamos criar a mesma tabela sem \u00edndices e verficar qual ser\u00e1 o desempenho de um TABLE FULL SCAN para recuperar a mesma linha da tabela.<\/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> DROP TABLE dbtw_employees purge;<\/span>
\n
\nTabela eliminada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw_employees  (<\/span>EMPLOYEE_ID NUMBER(<\/span>6<\/span>,<\/span>0<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  2<\/span>                               FIRST_NAME VARCHAR2(<\/span>20<\/span>)<\/span>,<\/span>
\n  3<\/span>                               LAST_NAME VARCHAR2(<\/span>25<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  4<\/span>                               EMAIL  VARCHAR2(<\/span>25<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  5<\/span>                               PHONE_NUMBER  VARCHAR2(<\/span>20<\/span>)<\/span>,<\/span>
\n  6<\/span>                               HIRE_DATE  DATE<\/span><\/a> NOT NULL<\/span>,<\/span>
\n  7<\/span>                               JOB_ID  VARCHAR2(<\/span>10<\/span>)<\/span> NOT NULL<\/span>,<\/span>
\n  8<\/span>                               SALARY  NUMBER(<\/span>8<\/span>,<\/span>2<\/span>)<\/span>,<\/span>
\n  9<\/span>                               COMMISSION_PCT NUMBER(<\/span>2<\/span>,<\/span>2<\/span>)<\/span>,<\/span>
\n 10<\/span>                               MANAGER_ID NUMBER(<\/span>6<\/span>,<\/span>0<\/span>)<\/span>,<\/span>
\n 11<\/span>                               DEPARTMENT_ID NUMBER(<\/span>4<\/span>,<\/span>0<\/span>)<\/span>)<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> INSERT INTO dbtw_employees SELECT *<\/span> FROM hr.<\/span>employees where rownum <<\/span> 61<\/span>;<\/span>
\n
\n60<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COMMIT;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span>USER,<\/span> tabname=><\/span>'DBTW_EMPLOYEES'<\/span>,<\/span> method_opt=><\/span>\u2019FOR<\/span> ALL COLUMNS SIZE AUTO', cascade=>true);
\n
\nProcedimento PL\/SQL conclu\u00eddo com sucesso.
\n
\nSQL >
\nSQL > ALTER SESSION SET statistics_level=ALL;
\n
\nSess\u00e3o alterada.
\n
\nSQL >
\nSQL > SELECT \/* dbtw004  *\/ first_name, job_id, salary
\n  2    FROM dbtw_employees
\n  3   WHERE employee_id=143;
\n
\nFIRST_NAME           JOB_ID         SALARY
\n-------------------- ---------- ----------
\nRandall              ST_CLERK         2600
\n
\nSQL >
\nSQL > column sql_id new_value m_sql_id
\nSQL > column child_number new_value m_child_no
\nSQL >
\nSQL > SELECT sql_id, child_number
\n  2    FROM v$sql
\n  3   WHERE sql_text LIKE '<\/span>%<\/span>dbtw004%<\/span>'
\n  4     AND sql_text NOT LIKE '<\/span>%<\/span>v$sql<\/span>%<\/span>';
\n
\nSQL_ID        CHILD_NUMBER
\n------------- ------------
\n2vnw6mgfa490h            0
\n
\nSQL >
\nSQL >
\nSQL > SELECT *
\n  2    FROM TABLE (dbms_xplan.display_cursor ('<\/span>&<\/span>m_sql_id',&m_child_no,'<\/span>typical iostats last'));
\nantigo   2:   FROM TABLE (dbms_xplan.display_cursor ('<\/span>&<\/span>m_sql_id',&m_child_no,'<\/span>typical iostats last'))
\nnovo   2:   FROM TABLE (dbms_xplan.display_cursor ('<\/span>2vnw6mgfa490h',         0,'<\/span>typical iostats last'))
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\nSQL_ID  2vnw6mgfa490h, child number 0
\n-------------------------------------
\nSELECT \/* dbtw004  *\/ first_name, job_id, salary   FROM dbtw_employees
\nWHERE employee_id=:"SYS_B_0"
\n
\nPlan hash value: 1134787269
\n
\n------------------------------------------------------------------------------------------------------------------------------
\n| Id  | Operation         | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
\n------------------------------------------------------------------------------------------------------------------------------
\n|   0 | SELECT STATEMENT  |                |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       7 |
\n|*  1 |  TABLE ACCESS FULL| DBTW_EMPLOYEES |      1 |      1 |    24 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
\n------------------------------------------------------------------------------------------------------------------------------
\n
\nPredicate Information (identified by operation id):
\n---------------------------------------------------
\n
\n   1 - filter("EMPLOYEE_ID"=:SYS_B_0)
\n
\n
\n19 linhas selecionadas.
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Consultando o plano de execu\u00e7\u00e3o verificamos que o desempenho de uma opera\u00e7\u00e3o TABLE FULL SCAN no nosso caso \u00e9 muito ruim, foram necess\u00e1rias a leitura de 7 Buffers para retorna um linha da tabela.<\/span><\/p>\n

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

O melhor m\u00e9todo de acesso para recuperar uma linha da tabela em nossas simula\u00e7\u00f5es foi a utiliza\u00e7\u00e3o da tabela IOT. Nos pr\u00f3ximos trabalhos de Tuning quando se deparar com uma tabela pequena que seja muito acessada pela aplica\u00e7\u00e3o e que consuma boa parte dos recursos do banco, considere a utiliza\u00e7\u00e3o de tabela IOT.<\/span><\/p>\n

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

https:\/\/docs.oracle.com\/cd\/E25054_01\/server.1111\/e25789\/indexiot.htm<\/a><\/span><\/p>\n

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

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

\u00c9 comum pensar que uma instru\u00e7\u00e3o SQL que acessa uma linha numa tabela pequena \u00e9 muito r\u00e1pida e n\u00e3o devemos perder tempo em verificar se \u00e9 mais r\u00e1pido fazer um FULL SCAN na tabela ou utilizar um \u00edndice para fazer o acesso. Muitas aplica\u00e7\u00f5es utilizam tabelas pequenas como par\u00e2metros da aplica\u00e7\u00e3o e dependendo da quantidade de usu\u00e1rios e itera\u00e7\u00e3o destes<\/p>\n","protected":false},"author":2,"featured_media":1581,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35],"tags":[6,36,197,82,10,11,198],"class_list":["post-1578","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-execution-plan","tag-cbo","tag-execution-plan","tag-iot-table","tag-optimizer","tag-otimizador","tag-plano-de-execucao","tag-tabela-iot"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1578","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=1578"}],"version-history":[{"count":23,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1578\/revisions"}],"predecessor-version":[{"id":2076,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1578\/revisions\/2076"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1581"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1578"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1578"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1578"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}