Can "parameter sniffing" be an issue when the data source is oracle?

Question

Hi all,

I've got a report whose underlying query runs quickly in toad, but the report is waaaaaay slower. There's not much happening in the report itself. After googling around I saw the parameter-sniffing thing discussed, but only in reference to mssql data sources. Can the same thing happen with oracle as the back end?

Thanks,

sff

Answer 1

Hi There

can you please do some profiling and see what going on one the server when you run the report

many thanks

Syed

Answer 2

Hi sherifffruitfly2,
To troubleshoot the report performance, you can query the “ExecutionLog3” view from the Report Server database.

SELECT TOP 1000 [InstanceName]
      ,[ItemPath]
      ,[UserName]
      ,[ExecutionId]
      ,[RequestType]
      ,[Format]
      ,[Parameters]
      ,[ItemAction]
      ,[TimeStart]
      ,[TimeEnd]
      ,[TimeDataRetrieval]
      ,[TimeProcessing]
      ,[TimeRendering]
      ,[Source]
      ,[Status]
      ,[ByteCount]
      ,[RowCount]
      ,[AdditionalInfo]
  FROM [ReportServer].[dbo].[ExecutionLog3]
  Order by [TimeStart] DESC

We can analyze the TimeDataRetrieval, TimeProcessing and TimeRendering values to troubleshoot performance issue. Here are some articles for your reference, please see:
Troubleshooting Report Performance: http://technet.microsoft.com/en-us/library/bb522806.aspx
Report Server Execution Log and the ExecutionLog3 View: http://technet.microsoft.com/en-us/library/ms159110(v=sql.105).aspx

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Bin Long

TechNet Community Support