DBMS Cost-Based Optimizers: Back Story and State of the Art

Query optimizers for relational databases have come a long evolutionary way to grow into complicated algorithms for assessing the costs of various options. However, the new generation databases pay almost zero attention to a query cost. What is it, a step back or two steps ahead? Do we even need new cost optimizers for the big data world at all?

В журнале Открытые системы №1, 2016 вышла моя статья Стоимостные оптимизаторы для СУБД: вчера и сегодня

В ней я пытаюсь рассуждать о стоимостной оптимизации в Oracle, Vertica и SparkSQL. Кажется, в области стоимостной оптимизации предстоит решить ещё множество интересных задач.

DBMS Cost-Based Optimizers: Back Story and State of the Art

iSCSI target for Oracle RAC cluster with SSD and 10 Gb Ethernet

It’s already an old one story🙂 More then year ago we was ripe for migrating from SATA to new SSD disk storage. The problem was our infrastructure wasn’t ready – I mean when I launched local performance measure test it showed 180K IOPS (in 8192 blocks) whilst remote (using iSCSI) performance measure test showed only 18K IOPS. 10 times is altogether bad.

After some investigation we found the bottleneck was iSCSI target. And since IET hasn’t being developed any more we decided to move out to SCST. Below is our experience with getting the maximum performance using SCST as iSCSI target.

1. About the measurements.

We used ORION for testing IO throughout. How to do this has been skilfully explained by Alex Gorbachev.

I made a bunch of experiments but executing just a single test appeared to be enough:

orion -run advanced -duration 60 -matrix row -num_large 0 -num_disks 300 -simulate raid0 -type rand

It lasts for about 96 minutes and gradually (in reality logarithmically from 1 to 1500) increases a number of parallel workers so we could see:
1. What is the throughput of a single session
2. What is the maximum throughput
3. The system behaviour: how many parallel session needs for reaching the peak, what happens after reaching the peak, what is the velocity of changing and so on

Other tests of course might be very useful too but in my case have added nothing new.

2. System-wide settings

Migrating from IET to SCST increased throughput from 18K IOPS to 30K IOPS. Good but not enough. The next step was in tuning OS and hardware devices. The similar was described in Tuning 10Gb network cards on Linux

Below are the results of my experiments


On the pictuire:

  • Y-axis is the overall throughput in 8K blocks;
  • X-axis is the amount of parallel read processes;
  • Green line – disk performance from one host without any changes, roughly 30K IOPS;
  • Red and blue lines – disk performance after changing the queue and scheduler settings (see for example tuning noop scheduler) for SSD devices on a target nodes, about 45K IOPS;
  • Yellow line – the same as red + changing the ixgb driver settings, increase the number of parallel processing queues, RSS and VMDQ (see details in README in the latest ixgbe Intel driver). As you can see the peak performance is the same but for small amount of parallel processes (less than 20) performance is higher on 5-10K IOPS, so we could state that the system is more responsive;
  • Brown line – same as yellow + NUMA affinity setttings (see for example IRQ affinity and CPU affinity) , as you can see performance is only slightly better, practically the same 45K IOPS;
  • Sky Blue – the total performance from 2 initiator hosts and 1 target host. Peak performance is 80K IOPS but periodically falls down to 60K IOPS;
  • Black line – “raw” performance from target host, without iSCSI, just for comparison.

3. Tuning iSCSI initiator

The difference between raw and iSCSI devices is almost 2 times, it’s really huge. After a series of experiments I did the test from target, when host is both initiator and target and got the same results. It was strange (network wasn’t used and a such degradation), I was confused and began checking all settings for each component. And at this moment I found post Bart Van Assche about parallel sessions and everything fell into place. My problem was not in target but in initiator. According to open-iscsi site maximum performance for one session is 50K IOPS. I’ve read it but didn’t relate to my configuration – as you could see in previous graph I’ve reached even 60K IOPS, but it was a theoretical limit for one open-iSCSI session. Cure is simple, I just changed my scst config to using multiple targets (and thus sessionsб 3 disks per 1 session):

