13 Replies Latest reply on Jan 8, 2017 10:35 PM by rp0428

    How to measure the time spent for updating the indexes during a DML operation

    user5737343

      Hello

       

      I would like to be able to distinguish the time spent for inserting into an indexed table, and the time spent for updating the associated indexes.

      Can anybody suggest a way to get this information ?

       

      (Trying with and without indexes is not an acceptable solution.)

       

       

      Thanks in advance for any suggestion !

        • 1. Re: How to measure the time spent for updating the indexes during a DML operation
          rp0428
          (Trying with and without indexes is not an acceptable solution.)

          Too bad - you've just ruled out the ONLY POSSIBLE solution.

           

          So unless you tell us why you need to do something that probably no one else even cares about I don't know how anyone is going to be able to help you.

           

          For the life of me I can't imagine how that info will be of any value. Can you explain why you think it is worth knowing?

          • 2. Re: How to measure the time spent for updating the indexes during a DML operation
            John Thorton

            user5737343 wrote:

             

            Hello

             

            I would like to be able to distinguish the time spent for inserting into an indexed table, and the time spent for updating the associated indexes.

            Can anybody suggest a way to get this information ?

             

            (Trying with and without indexes is not an acceptable solution.)

             

             

            Thanks in advance for any suggestion !

            WHY?

             

            What will you do after you have this number?

             

            Having INDEX on table will make DML slower; but having INDEX makes SELECT faster.

            By definition if you want table access by INDEX you accept the cost to slow down DML.

            On some, many, most OLTP applications READs are 10 times (or more) prevalent than WRITEs; so having INDEX is net gain

            • 3. Re: How to measure the time spent for updating the indexes during a DML operation
              Jonathan Lewis

              What degree of precision ?

              As a broad generalisation the main cost of index maintenance that gets noticed is the random reads to find the correct leaf block to update, so enabling extended tracing (10046) with wait states is often sufficient to give a good enough picture. You have to analyze the trace file, of course, to aggregate the wait states for the cursor by obj#

               

              Here's a couple of lines showing access to an undo block then reads of two index blocks:

               

              WAIT #140296542596576: nam='db file sequential read' ela= 649 file#=3 block#=2040 blocks=1 obj#=0 tim=1483653071799538

              WAIT #140296542596576: nam='db file sequential read' ela= 672 file#=5 block#=1540 blocks=1 obj#=250995 tim=1483653071800658

              WAIT #140296542596576: nam='db file sequential read' ela= 693 file#=5 block#=1548 blocks=1 obj#=250995 tim=1483653071801605

               

              If you want to improve precision about work on indexes you'd have to allow for the undo block accesses that seemed to be related to getting read-consistent versions of index leaf blocks before the index maintenance took place.


              Regards

              Jonathan Lewis

              1 person found this helpful
              • 4. Re: How to measure the time spent for updating the indexes during a DML operation
                jgarry

                In addition to what the others said, concurrency issues and whether the segments are already buffered can make for a substantial variance.

                 

                What is it you really want to know?  You can test various scenarios with the wait tracing as Jonathan mentioned, but if you want to track production waits, that's a different barrel of lutefisk.

                • 5. Re: How to measure the time spent for updating the indexes during a DML operation
                  Andrew Sayer

                  Do you have diagnostics pack licence? If so you can join v$active_session_history to v$sql_plan for a particular SQL. You can join from v$ash to dba_objects on current_obj# = object_id to find out the object that was being accessed when the sample happened, if you select the appropriate columns then you can match up with with the actual update step.

                   

                  I'm sure I've written a query somewhere, will update when I find it.

                   

                  -edit

                  It's a little horribly formatted but it's been tinkered with lots over time. I save this whole script in a file and run in using @file_name <SQL_ID>, you might want to change some of it as it probably has some inbuilt assumptions for my environment (a DSS)

                   

                  set lines 500
                  col "Operation" FOR a60
                  break on child_number nodup on "Id" nodup on "Operation" nodup on "Name" nodup on cost nodup on CARDINALITY nodup
                  col child_number noprint
                  col "Id" for 999
                  col event for a30
                  col cost for 9999999
                  col activity for a10
                  col active for a3
                  var sql_id varchar2(30 char);
                  exec :sql_id := '&1'      
                  
                  
                  with ash AS (SELECT sql_id
                        ,sql_plan_hash_value
                        ,SQL_PLAN_LINE_ID
                        ,NVL(event,'CPU') event
                        ,nvl2(event,o.object_name,null) object_name
                        ,COUNT(DISTINCT sample_id) cnt
                        ,ROUND(SUM(ash.delta_read_io_bytes/1024/1024 ),2) Read_MB
                        ,MAX(sample_time) most_recent_time
                  FROM   v$active_session_history ash
                  LEFT JOIN dba_objects o
                    ON   ash.current_obj# = o.object_id
                  WHERE  ash.sql_id = :sql_id
                  GROUP BY sql_id
                        ,sql_plan_hash_value
                        ,SQL_PLAN_LINE_ID
                        ,NVL(event,'CPU')
                        ,nvl2(event,o.object_name,null)
                  ORDER BY 1,2,3,5)
                   ,sp AS (SELECT sp.plan_hash_value
                                 ,id
                                 ,sql_id
                                 ,LPAD(' ',sp.depth,' ')||sp.operation||' '||sp.options "Operation"
                                 ,sp.OBJECT_NAME "Name"
                                 ,max(sp.cardinality) cardinality
                                 ,MAX(sp.io_cost) cost
                                 ,sp.object_name
                          FROM   v$sql_plan sp
                          WHERE  sp.sql_id = :sql_id
                          group by sp.plan_hash_value
                                 ,id
                                 ,sql_id
                                 ,LPAD(' ',sp.depth,' ')||sp.operation||' '||sp.options
                                 ,sp.object_name
                         )      
                   select active
                         ,PLAN_HASH_VALUE
                         ,"Id"
                         ,"Operation"
                         ,"Name"
                         ,cardinality
                         ,cost
                         ,event
                         ,object_name
                         ,activity
                         ,Read_MB
                   From (
                   select CASE WHEN ash.most_recent_time > sysdate-interval'5'second then '*' else ' ' end active
                        ,sp.plan_hash_value
                        ,sp.id "Id"
                        ,sp."Operation"
                        ,sp.object_name "Name"
                        ,sp.cardinality
                        ,sp.cost
                        ,ash.event
                        ,ash.object_name
                        ,DECODE(ash.cnt,NULL,NULL,ash.cnt||' ('||ROUND(100*RATIO_TO_REPORT(ash.cnt) OVER (PARTITION BY sp.plan_hash_value))||'%)') activity
                        ,ash.cnt
                        ,ash.Read_MB
                   from   sp
                  left join   ash
                     on  sp.plan_hash_value = ash.sql_plan_hash_value
                    and sp.id = ash.sql_plan_line_id
                    and sp.sql_id = ash.sql_id
                   order by sp.plan_hash_value,sp.id, ash.cnt
                   )
                   order by plan_hash_value, "Id" , cnt
                  /
                  
                  
                  
                  1 person found this helpful
                  • 6. Re: How to measure the time spent for updating the indexes during a DML operation
                    Hemant K Chitale

                    Wait stats would report index leaf blocks that are not already present in the buffer cache.  Many-a-times, the leaf blocks are in the buffer cache and/or multiple rows go into the same leaf blocks so the 'db sequential read' wait doesn't report them.

                    Could V$SEGSTAT / V$SEGMENT_STATISTICS be useful  subject to there being no other session accessing/updating the same indexes ?

                     

                     

                    Hemant K Chitale

                    1 person found this helpful
                    • 7. Re: How to measure the time spent for updating the indexes during a DML operation
                      Stefan Koehler

                      Hi,

                       

                      > Can anybody suggest a way to get this information ?

                      Well, as Jonathan already pointed out - it is a matter of  your needed precision. You already got the answers for a rough estimation and here is the answer for getting it in detail.

                       

                      Profile the application - you just need to figure out which code path is related to the table and which code path is related to the index. After that you can measure the time (e.g. with DTrace or SystemTap) in the specific C code tree. Remember - Oracle is (mainly) just a C program - so you can use the usual techniques to measure anything :-)

                       

                      Best Regards

                      Stefan Koehler

                      Freelance Oracle performance consultant and researcher

                      • 8. Re: How to measure the time spent for updating the indexes during a DML operation
                        Franck Pachot

                        Hi Richard ,

                         

                        As suggested by Hemant, segments statistics may give an idea of logical reads, physical reads and block changes:

                         

                        SQL> create table DEMO as select rownum a, rownum b, rownum c, rownum d from xmltable('1 to 1000');

                        Table created.

                        SQL> create index DEMOA on DEMO(a);

                        Index created.

                        SQL> create index DEMOB on DEMO(b);

                        Index created.

                        SQL> create index DEMOC on DEMO(c);

                        Index created.

                        SQL> create index DEMOD on DEMO(d);

                        Index created.

                        SQL> create table SEGSTAT as select current_timestamp ts, v.* from V$SEGMENT_STATISTICS v where owner='DEMO' and OBJECT_NAME in ('DEMO','DEMOA','DEMOB','DEMOC','DEMOD') and statistic_name not like '%space%';

                        Table created.

                        SQL> save .tmp.sql replace

                        Wrote file .tmp.sql

                        SQL>

                        SQL> set timing on autotrace on

                        SQL> update DEMO set a=a+1, b=b-1, c=c/3, d=-d;

                        1000 rows updated.

                        Elapsed: 00:00:00.06

                        Execution Plan

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

                        Plan hash value: 1805557832

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

                        | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

                        |   0 | UPDATE STATEMENT   |      |  1000 | 16000 |     3   (0)| 00:00:01 |

                        |   1 |  UPDATE            | DEMO |       |       |            |          |

                        |   2 |   TABLE ACCESS FULL| DEMO |  1000 | 16000 |     3   (0)| 00:00:01 |

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

                        Statistics

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

                                 14  recursive calls

                              19493  db block gets

                                702  consistent gets

                                 12  physical reads

                            2663600  redo size

                                859  bytes sent via SQL*Net to client

                                962  bytes received via SQL*Net from client

                                  3  SQL*Net roundtrips to/from client

                                  2  sorts (memory)

                                  0  sorts (disk)

                               1000  rows processed

                        SQL> set timing off autotrace off

                        SQL>

                        SQL> get .tmp.sql

                          1* create table SEGSTAT as select current_timestamp ts, v.* from V$SEGMENT_STATISTICS v where owner='DEMO' and OBJECT_NAME in ('DEMO','DEMOA','DEMOB','DEMOC','DEMOD') and statistic_name not like '%space%'

                        SQL> c/create table/insert into/

                          1* insert into SEGSTAT as select current_timestamp ts, v.* from V$SEGMENT_STATISTICS v where owner='DEMO' and OBJECT_NAME in ('DEMO','DEMOA','DEMOB','DEMOC','DEMOD') and statistic_name not like '%space%'

                        SQL> c/as select/select/

                          1* insert into SEGSTAT select current_timestamp ts, v.* from V$SEGMENT_STATISTICS v where owner='DEMO' and OBJECT_NAME in ('DEMO','DEMOA','DEMOB','DEMOC','DEMOD') and statistic_name not like '%space%'

                        SQL> /

                         

                        SQL> select statistic_name,object_type,object_name,sum(value)

                          2  from (select object_name,object_type,statistic_name,value-lag(value)over(partition by ts#,obj#,dataobj#,statistic# order by ts) value from SEGSTAT) where value>0

                          3  group by rollup(statistic_name,object_type,object_name)

                          4  order by statistic_name nulls last,object_type desc nulls last,object_name

                          5  ;

                         

                        STATISTIC_NAME            OBJECT_TYP OBJECT_NAM SUM(VALUE)

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

                        db block changes          TABLE      DEMO             1360

                        db block changes          TABLE                       1360

                        db block changes          INDEX      DEMOA            2048

                        db block changes          INDEX      DEMOB            2096

                        db block changes          INDEX      DEMOC            2112

                        db block changes          INDEX      DEMOD            2416

                        db block changes          INDEX                       8672

                        db block changes                                     10032

                        logical reads             TABLE      DEMO             2848

                        logical reads             TABLE                       2848

                        logical reads             INDEX      DEMOA            4112

                        logical reads             INDEX      DEMOB            4352

                        logical reads             INDEX      DEMOC            4176

                        logical reads             INDEX      DEMOD            4112

                        logical reads             INDEX                      16752

                        logical reads                                        19600

                        physical read requests    INDEX      DEMOA               3

                        physical read requests    INDEX      DEMOB               3

                        physical read requests    INDEX      DEMOC               3

                        physical read requests    INDEX      DEMOD               2

                        physical read requests    INDEX                         11

                        physical read requests                                  11

                        physical reads            INDEX      DEMOA               3

                        physical reads            INDEX      DEMOB               3

                        physical reads            INDEX      DEMOC               3

                        physical reads            INDEX      DEMOD               2

                        physical reads            INDEX                         11

                        physical reads                                          11

                                                                             29654

                         

                        Regards,

                        Franck

                        1 person found this helpful
                        • 9. Re: How to measure the time spent for updating the indexes during a DML operation
                          Jonathan Lewis

                          I've done a little write-up of this type of requirement for  All Things Oracle - Content for Oracle Developers and DBAs  Probably going to be published in the next couple of days

                           

                          Regards

                          Jonathan Lewis

                          • 11. Re: How to measure the time spent for updating the indexes during a DML operation
                            user5737343

                            Thank you very much for all suggestions, scripts and demos !

                            I will have to play a bit with these solutions...

                             

                            For those asking for the utility of such a figure :

                            - indexing is a permanent trade-off between its benefits and its disadvantages, so we need to measure the cost of the index maintenance

                            - in SQL Server, this information (cost of the index maintenance) is available with no effort in a basic execution plan

                            - we could imagine a view providing this information. For example, as we have USER_IO_WAIT_TIME in v$sqlarea, we could have something like INDEX_MAINTENANCE_TIME.

                             

                            Thank you very much again !

                            • 12. Re: How to measure the time spent for updating the indexes during a DML operation
                              John Thorton

                              user5737343 wrote:

                               

                              Thank you very much for all suggestions, scripts and demos !

                              I will have to play a bit with these solutions...

                               

                              For those asking for the utility of such a figure :

                              - indexing is a permanent trade-off between its benefits and its disadvantages, so we need to measure the cost of the index maintenance

                              - in SQL Server, this information (cost of the index maintenance) is available with no effort in a basic execution plan

                              - we could imagine a view providing this information. For example, as we have USER_IO_WAIT_TIME in v$sqlarea, we could have something like INDEX_MAINTENANCE_TIME.

                               

                              Thank you very much again !

                              post formula used to decide whether index maintenance cost exceeds the index value since populating index is a 1 time event while the index can benefit SELECT many times a  day into the infinite future.

                              • 13. Re: How to measure the time spent for updating the indexes during a DML operation
                                rp0428

                                For those asking for the utility of such a figure :

                                indexing is a permanent trade-off between its benefits and its
                                disadvantages, so we need to measure the cost of the index maintenance

                                - in SQL Server, this information (cost of the index maintenance) is available with no effort in a basic execution plan

                                Ok - but NONE of that explains the 'utility'. And it misses a key piece of info.

                                 

                                Indexes are a 'solution' to a problem. If you don't have a problem you don't need an index. You should not just be adding indexes because you think it might help. You add an index because you KNOW it will help.

                                 

                                And the trade-off is generally between making one query (or queries) perform better at the expense of making other queries perhaps perform worse.

                                 

                                If the queries and apps perform within their SLA (service level agreement) then you have no problem to solve no matter how long it is taking to maintain indexes.

                                 

                                So that is what made this initial statement of yours rather puzzling:

                                (Trying with and without indexes is not an acceptable solution.)

                                That is how you determine if an index is useful in solving a problem. You do tests with, and without, the index.

                                 

                                The way your question/issue is worded you make is sound like if the index maintenance time becomes too high you will just eliminate that index to avoid having that happen.

                                 

                                Hence the questions John keeps asking about what you plan to do with the info once you have it. Because, frankly, I can't think of much that would be useful in terms of solving a problem on one of my clients systems.

                                 

                                Now do you see why we are asking? It isn't to be obstinate - it is to try to understand WHAT PROBLEM you are are really trying to solve.

                                 

                                I can't recall any place I've worked where decisions about an index were based on what the maintenance time was. They were always based on the impact on the positive performance of a query/report or the negative performance of other queries/reports if the index made Oracle start using a different plan than before.

                                 

                                So can you provide some SPECIFIC examples of how you actually plan to use the info?

                                 

                                1. A test shows index maintenance is taking X minutes

                                2. Therefore we plan to eliminate that index

                                 

                                I mean, give us SOMETHING!