Database Performance problem..
Dear DBAs,
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.
Any help will be appreciated.
Best Regards,
Bibhudatta.