Inefficient default query from Crystal Reports 1

[Guest_imported]

I have a problem with a query that I see in our Oracle9 database coming from a Crystal Reports application we have. (I'm the Oracle DBA here, not a Crystal Reports expert.) The query selects some information from Oracle's data dictionary apparently to get a list of columns and data types for a table the report then selects data from. Here is what the query looks like to Oracle (it was definitely *NOT* written by our in-house Crystal Reports developer):
--
SELECT '', owner, table_name, column_name, 0, data_type, data_precision, decode(data_type,'DATE',16,'FLOAT',8,'LONG RAW',2147483647,'LONG',2147483647,'CLOB',2147483647,'BLOB',2147483647,'BFILE',2147483647,'NUMBER',NVL(data_precision+2,40),data_length), data_scale, 0, decode(nullable, 'Y', 1, 'N', 0), '' , '', 0, 0, 0, column_id, decode(nullable, 'Y', 'YES', 'N', 'NO') FROM sys.all_tab_columns WHERE TABLE_NAME LIKE 'YIELD_SUMMARY_AREA_VIEW' AND OWNER LIKE 'OEC_PROD' UNION SELECT '', b.owner, b.synonym_name, a.column_name, 0, a.data_type, a.data_precision, a.data_length, a.data_scale, 0, decode(a.nullable, 'Y', 1, 'N', 0), '' , '', 0, 0, 0, a.column_id, decode(a.nullable, 'Y', 'YES', 'N', 'NO') FROM sys.all_tab_columns a, sys.all_synonyms b WHERE a.table_name = b.table_name AND b.synonym_name LIKE 'YIELD_SUMMARY_AREA_VIEW' AND b.OWNER LIKE 'OEC_PROD' ORDER BY 2,3,17
--
This causes a very high rate of "logical reads" for Oracle each time this query is submitted, and it is submitted hundreds of times per day. I could understand a query like this being submitted by Crystal Reports when a report is being developed, but this is happening at run-time for existing reports. Is there any way to turn this off, or avoid it?

 

[vidru]

As a test, open one of the offending reports, go to the Database menu, and see if 'Verify on Every Print' is checked. If so, try unchecking it, and resaving the report. That's the only thing I can think of that would run a test on the db structure.

-dave

 

[synapsevampire]

Data dictionaries aren't suggested by Crystal, so I would suggest that you rewrite the report.

-k

 

[Guest_imported]

Thank you vidru. That looks like it might be the problem. Our Crystal Reports developer is not here today. Hopefully he will be tomorrow and we can check your suggestion then.