Предыдущее наблюдение касалось обычных (неадаптивных) планов выполнения, адаптивные планы демонстрируют тот же эффект в версии 12.2, что логично и любопытно выглядит
Итак, запрос с нестабильным / небыстрым выполнением показывает признаки адаптивности — столбцы «V$SQL.Adapt», «XML.Adapt» (V$SQL_PLAN.OTHER_XML), плюс BIND_SENSE=Y — т.е. чувствителен к значениям связанных переменных, но для применения Adaptive Cursor Sharing оказался не готов — BIND_AWARE=N: (more…)
Начиная с версии 12.2 часто наблюдаю, как 2 плана выполнения с различными PHV используются для обозначения одного и того же фактического плана выполнения
Например, запрос в анамнезе показывает 2 несовпадающих PLAN_HASH_VALUE при эквивалентной паре (FULL_PLAN_HASH_VALUE, PLAN_HASH_2):
SQL> @sql_plan_hashs cj1sg1hk1n3ct SRC SQL_ID FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE PLAN_HASH_2 TIMESTAMP --- ------------- -------------------- --------------- ----------- -------------------- AWR cj1sg1hk1n3ct 2164351587 2164351587 2164351587 26.02.2019 17:37:36 AWR cj1sg1hk1n3ct 2164351587 3400946657 2164351587 26.02.2019 17:29:31
При этом, PHV=2164351587 и PHV=3400946657 по содержимому совпадают абсолютно: (more…)
Продолжая тесты с Oracle LiveSQL 19c, проверил наличие проблемы с плохими FULL SCAN-ами при [авто]добавлении [INTERVAL] PARTITION в процессе каждодневной DML активности и запросами с условием IN
Тестовая таблица простая, и в процессе первоначальной Direct PATH загрузки партиции добавляются, по крайней мере global statistics успешно собирается: (more…)
Запись V$SESSION_LONGOPS, в частности, поля MESSAGE, TOTALWORK для операций Dynamic Sampling выглядят очень серьёзно:
12.2.0.1@ SQL> select * from gv$session_longops where (inst_id, sid, serial#) in ((1,4974,54090)); SID SERIAL# OPNAME TARGET SOFAR TOTALWORK UNITS START_TIME LAST_UPDATE_TIME ELAPSED_SECONDS MESSAGE USERNAME SQL_ADDRESS SQL_HASH_VALUE SQL_ID SQL_PLAN_HASH_VALUE SQL_EXEC_START SQL_EXEC_ID SQL_PLAN_LINE_ID SQL_PLAN_OPERATION SQL_PLAN_OPTIONS QCSID CON_ID ----- -------- ----------- ----------- ---------- ---------- ------- -------------------- -------------------- --------------- ------------------------------------------------------------------ --------- ---------------- -------------- ------------- ------------------- -------------- ----------- ---------------- ------------------- ----------------- ----- ------ 4974 54090 Table Scan XO.PAYMENTS 278127738 278127738 Blocks 05.04.2019 17:59:00 05.04.2019 17:59:08 8 Table Scan: XO.PAYMENTS: 278127738 out of 278127738 Blocks done REPORTS 00000005FA90E240 896485255 98qwqt8uqyjw7 0 2 TABLE ACCESS SAMPLE 0 3
— при рекордно малой продолжительности ELAPSED_SECONDS = 8 для немаленькой таблицы — 278,127,738 Blocks — и только пустые SQL_PLAN_HASH_VALUE, SQL_EXEC_START, SQL_EXEC_ID да SQL_PLAN_OPTIONS выдают неладное)
В ASH та же операция Dynamic Sampling также отчётливо отображена:
SQL> select inst_id,
2 session_id,
3 session_serial#,
4 sql_id,
5 sql_plan_hash_value,
6 sql_full_plan_hash_value,
7 sql_exec_id,
8 count(*),
9 min(sample_time),
10 max(sample_time)
11 from gv$active_session_history
12 where sql_id = '98qwqt8uqyjw7'
13 group by inst_id,
14 session_id,
15 session_serial#,
16 sql_id,
17 sql_exec_id,
18 sql_plan_hash_value,
19 sql_full_plan_hash_value
20 /
INST_ID SESSION_ID SESSION_SERIAL# SQL_ID SQL_PLAN_HASH_VALUE SQL_FULL_PLAN_HASH_VALUE SQL_EXEC_ID COUNT(*) MIN(SAMPLE_TIME) MAX(SAMPLE_TIME)
---------- ---------- --------------- ------------- ------------------- ------------------------ ----------- ---------- ---------------------- ---------------------
1 4974 54090 98qwqt8uqyjw7 637899589 2462500783 9 05.04.19 17:59:01,494 05.04.19 17:59:09,521 -- здесь
1 4974 54090 98qwqt8uqyjw7 637899589 2462500783 16777216 2068 05.04.19 17:59:10,525 05.04.19 18:33:42,965
— те же 8 секунд / 9 записей ASH, в отличие от V$SESSION_LONGOPS с определённым SQL_PLAN_HASH_VALUE, но логично пустым SQL_EXEC_ID
Причина — форсированный через SQL Patch уровень DS:
SQL> @spm_check4sql_id 98qwqt8uqyjw7 SPM_TYPE SQL_HANDLE PATCH_NAME ORIGIN VERSION CREATED LAST_MODIFIED LAST_EXECUTED LAST_VERIFIED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE SPM_SIGNATURE SQL_EXACT_SIGNATURE SQL_FORCE_SIGNATURE ---------- --------------------- ------------------ -------------- ---------- ------------------- ------------------- ------------- ------------- ------- -------- ----- ---------- --------- --------------------- --------------------- --------------------- SQL Patch SQL_867bc1c7ebfabd0d 98qwqt8uqyjw7_DS6 MANUAL-SQLTUNE 12.2.0.1.0 05.04.2019 17:27:17 05.04.2019 17:27:17 YES NO NO YES NO 9690552087619484941 9690552087619484941 1482752548320229273 SQL> @sqlpatch_hints 98qwqt8uqyjw7_DS6 SQL_PATCH_HINTS -------------------- dynamic_sampling(6) SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( '98qwqt8uqyjw7',format => 'all allstats advanced last')); ... Note ----- - dynamic statistics used: dynamic sampling (level=6) - SQL patch "98qwqt8uqyjw7_DS6" used for this statement
Уважаемый коллега Максим Филатов продолжает изыскания:
1) в ходе экспериментов с Infiniband на общедоступном железе Максим доказал/добился внесения важного уточнения в неоднозначную документацию поддержки Oracle Clusterware and RAC Support for RDS Over Infiniband (Doc ID 751343.1):
In 12.2+ version, RDS is supported only for databases running on the engineered systems, and the databases on non-engineered systems always use UDP and the database alert.log will show it is using UDP instead of RDS. This is true even if RDS is linked into the oracle binary.
— не факт, что именно с версии 12.2 (просто на версии 12.2.0.1 проводилось тестирование), но, по крайней мере, теперь определённо: RDS == engineered systems
2) тестирует NVMe устройство типа Micron-MTFDHAL3T2TCU, при этом:
Maximum Large MBPS=12951.92 @ Small=0 and Large=1
— 12,5 GBPS !, т.е. за разные FULL SCAN / Direct Path Read операции можно быть спокойным)
Наиболее точное определение хинта DYNAMIC_SAMPLING можно найти у Джонатана Льюиса:
You can use the dynamic_sampling() hint in two different ways in an SQL statement.
- If you use the hint in the “cursor-level” form: /*+ dynamic_sampling ({level}) */ e.g. /*+ dynamic_sampling(4) */, this is equivalent to setting the parameter optimizer_dynamic_sampling to that level for the duration of that query.
- If you use the hint in the “table-level” form /*+ dynamic_sampling({alias} {level}) */ e.g. /*+ dynamic_sampling(ord 4) */ you are directing Oracle to sample a specific table unconditionally. In this form, the sample size for levels 1 to 9 is 2level-1 * “basic sample size”(which is set to 32 blocks by default by parameter _optimizer_dyn_smp_blks.) You can have multiple hints of this form in a single query, one for each table you want sampled.
Интересно проверить, насколько изменились описанные трудности форсирования DYNAMIC SAMPLING в новой версии Oracle 19c, используемой в https://livesql.oracle.com, на том же примере: (more…)
Если вдруг в приступе доброты душевной разработчик решил не ограничивать себя в фантазиях потребление ресурсов и, например, в определении materialized view
(или в запросе) по-простому вставил /*+ PARALLEL */, и это породило не всегда приятный для администраторов DML вида:
/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."SOME_MATVIEW" select /*+ parallel */ ...
, который в полном соответствии с Automatic Degree of Parallelism (AutoDOP) is enabled when «PARALLEL_DEGREE_POLICY=MANUAL and PARALLEL HINT» is Used at SQL Statement (Doc ID 1542556.1) и по данным SQL MONITOR:
Parallel Execution Details (DOP=30 , Servers Requested=224 , Servers Allocated=60)
— запрашивает 224 PX процесса, но получает «лишь» 60 из 60 имеющихся:
12.2.0.1@ SQL> @param parallel_max_servers NAME VALUE DSC --------------------- ------ ------------------------------------------- parallel_max_servers 60 maximum parallel query servers per instance
— простой SQL Patch:
SQL> @sqlpatch+ 15b35sdpa9rgg "PARALLEL(8)" "15b35sdpa9rgg_PARALLEL_8"
к счастью, приводит ситуацию в порядок: (more…)
С Иваном Постниковым наблюдали/разбирали нечасто встречаемое ожидание library cache load lock, в течение заметного времени наблюдавшееся у ряда сессий 2-го инстанса:
12.1.0.2@inst#2 SQL> select * from v$session where state = 'WAITING' and event = 'library cache load lock'; SID SERIAL# USERNAME COMMAND STATUS SERVER SQL_ID SQL_CHILD_NUMBER SQL_EXEC_START SQL_EXEC_ID MODULE ACTION ROW_WAIT_OBJ# LAST_CALL_ET BLOCKING_SESSION_STATUS BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_SESSION_STATUS FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION EVENT P1TEXT P1RAW P2TEXT P2RAW P3TEXT P3RAW WAIT_CLASS SECONDS_IN_WAIT STATE ----- ---------- -------- ------- -------- --------- ------------- ---------------- -------------- ----------- ------------------------ ------ ------------- ------------ ------------------------ ----------------- ---------------- ----------------------------- ----------------------- ---------------------- ------------------------ --------------- ---------------- ------------- ---------------- ------------------ ---------------- ----------- --------------- ------- 13 54031 APPS 3 ACTIVE DEDICATED 8r49n0b59js0a 0 e:SQLGL:cp:xla/XLADRPGLT SQLGL/ 2707322 81853 VALID 2 392 VALID 2 392 library cache load lock object address 0000005D14491B18 lock address 0000005CA8C05AD0 100*mask+namespace 0000000000010003 Concurrency 80854 WAITING 652 55148 APPS 3 ACTIVE DEDICATED a742atptz32bs 0 e:SQLGL:cp:xla/XLADRPGLT SQLGL/ -1 81853 VALID 2 392 VALID 2 392 library cache load lock object address 0000005D14491B18 lock address 0000005DA2ACFDF0 100*mask+namespace 0000000000010003 Concurrency 80854 WAITING 1632 58741 APPS 3 ACTIVE DEDICATED gn7fg92jr1vkj 0 e:SQLGL:cp:xla/XLADRPGLT SQLGL/ 2707322 81851 VALID 2 392 VALID 2 392 library cache load lock object address 0000005D14491B18 lock address 0000005C037E1AC0 100*mask+namespace 0000000000010003 Concurrency 81758 WAITING 1801 31526 APPS 3 ACTIVE DEDICATED 99340wc8xpd52 0 e:SQLGL:cp:xla/XLADRPGLT SQLGL/ 2707322 81852 VALID 2 392 VALID 2 392 library cache load lock object address 0000005D14491B18 lock address 0000005BA6313E70 100*mask+namespace 0000000000010003 Concurrency 80854 WAITING 2495 38991 APPS 3 ACTIVE DEDICATED 39wnr00vh9t1b 0 e:SQLGL:cp:xla/XLADRPGLT SQLGL/ -1 81852 VALID 2 392 VALID 2 392 library cache load lock object address 0000005D14491B18 lock address 0000005DA151AF38 100*mask+namespace 0000000000010003 Concurrency 80854 WAITING
, содержимое ROW_WAIT_OBJ# не очень полезно:
SQL> select * from dba_objects where object_id = 2707322; OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ----- ----------- ---------- -------------- ----------- SYS USER$ 2707322 2707320 TABLE
, судя по пустым SQL_EXEC_START / SQL_EXEC_ID запросы/курсоры находятся в процессе погрузки в Library Cache (на что указывает и название ожидания длительностью SECONDS_IN_WAIT 80000+ сек) и имеют сходные тексты, относящиеся к одной и той же временной по сути, но не по определению (TEMPORARY=N) таблице: (more…)
До обновления 12.1 -> 12.2 запрос удовлетворительно (SLA, timeouts,…) быстро выполнялся по плану:
SQL_ID 4dgcbuug0z2mk
--------------------
Plan hash value: 1982197705
-----------------------------------------------------------------------------------------------------
| Id | Operation | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 8 (100)| |
| 1 | VIEW | 1 | 1258 | 8 (13)| 00:00:01 |
| 2 | WINDOW SORT | 1 | 1180 | 8 (13)| 00:00:01 |
| 3 | SORT GROUP BY ROLLUP | 1 | 1180 | 8 (13)| 00:00:01 |
| 4 | NESTED LOOPS | 1 | 1180 | 7 (0)| 00:00:01 |
| 5 | NESTED LOOPS | 1 | 1180 | 7 (0)| 00:00:01 |
| 6 | NESTED LOOPS | 1 | 1171 | 6 (0)| 00:00:01 |
| 7 | NESTED LOOPS | 1 | 24 | 5 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | 1 | 12 | 4 (0)| 00:00:01 |
| 9 | INDEX UNIQUE SCAN | 1 | | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED | 1 | 12 | 1 (0)| 00:00:01 |
| 11 | INDEX RANGE SCAN | 1 | | 0 (0)| |
| 12 | VIEW | 1 | 1147 | 1 (0)| 00:00:01 | -- **
| 13 | UNION-ALL PARTITION | | | | | -- ***
| 14 | NESTED LOOPS | 1 | 156 | 24 (0)| 00:00:01 |
| 15 | NESTED LOOPS | 1 | 156 | 24 (0)| 00:00:01 |
| 16 | NESTED LOOPS OUTER | 1 | 100 | 22 (0)| 00:00:01 |
| 17 | NESTED LOOPS | 1 | 60 | 21 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID BATCHED | 1 | 50 | 20 (0)| 00:00:01 | -- ****
| 19 | INDEX RANGE SCAN | 17 | | 4 (0)| 00:00:01 | -- ****
| 20 | MAT_VIEW ACCESS BY INDEX ROWID BATCHED| 1 | 10 | 1 (0)| 00:00:01 | -- ****
| 21 | INDEX RANGE SCAN | 1 | | 0 (0)| | -- ****
| 22 | TABLE ACCESS BY INDEX ROWID | 1 | 40 | 1 (0)| 00:00:01 | -- ****
| 23 | INDEX UNIQUE SCAN | 1 | | 0 (0)| | -- ****
| 24 | INDEX RANGE SCAN | 1 | | 0 (0)| | -- ****
| 25 | TABLE ACCESS BY INDEX ROWID | 1 | 56 | 2 (0)| 00:00:01 | -- ****
...
| 52 | INDEX UNIQUE SCAN | 1 | | 0 (0)| |
| 53 | TABLE ACCESS BY INDEX ROWID | 1 | 9 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
...
OLD_PUSH_PRED(@"SEL$641071AC" "MS"@"SEL$3" ("INVOICE"."CLIENT_ID")) -- *
...
END_OUTLINE_DATA
*/
, с использованием OJPPD — Old-stype (non-cost-based) Join Predicate Push-Down (*) высокоизбирательного условия («INVOICE».»CLIENT_ID») в обзор (**) вида UNION-ALL PARTITION (***) с последующим быстрым индексным доступом (****)
В версии 12.2, похоже, что-то существенно изменилось, OJPPD в процессе компиляции плана перестал рассматриваться совсем, в то время как Cost-based JPPD, похоже, как и в 12.1 (возможно и раньше), отказывается работать с Union-All View под надуманным предлогом (*), меняя при этом устаревшую (?) операцию UNION-ALL PARTITION на UNION-ALL: (more…)
Заметно замедлившайся рутинная операция обновления matview в версии Oracle 12.2 указывала, что выполняющая обновление сессия помимо необходимых и понятных операций DELETE+INSERT значительную часть времени проводила в дублирующих общую статистику DBMS_STATS подсчётах Cardinality всех подряд странного набора matview нашей базы: (more…)
 | Игорь Усольцев к записи Новости технологий Infiniband/… |
 | Yury Pudovchenko к записи Новости технологий Infiniband/… |
| Yury Pudovchenko к записи Новости технологий Infiniband/… |
 | Сергей к записи 12c: In-Memory деградация и ад… |
 | vesselova к записи Hintset Object |
Oracle Optimizer Jonathan Lewis blog Richard Foote’s Oracle Blog Коллегиальный блог