BulkSaveChanges (Oracle) // Query on ALL_CONS_COLUMNS, ALL_CONSTRAINTS added

[RenePhaneuf]

When i'm using BulkSaveChanges on Oracle 12c database, I see in the trace file the following query appearing

SELECT C1, C2, C3 
FROM   (SELECT ACC.COLUMN_NAME     C1
              ,ACC.CONSTRAINT_NAME C2
	  		  ,AC.CONSTRAINT_TYPE  C3 
        FROM   ALL_CONS_COLUMNS ACC
		      ,ALL_CONSTRAINTS  AC 
	    WHERE  (AC.CONSTRAINT_TYPE = 'P' OR AC.CONSTRAINT_TYPE = 'U') 
		AND    AC.TABLE_NAME = :B2 
		AND    AC.OWNER = :B1 
		AND    AC.TABLE_NAME = ACC.TABLE_NAME 
		AND    AC.OWNER = ACC.OWNER 
		AND    AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME 
		UNION 
	    SELECT AIC.COLUMN_NAME C1, AI.INDEX_NAME C2, 'U' C3 
	    FROM   ALL_INDEXES AI
		      ,ALL_IND_COLUMNS AIC 
	    WHERE  AI.UNIQUENESS = 'UNIQUE' 
		AND    AI.TABLE_NAME = :B2 
		AND    AI.TABLE_OWNER= :B1 
		AND    AI.TABLE_NAME = AIC.TABLE_NAME 
		AND    AI.TABLE_OWNER = AIC.TABLE_OWNER 
		AND    AI.INDEX_NAME = AIC.INDEX_NAME 
		AND    AI.OWNER = AIC.INDEX_OWNER
	   ) 
ORDER BY 3, 2, 1

This query take around 1-2 seconds to execute which makes the BulkSaveChanges slower than the normal SaveChanges.

There any reason why it's added?

• EF version: 6.4.4
• EF Extensions version: 5.2.16
• Database Provider: Oracle 12c

[JonathanMagnan]

Hello @RenePhaneuf ,

After investigation, this query is not one we directly execute but it gets executed automatically when we call the GetSchemaTable from the reader to retrieve column information. So we don't really have control over it.

However, it should not take 1-2s as it looks to be a very basic query.

Does it take you this time also when you execute it directly in your Oracle client?

Best Regards,

Jon

[RenePhaneuf]

@JonathanMagnan

Most of the time yes it's take the same time when i'm executing the query directly in the Oracle client.

I have also tried these commands as a possible solution, but it doesn't help

purge dba_recyclebin
exec dbms_stats.gather_dictionary_stats
exec dbms_stats.gather_fixed_objects_stats

exec dbms_stats.gather_system_stats
exec dbms_stats.gather_processing_rate

[JonathanMagnan]

Thank you,

We will investigate more on this query