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

    Query performance Issue

    933257
    933257 Aug 17, 2018 1:35 PM

      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
      • 2366 Views
      • Tags:
        • 1. Re: Query performance Issue
          John Thorton
          John Thorton Aug 17, 2018 1:42 PM (in response to 933257)

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

           

           

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

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

          • 2. Re: Query performance Issue
            933257
            933257 Aug 17, 2018 3:17 PM (in response to John Thorton)

            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
              John Thorton Aug 17, 2018 3:29 PM (in response to 933257)

              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
                AndrewSayer Aug 18, 2018 7:56 AM (in response to 933257)

                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
                  AndrewSayer Aug 18, 2018 7:59 AM (in response to John Thorton)

                  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
                    933257 Aug 18, 2018 12:30 PM (in response to AndrewSayer)

                    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
                      AndrewSayer Aug 18, 2018 1:21 PM (in response to 933257)

                      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
                        933257 Aug 18, 2018 7:34 PM (in response to AndrewSayer)

                        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
                          933257 Aug 18, 2018 7:42 PM (in response to AndrewSayer)

                          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
                            933257 Aug 19, 2018 10:58 AM (in response to AndrewSayer)

                            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
                              rp0428 Aug 19, 2018 5:21 PM (in response to 933257)

                              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
                                933257 Aug 19, 2018 7:22 PM (in response to rp0428)

                                "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
                                  rp0428 Aug 19, 2018 8:06 PM (in response to 933257)
                                  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
                                    Jonathan Lewis Aug 20, 2018 5:04 AM (in response to 933257)

                                    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