Олег Коротков Curious Cases of Oracle Database

Содержание Scalar Subquery Caching – когда он не нужен Пример преимущества «неэффективной архитектуры» B*tree индексы, отсортированные по убыванию Они «хранят значения NULL» Уникальные индексы отличаются от asc-индексов

1. Scalar Subquery Caching Механизм кэширования результата скалярного подзапроса посредством хэш-таблицы Литература: htmlhttp:// html Льюис. Основы стоимостной оптимизации. Глава 9. Скалярные подзапросы. Стр 250.

Практическое применение (volume for a month) : F(x): Statistics recursive calls 90 db block gets consistent gets 7 physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) 0 sorts (disk) rows processed 1. Scalar Subquery Caching

Практическое применение: Select f(x) from dual: Statistics recursive calls - 27,6% 102 db block gets consistent gets - 12,7% 9 physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) - 27,6% 0 sorts (disk) rows processed 1. Scalar Subquery Caching

Практическое применение: Select f(x) from dual + order by …: Statistics recursive calls - 98,3% 4 db block gets consistent gets - 58,9% 12 physical reads 1012 redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client 8806 sorts (memory) - 98,3% 0 sorts (disk) rows processed 1. Scalar Subquery Caching

Том Кайт ( ): there is a good reason to always wrap your function call in a SELECT FROM DUAL. Ive gotten into the habit over the years of never coding select * from t where column = plsql_function(..); but rather including a simple scalar subquery select * from t where column = (select plsql_function(...) from dual); to get the performance benefit of the scalar subquery cache.

1. Scalar Subquery Caching Пример «неудачного» использования: SELECT FROM executions E JOIN execution_legs EL ON (E.Execution_ID = EL.Execution_ID) WHERE E.EXECUTION_ID = EL.EXECUTION_ID AND E.EXECUTION_TIME > (select trunc(Sysdate) from dual)

1. Scalar Subquery Caching Пример «неудачного» использования, план выполнения: | Id | Operation | Name | Rows | |* 2 | HASH JOIN | | 9003K| |* 3 | TABLE ACCESS BY INDEX ROWID| EXECUTIONS | 6635K| |* 4 | INDEX RANGE SCAN | EXEC_ET | 4828K| | 5 | FAST DUAL | | 1 | | 6 | INDEX FAST FULL SCAN | EL_PRIM_KEY | 125M|

1. Scalar Subquery Caching Заменим условие на E.EXECUTION_TIME > trunc(Sysdate): | Id | Operation | Name | Rows | | 2 | NESTED LOOPS | | 3 | |* 3 | TABLE ACCESS BY INDEX ROWID| EXECUTIONS | 2 | |* 4 | INDEX RANGE SCAN | EXEC_ET | 8 | |* 5 | INDEX RANGE SCAN | EL_PRIM_KEY | 1 |

1. Scalar Subquery Caching E.EXECUTION_TIME > trunc(Sysdate): для расчета Cardinality Estimation используется гистограмма.

1. Scalar Subquery Caching E.EXECUTION_TIME > trunc(Sysdate): для расчета Cardinality Estimation используется гистограмма. E.EXECUTION_TIME > (select trunc(Sysdate) from dual): Cardinality Estimation = 5% строк таблицы.

1. Scalar Subquery Caching Простой пример: SQL> select * from v$version where rownum=1; BANNER Oracle Database 11g Enterprise Edition Release bit Production create table test as select rownum id, -- trunc(sysdate)+1-rownum/100 dd, trunc(sysdate+1)-mod(rownum,10000)-mod(rownum,100)/100 dd, rpad(rownum,100,'x') data from dual connect by level

1. Scalar Subquery Caching Простой пример: SQL> select * from test where dd>=trunc(sysdate); Execution Plan Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 200 | | 204 (0)| 00:00:03 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 200 | | 204 (0)| 00:00:03 | |* 2 | INDEX RANGE SCAN | TEST_DD | 200 | | 4 (0)| 00:00:01 |

1. Scalar Subquery Caching Простой пример: SQL> select * from test where dd>=(select trunc(sysdate) from dual); Execution Plan Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | 5566K| 3444 (1)| 00:00:38 | |* 1 | TABLE ACCESS FULL| TEST | | 5566K| 3442 (1)| 00:00:38 | | 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |

1. Scalar Subquery Caching Простой пример: create or replace function f return date as begin return trunc(sysdate); end; /

