суббота, 18 мая 2019 г.

Oracle 18-19: новые возможности для разработчика

Список нововведений в Oracle DB, важных, по моему мнению, для разработчика.

Oracle 19


DISTINCT Option for LISTAGG Aggregate
Опция DISTINCT теперь доступна для аналитической функции LISTAGG. Больше не нужно делать свою агрегатную функцию

Generation of Unique Sequence Numbers Across Shards
Возможность создание единой последовательности для всех нод шардированной базы.

Automatic Indexing
Автоматическое создание индексов (или рекомендации для создания) на таблицах с полным сканированием.
Почитайте блог richardfoote. Функицонал имеет ряд ограничений и иногда действует слишком прямолинейно.

SQL Quarantine
Если SQL запрос с определенным планом превышает лимиты Resource Manager, то его hashplan помещается в карантин.
Все последующие попытки выполнить запрос с этим планом будут прерываться, чтобы не исчерпать все доступные ресурсы бд.

Real-Time Statistics
Автоматический сбор статистики во время выполнения DML. Собираются только общие показатели: число строк, границы.
Т.е. плановые сборы статистики для гистограмм или числа уникальных значений остаются.

Hint usage reporting
dbms_xplan с опцией +hint показывает раздел по хинтам запроса: какие были использованы, а какие нет с причиной их отсечения
select /*+ FULL(DUAL) INDEX(BLABLABLA) BLABLABLA(DUAL) */ * from dual;
select * from dbms_xplan.display_cursor('3ps01tc9mxuhd',format=>'+HINT_REPORT');

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (N - Unresolved (1), E - Syntax error (1))
---------------------------------------------------------------------
1 -  SEL$1
         N -  INDEX(BLABLABLA)
         E -  BLABLABLA
1 -  SEL$1 / DUAL@SEL$1
           -  FULL(DUAL)

Compare_plans
dbms_xplan теперь может сравнивать планы 2 запрсов через функцию compare_plans
DBMS_XPLAN.COMPARE_PLANS( 
 reference_plan    => cursor_cache_object('10z134y2v2k12', NULL),
 compare_plan_list => plan_object_list(cursor_cache_object('37qs04s54p0pf', NULL)),
 type              => 'TEXT',
 level             => 'TYPICAL', 
 section           => 'ALL');

Hybrid Partitioned Tables
Возможность гибридной партицирования таблицы, когда часть партиций обычные в Oracle, а часть во внеших источниках: ORACLE_DATAPUMP, ORACLE_LOADER, ORACLE_HDFS, ORACLE_HIVE

Синтаксис таблицы, где 1 партиций вынесена в hdfs кластера hadoop:
CREATE TABLE hybrid_partition_table
  ( prod_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
    EXTERNAL PARTITION ATTRIBUTES (
        type oracle_hdfs
           default directory def_dir1
           access parameters
           (com.oracle.bigdata.cluster   = hadoop_cl_1
            com.oracle.bigdata.rowformat = delimited fields terminated by ',')
     ) 
    PARTITION BY RANGE (time_id)
    (PARTITION sales_2014 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy')),
     PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')) EXTERNAL
          DEFAULT DIRECTORY sales_data_acfs LOCATION ('sales2019_data.txt')
 );

Bitmap Based Count Distinct SQL Function
Как известно, основной минус предрасчиывания кол-ва уникальных значений в том, что их нельзя складывать или переиспользовать в других срезах, отличных от посчитанных.

Для решения этой проблемы Oracle ввела новую интересную опцию, позволяющую создавать предрасчитанные битовые карты count distinct, с возможностью их последующего объединения в других разрезах.

Битовые карты позволяют хранить относительно компактно уникальные значения.
BITMAP поле - максимум 4КБ (*8 бит = число возможных значений)

Мое представление о работе: (может отличаться от реального, но в документации пока нет подробностей)
допустим у нас есть продажи, в которых число уникальных значений PROMO = 2 , CHANNEL_ID = 2, PROD_ID = 5.
Мы хотим предрасчитать число уникальных значений PROD_ID для всех возможных срезов PROMO + CHANNEL_ID.
Основная цель - сжать данные ключа так, чтобы они занимали меньше места и чтобы потом их можно было быстро объединять.
Для этого можно завести битовую маску, где ее число битов = общему числу уникальных записей в PROD_ID и для каждой уникальной записи PROD_ID свой бит в маске

Пример создания matview с битовыми картами для расчета distinct
create materialized view mv_sales_amount AS
SELECT PROMO_ID, CHANNEL_ID,
       BITMAP_BUCKET_NUMBER(PROD_ID) as bm_bktno,
       BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(PROD_ID)) as bm_details,
       SUM(AMOUNT_SOLD) as amount_sold
