Re: Performance Problem

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 25 Apr 2006 08:22:45 +0100
Message-ID: <Q8udnc-xybTXU9DZRVnygQ@bt.com>

<jatinder.1975_at_gmail.com> wrote in message news:1145711286.196867.33970_at_v46g2000cwv.googlegroups.com...
> Hi Lewis ,
> 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.
>
> I tried as you suggested and got that it is first part that is causing
> the problem. It just hangs.
> Number of rows in BR.SBB_IMPL_BRS is 6.
>
>> Thanks, Jatinder. You should be able to get a raw trace file that
>> shows what the session is doing/waiting on. Lookup 10046 trace
>> anywhere on google and you will get a million hits on it.
>>
>> Regards,
>>
>> Steve
>
> Steve
> I tried
> alter session 'set events 10046 context forever level 12'
> and the session got terminated after two hour .
> Could you Please tell me how to find skewed data table ??
>
> With Warm Regards
> Jatinder Singh
>

It's not really sensible to optimise a query remotely in 5 minutes - but if you'd like a guess:

The query execution plan shows two filters, one from a NOT IN subquery, and one from an IN subquery. Oracle operates filter subqueries at the end of the execution plan. Sometimes it is better to operate them (particularly NOT IN subqueries) earlier. The hint push_subq forces filter subqueries to be operated at the earliest possible moment. This may help. The hint goes after the select in the driving query in 8i and 9i, but has to be put into the relevant subqueries in 10g.

Before doing anything else - note the order in which Oracle visits the tables. Is this the way YOU would work through the tables if you were operating the query by hand. What do YOU know that the optimizer is missing. If there is an 'obvious' order (why, FOR EXAMPLE, does the query not visit voucher_master first and then voucher detail) then re-order the tables and add the ORDERED hint.

In passing, there is an oddity with the predicates on a.doc_br_cd - note the two conditions:

        A.doc_br_cd NOT IN
        (
        SELECT
            br_cd
        FROM BR.SBB_IMPL_BRS
        )
        OR TRUNC(VCHR_DATE) < '01-APR-05'

    )

    AND b.gl_cd LIKE '0807%'
    AND b.gl_cd = d.gl_cd
    AND A.doc_br_cd = '01'

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

Received on Tue Apr 25 2006 - 02:22:45 CDT