Query on ALL_CONS_COLUMNS And ALL_CONSTRAINTS When Using ODP.Net

karthi_

How to disable this query running from ODP when the application runs.

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

TkProf :

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse     7802      0.21       0.19          0          0          0           0

Execute   7802      3.67       3.60          0          0          0           0

Fetch     7802    580.19     583.00          0   27988736          0       25966

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    23406    584.08     586.80          0   27988736          0       25966

Mark Williams

Hello,

If you have access to My Oracle Support the following knowledge base article might have some useful information:

Frequent Query on ALL_CONS_COLUMNS And ALL_CONSTRAINTS When Using ODP.Net Statement Caching (Doc ID 1386371.1)

Regards,

Mark

Alex Keh-Oracle

Which ODP.NET API is running this command? Is this an app you're building yourself or an ISV app?

karthi_

We are using the unmanaged DLL in an app we built ourselves. 

Alex Keh-Oracle

I was actually asking about the specific ODP.NET API being called rather than about the provider type (i.e. managed or unmanaged).

Most likely, the answer to your question is no, the SQL can't be disabled, unless you can workaround the API call itself. Knowing the API would be useful to be able to present alternatives, if any.

From a user perspective, you can limit the columns and constraints the user has access to speed up the query.

Mark Williams

Hello,

If you have access to My Oracle Support the following knowledge base article might have some useful information:

Frequent Query on ALL_CONS_COLUMNS And ALL_CONSTRAINTS When Using ODP.Net Statement Caching (Doc ID 1386371.1)

Regards,

Mark

Alex Keh-Oracle

Thanks, Mark, for the pointer. The short answer to the question is to enable statement caching to prevent the frequent execution of that specific SQL.

karthi_

Thanks Alex,

after enabling the statement cache I was able to achieve my previous runtime .

had set the Statement Cache Size=2000 equal to the number of open_cursors on the DB.

But I still see this query goes in for 7k parse/execute/fetch ,

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse     7802      0.21       0.19          0          0          0           0

Execute   7802      3.67       3.60          0          0          0           0

Fetch     7802    580.19     583.00          0   27988736          0       25966

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    23406    584.08     586.80          0   27988736          0       25966

Alex Keh-Oracle

Statement caching allows you to soft parse the SQL instead of hard parse. The parse count may be the same, but it's definitely faster, uses less CPU, and network as metadata doesn't need to be re-fetched.