Re: Performance Difference Between != and <>?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Sep 2006 21:04:52 +0100
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@xxxxxxxxxxx>
To: <srcdco@xxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
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.
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
