RE: FBI Not Used - Oracle Spatial

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Mon, 13 Mar 2006 13:51:41 -0500
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF410506A7F319@usahm236.amer.corp.eds.com>

Does the job set any session parameters that could be interfering? Have you verified that no outline exists that could be interfering?

What about bind variable peeking? Are there histograms on this column? Is the data in sv_type column skewed?

HTH -- Mark D Powell --

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Charlotte Hammond Sent: Monday, March 13, 2006 12:39 PM
To: oracle-l_at_freelists.org
Subject: FBI Not Used - Oracle Spatial

Hi All,

I have a JDBC batch job which executes a series of inserts into an Oracle Spatial schema. There are a number of triggers which get fired during this load, and one piece of SQL is particularly time-consuming:

SELECT a.value_id
FROM mdsys.rdf_value$ a WHERE a.value_name.getURL() = :v_subject AND a.value_type = :sv_type

This should be using a function-based index, and it does suggest that it will do so when you execute an "EXPLAIN PLAN FOR" the statement:

SQL> explain plan for
2 SELECT a.value_id FROM mdsys.rdf_value$ a WHERE a.value_name.getURL() = :v_
subject AND a.value_type = :sv_type
3 /

Explained.

SQL> select operation,object_name
2 from plan_table
3 /

OPERATION                      OBJECT_NAME


------------------------------

------------------------------

SELECT STATEMENT
TABLE ACCESS                   RDF_VALUE$
INDEX                          RDF_VAL_NAMETY_IDX

Yet, tracing the JDBC job (run as same user), it reports a full table scan instead:

Misses in library cache during parse: 1
Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 46 (recursive depth: 1)

Rows Row Source Operation

1 TABLE ACCESS FULL RDF_VALUE$ (cr=378 pr=4 pw=0 time=269679 us)

This FTS is making things very slow. This is a 10gR2 database so "QUERY REWRITE" should not be necessary (I tried it anyway - no help).

If anyone could suggest why the JDBC job is not using the index, but explain plan is suggesting it should, I'd be immensely grateful.

(I've checked the stuff I can think of: no stats changes, no DDL changes, same user account).

Many thanks
Charlotte

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 13 2006 - 12:51:41 CST