I have a production database where I am running a process which consists of many shells/batches.
Please donít bother too much about the batches as individually every batch just performs some action(DML) on the database tables by calling some functions/packages/procedures.
& there are as many as 60 shells running one after another.
The problem is that the entire process is taking so much of time..almost 8 hours..
But I have another performance database which is just a replication of the production database. Means if I have to test something on today then I would take the mirror image of the Production database till yesterday EOD.
Now if I am running the same process (shells) in the performance database then the entire process is taking as much as Half of the total time that is being consumed in the Production database Ösomething around 4 hours 15 mins.
Even the Hardware configuration of the Production database is much much better than the performance database too.
I really cant figure out as to why this is happening & how there can be such a big time difference for the execution of the same thing while both the databases are almost similar ????
Can anybody kindly suggest what can be the difference between the two databases ?? like, which parameters can I check in both the databases to iron out this problem..or what can basically go wrong with the production database ??
P.S It will not be possible for me to rebuild the Statistics of the Production Database as my seniors will not allow for the same.
Thanx for the reply.
well its not all the shells that are running slow..there are around 15 shells which are really taking a lot of time to complete..
The problem is , i have compared both of the two databases & found that almost all the database parameters are nearly similar. But still the production database is much slower than the Performance database.
There are many possibilities for that.
May be in production database there is more traffic when comparing to ur test db. May be the data is scatterd on production database. May be there are lot of reloads in shared pool area and likewise. The best solution is to tune your database then drill down to ur application.