Oracle mechanics

06.04.2019

Dynamic Sampling Longops

Filed under: heuristics,Oracle — Игорь Усольцев @ 09:23
Tags:

Запись 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

17.03.2019

Новости технологий Infiniband/RDS и NVMe

Filed under: Exadata,Hardware,Oracle — Игорь Усольцев @ 14:41
Tags:

Уважаемый коллега Максим Филатов продолжает изыскания:

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 операции можно быть спокойным)

11.03.2019

Как форсировать применение DYNAMIC SAMPLING на примерах LiveSQL версии 19c

Filed under: hints,Oracle — Игорь Усольцев @ 00:30
Tags: ,

Наиболее точное определение хинта 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…)

10.03.2019

Ограничение DOP с помощью SQL Patch

Filed under: heuristics,Oracle,Plan Management — Игорь Усольцев @ 11:19
Tags:

Если вдруг в приступе доброты душевной разработчик решил не ограничивать себя в фантазиях потребление ресурсов и, например, в определении 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…)

14.12.2018

row cache lock: global deadlock

Filed under: Блокировки,Oracle,shared pool — Игорь Усольцев @ 00:33
Tags:

С Иваном Постниковым наблюдали/разбирали нечасто встречаемое ожидание 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…)

10.12.2018

12.2: JPPD/OJPPD и параметр _OPTIMIZER_PUSH_PRED_COST_BASED

Filed under: 12.2,Oracle,Scripts — Игорь Усольцев @ 12:42
Tags:

До обновления 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…)

17.08.2018

12.2 Materialized View Refresh Statistics Slowdown

Filed under: 12.2,Oracle — Игорь Усольцев @ 01:24
Tags: ,

Заметно замедлившайся рутинная операция обновления matview в версии Oracle 12.2 указывала, что выполняющая обновление сессия помимо необходимых и понятных операций DELETE+INSERT значительную часть времени проводила в дублирующих общую статистику DBMS_STATS подсчётах Cardinality всех подряд странного набора matview нашей базы: (more…)

SQL Developer и связанные переменные вида :”1”

Filed under: Oracle,shared pool — Игорь Усольцев @ 00:20
Tags:

Поскольку некоторые инструменты разработки генерируют связанные переменные вида :"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…)

15.06.2018

Oracle 12c: Optimizer Adaptive Features как генератор планов выполнения и сокращение mutex-ожиданий cursor: pin S

Filed under: Oracle,SQL Tuning — Игорь Усольцев @ 18:06
Tags: , ,

Скачок Load Average на обеих нодах:


high_LA_06062018.png

был спровоцирован частым выполнением запроса со стабильным, но не очень удачным планом 4125831042: (more…)

01.06.2018

12.2: ORA-979 при разборе запросов с использованием WITH clause, PLSQL function и View Merging

Filed under: 12.2,bugs,Oracle — Игорь Усольцев @ 10:25
Tags: ,

Евгений Калинин обнаружил запрос, факт успешности выполнения которого неожиданно зависел от использования/неиспользования столбцов 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…)

← Предыдущая страницаСледующая страница →

Создайте бесплатный сайт или блог на WordPress.com.