1 2 Previous Next 20 Replies Latest reply on Aug 20, 2018 11:09 AM by Jonathan Lewis

    Query performance Issue

    933257

      We are using version 11.2.0.4 of oracle. We have a query running in ~30 seconds in one database(DB1) and the same query is taking ~5minutes in another database(DB2) with data volume and all other configuration remain same in both the databases. i have captured the sql monitor and the session stats for both the databases. I was checking the table/column/index stats and i see from the statistics of the table from both the databases there exists non zero chain_count in one of the databases(DB2), wondering if that is the reason which is causing this query running slow, btw i don't see significant different in "table fetch continued row" value of the both the session stats, so wanted to understand the actual cause of slowness?

       

      SELECT c1, c2, c3, c4, c5, c6, c7, c8..
      FROM TAB1
      WHERE STS IN ( 'A', 'B')
      AND cnt < '4'
      AND dt < sysdate
      and rownum <=1;
      
      Sql_monitor and stats from DB1
      ******************************
      
      Global Information
      ------------------------------
       STS              :  DONE (ALL ROWS)           
       Instance ID         :  1                         
       Execution Started   :  08/17/2018 08:31:22       
       First Refresh Time  :  08/17/2018 08:31:22       
       Last Refresh Time   :  08/17/2018 08:31:53       
       Duration            :  31s                       
       Program             :  sqlplus.exe               
       Fetch Calls         :  1                         
      
      Global Stats
      ===============================================================================
      | Elapsed |   Cpu   |    IO    | Concurrency | Fetch | Buffer | Read  | Read  |
      | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Calls |  Gets  | Reqs  | Bytes |
      ===============================================================================
      |      33 |    3.00 |       30 |        0.08 |     1 |   102K | 38571 | 301MB |
      ===============================================================================
      
      SQL Plan Monitoring Details (Plan Hash Value=715774357)
      ======================================================================================================================================================================================
      | Id |            Operation            |          Name           |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read  | Read  | Activity |       Activity Detail        |
      |    |                                 |                         | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes |   (%)    |         (# samples)          |
      ======================================================================================================================================================================================
      |  0 | SELECT STATEMENT                |                         |         |       |         1 |    +31 |     1 |        1 |       |       |          |                              |
      |  1 |   COUNT STOPKEY                 |                         |         |       |         1 |    +31 |     1 |        1 |       |       |          |                              |
      |  2 |    INLIST ITERATOR              |                         |         |       |         1 |    +31 |     1 |        1 |       |       |          |                              |
      |  3 |     TABLE ACCESS BY INDEX ROWID | TAB1                    |    114K | 33399 |        32 |     +0 |     2 |        1 | 38377 | 300MB |    96.77 | Cpu (1)                      |
      |    |                                 |                         |         |       |           |        |       |          |       |       |          | db file sequential read (16) |
      |    |                                 |                         |         |       |           |        |       |          |       |       |          | read by other session (13)   |
      |  4 |      INDEX RANGE SCAN           | TAB1_STS_IDX            |    115K |   723 |        30 |     +2 |     2 |     118K |   194 |   2MB |     3.23 | read by other session (1)    |
      ======================================================================================================================================================================================
      
      Elapsed: 00:00:31.62
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 715774357
      
      ---------------------------------------------------------------------------------------------------------
      | Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)| Time  |
      ---------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |                         |     1 |  1847 | 33399   (1)| 00:03:14 |
      |*  1 |  COUNT STOPKEY                |                         |       |       |            |       |
      |   2 |   INLIST ITERATOR             |                         |       |       |            |       |
      |*  3 |    TABLE ACCESS BY INDEX ROWID| TAB1                    |   114K|   201M| 33399   (1)| 00:03:14 |
      |*  4 |     INDEX RANGE SCAN          | TAB1_STS_IDX            |   114K|       |   723   (1)| 00:00:05 |
      ---------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter(ROWNUM<=1)
         3 - filter("cnt"<'4' AND "dt"<SYSDATE@!)
         4 - access("STS"='A' OR "STS"='B')
      
      
      Statistics
      ----------------------------------------------------------
                1  recursive calls
                0  db block gets
           102354  consistent gets
            38573  physical reads
                0  redo size
             1649  bytes sent via SQL*Net to client
              785  bytes received via SQL*Net from client
                5  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed
                
      
      
      Table stats:- 
      table_name,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len           
      TAB1       79654925    22416917    0        0          1847        
      
      column_stats(STS):-
      table_name, column_name, num_distinct,num_nulls,density, avg_col_len
      TAB1        STS             5              0           6.2049410678782E-9    2  
      
      Index_stats(on STS):- 
      index_name,leaf_blocks,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key, clustering_factor,num_rows
      TAB1_STS_IDX    487939    5            97587                     4458874                  22294372            78308939
      
      Session Stats:-
      
      process last non-idle time    1534508966
      session connect time    1534508966
      logical read bytes from cache    839663616
      cell physical IO interconnect bytes    316055552
      physical read bytes    316055552
      physical read total bytes    316055552
      file io wait time    17044083
      session pga memory    8643880
      session pga memory max    8643880
      temp space allocated (bytes)    4194304
      session uga memory    1755696
      session uga memory max    1755696
      buffer is pinned count    135743
      table fetch by rowid    117519
      non-idle wait count    107301
      session logical reads    102500
      consistent gets    102450
      consistent gets from cache    102448
      no work - consistent read gets    102368
      buffer is not pinned count    101741
      free buffer inspected    43458
      free buffer requested    38592
      physical read total IO requests    38581
      physical read IO requests    38581
      physical reads    38581
      physical reads cache    38579
      hot buffers moved to head of LRU    37258
      bytes sent via SQL*Net to client    7370
      bytes received via SQL*Net from client    6869
      redo size    5536
      undo change vector size    4432
      DB time    3166
      non-idle wait time    2962
      user I/O wait time    2954
      table fetch continued row    2423
      

       

       

      Sql_monitor and stats from DB2
      ******************************          
      
      Global Information
      ------------------------------
       STS              :  DONE (ALL ROWS)         
       Instance ID         :  1                       
       Execution Started   :  08/17/2018 08:21:47     
       First Refresh Time  :  08/17/2018 08:21:47     
       Last Refresh Time   :  08/17/2018 08:26:29     
       Duration            :  282s                    
       Module/Action       :  SQL*Plus/-              
       Program             :  sqlplus.exe             
       Fetch Calls         :  1                       
      
      Global Stats
      ================================================================
      | Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
      | Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
      ================================================================
      |     287 |    8.76 |      278 |     1 |   110K | 110K | 858MB |
      ================================================================
      
      SQL Plan Monitoring Details (Plan Hash Value=715774357)
      ======================================================================================================================================================================================
      | Id |            Operation            |          Name           |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |        Activity Detail        |
      |    |                                 |                         | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |          (# samples)          |
      ======================================================================================================================================================================================
      |  0 | SELECT STATEMENT                |                         |         |       |         1 |   +282 |     1 |        1 |      |       |          |                               |
      |  1 |   COUNT STOPKEY                 |                         |         |       |         1 |   +282 |     1 |        1 |      |       |          |                               |
      |  2 |    INLIST ITERATOR              |                         |         |       |         1 |   +282 |     1 |        1 |      |       |          |                               |
      |  3 |     TABLE ACCESS BY INDEX ROWID | TAB1                    |    142K | 40211 |       282 |     +1 |     2 |        1 | 109K | 854MB |   100.00 | db file sequential read (277) |
      |  4 |      INDEX RANGE SCAN           | TAB1_STS_IDX            |    142K |   892 |       280 |     +3 |     2 |     118K |  491 |   4MB |          |                               |
      ======================================================================================================================================================================================
      
      
      Elapsed: 00:04:42.34
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 715774357
      
      ---------------------------------------------------------------------------------------------------------
      | Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)| Time  |
      ---------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |                         |     1 |  1847 | 40211   (1)| 00:08:03 |
      |*  1 |  COUNT STOPKEY                |                         |       |       |            |       |
      |   2 |   INLIST ITERATOR             |                         |       |       |            |       |
      |*  3 |    TABLE ACCESS BY INDEX ROWID| TAB1                    |   141K|   249M| 40211   (1)| 00:08:03 |
      |*  4 |     INDEX RANGE SCAN          | TAB1_STS_IDX            |   141K|       |   892   (1)| 00:00:11 |
      ---------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter(ROWNUM<=1)
         3 - filter("cnt"<'4' AND "dt"<SYSDATE@!)
         4 - access("STS"='A' OR "STS"='B')
      
      
      Statistics
      ----------------------------------------------------------
                1  recursive calls
                0  db block gets
           110182  consistent gets
           109818  physical reads
                0  redo size
             9177  bytes sent via SQL*Net to client
             4547  bytes received via SQL*Net from client
                5  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed
                
                
      Table stats:- 
      table_name,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len           
      TAB1    79447350    22318667    710    537597    1847
      
      column_stats(STS):-
      table_name, column_name, num_distinct,num_nulls,density, avg_col_len
      TAB1    STS    5    0    6.17895322507497E-9    2
      
      Index_stats(on STS):- 
      index_name,leaf_blocks,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key, clustering_factor,num_rows
      TAB1_STS_IDX    493152    5             98630                     4382625                  21913127            79106263
      
      Session Stats:-
      
      process last non-idle time    1534508200
      session connect time    1534508200
      logical read bytes from cache    903790592
      physical read total bytes    899629056
      cell physical IO interconnect bytes    899629056
      physical read bytes    899629056
      file io wait time    277881742
      session pga memory    8586744
      session pga memory max    8586744
      temp space allocated (bytes)    4194304
      session uga memory max    1690184
      session uga memory    1690184
      buffer is pinned count    129148
      table fetch by rowid    117521
      session logical reads    110326
      consistent gets    110276
      consistent gets from cache    110276
      no work - consistent read gets    110071
      non-idle wait count    109879
      free buffer requested    109830
      physical read IO requests    109818
      physical reads cache    109818
      physical reads    109818
      physical read total IO requests    109818
      buffer is not pinned count    109577
      free buffer inspected    70740
      hot buffers moved to head of LRU    31910
      DB time    28203
      non-idle wait time    27788
      user I/O wait time    27788
      dirty buffers inspected    19067
      bytes sent via SQL*Net to client    14927
      bytes received via SQL*Net from client    10607
      redo size    5440
      undo change vector size    4432
      table fetch continued row    3660
      
        • 1. Re: Query performance Issue
          John Thorton

          933257 wrote:

           

          We are using version 11.2.0.4 of oracle. We have a query running in ~30 seconds in one database(DB1) and the same query is taking ~5minutes in another database(DB2) with data volume and all other configuration remain same in both the databases. i have captured the sql monitor and the session stats for both the databases. I was checking the table/column/index stats and i see from the statistics of the table from both the databases there exists non zero chain_count in one of the databases(DB2), wondering if that is the reason which is causing this query running slow, btw i don't see significant different in "table fetch continued row" value of the both the session stats, so wanted to understand the actual cause of slowness?

           

          1. SELECTc1,c2,c3,c4,c5,c6,c7,c8..
          2. FROMTAB1
          3. WHERESTSIN('A','B')
          4. ANDcnt<'4'
          5. ANDdt<sysdate
          6. andrownum<=1;
          7. Sql_monitorandstatsfromDB1
          8. ******************************
          9. GlobalInformation
          10. ------------------------------
          11. STS:DONE(ALLROWS)
          12. InstanceID:1
          13. ExecutionStarted:08/17/201808:31:22
          14. FirstRefreshTime:08/17/201808:31:22
          15. LastRefreshTime:08/17/201808:31:53
          16. Duration:31s
          17. Program:sqlplus.exe
          18. FetchCalls:1
          19. GlobalStats
          20. ===============================================================================
          21. |Elapsed|Cpu|IO|Concurrency|Fetch|Buffer|Read|Read|
          22. |Time(s)|Time(s)|Waits(s)|Waits(s)|Calls|Gets|Reqs|Bytes|
          23. ===============================================================================
          24. |33|3.00|30|0.08|1|102K|38571|301MB|
          25. ===============================================================================
          26. SQLPlanMonitoringDetails(PlanHashValue=715774357)
          27. ======================================================================================================================================================================================
          28. |Id|Operation|Name|Rows|Cost|Time|Start|Execs|Rows|Read|Read|Activity|ActivityDetail|
          29. ||||(Estim)||Active(s)|Active||(Actual)|Reqs|Bytes|(%)|(#samples)|
          30. ======================================================================================================================================================================================
          31. |0|SELECTSTATEMENT||||1|+31|1|1|||||
          32. |1|COUNTSTOPKEY||||1|+31|1|1|||||
          33. |2|INLISTITERATOR||||1|+31|1|1|||||
          34. |3|TABLEACCESSBYINDEXROWID|TAB1|114K|33399|32|+0|2|1|38377|300MB|96.77|Cpu(1)|
          35. |||||||||||||dbfilesequentialread(16)|
          36. |||||||||||||readbyothersession(13)|
          37. |4|INDEXRANGESCAN|TAB1_STS_IDX|115K|723|30|+2|2|118K|194|2MB|3.23|readbyothersession(1)|
          38. ======================================================================================================================================================================================
          39. Elapsed:00:00:31.62
          40. ExecutionPlan
          41. ----------------------------------------------------------
          42. Planhashvalue:715774357
          43. ---------------------------------------------------------------------------------------------------------
          44. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
          45. ---------------------------------------------------------------------------------------------------------
          46. |0|SELECTSTATEMENT||1|1847|33399(1)|00:03:14|
          47. |*1|COUNTSTOPKEY||||||
          48. |2|INLISTITERATOR||||||
          49. |*3|TABLEACCESSBYINDEXROWID|TAB1|114K|201M|33399(1)|00:03:14|
          50. |*4|INDEXRANGESCAN|TAB1_STS_IDX|114K||723(1)|00:00:05|
          51. ---------------------------------------------------------------------------------------------------------
          52. PredicateInformation(identifiedbyoperationid):
          53. ---------------------------------------------------
          54. 1-filter(ROWNUM<=1)
          55. 3-filter("cnt"<'4'AND"dt"<SYSDATE@!)
          56. 4-access("STS"='A'OR"STS"='B')
          57. Statistics
          58. ----------------------------------------------------------
          59. 1recursivecalls
          60. 0dbblockgets
          61. 102354consistentgets
          62. 38573physicalreads
          63. 0redosize
          64. 1649bytessentviaSQL*Nettoclient
          65. 785bytesreceivedviaSQL*Netfromclient
          66. 5SQL*Netroundtripsto/fromclient
          67. 0sorts(memory)
          68. 0sorts(disk)
          69. 1rowsprocessed
          70. Tablestats:-
          71. table_name,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len
          72. TAB17965492522416917001847
          73. column_stats(STS):-
          74. table_name,column_name,num_distinct,num_nulls,density,avg_col_len
          75. TAB1STS506.2049410678782E-92
          76. Index_stats(onSTS):-
          77. index_name,leaf_blocks,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,num_rows
          78. TAB1_STS_IDX48793959758744588742229437278308939
          79. SessionStats:-
          80. processlastnon-idletime1534508966
          81. sessionconnecttime1534508966
          82. logicalreadbytesfromcache839663616
          83. cellphysicalIOinterconnectbytes316055552
          84. physicalreadbytes316055552
          85. physicalreadtotalbytes316055552
          86. fileiowaittime17044083
          87. sessionpgamemory8643880
          88. sessionpgamemorymax8643880
          89. tempspaceallocated(bytes)4194304
          90. sessionugamemory1755696
          91. sessionugamemorymax1755696
          92. bufferispinnedcount135743
          93. tablefetchbyrowid117519
          94. non-idlewaitcount107301
          95. sessionlogicalreads102500
          96. consistentgets102450
          97. consistentgetsfromcache102448
          98. nowork-consistentreadgets102368
          99. bufferisnotpinnedcount101741
          100. freebufferinspected43458
          101. freebufferrequested38592
          102. physicalreadtotalIOrequests38581
          103. physicalreadIOrequests38581
          104. physicalreads38581
          105. physicalreadscache38579
          106. hotbuffersmovedtoheadofLRU37258
          107. bytessentviaSQL*Nettoclient7370
          108. bytesreceivedviaSQL*Netfromclient6869
          109. redosize5536
          110. undochangevectorsize4432
          111. DBtime3166
          112. non-idlewaittime2962
          113. userI/Owaittime2954
          114. tablefetchcontinuedrow2423

           

           

          1. Sql_monitorandstatsfromDB2
          2. ******************************
          3. GlobalInformation
          4. ------------------------------
          5. STS:DONE(ALLROWS)
          6. InstanceID:1
          7. ExecutionStarted:08/17/201808:21:47
          8. FirstRefreshTime:08/17/201808:21:47
          9. LastRefreshTime:08/17/201808:26:29
          10. Duration:282s
          11. Module/Action:SQL*Plus/-
          12. Program:sqlplus.exe
          13. FetchCalls:1
          14. GlobalStats
          15. ================================================================
          16. |Elapsed|Cpu|IO|Fetch|Buffer|Read|Read|
          17. |Time(s)|Time(s)|Waits(s)|Calls|Gets|Reqs|Bytes|
          18. ================================================================
          19. |287|8.76|278|1|110K|110K|858MB|
          20. ================================================================
          21. SQLPlanMonitoringDetails(PlanHashValue=715774357)
          22. ======================================================================================================================================================================================
          23. |Id|Operation|Name|Rows|Cost|Time|Start|Execs|Rows|Read|Read|Activity|ActivityDetail|
          24. ||||(Estim)||Active(s)|Active||(Actual)|Reqs|Bytes|(%)|(#samples)|
          25. ======================================================================================================================================================================================
          26. |0|SELECTSTATEMENT||||1|+282|1|1|||||
          27. |1|COUNTSTOPKEY||||1|+282|1|1|||||
          28. |2|INLISTITERATOR||||1|+282|1|1|||||
          29. |3|TABLEACCESSBYINDEXROWID|TAB1|142K|40211|282|+1|2|1|109K|854MB|100.00|dbfilesequentialread(277)|
          30. |4|INDEXRANGESCAN|TAB1_STS_IDX|142K|892|280|+3|2|118K|491|4MB|||
          31. ======================================================================================================================================================================================
          32. Elapsed:00:04:42.34
          33. ExecutionPlan
          34. ----------------------------------------------------------
          35. Planhashvalue:715774357
          36. ---------------------------------------------------------------------------------------------------------
          37. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
          38. ---------------------------------------------------------------------------------------------------------
          39. |0|SELECTSTATEMENT||1|1847|40211(1)|00:08:03|
          40. |*1|COUNTSTOPKEY||||||
          41. |2|INLISTITERATOR||||||
          42. |*3|TABLEACCESSBYINDEXROWID|TAB1|141K|249M|40211(1)|00:08:03|
          43. |*4|INDEXRANGESCAN|TAB1_STS_IDX|141K||892(1)|00:00:11|
          44. ---------------------------------------------------------------------------------------------------------
          45. PredicateInformation(identifiedbyoperationid):
          46. ---------------------------------------------------
          47. 1-filter(ROWNUM<=1)
          48. 3-filter("cnt"<'4'AND"dt"<SYSDATE@!)
          49. 4-access("STS"='A'OR"STS"='B')
          50. Statistics
          51. ----------------------------------------------------------
          52. 1recursivecalls
          53. 0dbblockgets
          54. 110182consistentgets
          55. 109818physicalreads
          56. 0redosize
          57. 9177bytessentviaSQL*Nettoclient
          58. 4547bytesreceivedviaSQL*Netfromclient
          59. 5SQL*Netroundtripsto/fromclient
          60. 0sorts(memory)
          61. 0sorts(disk)
          62. 1rowsprocessed
          63. Tablestats:-
          64. table_name,num_rows,blocks,empty_blocks,chain_cnt,avg_row_len
          65. TAB179447350223186677105375971847
          66. column_stats(STS):-
          67. table_name,column_name,num_distinct,num_nulls,density,avg_col_len
          68. TAB1STS506.17895322507497E-92
          69. Index_stats(onSTS):-
          70. index_name,leaf_blocks,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,num_rows
          71. TAB1_STS_IDX49315259863043826252191312779106263
          72. SessionStats:-
          73. processlastnon-idletime1534508200
          74. sessionconnecttime1534508200
          75. logicalreadbytesfromcache903790592
          76. physicalreadtotalbytes899629056
          77. cellphysicalIOinterconnectbytes899629056
          78. physicalreadbytes899629056
          79. fileiowaittime277881742
          80. sessionpgamemory8586744
          81. sessionpgamemorymax8586744
          82. tempspaceallocated(bytes)4194304
          83. sessionugamemorymax1690184
          84. sessionugamemory1690184
          85. bufferispinnedcount129148
          86. tablefetchbyrowid117521
          87. sessionlogicalreads110326
          88. consistentgets110276
          89. consistentgetsfromcache110276
          90. nowork-consistentreadgets110071
          91. non-idlewaitcount109879
          92. freebufferrequested109830
          93. physicalreadIOrequests109818
          94. physicalreadscache109818
          95. physicalreads109818
          96. physicalreadtotalIOrequests109818
          97. bufferisnotpinnedcount109577
          98. freebufferinspected70740
          99. hotbuffersmovedtoheadofLRU31910
          100. DBtime28203
          101. non-idlewaittime27788
          102. userI/Owaittime27788
          103. dirtybuffersinspected19067
          104. bytessentviaSQL*Nettoclient14927
          105. bytesreceivedviaSQL*Netfromclient10607
          106. redosize5440
          107. undochangevectorsize4432
          108. tablefetchcontinuedrow3660

          consider to REBUILD the index on DB where elapsed duration is > 4 minutes

          • 2. Re: Query performance Issue
            933257

            Can you please help me understand which exact stats suggests the issue with the index and rebuild will fix this issue?

            • 3. Re: Query performance Issue
              John Thorton

              933257 wrote:

               

              Can you please help me understand which exact stats suggests the issue with the index and rebuild will fix this issue?

              Both PLANS show index range scan.

              One takes 30 seconds.

              The other takes 4+minutes.

              If the INDEX content is identical, then time should be the same.

              Since duration is majorly different, doing REBUILD of slow index may reduce elapsed time.

               

              >4 |      INDEX RANGE SCAN           | TAB1_STS_IDX          

              • 4. Re: Query performance Issue
                AndrewSayer

                Looks like a classic case of goodish clustering of data in one DB - you “only” had to do 33K read requests against the table to look at all 118k rows (before cutting it down to one!), compared to the 109K read requests against the table blocks to look at 118K rows (again, cutting it down to one!).

                 

                Otherwise, this could be a case of the table blocks being highly cached in the first DB but not so much in the second. We can see the difference in cost between the two DBs does suggest the clustering factor is playing a role.

                 

                How does DB2 have the same data as DB1? Was logical replication used (e.g. expdp impdp)? This would cause rows to be stored in different orders on both DBs so your clustering factors will be different. This is a prime example of why you shouldn't use logically replicated DBs to test performance.

                 

                The effect of row chaining here is going to be pretty small, only an extra 1K or so rows are chained/migrated but you've got a difference of 70K IOs.

                Rebuilding the index could save you about 300 IOs, it's a tiny drop in a massive ocean, definitely not worth the effort (and risk).

                 

                The rownum=1 filter obviously means that there's a degree of luck involved in the execution, it's going to stop as soon as it finds a row that matches the other two conditions (after identifying rows that match the indexed column condition). However, both DBs stop after looking at 118K rows, so I don't think this is playing a part - although it would suggest that the position of the first matching row is similar in the two DBs which could hint at similar clustering - but we can see with the stats that they don't have the same clustering factors and just because these rows are similarly positioned, the rows coming up to them may not be.

                 

                Of course, the obvious suggestion is to just use a different index - if you only want one row then the index should be able to do a lot of work for you. You will have to bear in mind the golden rule of indexing as you have two non-equalities (https://ctandrewsayer.wordpress.com/2017/03/24/the-golden-rule-of-indexing/ ), but a quick guess would suggest that the sysdate filter will not do a lot of work - so something like

                 

                create index test_index_1 on tab1 (sts, cnt)

                 

                Might do the trick. Hopefully, you'll still have the inlist iterator for the sts filter. If you need the other column in the index then consider where it should come - although you'll be doing a lot less work just by being able to filter down before hitting the table.

                1 person found this helpful
                • 5. Re: Query performance Issue
                  AndrewSayer

                  John Thorton wrote:

                   

                  933257 wrote:

                   

                  Can you please help me understand which exact stats suggests the issue with the index and rebuild will fix this issue?

                  Both PLANS show index range scan.

                  One takes 30 seconds.

                  The other takes 4+minutes.

                  If the INDEX content is identical, then time should be the same.

                  Since duration is majorly different, doing REBUILD of slow index may reduce elapsed time.

                   

                  >4 | INDEX RANGE SCAN | TAB1_STS_IDX

                  Since the huge majority of the time is spent in touching the table blocks rather than the index (as shown in the live monitor reports), we can see that rebuilding the index will have no effect on the bulk of the execution.

                  1 person found this helpful
                  • 6. Re: Query performance Issue
                    933257

                    Thank you so much.

                    Here we use "Streams replication" to replicate data between these two database, so isn't it true that in this case, the rows should be applied in traget database in same way/order as source, so in both the databases the clustering factor should not differ much? I need to see if both/two way replication is used and that is any way causing this, btw I do see that the difference in clustering factor is 21.9 million vs 22.2 million, not sure if that will have such drastic impact in query timing? And i agree that sql monitor showing plan_line_id- 3 i.e "TABLE ACCESS BY INDEX ROWID" has consumed ~97% of the time, so thinking of index rebuild as may be fragmented may not add much benefit to us.

                     

                    Another doubt i had, is my understanding is correct regarding the point that ,may be someone did run the ANALYZE table command due to which this column-"chain_count", in dba_tables gets populated, but may be this has been done in long back, so we may need to do that again to see the percentage of chained row at current moment? But again as the session statistics shows the "table fetch continued row" stats as 3660 vs 2423 for both executions, so does it mean, this has not impacted the execution time much, so we should not waste effort looking in this(row chaining) direction? Some of the team mates suggesting to do table reorg, so not sure if that will help us anyway here?

                     

                    This table is having ~172GB in size holding ~79million rows, it has ~3BLOB columns too(and we are selecting two of the blob columns, however commenting those two columns does not make any difference in elapsed time).And there exists two indexes, one on the primary key(on different columns altogether) and other one-TAB1_STS_IDX on column STS, but as i mentioned t he stats , this column is very less distinct in nature. And also the other colmn used in filter i.e. CNT is very less distinct in nature, so would it be advisable to go for new index on (STS,CNT) considering may not benefit other queries much and impact on DML?

                     

                    table_name, column_name, num_distinct,num_nulls,density, avg_col_len

                    TAB1        DT             43433984      0           2.30234463410034E-8    8

                    TAB1        CNT              7              866       6.20717225848154E-9    2

                     

                     

                    finally, I tried executing the same query multiple times and seeing the execution time for the second run has been in few seconds in both the databases. the consistent gets were 102k vs 110k , but the physical reads was '1' in both the databases for each subsequent execution and the query was too giving result in seconds for the subsequent execution in both the databases, i was not seeing much difference. So now wondering if its simply due to the caching effect? is this caching anyway related to the presence of blob columns in the table and if this can be improved?

                    • 7. Re: Query performance Issue
                      AndrewSayer

                      Your initial copy before you turned on the streams replication could have reoordered the rows unless you used physical copies of the data files.

                       

                      Is the performance difference consistent? eg can you get it down to less than 30 seconds in DB2 just by rerunning the query? That would suggest caching, perhaps also your cache sizes are different so you can’t fit in the magic number of blocks required for this query in DB2.

                       

                      There is certainly a difference due to clustering but it seems to be only 10%.

                       

                      Blobs won’t really be touched until the row is projected out, this only happens for one row so I think it’s fine to ignore them. Although, be aware that lobs make everything different, including streams replication - it might have to do additional lookups on the source DB table which would increase the caching of blocks in this table.

                       

                      num_distinct isn’t so important when your filter is a range ( < ), what matters more is the range of values that your column goes over (the low and high values, or the histogram).

                       

                      For this SQL you will certainly eliminate about 100K buffers just by being able to filter on cnt and the date column in that index - as I said, you may only need one of those columns. It’s up to you how important the query is, but as this seems to be the target of an async replication: it‘s probably not too important that the DML time is minimized - the users won’t experience it and it’s extremely unlikely to really contribute to an unacceptable lag in your replication (but obviously test it)

                      1 person found this helpful
                      • 8. Re: Query performance Issue
                        933257

                        Thank You so much Andrew.

                         

                        I did run the sql query in both the databases three times each to test the caching effect. And i have published the sql monitor and trace output for each of them. The subsequent execution in each of the database does finishes within few seconds. So it hopefully justify the caching is the main reason behind such a large difference in elapsed time of the first execution of the query and it must be due to the fact the DB1 is heavily used so the table blocks are readily available in the cache so making the initial execution faster on DB1 as compared to DB2. And it may be happening sometimes to reach out to the first matching row(rownum=1) would be taking bit longer and the only possibility to further improve the query performance would be, as you suggested , to create composite index on STS,CNT, based on the importance of the query. Please correct me if my understanding is wrong here.

                         

                        *******Statistics From DB1*********
                        
                        ++++++ Execution -1
                        
                        Global Information
                        ------------------------------
                         Status              :  DONE (ALL ROWS)           
                         Instance ID         :  1                         
                         SQL Execution ID    :  16777216                  
                         Execution Started   :  08/18/2018 14:13:36       
                         First Refresh Time  :  08/18/2018 14:13:36       
                         Last Refresh Time   :  08/18/2018 14:13:56       
                         Duration            :  20s                       
                         Program             :  sqlplus.exe               
                         Fetch Calls         :  1                         
                        
                        Global Stats
                        ==============================================================================
                        | Elapsed |   Cpu   |    IO    | Concurrency | Fetch | Buffer | Read | Read  |
                        | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Calls |  Gets  | Reqs | Bytes |
                        ==============================================================================
                        |      21 |    1.19 |       19 |        0.01 |     1 |   102K | 8531 |  67MB |
                        ==============================================================================
                        
                        SQL Plan Monitoring Details (Plan Hash Value=715774357)
                        ====================================================================================================================================================================================
                        | Id |            Operation            |          Name           |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |       Activity Detail       |
                        |    |                                 |                         | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |         (# samples)         |
                        ====================================================================================================================================================================================
                        |  0 | SELECT STATEMENT                |                         |         |       |         1 |    +20 |     1 |        1 |      |       |          |                             |
                        |  1 |   COUNT STOPKEY                 |                         |         |       |         1 |    +20 |     1 |        1 |      |       |          |                             |
                        |  2 |    INLIST ITERATOR              |                         |         |       |         1 |    +20 |     1 |        1 |      |       |          |                             |
                        |  3 |     TABLE ACCESS BY INDEX ROWID | TAB1                    |    114K | 33399 |        21 |     +1 |     2 |        1 | 8485 |  66MB |   100.00 | Cpu (2)                     |
                        |    |                                 |                         |         |       |           |        |       |          |      |       |          | db file sequential read (9) |
                        |    |                                 |                         |         |       |           |        |       |          |      |       |          | read by other session (9)   |
                        |  4 |      INDEX RANGE SCAN           | TAB1_STS_IDX            |    115K |   723 |        19 |     +2 |     2 |     118K |   46 | 368KB |          |                             |
                        ====================================================================================================================================================================================
                        
                        
                        Statistics
                        ----------------------------------------------------------
                                  1  recursive calls
                                  0  db block gets
                             102438  consistent gets
                               8531  physical reads
                                  0  redo size
                                887  bytes sent via SQL*Net to client
                                369  bytes received via SQL*Net from client
                                  3  SQL*Net roundtrips to/from client
                                  0  sorts (memory)
                                  0  sorts (disk)
                                  1  rows processed
                        
                        SQL>
                        
                        
                        +++++ Execution-2 
                        
                        SQL Monitoring Report
                        
                        Global Information
                        ------------------------------
                         Status              :  DONE (ALL ROWS)           
                         Instance ID         :  1                         
                         SQL Execution ID    :  16777216                  
                         Execution Started   :  08/18/2018 14:15:15       
                         First Refresh Time  :  08/18/2018 14:15:15       
                         Last Refresh Time   :  08/18/2018 14:15:15       
                         Duration            :  .670934s                  
                         Program             :  sqlplus.exe               
                         Fetch Calls         :  1                         
                        
                        Global Stats
                        =================================================
                        | Elapsed |   Cpu   |  Other   | Fetch | Buffer |
                        | Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
                        =================================================
                        |    0.67 |    0.65 |     0.02 |     1 |   102K |
                        =================================================
                        
                        SQL Plan Monitoring Details (Plan Hash Value=715774357)
                        =========================================================================================================================================================
                        | Id |            Operation            |          Name           |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
                        |    |                                 |                         | (Estim) |       | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
                        =========================================================================================================================================================
                        |  0 | SELECT STATEMENT                |                         |         |       |         1 |     +0 |     1 |        1 |          |                 |
                        |  1 |   COUNT STOPKEY                 |                         |         |       |         1 |     +0 |     1 |        1 |          |                 |
                        |  2 |    INLIST ITERATOR              |                         |         |       |         1 |     +0 |     1 |        1 |          |                 |
                        |  3 |     TABLE ACCESS BY INDEX ROWID | TAB1                    |    114K | 33399 |         1 |     +0 |     2 |        1 |   100.00 | Cpu (1)         |
                        |  4 |      INDEX RANGE SCAN           | TAB1_STS_IDX            |    115K |   723 |         1 |     +0 |     2 |     118K |          |                 |
                        =========================================================================================================================================================
                        
                        Statistics
                        ------------------------------------------------------
                                  1  recursive calls
                                  0  db block gets
                             102425  consistent gets
                                  2  physical reads
                                  0  redo size
                               1671  bytes sent via SQL*Net to client
                                785  bytes received via SQL*Net from client
                                  5  SQL*Net roundtrips to/from client
                                  0  sorts (memory)
                                  0  sorts (disk)
                                  1  rows processed
                        
                        
                        
                        
                        +++++ Execution -3
                                  
                        Global Information
                        ------------------------------
                         Status              :  DONE (ALL ROWS)           
                         Instance ID         :  1                         
                         Execution Started   :  08/18/2018 14:15:34       
                         First Refresh Time  :  08/18/2018 14:15:34       
                         Last Refresh Time   :  08/18/2018 14:15:34       
                         Duration            :  .586472s                  
                         Module/Action       :  SQL*Plus/-                
                          Fetch Calls         :  1                         
                        
                        Global Stats
                        ===============================================================
                        | Elapsed |   Cpu   | Concurrency |  Other   | Fetch | Buffer |
                        | Time(s) | Time(s) |  Waits(s)   | Waits(s) | Calls |  Gets  |
                        ===============================================================
                        |    0.59 |    0.57 |        0.00 |     0.02 |     1 |   102K |
                        ===============================================================
                        
                        SQL Plan Monitoring Details (Plan Hash Value=715774357)
                        =========================================================================================================================================================
                        | Id |            Operation            |          Name           |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
                        |    |                                 |                         | (Estim) |       | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
                        =========================================================================================================================================================
                        |  0 | SELECT STATEMENT                |                         |         |       |         1 |     +0 |     1 |        1 |          |                 |
                        |  1 |   COUNT STOPKEY                 |                         |         |       |         1 |     +0 |     1 |        1 |          |                 |
                        |  2 |    INLIST ITERATOR              |                         |         |       |         1 |     +0 |     1 |        1 |          |                 |
                        |  3 |     TABLE ACCESS BY INDEX ROWID | TAB1                    |    114K | 33399 |         2 |     +0 |     2 |        1 |   100.00 | Cpu (1)         |
                        |  4 |      INDEX RANGE SCAN           | TAB1_STS_IDX            |    115K |   723 |         1 |     +0 |     2 |     118K |          |                 |
                        =========================================================================================================================================================
                        
                        Statistics
                        ----------------------------------------------------------
                                  1  recursive calls
                                  0  db block gets
                             102422  consistent gets
                                  0  physical reads
                                  0  redo size
                               6286  bytes sent via SQL*Net to client
                               3035  bytes received via SQL*Net from client
                                  4  SQL*Net roundtrips to/from client
                                  0  sorts (memory)
                                  0  sorts (disk)
                                  1  rows processed
                        
                        

                         

                         

                         

                        ********************* Statistics on DB-2
                        
                        ++++++ Execution-1
                        
                        
                        Global Information
                        ------------------------------
                         Status              :  DONE (ALL ROWS)          
                         Instance ID         :  1                        
                         SQL Execution ID    :  16777216                 
                         Execution Started   :  08/18/2018 14:16:03      
                         First Refresh Time  :  08/18/2018 14:16:03      
                         Last Refresh Time   :  08/18/2018 14:20:05      
                         Duration            :  242s                     
                         Module/Action       :  SQL*Plus/-               
                         Program             :  sqlplus.exe              
                         Fetch Calls         :  1                        
                        
                        Global Stats
                        ================================================================
                        | Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
                        | Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
                        ================================================================
                        |     247 |    8.22 |      239 |     1 |   110K | 110K | 859MB |
                        ================================================================
                        
                        SQL Plan Monitoring Details (Plan Hash Value=715774357)
                        ======================================================================================================================================================================================
                        | Id |            Operation            |          Name           |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |        Activity Detail        |
                        |    |                                 |                         | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |          (# samples)          |
                        ======================================================================================================================================================================================
                        |  0 | SELECT STATEMENT                |                         |         |       |         1 |   +242 |     1 |        1 |      |       |          |                               |
                        |  1 |   COUNT STOPKEY                 |                         |         |       |         1 |   +242 |     1 |        1 |      |       |          |                               |
                        |  2 |    INLIST ITERATOR              |                         |         |       |         1 |   +242 |     1 |        1 |      |       |          |                               |
                        |  3 |     TABLE ACCESS BY INDEX ROWID | TAB1                    |    142K | 40211 |       242 |     +1 |     2 |        1 | 109K | 855MB |   100.00 | Cpu (4)                       |
                        |    |                                 |                         |         |       |           |        |       |          |      |       |          | db file sequential read (233) |
                        |  4 |      INDEX RANGE SCAN           | TAB1_STS_IDX            |    142K |   892 |       241 |     +2 |     2 |     118K |  490 |   4MB |          |                               |
                        ======================================================================================================================================================================================
                        
                        Statistics
                        ----------------------------------------------------------
                                  1  recursive calls
                                  0  db block gets
                             110134  consistent gets
                             109904  physical reads
                                  0  redo size
                              17062  bytes sent via SQL*Net to client
                               8460  bytes received via SQL*Net from client
                                  5  SQL*Net roundtrips to/from client
                                  0  sorts (memory)
                                  0  sorts (disk)
                                  1  rows processed
                        
                        SQL>
                        
                        
                        ++++++++++++++Execution -2
                        
                        
                        SQL Monitoring Report
                        
                        Global Information
                        ------------------------------
                         Status              :  DONE (ALL ROWS)          
                         Instance ID         :  1                        
                         SQL Execution ID    :  16777216                 
                         Execution Started   :  08/18/2018 14:22:18      
                         First Refresh Time  :  08/18/2018 14:22:18      
                         Last Refresh Time   :  08/18/2018 14:22:19      
                         Duration            :  1s                       
                         Module/Action       :  SQL*Plus/-               
                         Program             :  sqlplus.exe              
                         Fetch Calls         :  1                        
                        
                        Global Stats
                        =================================================
                        | Elapsed |   Cpu   |  Other   | Fetch | Buffer |
                        | Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
                        =================================================
                        |    0.53 |    0.52 |     0.01 |     1 |   110K |
                        =================================================
                        
                        SQL Plan Monitoring Details (Plan Hash Value=715774357)
                        =========================================================================================================================================================
                        | Id |            Operation            |          Name           |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
                        |    |                                 |                         | (Estim) |       | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
                        =========================================================================================================================================================
                        |  0 | SELECT STATEMENT                |                         |         |       |         1 |     +1 |     1 |        1 |          |                 |
                        |  1 |   COUNT STOPKEY                 |                         |         |       |         1 |     +1 |     1 |        1 |          |                 |
                        |  2 |    INLIST ITERATOR              |                         |         |       |         1 |     +1 |     1 |        1 |          |                 |
                        |  3 |     TABLE ACCESS BY INDEX ROWID | TAB1                    |    142K | 40211 |         1 |     +1 |     2 |        1 |          |                 |
                        |  4 |      INDEX RANGE SCAN           | TAB1_STS_IDX            |    142K |   892 |         1 |     +1 |     2 |     118K |          |                 |
                        =========================================================================================================================================================
                        
                        Statistics
                        ----------------------------------------------------------
                                  1  recursive calls
                                  0  db block gets
                             110134  consistent gets
                                  0  physical reads
                                  0  redo size
                              17063  bytes sent via SQL*Net to client
                               8460  bytes received via SQL*Net from client
                                  5  SQL*Net roundtrips to/from client
                                  0  sorts (memory)
                                  0  sorts (disk)
                                  1  rows processed
                        
                        
                        SQL Monitoring Report
                        
                        ++++++++++++++Execution -3
                        
                        
                        Global Information
                        ------------------------------
                         Status              :  DONE (ALL ROWS)          
                         Instance ID         :  1                        
                         SQL Execution ID    :  16777216                 
                         Execution Started   :  08/18/2018 14:22:38      
                         First Refresh Time  :  08/18/2018 14:22:38      
                         Last Refresh Time   :  08/18/2018 14:22:38      
                         Duration            :  .541429s                 
                         Program             :  sqlplus.exe              
                         Fetch Calls         :  1                        
                        
                        Global Stats
                        =================================================
                        | Elapsed |   Cpu   |  Other   | Fetch | Buffer |
                        | Time(s) | Time(s) | Waits(s) | Calls |  Gets  |
                        =================================================
                        |    0.54 |    0.54 |     0.00 |     1 |   110K |
                        =================================================
                        
                        SQL Plan Monitoring Details (Plan Hash Value=715774357)
                        =========================================================================================================================================================
                        | Id |            Operation            |          Name           |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |
                        |    |                                 |                         | (Estim) |       | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |
                        =========================================================================================================================================================
                        |  0 | SELECT STATEMENT                |                         |         |       |         1 |     +0 |     1 |        1 |          |                 |
                        |  1 |   COUNT STOPKEY                 |                         |         |       |         1 |     +0 |     1 |        1 |          |                 |
                        |  2 |    INLIST ITERATOR              |                         |         |       |         1 |     +0 |     1 |        1 |          |                 |
                        |  3 |     TABLE ACCESS BY INDEX ROWID | TAB1                    |    142K | 40211 |         1 |     +0 |     2 |        1 |          |                 |
                        |  4 |      INDEX RANGE SCAN           | TAB1_STS_IDX            |    142K |   892 |         1 |     +0 |     2 |     118K |          |                 |
                        =========================================================================================================================================================
                        
                        Statistics
                        ----------------------------------------------------------
                                  1  recursive calls
                                  0  db block gets
                             110134  consistent gets
                                  0  physical reads
                                  0  redo size
                              17067  bytes sent via SQL*Net to client
                               8460  bytes received via SQL*Net from client
                                  5  SQL*Net roundtrips to/from client
                                  0  sorts (memory)
                                  0  sorts (disk)
                                  1  rows processed
                        
                        
                        
                        • 9. Re: Query performance Issue
                          933257

                          I confirmed the sga_max_size is set as  24gb in both the databases. sga_target set as 0, so it ASMM enabled and i don't see  many resize operation in gv$sga_resize_ops. And max db_cache_size in gv$sga_dynamic_components noted as ~6gb and shared_pool as ~4gb.

                          • 10. Re: Query performance Issue
                            933257

                            As i see even the first execution on db-1 also took ~20 seconds(with ~8k physical reads) as  compared to seconds(with ~2 physical reads) in subsequent execution. So my thought was , if the caching of this table blocks is depends on how the blob gets stored in that table? if it gets stored along with the row then chances are there it would flush out of the cache quickly due to the larger size of the row as  compared to the scenario when its stored outside the row and is there any workaround possible to make this better?

                            • 11. Re: Query performance Issue

                              I did run the sql query in both the databases three times each to test the caching effect.

                              Sorry - but no, that does NOT test 'the caching effect'. At least not the one Andrew was referring to.

                               

                              Maybe you misunderstood what Andrew has tried to tell you? Because you keep wandering off into other things like LOBs, etc.

                               

                              This is what you posted;

                                    38573  physical reads -db1

                                   109818  physical reads -db2

                              And this is what Andrew said

                              you “only” had to do 33K read requests against the table to look at all 118k rows (before cutting it down to one!), compared to the 109K read requests against the table blocks to look at 118K rows (again, cutting it down to one!).

                              Andrew mentioned clustering. Your response was to do three executions on each DB.

                               

                              That tests the WRONG thing - it isn't important how well things work if you cache a lot of the data in memory. That has NOTHING to do with clustering factor.

                               

                              What matters is how important it works when there is NOTHING in memory. Then the clustering factor will affect how many blocks need to be PHYSICALLY read to get the data needed.

                               

                              Those 'physical' reads above already give you an indication.

                               

                              The test you need to run is to FLUSH the buffer cache so that NONE of the blocks are in memory and then see how many blocks get read on each DB. That will give you an indication of the clustering factor.

                               

                              Before you do anything I suggest you read Richard Foote's articles about clustering factor - both how to calculate it and how it can affect things.

                               

                              Then please follow the guidelines in the FAQ about how to post a tuning request and the info you need to provide. That info includes:

                               

                              1. the DDL for the tables and indexes

                              2. row counts for the tables AND query predicates

                              3. info about stats - are they current and how they were collected

                               

                              And, for your use case, info about what Andrew ask about - how was the INITIAL table data populated on DB2 from DB1.

                              • 12. Re: Query performance Issue
                                933257

                                "Is the performance difference consistent? eg can you get it down to less than 30 seconds in DB2 just by rerunning the query? That would suggest caching, perhaps also your cache sizes are different so you can’t fit in the magic number of blocks required for this query in DB2."

                                 

                                "is certainly a difference due to clustering but it seems to be only 10%."

                                 

                                Sorry if i misunderstood but above were two of Andrew's points, so i have made the query rerun to evaluate caching effect and i have confirmed there was no difference in sga sizes fr the two databases. And i think even the initial execution of query on DB2 is slower as compared to DB1 as because of the DB1 is active/used by the external users and so the table blocks are more cached as compared to DB2(which is not exposed to external users and maintained as kind of standby). And i was going into the LOB side, as because i was wondering of those anyway impact the caching of rows/blocks due to larger size or anything?

                                 

                                And it may be  because we may not have full control on improving the clustering factor, and as per Andrew's suggestion creating composite index on column (STS,CNT) should make the performance better i.e. by making the index to do maximum work for us rather relying on the post filter criteria.

                                 

                                As this issue is on production, so I will see if i can test/reproduce these on lower environment again by flushing buffer cache and post them. Actually i was thinking the first execution which i posted for DB1 vs DB2 should give the real impact/difference of clustering factor. I have published the table/column/index stats in previous post and stats are updated. I will post the DDL for table/index.

                                • 13. Re: Query performance Issue
                                  And i think even the initial execution of query on DB2 is slower as compared to DB1 as because of the DB1 is active/used by the external users and so the table blocks are more cached as compared to DB2(which is not exposed to external users and maintained as kind of standby).

                                  Sorry - but you STILL seem to have it backwards - not sure why.

                                   

                                  1. there is only so much memory

                                  2. the buffer cache is only so big

                                  3. if DB1 is used by other users then table blocks are LESS cached compared to DB2.

                                   

                                  ALL USERS will use buffer cache. So if DB1 is used by other users they will use some of the buffer cache for the work they are doing.

                                   

                                  That means there will be LESS CACHE available for the user you are testing - not more.

                                   

                                  That means that 'caching' would be LESS PRONOUNCED between the two machines.

                                   

                                  In normal operations you can NOT flush the buffer cache.

                                   

                                  In normal operations you can NOT run things multiple times.

                                   

                                  You need to test/tune so that things run properly and meet their SLA (service level agreement) in the NORMAL scheme of operations.

                                   

                                  Flushing the cache is just a better way to see the difference in PHYSICAL reads between the two DBs. That is it.

                                   

                                  Did you read the article on clustering factor?

                                  • 14. Re: Query performance Issue
                                    Jonathan Lewis

                                    The effort you've made to supply relevant information, and the description of your thought processes to date is something that many users of this forum could learn from.

                                    There are several features of the information you've supplied that suggest your ideas about "recent use" are correct. If I were sitting in your seat with your access to the data I'd be checking the frequency histogram that almost certainly exists on the STS column to confirm the hypothesis, but everything about the figures says there's a tiny fraction of the data that has values A or B and that's the interesting fraction of the data that the users are accessing most frequently, which is why it's fairly well cached on the primary system but very poorly cached on the secondary system.

                                     

                                    I'll be writing up some notes later on today or tomorrow about interpreting the plan and the statistics you've reported, with a couple of comments on how some of the stats might be misleading and need corroboration, and how some of the stats have to be describing exactly what's happening, and how some of them need to be cross-referenced to make sure that you have a consistent understanding of the data.

                                     

                                    Thanks for posting this.

                                     

                                    Regards

                                    Jonathan Lewis

                                    1 2 Previous Next