(re) Time to read 6000 (block size 2k) blocks
- From: ryan.gaffuri@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 06 Aug 2004 15:45:38 +0000
When tuning a query in the sub 1 second range you should NOT look at response
time as an indicator of improvement. You can run a query 10 times and get 10
different sub 1 second response times because its such a small amount of time.
You can also signfiicantly improve the query by reducing logical IOs with
little to know response time improvement. Why? Well, its overall stress on the
system, but the response of the query in isolation is so fast, that you will
not see an improvement in its response time.
Lets look at the query:
AND P.SEARCH_COMPANY_NAME LIKE 'ACME%
like statements can often radically increase LIOs all by themselves. How
selective is 'ACME'? If it is not very selective, then you are returning a
large number of records. According to your explain plan this is the originating
filter(you can tell this by your index range scan). If you look at your explain
plan you can see how many rows Oracle is returning from this operation.
There are two things to check here:
1. What happens if you choose a far more selective value than ACME? Do your
LIOs drop significantly?
2. Look at column ordering in the indexes. They should be:
(I do not know what table this is from 'HSBC_USER_CATEGORY ')
P.SEARCH_COMPANY_NAME, P.PROFILEDUSERID
E.CUSTOMERID ,.PROFILEDUSERID
You also have the 'HSBC_USER_CATEGORY. If this is in table 'E', the column
should come after CUSTOMERID,
If it is in TABLE 'P' it should come first.
The mistake I typically see with index column ordering is that people put the
'join' column first. This is your least selective column. By default your
ordering should be
1. Columns with '='
2. Columns with 'IN' and 'OR'
3. Columns with 'like'
4. Join Columns
2 and 3 can flip based on data distribution.
SELECT DISTINCT P.PROFILEDUSERID PROFILEDUSERID,
SEARCH_LAST_NAME,
SEARCH_FIRST_NAME
FROM PROFILEDUSER P ,
EXTENDEDATTRIBUTES E
WHERE P.PROFILEDUSERID = E.PROFILEDUSERID
AND P.SEARCH_COMPANY_NAME LIKE 'ACME%' ESCAPE '/'
AND E.CUSTOMERID = 'ABCDEFGH'
AND HSBC_USER_CATEGORY IN ('VAL1','VAL2')
AND ROWNUM < 150
ORDER BY SEARCH_LAST_NAME,SEARCH_FIRST_NAME
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » (re) Time to read 6000 (block size 2k) blocks
