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

Реклама

22.02.2017

Hintset Object

Filed under: Oracle,Oracle 12c,parameters,shared pool — Игорь Усольцев @ 18:52
Tags: ,

The plan_baseline hintset is just the list of hints SPM tried to use to reproduce the plan

В ТОПе Library Cache периодически появлялся непривычный объект:

SQL> select *
  2    from (select hash_value,
  3                 substr(name, 1, 100) as object_name,
  4                 namespace,
  5                 type,
  6                 kept,
  7                 count(*),
  8                 min(timestamp),
  9                 sum(locked_total),
 10                 sum(pinned_total),
 11                 sum(loads),
 12                 sum(executions),
 13                 sum(sharable_mem)
 14            from v$db_object_cache
 15           group by hash_value,
 16                    substr(name, 1, 100),
 17                    namespace,
 18                    type,
 19                    kept
 20           order by sum(sharable_mem) desc)
 21   where rownum <= 3
 22  /
 
HASH_VALUE OBJECT_NAME          NAMESPACE      TYPE           KEPT COUNT(*) MIN(TIMESTAMP)      SUM(LOCKED_TOTAL) SUM(PINNED_TOTAL) SUM(LOADS) SUM(EXECUTIONS) SUM(SHARABLE_MEM)
---------- -------------------- -------------- -------------- ---- -------- ------------------- ----------------- ----------------- ---------- --------------- -----------------
2209848120 6004199150836888961  HINTSET OBJECT HINTSET OBJECT NO          1                                 14872             14872          4               0         625277440
 838046317 select               SQL AREA       CURSOR         NO        459 2016-11-02/10:54:33            442985            288445      79518          180659         155325176
 269081421 select               SQL AREA       CURSOR         NO        627 2016-10-31/00:07:03            273128            218432     143717           91650         154551720
...

необычайно крупного размера SHARABLE_MEM > 600MB с цифровым наименованием, под коим числилось 2 объекта: (more…)

30.01.2017

12c Adaptive Bitmap Pruning — особый случай адаптивного выполнения: PHV, PHV2, FPHV & Outline Hints

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

В процессе подготовки семинара, столкнулся с любопытным случаем, для адаптивного плана выполнения:

12.1.0.2.SCOTT@/ORCL1201 SQL> exec dbms_stats.gather_schema_stats('SH')

PL/SQL procedure successfully completed.

SQL> SELECT /*+ star_transformation(s)                                   -- star_transformation_enabled = TRUE на уровне запроса
  2             gather_plan_statistics
  3             OPT_PARAM('_optimizer_dsdir_usage_control' 0)            -- для исключения эффектов
  4  --           OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
  5             OPT_PARAM('_optimizer_gather_feedback' 'false') */       -- --//--
  6   c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
  7    FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  8   WHERE s.time_id = t.time_id
  9     AND s.cust_id = c.cust_id
 10     AND s.channel_id = ch.channel_id
 11     AND c.cust_state_province = 'CA'
 12     AND ch.channel_desc = 'Internet'
 13     AND t.calendar_quarter_desc IN ('1999-01', '1999-02')
 14   GROUP BY c.cust_city, t.calendar_quarter_desc
 15  /

22 rows selected.

(more…)

29.01.2017

Проблема ограничения длины хинта SQL_PROFILE_FROM_SQL.SQL

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

Коллега Леонид Борчук в процессе практического применения скрипта sql_profile_from_sql.sql обнаружил ошибку:

 ORA-06502: PL/SQL: numeric or value error: character string buffer too small

и указал на ограничение:

TYPE sqlprof_attr AS VARRAY(2000) of VARCHAR2(500)

— т.е. нашёл-таки запрос с хинтом длиною более 500 символов! — для запросов OEBS нет пределов:)

А, поскольку, в мировом масштабе, эта проблема уже была разрешена Carlos SierraRunning «coe_xfr_sql_profile.sql» Script (Shipped with SQLT) Raises «ORA-06502: PL/SQL: numeric or value error» (Doc ID 2043600.1) в новых версиях SQLT, оставалось лишь посмотреть как это сделано:

begin
  LOOP
    l_hint := i.hint;
    WHILE NVL(LENGTH(l_hint), 0) > 0 LOOP
      IF LENGTH(l_hint) <= 500 THEN
        DBMS_OUTPUT.PUT_LINE('q''[' || l_hint || ']'',');
        l_hint := NULL;
      ELSE
        l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
        DBMS_OUTPUT.PUT_LINE('q''[' || SUBSTR(l_hint, 1, l_pos) || ']'',');
        l_hint := ' ' || SUBSTR(l_hint, l_pos);
      END IF;
    END LOOP;
  END LOOP;
