Oracle mechanics

11.05.2020

Подсказки TABLE_STATS/INDEX_STATS/COLUMN_STATS в стандартных SQL Profile

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

Обратил внимание, что в стандартном автоматически созданном (Sql Tuning Advisor) SQL Profile на фоне многочисленных корректирующих OPT_ESTIMATE лишь иногда (нечасто) проскакивают хинты типа TABLE_STATS / INDEX_STATS / COLUMN_STATS:

12.1.0.2. SQL> @sql_profile_hints SYS_SQLPROF_0271cb2a05080003

HINT
---------------------------------------------------------------------------------------------------------
OPT_ESTIMATE(...
...
OPT_ESTIMATE(...
COLUMN_STATS("YO"."MANAGER", "NAME", scale, length=70 distinct=13031)
COLUMN_STATS("YO"."MANAGER", "MANAGER_CODE", scale, length=4 distinct=13272 nulls=0 min=1 max=44787)
TABLE_STATS("YO"."MANAGER", scale, blocks=964 rows=21966)

21 rows selected

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

21.04.2020

Quiz: изменение механизма JPPD при обновлении 12.1.0.2->19c

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

in English

Столкнулись с проблемой, подготовил простой тесткейс на данных схемы HR
В SR отправил, но, возможно, я упустил что-то очевидное или кто-то уже сталкивался, или есть идеи и/или время/желание поэкспериментировать
Запрос:

select /***/ * from hr.employees e1
left join
(select location_id, department_name, max(manager_id) max_manager_id
 from hr.departments where department_name = 'IT' group by location_id, department_name)
    e2 on e2.location_id = e1.department_id
AND    (NOT EXISTS      (SELECT 1
                         FROM   hr.employees e3
                         WHERE e3.department_id = e2.max_manager_id
                           AND e3.job_id = e1.job_id))
where e1.job_id = 'FI_MGR';

в версии 12.1.0.2 выдаёт простой и быстрый план: (more…)

14.04.2020

Fake Table Expansion на примере In Database Row Archiving

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

С версии 12.1 несколько раз наблюдал фэйковые Table Expansion Transformation (TE) — случаи, когда в присутствии на таблице частично Usable/Unusable Partitioned Index, оптимизатор задействует TE для операций, никак не использующих/не касающихся этого партицированного индекса

Описываемый пример (12.1.0.2) для таблицы с включеным In Database Row Archiving:

SQL> select hidden_column from dba_tab_cols where table_name = 'SLA_DISTRIBUTION_LINKS' and column_name = 'ORA_ARCHIVE_STATE';

HIDDEN_COLUMN
-------------
YES

SQL> select status, count(*)
  2    from dba_ind_partitions
  3   where index_name = 'SLA_DISTRIBUTION_LINKS_XX1'
  4     and index_name in
  5         (select index_name from dba_indexes where table_name = 'SLA_DISTRIBUTION_LINKS')
  6   group by status;

STATUS     COUNT(*)
-------- ----------
USABLE            1
UNUSABLE         18

и частично USABLE индексом (экономили место!) отчётливо проявляет проблему

Простой запрос одной/первой строки затягивается на время = Full Scan всех партиций немаленькой таблицы: (more…)

20.03.2020

Oracle 19c: ORA-07445: [kkopmDumpPlanInfo()+3242] при попытке dbms_sqldiag.dump_trace в присутствии Baseline

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

В процессе тестирования Oracle 19c попытка сгенерировать CBO trace неожиданно неудачна:

19.5.0.0.0@ SQL> begin
  2  dbms_sqldiag.dump_trace(
  3     p_sql_id=>'fppuw3hpvww2d',
  4     p_child_number => 0,
  5     p_component=>'Optimizer',
  6     p_file_id=>'My_CBO_Trace_fppuw3hpvww2d');
  7  end;
  8  /

ORA-03113: end-of-file on communication channel
Process ID: 207304
Session ID: 4046 Serial number: 47139

, файл трейса обрывается на этапе SPM plan reproduce:

SPM: planId in plan baseline = 825902678, planId of reproduced plan = 3531133102
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : APPS
  plan_baseline signature  : 10360884871991321708
  plan_baseline plan_id    : 825902678
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
    hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.4') -- SQL Patch ADM-3026 -- (*)
    hint num  3 len 22 text: DB_VERSION('12.1.0.2')
...
    hint num 1213 len 57 text: USE_NL(@"SEL$E330BDE4" "AR_RECEIVABLES_TRX_ALL"@"SEL$10")
    hint num 1214 len 63 text: NLJ_BATCHING(@"SEL$E330BDE4" "AR_RECEIVABLES_TRX_ALL"@"SEL$10")

*** 2020-03-20T18:20:26.903037+03:00 (OEBS(3))
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x10] [PC:0x9507FDA, kkopmDumpPlanInfo()+3242] [flags: 0x0, count: 1]
2020-03-20T18:20:26.989775+03:00
Incident 915457 created, dump file: /opt/oracle/base/diag/rdbms/...
ORA-07445: exception encountered: core dump [kkopmDumpPlanInfo()+3242] [SIGSEGV] [ADDR:0x10] [PC:0x9507FDA] [Address not mapped to object] []

— относительно свежей (неопределяемой на сайте поддержки, по крайней мере, для моего аккаунта) ошибкой ORA-07445: exception encountered: core dump [kkopmDumpPlanInfo()+(more…)

01.03.2020

Проблемы online index (re)build и избыточное преобразование Table Expansion

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

После формально успешного создания нестандартного индекса по 5 полям — многовато, но так советовал SQL Tuning Advisor!) — для стандартной таблицы EBS:

12.1.0.2@ SQL> create index XLA.IDX$$_A39FF0001 on XLA.XLA_DISTRIBUTION_LINKS("EVENT_ID","SOURCE_DISTRIBUTION_ID_NUM_1",...) online
  2  /

Index created.

с коллегой Максимом Филатовым наблюдали необычно абсолютное игнорирование этого индекса в трейсе CBO:

******************************************
----- Current SQL Statement for this session (sql_id=5a8dk72hdtuam) -----
explain plan for
SELECT --+ index(@"SEL$1" "XDL"@"SEL$1" "IDX$$_A39FF0001")
 XDL.*
  FROM apps.XLA_DISTRIBUTION_LINKS XDL
 WHERE (XDL.SOURCE_DISTRIBUTION_ID_NUM_1, XDL.EVENT_ID) in ((1, 1))
*******************************************
...
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: XLA_DISTRIBUTION_LINKS  Alias: XDL  (Using composite stats)
  #Rows: 5426304124  SSZ: 0  LGR: 0  #Blks:  207306547  AvgRowLen:  276.00  NEB: 0  ChainCnt:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0  CHR: 0  KQDFLG: 1
  #IMCUs: 0  IMCRowCnt: 0  IMCJournalRowCnt: 0  #IMCBlocks: 0  IMCQuotient: 0.000000
Index Stats::
  Index: XLA_DISTRIBUTION_LINKS_N1  Col#: 5 11 86
    USING COMPOSITE STATS
  LVLS: 4  #LB: 45196718  #DK: 1627394718  LB/K: 1.00  DB/K: 2.00  CLUF: 3255994960.00  NRW: 5503051110.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
  KKEISFLG: 1
    ALL PARTITIONS USABLE
  Index: XLA_DISTRIBUTION_LINKS_N3  Col#: 3 4
    USING COMPOSITE STATS
  LVLS: 3  #LB: 26862145  #DK: 2359951360  LB/K: 1.00  DB/K: 1.00  CLUF: 2114999539.00  NRW: 5502258838.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
  KKEISFLG: 1
    ALL PARTITIONS USABLE
  Index: XLA_DISTRIBUTION_LINKS_U1  Col#: 1 20 25 3
    USING COMPOSITE STATS
  LVLS: 3  #LB: 37410241  #DK: 5613018740  LB/K: 1.00  DB/K: 1.00  CLUF: 1359701404.00  NRW: 5613018740.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
  KKEISFLG: 1
    ALL PARTITIONS USABLE
  Index: XLA_DISTRIBUTION_LINKS_XX1  Col#: 45 3 4
    USING COMPOSITE STATS
  LVLS: 3  #LB: 6191214  #DK: 659324928  LB/K: 1.00  DB/K: 1.00  CLUF: 358318531.00  NRW: 973461787.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
  KKEISFLG: 1
    UNUSABLE -- означает, что индекс имеет наряду с USABLE и UNUSABLE partitions, т.е. ALL PARTITIONS USABLE != true -- *
=======================================

т.е. по таблице XLA_DISTRIBUTION_LINKS и всем её индексам статистика приводится/рассматривается, кроме нового, с виду вполне VALID-ного: (more…)

16.12.2019

Цепочка ожиданий enq: TX — allocate ITL entry -> row cache lock -> KGL Lock timeout

Filed under: Active Session History,Блокировки,Oracle — Игорь Усольцев @ 09:50
Tags: , ,

Проблемы, выраженные в повышенном соотношении DB Time/Elapsed (*) с последующим относительным всплеском Redo size Per Second (**):

              Snap Id      Snap Time      Sessions Curs/Sess Instances
            --------- ------------------- -------- --------- ---------
Begin Snap:     51074 05-Dec-19 02:50:07     4,705      12.2         2
  End Snap:     51075 05-Dec-19 03:00:13     4,156      12.4         2
   Elapsed:               10.10 (mins)
   DB Time:            4,607.72 (mins)                                --*

Load Profile                    Per Second   Per Transaction  Per Exec  Per Call
~~~~~~~~~~~~~~~            ---------------   --------------- --------- ---------
             DB Time(s):             456.4               0.1      0.02      0.01
              DB CPU(s):              25.6               0.0      0.00      0.00
      Background CPU(s):              -0.0               0.0      0.00      0.00
      Redo size (bytes):      77,493,207.4          13,308.7                    --**

характеризовались нетипичным для стабильной OLTP-системы набором ожиданий (***):

--inst#1
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Avg   % DB Wait    
Event                                Waits Time (sec)      Wait   time Class   
------------------------------ ----------- ---------- --------- ------ --------
enq: TX - allocate ITL entry       111,030     148,2K 1334.56ms   53.6 Configur -- ***
row cache lock                      10,668      66,2K 6203.93ms   23.9 Concurre -- ***
DB CPU                                          15,5K              5.6         
enq: TX - row lock contention        1,437      12,5K 8729.30ms    4.5 Applicat
library cache lock                  13,472     9555,7  709.30ms    3.5 Concurre -- ***
...
--inst#2
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Avg   % DB Wait    
Event                                Waits Time (sec)      Wait   time Class   
------------------------------ ----------- ---------- --------- ------ --------
enq: TX - allocate ITL entry        22,679      59,2K 2608.77ms   38.3 Configur -- ***
cursor: pin S wait on X                921      35,5K   38.53 s   23.0 Concurre -- ***
row cache lock                       2,984      30,8K   10.33 s   19.9 Concurre -- ***
library cache lock                   6,707      11,6K 1735.48ms    7.5 Concurre -- ***
enq: TX - row lock contention          493      11,6K   23.60 s    7.5 Applicat
...

, кроме того, проблема была чётко ограничена по времени: (more…)

Выступление Тимура Ахмадеева, RuOUG, 22 ноября 2019

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

SQL Optimization, на примерах из Oracle E-Business Suite

31.10.2019

12.2 ORA-1652 in RAC Standby DB

Filed under: Active Data Guard,Oracle — Игорь Усольцев @ 09:48
Tags: ,

После возникновения/обсуждения в группе @RuOUG проблемы с перераспределением временных сегментов в между read-only ADG RAC инстансами пришлось завести SR, в том числе по рез-там которого, надеюсь, в сентябре появился док-нт Temporary Tablespace Allocation Fails with ORA-1652 in RAC Standby DB Even When There is Still Free Temp Space (Doc ID 2586477.1) с формальным описанием и решением проблемы:
- The rdbms version is equal to or greater than 12.2.
- This is a RAC database.
- The RAC instances are open in read-only mode; example: standby active data guard database (ADG).
- There are more than one RAC instances open in read-only mode in this database.
- ORA-1652 does not happen if the rest of the instances are closed (or mounted).
- If parameter _trace_temp is set to TRUE, the trace file **does not have** this retry message:
..
[ktstgrowseg] retry_1652:1 rss_exts:100
..
- ORA-1652 does not happen in a database open in Read-Write mode; example: in a standby database configuration, the same query runs with no errors on the primary database
...
Please apply Patch 30193736. This bug fix is included in 20.1 onward.

И, если до установки патча при неравномерном начальном распределении TEMP сегментов: (more…)

13.09.2019

Oracle 12.2 ADG: конкуренция parse — LGWR на ожидании library cache lock

Filed under: Active Data Guard,Oracle,SQL Tuning — Игорь Усольцев @ 10:13
Tags: ,

Первоначально проблема представлялась просто как запрос с нестабильным временем выполнения на Read-Only Standby , при более внимательном рассмотрении выяснилось, что запрос не просто небыстро выполняется, а периодически-трагически гибнет:

select ...
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7130
Session ID: 3513 Serial number: 31958


Elapsed: 00:08:43.45 -- затратив почти 9 минут!

с сообщениями в alert.log:

2019-09-02T10:40:26.200143+03:00
Errors in file /opt/oracle/base/diag/rdbms/.../trace/orclcdb1_lmhb_11715.trc  (incident=49228) (PDBNAME=CDB$ROOT):
ORA-29771: process USER (OSID 15557) blocks LGWR (OSID 11731) for more than 70 seconds
Incident details in: /opt/oracle/base/diag/rdbms/.../incident/incdir_49228/orclcdb1_lmhb_11715_i49228.trc
2019-09-02T10:40:30.858330+03:00
USER (ospid: 15557) is blocking LGWR (ospid: 11731) in a wait
LMHB (ospid: 11715) kills USER (ospid: 15557).
Please check LMHB trace file for more detail.

LMHB trace file: (more…)

04.09.2019

12.2: JPPD в присутствии удалённой таблицы/remote table

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

Аналогично описанному для версий 11-12.1 — JPPD в присутствии удалённой таблицы и View Merging — и справедливому до 19.3 включительно (тесткейс там есть — можно проверить) наблюдению, CBO версии 12.2 также может полностью блокировать применение Join Predicate Push Down с резолюцией JPPD bypassed: Remote table referenced — в этот раз на примере локального Non-Mergeable Outer Joined View и новым workaround-ом

Из 10053 трейса для запроса к локальной и удалённой таблицам + left-outer joined MuLTiple_TABle_view:

----- Current SQL Statement for this session (sql_id=019vqbbfv70gv) -----
explain plan for
select--+ NO_ADAPTIVE_PLAN -- во избежание...
 *
  from invoice                                            LOCAL_TABLE,
       (SELECT ir.invoice_id,
               ir.repay_invoice_id,
               rii.external_id         repay_invoice_eid,
               rii.status_id           repay_status_id
          FROM invoice_repay ir, invoice rii
         WHERE ir.repay_invoice_id = rii.ID
           AND rii.hidden IN (0, 1))                      MuLTiple_TABle_view,
       product_unit@dblink                                REMOTE_TABLE
 WHERE local_table.ID = MuLTiple_TABle_view.invoice_id(+)
   AND local_table.id = remote_table.ID
   and local_table.client_id = 108982416
*******************************************

, механизм Complex View Merging (CVM) для MuLTiple_TABle_view не срабатывает по определённым правилам/heuristics: (more…)

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

Блог на WordPress.com.