FROM sales
GROUP BY PROMO_ID, CHANNEL_ID, BITMAP_BUCKET_NUMBER(PROD_ID);

--COUNT(DISTINCT PROD_ID) по срезу PROMO_ID будет использовать mv_sales_amount  
SELECT PROMO_ID, COUNT(DISTINCT PROD_ID), SUM(AMOUNT_SOLD)
FROM sales
GROUP BY PROMO_ID;

Что будет лежать в строках представления mv_sales_amount:
PROMO_ID | CHANNEL_ID | BITMAP(PROD_ID)
1           1           00001
1           2           10001
2           1           01010
2           2           10100
т.е. теперь, чтобы получить число уникальных значений PROD_ID только по колонке PROMO_ID нужно объединить маски с одним PROMO_ID
PROMO_ID | BITMAP(PROD_ID)
1          00001
1          10001 ==> 10001 => 2 бита - 2 уникальных значения
2          01010
2          10100 ==> 11110 => 4 установленных бита - 4 значения

+ битовая карта занимает меньше места, чем хэш массив
+ возможность быстрого объединения битовых массивов, вместо перебора значений по хэшу
=> что дает значительное преимущество - 1 агрегат DISTINCT, для несольких возможных срезов
- ограничение в 4КБ для битового массива
BITMAP_BUCKET_NUMBER - вероятно, создан для того, чтобы добавить дополнительные уровень разбиения PROD_ID, если размер битовой карты превышает 4КБ
- утяжеляющийся предрасчет и дополнительный объем данных под bitmap
- работает только с number


Oracle 18


Private Temporary Tables
Private temporary tables удаляются вконце транзакции или сессии. Такие таблицы хранятся в памяти и видимы только внутри одной сессии (аналог mssql временных таблиц).
CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_transaction
    (time_id      DATE,
     amount_sold  NUMBER(10,2))
   ON COMMIT DROP DEFINITION;

User-Defined Sharding Method
LIST и RANGE партиции теперь можно вручнуть положить на нужный шард.
CREATE TABLESPACE ts1 IN SHARDSPACE west;
CREATE TABLESPACE ts2 IN SHARDSPACE central;

CREATE SHARDED TABLE accounts
( id             NUMBER
, account_number NUMBER
, customer_id    NUMBER
, state          VARCHAR(2) NOT NULL
)
PARTITION BY LIST (state)
( PARTITION p_northwest VALUES ('OR', 'WA') TABLESPACE ts1
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM') TABLESPACE ts2
)
;

Уровни консистенции для запросов к нескольким шардам
Можно установить уровень консистенции для запроса с нескольких шардов.
* STRONG - полная консистенция до SCN (по умолчанию)
* SHARD_LOCAL - консистенция SCN на уровне шарда. Если допустимо, то должно ускорить запрос.
* DELAYED_STANDBY_ALLOWED - возможность забирать данные из STANDBY , где данные могут быть с задержкой

Oracle RAC Sharding
Oracle RAC Sharding может связывать партиции таблицы с инстанциями Oracle RAC. И запрос с указанным ключем партицирования перенаправляется на нужную инстанцию, которая связанна с определенным шардом.

