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…)

Реклама

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…)

18.04.2016

ASH_SQLMON12S.SQL: блочный мониторинг длинных планов выполнения

Filed under: Active Session History,Oracle,Scripts — Игорь Усольцев @ 23:48
Tags: ,

Причины длительного выполнения запросов с обширным планом бывает непросто увидеть/определить как в связи объёмом, так и в связи с ненадёжностью / периодическим прерыванием функционирования DBMS_SQLTUNE.REPORT_SQL_MONITOR сообщениями типа:

Global Information
------------------------------
 Status                                 :  DONE (ERROR)

— для всё ещё продолжающих выполняться запросов

Идея скрипта проста — сгруппировать ASH строки по имени Query Block определённого плана выполнения, например так: (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…)

30.01.2016

Подсказка INDEX и bitmap-операции с B-tree индексами

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

in English

Причиной увеличения DB Time/Elapsed:

WORKLOAD REPOSITORY report for
Elapsed:               30.08 (mins)
DB Time:            1,035.38 (mins)
...

Snap Time           Load    %busy    %user     %sys    %idle  %iowait
--------------- -------- -------- -------- -------- -------- --------
15-Jan 08:00:06     26.7      N/A      N/A      N/A      N/A      N/A
15-Jan 08:30:10     48.4     69.7     65.4      3.2     30.3      6.5 -- *
15-Jan 09:00:13      6.2     33.0     30.2      2.3     67.0      3.5

— с соопутствующим скачком Load Average (*) был хорошо заметный в том же AWR-отчёте запрос:

SQL ordered by Elapsed Time    DB/Inst: ORCL/orcl1  Snaps: 321153-321154
        Elapsed                  Elapsed Time                                   
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id    
---------------- -------------- ------------- ------ ------ ------ -------------
        32,145.0         33,505          0.96   51.7   72.6     .0 c9cxc4hy2tbsa -- определённо этот
...
         9,798.9              0           N/A   15.8   45.7   45.9 1mbx3m11g17vn
...
         2,533.0          4,113          0.62    4.1   22.7   22.1 c8cx3cxf286d7

, выполнявшийся в проблемый период попеременно по 2-м разным планам согласно статистике V$SQL/V$SQL_SHARED_CURSOR: (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…)

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…)

12.10.2014

История одного запроса: SPM, трансформации и подсказки

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

Типичный случай — план запроса «неожиданно» измененился, и не в лучшую сторону:

11.2.0.4.@ SQL> @shared_cu 8dvbszd8kj04m

INST EXECS LAST_LOAD_TIME LAST_ACTIVE_TIME ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON1
---- ----- -------------- ---------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ------------------ ---------------- ---------------- ------------------
   2     9 07.10 14:50    07.10 17:57        1076192531       465674203            132     1 Y          N          Y          N                  Y                N                NLS Settings(0)  |
   2     5 07.10 16:04    07.10 17:04            263416      4268563287            246     3 Y          N          Y          N                  N                N                NLS Settings(0)  |

, при этом V$SQL_SHARED_CURSOR.LOAD_OPTIMIZER_STATS=Y (обозначенное как OPTIMIZER_STATS) означает, что неудачный выбор формально более выгодного плана был динамически предопределён технологией cardinality feedback (CF):

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('8dvbszd8kj04m', 1,format => '+note'));
...
Note
-----
   - cardinality feedback used for this statement

Часть 1. SQL Plan Management (SPM)

(more…)

25.06.2014

Особенности преобразования Table Expansion

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

Полезное стоимостное (CBQT) преобразование Table Expansion (TE), доступное с версии 11.2, кроме несомненных достоинств имеет ряд недоработок-багов особенностей реализации и применения, периодически омрачающих радость столкновений с лучшим из стоимостных оптимизаторов (CBO)

Далее — краткий обзор случаев неоднозначного поведения TE, доступных в версиях 11.2 — 12.1.0.1

И вначале — о хорошем: для случая простого Range Partitioning технология успешно отрабатывает независимо от количества и порядка расположения Unusable Index Partitions: (more…)

20.06.2014

И ещё про Filter Push-Down

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

При попытке отключения всех типов трансформаций запроса (на примере из предыдущей записи): (more…)

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

Блог на WordPress.com.