Re: Crystal reports issue?
- From: Brian Wisniewski <brian_wisniewski@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 31 May 2005 13:16:04 -0700 (PDT)
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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
---------------------------------
Do You Yahoo!?
Yahoo! Small Business - Try our new Resources site!
--
//www.freelists.org/webpage/oracle-l
Other related posts:
