Forum Stats

  • 3,874,727 Users
  • 2,266,768 Discussions
  • 7,911,961 Comments

Discussions

Gather Statistics from Active Data Guard

Franck Pachot
Franck Pachot Member Posts: 912 Bronze Trophy
edited Jan 18, 2019 3:21PM in Database Ideas - Ideas

Hi, Statistics gathering can be a challenge: the window where there's no business activity is quite short. And concurrent statistics gathering can consume resources (I/O and CPU) which competes with online activity. In a Data Guard configuration, the hard job (read, sort, hash) could be done on the standby and statistics updated in the primary. Maybe with the possibility to gather them pending and publish them during a specific .

Franck PachotMathias Zarickfloo_barKnut HärtelSven W.KayKpcpaascheFranky Weber FaustBPeaslandDBAMatthiasRogelconnor_mc_d-OracleArmin EberleEmad Al-MousaDani SchniderPhilipp SalvisbergulohmannAlex.ZaballaMarkus.Michalewicz-Oracle3725482Rodrigo Jorge DBAKazuhiro3493905Andris Perkons-OracleUser_4TRV9Oana Grigorie - Support - Oracle-OracleberxUser_AOUZ43870543Luís Gustavo LiraRajeshwaran, JeyabalUser_PKCXNLavieUser_Y4Q5ZUser_PLTUNsdstuber
35 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    Interesting idea. Statistics also depend on column usage (sys.col_usage$). So to make that work probably such aspects need to be transfered to the standby DB before calculating stats there.

  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    Interesting idea. Statistics also depend on column usage (sys.col_usage$). So to make that work probably such aspects need to be transfered to the standby DB before calculating stats there.

    Hi Swen, COL_USAGE$ is a table, so it can be read from the standby. I think the biggest problem will be with synopsis for incremental statistics.

  • Cool idea.

    I'd go one further, why not allow it on *any* read only standby. Just because a "non-active" dataguard node would be somewhat out of date if opened in readonly mode, it would still be a great source to derive statistics from it.

    I'd also like to see it be possible to done totally isolated from the primary, eg gather into a global temp table on the standby (with temp undo enabled) and then allow exporting of it.

    Franck Pachot
  • connor_mc_d-Oracle
    connor_mc_d-Oracle Posts: 88 Employee
    edited Jan 16, 2019 4:09AM

    Hi Swen, COL_USAGE$ is a table, so it can be read from the standby. I think the biggest problem will be with synopsis for incremental statistics.

    And probably the management of historical information, ie, when we gather stats we are doing DML to keep a record of the previous set of stats. So we need to manage and/or bypass that.

    Franck PachotShirish Reddy
  • Markus.Michalewicz-Oracle
    Markus.Michalewicz-Oracle Member Posts: 237 Employee

    Hello Franck,

         This is a good idea indeed. Will discuss with development and see what we can do.

    Thank you,
            Markus

    Franck Pachot
  • Markus.Michalewicz-Oracle
    Markus.Michalewicz-Oracle Member Posts: 237 Employee
    edited Jan 19, 2019 1:22AM

    Hello Franck,

         This is a good idea indeed. Will discuss with development and see what we can do.

    Thank you,
            Markus

    Hello Franck, all,

          Checked with development as promised.

    The answer was quick, which attests to the fact that the idea is not new and had been discussed between the Data Guard and Optimizer teams before. Bottom line is that the idea is considered a "good idea", but...

    one of the issues with gathering stats (as mentioned before in this discussion) is that it ends up doing some updates. These updates are not 'simple' DML-like updates; instead, they are done using internal components of the RDBMS (KGL and KQR) in such a way that the updates are first performed in the KGL/KQR cache and committed in the dictionary atomically with the cache.

    If it was only for DML, a new Active Data Guard (ADG) DML Redirection feature that is planned to be released with Oracle Database 19c could have helped with this. The part where it becomes tricky is how to somehow sync up the KGL/KQR cache on ADG, which is a hard problem to solve and hence, requires more effort than can be allocated via an Enhancement Request (ER); it requires what we call a project (for the next release).

    That said, as with Oracle Database 19c the foundation for this improvement project is supposed to be available (the DML Redirection feature), we will re-assess what we can do based on the voting here and for a future release. Thus, keep on voting for the idea, please!

    Somewhat unrelated, but in a similar context as stats gathering, we do allow for AWR reports on the standby (not just Statspack). The AWR report is uploaded to the Primary (or any read-write database), as you may know.

    Hope that helps. Thanks,
           Markus

    Franck PachotberxUser_1AR58
  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    Hello Franck, all,

          Checked with development as promised.

    The answer was quick, which attests to the fact that the idea is not new and had been discussed between the Data Guard and Optimizer teams before. Bottom line is that the idea is considered a "good idea", but...

    one of the issues with gathering stats (as mentioned before in this discussion) is that it ends up doing some updates. These updates are not 'simple' DML-like updates; instead, they are done using internal components of the RDBMS (KGL and KQR) in such a way that the updates are first performed in the KGL/KQR cache and committed in the dictionary atomically with the cache.

    If it was only for DML, a new Active Data Guard (ADG) DML Redirection feature that is planned to be released with Oracle Database 19c could have helped with this. The part where it becomes tricky is how to somehow sync up the KGL/KQR cache on ADG, which is a hard problem to solve and hence, requires more effort than can be allocated via an Enhancement Request (ER); it requires what we call a project (for the next release).

    That said, as with Oracle Database 19c the foundation for this improvement project is supposed to be available (the DML Redirection feature), we will re-assess what we can do based on the voting here and for a future release. Thus, keep on voting for the idea, please!

    Somewhat unrelated, but in a similar context as stats gathering, we do allow for AWR reports on the standby (not just Statspack). The AWR report is uploaded to the Primary (or any read-write database), as you may know.

    Hope that helps. Thanks,
           Markus

    Thanks a lot Markus for this quick and comprehensive explanation. With this implementation detail, I understand that it is not as easy as I thought.

    Markus.Michalewicz-OracleUser_1AR58