Decrease response time of search user in tab position
From OraWiki
Contents |
Author
| (c) 2009 by Steven of Oz | ||
|---|---|---|
| Author | ||
| Name | Gerald Roehrbein | |
| http | www.OraForecast.com | |
| Gerald.Roehrbein@OraForecast.com | ||
| Release | 0.9 | |
| Created | 2009.03.16 | |
| Last update | 2009.03.16 | |
| Changes | Started article | |
| Estimated duration of installation | a few seconds | |
| License | Freeware | |
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.
SELECT
T2160.C1,
C536870913,
C7,
C8,
C536870962,
C3,
C536870916,
C536870915,
C200000012,
C536870972,
C536871008,
C260000000,
C536870913
FROM <owner>.T2160
WHERE
(((T2160.C536870913 LIKE ('%' || ' ')) OR (' ' = ' ')) AND
((T2160.C8 LIKE (' ' || '%')) OR (' ' = ' ')) AND
((T2160.C700100004 LIKE (('%' || ' ') || '%')) OR
(' ' = ' ')) AND ((' ' = T2160.C536870960) OR (' ' = ' ')) AND
((' ' = T2160.C600100114) OR (' ' = ' ')) AND
((' ' = T2160.C600100115) OR (' ' = ' ')) AND
((T2160.C536870962 LIKE (('%' || '<SEARCH PHRASE>') || '%')) OR ('<SEARCH PHRASE>' = ' ')) AND
((T2160.C200000012 LIKE (' ' || '%')) OR (' ' = ' ')) AND
((' ' = T2160.C200000007) OR (' ' = ' ')) AND
((' ' = T2160.C536870949) OR (' ' = ' ')) AND
((' ' = T2160.C536870950) OR (' ' = ' ')) AND
((' ' = T2160.C536871046) OR (' ' = ' ')) AND
('<SEARCH PHRASE>' = T2160.C700100001) AND
((T2160.C7 IS NULL) OR (0 = 0)) AND
(T2160.C3 >= 31532400) AND
(T2160.C3 <= 1237244400) AND
((T2160.C536870956 LIKE (('%' || ' ') || '%')) OR (' ' = ' ')) AND
((T2160.C536870961 = ' ') OR (' ' = ' ')) AND
(T2160.C536870917 < 5) AND (((2 = 0) AND
(T2160.C536871008 IS NOT NULL)) OR ((2 = 1) AND
(T2160.C536871008 IS NULL)) OR (2 = 2) OR (0 != 0)) AND
((T2160.C240000007 LIKE (('%' || ' ') || '%')) OR
(' ' = ' '))) ORDER BY 13 ASC;
Query above generates QEP below:
Elapsed: 00:00:19.21
Execution Plan
----------------------------------------------------------
Plan hash value: 825349215
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21409 | 7902K| | 53064 (1)| 00:10:37 |
| 1 | SORT ORDER BY | | 21409 | 7902K| 16M| 53064 (1)| 00:10:37 |
|* 2 | TABLE ACCESS BY INDEX ROWID | T1978 | 1 | 160 | | 1 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 21409 | 7902K| | 51337 (1)| 00:10:17 |
|* 4 | HASH JOIN | | 52454 | 10M| 5480K| 25092 (1)| 00:05:02 |
|* 5 | TABLE ACCESS FULL | T1951 | 55510 | 4824K| | 7187 (1)| 00:01:27 |
|* 6 | TABLE ACCESS BY INDEX ROWID| T1975 | 46675 | 5879K| | 17326 (1)| 00:03:28 |
|* 7 | INDEX RANGE SCAN | I1975_700100001_1 | 46675 | | | 109 (0)| 00:00:02 |
|* 8 | INDEX RANGE SCAN | I1978_536870997_1 | 1 | | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
471 recursive calls
0 db block gets
32627 consistent gets
15787 physical reads
0 redo size
1111 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
0 rows processed
Query based upon two views for three tables:
- T2160 based upon view T2135 (join T1975 and T1978) and table T1951
Performance of query above will be much better if using index below:
create index <owner>.<index1_name> on <owner>.t1951 (C536870961,C536870959,C536870972,C536871008,C200000012,C200000007,C536870962,c1) tablespace <tablespace_name> parallel <#CPU's-1> nologging; alter index <owner>.<index1_name> noparallel logging;
QEP with OraForecast recommended index:
Elapsed: 00:00:00.87
Execution Plan
----------------------------------------------------------
Plan hash value: 856500736
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21409 | 7902K| | 49265 (1)| 00:09:52 |
| 1 | SORT ORDER BY | | 21409 | 7902K| 16M| 49265 (1)| 00:09:52 |
|* 2 | TABLE ACCESS BY INDEX ROWID | T1978 | 1 | 160 | | 1 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 21409 | 7902K| | 47538 (1)| 00:09:31 |
|* 4 | HASH JOIN | | 52454 | 10M| 5480K| 21293 (1)| 00:04:16 |
|* 5 | INDEX FAST FULL SCAN | <index1_name> | 55510 | 4824K| | 3387 (1)| 00:00:41 |
|* 6 | TABLE ACCESS BY INDEX ROWID| T1975 | 46675 | 5879K| | 17326 (1)| 00:03:28 |
|* 7 | INDEX RANGE SCAN | I1975_700100001_1 | 46675 | | | 109 (0)| 00:00:02 |
|* 8 | INDEX RANGE SCAN | I1978_536870997_1 | 1 | | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
239 recursive calls
0 db block gets
15590 consistent gets
0 physical reads
0 redo size
1111 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
0 rows processed
A second index at table T1975 seems to be also efficient.
create index <owner>.<index2_name> on <owner>.t1975 (c1,c7,c3,c8,C260000000, C536870956, C536870960, C536870961, C536871016, C600100114, C600100115, C700100001, C700100004) tablespace aradmin parallel 7 nologging; alter <owner>.<index2_name> noparallel logging;
This changes QEP to:
Elapsed: 00:00:00.80
Execution Plan
----------------------------------------------------------
Plan hash value: 1353075123
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21409 | 7902K| | 34051 (1)| 00:06:49 |
| 1 | SORT ORDER BY | | 21409 | 7902K| 16M| 34051 (1)| 00:06:49 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1978 | 1 | 160 | | 1 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 21409 | 7902K| | 32324 (1)| 00:06:28 |
|* 4 | HASH JOIN | | 52454 | 10M| 5480K| 6078 (1)| 00:01:13 |
|* 5 | INDEX FAST FULL SCAN | <index1_name> | 55510 | 4824K| | 3387 (1)| 00:00:41 |
|* 6 | INDEX FAST FULL SCAN | <index2_name> | 46675 | 5879K| | 2112 (1)| 00:00:26 |
|* 7 | INDEX RANGE SCAN | I1978_536870997_1 | 1 | | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15525 consistent gets
0 physical reads
0 redo size
1111 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
A third index at table T1978 may also help.
create index <owner>.<index3_name> on <owner>.t1978 ( C536870997, C1, C240000007, C8, C7, C536870949, C536870950, C536870990, C536871046, C536871165 ) tablespace <tablespace_name> parallel 7 nologging; alter <owner>.<index3_name> noparallel logging;
All of the indexes above produces an interesting result:
Elapsed: 00:00:00.91
Execution Plan
----------------------------------------------------------
Plan hash value: 1653162645
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21246 | 7842K| | 21937 (1)| 00:04:24 |
| 1 | SORT ORDER BY | | 21246 | 7842K| 16M| 21937 (1)| 00:04:24 |
|* 2 | HASH JOIN | | 21246 | 7842K| 11M| 20222 (1)| 00:04:03 |
|* 3 | HASH JOIN | | 52055 | 10M| 5480K| 4397 (1)| 00:00:53 |
|* 4 | INDEX FAST FULL SCAN| <index1_name>| 55510 | 4824K| | 3387 (1)| 00:00:41 |
|* 5 | INDEX RANGE SCAN | <index2_name>| 46320 | 5835K| | 433 (1)| 00:00:06 |
|* 6 | INDEX FAST FULL SCAN | <index3_name>| 1128K| 172M| | 6061 (1)| 00:01:13 |
------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15761 consistent gets
0 physical reads
0 redo size
1111 bytes sent via SQL*Net to client
481 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
The number of gets to generate the resultset does not really change but the execution time. The reason is that the query is able to get all of the data required to build the result set from the indexes. It's not required to access one of the tables and that's a benefit because the response time seems to be much more stable if using this strategy.
It makes also sense to analyze the tables after index creation. That's not a must. In most cases the CBO prefer the new created indexes. It's not official documented but creation time and time of last analysis of an index seem to have effects at the plans. So there will be different results when analyzing a table with all dependent indexes or only some of the indexes:
analyze table <owner>.t1951 estimate statistics sample 5 percent for all indexed columns; analyze table <owner>.t1978 estimate statistics sample 5 percent for all indexed columns; analyze table <owner>.t1975 estimate statistics sample 5 percent for all indexed columns;
Response time of the query without these indexes variied between 0,9 seconds and 19 seconds. The reason for that are the Full Table Scan and the caching strategy of Oracle when a table was read using a full scan. Oracle treats indexes full scans in a different way. It makes sense to avoid index full scans because they can flood the cache. The system here have a buffer cache of 24G and most blocks in the cache are unused. That's the reason why this strategy is usefull.
If you want to analyze your cache efficiency than use the scripts
fsora buffer_cold_blocks -- Shows the number of unused blocks in a cache (TCH=0) fsora buffer_usage -- Shows segments used by cache and size of caches and usage of caches (TCH=0)
of OraForecast.com performance tool Fast Small Oracle Remote Analyzer.
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
- If you need further information or assistance with performance tuning or PL/SQL development feel free to contact mailto:performance@oraforecast.com
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
