Здравствуйте, разумные.

Передали на сопровождение чужой подтормаживающий код.
Вставка записей идет во вьюху, на которую повешен замещающий (INSTEAD) триггер, который и делает нужные INSERT/UPDATE/DELETE в 3 таблицы.
Сделал трейс тормозящего куска сессии.
При анализе вижу, что 90% времени по сессии теряется на запрос, который вызывается при каждой вставке:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options




Запрос порождает внутренний механизм Oracle.
В переданных исходниках ничего подобного нет.
Прошу проконсультировать: откуда берется, как избавиться или оптимизировать.

Спасибо за помощь.

Для ищущих сохраню ответ:

The query is executed by the Oracle Data Provider for .Net to gather the primary key and unique column information of the columns selected in the sql query. The only way to reduce the number of times this sql query is executed by ODP.NET is by using Statement Caching.

When you use statement caching, on the first execution of the sql statement. ODP.NET will issue a query to gather the primary key and unique column information. Once it has been gathered, it will store that information in the statement cache. On the next execution of that sql statement on the same connection, it will use the information from the statement cache to execute the sql.

With ODP.NET versions 11.1.0.6.20 or earlier, the statement cache is enabled by default. The Statement Cache Size=10.

With ODP.NET versions 11.1.0.7.20 or later, Self Tuning is introduced and enabled by default. This feature provides dynamic tuning of the statement cache for applications to achieve optimal performance. This eliminates the need to manually tune the cache for an application.


When using ODP.NET versions 11.1.0.7.20 or later and self tuning is disabled manually, you will need to manually set the Statement Cache Size to enable Statement Caching for your application. This is referred to as "Manually Tuning the Cache".

Statement Caching can be turned on in the connection string by using the setting:

Statement Cache Size = <Number of sql statements you want cached> ie. Statement Cache Size=200;The query is executed by the Oracle Data Provider for .Net to gather the primary key and unique column information of the columns selected in the sql query. The only way to reduce the number of times this sql query is executed by ODP.NET is by using Statement Caching.

When you use statement caching, on the first execution of the sql statement. ODP.NET will issue a query to gather the primary key and unique column information. Once it has been gathered, it will store that information in the statement cache. On the next execution of that sql statement on the same connection, it will use the information from the statement cache to execute the sql.

With ODP.NET versions 11.1.0.6.20 or earlier, the statement cache is enabled by default. The Statement Cache Size=10.

With ODP.NET versions 11.1.0.7.20 or later, Self Tuning is introduced and enabled by default. This feature provides dynamic tuning of the statement cache for applications to achieve optimal performance. This eliminates the need to manually tune the cache for an application.


When using ODP.NET versions 11.1.0.7.20 or later and self tuning is disabled manually, you will need to manually set the Statement Cache Size to enable Statement Caching for your application. This is referred to as "Manually Tuning the Cache".

Statement Caching can be turned on in the connection string by using the setting:

Statement Cache Size = <Number of sql statements you want cached> ie. Statement Cache Size=200;