Oracle Cloud Day 2017 Moscow

В Москве 26 октября прошла конференция Oracle Cloud Day 2017.

В залах Raddison-славянская в этот день много говорилось о новейших, прежде всего облачных, технологиях в области хранения, обработки и анализа данных. Можно было послушать выступления как консультантов Oracle относительно новых продуктов компании, так и представителей компаний-клиентов.

Наиболее интересными мне показались примеры внедрения, осуществленных компаниями Ашан и Мегафон. Кирилл Бойко, представитель Ашана, рассказал об использовании on-premis решения exadata, и отметил чрезвычайно высокую скорость работы этого решения. Мария Трофимова из Мегафона поделилась опытом интеграции ЦОД из восьми филиалов в одном месте. Также она отметила потенциал использования технологии Oracle Big Data SQL, когда к традиционной реляционной БД добавляется Hadoop кластер, к которому также идет обращение при отправке SQL-запросов на сервер.

Программа конференции была в некоторой степени скорректирована, с учетом прошедшей три недели назад Oracle Open World. В частности сделали презентацию новых фич готовящейся к релизу версии базы данных 18с. Из интересных нововведений хотел бы обозначить две. Первое – полиморфные табличные функции. Теперь для возврата данных в табличном виде их не обязательно строго определять в самой функции. Количество колонок, тип данных – все это можно динамически менять путем, например, передачи параметров при вызове функции. Соответственно, в новой версии можно будет избежать дублирования кода в случаях, когда необходимо получать различные таблицы при исполнении функции.

Второе – Stored Procedure Multilingual Engine. Новый движок для разных языков программирования, на основе GraalVM. Поддерживается JavaScript, ожидается поддержка Python, Ruby, R. На слайдах показали пример компиляции процедуры на JS, которая печатает ‘Hello’ и Trace. Впоследствии эту процедуру можно вызывать уже непосредственно из SQL-Plus. Так что, принимая во внимание стабильно растущую популярность JavaScript, этой технологии определенно стоит уделить внимание. Хотя хоронить PL-SQL, конечно, не стоит.

И еще один очень хороший анонс был сделан в конце конференции. Бесплатная версия базы данных Oracle XE также получит обновление, но не до предыдущей версии 12c, как оно обычно происходило, а сразу до актуальной версии 18с. Релиз обещают чуть позже в течение 2018 года, и в дистрибутив будет включено множество раньше недоступного для бесплатной версии функционала. В частности упомянули о партиционировании (хотя при ограничении размера табличного пространства в 12 Gb говорить о значительной пользе партиционирования не приходится).

В целом мероприятие было достаточно интересным. Истории успешного внедрения крупных и сложных продуктов всегда занимательны.

Установка Oracle XE 11g на Ubuntu 16.04LTS

По работе столкнулся с необходимостью ознакомится с основными командами на Linux. В процессе изучения bash решил на домашней машине переехать с базой данных и с IDE на Ubuntu. С установкой самой операционной системы никаких проблем не возникает, вопросы начали появляться, когда настала очередь базы данных.

На домашних стендах я использую Oracle XE 11g . Установка базы данных на Linux не настолько простой и приятный процесс, как на Windows. Подробную инструкцию для версии Ubuntu 12.04LTS я нашел здесь и выполнил все операции пошагово. Не смотря на то что некоторые параметры не отображались как задумано в инструкции (например fs.file-max), БД установилась. Также у меня не возникло необходимости увеличивать размер swap.

Решил сохранить инструкцию у себя:

Скачать Oracle XE 11g можно по ссылке (требует бесплатного аккаунта)
http://www.oracle.com/technetwork/products/express-edition/downloads/index.html

Распаковываем скачанный файл:

unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip

Производим дополнительную установку:

sudo apt-get install alien libaio1 unixodbc vim

Установщик Oracle XE 11g от Red Hat зависит от файла /sbin/chkconfig, который не используется в Ubuntu. Пакет chkconfig доступный для текущей версии Ubuntu вызывает ошибки и может быть небезопасно использовать. Поэтому нужно будет создать специальный chkconfig скрипт, который поможет обойти эту проблему и позволит установить Oracle XE.

sudo vi /sbin/chkconfig

Вставляем следующий текст в файл:

