We know and perform lots of tuning stuff on our databases, mainly on primary side.
but the set of queries running on standby databases (like to use for reporting purpose) are totally different from one running at primary.
so How can we tune out our Standby DB to perform well ?
CKPT wrote:Hi CKPT,
Can you describe your question more in detail?
First thing is standby is always image copy of primary database, if you even perform gather stats the redo will be generated and the redo will be applied on standby database. So there will be execution plan will be same. I never tested it but as a concept of standby how it works.
besides if any report is running on primary for 2 minutes, and the same query running on standby for 5 minutes or 10 minutes.
You have to see
1) whether SGA settings are same?
2) how is DISK I/O, How storage is responsing?
3) Whether hardware, cores, processors are same?
If these all are same as primary and then i dont think you may have any performance issues for Read-Only queries. Of course for first time may be Hard parsing will be taken place which can cause Physical I/O's. If it is daily running report for several times a day, then if your buffer cache is capable of such data then of course you can have good response time.
So it depends on configurations also.
I f I understand the question correctly there's a white paper on this :
Oracle Active Data Guard Oracle Data Guard 11g
See- APPENDIX D: TUNING QUERIES ON THE STANDBY DATABASE
SQL Profile on an Active Data Guard Standby is not the same as on the primary database [ID 1264838.1]