Я построил реальный тестовый пример с CSV, предоставленным с 20M записями. В качестве доказательства концепции я сравню два варианта в Oracle. Вариант Oracle In-Memory против параллельного запроса Oracle. Идея состоит в том, чтобы увидеть, соответствуют ли результаты вашим ожиданиям.
- In Memory Option - это лицензионная плата, поэтому вам придется заплатить дополнительные расходы.
- Параллельный запрос - это функция, включенная в Enterprise Edition базы данных Oracle.
Лабораторная работа: Сервер Linux Red Hat 7: 16 процессоров и 32 ГБ ОЗУ в разделе памяти = виртуальный сервер 4 ГБ на VMware с использованием серии оборудования IBM ESX.
Элементы для тестового примера
SQL> create tablespace tbtest datafile '/bbdd_odcgrc1r/datos/test.dbf' size 2g autoextend on next 100m maxsize 10g ;
Tablespace created.
SQL> create user test_perf identified by "Oracle_1" ;
User created.
SQL> grant connect to test_perf ;
Grant succeeded.
SQL> grant create table to test_perf ;
Grant succeeded.
SQL> alter user test_perf quota unlimited on tbtest ;
User altered.
SQL>
Параллельный запрос Oracle без опции в памяти
Я загрузил файл csv, используя загрузчик sql с прямым путем:
Table TEST_PERFORMANCE:
20000000 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 20000000
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 4112
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Sat Jul 25 00:57:23 2020
Run ended on Sat Jul 25 00:57:34 2020
Очевидно, что загрузка файла в базу данных - это совсем не то же самое, что загрузка файла в panda. Поскольку Panda не нужно загружать данные ни во что (в данном случае в файл данных базы данных)
SQL> desc test_perf.test_performance
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
DATE_ID DATE
INSTANCE_ID NUMBER
TERRITORY_ID VARCHAR2(10 CHAR)
CODE VARCHAR2(10 CHAR)
PRICE NUMBER
CURRENCY_CODE_ID VARCHAR2(10 CHAR)
PRICE_IN_USD NUMBER
Собираю статистику таблицы и теперь посмотрим как она себя ведет
SQL> exec dbms_stats.gather_table_stats ( 'TEST_PERF' , 'TEST_PERFORMANCE' , block_sample => true );
PL/SQL procedure successfully completed.
Группировать по currency_code_id (без сортировки)
SQL> select count(*) , currency_code_id from test_perf.test_performance group by currency_code_id
90 rows selected.
Elapsed: 00:00:00.35
Группировка по currency_code_id, сортировка по счетчику
SQL> select count(*) , currency_code_id from test_perf.test_performance group by currency_code_id order by count(*) desc ;
90 rows selected.
Elapsed: 00:00:00.70
Сортировка всех записей и их отображение по sqlplus занимает много времени только на создание вывода, но сам запрос выполняется очень быстро
SQL> select * from test_perf.test_performance order by PRICE_IN_USD desc ;
20000000 rows selected.
Elapsed: 00:01:31.48
Execution Plan
----------------------------------------------------------
Plan hash value: 1897103579
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20M| 972M| | 5940 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 20M| 972M| | 5940 (1)| 00:00:01 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 20M| 972M| 1376M| 5940 (1)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 20M| 972M| | 435 (1)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 20M| 972M| | 435 (1)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 20M| 972M| | 435 (1)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| TEST_PERFORMANCE | 20M| 972M| | 435 (1)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 32 because of table property
Statistics
----------------------------------------------------------
364 recursive calls
3 db block gets
36963 consistent gets
45558 physical reads
2860 redo size
703698256 bytes sent via SQL*Net to client
14667271 bytes received via SQL*Net from client
1333335 SQL*Net roundtrips to/from client
64 sorts (memory)
1 sorts (disk)
20000000 rows processed
Реальное время, затрачиваемое на сам запрос,
SQL> select * from test_perf.test_performance order by PRICE_IN_USD desc ;
Elapsed: 00:00:02.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1897103579
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20M| 972M| | 5940 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 20M| 972M| | 5940 (1)| 00:00:01 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 20M| 972M| 1376M| 5940 (1)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 20M| 972M| | 435 (1)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 20M| 972M| | 435 (1)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | PX BLOCK ITERATOR | | 20M| 972M| | 435 (1)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| TEST_PERFORMANCE | 20M| 972M| | 435 (1)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 32 because of table property
Выберите отдельный идентификатор_территории таблицы
SQL> select distinct territory_id from test_perf.test_performance order by territory_id desc ;
136 rows selected.
Elapsed: 00:00:00.58
В качестве опоры я выбрал этот простой пример
SQL> select * from test_perf.test_performance
pivot ( count(*) for TERRITORY_ID in
(
'ZW',
'ZM',
'ZA',
'VN',
'VG',
'VE',
'UZ',
'UY',
'US',
'UG',
'UA',
'TZ',
'TW',
'TT',
'TR',
'TM',
'TJ',
'TH',
'TG',
'SZ',
'SV',
'SN',
'SK',
'SI',
'SG',
'SE',
'SA',
'RW',
'RU',
'QA',
'PY',
'PT',
'PL',
'PH',
'PG',
'PE',
'PA',
'OM',
'NZ',
'NP',
'NO',
'NL',
'NI',
'NE',
'NA',
'MZ',
'MY',
'MX',
'MU',
'MT',
'MO',
'MN',
'ML',
'MK',
'MD',
'LV',
'LU',
'LT',
'LK',
'LB',
'LA',
'KZ',
'KY',
'KW',
'KR',
'KN',
'KH',
'KG',
'JP',
'JO',
'JM',
'IT',
'IS',
'IN',
'IL',
'IE',
'ID',
'HU',
'HT',
'HR',
'HN',
'HK',
'GW',
'GT',
'GR',
'GM',
'GH',
'GD',
'GB',
'GA',
'FR',
'FM',
'FJ',
'FI',
'ES',
'EG',
'EE',
'EC',
'DO',
'DM',
'DK',
'DE',
'CZ',
'CY',
'CV',
'CR',
'CO',
'CL',
'CI',
'CH',
'CA',
'BZ',
'BY',
'BW',
'BS',
'BR',
'BO',
'BN',
'BM',
'BJ',
'BH',
'BG',
'BF',
'BE',
'BA',
'AZ',
'AW',
'AU',
'AT',
'AR',
'AO',
'AM',
'AL',
'AI',
'AG',
'AE'
) )
order by id
Elapsed: 00:00:04.74
Вариант Oracle In Memory
Я сконфигурировал 4 ГБ внутренней памяти, что не так уж и много.
Total System Global Area 1.2885E+10 bytes
Fixed Size 12192520 bytes
Variable Size 5184161016 bytes
Database Buffers 3388997632 bytes
Redo Buffers 4583424 bytes
In-Memory Area 4294967296 bytes
Database mounted.
Database opened.
SQL> SHOW PARAMETER INMEMORY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_adg_enabled boolean TRUE
inmemory_clause_default string
inmemory_expressions_usage string ENABLE
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 8
inmemory_query string ENABLE
inmemory_size big integer 4G
inmemory_trickle_repopulate_servers_ integer 1
percent
inmemory_virtual_columns string MANUAL
optimizer_inmemory_aware boolean TRUE
SQL> ALTER TABLE TEST_PERF.TEST_PERFORMANCE INMEMORY PRIORITY HIGH;
Table altered.
SQL> select segment_name
, partition_name
2 3 , inmemory_size / 1024 / 1024 as inmemory_size_mb
, bytes / 1024 / 1024 as bytes_mb
, populate_status
4 5 6 , trunc(bytes / inmemory_size, 1) * 100 as compression_ratio
from v$im_segments
7 8 order by segment_name, partition_name;
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME
--------------------------------------------------------------------------------
INMEMORY_SIZE_MB BYTES_MB POPULATE_STAT COMPRESSION_RATIO
---------------- ---------- ------------- -----------------
TEST_PERFORMANCE
362.25 514.046875 COMPLETED 140
SQL> select count(*),length(date_id) from test_perf.test_performance group by length(date_id)
Execution Plan
----------------------------------------------------------
Plan hash value: 3227171220
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 121 | 29 (59)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 11 | 121 | 29 (59)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 11 | 121 | 29 (59)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 11 | 121 | 29 (59)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 11 | 121 | 29 (59)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 11 | 121 | 29 (59)| 00:00:01 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 20M| 209M| 14 (15)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS INMEMORY FULL| TEST_PERFORMANCE | 20M| 209M| 14 (15)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 32 because of table property
Проверим несколько запросов.
Группировать по территории
SQL> select count(*),TERRITORY_ID from test_perf.test_performance group by TERRITORY_ID ;
136 rows selected.
Elapsed: 00:00:00.24
Группировать по instance_id
SQL> select count(*) , INSTANCE_ID from test_perf.test_performance group by INSTANCE_ID
11251 rows selected.
Elapsed: 00:00:00.27
Группировать по 2 полям
SQL> select count(*), instance_id, territory_id from test_perf.test_performance group by instance_id, territory_id ;
278269 rows selected.
Elapsed: 00:00:00.84
Как вы можете, очевидно, когда у меня есть одна таблица в памяти, производительность этих операций возрастает, но вы заметите большее улучшение, если начнете применять аналитику к запросам.
Несколько статистических функций и группировка по
SQL> select territory_id,sum(to_number(price)),avg(to_number(price)),max(to_number(price)),min(to_number(price))
2 from test_perf.test_performance group by territory_id ;
Elapsed: 00:00:00.57
Однако PIVOT обычно не подходит для хранения в памяти, поскольку для таблиц используется хранилище столбцов.
The same query with pivot executed before takes
Elapsed: 00:00:15.93
Как вы можете видеть в приведенных выше примерах, я не менял свойство PARALLEL таблицы, поэтому позвольте мне показать вам, как ведет себя запрос, когда параллелизм не включен для объекта, у которого есть опция INMEMORY.
SQL> alter table test_perf.TEST_PERFORMANCE noparallel ;
Table altered.
SQL> select count(*) , currency_code_id from test_perf.test_performance group by currency_code_id
2 ;
90 rows selected.
Elapsed: 00:00:02.14
Execution Plan
----------------------------------------------------------
Plan hash value: 151279035
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90 | 450 | 909 (62)| 00:00:01 |
| 1 | HASH GROUP BY | | 90 | 450 | 909 (62)| 00:00:01 |
| 2 | TABLE ACCESS INMEMORY FULL| TEST_PERFORMANCE | 20M| 95M| 411 (16)| 00:00:01 |
------------------------------------------------------------------------------------------------
IMPQ
В последней части я оставляю лучший из всех вариантов, комбинацию параллельного и inmemory, который называется IMPQ или In Memory Parallel Query. Эта функция обеспечивает лучшее из обоих миров, и для того, чтобы начать работу, вы должны включить параллелизм для своей таблицы, поместить таблицу в область памяти и определить для параметра PARALLEL_DEGREE_POLICY значение AUTO.
Примером этого может быть
SQL> alter session set parallel_degree_policy=auto ;
Session altered.
SQL> set autotrace traceonly explain
SQL> select count(*),territory_id from test_perf.test_performance group by territory_id
Execution Plan
----------------------------------------------------------
Plan hash value: 3227171220
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 136 | 408 | 78 (59)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 136 | 408 | 78 (59)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 136 | 408 | 78 (59)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 136 | 408 | 78 (59)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 136 | 408 | 78 (59)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 136 | 408 | 78 (59)| 00:00:01 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 20M| 57M| 38 (16)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS INMEMORY FULL| TEST_PERFORMANCE | 20M| 57M| 38 (16)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 12
Проверьте в приведенном выше операторе изменение DOP с 32 на 12. Это означает, что IMPQ определил, что наилучшая степень для оператора равна 12 вместо 32, которая была получена из CPU_COUNT * 2 (поскольку таблица была создана со степенью по умолчанию автомат).
Этот запрос ниже теперь с IMPQ выполняется всего за 0,14 секунды вместо 0,35.
SQL> select count(*),territory_id from test_perf.test_performance group by territory_id
2 ;
136 rows selected.
Elapsed: 00:00:00.14
Этот другой запрос теперь занимает 0,14 секунды, тогда как раньше занимал 0,70 секунды.
SQL> select count(*) , currency_code_id from test_perf.test_performance group by currency_code_id order by count(*) desc ;
90 rows selected.
Elapsed: 00:00:00.14
Резюме
Если вам нужно время меньше секунды, и вы в основном используете аналитику, вариант Oracle in-memory может быть хорошей идеей. В этом случае вы можете также включить IMPQ для получения наилучших результатов. Необходимо будет поработать над тем, какие столбцы следует или не включать, как таблица заполняется в области памяти и т. Д. Чем сложнее аналитика, тем выше производительность, которую вы заметите.
Тем не менее, если вы можете жить с временем от 1 до 5 секунд, параллельный запрос без индексов может быть бесплатным решением, которое довольно легко настроить.
Не стесняйтесь комментировать что-либо или просить каких-либо разъяснений относительно тестового примера.
person
Roberto Hernandez
schedule
25.07.2020
SELECT currency_code_id, COUNT(*) FROM dbo.Sales20M GROUP BY currency_code_id;
. YMMV. - person Dan Guzman   schedule 24.07.2020pivot
еще не доступен. - person Nick Becker   schedule 24.07.2020group by x,y
, теперь это 10 тыс. Индексов / агрегатов. - person David542   schedule 25.07.2020