#!/bin/bash
# Oracle 11gR2 XE installer chkconfig hack for Ubuntu
file=/etc/init.d/oracle-xe
if [[ ! `tail -n1 $file | grep INIT` ]]; then
echo >> $file
echo '### BEGIN INIT INFO' >> $file
echo '# Provides: OracleXE' >> $file
echo '# Required-Start: $remote_fs $syslog' >> $file
echo '# Required-Stop: $remote_fs $syslog' >> $file
echo '# Default-Start: 2 3 4 5' >> $file
echo '# Default-Stop: 0 1 6' >> $file
echo '# Short-Description: Oracle 11g Express Edition' >> $file
echo '### END INIT INFO' >> $file
fi
update-rc.d oracle-xe defaults 80 01
#EOF

Чтобы выйти с сохранением из редактора VI нужно набрать :wq

Добавляем необходимые привилегии на запуск:

chmod 755 /sbin/chkconfig

(у меня сработало только sudo chmod 755 /sbin/chkconfig)

Для установки Oracle XE необходимы следующие параметры ядра:

sudo vi /etc/sysctl.d/60-oracle.conf 

Вставляем следующий текст в файл:

# Oracle 11g XE kernel parameters
fs.file-max=6815744
net.ipv4.ip_local_port_range=9000 65000
kernel.sem=250 32000 100 128
kernel.shmmax=536870912 

Сохраняем.
Загружаем новые параметры ядра:

sudo service procps start 

Проверяем (у меня правильно начало отображаться только после перезагрузки, тем не установка завершилась нормально)

sudo sysctl -q fs.file-max
 -> fs.file-max = 6815744

Некоторые необходимые изменения:

sudo ln -s /usr/bin/awk /bin/awk
sudo mkdir -p /var/lock/subsys
sudo touch /var/lock/subsys/listener 

Конвертируем red-hat ( rpm ) пакет в Ubuntu-пакет (может занять некоторое время) :

sudo alien --scripts -d oracle-xe-11.2.0-1.0.x86_64.rpm

Дальше распаковываем Ubuntu-пакет:

sudo dpkg --install oracle-xe_11.2.0-2_amd64.deb 

Выполняем следующие команды, чтобы избежать MEMORY TARGET ошибки (ORA-00845: MEMORY_TARGET) :

sudo rm -rf /dev/shm
sudo mkdir /dev/shm
sudo mount -t tmpfs shmfs -o size=2048m /dev/shm

(я оставил 2048, хотя размер оперативной памяти для виртуалки у меня 3GB)

Далее создаем файл с именем S01shm_load в папке /etc/rc2.d :

sudo vi /etc/rc2.d/S01shm_load

Сохраняем в файл следующий текст:

#!/bin/sh
case "$1" in
start) mkdir /var/lock/subsys 2>/dev/null
touch /var/lock/subsys/listener
rm /dev/shm 2>/dev/null
mkdir /dev/shm 2>/dev/null
mount -t tmpfs shmfs -o size=2048m /dev/shm ;;
*) echo error
exit 1 ;;
esac 

Сохраняем файл и даем привилегии

chmod 755 /etc/rc2.d/S01shm_load

Так вы обеспечите себе работающую БД при каждом запуске операционной системы.
Инициализируем Oracle и следуем подсказкам:

 sudo /etc/init.d/oracle-xe configure

Также полезно добавить следующие строки в конец файла /etc/bash.bashrc :

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export ORACLE_BASE=/u01/app/oracle
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH

Выполняем чтобы применить эти настройки:

source /etc/bash.bashrc

Запускаем БД:

sudo service oracle-xe start

Все, можно работать.

Особенности обновления MATERIALIZED VIEW

Рассматривая вопрос обновления материальных представлений обнаружил некоторые особенности. Первое это то, что запрос который мы передаем на создание матвью может быть изменен системой. В частности оператор ORDER BY для главного селекта пропадает, а для вложенных запросов нет. Приведу пример, чтобы было лучше понятно.

Создаем достаточно большую таблицу и создаем матвью.

drop table test_table purge;
CREATE TABLE TEST_TABLE
  (
    id NUMBER(10),
    X  VARCHAR2(255),
    Y  VARCHAR2(255)
  );

