Заметно замедлившайся рутинная операция обновления matview в версии Oracle 12.2 указывала, что выполняющая обновление сессия помимо необходимых и понятных операций DELETE+INSERT значительную часть времени проводила в дублирующих общую статистику DBMS_STATS подсчётах Cardinality всех подряд странного набора matview нашей базы: (more…)
Поскольку некоторые инструменты разработки генерируют связанные переменные вида :"1", :"2",.., а некоторые классы запросов могут по факту вытолнятся только на Readonly Standby, не присутствуя на Primary ни в каком виде, для фиксации/оптимизации плана бывает необходимо каким-то образом поместить такой запрос (sql_id) в Primary Shared Pool, что можно сделать стандартным способом через execute immediate:
12.1.0.2.@ SQL> declare 2 t date; 3 begin 4 execute immediate 'SELECT sysdate from dual where 1 = :"1"' into t using 1; 5 end; 6 / PL/SQL procedure successfully completed. SQL> select sql_id, sql_text from v$sql where sql_text like 'SELECT sysdate from dual where 1 = :"1"%'; SQL_ID SQL_TEXT ------------- ----------------------------------------------------------------------------------------- 2cg0gucz7scgq SELECT sysdate from dual where 1 = :"1"
, что для громоздких запросов с большим кол-вом bind-ов может потребовать определённых усилий
Прямое выполнение запроса в SQL*Plus для этой цели приспособить затруднительно: (more…)
Скачок Load Average на обеих нодах:
high_LA_06062018.png
был спровоцирован частым выполнением запроса со стабильным, но не очень удачным планом 4125831042: (more…)
Евгений Калинин обнаружил запрос, факт успешности выполнения которого неожиданно зависел от использования/неиспользования столбцов Inline view(*) а качестве параметров PLSQL_FUNCTION, и худшем случае завершавшийся простой ошибкой:
12.2.0.1.@ SQL> with s_q_by_month as
2 (select contract_id,
...
19 group by contract_id,
...
30 s_q as
31 (select plsql_function(sum_rub, -- вот этих столбцов *
32 max_amt,
...
36 from (select contract_id, -- вот этого Inline view
...
43 max(amt) as max_amt,
44 sum(amt_rub) as sum_rub,
...
48 from s_q_by_month
49 group by contract_id,
...
56 select
57 * from s_q
58 /
* from s_q
*
ERROR at line 57:
ORA-00979: not a GROUP BY expression
, возникавшей на этапе построения плана, обрывая трассировку где-то в районе Query Transformation — в данном случае трейс заканчивался упоминанием OJE(Outer Join Elimination)
Соответствующая трассировка по номеру ошибки, почерпнутая из Bug 21799456 : ORA-979 ERROR GENERATED FROM A SELECT STATEMENT IN 12C: (more…)
В докуметации Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1) определённо(*) рекомендуется отключить адаптивные фичи:
######### # # Optimizer Parameters # # Release 12 uses cost based optimization. The following optimizer # parameters must be set as shown, and should not be changed. # It is recommended to disable the adaptive optimizer features: adaptive plans, # automatic re-optimization, and SQL plan directives. # ######### optimizer_adaptive_features = FALSE #MP -- Mandatory parameters --*
— как указано, чтобы использовать «чистый» CBO, т.о. блокируя избыточную генерацию планов и общую нестабильность (см., например, 12c: адаптивная оптимизация и CBO), что и было давно выполнено на уровне системы:
12.1.0.2@ SQL> @param optimizer_adaptive_features NAME VALUE IS_DEF IS_MOD DSC ---------------------------- ------ -------- ---------- -------------------------- optimizer_adaptive_features FALSE FALSE FALSE controls adaptive features
, отключив Adaptive Plans, Automatic Re-Optimization / Statistics Feedback, and SQL plan directives как следствие
Однако, так просто условно-избыточная генерация планов выполнения не прекратилась: (more…)
При выполнении ALTER TABLE DROP CONSTRAINT случился TM — library cache deadlock распределённого (RAC) вида, отразившийся в ASH следующим образом:
12.1.0.2@ SQL> @ash_sql_wait_tree "(session_id,session_serial#) in ((4460,16750)) and sample_time > trunc(sysdate,'hh24')" 0 LVL INST_ID BLOCKING_TREE EVENT WAIT_CLASS DATA_OBJECT_P1RAW IN_PARSE SQL_CHILD_NUMBER WAITS_COUNT EXECS_COUNT AVG_WA SESS_COUNT BLOCK_SID MIN_STIME MAX_STIME SQL_ID SQL_PLAN_HASH_VALUE SQL_EXEC_ID SQL_TEXT --- ------- --------------- --------------------- ------------ ------------------ -------- ---------------- ----------- ----------- ------ ---------- ---------- -------------------------- -------------------------- ------------- ------------------- ----------- ---------------------------------------------------------------------------------------------------- 1 1 (USER) enq: TM - contention Application MY.PAYMENT_Z. N 0 11 1 0 1 GLOBAL i# 26-JUN-17 04.06.39.431 PM 26-JUN-17 04.06.49.451 PM 7kpzqksdg4wu8 0 1 1 1 (USER) enq: TM - contention Application MY.PAYMENT_Z. N 0 2 1 0 1 VALID i#2 26-JUN-17 04.06.50.451 PM 26-JUN-17 04.06.51.451 PM 7kpzqksdg4wu8 0 1 1 1 (USER) library cache pin Concurrency 000000261138EA48 Y 0 1 0 1 1 GLOBAL i# 26-JUN-17 04.06.37.431 PM 26-JUN-17 04.06.37.431 PM 73f85gf8qvb34 2024994732 0 select NULL AS TABLE_CAT, OWNER AS TABLE_SCHEM, 'NO' as IS_AUTOINCREMENT, cc.COMMENTS AS REMARKS,OWN 1 1 (USER) library cache pin Concurrency 000000274A98AEE0 Y 0 1 0 0 1 GLOBAL i# 26-JUN-17 04.06.36.431 PM 26-JUN-17 04.06.36.431 PM 6196bjzyj6dv7 2050374072 0 SELECT null as TABLE_CAT, a.OWNER as TABLE_SCHEM, a.TABLE_NAME as TABLE_NAME, a.TEMPORARY as TEMPORA 2 2 (USER) library cache lock Concurrency 00000027E6E364A8 Y -1 4 0 6807 1 VALID i#1 26-JUN-17 04.06.50.574 PM 26-JUN-17 04.06.51.574 PM 0w12fswfwtrdv 3054558131 0 UPDATE payment_z SET ... 2 2 (USER) library cache lock Concurrency 00000027E6E364A8 Y -1 1 0 0 1 GLOBAL i# 26-JUN-17 04.06.49.564 PM 26-JUN-17 04.06.49.564 PM 0w12fswfwtrdv 3054558131 0 UPDATE payment_z SET ... 3 1 (USER) enq: TM - contention Application MY.PAYMENT_Z. N 0 4 1 0 1 VALID i#2 26-JUN-17 04.06.50.451 PM 26-JUN-17 04.06.51.451 PM 7kpzqksdg4wu8 0 1 3 1 (USER) enq: TM - contention Application MY.PAYMENT_Z. N 0 2 1 0 1 GLOBAL i# 26-JUN-17 04.06.49.451 PM 26-JUN-17 04.06.49.451 PM 7kpzqksdg4wu8 0 1 4 2 (USER) library cache lock Concurrency 00000027E6E364A8 Y -1 4 0 6807 1 VALID i#1 26-JUN-17 04.06.50.574 PM 26-JUN-17 04.06.51.574 PM 0w12fswfwtrdv 3054558131 0 UPDATE payment_z SET ... 4 2 (USER) library cache lock Concurrency 00000027E6E364A8 Y -1 2 0 0 1 GLOBAL i# 26-JUN-17 04.06.49.564 PM 26-JUN-17 04.06.49.564 PM 0w12fswfwtrdv 3054558131 0 UPDATE payment_z SET ... 5 1 (USER) enq: TM - contention Application MY.PAYMENT_Z. N 0 2 1 0 1 GLOBAL i# 26-JUN-17 04.06.49.451 PM 26-JUN-17 04.06.49.451 PM 7kpzqksdg4wu8 0 1 11 rows selected
где: (more…)
По причине ошибки ORA-00600: Internal Error Code, Arguments: [qmxtcsxmlt:xmltype] (Doc ID 2079499.1), характерной для использования функции XMLAgg в бд состояния MOUNTED (standby or non-standby), переписал скрипт ASH_IO_WAITS.SQL для поиска наиболее активных в WAIT_CLASS-ах ‘User I/O‘ и ‘System I/O‘ процессов/запросов на STANDBY и прочих MOUNTED системах
Например, при замедлившемся накате standby (в отсутствии Read Only пользовательских запросов) можно было наблюдать аномально интенсивную IO активность пользовательских процессов RFS в части control file sequential read и RFS write:
SQL> @ash_io_waitsby blocks 10 "1 = 1" INST SQL_PROCESS SUM(WAIT_COUNT) waits% SUM(REQUESTS) reqs% SUM(BLOCKS) blocks% event(waits:requests:blocks) ---- ------------- --------------- ------- ------------- ------- ----------- ------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 (USER) 156134 73.56 156134 11.11 1269165 39.07 control file sequential read(11699:11699:1123994); RFS write(105538:105538:105538); RMAN backup & recovery I/O(29486:29486:29486); Data file init write(6877:6877:6877); control file parallel write(736:736:1472); Disk file operations I/O(1049:1049:1049); Disk file Mirror Read(641:641:641); Log archive I/O(108:108:108) 1 (DBW.) 14696 6.92 1207318 85.94 1207318 37.16 db file parallel write(14696:1207318:1207318) 1 (PR..) 17569 8.28 17569 1.25 523541 16.12 log file sequential read(3984:3984:506025); recovery read(13311:13311:13311); control file sequential read(120:120:4020); direct path write(91:91:91); control file parallel write(31:31:62); direct path read(31:31:31); Disk file operations I/O(1:1:1) 1 (ARC.) 16169 7.62 16169 1.15 234540 7.22 control file sequential read(8614:8614:226728); Log archive I/O(7104:7104:7104); control file parallel write(280:280:560); Disk file Mirror Read(106:106:106); Disk file operations I/O(40:40:40); log file sequential read(25:25:2) 1 (CKPT) 4624 2.18 4624 0.33 8932 0.27 control file parallel write(3301:3301:6602); control file sequential read(1007:1007:2014); Disk file Mirror Read(316:316:316) 1 a5xcbum9zpyzn 1750 0.82 1750 0.12 2912 0.09 control file sequential read(1162:1162:2324); Disk file Mirror Read(588:588:588) 1 6jaghrm3vy74f 769 0.36 769 0.05 1303 0.04 control file sequential read(534:534:1068); Disk file Mirror Read(235:235:235) 1 89z2u0fdrdg66 397 0.19 397 0.03 761 0.02 control file sequential read(364:364:728); Disk file Mirror Read(33:33:33) 1 (M...) 50 0.02 50 0.00 92 0.00 control file sequential read(42:42:84); Disk file Mirror Read(8:8:8) 1 7mgr3uwydqq8j 13 0.01 13 0.00 22 0.00 control file sequential read(9:9:18); Disk file Mirror Read(4:4:4) 10 rows selected
в сравнении с нормальным поведением, где RFS заметен лишь на 4-м месте с 5% прочитанных/записанных blocks: (more…)
Всплеск нагрузки на IO подсистему:
сопровождался, по нашим с Дмитрием Якубеней наблюдениям, дисбалансом в отчёте AWR — совершенно незначительные цифры пользовательской нагрузки (*):
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 73.0 0.0 0.01 0.00
DB CPU(s): 10.5 0.0 0.00 0.00
Background CPU(s): 0.9 0.0 0.00 0.00
Redo size (bytes): 6,734,104.6 3,153.3
Logical read (blocks): 2,698,366.4 1,263.6
Block changes: 41,001.2 19.2
Physical read (blocks): 11,301.7 5.3
Physical write (blocks): 3,319.9 1.6
Read IO requests: 4,417.3 2.1
Write IO requests: 721.0 0.3
Read IO (MB): 44.2 0.0 -- *
Write IO (MB): 13.0 0.0 -- *
...
никак не коррелировали с Total (MB) нагрузкой AWR IO Profile (**): (more…)
В процессе тестирования с Максимом Филатовым Patch 20933264: OUT-OF-PLACE COMPLETE REFRESH NOT USING DIRECT LOAD IN POPULATING OUTSIDE TABLE наблюдали следующее воспроизводимое поведение:
12.1.0.2@ SQL> CREATE MATERIALIZED VIEW scott.EMP_SNAPSHOT2 tablespace users REFRESH complete ON DEMAND as SELECT * FROM "SCOTT"."EMP" join "SCOTT"."DEPT" using(deptno)
2 /
Materialized view created.
SQL> alter session set events '10979 trace name context forever';
Session altered.
SQL> alter session set events '10046 trace name context forever , level 12';
Session altered.
SQL> exec dbms_mview.refresh('"SCOTT"."EMP_SNAPSHOT2"', method => '?', atomic_refresh => false, out_of_place => TRUE)
PL/SQL procedure successfully completed.
После чего SYS.SNAP_REFOP$ содержит три операции: (more…)
— живой и интересный!
Несколько иллюстраций к рассказанному Александром: (more…)
 | Сергей на 12c: In-Memory деградация и ад… |
 | vesselova на Hintset Object |
 | Игорь Усольцев на 12c: In-Memory деградация и ад… |
 | sshumeev на 12c: In-Memory деградация и ад… |
| Игорь Усольцев на dynamic sampling при индексном… |
Oracle Optimizer Jonathan Lewis blog Richard Foote’s Oracle Blog Коллегиальный блог