TARGET iqn.2001-04.com.yandex:targettest01e_1.yandex.ru {
LUN 0 T01L200
LUN 1 T01L201
LUN 2 T01L202

TARGET iqn.2001-04.com.yandex:targettest01e_2.yandex.ru {
LUN 0 T01L203
LUN 1 T01L204
LUN 2 T01L205

And it increased IOPS twice to 100K IOPS from one host. Below is the new series of tests (sorry for unnecessary lines I made this graph for internal usage)


On a graph:

  • Y-axis is the overall throughput in 8K blocks;
  • X-axis is the amount of parallel read processes;
  • Blue and Red lines – “raw” performance from target host, without iSCSI, just for comparison;
  • Green and Yellow lines – multiple sessions from one host, about 80K IOPS;

4. Changing linux kernel

It is need to mention the last strange thing: the overall instability. During the measurements I saw that net performance varied from 4 to 8 Gb/s from time to time and the similar is being watched on the graph above (Green and Yellow lines). Surprisingly, it was solved as easily as in a case of iSCSI initiator. I just changed kernel from UEK R2 ( to UEK R3 (3.8.13) and you can see

  • Sky Blue – performance with UEK R3, about 100K IOPS.

To sum up, we got 100K IOPS from one host over iSCSI – it’s 8 Gb/s, I think for 10 Gb Ethernet it’s very close to the best result.

iSCSI target for Oracle RAC cluster with SSD and 10 Gb Ethernet

Patches for 12c Oracle Database in EBS environment

After migration to Oracle Database 12c and OeBS 12.2 we’ve faced a strange problem with searching database patches.

I’m used to a simple paradigm: we have our own set of patches. It could be a huge list but if we meet a problem it happens quite rarely and I had a plenty of time to investigate it.

But in 12.2 everything has changed. Now we have 2 set of patches. First is the old set that comes from our peculiarities – customizations, hardware and workload. Second is the new set which is recommended by OeBS development team (see Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID 1594274.1) ).

The problem is in an overall number of patches:

1. Our own list of bugs consists of about 10 items (among them are mainly patches for wrong query results (like 18650065: WRONG RESULTS ON QUERY WITH SUBQUERY USING OR EXISTS ) and RAC-specific issues (like 19124336 – ORA-600 [kjblcwscn:!wr]can occur when handling a stale but valid write completion) ).
2. List of OeBS patches (that should be installed too) from Doc ID 1594274.1 consists of 34 bugs and 22 patches (for
3. In addition to that 4 times a year is released Patch Set Update. It’s highly advisable install it (and in our case I can’t imagine how we would maintain our system without PSU).

All would be nothing, but some of the patches conflicts each other or PSU (and some conflicts both). So in order to meet all the requirements I should install PSU, all patches that haven’t been included in PSU, overlay patches that conflict PSU, merge patches that conflict each other and overlay merge patches that conflicts all of them.

I’ve made it once – for April 2015 PSU – checked each bug, find all patches and opened a couple of SR for missing ones.

After half a year I returned to the task and was horrified – number of bugs increased twice! I commenced to work with a list of bugs but this time was fortunate enough – in a one SR met engineer who gave me the proper list of links. Here it is:

1. There is no need to search recommended OeBS patches for each PSU by yourself. Just read Database Patch Set Update Overlay Patches Required for Use with PSUs and Oracle E-Business Suite ( Doc ID 1147107.1 ) – in my case all patches was in ‘Table 1 Release PSUs and Patch Lists for EBS r12.2 customers not using the In-Memory option’

2. There is no need to check list of patches on conflicts by hand. My Oracle Support has Conflict Checker Tool that could be used not only for analyzing but also for requesting patches. See How to Use the My Oracle Support Conflict Checker Tool for Patches Installed with OPatch [Video] ( Doc ID 1091294.1 )

So I installed October 2015 PSU, get my oracle inventory, list of patches from set 1. and Doc ID 1147107.1, perform “Analyze for Conflicts” steps, click “Request Patch” and made special query for missing merge overlay patch. I didn’t expect, but my merge patch was ready next day! And it’s all, I just downloaded patches from Download table and installed it in my system.

Patches for 12c Oracle Database in EBS environment

The necessity of right tool for checking Coherence cluster configuration

I faced with a quite interesting problem after upgrading Coherence in my Oracle Access Manager cluster.

The issue appeared right after upgrading, all log files of OAM cluster were full of messages like:

<2015-08-28 12:37:09.878/1513.280 Oracle Coherence GE (thread=Cluster, member=n/a): Delaying formation of a new cluster; IpMonitor failed to verify the reachability of senior Member(Id=1, Timestamp=2015-08-26 13:38:36.265, Address=xxx.xxx.xxx.xxx:9095, MachineId=3353, Location=site:,machine:oam1,process:12886, Role=WeblogicServer); if this persists it is likely the result of a local or remote firewall rule blocking either ICMP pings, or connections to TCP port 7>

At a first glance, it’s well known issue and relates in a some kind of network problems as described in Doc ID 1530288.1 IpMontor Failed To Verify The Reachability Of Senior Member. The only problem that I absolutely sure there is no firewall between coherence instances and all hosts in a cluster are reachable. Of course I’ve made all recommended tests: java ping test from Doc ID 1936105.1, multicast test from Doc ID 1936452.1, datagram test from 1936575.1.

I’ve even written a special program for checking whether method InetAddress.isReachable() works or not (as it’s described in What Is The Purpose Of IpMonitor In A Coherence Cluster? ( Doc ID 1526745.1 ))

It’s quite simple:

import java.net.*;

public class alive{
public static void main(String args[]){
InetAddress ia = InetAddress.getByName("oam2");;
boolean b = ia.isReachable(10000);

}catch(Exception e){
System.out.println("Exception: " + e.getMessage());

What I tried is to check availability of host oam2 from host oam1. And it worked.

[oracle@oam2 ~]$ /opt/oracle/jrockit/bin/java alive

Also I colud see icmp packets in tcpdump:

12:32:40.656815 IP (tos 0x0, ttl 64, id 16265, offset 0, flags [DF], proto ICMP (1), length 72)
oam1 > oam2: ICMP echo request, id 25757, seq 1, length 52
12:32:40.656942 IP (tos 0x60, ttl 64, id 49076, offset 0, flags [none], proto ICMP (1), length 72)
oam2 > oam1: ICMP echo reply, id 25757, seq 1, length 52

The particularly strange was I couldn’t see any network packets (nor icmp nor tcp port 7) from working oam1 server whereas out file was full of error messages “IpMonitor failed to verify the reachability of senior Member”.

So OAM_Server seemed not to have sent network packets (I mean IpMonitor, there was a lot of traffic between servers on the other ports) at all.

After some investigation I found out a couple of interesting strings in a strace output:

27348 socket(PF_INET, SOCK_RAW, IPPROTO_ICMP) = -1 EPERM (Operation not permitted)
27348 socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 665
27348 bind(665, {sa_family=AF_INET, sin_port=htons(0), sin_addr=inet_addr("")}, 16) = 0
27348 connect(665, {sa_family=AF_INET, sin_port=htons(7), sin_addr=inet_addr("xxx.xxx.xxx.xxx")}, 16

Here you can see that program tried to send network packets to public address from a private address. In that moment it became clear the matter is in our specific network configuration.

But at first, let’s reproduce the issue. My code was wrong:

InetAddress ia = InetAddress.getByName("oam2");;
boolean b = ia.isReachable(10000);

And wrong was all coherence tests (like java ping test)
It is not how ipMonitor calls isReachable method. In Coherence it selects network interface for sending a ping packet:

InetAddress ia = InetAddress.getByName("oam2");;
NetworkInterface netIntfc = NetworkInterface.getByInetAddress(InetAddress.getByName("oam1")) ;
boolean b = ia.isReachable(netIntfc, 0 , 10000);

Let’s see in jdk source code, I mean InetAddress source code and isReachable method implementation.
Here if we launch isReachable and pass the parameter NetworkInterface (as it does Coherence) it’ll take the first IPv4address found on interface and uses it as a source address.

cat ./java/net/Inet4AddressImpl.java
class Inet4AddressImpl implements InetAddressImpl {
private native boolean isReachable0(byte[] addr, int timeout, byte[] ifaddr, int ttl) throws IOException;
public boolean isReachable(InetAddress addr, int timeout, NetworkInterface netif, int ttl) throws IOException {
byte[] ifaddr = null;
if (netif != null) {
* Let's make sure we use an address of the proper family
java.util.Enumeration it = netif.getInetAddresses();
InetAddress inetaddr = null;
while (!(inetaddr instanceof Inet4Address) &&
inetaddr = it.nextElement();
if (inetaddr instanceof Inet4Address)
ifaddr = inetaddr.getAddress();
return isReachable0(addr.getAddress(), timeout, ifaddr, ttl);

It works on windows (where interface could have only addresses from one network), but Linux kernel has source routing. We have 2 addresses in one interface: public and private. Jdk took first address it found (I checked, the same behavior is in last jdk 8u60), and in our case it was private address. And tried to send packets in a public network. Such types of network packets normally drop by kernel (see Reverse Path Filtering http://tldp.org/HOWTO/Adv-Routing-HOWTO/lartc.kernel.rpf.html) because they are meaningless. Programs should either do not use source address or use the right public address for send packets.

As a workaround I added rule for rewriting source addresses of those packets.

I’m not sure whether it’s bug or feature. And also I think our case is quite unique. But it’s once again reminded me the simple truth: if you test something you should do it that way as you do it in a production.

P.S. During investigation I realized that OAM Coherence is not synonymous with the Oracle Coherence (see Doc ID 1579874.1 Questions about OAM 11g Coherence Configuration)🙂 It has their own settings in oam-config.xml, they difference from Oracle Coherence settings.

P.P.S. Java could send icmp requests instead of tcp packets. You could read about it at stackoverflow or at stackexchange. We don’t use it.

The necessity of right tool for checking Coherence cluster configuration

ACFS and OeBS 12.2

I know it’s an unsupported configuration (and what’s a supported configuration then?) but if you use ACFS as a shared filesystem for OeBS 12.2 you might face with some strange problem by using new adop functionality.

I run adop phase=fs_clone and get the error on my system:

ERROR while running FSCloneApply...
ERRORMSG: /opt/acfs/R12.2/fs1/EBSapps/comn/adopclone_appltest01e/bin/adclone.pl did not go through successfully.

AutoConfig could not successfully execute the following scripts:
ouicli.pl INSTE8_APPLY 1

ERROR: RC-50013: Fatal: Instantiate driver did not complete successfully.
ERROR: RC-50004: Fatal: Error occurred in ApplyAppsTechStack:
RC-50013: Fatal: Failed to instantiate driver /opt/acfs/R12.2/fs2/EBSapps/10.1.2/appsutil/driver/regclone.drv

The truly error message could be found in ohclone.log:

Executing command sh -c "/opt/acfs/R12.2/fs2/EBSapps/10.1.2/oui/bin/runInstaller -printdiskusage -ignoreDiskWarning -debug -clone -silent -force -nolink -waitForCompletion -invPtrLoc /etc/oraInst.loc session:ORACLE_HOME=/opt/acfs/R12.2/fs2/EBSapps/10.1.2
Error returned: Value too large for defined data type
There is not enough space on the volume you have specified. Oracle Universal Installer has detected that you currently have 0 MB available on the chosen volume. 750 MB of space is required for the software.

It helps but not a lot. We could find document on MOS: rapidwiz File System Upgrade Fails With “There is not enough space on the volume you have specified.” Even Though There Is Sufficient Space (Doc ID 1942808.1)
There it was recommended to decrease size of NFS-mounted disk from 36 Tb to 2 Tb. The problem is I have 7 Tb ext4 volume where all works great. It looks like ACFS-specific issue.

After a couple of days investigation I noticed some strange strings in the strace output:

23174 statfs("/opt/acfs/R12.2/fs2/EBSapps/10.1.2", 0x5621f54c) = -1 EOVERFLOW (Value too large for defined data type)
23174 write(2, "Error returned: Value too large for defined data type\n", 54) = 54

So the error message seems to have gotten after statfs system call. I reproduced the issue by using a simple C code:


main(int argc, char **argv) {
char fn[]="/opt/acfs/R12.2/fs2/EBSapps/10.1.2";
char *init_d;
struct statfs info;
int file_descriptor;
init_d = *++argv;
printf("Filesystem to check: %s\n", init_d);
file_descriptor = statfs(init_d, &info);
int errsv = errno;
printf(" Result: %d error string: %s\n", file_descriptor,strerror(errsv));
printf(" Type of filesystem: %08x\n", (int) info.f_type);
printf(" Optimal transfer block size: %lld\n", (long long) info.f_blocks);
printf("Total data blocks in filesystem: %lld\n", (long long) info.f_blocks);
printf(" Free blocks in filesystem: %lld\n", (long long) info.f_bfree);
printf(" Free blocks available: %lld\n", (long long) info.f_bavail);
printf(" Total file nodes in filesystem: %d\n", (int) info.f_files);
printf(" Free file nodes in filesystem: %d\n", (int) info.f_ffree);
printf(" Maximum length of filenames: %d\n", (int) info.f_namelen);
printf(" Fragment size: %d\n", (int) info.f_frsize);
printf(" Mount flags of filesystem: %d\n", (int) info.f_flags);

This code should be compiled as a 32-bit application (in a 64 bit mode it works well).

The error output:

[oracle@appltest01e.oebs.yandex.net ~]$ ./test_statfs /opt/acfs
Filesystem to check: /opt/acfs
Result: -1 error string: Value too large for defined data type
Type of filesystem: 00000000
Optimal transfer block size: 1224734968
Total data blocks in filesystem: 1224734968
Free blocks in filesystem: 1
Free blocks available: 13238272
Total file nodes in filesystem: 0
Free file nodes in filesystem: 15774463
Maximum length of filenames: 20468
Fragment size: 1226387928
Mount flags of filesystem: 0

The real problem here is the number of inodes. I compared ext4 and acfs and the number of inodes in our ext4 filesystems are usually small and could be written in unsigned longint:

$ df -i
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/md1 242872320 5583659 237288661 3% /opt

whereas for acfs:

$ df -i
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/asm/acfs-116 4294967296 836130432 3458836864 20% /opt/acfs

The differences are very lucidly explained in Doc ID 2026700.1 Does ACFS Use inode architecture?:

ACFS filesystems does not use the inode architecture.
ACFS filesystems does not have a pre-allocated inode table.
Therefore, “df -i” command returns the number of inodes that are theoretically possible given the space remaining.
On ACFS filesystems, the inode table grows dynamically. Any free storage is eligible for inode creation.

The number of inodes in my case (4294967296) cannot be stored as unsigned longint. And it is twice more than disk volume size.

So we just resized ACFS volume

acfsutil size -100G /opt/acfs

Now all works fine. There is only one unpleasant feature – 2 Tb limit of disk volume. Hope OeBS team will change their mind about ACFS support someday and we will be able to store all the data in a one volume.

ACFS and OeBS 12.2

Upgrade Identity Management

It was the spring when we upgraded our IDM system for EBS authentication. Now a few months have passed and all seems to have been working fine so I’d like to share a brief notes about issues we met and a final configuration.

We have a classical for identity management 3-Nodes configuration and now we’re running on:
OEL6 with UEK R3 kernel
Oracle Database + PSU3
Java 1.7_75

The most difficult (for me) question was where could I find documentation for upgrading? Here what I collected:
Database upgrade guide
Oracle® Fusion Middleware Upgrade Guide for Oracle Identity and Access Management 11g Release 2 (
Patch Management of an Oracle Identity Management Deployment
OAM Bundle Patch Release History [ID 736372.1]
How to Upgrade OID/ OVD To (IDM PatchSet 6) (Doc ID 1962045.1)
Considerations When Applying Patch Sets to FMW 11g Release 1 Identity Management (Doc ID 1298815.1)
Master Note on Fusion Middleware Proactive Patching – Patch Set Updates (PSUs) and Bundle Patches (BPs) (Doc ID 1494151.1)
Oracle Internet Directory (OID) Version Bundle Patches For Non-Fusion Applications Customers (Doc ID 1614114.1)
Integrating Oracle E-Business Suite Release 12 with Oracle Access Manager 11gR2 (11.1.2) using Oracle E-Business Suite AccessGate [ID 1484024.1]

I cannot say that there weren’t any problems at all but the vast majority of SR I opened were on OAM. They were mostly related to misconfigurations in oam-config.xml file. Either because of upgrade or because of old errors. All this is our local particularity and not so interesting.

All works great in a test environment but the first day after upgrade production system was quite strained:

1. It appears that index IDX_JPS_PARENTDN in schema OID_OPSS was replaced by composite index. The old non-unique index was extended by unique key entity_id. This led to that the index grew from 300 to 600 MB. Explain plans also changed. All still works fine for a single client session. But when clients commenced connect at the same time execution time of search queries increased significantly (because of heavy disk IO) and method “ldapbind” was timing out. I recreated the old index and the majority of problems has gone.

2. Some queries still used the new one (composite) index. After some investigation we noticed too high cost of their explain plans. It was new 12c query re-optimization functionality named “SQL Plan Directives”. Igor Usoltsev wrote about it. We just disabled those SQL Directives.

3. Http GET response significantly increased. For some type of browsers (IE11 for example) it became more than 8K. We terminated SSL/TLS at a proxy side. And proxy (we use nginx) drops large packets by default. Large packets could get from both client and server sites. For nginx we increased buffer sizes (there are several settings, one for client side and other for server).

P.S. It would be useful also read this community thread. We haven’t faced with Coherence issues yet, our versions works fine but folks reports that is more stable.

Upgrade Identity Management

Extended statistics и invalid objects

После миграции на 12с c удивлением обнаружили, что у нас стали появляться столбцы с расширенной статистикой

select count(*) from dba_tab_col_statistics where column_name like 'SYS_ST%';


Функционал в принципе известный, проблема только в том, что мы не запускали DBMS_STATS.CREATE_EXTENDED_STATS.

После внимательного исследования архивных журналов выяснилось, что столбцы создаются в моменты сбора статистика по таблице. Чтение кода пакета DBMS_STATS показало, что в новой версии безусловно вызывается процедура создания расширенной статистики по объектам. Эта процедура сканирует таблицу COL_GROUP_USAGE$ и, если там есть новые записи, создаёт по ним расширенную статистику. Для создания расширенной статистики достаточно собрать статистику на уровне таблицы.

Посмотреть объекты, по которым создаётся статистика, можно запросом:

order by timestamp desc;

Стало интересно: а кто и как помещает записи в таблицу COL_GROUP_USAGE$ ? Опять помог logminer. Записи создаются рекурсивными транзакциями пользовательских сессий. Например, в моём исследовании для таблицы CSI_I_PARTIES была обновлена запись о столбцах PARTY_SOURCE_TABLE, RELATIONSHIP_TYPE_CODE, CONTACT_FLAG, ACTIVE_END_DATE. Сделано это было на этапе hard parse запроса:

SELECT parties.PARTY_ID PartySourceId ,
parties.RELATIONSHIP_NAME CsietParty_RelaTypeName ,

Plan hash value: 502729463

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 21750 (100)| |
| 1 | HASH GROUP BY | | 1 | 469 | 21750 (1)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | CSI_I_PARTIES | 21 | 735 | 97 (0)| 00:00:01 |
|* 78 | INDEX RANGE SCAN | PER_PEOPLE_F_N55 | 2 | | 1 (0)| 00:00:01 |

Predicate Information (identified by operation id):

- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
- 5 Sql Plan Directives used for this statement

Краткое описание процесса также можно найти в документе Are Extended Statistics Collected Automatically on Oracle 12c? (Doc ID 1964223.1)

А также читаем в документе Optimizer with Oracle Database 12c:

SQL plan directives are also used by Oracle to determine if extended statistics, specifically column
groups, are missing and would resolve the cardinality misestimates. After a SQL directive is used the optimizer decides if the cardinality misestimate could be resolved with a column group. If so, it will
automatically create that column group the next time statistics are gathered on the appropriate table.

Директива плана выполнения, которая послужила источником изменений, находится запросом:

SELECT TO_CHAR(d.directive_id) dir_id,
o.subobject_name col_name, o.object_type, d.type, d.state, d.reason, d.created, d.last_used
FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o
WHERE d.directive_id=o.directive_id
AND o.object_name = 'CSI_I_PARTIES'
ORDER BY reason, created, object_type, col_name, dir_id;





Здесь видим несколько записей с одинаковым значением directive_id для разных столбцов, а также что эта статистика появилась в результате работы механизма Dynamic Sampling из-за неверной оценки селективности ранее.

Какие объекты расширенной статистике для таблицы уже были созданы, можно посмотреть через представление dba_stat_extensions. В нашем случае запрос показал, что такой статистики ещё нет (об этом же косвенно говорит статус ‘USABLE’ и директивы):

Во всём этом великолепном механизме есть только одна ложка дёгтя: Bug 19450314 : INVALIDATIONS IN 12C

Утром на второй день после миграции я с некоторым удивлением обнаружил в нашей системе порядка 30 000 инвалидных объектов. Пришлось всё останавливать, компилировать их скриптом utlrp, и только потом подавать пользовательскую нагрузку снова.

Баг весьма неприятный, в простых случаях не воспроизводится, однако под нагрузкой проявляет себя так: добавление столбцов для расширенной статистики в таблицу может приводить к тому, что зависимые объекты инвалидируются. Как правило затрагивает это те объекты, с которыми больше всего работают. Далее в системе появляется множествеенные library cache pin/lock, и приходится принимать экстренные меры.

С нетерпением ждём, когда же баг исправят, а пока что живём со значением параметра _optimizer_dsdir_usage_control=0 и не создаём новую расширенную статистику …

P.S. Вышел патч на Bug 19450314 “UNNECESSRAY INVALIDATIONS IN 12C” – у нас уже установлен в production и пока что проблем с инвалидацией не наблюдаем.

Extended statistics и invalid objects