Oracle mechanics

25.05.2017

Формальный SQL тюнинг с использованием SQL Patch

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

Активно читающий запрос из AWR:

SQL ordered by Reads           DB/Inst: ORCL/orcl2  Snaps: 344667-344671
-> %Total - Physical Reads as a percentage of Total Disk Reads
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Total Disk Reads:     369,567,665
-> Captured SQL account for   82.3% of Total

   Physical              Reads              Elapsed                             
      Reads  Executions per Exec   %Total   Time (s)   %CPU    %IO    SQL Id    
----------- ----------- ---------- ------ ---------- ------ ------ -------------
1.38570E+08          87 1.5928E+06   37.5   17,760.1   71.3   26.3 d95aab32us9ur -- вот этот
...
 65,092,102           1 6.5092E+07   17.6   12,031.6   55.6   35.2 apm9v1umcv9sa
...
 37,186,139           0        N/A   10.1    7,222.6   66.2   27.2 b6usrg82hwsa3
...

, заметность которого, похоже, обусловлена выбором и активным использованием, по-видимому, не самого оптимального плана 2944350538: (more…)

Реклама

06.04.2016

Изменение параметров SPMBaseline с помощью SQL Patch

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

Предположим запрос, периодически потребляющий TEMPORARY TABLESPACE в недопустимых объёмах:

SQL> @v$sqlstats fxgzfhx4fr9rv
 
INST EXECS SQL_ID              PLAN ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC UIO_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC DISK_READS_PER_EXEC WRITES_MB_PER_EXEC WRITES_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC PX_PER_EXEC
---- ----- ------------- ---------- ------------ ------------ ------------- ------------- ------------ ------------ ---------------- -------------- ------------------- ------------------ --------------- ---------------------- ------------- -----------
   2     5 fxgzfhx4fr9rv 1662599281      9728601      6012200        163289         43900       107579      3759166              228           5265                   0                  0               0                      0            13           0
   1     1 fxgzfhx4fr9rv 1494936070  27639543205   3917421000     769437420    2212170898   2758482394   3583471835             1029          17400                   0             125171         1068134               16019535          1888           0
 
SQL_EXEC_ID SQL_PLAN_HASH_VALUE ACTION             MAX_TEMP_GB MAX_PGA_GB MAX_PX_USED
----------- ------------------- ------------------ ----------- ---------- -----------
   16777225           792237607 Concurrent Request           0      0,029           0
   16777226          1494936070 Concurrent Request           0      0,225           0
   16777227          1494936070 Concurrent Request      72,007       0,19           0
   16777228          1494936070 Concurrent Request      39,393      0,879           0
   16777229          1494936070 Concurrent Request      14,435      0,704           0
   16777230          1494936070 Concurrent Request       2,726      0,878           0
   16777231          1494936070 Concurrent Request       1,894      0,879           0
   33554442          1662599281 Concurrent Request           0      0,054           0
   33554443          1662599281 Concurrent Request           0       0,05           0
   33554444          1662599281 Concurrent Request           0      0,042           0
   33554445          1662599281 Concurrent Request           0      0,039           0
   33554440          3996693969 Concurrent Request       3,127       1,13           0
   33554441          3996693969 Concurrent Request       3,142      1,145           0

— при этом повышенное потребление TEMP (и DIRECT_WRITES_PER_EXEC, соотв-но) было характерно для использования плана 1494936070, и не отмечается для плана 1662599281, т.е. зависело, в основном, от выбора плана выполнения и слабо зависело от прочих исходных данных и значений bind-ов — судя по BIND_SENSE(V$SQL.IS_BIND_SENSITIVE) и BIND_AWARE(V$SQL.IS_BIND_AWARE): (more…)

18.02.2016

12c: эффекты Automatic Dynamic Sampling

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

Невысокая скорость выполнения запроса 8tx5kfa369rdb:

SQL> @shared_cu12_noxml 8tx5kfa369rdb 0
 
EXECS USERS_OPENING LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC PARSES_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CONC_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL REASON#1 SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE
----- ------------- -------------------- ------------------- ------------ ------------ --------------- ------------- -------------- ------------ ------------- --------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- -------- ----------------- --------- ---------------- -----------
 2374             9 2016-02-08/12:47:01  12.02.2016 18:33:56   1474675990   1032495912               1        304100           8512     43756160     340539863      4044237314          10748     6 Y          N          Y          N                  N                       12        0                                                                                   

