Если в версии 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
По следам недавнего успешного «краудсорсингового» решения проблемы в телеграм-группе @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…)
Обратил внимание, что в стандартном автоматически созданном (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…)
Столкнулись с проблемой, подготовил простой тесткейс на данных схемы 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…)
С версии 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…)
В процессе тестирования 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…)
После формально успешного создания нестандартного индекса по 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…)
Проблемы, выраженные в повышенном соотношении 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…)
SQL Optimization, на примерах из Oracle E-Business Suite
После возникновения/обсуждения в группе @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…)
 | Игорь Усольцев к записи Oracle 19c: TO_NUMBER, aggrega… |
| Игорь Усольцев к записи PDB 12.2: Ожидание cursor: pin… |
 | Mikhail Velikikh к записи Oracle 19c: TO_NUMBER, aggrega… |
| Игорь Усольцев к записи Oracle 19c: Bug 27175987… |
 | skahinpihel к записи Oracle 19c: Bug 27175987… |
Oracle Optimizer blog Jonathan Lewis blog Richard Foote’s Oracle Blog Коллегиальный блог