7 Replies Latest reply: Sep 4, 2013 9:09 AM by 1003886 RSS

    Can you monitor indexes being used by queries on a read-only standby database?

    1003886

      I can query the index monitoring Views when on the standby but It is not registering that I have used the index (on the standby)

       

      --check monitoring

      SELECT io.name  INDEX_NAME,

                t.name TABLE_NAME,

                DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') MONITORING,

                DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES') USED,

                ou.start_monitoring START_MONITORING,

                ou.end_monitoring END_MONITORING

                ,ou.flags

           FROM sys.obj$ io,

                sys.obj$ t,

                sys.ind$ i,

                sys.object_usage ou,

                dba_users u

          WHERE i.obj# = ou.obj# AND io.obj# = ou.obj# AND t.obj# = i.bo# and t.owner# = u.user_id

          and io.name = 'XIF1PHT';

       

      XIF1PHTMFST_RQSTYESNO07/09/2013 10:52:09

      0

      --This is monitored and not used

       

      --use the index (run on the standby)

      select /*+index(mr XIF1PHT)*/ * from HERMES_MI_STAGE.MFST_RQST mr where MFST_DTE = sysdate;

       

      --check monitoring again

      SELECT io.name  INDEX_NAME,

                t.name TABLE_NAME,

                DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') MONITORING,

                DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES') USED,

                ou.start_monitoring START_MONITORING,

                ou.end_monitoring END_MONITORING

                ,ou.flags

           FROM sys.obj$ io,

                sys.obj$ t,

                sys.ind$ i,

                sys.object_usage ou,

                dba_users u

          WHERE i.obj# = ou.obj# AND io.obj# = ou.obj# AND t.obj# = i.bo# and t.owner# = u.user_id

          and io.name = 'XIF1PHT';

       

      XIF1PHTMFST_RQSTYESNO07/09/2013 10:52:090

      --Still monitored and not used!

       

      Note: I started the monitorng of this index on the Primary

        • 1. Re: Can you monitor indexes being used by queries on a read-only standby database?
          mseberg

          Hello;

           

          I would have to test to know. But I might try an EXPLAIN_PLAN.

           

          Best Regards

           

          mseberg

          • 2. Re: Can you monitor indexes being used by queries on a read-only standby database?
            1003886

            I checked that the index would be used by the query with an explain plan on the Primary.

             

            Unfortunately it is not possible to check on the Standby as it requires a write to the PLAN_TABLE and it is in Read-Only mode.

            • 3. Re: Can you monitor indexes being used by queries on a read-only standby database?
              FreddieEssex

              Run the following and it will create a trace for your session with the explain plan:

               

              alter session set events '10053 trace name context forever, level 2';
              
              • 4. Re: Can you monitor indexes being used by queries on a read-only standby database?
                1003886

                Thanks Freddie.

                 

                I can now prove that the index was used on the Standby db, but that the index monitoring has not picked it up.

                 

                10053 TRACE OUTPUT  (on RO Standby):

                ----- Current SQL Statement for this session (sql_id=cs6mpt49tn03g) -----

                select /*+index(mr XIF1PHT)*/

                * from HERMES_MI_STAGE.MFST_RQST mr where MFST_DTE = sysdate

                sql_text_length=91

                sql=select /*+index(mr XIF1PHT)*/

                * from HERMES_MI_STAGE.MFST_RQST mr where MFST_DTE = sysdate

                ----- Explain Plan Dump -----

                ----- Plan Table -----

                 

                 

                ============

                Plan Table

                ============

                -------------------------------------------------------+-----------------------------------+---------------+

                | Id  | Operation                           | Name     | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |

                -------------------------------------------------------+-----------------------------------+---------------+

                | 0   | SELECT STATEMENT                    |          |       |       |     7 |           |       |       |

                | 1   |  PARTITION RANGE SINGLE             |          |   338 |   39K |     7 |  00:00:01 | KEY   | KEY   |

                | 2   |   TABLE ACCESS BY LOCAL INDEX ROWID | MFST_RQST|   338 |   39K |     7 |  00:00:01 | KEY   | KEY   |

                | 3   |   INDEX RANGE SCAN                 | XIF1PHT |    12 |       |     2 |  00:00:01 | KEY   | KEY   |

                -------------------------------------------------------+-----------------------------------+---------------+

                 

                --CHECK INDEX MONITORING (on RO Standby)

                SELECT io.name  INDEX_NAME,

                          t.name TABLE_NAME,

                          DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') MONITORING,

                          DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES') USED,

                          ou.start_monitoring START_MONITORING,

                          ou.end_monitoring END_MONITORING

                          ,ou.flags

                     FROM sys.obj$ io,

                          sys.obj$ t,

                          sys.ind$ i,

                          sys.object_usage ou,

                          dba_users u

                    WHERE i.obj# = ou.obj# AND io.obj# = ou.obj# AND t.obj# = i.bo# and t.owner# = u.user_id

                    and io.name = 'XIF1PHT';

                 

                INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING           FLAGS

                ------------------------------ ------------------------------ --- --- ------------------- ------------------- ----------

                XIF1PHT                        MFST_RQST                      YES NO 07/09/2013 10:52:09                              0

                 

                --STILL SAYING NOT USED

                • 5. Re: Can you monitor indexes being used by queries on a read-only standby database?
                  mseberg

                  Hello again;

                   

                  Since its a Standby I would only v$ views. No promises as to results but something like this:

                   

                  SELECT index_name,

                    table_name,

                    monitoring,

                    used,

                    start_monitoring,

                    end_monitoring

                  FROM v$object_usage

                  WHERE index_name = 'XIF1PHT'

                  ORDER BY index_name;

                   

                  Best Regards

                   

                  mseberg

                  • 6. Re: Can you monitor indexes being used by queries on a read-only standby database?
                    1003886

                    Hello mseberg

                     

                    The query I used is based on the v$object_usage View. I just added the join to dba_users so I could plug in the usernames.

                    Unfortunately either way it shows that the index as not being used when the query is run on the RO Standby.

                     

                    I think I should raise an SR to ask if index monitoring is possible in this Senario.

                     

                    Thanks

                    Mark

                     

                    • 7. Re: Can you monitor indexes being used by queries on a read-only standby database?
                      1003886

                      The SR came back with a blank, however this might work:

                       

                      --create a db_link to the standby database

                        CREATE public DATABASE LINK getfromstandby

                       

                      --create a table containing info from Standby's SQL_Plan View 

                      create table indexes_used_on_standby_db as

                      select distinct p.object#,o.owner,o.object_name

                      from dba_objects o, V$SQL_PLAN@getfromstandby p

                            where  o.object_id =  p.object#

                            and p.operation in  ('INDEX','BITMAP INDEX')

                            and p.object_owner in ('INDEX_OWNER');

                       

                      --test the job to update this table

                      BEGIN     

                        INSERT INTO indexes_used_on_standby_db

                        SELECT p.object#, o.owner, o.object_name

                        FROM dba_objects o, V$SQL_PLAN@getfromstandby p

                        WHERE o.object_id = p.object#

                        AND p.operation in  ('INDEX','BITMAP INDEX')

                        AND p.object_owner in ('INDEX_OWNER')

                          MINUS

                        SELECT *

                        FROM indexes_used_on_standby_db; 

                      COMMIT;

                      END;

                      /

                       

                      --schedule the job (every 13 minutes for example)

                       

                      --see the indexes that have been used by your read-only standby

                      select * from indexes_used_on_standby_db;

                       

                      -Mark