end;

— через разделение отдельного хинта по пробелу (оказывается, так можно!) и применить в sql_profile_from_sql.sql — что и было сделано

13.11.2016

Oracle 12.1: Library Cache Lock на объекте типа $BUILD$, Adaptive Plan и параллельное выполнение

Filed under: Oracle,Scripts,shared pool,wait events — Игорь Усольцев @ 23:37
Tags: ,

Активизировавшаяся конкуренция за объекты Library Cache в форме ожиданий library cache lock, и в меньшей степени kksfbc child completion, library cache: mutex X:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait    
Event                                Waits Time (sec)    Avg(ms)   time Class   
------------------------------ ----------- ---------- ---------- ------ -----------
DB CPU                                          10.3K              77.6         
library cache lock                 159,109     1554.8       9.77   11.7 Concurrency -- здесь
db file sequential read            297,807      436.2       1.46    3.3 User I/O
cursor: pin S wait on X             26,087      322.5      12.36    2.4 Concurrency
log file sync                      116,938      267.6       2.29    2.0 Commit  
SQL*Net more data from client    1,809,223      131.1       0.07    1.0 Network 
kksfbc child completion              2,213      108.3      48.94     .8 Other       -- , здесь
control file sequential read       290,199       72.9       0.25     .5 System I
direct path write                    6,026       46.3       7.69     .3 User I/O
library cache: mutex X              83,522       45.7       0.55     .3 Concurrency -- и здесь

оказалось вызвана PX-slave процессами (P…) одного параллельно выполнявшимся запроса dx0fckp3gckku в стадии подготовки (IN_PARSE) адаптивного плана (SQL_ADAPTIVE_PLAN_RESOLVED = 1): (more…)

27.07.2016

Презентация Oracle 12c ADS

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

, представленная на Oracle Database Community Day 2016 (добавил комментарии на сером фоне):

Небольшое дополнение и иллюстрация: (more…)

05.06.2016

Посказка BITMAP_TREE

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

Определение, приведённое ранее:

Usage: BITMAP_TREE([<@Block>] <Table> AND(<Index1>[ <Index2> ...]))
    or BITMAP_TREE([<@Block>] <Table> AND((<Index1 columns>)[ (<Index2 columns>) ...]))
Description: Instructs the optimizer to convert ROWIDs to bitmap, then performance bitmap operations

оказалось неполным, и в этом, как обычно, помогла проблема с планом критичного запроса типа:

SELECT *
  FROM dual
 WHERE EXISTS (SELECT *
          FROM (SELECT DISTINCT t_lines_1.*
                  FROM t_lines t_lines_1
                  JOIN t_lines
                    ON t_lines.group_lines_id = t_lines_1.id
                 WHERE t_lines.group_lines_id IS NOT NULL
                   AND t_lines_1.group_lines_id IS NULL
                   AND t_lines.service_id IN (:service_id_1, :service_id_2, :service_id_3, :service_id_4)                     -- [1]
                   AND (t_lines.person_id = :person_id_1 OR t_lines.client_id = :client_id_1 AND t_lines.person_id IS NULL))) -- [2]

, который, как обычно неожиданно, из 2-х планов выбрал худший (PHV 2429571734 — второй по счёту в нижеприведённом сравнении) — с использование комбинации двух BITMAP ROWIDS индексных пребразований, соответствующих 2-м вхождениям/использованиям оператора OR в запросе — в строках [1] и [2] (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…)

04.04.2016

12c: 2 x FULL_PLAN_HASH_VALUE для одного плана выполнения / PLAN_HASH_VALUE и скрипт ASH_SQLMON12.SQL

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

При интенсивном выполнении (конец месяца, подсчёт итогов,..) запроса типа SELECT наблюдал картину:

SQL> @shared_cu12_noxml 35hnzb2q6xrkc
 
  EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CLU_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL BIND_EQ_FAILURE REASON#1          SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE IS_OBSOLETE