BEGIN
 FOR I IN 1 .. 1
 LOOP
 INSERT INTO TEST_TABLE SELECT rownum, a.object_name, a.status FROM all_objects a, holidays h;
 END LOOP;
 COMMIT;
 END;

create MATERIALIZED view MV2_test_table
 as
 select *
 from test_table
 order by 1 desc

Матвью создалась, но если посмотреть DDL запрос создания этой матвью, то мы увидим там следующее:

CREATE MATERIALIZED VIEW "DKSD"."MV2_TEST_TABLE" ("ID", "X", "Y")
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS
select *
from test_table

Order by исчез. Соответственно, после обновления матвью у нас данные будут в другом порядке. Слева столбец до обновления, справа — после.

Но если создать матвью со следующим запросом, то тогда ORDER BY не пропадает.

create MATERIALIZED view MV2_test_table
as
select * from (
select *
from test_table
order by 1 desc
)

Здесь необходимо сказать, что применять оператор ORDER BY для создания представления не очень хорошая идея. Лучше полагаться на индексы и грамотно построенные запросы, чем на то как данные физически лежат.

Второе, хотел бы обратить внимание на параметр ATOMIC_REFRESH, который содержит функция обновления материализованных представлений DBMS_MVIEW.REFRESH. Дело в том, что скорость обновления таблицы сильно зависит от того стоит этот параметр в значении TRUE или FALSE. По-умолчанию в последних версиях Oracle эта функция выполняется с параметром TRUE и это, как правило, занимает больше времени.

Объяснение такому поведению нашел на сайте http://kubilaykara.blogspot.ru/2010/04/materialized-view-refresh-and.html Оказывается, параметр TRUE вызывает оператор DELETE для удаления данных старой матвью, в то время как FALSE вызывает более экономный для процессора оператор TRUNCATE.

Я повторил эксперимент, который описан по ссылке выше, и вот что у меня получилось. Итак, в созданной таблице test_table более миллиона строк. Создаем новое представление.

CREATE materialized VIEW mv_TEST_TABLE
AS
SELECT * FROM TEST_TABLE;

Теперь произведем некоторые изменения с основной таблицей.

UPDATE TEST_TABLE SET Y='1';
COMMIT;

Запускаем обновление

BEGIN
  DBMS_MVIEW.REFRESH('mv_TEST_TABLE','C',ATOMIC_REFRESH => TRUE);
END;

Запрос выполнен за 54,270 сек.

Снова обновляем данные и запускаем обновление с параметром ATOMIC_REFRESH => FALSE

UPDATE TEST_TABLE SET Y=2';
COMMIT; 

BEGIN
 DBMS_MVIEW.REFRESH('mv_TEST_TABLE','C',ATOMIC_REFRESH => FALSE);
END;

Обновление заняло 2,782 сек.

Конечно, прирост производительности в 20 раз это особенности моего тестового стенда. На реальных серверах и очень больших матвью вряд ли это возможно, но следует держать в уме возможность повышения скорости обновления установлением параметра ATOMIC_REFRESH в значение FALSE.

Количество рабочих дней с учетом праздников

Рассмотрим следующую задачку: «Написать SQL-запрос подсчитывающий количество рабочих дней между двумя датами учитывая все праздничные, выходные дни и их переносы”.

Без учета праздников эта задачка не представляет никаких трудностей. Просто подсчитываем количество дней, где день недели не равен СБ или ВС:

select count(*)
from (select to_date('01.01.2017','dd.mm.yyyy')+rownum dat,
to_char(to_date('01.01.2017','dd.mm.yyyy')+rownum,'DY') dy
from sys.all_objects)
where dat<=to_date('31.12.2017','dd.mm.yyyy') and dy not in('СБ','ВС')

Получаем 260 дней.

В стандартном пакете Oracle, да и наверное во всех других базах данных, отсутствуют сведения о праздничных днях в России. Значит их нужно откуда-нибудь взять. Будем вытаскивать выходные из производственного календаря системы Консультант плюс. Актуальный календарь на текущий год располагается по адресу http://www.consultant.ru/law/ref/calendar/proizvodstvennye/

