Oracle mechanics

14.05.2017

Конкатенация значений столбцов при превышении VARCHAR2 лимита 4000 bytes

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

, т.е. в случаях, когда использование VARCHAR2-функций типа LISTAGG заканчивается

ORA-01489: result of string concatenation is too long

LISTAGG_CLOB

Использовавшаяся поначалу User-Defined Aggregate Function типа LISTAGG_CLOB в плане скорости выполнения показывала самые грустные рез-ты: (more…)

14.12.2016

12c: наблюдаемые ограничения хранения в AWR и управления адаптивными планами

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

Для демонстрации/проверки многообразия планов у меня есть простой скрипт SQL_PLAN_HASHS.SQL, вытаскивающий хеши планов из AWR и/или SqlArea по SQL_ID [и, опционально, по FULL_PLAN_HASH_VALUE и/или PLAN_HASH_VALUE], и один планов запросов SQL_ID=fkw6hb5mrw02t и PHV=3887377781 а течение последних нескольких дней показывал следующую примечательную картину:

12.1.0.2.@ SQL> @sql_plan_hashs fkw6hb5mrw02t "" 3887377781
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
SQL fkw6hb5mrw02t           1266372674      3887377781 12.12.2016 17:47:16
AWR fkw6hb5mrw02t           2002632649      3887377781 31.10.2015 23:32:38

— в SqlArea (SRC=SQL) и в DBA_HIST_SQL_PLAN (SRC=AWR) этот запрос сосуществует с одним и тем же PLAN_HASH_VALUE, но с разными FULL_PLAN_HASH_VALUE

При этом, если для «старого» FULL_PLAN_HASH_VALUE=2002632649 был зафиксирован лишь один PLAN_HASH_VALUE:

SQL> @sql_plan_hashs fkw6hb5mrw02t 2002632649
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
AWR fkw6hb5mrw02t           2002632649      3887377781 31.10.2015 23:32:38

, то для относительно «свежего» FULL_PLAN_HASH_VALUE=1266372674 наблюдается два отличных PHV:

SQL> @sql_plan_hashs fkw6hb5mrw02t 1266372674
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
AWR fkw6hb5mrw02t           1266372674       864598493 04.10.2016 12:37:20
SQL fkw6hb5mrw02t           1266372674      3887377781 12.12.2016 17:47:16

— как и положено для адаптивного плана
В этом ничего удивительного нет, и я уже описывал подобную ситуацию сосуществования адаптивного и НЕадаптивного планов с совпадающими PHV, однако, в рассматриваемом случае проявляется пара новых проблем:

1) план с FULL_PLAN_HASH_VALUE=1266372674 и PHV=3887377781 наблюдается только в SqlArea Shared Pool, и, повторюсь, в течение достаточно длительного времени не сохраняется в AWR (не может быть сохранен в AWR в принципе?)

2) собственно сосуществование (и возможность управления) 2-мя различающимися адаптивными планами одного простого (без использования обзоров) запроса с совпадающими PHV и различными FULL_PLAN_HASH_VALUE (more…)

14.09.2016

Параллельное выполнение UNION ALL PUSHED PREDICATE и проблемы фиксации параллельных планов

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

I. Параллельное выполнение UNION ALL PUSHED PREDICATE

Иногда имеет смысл параллельное выполнение запроса с операцией UNION ALL PUSHED PREDICATE в плане, и если в 11g эту комбинацию воспроизвести просто — и на простом тесткейсе это выглядит так:

11.2.0.3.SCOTT@ SQL> select --+ PARALLEL(4) PUSH_PRED(d)
  2  * from scott.emp e,
  3        (select * from scott.dept
  4         union
  5         select * from scott.dept) d
  6  where d.deptno = e.deptno
  7    and e.job = 'SALESMAN'
  8  /

4 rows selected.

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |     7 |   476 |     5  (20)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                   |          |       |       |            |          |        |      |            | -- параллельное выполнение
|   2 |   PX SEND QC (RANDOM)             | :TQ10000 |     7 |   476 |     5  (20)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS                   |          |     7 |   476 |     5  (20)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR             |          |       |       |            |          |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL            | EMP      |     3 |   114 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |     VIEW                          |          |     1 |    30 |     4  (50)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |      SORT UNIQUE                  |          |     2 |    40 |     4  (75)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |       UNION ALL PUSHED PREDICATE  |          |       |       |            |          |  Q1,00 | PCWP |            | -- операция UNION ALL PUSHED PREDICATE
|   9 |        TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    20 |     1   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 10 |         INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  11 |        TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    20 |     1   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 12 |         INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 4 because of hint

