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:[EMAIL PROTECTED]
http://WaterMatters.org
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