------- -------------------- ------------------- ------------ ------------ ------------- -------------- ------------ ------------ ------------- --------------- -------------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- --------------- ----------------- ----------------- --------- ---------------- ----------- -----------
   1015 2016-04-01/10:10:12  01.04.2016 10:16:44       470644        74060         24316            462       303651       107095 VALID               434062702           3150513221         194928     3 Y          Y          N          N                  N                 Y     5         2         2        Y               Bind mismatch(33)                                                          N
    384 2016-04-01/10:16:47  01.04.2016 10:19:05       482506        66008         20388            401       309564       118371 VALID               434062702           3150513221          11350     5 Y          N          N          N                  N                 Y     5         2         2        N               Bind mismatch(25)                                                          N
    481 2016-04-01/10:19:05  01.04.2016 10:23:02       440066        44582         13717            295       214687       188648 VALID               434062702           3150513221             72     6 Y          Y          N          N                  N                 Y     5         2         2        N               Bind mismatch(33)                                                          N
    538 2016-04-01/10:22:06  01.04.2016 10:27:07       622140        69344         24159            422       305390       258859 VALID               434062702           3150513221         315650     7 Y          Y          N          N                  N                 Y     6         1         2        Y               Bind mismatch(33)                                                          N
   2426 2016-04-01/10:27:04  01.04.2016 10:35:02       324502        49090         15276            266       209320        73840 VALID               434062702           3150513221            345     8 Y          Y          N          N                  N                 Y     5         2         2        Y               Bind mismatch(33)                                                          N
  18369 2016-04-01/10:34:44  01.04.2016 10:54:36       212043        24282          6648            142       121101        69878 VALID               434062702           3150513221          11390     2 Y          Y          N          N                  N                 Y     5         2         2        Y               Bind mismatch(33)                                                          N
  11009 2016-04-01/10:54:26  01.04.2016 11:00:00       103472        16452          4501             87        70872        18074 VALID               434062702           3150513221          42424     9 Y          Y          N          N                  N                 Y     5         2         2        Y               Bind mismatch(33)                                                          N
  15439 2016-04-01/10:59:58  01.04.2016 11:07:58       157499        16593          4644             78       127386        14786 VALID               434062702           1196564125         385981    10 Y          Y          N          N                  N                       5         2         2        Y               Bind mismatch(33)                                                          N
 148072 2016-04-01/11:07:57  01.04.2016 12:01:52        53123         9772          2884             50        41752         2766 VALID               434062702           3150513221           8859    11 Y          Y          Y          N                  N                 Y     5         2         2        Y                                                                                          N
 
10 rows selected
 
--------------------------------------------------------------
SQL_ID=35hnzb2q6xrkc Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
  EXECS ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC READS_PER_EXEC UIO_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC FETCH_PER_EXEC ROWS_PER_EXEC PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE AVG_CBO_COST     CHILDS BIND_SENSE BIND_AWARE REOPT ADAPT SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE
------- ------------ ------------ ------------- -------------- ------------ ------------- ------------ -------------- ------------- --------------- -------------------- ------------ ---------- ---------- ---------- ----- ----- ----------------- --------- ---------------- -----------
  15439       157499        16593          4644             78       127386            16        14786              1             1       434062702           1196564125       385981          1 Y          Y          N     N                                                  
 182294        81718        12905          3774             69        56993            32        13470              1             1       434062702           3150513221        73127          8 Y          Y          N     Y

— любопытную прежде всего тем, что по причине BIND_AWARE / Bind mismatch(33) Oracle периодически перегенерировал один и тот же курсор с неизменным рез-татом в виде плана PLAN_HASH_VALUE = 434062702, но с использованием разных FULL_PLAN_HASH_VALUE — см. CHILD 9-11, например (more…)

12c: данные SPM Baseline в SYS.SQLOBJ$PLAN, необходимое уточнение

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

В дополнение к одной из предыдущих заметок оказалось, что в SYS.SQLOBJ$PLAN хранятся данные только «новых» SPM Plan Baseline-ов, созданных в версии 12c

Данные «унаследованных» Baseline-ов версии 11g после обновления так и остаются в табличке SYS.SQLOBJ$DATA

Т.е. если используются Baseline-ы разных версий:

12.1.0.2@ SQL> select plan_name, version from dba_sql_plan_baselines order by 2;
 
PLAN_NAME                       VERSION
------------------------------- ----------
SQL_PLAN_8yh6xn9ncndbf8f932b0a  11.2.0.3.0 -- *
...
SQL_PLAN_3yyva1p88muu4109f0d5f  11.2.0.3.0
SQL_PLAN_593vgbak4tb3409c26202  12.1.0.2.0
...

— то запрос к SYS.SQLOBJ$PLAN для Baseline-а версии 11.2 (*) ничего не даст:

SQL> @spb12 SQL_PLAN_8yh6xn9ncndbf8f932b0a

Дополненный соответствующим UNION ALL SYS.SQLOBJ$DATA скрипт SPB12.SQL правильно отображает содержимое Baseline-ов, созданных в обеих версиях:

SQL> @spb12 SQL_PLAN_8yh6xn9ncndbf8f932b0a
 
OUTLINE_HINTS
-------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
...
Следующая страница →

Блог на WordPress.com.