Analytic View FILTER FACT and ADD MEASURE Keywords
Запросы, которые читают аналитические представления, могут включать ключевые слова FILTER FACT для фильтрации данных до любых вычислений и ADD MEASURES для определения дополнительных вычисляемых показателей запроса.
https://docs.oracle.com/en/database/oracle/oracle-database/18/dwhsg/overview-analytic-views.html
https://docs.oracle.com/en/database/oracle/oracle-database/18/dwhsg/analytic-view-objects.html
CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY
  (time_attr_dim
    KEY month_id REFERENCES month_id
    HIERARCHIES (
      time_hier DEFAULT,
      time_season_hier),
   product_attr_dim
    KEY category_id REFERENCES category_id
    HIERARCHIES (
      product_hier DEFAULT),
   geography_attr_dim
    KEY state_province_id 
    REFERENCES state_province_id
    HIERARCHIES (
      geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  units FACT units
  )
DEFAULT MEASURE SALES;

SELECT time_hier.member_name, TO_CHAR(sales, '999,999,999,999') AS sales 
  FROM sales_av HIERARCHIES(time_hier)
  WHERE time_hier.level_name = 'YEAR'
  ORDER BY time_hier.hier_order;
  
 MEMBER_NAME          SALES
-----------  -------------
CY2011       6,755,115,981
CY2012       6,901,682,399
CY2013       7,240,938,718
CY2014       7,579,746,353
CY2015       7,941,102,885

SELECT time_hier.member_name, TO_CHAR(sales, '999,999,999,999') AS sales
  FROM ANALYTIC VIEW (                      -- inline analytic view
  USING sales_av HIERARCHIES(time_hier)
  FILTER FACT (time_hier TO level_name = 'MONTH'
               AND TO_CHAR(month_end_date, 'Q') IN (1, 2)
               )
  )
  WHERE time_hier.level_name = 'YEAR')
  ORDER BY time_hier.hier_order;
  
MEMBER_NAME          SALES
-----------  -------------
CY2011       6,755,115,981
CY2012       6,901,682,399
CY2013       7,240,938,718
CY2014       7,579,746,353
CY2015       7,941,102,885

ALTER SYSTEM CANCEL SQL
Отключение SQL запроса без отключения сессии.

Inline External Tables
Возможность читать external данные без создания таблицы:
SELECT * FROM   EXTERNAL (   
    (time_id        DATE NOT NULL,     
     prod_id        INTEGER NOT NULL,
     quantity_sold  NUMBER(10,2),
     amount_sold    NUMBER(10,2))     
    TYPE ORACLE_LOADER     
    DEFAULT DIRECTORY data_dir1
    ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      FIELDS TERMINATED BY '|')     
   LOCATION ('sales_9.csv') REJECT LIMIT UNLIMITED) sales_external;
Также external таблицы теперь можно партицировать
External таблицы теперь можно помещать в In-Memory

Polymorphic Tables
Функция принимает на вход таблицу и возвращает таблицу
Передача таблицы в параметр:
FUNCTION skip_col(tab TABLE, 
чтение данных из функции:
SELECT * FROM noop(emp);
синтаксис

Parallel Partition-Wise SQL Operations
SELECT DISTINCT, Window - теперь могут параллелиться, если идут в по колонке партиции

Modifying the Partitioning
Теперь стратегию партицирования можно менять налету: ALTER TABLE MODIFY PARTITION SQL

Улучшение SQL Tuning Advisor для Exadata
SQL Tuning Advisor получил дополнительные алгоритмы для оптимизации запросов для Oracle Exadata.

Concurrent SQL Execution with SQL Performance Analyzer
Теперь можно имитировать параллельную нагрузку от запросов в tuning set (раньше они всегда выполнялись последовательно)

Automatic In-Memory
Данные в In-Memory используют тепловые карты, статистику колонок и прочее для загрузки или выгрузки данных из Inmem.

Scalable Sequences
В начало последовательности добавляется префикс:
6 digit scalable sequence offset number = 3 digit instance offset number || 3 digit session offset number.
что разбрасывает данные равномерно и решает проблему конкуренции за крайнии блоки индекса.

Memoptimized Rowstore
inmemory k-v хранилище, для быстрого доступа по PK к очень популярным таблицам
ALTER TABLE sh.sales MEMOPTIMIZE FOR READ;
execute DBMS_MEMOPTIMIZE.POPULATE('OE','ORDERS'); 

Комментариев нет:

Отправка комментария