Re: Trace Files

Mike wrote:

> I have a report developed using Ora Excel which connects to 25 > databases and populates the report.

I assume that with Ora Excel you refer to using MS Query to embed SQL queries in Excel, Mike?

> When ever we run the report it
> consumes a lot of CPU and all other processes become very slow.

Connecting to 25 databases though.. that is a tall order for Excel. And of course it will make that PC slow down to a crawl. Again I assume that you are refering to the PC running Excel and doing the report - and not to the database servers.

> I want to enable trace for all the databases.The report is run from
> excel.Where do I enable the traceing?

As the other posters said, that will not make a difference. Besides, the problem I suspect is on the Excel client. Not on the 25 Oracle database servers. I sincerely hope each database is on a *separate* server?

The problem is quite likely the SQL queries used in that Ora Excel report of yours. 25 databases... that likely mean multiple queries per database and the collating and joining result sets in Excel.. which is exactly the wrong way of doing it (ideally, the complete report must be done on the powerful server while the client only deals with the formatting and representation of the data in report form).

There's likely some tuning that can be done on the Excel and SQL side.. but I doubt that it will provide the performance improvements you're hoping for. Nor make it any more robust...

The best would be to redesign this properly - doing as much in Oracle (even to the extend of creating one or more small reporting tables) and then have that data pulled down into Excel for formatting and display.

--
Billy