Evidence

Here’s a nice example on the OTN database forum of Dom Brooks looking at the evidence.

• The query is slow – what does the trace say.
• There’s “row source execution” line that says we get 71,288 rows before doing a hash unique drops it to 3,429 rows.
• There’s a statement (upper case, bind variables as :Bn) in the trace file that has been executed 71,288 times
• A very large fraction of the trace file time is in the secondary statement
• There’s a user-defined function call in the original select list, before a ‘select distinct’.

Conclusion: the code should probably do a “distinct” in an inline view before calling the function, reducing the number of calls to the function from 71,288 to 3,429.

Footnote: There may be other efficiency steps to consider – I’m always a little suspicious of a query that uses “distinct”: possibly it’s hiding an error in logic, possibly it should be rewritten with an existence subquery somewhere, but sometimes it really is the best strategy. There’s are some unusual statistics names coming from autotrace in the OP’s system – I wonder if he’s installed one of Tanel Poder’s special library hacks.