Oracle mechanics

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

Выступление Тимура Ахмадеева, 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…)

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

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