— ср.время / ELA_PER_EXEC ~ 1474 сек., много CPU и Concurrency, что визуально ассоциируется с 9 (в данном примере) одновременно открытыми курсорами / [V$SQL.]USERS_OPENING при полном отсутствии адаптивных эффектов (столбцы USE_FEEDBACK_STATS, REOPT, REOPT_HINTS, ADAPT, SPD_Used и DS_LEVEL) и нежданное событие ожидания в топе AWR:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait    
Event                                Waits Time (sec)    Avg(ms)   time Class   
------------------------------ ----------- ---------- ---------- ------ --------
DB CPU                                         181,6K              70.2         
latch: row cache objects        21,615,357      33,1K       1.53   12.8 Concurre -- тут
db file sequential read          4,369,250      23,5K       5.37    9.1 User I/O

судя по SQL Monitoring-у оказались связаны:) (more…)

17.12.2015

Фиксация плана с помощью SQL Profile и накладные расходы/overhead версии 12c

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

Процедура DBMS_SQLTUNE.CREATE_TUNING_TASK для конкретного SQL_ID игнорирует наличие SQL Profile для настраимоемого запроса:

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'fppuw3hpvww2d' ) FROM DUAL;

...
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- SQL Profile "TASK-3017" exists for this statement and was ignored
  during the tuning process.
...

, в то время как запрос с существующим SQL Patch настройке не подлежит:)

...
-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-16961: SQL statement with SQL patch is ignored

При этом список подсказок автоматически генерируемого SQL Profile, по крайней мере, с версии 12c, уже не ограничивается привычными корректирующими OPT_ESTIMATE: (more…)

26.07.2015

Одиночное выполнение SQL запроса с несколькими SQL_EXEC_ID и различными планами

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

В процессе совместного с Евгением Калининым тестирования с целью стандартизации/оптимизации времени выполнения запроса посчастливилось наблюдать диво дивное нечасто встречающееся событие: выполнение одного запроса очевидно затягивалось по причине периодической инвалидации используемого курсора, следом генеровался новый курсор (с новым планом) и начинал выполняться с начала, при этом со стороны запускающего приложения выполнение запроса не прерывалось, просто затянулось на 1,5 часа вместо обычных 20 минут:

