Re: Performance Problem

<jatinder.1975_at_gmail.com> wrote in message news:1145618906.269688.242790_at_z34g2000cwc.googlegroups.com...
> Hello All,
>
> I am facing a very weired problem in performance. The particular query
> is running on and on .... I think I should try Full Scan on tables
> Particularly Voucher Tables and Join should be Hash Join rather than
> nested. Any help would be greatly appreciated.
>
> Below is the query.
> SELECT
There are some highly suspect cardinalities coming out of that execution plan - is there really only one row in table BR.SBB_IMPL_BRS ? What do your table statistics look like - are they true ?

Since you have a UNION ALL, you
could start by running the two halves
separately to see if it is just one of
them that is causing the problem.

Since you have what seems to be a user-defined function in the where clause, you could check if this is part of the overload.

The Cartesian merge join in the plan is something to be suspicious of, but it could have appeared because the optimizer thinks that the first table will return only one row.

General guideline give each table an alias and use table aliases with the columns - it makes it possible to decide which tables are being joined, and whether you have a scoping error in subqueries.

-- 
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