Query on ALL_CONS_COLUMNS And ALL_CONSTRAINTS When Using ODP.Net — oracle-tech

    Forum Stats

  • 3,716,134 Users
  • 2,242,961 Discussions
  • 7,845,841 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Query on ALL_CONS_COLUMNS And ALL_CONSTRAINTS When Using ODP.Net

karthi_
karthi_ Member Posts: 9
edited July 2019 in ODP.NET

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

Best Answer

  • Mark Williams
    Mark Williams Member Posts: 67 Blue Ribbon
    edited July 2019 Accepted Answer

    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

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited July 2019

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

  • karthi_
    karthi_ Member Posts: 9
    edited July 2019

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

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited July 2019

    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
    Mark Williams Member Posts: 67 Blue Ribbon
    edited July 2019 Accepted Answer

    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
    Alex Keh-Oracle Posts: 2,753 Employee
    edited July 2019

    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_
    karthi_ Member Posts: 9
    edited July 2019

    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
    Alex Keh-Oracle Posts: 2,753 Employee
    edited July 2019

    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.

Sign In or Register to comment.