Decrease response time of most often used incident search

From OraWiki

Jump to: navigation, search

Contents

Author

(c) 2009 by Steven of Oz
Author
NameGerald Roehrbein
httpwww.OraForecast.com
e-mailGerald.Roehrbein@OraForecast.com
Release0.9
Created2009.03.17
Last update2009.03.17
ChangesStarted article
Estimated duration of installation a few seconds
LicenseFreeware


Purpose

  • Required Index for Remedy 7.0 to avoid full table scans for user searches.

AIX Oracle RAC 10.2.0.3 with three nodes.


Often used search strategy of ARS is a heavy resource consumer: 

SELECT T903.C1,C3 
FROM <owner>.T903 
WHERE (T903.C301289100 = '<YOUR INCIDENT>') 
ORDER BY 2 DESC;

This query is executed using a Full Table Scan: 

Elapsed: 00:00:00.54

Execution Plan
----------------------------------------------------------
Plan hash value: 702437578

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    39 |  7106   (1)| 00:01:26 |
|   1 |  SORT ORDER BY     |      |     1 |    39 |  7106   (1)| 00:01:26 |
|*  2 |   TABLE ACCESS FULL| T903 |     1 |    39 |  7105   (1)| 00:01:26 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T903"."C301289100"='<YOUR INCIDENT>')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      40711  consistent gets
          0  physical reads
          0  redo size
        589  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed


fsora buffer_usage | grep -i T903 (see Fast Small Oracle Remote Analyzer) shows: 

SUBCACHE     OBJECT_NAME                    SUM(BH.BLOCKS)  Fragments
------------ ------------------------------ -------------- ----------
DEFAULT      T956                                    54392          4

Using an index would result in a much more efficient usage of the cache.

To find the query which may be the reason for that can be done using:

fsora sql_full_scans | grep -i T956 (see Fast Small Oracle Remote Analyzer). This results in some occurences of query above.

Runtime of this query seems not to matter because it's less than one second. But this query consumes a lot of resources because it need 40711 block in the buffer cache and since the query is often executed it makes not sense to run it using a Full Table Scan. The major problem of this query is: What happened when the blocks are not in the cache?


A OraForecast.com recommended index can reduce resource wastage and give better QEP: 

create index <owner>.<index_name> on <owner>.t903
(C301289100,c1,c3)
tablespace <tablespace_name> parallel 7 nologging;

alter  index <owner>.<index_name> logging noparallel;

analyze table <owner>.t903 estimate statistics sample 3 percent for all indexed columns;

This gives plan below: 

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3675991343

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    39 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY    |             |     1 |    39 |     3  (34)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| <INDEX_NAME>|     1 |    39 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T903"."C301289100"='<YOUR INCIDENT>')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          1  physical reads
          0  redo size
        589  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

This optimization show that the 3:1 rule (three consistent gets per each row processed) is a good tuning goal. Sometimes a performance analyst can reach this without changing queries. Most times it is required to change the SQL of a query but most often a DBA do not have access to the source code. That's the reason why we can not always do the best for a customer/client.

Installation








Usage


  •  None



Recommendations


  •  Always implement changes in a production environment after having some tests in a lab.



Example(s)




Security related issues


  •  None.



How to make this feature as secure as possible


  •  None.



Source code of this extension


  •  See above



Planned enhancements


  •  Will follow



Discussions



History


  •  2009.03.16 Start of documentation



Known bugs


  •  Reported      Description of problem                                 Fixed         Release

  •  2009.03.16    No issues known



Running example


  •  Not required






Retrieved from "http://www.oraforecast.com/orawiki/index.php/Decrease_response_time_of_most_often_used_incident_search"

						
			

		

	

		

		

	
	

		
Personal tools