Oracle mechanics

29.03.2021

Особености event 10235

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

Продолжая про event, при разборе неприятной проблемы ORA-600 [532] leads to ORA-700 [ksepop:1 ksepop recursion ] аналитик поддержки с оговорками* рекомендовал в целях диагностики установить:

19.0.0.0.SCOTT@ SQL> alter system set event = '10235 trace name context forever, level 65538' container=all sid='*' scope=spfile;

System altered.

, что (после применения параметра/рестарта) приводит и интересному эффекту в части использования PL/SQL, %rowtype и EXECUTE IMMEDIATE с IN OUT параметрами (**): (more…)

19c upgrade: ORA-1555 is not reported in alert log

Filed under: bugs,Диагностика системы (instance),Oracle — Игорь Усольцев @ 00:12
Tags: , ,

В начале месяца Алексей Фролов (Спортмастер) на семинаре RuOUG рассказал о непростом пути обновления на версии 19c

В дополнение к теме имеется любопытный опыт работы с Bug 31888148 — ORA-1555 is not reported in alert log after RU 19.4.0.0.0 (Doc ID 31888148.8), к счастью, уже поправленном в современных версиях:

The fix for 31888148 is first included in	
21.1.0.0.0
19.9.0.0.DBRU:201020 (OCT 2020) DB Release Update Revision(DB RU)
19.8.1.0.0 (Oct 2020) DB Release Update Revision(DB RUR)
19.7.2.0.0 (Oct 2020) DB Release Update Revision(DB RUR)

В качестве workaround-а проблемы ORA-01555 is Not Reported in Alert Log Since RU 19.4.0.0.0 (Doc ID 2656624.1) в документе указывается 2 пути: условно сложный (применение патча) и простой (изменение параметра)

SOLUTION
Please apply patch 31888148.

or

Please set the following events parameter.

SQL> alter system set events '10442 trace name context forever, level 10';

Простой путь открывает бездны не сразу (more…)

27.09.2020

Oracle 19c: TO_NUMBER, aggregate functions и проблемы параллельного выполнения

Filed under: Oracle,Parallel Execution — Игорь Усольцев @ 21:15
Tags: ,

Если в версии 12.1 проблем с TO_NUMBER, агрегатными функциями и параллельным выполнением запроса не наблюдалось:

12.1.0.2.@ SQL> create table scott.t2number tablespace users as
  2  select
  3     to_char(level,'99999.99') a
  4  from dual
  5  connect by level < 1e5
  6  /

Table created.

SQL> SELECT --+ parallel(8) monitor
  2   sum(TO_NUMBER(a, '99999.99'))
  3    FROM scott.t2number
  4  /

SUM(TO_NUMBER(A,'99999.99'))
----------------------------
                  4999950000

(more…)

28.08.2020

Oracle 19c: Bug 27175987 — disable partition pruning with non-deterministic functions

Filed under: Oracle — Игорь Усольцев @ 12:32

По следам недавнего успешного «краудсорсингового» решения проблемы в телеграм-группе @RuOUG

Собственно, моей была только формулировка проблемы:

а не сталкивался ли кто с нерабочим partition pruning в 19.5 при использовании в качестве предикатов значений функций?
в 12.1 всё работало(

Антон подтвердил, а Саян Малакшинов — тут же предложил рабочее решение:

[xtender]
Ага, что-то смутно знакомое, кажется я тогда делал x in (select func(…) from dual)

[Anton Spitsyn]
Действительно, в Oracle 18.9 когда в предикате значение функции, partition pruning работает, а в 19.4 уже не работает. Если функцию обернуть в select func(…) from dual, то начинает работать и в 19.4

Тест: (more…)

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

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

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