Рассмотрим только текущий 2017 год. Парсить html страницу будем в java с помощью библиотеки jsoup (https://jsoup.org/). Для начала проинспектируем наш календарь.

Видим, что месяц заключен в таблицу, которой проставлен класс “month-block”. Именно по этому признаку и будем выделять необходимые тэги. Далее нужно определить какие дни у нас праздничные, а какие рабочие.

Инспектор html кода показал, что дни бывают трех классов: inactively, holiday weekend, work. Мы будем брать только те значения тэгов, у которых выставлен класс которому соответствуют праздничные дни.

Здесь я должен сделать небольшой дисклэймер. Мое творение не является образцом хорошего кодинга на java. В нем совсем не используются возможности объектно-ориентированного программирования, вся программа написана в одном методе main. Это очень плохо. Пока я не особо умею писать по-другому, но все впереди, я только учусь.

Также пока не подобрал хороший плагин для wordpress для отображения кода. Если стандартные тэги Code для SQL еще более-менее смотрятся, то вот java код практически не читабелен. Постараюсь со временем переделать соответствующие блоки.

Итак, можно приступить к программированию. Вот что у меня получилось.

public class ParseConsultant{
public static void main(String args[]) throws IOException, SQLException {
List monthsList = new ArrayList<>(); //сюда поместим все блоки всех месяцев
List daysList = new ArrayList<>(); //сюда поместим блоки с днями
Document doc = Jsoup.connect("http://www.consultant.ru/law/ref/calendar/proizvodstvennye/").get(); //коннекстимся к источнику
Elements monthsElements = doc.getElementsByAttributeValue("class", "month-block"); //добавляем элементы-месяцы
monthsElements.forEach(monthsElement -> {
String aElement = monthsElement.child(0).toString(); //выбираем все содержимаое месяца
monthsList.add(aElement);
});
Locale.setDefault(Locale.ENGLISH); //подключаемся к базе данных
String dbURL = "jdbc:oracle:thin:dksd/1@localhost:1521:XE"; //подключаемся к базе данных
Connection conn = DriverManager.getConnection(dbURL);
if (conn != null) {
System.out.println("Connected");
}
Statement statement = conn.createStatement(); //открываем statement для последующего insert
for (int x = 0; x < 12; x = x + 1) { //открываем цикл для пробега по всем 12-ти месяцам Document mon = Jsoup.parse(monthsList.get(x)); //парсим содержимое массива месяцев для выбора дней. System.out.println(mon.toString()); //смотрим что у нас в месяце Elements mElements = mon.getElementsByClass("holiday weekend"); //выбираем нужные тэги по классу mElements.forEach(mElement -> {
String dElem = mElement.text(); //получаем значение тэга, то есть праздничный день
daysList.add(dElem); //добавляем значение тэга в массив
});
int z = daysList.size(); //считаем сколько добавилось выходных дней в текущем месяце
for (int i = 0; i < z; i = i + 1) { //для каждого дня формируем insert строку
System.out.println(i + "-" + daysList.get(i)); //смотрим какие у нас выходные дни в текущем месяце
int m = x + 1; //для правильного insert месяца, который не может быть равен нулю
String insertSQL = "insert into holidays (HOLIDAY) VALUES (to_date('" + daysList.get(i) + "." + m + ".2017','dd.mm.yyyy'))";
statement.executeUpdate(insertSQL); //добавлем строки в таблицу holidays
}
daysList.clear(); //очищаем массив в конце цикла
}
}
}

Программа с помощью библиотеки jsoup коннектится к запрашиваемой странице, выбирает все блоки где имелся класс «month-block». Потом каждый блок еще раз парсится на предмет текста тэга, содержащего класс «holiday weekend». Сохраняет все в ArrayList и подставляет в строку для инсерта в базу данных.

Выполнив эту программу получаем даты всех выходных дней в предварительно созданной таблице HOLIDAYS, состоящей из одной колонки HOLIDAY. Их должно получиться 118.

Теперь модифицируем SQL запрос.

select count(*)
from (select to_date('01.01.2017','dd.mm.yyyy')+rownum dat
from sys.all_objects)
where dat<=to_date('31.12.2017','dd.mm.yyyy') and dat not in (select holiday from holidays)

Получаем 247 дней. На целых 13 дней меньше))