This discussion is archived
6 Replies Latest reply: Nov 20, 2012 1:11 AM by 668758 RSS

How to tune SQL queries for a standby  database?

668758 Newbie
Currently Being Moderated
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 ?
  • 1. Re: How to tune SQL queries for a standby  database?
    mseberg Guru
    Currently Being Moderated
    Hello;

    I f I understand the question correctly there's a white paper on this :

    Oracle Active Data Guard Oracle Data Guard 11g

    http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr1-activedataguard-1-128199.pdf

    See- APPENDIX D: TUNING QUERIES ON THE STANDBY DATABASE

    Also

    SQL Profile on an Active Data Guard Standby is not the same as on the primary database [ID 1264838.1]

    Best Regards

    mseberg
  • 2. Re: How to tune SQL queries for a standby  database?
    CKPT Guru
    Currently Being Moderated
    HiteshBhardwaj wrote:
    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 ?
    Hitesh,

    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.
    HTH.
  • 3. Re: How to tune SQL queries for a standby  database?
    user296828 Expert
    Currently Being Moderated
    What kind of standby database is it?

    Physical/Logical??
  • 4. Re: How to tune SQL queries for a standby  database?
    668758 Newbie
    Currently Being Moderated
    CKPT wrote:

    Hitesh,

    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.
    HTH.
    Hi CKPT,


    If we have every configurations same for Primary and secondary, there will be different execution time depending upon the Load on the respective DB.

    If we are using primary for data insert and update -- no reporting and secondary for reporting only - update and insert. Queries set will be different on both DB's so tuning efforts should also be different for both DB's. you can tune primary, but how can you secondary for different sets of queries and workload.

    Edited by: HiteshBhardwaj on Nov 20, 2012 1:12 AM
  • 5. Re: How to tune SQL queries for a standby  database?
    668758 Newbie
    Currently Being Moderated
    11g wrote:
    What kind of standby database is it?

    Physical/Logical??
    Physical standby.

    Edited by: HiteshBhardwaj on Nov 20, 2012 1:11 AM
  • 6. Re: How to tune SQL queries for a standby  database?
    668758 Newbie
    Currently Being Moderated
    mseberg wrote:
    Hello;

    I f I understand the question correctly there's a white paper on this :

    Oracle Active Data Guard Oracle Data Guard 11g

    http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr1-activedataguard-1-128199.pdf

    See- APPENDIX D: TUNING QUERIES ON THE STANDBY DATABASE

    Also

    SQL Profile on an Active Data Guard Standby is not the same as on the primary database [ID 1264838.1]

    Best Regards

    mseberg
    Hi,


    I have tried ashrptistd but getting following errors :


    SQL> @?/rdbms/admin/ashrptistd.sql

    You are running ASH report on a Standby database. To generate the report
    over data sampled on the Primary database, enter 'P'.
    Defaults to 'S' - data sampled in the Standby database.
    Enter value for stdbyflag: S
    old 2: (case when '&&stdbyflag' IS NULL
    new 2: (case when 'S' IS NULL
    old 4: else '&&stdbyflag' end) as src
    new 4: else 'S' end) as src

    Using Primary (P) or Standby (S): S

    old 2: select decode('&&stdbyflag', 'S', 1, 'P', 2, 1) into :stdby_flag from dual;
    new 2: select decode('S', 'S', 1, 'P', 2, 1) into :stdby_flag from dual;
    SP2-0552: Bind variable "STDBY_FLAG" not declared.
    SQL>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points