Oracle mechanics

17.08.2018

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

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

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

Блог на WordPress.com.