Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: crystal reports and 10gR2

Re: crystal reports and 10gR2

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 23 Oct 2007 08:13:47 -0700
Message-ID: <1193152427.288371@bubbleator.drizzle.com>


cptkirkh wrote:
> I recently upgraded to 10.2.0.3 and I have an applicatuion that runs
> crystal reports. it seems now one of the reports as it starts up the
> report which logs on the report to each table starts running sql code
> i have never seen before and causes a lot of CPU usage. Does anyone
> know what this could be? This wasn't happening in 10.1.0.5.
>
> /* Formatted on 2007/10/22 21:44 (Formatter Plus v4.8.8) */
> SELECT COUNT (*)
> FROM xdb.xdb$schema s
> WHERE BITAND (TO_NUMBER (s.xmldata.flags, 'xxxxxxxx'), 16) = 16
> AND xdb.xdb$extname2intname (s.xmldata.schema_url,
> s.xmldata.schema_owner) =
> :
> 1
> AND s.xmldata.schema_url NOT IN (
> SELECT s2.xmldata.schema_url
> FROM xdb.xdb$schema s2, user$ u2
> WHERE u2.user# = :2
> AND u2.NAME = s.xmldata.schema_owner)
>
> /* Formatted on 2007/10/22 21:44 (Formatter Plus v4.8.8) */
> SELECT *
> FROM (SELECT NULL table_catalog,
> DECODE (owner, 'PUBLIC', NULL, owner) table_schema,
> table_name table_name, column_name column_name,
> NULL column_guid, NULL column_propid,
> column_id ordinal_position, 0 column_hasdefault,
> NULL column_default,
> DECODE (nullable,
> 'N', DECODE (data_type,
> 'BFILE', 128,
> 'BLOB', 136,
> 'CHAR', 24,
> 'CLOB', 136,
> 'DATE', 24,
> 'FLOAT', 24,
> 'LONG', 136,
> 'LONG RAW', 136,
> 'NCHAR', 24,
> 'NCLOB', 136,
> 'NUMBER', 24,
> 'NVARCHAR2', 8,
> 'RAW', 8,
> 'ROWID', 272,
> 'VARCHAR', 8,
> 'VARCHAR2', 8,
> 0
> ),
> DECODE (data_type,
> 'BFILE', 224,
> 'BLOB', 232,
> 'CHAR', 120,
> 'CLOB', 232,
> 'DATE', 120,
> 'FLOAT', 120,
> 'LONG', 232,
> 'LONG RAW', 232,
> 'NCHAR', 120,
> 'NCLOB', 232,
> 'NUMBER', 120,
> 'NVARCHAR2', 104,
> 'RAW', 104,
> 'ROWID', 272,
> 'VARCHAR', 104,
> 'VARCHAR2', 104,
> 0
> )
> ) column_flags,
> DECODE (nullable, 'N', 0, -1) is_nullable,
> DECODE (data_type,
> 'BFILE', 128,
> 'BLOB', 128,
> 'CHAR', 129,
> 'CLOB', 129,
> 'DATE', 135,
> 'FLOAT', 5,
> 'LONG', 129,
> 'LONG RAW', 128,
> 'NCHAR', 130,
> 'NCLOB', 130,
> 'NUMBER', DECODE (data_precision,
> NULL, (DECODE (data_scale,
> NULL, 139,
> 0, 131,
> 139
> )
> ),
> 126, 5,
> 131
> ),
> 'NVARCHAR2', 130,
> 'RAW', 128,
> 'ROWID', 128,
> 'VARCHAR', 129,
> 'VARCHAR2', 129,
> 13
> ) data_type,
> NULL type_guid,
> DECODE (data_type,
> 'BFILE', 2147483647,
> 'BLOB', 2147483647,
> 'CHAR', char_length,
> 'CLOB', 2147483647,
> 'LONG', 2147483647,
> 'LONG RAW', 2147483647,
> 'NCHAR', char_length,
> 'NCLOB', 2147483647,
> 'NVARCHAR2', char_length,
> 'RAW', data_length,
> 'ROWID', data_length,
> 'VARCHAR', char_length,
> 'VARCHAR2', char_length,
> NULL
> ) character_maximum_length,
> DECODE (data_type,
> 'BFILE', 2147483647,
> 'BLOB', 2147483647,
> 'CHAR', data_length,
> 'CLOB', 2147483647,
> 'LONG', 2147483647,
> 'LONG RAW', 2147483647,
> 'NCHAR', data_length,
> 'NCLOB', 2147483647,
> 'NVARCHAR2', data_length,
> 'RAW', data_length,
> 'ROWID', data_length,
> 'VARCHAR', data_length,
> 'VARCHAR2', data_length,
> NULL
> ) character_octet_length,
> DECODE (data_type,
> 'FLOAT', 15,
> 'NUMBER', DECODE (data_precision,
> NULL, DECODE (data_scale,
> NULL, 38,
> 0, 38,
> 38
> ),
> 126, 15,
> data_precision
> ),
> NULL
> ) numeric_precision,
> DECODE (data_type,
> 'NUMBER', data_scale,
> NULL
> ) numeric_scale,
> DECODE (data_type, 'DATE', 0, NULL)
> datetime_precision,
> NULL character_set_catalog, NULL
> character_set_schema,
> NULL character_set_name, NULL collation_catalog,
> NULL collation_schema, NULL collation_name,
> NULL domain_catalog, NULL domain_schema, NULL
> domain_name,
> NULL description
> FROM all_tab_columns) dbschema_columns
> WHERE table_schema = 'test' AND table_name = C_and_f
> ORDER BY 2, 3, 7
Possibly you have run into a bug that I found during the 11g Beta test related to XML schemas ... they may not have existed in 10.1.0.5. If so there is nothing you can do about it until 11gR1 plus one patch. The bug isn't serious but it does cause a few queries to take a long time to complete.

I would suggest you open an SR at metalink and see if they will back port the solution to 10.1 while it is still supported.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Oct 23 2007 - 10:13:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US