Re: Performance Difference Between != and <>?

I missed the original response to my post - so I've picked it up from here.

Is the phenomenon totally reproducible ?

Did you check the filter_predicates and access_predicates of the plan, or just the structure. I don't expect any difference, but a change in predicate order can result in a significant change in CPU usage if you are unlucky.

If there is no difference there, then enable rowsource statistics (alter session set "_rowsource_execution_statistics"=true) and run the queries, then grab the execution plan from V$sql_plan and join to v$sql_plan_statistics to see if any of the figures about last_starts, last_XXX_buffer_gets, last_disk_reads, last_elapsed_time give you a clue about where the time went.

If you are on 10gR2 there is a /*+ gather_plan_statistics */ hint you can use instead of the "alter session".

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: "Allen, Brandon" <Brandon.Allen_at_OneNeck.com> To: <srcdco_at_rit.edu>; <oracle-l_at_freelists.org> Sent: Monday, September 25, 2006 6:17 PM Subject: RE: Performance Difference Between != and <>?

Scott,

Have you tried going beyond just explain & autotrace and running a full 10046 level 12 trace to see where the slower version is spending its time? This might shed some light on the subject, plus - be sure to verify that the explain plans are exactly the same in the 10046 trace file (not the ones generated with the EXPLAIN= option), and in v$sqlplan. There are some "features" of autotrace and explain that can cause it to not give you an accurate explain plan.

Regards,
Brandon

> -----Original Message-----
> Jonathan,
> Thank you for your answer. We did do an explain plan on
> both versions of the statement and they were identical, which
> is what is so puzzling about this.

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

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




-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.12.8/455 - Release Date: 22/09/2006


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