RE: FBI Not Used - Oracle Spatial
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 13 Mar 2006 13:51:41 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Charlotte Hammond
Sent: Monday, March 13, 2006 12:39 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
