Delphi 5 performance issues - unknown SQL statement...

[Helene]

We have switched to Delphi 5, and after releasing the new version of our application we receive a lot of feedback on the response times. I have tried searching the v$sqlArea table in Oracle for "worst performing" sql statements in terms of consistent Gets (buffer Gets). There is one query that I cannot find anywhere in the application (I have searched both pas files and dfm files. This SQL is performed very often, actually more often than most of the other SQL statements from the application, and it performs very bad.

This is the SQL statement:

select constraint_name, column_name from sys.all_cons_columns
where owner =

wner and table_name = :table_name
and constraint_name not like 'SYS_C%'
order by constraint_name

I tried to search the entire project also for property setting EnforceConstraints; this property is false in the entire project. Also the constraints property of the TOracleProvider is false everywhere.

Simply starting and closing the application makes this SQL run 4 times, shown from the trace file after running it through TKProf (excluding system-sql):
select constraint_name, column_name from sys.all_cons_columns
where owner =

wner and table_name = :table_name
and constraint_name not like 'SYS_C%'
order by constraint_name

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.01 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.92 0.92 0 48840 0 29
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.92 0.93 0 48840 0 29

Now I don't know where this SQL comes from and how I can get rid of it / optimize it !! ??

This SQL is not included if I run the previous version of the application, which was built in Delphi 4.

Among other new things we have introduced is the use of TOracleEvent component, listening for alert messages. The Oracle Session is connected at design time, and the DesignConnection property is true.

I really hope you have some advice on how to solve this...

 

[Marco Kalter]

This query is executed by a TOracleSession to determine the constraints for a table. This will only happen if you have set the OracleDictionary.EnforceConstraints property of your TOracleDataSet components to True. It will also happen only once for each table for the lifetime of a connection of a TOracleSession.

If this query is slow (0.23 seconds is not exactly fast, I get about 0.01 - 0.02 seconds for tables with a reasonable amount of constraints), then you can try the following:

• Check if the dictionary of this database instance is performing optimally. Like I said, 0.23 second is really much too slow for this query.
  
• Make sure that this query does not happen too often. As long as the session remains connected, each table can only cause one constraint query.
  
• Set the EnforceConstraints property to False. This will completely avoid the query.

The first option will be the best solution if you can fix it. The last 2 options are really just work arounds.

------------------
Marco Kalter
Allround Automations

 

[Helene]

From what I can see, that is not completely correct. All of the datasets in the project have the OracleDictionary.EnforceConstraints set to false. I have now been able to trace the origin of these SQL statements. It occurs in this situation: one oracle dataset is connected to a TOracleProvider, which is connected to a client dataset. Both the oracle and the client dataset have the ReadOnly property set to true. EnforceConstraints and provider.Constraints are both false. The TOracleDataset.PSGetIndexDefs function is triggered, I have not been able to determine where it is called from. This function calls the GetConstraints function.

At first it also got called from the PSGetKeyFields. This call was eliminated by setting the UniqueFields property of the dataset.

Are there any properties that can be set either on the provider, oracle or client dataset to prevent this from happening?

Your other point about optimizing the performance of the Oracle Data Dictionary is obviously a good point anyway. What could we do to achieve this? Could the System tablespace be too fragmented?

We have seen that this particular SQL performs badly both in our test database and in at least one customer's database. I suspect that poor dictionary performance in general will be rather unfortunate for the overall system performance ? (Besides I agree that setting the enforceConstraints property to true probably would be a very good idea, given that the dictionary performance could be speeded up!

)

 

[Marco Kalter]

I see that I didn't read your first message closely enough. Sorry about that

. I'm not sure under what conditions the PSGetIndexDefs method is called, I will have to look into this. It must have something to do with the Index... properties of the TClientDataSet.

To find out why the query takes so long, you could try to determine the query plan of the select statement. You must be connected as SYS to do this. Maybe this will provide a clue...

------------------
Marco Kalter
Allround Automations

 

[Helene]

There are three client datasets. Two of them have not set any index properties, while the last one uses the IndexDef and IndexName properties. I believe the statement is sent for all three of them. This has not been changed since we ran the project with delphi 4, and the SQL was not sent then.

However, we found that the dictionary performance was significantly improved by rerunning the system scripts: catalog.sql, catproc.sql, catexp.sql, catoctk.sql, caths.sql, catldr.sql and
dbmspool.sql.

However, this does not seem to help improve the overall performance of the application

- I suspect this can be a Delphi 5 issue. You wouldn't happen to know if there are general performance problems in Delphi 5 as compared to Delphi 4?