Re: Puzzing performance results with SQL

s_becker_at_voila.fr (Samuel Becker) wrote in message news:<acf86b1.0401300800.1834aae6_at_posting.google.com>...
> Hi there
>
> I use Oracle 8.1.7 and i have a strong difference of performance
> when i execute the two following SQL queries which are very similar:
>
> Query1:
>
> SELECT t1.TITLE, t1.ISBN, t1.PRICE, t1.PUBLISHER_ID
> FROM TAUTHOR t0, TBOOK_AUTHOR t2, TBOOK t1
> WHERE ((t0.LAST_NAME LIKE '%XXX-AUTHOR-3_1-24_1-YYY%')
> AND ((t2.ISBN = t1.ISBN) AND (t0.AUTHOR_ID = t2.AUTHOR_ID)))
>
>
> Query2:
> SELECT t1.TITLE, t1.ISBN, t1.PRICE, t1.PUBLISHER_ID
> FROM TBOOK t1, TAUTHOR t0, TBOOK_AUTHOR t2
> WHERE ((t0.LAST_NAME LIKE '%XXX-AUTHOR-3_1-24_1-YYY%')
> AND ((t2.ISBN = t1.ISBN) AND (t0.AUTHOR_ID = t2.AUTHOR_ID)))
>
> The "query1" takes 1 second to execute whereas "query2" takes less than
> 300ms !
> The only difference between these two queries is just in the "FROM" clause !
>
> Any idea ?
> Thanks.

Read up on explain plan in this newsgroup and in the documentation and on metalink. I'm sure others will post how to get it. In addition to what they say, there is also a way to get to it in the GUI OEM. In 9.2, one way is to run top sessions, click on the desired session and drill down to the SQL, then drill down to the explain plan. I don't remember exactly how the older OEM version did it, but it was something like double-click on the session and then look for the explain plan.

Also check what your optimizer mode is. If CHOOSE or CBO, check your statistics.

jg

--
@home.com is bogus.
Stress test:  http://www.signonsandiego.com/news/uniontrib/fri/news/news_1n30flush.html