Devart Forums

Posted: **Tue 16 Oct 2007 20:10**

My DBA is questioning me about this query that is a high CPU Bound:

SELECT
  Column_Name Col, Con.Constraint_Name Con
FROM
  All_Constraints Con, All_Cons_Columns Col
WHERE
  Con.Owner = :Owner and
  Col.Owner = :Owner and
  Con.Table_Name = :Name and
  Col.Table_Name = :Name and
  Con.Constraint_Type in ('P', 'U') and
  Con.Constraint_Name = Col.Constraint_Name
ORDER BY
  Con.Constraint_Name,Col.Position

I monitored my application and I cant see this query using Direct mode and ODAC monitor, I switched my application to Client mode and using TOAD monitor, I've noticed that it is executed after somes queries sometimes and before somes queries other times (see log bellow)

Code: Select all


-- AFTER

Timestamp: 17:37:03.523
SELECT SGL_PAIS, NOME_PAIS
  FROM PAIS
  WHERE SGL_PAIS  :pPais
  ORDER BY NOME_PAIS
pPais = 'BR'
----------------------------------
Timestamp: 17:37:04.333
SELECT
  Column_Name Col, Con.Constraint_Name Con
FROM
  All_Constraints Con, All_Cons_Columns Col
WHERE
  Con.Owner = :Owner and
  Col.Owner = :Owner and
  Con.Table_Name = :Name and
  Col.Table_Name = :Name and
  Con.Constraint_Type in ('P', 'U') and
  Con.Constraint_Name = Col.Constraint_Name
ORDER BY
  Con.Constraint_Name,Col.Position
Name = 'PAIS'
Owner = 'ARRECADACAO'

-- BEFORE

Timestamp: 17:37:29.995
SELECT
  Column_Name Col, Con.Constraint_Name Con
FROM
  All_Constraints Con, All_Cons_Columns Col
WHERE
  Con.Owner = :Owner and
  Col.Owner = :Owner and
  Con.Table_Name = :Name and
  Col.Table_Name = :Name and
  Con.Constraint_Type in ('P', 'U') and
  Con.Constraint_Name = Col.Constraint_Name
ORDER BY
  Con.Constraint_Name,Col.Position
Name = 'PERFIL_USUARIO'
Owner = 'ARRECADACAO'
----------------------------------
Timestamp: 17:37:30.197
SELECT /*+ RULE */
       PU.IDNUMBERPERFIL,
       P.NOMPERFIL,
       PU.IDNUMBEROPERADOR,
       PU.IDUSUCAD,
       PU.DATCAD
  FROM ARRECADACAO.PERFIL_USUARIO PU,
       ARRECADACAO.PERFIL         P
 WHERE (PU.IDNUMBERPERFIL = P.IDNUMBERPERFIL) AND IDNUMBEROPERADOR = :IDNUMBEROPERADOR
IDNUMBEROPERADOR = 2881

Looking at the component in my forms the queries has all default parameters and no field added.

I wanna know in which situations this query is executed and if is there a way of disable it

My specs are:

ODAC Version 6.20 (Direct Mode)
BDS 2006
Oracle 10g R2

Any help will be apreciatted.

Posted: **Thu 18 Oct 2007 09:21**

The query to ALL_CONSTRAINTS and ALL_CONS_COLUMNS tables is executed to detect primary keys for your tables. Set the KeyFileds property of TOraQuery to disable this query.

Posted: **Thu 18 Oct 2007 16:57**

Ok, but this detecting isn't executed for all of my queries and I am not using KeyFields property in my programs, must exists another situation for this detection be executed.

Posted: **Fri 19 Oct 2007 09:38**

The query for detecting primary key is executed when you insert, update or delete a record in the dataset first time. At this time ODAC generates a SQL statement to perform operation on the database table, and ODAC needs key fields to generate such statements.
ODAC does not execute the query for detecting primary key in the following cases: you have set the KeyFields property, or there is a ROWID field in your SELECT statement, or you have assigned values to the SQLInsert, SQLUpdate, SQLDelete, SQLRefresh, SQLLock properties.

Devart Forums

High CPU Bound query executed in Background

High CPU Bound query executed in Background