| Quering in join tables with contains (too slow!) [message #228150] |
Sun, 01 April 2007 14:05  |
inapal
Messages: 14
Registered: September 2006
|
Junior Member |
|
|
I’m experiencing performance issues accessing full text index from a Java app.
I have a Java class (running over Oracle IAS 10g) that connects to an Oracle 9i DB, using JDBC and runs the following sentence
SELECT COUNT(*) FROM CSFAL
INNER JOIN DOCF ON (CSFAL.NUMB = DOCF.NUMB AND CSFAL.DATS = DOCF.DATS)
WHERE CONTAINS(CSFAL.FT, 'term1', 1) >0 AND CONTAINS(DOCF.BIN, 'term2', 2) > 0
The strings 'term1' and 'term2' are very common. The first one exist 8000 times in the full text indexes; and 125000 the second one.
When the query starts, Oracle hung and never concludes it resolution( I had stopped it manually from the Manager after 2hs of execution).
If the same SQL sentence is run from the SQLPlus, the result show up after 15 seconds.
What is strange, is that even the instruction is exactly the same, Oracle shows two completely different execution plans for each case. On the first case, where the query is lunched from the Java class (which is the one that take so long), the execution plan shows a Cartesian product between the two tables, which is what make the resolution extremely slow. This Cartesian product doesn’t exist on the execution plan created by running the query from SQLPlus, taking only 15 seconds.
If I delete the Oracle’s statistics, the execution plan of the SQLPlus becomes the same as the Java class’s ( with the Cartesian product), never reaching the end, and consuming almost all the CPU resources assigned to Oracle.
If I re create the statistics, the query run from SQLPlus uses the original execution plan, and is solved quickly. But the one run from the Java class, still follows the execution plan with the Cartesian product and never completes it execution.
For some reason I don’t know, it seems that the query run by the Java class through JDBC ignores all the statistics and solves the instruction in a very inefficient way. The SQLPlus provided by the Manager, is also developed in Java and I assume that it uses JDBC as well.
Does somebody have any idea/suggestion for me to be able to resolve this query from Java in the optimal way?
Thanks,
Ignacio.
|
|
|
|
|
|
|
|
| Re: Quering in join tables with contains (too slow!) [message #228311 is a reply to message #228304] |
Mon, 02 April 2007 09:15  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
If you provide literal values, Oracle knows whether or not these values are distinctive. If you use bind variables, it does not.
Simulate using binds by defining variables in sqlplus. I use this script:
set lines 200
set pages 10000
spool xplan.lst
delete plan_table where statement_id = 'MY_XPLAN'
/* INSERT BINDS HERE */
variable b1 varchar2
variable b2 varchar2
variable b3 varchar2
variable b4 number
variable b5 number
explain plan set statement_id = 'MY_XPLAN'
for
/* INSERT STATEMENT HERE */
select <your_column>
from <your_table>
where <your_second_column> = :b1
and <your_date_column> = to_date(:b2) -- Don't forget to do a to_date if your bind has datatype DATE!!!!!!
/
set feed off
select * from table( dbms_xplan.display(null, 'MY_XPLAN'));
set feed on
spool off
Cool eh, that db stuff? 
[Updated on: Mon, 02 April 2007 09:16] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