, то в Oracle 12c (возможно, как рез-т исправления Bug 14217009 : WRONG RESULTS FROM PARALLEL QUERY USING UNION ALL и ему подобных?) аналогичную операцию UNION ALL PUSHED PREDICATE уже не так просто заставить выполняться параллельно: (more…)

30.03.2016

12c: Automatic Dynamic Statistics в присутствии SPM Baseline — 2. Testcases

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

В дополнение к предыдущему могут быть полезны простые testcase-ы поведения Oracle 12.1.0.2 при разборе / hard parse запроса с применением SQL Plan Baseline

Первый case был предложен/подготовлен коллегой Русланом Бикбаевым на противопоставлении Baseline-а,фиксирующего FULL TABLE SCAN и добавленным позднее индексом:

SQL> create table large_table (c1 number, c2 varchar2 (1000))
  2  /

Table created.

SQL> insert into large_table
  2    select level, 'some fool text and number ' || level
  3      from dual
  4    connect by level < 1000000 5 / 999999 rows created. SQL> exec dbms_stats.gather_table_stats('','LARGE_TABLE')

PL/SQL procedure successfully completed.

SQL> select c2 from large_table where c1 = 402200;

C2
---------------------------------------------------------------------------------
some fool text and number 402200

1 row selected.

SQL> @xplan "" "" "-predicate +alias"

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  cgrtc39b07cg4, child number 0
-------------------------------------
select c2 from large_table where c1 = 402200

Plan hash value: 1101256009

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |  1643 (100)|          |
|   1 |  TABLE ACCESS FULL| LARGE_TABLE |     1 |    38 |  1643   (1)| 00:00:01 |
---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / LARGE_TABLE@SEL$1

SQL> -- фиксация плана 
SQL> @bl_create cgrtc39b07cg4 1101256009 "2test_baseline"

Baseline SQL_ff24385815ccded7 SQL_PLAN_gy91sb0awtrqr07ba49cd was [re]created
for SQL_ID=cgrtc39b07cg4, SQL_PLAN_HASH=1101256009

SQL> create index lt_idx on large_table(c1)
  2  /

Index created.

SQL> select c2 from large_table where c1 = 402200;

1 row selected.

SQL> @xplan "" "" "-predicate +alias"

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  cgrtc39b07cg4, child number 1
-------------------------------------
select c2 from large_table where c1 = 402200

Plan hash value: 1101256009

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |  1643 (100)|          |
|   1 |  TABLE ACCESS FULL| LARGE_TABLE |     1 |    38 |  1643   (1)| 00:00:01 |
---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / LARGE_TABLE@SEL$1

Note
-----
   - SQL plan baseline SQL_PLAN_gy91sb0awtrqr07ba49cd used for this statement

, 10053 трейс которого отчётливо показывает все 3 фазы разбора: (more…)

02.03.2016

12c: данные SPM Baseline в SYS.SQLOBJ$PLAN

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

Ежели в версии 11g Oracle хранил данные всех элементов системы SQL Plan Mangement (SPM, включая SQL Patch, SQL Profile и SQL Plan Baseline (SPB)) в SYS.SQLOBJ$DATA.COMP_DATA, то, начиная с 12.1 данные, составляющие собственно Baseline, переместились в clob OTHER_XML новой таблицы SQLOBJ$PLAN, которая также содержит полный план выполнения на момент создания SPM Baseline-а

Этот сохранённый план может быть любопытен в иллюстративных целях, например, при просмотре через Enterprise Manager/Cloud Control, или через соответствующий command line скрипт SPB12.SQL представляет информацию в следующем виде: (more…)

17.02.2016

UPGRADE 12c: краткий список возможных проблем

Filed under: bugs,Oracle,Plan Management — Игорь Усольцев @ 22:37
Tags:

В блогах Oracle мелькнула заметка Mike Dietrich Some Parameter Recommendations for Oracle 12c, обобщающая некоторые проблемы обновления

