Hi Shashank,

Check the DDL of the tables in DEV and Prod, check whether the primary index is same on both the tables.

Check any collect stats applied on Dev tables and not in Prod.

ex: help stats databasename.tablename;

Thanks,

Karthik. N

 Hi Shashank,

One more suggestion from me is we couldn’t expect same performance in both dev and prod. Because in prod lot of batch jobs also will run in parallel.

But we can improve performance based on stats & primary index like wat karthik told.

Thanks,

Prem.

Hi Shashank,

DBQL is the query log that captures CPU and IO consumption for queries.   These numbers depend only on the query itself (DDL, stats, data), and do not depend on the batch jobs that run in parallel.    

Regards,

Vlad.

Shashank,

Like others have said, could you check if you are also running other batch jobs on while executing these queries on Production. In that case, check the resource utilization ( CPU, Memory, I/O).

Also are you refreshing the stats on the tables in Production. If you are using stale stats, it will hit the performance.

Regards

VikR

As said before - first check DBQL to see if resource consumption is different. If yes, stats, index choice etc. should be checked.

If no - check the TSAM settings for the workload this reports are running in.

Try running the reports in user window i.e out of critical batch window in production..Also check for the workload this reports are running in....

Stalin