Re: Crystal reports issue?

From: Brian Wisniewski <brian_wisniewski_at_yahoo.com>
Date: Tue, 31 May 2005 13:16:04 -0700 (PDT)
Message-ID: <20050531201605.90003.qmail@web32215.mail.mud.yahoo.com>

FYI. Found the issue with this. Apparently Crystal reports has some built-in 'feature' something like validate schema or something like that. The developers found when they had that feature turned on that the reports at client site errored out 'less often'. The reports were built on SqlServer ported to Oracle and they turned on this option and left it on. Every time a report ran so did the sql to validate the schemas and it was a dog.

Once they turned this off I traced the sessions and magically that problem went away.

Brian Brian Wisniewski <brian_wisniewski_at_yahoo.com> wrote: I'm trying to debug an application which had zero dba participation when it was built and now a client who is using it is experiencing 'performance' problems.

In their statspack reports I originially thought recursive sql was the problem based on the way the sql was structured and the developers claiming it wasn't their code. Statspack wasn't showing the entire sql so it threw me for a loop initially.

We brought in an export of their db and loaded it and I traced a run of the problem child.

I found the exact same sql in the trace file that was showing as hugely expensive in the statspack report. Since then I found out that Crystal reports is somehow integrated into this application.

Has anyone seen anything like this kicked out of Crystal?

There is no user named 'Admi', they don't have db_links but they have a ton of synonyms.

SELECT /*+ RULE */ COUNT(*) FROM ALL_SYNONYMS WHERE DB_LINK IS NOT NULL AND SYNONYM_NAME='CLASS' AND OWNER='Admi'

SELECT /*+ RULE */ COUNT(*) FROM ALL_SYNONYMS WHERE DB_LINK IS NOT NULL AND SYNONYM_NAME='CLASS' SELECT /*+ RULE */ '',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 all_tab_columns WHERE TABLE_NAME='CLASS' AND OWNER='Admi' UNION SELECT /*+ RULE */ '',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 all_tab_columns a, all_synonyms b WHERE a.table_name = b.table_name and a.owner = b.table_owner AND b.synonym_name ='CLASS' AND b.OWNER='Admi' ORDER BY 2,3,17 I have no clue as to why these queries would be run in the first place since they'd return nothing. But these type of queries are definitely the source of the problem.

I know next to nothing about Crystal reports and was hoping someone had experienced something similiar to this before.

Thanks - Brian

Yahoo! Mail
Stay connected, organized, and protected. Take the tour

--

http://www.freelists.org/webpage/oracle-l

Do You Yahoo!?
Yahoo! Small Business - Try our new Resources site!

--

http://www.freelists.org/webpage/oracle-l Received on Tue May 31 2005 - 16:21:11 CDT