Oracle-L: Poor query performance on smaller tables

From: <Sherrie.Kubis_at_swfwmd.state.fl.us>
Date: Fri, 26 Sep 2003 05:59:45 -0800
Message-ID: <F001.005D12EA.20030926055945@fatcity.com>

Oracle 9.2.0.3.0
HP-UX 11i
block size 16K
db_file_multiblock_read_count 64

I have five groups of tables that have the same query issued to them. Table structures, indexes
are the same, just the data and the number of rows are different. The three tables that have more
than 3 million rows return results in an acceptable time, two tables that hover around 1 million and little
less take a very long time. The execution plans for all queries is the same.

The query comes from a third-party application and is quite ugly:

SELECT DISTINCT SDECREATOR.DATACOLLECTIONSTATIC.OBJECTID FROM

SDE_LOGFILE_DATA,SDECREATOR.DATACOLLECTIONSTATIC WHERE
SDE_LOGFILE_DATA.sde_row_id=SDECREATOR.DATACOLLECTIONSTATIC.shape AND
SDE_LOGFILE_DATA.LOGFILE_DATA_ID = (SELECT LOGFILE_DATA_ID FROM
SDE_LOGFILES WHERE LOGFILE_NAME = 'XX00000621_0' AND EXISTS (SELECT 'X'

FROM RODB.WMT022_SP WHERE sde_row_id = shape AND wmcollectdate BETWEEN to_date('5/1/1930','mm/dd/yyyy') AND to_date('9/15/2003','mm/dd/yyyy')))

This same query without the BETWEEN on the date column returns results on all five table sets quickly, so
this leads me to believe that it's the date column that is at issue. The date columns have a normal index with these stats. The two with BLEVEL of 1 are the problems.

select index_name, index_type, LEAF_BLOCKS, DISTINCT_KEYS, BLEVEL 2 from user_indexes
3 where index_name like '%_SP_DATE_%';

INDEX_NAME                     INDEX_TYPE                      LEAF_BLOCKS
DISTINCT_KEYS          BLEVEL
------------------------------ --------------------------- ---------------
--------------- ---------------
WMT022_SP_DATE_IX              NORMAL                                  686
22612               1
WMT023_SP_DATE_IX              NORMAL                                 2096
27037               2
WMT025_SP_DATE_IX              NORMAL                                 2299
23819               2
WMT026_SP_DATE_IX              NORMAL                                  581
9588               1
WMT027_SP_DATE_IX              NORMAL                                 2263
37498               2

The other weird thing about this queries is that parallel default on the WMT022_SP table to never return a result. I take it off, we get results, they are just not very good, somewhere around 4 minutes. The largest table WMT025_SP returns millisecond results with or without parallel. The next two largest, WMT023 and WMT027 work better with parallel than without.

I am stumped, and not sure what to look at next. Does anyone have any insights that could point me in the right direction?

Sherrie Kubis
Southwest Florida Water Management District 2379 Broad Street
Brooksville FL 34604-6899

Phone: (352) 796-7211, Ext. 4033
Fax: (352) 754-6776
Email: Mailto:Sherrie.Kubis_at_swfwmd.state.fl.us http://WaterMatters.org

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: Sherrie.Kubis_at_swfwmd.state.fl.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Sep 26 2003 - 08:59:45 CDT