12.1.0.2@ SQL> select nvl(qc_session_id, session_id) sid,
  2         nvl(qc_session_serial#, session_serial#) serial#,
  3         sql_exec_id,
  4         sql_child_number                    as CHILD,
  5         sql_plan_hash_value                 as PHV,
  6         sql_full_plan_hash_value            as FPHV,
  7         min(sample_time),
  8         max(sample_time),
  9         count(*)                            as ASH_ROWS,
 10         count(distinct session_id) - 1      as PX_COUNT,
 11         max(sample_time) - min(sample_time) as DURATION
 12    from v$active_session_history
 13   where module = 'sqlplus.exe'
 14     and sql_id = '5s0w4cubz5yyt'
 15     and sql_child_number >= 0
 16   group by nvl(qc_session_id, session_id),
 17            nvl(qc_session_serial#, session_serial#),
 18            sql_exec_id,
 19            sql_child_number,
 20            sql_plan_hash_value,
 21            sql_full_plan_hash_value
 22   order by max(sample_time)
 23  /
 
 SID SERIAL# SQL_EXEC_ID CHILD        PHV       FPHV MIN(SAMPLE_TIME)       MAX(SAMPLE_TIME)       ASH_ROWS PX DURATION
 --- ------- ----------- ----- ---------- ---------- ---------------------- ---------------------- -------- -- -------------------
  43    2250    16777216     0 1177121823 1348556722 21.07.15 14:56:58,291  21.07.15 15:15:56,736      3802  8 +000000000 00:18:58
  43    2250                 1  598640444 1944960939 21.07.15 15:15:57,736  21.07.15 15:15:58,736         2  1 +000000000 00:00:01
  43    2250                 1 2027645636 2114082327 21.07.15 15:15:59,736  21.07.15 15:16:13,736        15  0 +000000000 00:00:14
  43    2250                 1 2864496871 3345652639 21.07.15 15:16:14,736  21.07.15 15:16:27,736        14  0 +000000000 00:00:13
  43    2250                 0 1852732831 4122082463 21.07.15 15:16:28,736  21.07.15 15:16:28,736         1  0 +000000000 00:00:00
--43    2250                 1          0          0 21.07.15 15:54:16,636  21.07.15 15:54:30,646        15  0 +000000000 00:00:14
--43    2250                 3 3013780789 4105931067 21.07.15 15:55:48,694  21.07.15 15:56:00,694        13  0 +000000000 00:00:12
--43    2250                 2  667538297 1913179438 21.07.15 15:56:28,694  21.07.15 15:58:23,740         4  2 +000000000 00:01:55
--43    2250                 2 3013780789 4105931067 21.07.15 15:55:18,674  21.07.15 15:59:47,777       112  5 +000000000 00:04:29
--43    2250                 1  667538297 1913179438 21.07.15 15:27:52,024  21.07.15 16:00:50,807        16  5 +000000000 00:32:58
--43    2250                 1 3013780789 4105931067 21.07.15 15:27:53,024  21.07.15 16:01:17,807       332  6 +000000000 00:33:24
--43    2250                 0          0 4122082463 21.07.15 16:11:06,080  21.07.15 16:11:06,080         4  3 +000000000 00:00:00
  43    2250    16777217     0 1852732831 4122082463 21.07.15 15:16:29,736  21.07.15 16:11:06,080      7198  8 +000000000 00:54:36
  43    2250    16777218     0 2628977128  173302026 21.07.15 16:11:07,080  21.07.15 16:16:07,196       630  8 +000000000 00:05:00

(more…)

13.04.2015

PL/SQL, RESULT_CACHE и sysdate в запросах

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

Жил-был запрос, потреблявший бОльшую часть DB Time:

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:    307361 02-Apr-15 18:30:14       371       2.0
  End Snap:    307362 02-Apr-15 19:00:16       343       2.1
   Elapsed:               30.04 (mins)
   DB Time:            1,395.37 (mins)

...

SQL ordered by Elapsed Time

        Elapsed                  Elapsed Time                                   
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id    
---------------- -------------- ------------- ------ ------ ------ -------------
        50,849.2        122,466          0.42   60.7   56.1     .5 d3gt02wdxt8k3 -- с большим опережением
SELECT /*+ RESULT_CACHE*/ NVL(BUDGET, 0) FROM U0.V_CLIENT_DISCOUNTS D RIGHT JOIN
 DUAL ON CLIENT_ID = :B2 AND DT = TRUNC(:B1 )

         7,787.2          4,241          1.84    9.3   28.4     .0 14a9q0pdpf0jv -- от ближайшего конкурента

, указанный в тексте /*+ RESULT_CACHE*/ не работал, поскольку в глубинах вложенных обзоров запроса содержалось упоминание sysdate в виде TRUNC(SYSDATE), что не способствует использованию SQL Result Cache по определению, но и логика в этом есть не всегда. В частности, при разного рода округлениях sysdate, да и без округлений — например, для запроов с достаточно высоким RPS, как в этом случае возможность использования Result Cache может быть отнюдь не лишней

План запроса адекватно отражает источник высокой стоимости и невысокой скорости выполнения (~ 400 мс на выполнение): (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…)

22.02.2015

Особенности SQL Patch

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

Лишь частично документированный, но достаточно удобный для простого манипулирования планами выполнения SQL Patch, как и всякий инструмент имеет свои ограничения/особенности

Так, например, длина добавляемой к запросу подсказки имеет точное ограничение:
(more…)

14.12.2014

SPM подходы к изменению плана запроса с hardcoded подсказками

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

Вопрос: как максимально корректно изменить план выполнения уже хинтованного запроса? Корректно, в смысле не меняя текста запроса (само собой) и, желательно, не меняя параметров сессии, т.е. используя, например, безконтактный SQL Plan Management (SPM)?

Простейшая тестовая схема:

11.2.0.3@SCOTT SQL> create table emp123
  2  as
  3  select * from emp
  4  /

Table created.

SQL> exec dbms_stats.gather_table_stats('','EMP123')

PL/SQL procedure successfully completed.

SQL> create index emp123_sal_idx on emp123(sal)
  2  /

Index created.

Простой запрос с использованием FTS: (more…)

12.12.2014

Гарантированный DOP и параметр PARALLEL_MIN_PERCENT

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

Встречаются запросы с завидным потреблением временного табличного пр-ва для HASH JOIN-ов разного рода и других сортировочно-группировочных операций, при недостатке показывающие:

ORA-01652: невозможно увеличить временный сегмент до 64 в разделе TEMP

Одним из методов сокращения требуемых объёмов temp является использование параллельного выполнения благодаря которому, попутно с улучшением общей скорости выполнения запроса, можно съэкономить временное пр-во, используя бОльший объём PGA большИм кол-вом px-процессов

В этом случае бывает необходимо обеспечить определённый Degree Of Parallelism (DOP) для выполнения конкретного запроса, т.е. ограничить DOP «снизу», для чего теоретически идеально подходит механизм parallel statement queuing, однако установка параметра _PARALLEL_STATEMENT_QUEUING = TRUE не всегда срабатывает, например, при выполнении DBMS_SCHEDULER-ного задания (job) запросы без ожидания resmgr:pq queued, начинают выполняться непараллельно, невзирая на отсутствие свободных PX

Как оказалось, в заданиях DBMS_SCHEDULER отлично срабатывает другой параметр PARALLEL_MIN_PERCENT, со своими особенностями, но срабатывает и в PL/SQL блоке DBMS_SCHEDULER, и на уровне обычной пользовательской сессии

Итак, на сервере со следующими ресурсами: (more…)

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

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