Oracle Standard Edition — путешествие в экономклассе с комфортом

Выступление Ильи Деева, архитектора баз данных «Иннова Дистрибьюшен» (апрель 2010 — февраль 2019), ведущего программиста «КантриКом» (с февраля 2019), с докладом на CUSTIS Meetup: Russian Oracle User Group (4 апреля 2019, Москва).

Видеозапись выступления: https://www.youtube.com/watch?v=M6YJPNSnu5s.

Transcript

1. Oracle Standard Edition - путешествие в экономклассе с комфортом (ч.1, псевдосекционирование и мониторинг) Деев Илья
2. Введение
3. Причины использования SE • Основная причина - конечно, стоимость. https://www.oracle.com/assets/technology-price-list-070617.pdf • $17500 на процессор за SE2 против $47500 за EE • В SE не учитывается количество ядер! Ограничение - 2 сокета. • Опция Partitioning – $11500 на процессор дополнительно к покупке EE • SE1 - предшественник SE2, лицензии были еще дешевле, но не было возможности использования RAC
4. Предыстория • Опыт сопровождения биллинга МТС, применение патчсетов. • Участие в разработке системы контроля версий и доставки обновлений в биллинговой системе CBOSS. • Анализ возможности перехода на псевдосекционирование и SE
5. Новый функционал и старые возможности • Edition-based Redefinition (EBR), безостановочное обновление в 11.2 • Partition view в Oracle 7, псевдосекционирование. • Standby в стиле Oracle 7 вместо Data Guard.
6. Благоприятные обстоятельства • Новое место работы • Средняя по размеру компания, не гигант • Разработка для внутренних нужд • Короткий цикл разработки и внедрения • Поощрение нововведений • Разработка новой системы, отсутствие груза предыдущих схем и правил • Необходимость в эффективных решениях при ограниченных ресурсах
7. О системе
8. Метафора системы – “машина времени” Oracle Time Machine t t
9. Метафора системы – “машина времени” Oracle Time Machine t t • Будущее – активное ожидание новых событий, планирование, предсказание на основе прошлого • Настоящее – активный захват и обработка поступающих данных • Прошлое – упаковка, переработка, анализ и исследование
10. Окружение Oracle OLTP t t C* OLAP (MS SQL, Power BI) Azure C* - Кассандра (в качестве внешнего кеша)
11. Окружение • Внешний кеш – изначально Memcached, потом Cassandra. Кеширование наиболее часто используемых и при этом относительно стабильных данных. Снятие нагрузки с Oracle по часто запрашиваемым данным. Сброс либо перезаполнение кеша при изменении данных. • Внешняя OLAP система – MS SQL, Azure снятие нагрузки при интенсивной переработке и анализе первично обработанных данных. Обработка данных в кубах в локальном датацентре и во внешнем облачном хранилище.
12. Основные принципы построения системы • Обработка данных – внутри базы • Интерфейсный доступ через пакеты • Безопасность через разграничение прав • Свободное пространство и запас производительности как ценные ресурсы • Самодиагностика и проактивный мониторинг • Жесткий прагматичный минимализм
13. Основные принципы построения системы KISS : Keep It Short and Simple
14. Основные принципы построения системы • Если можно сделать просто – делаем просто • Сведение к минимуму взаимодействия между компонентами • Минимальное использование больших и сложных типов данных • Автоматизированная чистка устаревающих данных, использование архивных таблиц • Минимально необходимый набор индексов • Производительность – непременное условие
15. Псевдосекционирование
16. Накопление данных, доступных пользователям t t 2010 06.2010 Oracle
17. Накопление данных t t 2010.... 06.2010 Oracle
18. Монолитный вариант
19. Помесячное разбиение
20. Таблица ACCOUNT_ACTION — действия пользователей и их параметры • Объем данных — более половины общего объема данных • Проблемы — обработка довольно больших объемов на уровне отчетов, место для хранения, сложности в администрировании • Стандартное секционирование недоступно, хотя в Standard Edition и XE оно имеется «под капотом». Небольшое отступление об этом. • Решение — псевдосекционирование (на основе partition view), кодирование типовых атрибутов и значений JSON в поле описания события и «распаковкой» при обращении.
21. Разбиение по месяцам t t 06.2010 Oracle 06.10 07.10 08.10 09.10 10.10 11.10 12.10
22. Разбиение больших таблиц • Проблема с разбиением очень больших таблиц • Варианты решения • Возможность обработки данных по отдельным экстентам, начиная с последних • Вставка в новую таблицу, удаление данных в старой, SHRINK • Ограничение доступности видимости данных таблицы через объединение с данными по экстентам x x x Shrink DATA EXTENT LIST BIG 2019 2018
23. Упаковка описания действий пользователей create or replace view usr.account_action as ... select '201501' key_val, master_account_id, service_account_id , usr.utl_pkg.dsc_decompress(nvl(action_description,'{ }'), project_id) action_description, action_timestamp , account_action_type_id, operator_account_id, reason_int, ip_address, service_id , project_id, area_id from usr.account_action_2015_01 a201501 union all select '201502' key_val, master_account_id, service_account_id , usr.utl_pkg.dsc_decompress(nvl(action_description,'{ }'), project_id) action_description, action_timestamp , account_action_type_id, operator_account_id, reason_int, ip_address, service_id , project_id, area_id from usr.account_action_2015_02 a201502 union all … action_description в «упакованном» виде: { #19:'4Pay ', #18:500, #14:500, #7:1.82, #6:501.82, #15:#6, #13:'', #16:500, #17:256577493 } Распакованный вид: { 'ps' : '4Pay ', 'paysum' : 500, 'change_balance_sum' : 500, 'balance_sum_before' : 1.82, 'balance_sum_after' : 501.82, 'currency_code' : 'RUB', 'reason_ext' : '', 'currency_qty' : 500, 'doc_payment_id' : 256577493 }
24. Пример запроса с исключением псевдосекций select * from usr.account_action aa where key_val between to_char(i_dt_from,'YYYYMM') and to_char(i_dt_to,'YYYYMM') and master_account_id = i_master_account_id and action_timestamp between i_dt_from and i_dt_to Лишние «секции» не читаются вообще! Два способа достижения этого эффекта — либо нужен ключ секционирования в поле view с дополнительным условием в запросе, либо необходимо явное условие where по датам для каждой «секции» view и тогда в запросах можно обойтись обычным условием по датам без условия по ключу (вероятно, это более предпочтительный вариант). См. примеры скриптов.
25. Немного конспирологии • Partition view – предтеча секционирования, функционал Oracle 7 https://docs.oracle.com/cd/A57673_01/DOC/server/doc/A48506/partview.htm • С появлением секционирования упоминания в документации, естественно, исчезают, но функционал доступен. • Том Кайт резко отвечает тем, кто пытается использовать старый функционал https://asktom.oracle.com/pls/apex/asktom.search?tag=partitioning-200105 • Ноту на Металинке о псевдосекционировании убирают
26. Накопление данных, RO-пространства t t Oracle 2012 RW 2011 RW-RO 2010 RO 01.2012 Важно: RMAN в SE использует не более 2 channels
27. “Остывание” данных Oracle t t 2014 ... 2011 2010
28. Накопление данных. Появление архивной базы Oracle. Oracle (main) t t 2019 2018 2017 2016 2011 2010 ... 2015 Oracle (arch) Архивная база разгружает основную. Расположение – на хосте со стендбаем, позже - на виртуальном сервере.
29. Появление архивной базы ... select '201512' key_val, master_account_id, service_account_id , usr.utl_pkg.dsc_decompress(nvl(action_description,'{ }'), project_id) action_description, action_timestamp , account_action_type_id, operator_account_id, reason_int, ip_address, service_id , project_id, area_id from usr.account_action_2015_12@billarch a201512 union all select '201601' key_val, master_account_id, service_account_id , usr.utl_pkg.dsc_decompress(nvl(action_description,'{ }'), project_id) action_description, action_timestamp , account_action_type_id, operator_account_id, reason_int, ip_address, service_id , project_id, area_id from usr.account_action_2016_01 a201601 union all ...
30. «Extended Partitioning» Компоненты: • Oracle 12.1 Standard Edition 2 • Oracle Database Gateway • UnixODBC + Postgres ODBC driver • Postgres 9.6
31. От локального “псевдосекционирования” к трансконтинентальному “псевдошардингу”. Oracle (main) t t 2019 2018 2017 2016 2011 2010 ... 2015 Oracle (arch) 2010 Postgres (arch) 2011 ... 2015
32. Появление архивной базы Postgres select '201012' key_val, master_account_id, service_account_id , usr.utl_pkg.dsc_decompress(nvl(action_description,'{ }'), project_id) action_description, action_timestamp , account_action_type_id, operator_account_id, reason_int, ip_address, service_id , project_id, area_id from usr.account_action_arch@billarch a201012 /* view в арх. базе обращается к PG */ union all ... select '201512' key_val, master_account_id, service_account_id , usr.utl_pkg.dsc_decompress(nvl(action_description,'{ }'), project_id) action_description, action_timestamp , account_action_type_id, operator_account_id, reason_int, ip_address, service_id , project_id, area_id from usr.account_action_2015_12@billarch a201512 union all select '201601' key_val, master_account_id, service_account_id , usr.utl_pkg.dsc_decompress(nvl(action_description,'{ }'), project_id) action_description, action_timestamp , account_action_type_id, operator_account_id, reason_int, ip_address, service_id , project_id, area_id from usr.account_action_2016_01 a201601 union all ...
33. Появление архивной базы Postgres create or replace view usr.account_action_arch as /* view в архивной базе */ select "master_account_id" as master_account_id /* использование кавычек */ , "service_account_id" as service_account_id , "action_description" as action_description , cast("action_timestamp" as date) as action_timestamp /* обращение через cast */ , "account_action_type_id" as account_action_type_id , "operator_account_id" as operator_account_id , cast("reason_int" as varchar2(1333)) as reason_int /* исх. размер — 4000, эффект исп. UNICODE */ , cast("ip_address" as varchar2(80)) as ip_address , "service_id" as service_id , "project_id" as project_id , "area_id" as area_id from "account_action_arch"@PG_BILL_ARCH; /* линк к Postgres, обращение к самым старым данным*/
34. Распределение данных по различным базам Данные за последние три года в основной базе BILLMAIN Более старые данные расположены в архивной базе BILLARCH Самые старые данные - в Postgres.
35. Мониторинг
36. Мониторинг • Основной мониторинг – встроенный. Внешний мониторинг ключевых параметров серверов Oracle. • Общие показатели: CPU, ошибки в alert.log, блокировки, использование пространства, обработка очередей, длительные транзакции и др. Бизнес-показатели – регистрация пользователей, платежи, покупки, входы, обработка заявок на различные операции и др. • Проактивный мониторинг. Доставка сообщений на почту и через SMS тем, кто отвечает за соответствующие области. Дополнительные отчеты с данными по проблемам. • STATSPACK • Мониторинг запросов через ASH Viewer (работает в SE!) https://habr.com/ru/company/jetinfosystems/blog/245507/
37. Мониторинг • Основное внимание – мониторингу интерфейсных PL/SQL- вызовов • Все общение с системой организовано через интерфейсные пакеты, именно скорость выполнения этих вызовов наиболее показательна • Настройка на уровне отдельных SQL часто не так актуальна, т.к. многое определяется частотой вызовов и их функциональной областью. Например, для оператора из поддержки отличия в миллисекунды не играют роли. • Основной источник данных – V$SQL. Ведение данных по изменению количества и статистики выполнения вызовов. Сброс статистики некоторых курсоров после обновления соответствующих программных модулей. Снимки – обычно раз в сутки. Вывод данных через web-интерфейс. • Установка baseline и фиксация отклонений, оповещения по email и отчет по проблемам.
38. Мониторинг • Необходимость соотносить запросы курсорных out- переменных с пакетными методами begin interface_call(i_account, i_timestamp, o_cur); end; • Статистика по такому коду не учитывает выполнение запроса в o_cur, т.к. внутри вызова происходит только открытие курсора. Выполнение и выборка данных происходит после окончания вызова. • Можно использовать DBMS_APPLICATION_INFO
39. Онлайн-мониторинг PL/SQL-вызовов. with function wait_seconds(i_seconds in number default 0) return number –-12с inline is begin dbms_lock.sleep(seconds => i_seconds) ; return i_seconds ; end ; select q2.*, round(ratio_to_report(q2.ela_ms) over()*100,2) as pct from ( select parsing_schema_name, sql_id, command_type, sql_text , lead(executions, 1) over(partition by sql_id, child_number order by snap) - executions as execs , (lead(cpu_time, 1) over(partition by sql_id, child_number order by snap) - cpu_time) / 1000 as cpu_ms , (lead(elapsed_time, 1) over(partition by sql_id, child_number order by snap) - elapsed_time)/ 1000 as ela_ms , lead(buffer_gets, 1) over(partition by sql_id, child_number order by snap) - buffer_gets as buff from (select 1 snap, s.parsing_schema_name, s.command_type, sql_id, sql_text , child_number, executions, cpu_time, elapsed_time, buffer_gets from v$sql s union all select 2 snap, s.parsing_schema_name, s.command_type, sql_id, sql_text , child_number, executions, cpu_time, elapsed_time, buffer_gets from v$sql s where wait_seconds(10) > 0 -- seconds, >=12c inline, иначе отд. функц. ) q ) q2 where execs > 0 and command_type = 47 -- 47 PL/SQL, 3 - SELECT and parsing_schema_name != 'SYS' order by ela_ms desc /
40. Online-мониторинг выполнения запроса в другой сессии в виде вывода плана запроса и процента выполнения каждого шага with q_sess as (select sql_id, sql_child_number, s.sql_exec_id, s.sid from v$session s where sid =/* SID */ (select sid from v$session where username = user and userenv('SID') != sid and status = 'ACTIVE') ), q_plan as (select id , lpad(' ',depth * 2,' ')||operation||' '||options as operation , object_name, cardinality, bytes, cost from v$sql_plan p where (p.sql_id, p.child_number) in (select sql_id, sql_child_number from q_sess) ) select q_plan.* , round(lo.sofar/lo.totalwork*100) pct, lo.elapsed_seconds, lo.time_remaining from q_plan , q_sess , v$session_longops lo where lo.sid(+) = q_sess.sid and lo.sql_plan_line_id(+) = q_plan.id and lo.sql_exec_id(+) = q_sess.sql_exec_id and lo.sql_id(+) = q_sess.sql_id order by id;
41. Корректировка плана запроса с помощью SQL patch declare l_sql_id_trg char(13) := '4aay3kxc7rddg'; -- запрос для настройки (v$sql.sql_id) l_sql_id_src char(13) := 'g2u2fv1npc6q5'; -- настроенный запрос - источник хинтов: v$sql.sql_id, l_child_number_src number :=0; -- v$sql.child_number l_hints varchar2(32767); l_sql_text clob; begin -- удаление SQL патча, если такой есть dbms_sqldiag.drop_sql_patch(name => 'patch_'||l_sql_id_trg, ignore => true); -- список хинтов настроенного запроса в одной строке select listagg( hint_val,' ') within group (order by rn) into l_hints from (select extractValue(value(d), '/hint') hint_val, rownum rn from (select other_xml from v$sql_plan where sql_id = l_sql_id_src and child_number = l_child_number_src and id = 1) add_data, table(XMLSequence(XMLType(add_data.other_xml).extract('other_xml/outline_data/hint'))) d); -- текст настраиваемого запроса select sql_fulltext into l_sql_text from v$sql where sql_id = l_sql_id_trg and rownum = 1; -- создание SQL патча sys.dbms_sqldiag_internal.i_create_patch( sql_text => l_sql_text, hint_text => l_hints, name => 'patch_'||l_sql_id_trg); dbms_output.put_line('patch_'||l_sql_id_trg||' was created'); End; См. http://www.fors.ru/upload/magazine/05/http_texts/russia_ruoug_deev_sql_plans.html https://iusoltsev.wordpress.com/2015/02/22/sql-patch-notes/