1. Scalar Subquery Caching Простой пример: create or replace function f return date as begin return trunc(sysdate); end; / SQL> select * from test where dd>=f; Execution Plan Plan hash value: | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | | 5566K| 3560 (5)| 00:00:40 | |* 1 | TABLE ACCESS FULL| TEST | | 5566K| 3560 (5)| 00:00:40 |

Содержание Scalar Subquery Caching – когда он не нужен Пример преимущества «неэффективной архитектуры» B*tree индексы, отсортированные по убыванию

2. Архитектура приложения Имеются две таблицы: Alerts – описание основной сущности; Alert_legs – таблица деталей, связь «один ко многим». Требуется периодически считывать информацию об актуальных сущностях и их деталях.

2. Архитектура приложения Текущая реализация: List result = dao.getAllActiveAlertsList(); for (AlertsData alert : result) Alerts.fillAlertWithLegs(dao, alert); fillAlertWithLegs: select... from alert_legs where alert_id = ?

2. Архитектура приложения Текущая реализация: выглядит абсурдно. Однако...

2. Архитектура приложения Текущая реализация: выглядит абсурдно. Однако... Класс Alerts имеет кэш.

2. Архитектура приложения Запросы к БД (текущее состояние): getAllActiveAlertsList select... from alerts where close_time is null order by... Execs per day: Rows return per exec: ~ Gets per exec (LIO): ~ Disk IO per exec: ~2 000 Ela per exec: ~4.5 s

2. Архитектура приложения Запросы к БД (текущее состояние): getAllActiveAlertsList select... from alerts where close_time is null order by... Execs per day: Rows return per exec: ~ Gets per exec (LIO): ~ Disk IO per exec: ~2 000 Ela per exec: ~4.5 s fillAlertWithLegs select... from alert_legs where alert_id = :1 Execs per day: 2M-3M Rows return per exec: ~1 Gets per exec (LIO): ~4 Disk IO per exec: ~0.2 Ela per exec: ~1 ms

2. Архитектура приложения Запросы к БД: -- (1) + (2) [текущее состояние, с учетом кэширования на стороне сервера приложений] Total gets: 250*70K + 2.5M*4 = ~27.5M Total ela: 250* M*1ms = ~3 600s

2. Архитектура приложения Запросы к БД: -- (1) + (2) [текущее состояние, с учетом кэширования на стороне сервера приложений] Total gets: 250*70K + 2.5M*4 = ~27.5M Total ela: 250* M*1ms = ~3 600s -- (1) + (2) [гипотетическое, без учета кэширования на стороне сервера приложений] Total gets: 250*(70K + 500K*4) = ~517.5M Total ela: 250*( K*1ms) = ~ s

2. Архитектура приложения Запросы к БД: новый запрос с соединением таблиц Rows return per exec: ~500K Gets per exec (LIO): ~1.5M Disk IO per exec: depends on buffer cache content - from 0 to 1M Ela per exec: depends on disk IO - from 90 to 400 s.

2. Архитектура приложения Запросы к БД: новый запрос с соединением таблиц Rows return per exec: ~500K Gets per exec (LIO): ~1.5M Disk IO per exec: depends on buffer cache content - from 0 to 1M Ela per exec: depends on disk IO - from 90 to 400 s. -- (3) за день, без учета кэширования на стороне сервера приложений Execs per day: 250 Total gets: 250*1.5M = 375M Total ela: 250*120s = s

2. Архитектура приложения Запросы к БД: -- новый запрос с соединением таблиц за день, без учета кэширования на стороне сервера приложений Execs per day: 250 Total gets: 250*1.5M = 375M Total ela: 250*120s = s -- текущие запросы, с учетом кэширования на стороне сервера приложений Total gets: 250*70K + 2.5M*4 = ~27.5M Total ela: 250* M*1ms = ~3 600s

Содержание Scalar Subquery Caching – когда он не нужен Пример преимущества «неэффективной архитектуры» B*tree индексы, отсортированные по убыванию Они «хранят значения NULL» Уникальные индексы отличаются от asc-индексов

3. Descending Indexes Oracle® Database SQL Language Reference. 11g Release 2 (11.2) CREATE INDEX Oracle Database treats descending indexes as if they were function-based indexes... Ascending unique indexes allow multiple NULL values. However, in descending unique indexes, multiple NULL values are treated as duplicate values and therefore are not permitted.

3. Descending Indexes Пример create table test_index as select rownum n1, rownum n2, decode(mod(rownum,100),1,1,null) flag1, decode(mod(rownum,100),1,1,null) flag2, rpad(rownum,100,'x') data from dual connect by level

