| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Crystal Reports and the IdiotManager(TM)
Again, the IdiotManager(TM) theme...
I found out late yesterday afternoon that there is a report in Crystal that someone has been repeatedly trying to run - and then killing before it finishes. It will run for 20+ hours and still be only perhaps 50% done. They tried it about 3 or 4 times this week of course, expecting it to somehow "get better". When I found out, I asked for the SQL. It was a bit ridiculous as it attempts to sucks up 4.5 million rows out of a 20 million row table to create a two fairly simple one page bar graphs. There is an index on all the columns in the where clause, but in this case the optimizer made the intelligent choice (verified) of a full table scan. Running it on the DB server took only 10 minutes 51 seconds. Running it from another server via SQL*Net took only slightly longer. I asked the developer to run it again and traced it with the 10046 event level 12. It ran for almost two hours before it was cancelled. Even though it had done little yet, 99.9999999999% of the wait time (almost=elapsed time) was on SQL*Net message from client! It turns out that the client was trying to read the 1.2 gig of data into 256M of memory. The client PC was completely unusable for the duration of course - CPU usage was only about 5% but memory was totally saturated and it was swapping like mad. IdiotManager(TM) had spent most of the last two days in meetings trying to convince executive management that it was a "database problem" and that the company should switch to Postgres or, preferably, flat files - "for performance". (Seriously! For a 300+ GB OLTP/hybrid database with hundreds of tables, a billion records, and >10 million transactions and 20-50 reports per day.) I overheard her coming out of one of these meeting saying that "The problem with Oracle is that you can put the data in, but can't ever get it back". During all this time she never bothered to mention this "database problem" to me or anyone else who might have any sort of clue as to how to actually diagnose anything. She was in an extremely foul mood this morning after I sent out the trace results of the attempt from Crystal, of four test runs using SQL and a very detailed technical analysis of why it was not really a database problem - and cc'ed the executives to whom she has been constantly spewing this sort of nonsense.
I don't know beans about Crystal (and would actually prefer to keep it that way). Does anyone know if it is somehow "tunable" for result sets >> memory? Would 2GB of client memory help significantly (at least temporarily)?. Is this simply a Crystal "scalability ceiling" thing? Does anyone know of Crystal running well against large (10-500 million records) data sets and large (N*million record) result sets? I'm really looking for pointers, experiences, and perhaps even general information to pass to the duhveloper and mangler. The only way I can think of to make this pig (and its cousins) any better is to create materialized views or rollup tables in the database (or better - in another database) and have them change the report so that Crystal hits them and doesn't attempt to read millions of records to find the "Top 20" of this and that grouped by the other thing. I would like to avoid any having a hundred "report-specific" persistent storage objects - all requiring modification every time they change a report (at least daily).
-Don Granaman
OraSaurus - Kinda hungry for a duhvelopment mangler BBQ
 |
 |