Появилась и пропала (#гугльпомнит), вероятно, ввиду недостаточного оптимизма)

Однако, кроме описания различных случаев/багов и соответствующих параметров/workaround-ов, заметка содержала практически официальные описания параметров управления SQL Plan Directives (SPD): (more…)

13.01.2016

Oracle 12c Adaptive Soft Parse overhead

Логичным развитием темы предыдущей заметки будет оценка вклада типа Oracle 12c Adaptive Soft Parse overhead на систему в целом через подсчёт доли в общем DB Time (через кол-во ASH rows) «адаптивных» запросов в момент между генерацией плана и началом выполнения запроса (sql_plan_hash_value > 0 AND sql_exec_id is null)

Как-то так раскадка DB Time выглядит на примере системы (в бОльшей части OLTP типа) для sql типа SELECT:

SQL> select plan_hash_value,
  2         sql_exec_id,
  3         adaptive_plan,
  4         sum(ash_rows),
  5         listagg(session_state || '(' || ash_rows || ')', ';') within group(order by ash_rows desc) as DB_TIME_WASTE,
  6         to_char(RATIO_TO_REPORT(sum(ash_rows)) OVER() * 100,'990.99')                              as "PerCent"
  7    from (select decode(nvl(sql_plan_hash_value, -1), -1, 'not yet', 0, '0', 'exists')              as PLAN_HASH_VALUE,
  8                 nvl2(sql_exec_id, 'exists', 'not yet')                                             as SQL_EXEC_ID,
  9                 session_state,
 10                 sql_adaptive_plan_resolved                                                         as ADAPTIVE_PLAN,
 11                 count(*)                                                                           as ASH_ROWS
 12            from gv$active_session_history
 13           where sql_opname = 'SELECT'
 14           group by decode(nvl(sql_plan_hash_value, -1), -1, 'not yet', 0, '0', 'exists'),
 15                    nvl2(sql_exec_id, 'exists', 'not yet'),
 16                    session_state,
 17                    sql_adaptive_plan_resolved)
 18   group by plan_hash_value, sql_exec_id, adaptive_plan
 19   order by sum(ash_rows) desc
 20  /
 
PLAN_HASH_VALUE SQL_EXEC_ID ADAPTIVE_PLAN SUM(ASH_ROWS) DB_TIME_WASTE                 PerCent
--------------- ----------- ------------- ------------- ----------------------------- -------
exists          exists                  1        210576 ON CPU(173241);WAITING(37335)   85.23 -- фаза обычного выполнения, планы отмечены как адаптивные в 85% случаев
exists          not yet                 1         30274 WAITING(20739);ON CPU(9535)     12.25 -- (*) 12c Adaptive Soft Parse, то что подлежит оценке
0               exists                  0          2279 ON CPU(1254);WAITING(1025)       0.92 -- (**) строки нормального выполнения в ASH с PLAN_HASH_VALUE=0, особый случай
exists          not yet                 0          2161 WAITING(1939);ON CPU(222)        0.87 -- Soft Parse НЕадаптивных планов
0               not yet                 0          1415 ON CPU(986);WAITING(429)         0.57 -- Hard Parse НЕадаптивный
0               not yet                 1           366 ON CPU(283);WAITING(83)          0.15 -- Hard Parse адаптивный
0               exists                  1             1 ON CPU(1)                        0.00 -- (**) выполнение в ASH с PLAN_HASH_VALUE=0, особый случай
exists          exists                  0             1 ON CPU(1)                        0.00 -- фаза обычного выполнения НЕадаптивных планов

(more…)

21.06.2015

Dynamic sampling (level=AUTO) в параллельных запросах с фиксированным планом выполнения

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

При выполнении параллельного запроса наблюдалась неприятная картина ожиданий:

12.1.0.2.@ SQL> @ash_sqlmon2 8d49sjc17xwuc
 
LAST_PLSQL  SQL_ID        PLAN_HASH_VALUE   ID PLAN_OPERATION                                                                   PX ASH_ROWS WAIT_PROFILE
----------- ------------- --------------- ---- -------------------------------------------------------------------------------- -- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Soft Parse  8d49sjc17xwuc       162625646    0 sql_plan_hash_value > 0; sql_exec_id is null                                     39    26689 library cache lock(19424); cursor: pin S wait on X(7223); kksfbc child completion(29); ON CPU(9); db file sequential read(3); db file scattered read(1)
Main Query  8d49sjc17xwuc       162625646    0   SELECT STATEMENT                                                                           
                                             1     PX COORDINATOR                                                                           
...
                                            38                                                           TABLE ACCESS FULL      19      339 db file scattered read(194); ON CPU(80); db file sequential read(64); latch: gcs resource hash(1)
                                            39                                                     TABLE ACCESS BY INDEX ROWID  19       96 ON CPU(90); db file sequential read(6)
                                            40                                                       INDEX RANGE SCAN           19      158 ON CPU(129); db file sequential read(29)
                                            41                                                   INDEX RANGE SCAN               19      834 db file sequential read(737); ON CPU(97)
                                            42                                                 TABLE ACCESS BY INDEX ROWID      19     5278 db file sequential read(4076); gc cr grant 2-way(597); ON CPU(378); gcs drm freeze in enter server mode(219); gc current block 2-way(5); latch: gcs resource hash(1); gc cr request(1); read by other session(1)
...
SQL Summary                             0    0 ASH fixed 1 execs from 41 sessions                                                     34181  ash rows were fixed from 29.05.2015 12:13:09 to 29.05.2015 12:30:48

— где наряду с «нормальными» ожиданиями по ходу выполнения (ID 38-42, например), преобладают типичные для стадии Soft Parse (sql_plan_hash_value > 0 AND sql_exec_id is null) ожидания library cache lock и cursor: pin S wait on X, вероятно, связанные с параллельным выполнением судя по кол-ву ожидающих PX-процессов (столбец PX)

Что можно проверить во-время (выполнения запроса) запущенным скриптом: (more…)

02.03.2015

Стабильность SQL Plan Baseline при обновлениях

Filed under: Oracle,Plan Management,SQL Tuning — Игорь Усольцев @ 22:13
Tags:

Всё указывало на запрос 9s7ppf88qzx2w: и экспресс-анализ по пользователю/модулю доморощенным скриптом ASH_SQL_WAIT_TREE.SQL:

SQL> @ash_sql_wait_tree "client_id  = 'APPS_USER' and module = 'e:AR:frm:XXXXX'" 0
 
LVL BLOCKING_TREE  EVENT                     WAITS_COUNT EXECS_COUNT AVG_WA SQL_ID
--- -------------- ------------------------- ----------- ----------- ------ -------------
  1 (USER)         On CPU / runqueue                 691          10      0 9s7ppf88qzx2w
  1 (USER)         db file sequential read            83           7     53 9s7ppf88qzx2w
  1 (USER)         gc current block 2-way             29           3      3 9s7ppf88qzx2w
  1 (USER)         gc cr grant 2-way                  12           3      4 9s7ppf88qzx2w
  1 (USER)         db file sequential read             9           9    295 63sr2px4kd9da
...

, и данные SQL трейса: (more…)

07.12.2014

ROWID доступ к партицированной таблице

Filed under: Oracle,Plan Management,SQL Tuning — Игорь Усольцев @ 10:53
Tags: ,

С Леонидом Борчуком разбирали страшное:

SQL ordered by Elapsed Time           DB/Inst: OEBS/OEBS1  Snaps: 77632-77633

        Elapsed                  Elapsed Time                                   
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id    
---------------- -------------- ------------- ------ ------ ------ -------------
        49,260.9            139        354.39   72.4    9.0   93.5 2b01txt3hnjha -- запрос потребляет более 70% DB Time
Module: e:SQLAP:bes:oracle.apps.xla.accounting.postproce
UPDATE /*+ PARALLEL (AEL) */ XLA_AE_LINES AEL SET AEL.ANALYTICAL_BALANCE_FLAG = 
:B8 ,AEL.LAST_UPDATE_DATE = :B5 ,AEL.LAST_UPDATED_BY = :B7 ,AEL.LAST_UPDATE_LOGI
N = :B6 ,AEL.PROGRAM_UPDATE_DATE = :B5 ,AEL.PROGRAM_APPLICATION_ID = :B4 ,AEL.PR
OGRAM_ID = :B3 ,AEL.REQUEST_ID = :B2 WHERE (AEL.ROWID) IN (SELECT ...

         5,312.4          1,801          2.95    7.8   99.2     .0 7r2xtc0ru1h0s -- , на порядок опережая конкурентов
...

Один из стандартных запросов OEBS, судя по истории обычно выполнялся небыстро, потребляя значительные I/O ресурсы на direct path read (судя по соотношению DISK_READS_PER_EXEC и READS_PER_EXEC) большим количеством параллельных процессов (PX_PER_EXEC): (more…)

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

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