Home » Developer & Programmer » JDeveloper, Java & XML » Quering in join tables with contains (too slow!)
Quering in join tables with contains (too slow!) [message #228150] Sun, 01 April 2007 14:05 Go to next message
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 #228192 is a reply to message #228150] Mon, 02 April 2007 01:04 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Are the two literals in you query actually bindvariables in Java?
How did you test this in sqlplus? By replacing these binds by literals?
That could explain the difference
Re: Quering in join tables with contains (too slow!) [message #228304 is a reply to message #228192] Mon, 02 April 2007 08:29 Go to previous messageGo to next message
inapal
Messages: 14
Registered: September 2006
Junior Member
Yes!
I'm using bindvariables in Java, and copying the query to SQLPlus with literals.

Which is the difference?

Ignacio.
Re: Quering in join tables with contains (too slow!) [message #228311 is a reply to message #228304] Mon, 02 April 2007 09:15 Go to previous message
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? Wink

[Updated on: Mon, 02 April 2007 09:16]

Report message to a moderator

Previous Topic: how to insert attributes in xml while generating from package dbms_xmlgen
Next Topic: How to get String and Integer values out from an Oracle OUT Parameter?
Goto Forum:
  


Current Time: Sat Dec 07 00:39:43 CST 2024