3. Descending Indexes Пример select index_name, index_type, leaf_blocks, num_rows, distinct_keys from user_indexes where table_name='TEST_INDEX'; INDEX_NAME INDEX_TYPE LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS TEST_INDEX_N1 NORMAL TEST_INDEX_N2 FUNCTION-BASED NORMAL TEST_INDEX_F1 NORMAL TEST_INDEX_F2 FUNCTION-BASED NORMAL

3. Descending Indexes Пример select index_name, column_name, descend from user_ind_columns where table_name='TEST_INDEX'; INDEX_NAME COLUMN_NAME DESCEND TEST_INDEX_N1 N1 ASC TEST_INDEX_N2 SYS_NC00006$ DESC TEST_INDEX_F1 FLAG1 ASC TEST_INDEX_F2 SYS_NC00007$ DESC

3. Descending Indexes Пример select column_id, column_name, data_type, data_default, hidden_column, virtual_column, internal_column_id from user_tab_cols -- not user_tab_columns where table_name='TEST_INDEX'; COLUMN_ID COLUMN_NAME DATA_TYPE DATA_DEFAULT HIDDEN VIRTUAL INTERNAL N1 NUMBER NO NO 1 2 N2 NUMBER NO NO 2 3 FLAG1 NUMBER NO NO 3 4 FLAG2 NUMBER NO NO 4 5 DATA VARCHAR2 NO NO 5 SYS_NC00006$ RAW "N2" YES YES 6 SYS_NC00007$ RAW "FLAG2" YES YES 7

3. Descending Indexes Пример select * from test_index where n1 between 10 and 20; | Id | Operation | Name | | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_INDEX | |* 2 | INDEX RANGE SCAN | TEST_INDEX_N1 | Predicate Information (identified by operation id): access("N1">=10 AND "N1"

3. Descending Indexes Пример select * from test_index where n2 between 10 and 20; | Id | Operation | Name | | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_INDEX | |* 2 | INDEX RANGE SCAN | TEST_INDEX_N2 | Predicate Information (identified by operation id): access(SYS_OP_DESCEND("N2")>=HEXTORAW('3EEAFF') AND SYS_OP_DESCEND("N2")

3. Descending Indexes Пример select dump(10), SYS_OP_DESCEND(10), dump(SYS_OP_DESCEND(10)) from dual; DUMP(10) SYS_OP DUMP(SYS_OP_DESCEND(10)) Typ=2 Len=2: 193,11 3EF4FF Typ=23 Len=3: 62,244,255

3. Descending Indexes Пример select dump(10), SYS_OP_DESCEND(10), dump(SYS_OP_DESCEND(10)) from dual; DUMP(10) SYS_OP_D DUMP(SYS_OP_DESCEND(10)) Typ=2 Len=2: 193,11 3EF4FF Typ=23 Len=3: 62,244,255 select dump(null), SYS_OP_DESCEND(null), dump(SYS_OP_DESCEND(null)) from dual; DUMP(NULL) SYS_OP_D DUMP(SYS_OP_DESCEND(NULL)) NULL 00 Typ=23 Len=1: 0

3. Descending Indexes Пример, уникальные индексы create table test_index2 ( n1 number, n2 number, data char(100 char) ); create unique index test_index2_n1 on test_index2(n1); create unique index test_index2_n2 on test_index2(n2 desc);

3. Descending Indexes Пример, уникальные индексы insert into test_index2(n1, n2, data) values(null,2,'x'); 1 row created. insert into test_index2(n1, n2, data) values(null,3,'x'); 1 row created.

3. Descending Indexes Пример, уникальные индексы insert into test_index2(n1, n2, data) values(null,2,'x'); 1 row created. insert into test_index2(n1, n2, data) values(null,3,'x'); 1 row created. insert into test_index2(n1, n2, data) values(4,null,'x'); 1 row created. insert into test_index2(n1, n2, data) values(5,null,'x'); * ERROR at line 1: ORA-00001: unique constraint (TEST_INDEX2_N2) violated

3. Descending Indexes Ограничения function-based индексов распространяются и на desc-индексы.

Вопросы?

Спасибо Олег Коротков

Made in Devexperts is a financial software technology firm established in We specialize in the development, design, implementation and maintenance of state- of-the-art software systems for online brokerage, exchange, and investment tracking, particularly for stocks, options and the Forex markets.