Tuning Query

[Nayan Parmar]

Hi All,

 

I am using Oracle 9i.

I have some reports developed in Reports 6i which run very slow.

When I run the query in TOAD, it’s taking too much time for displaying result.

 

Can anybody suggest me how to tune this query ?

 

SELECT DISTINCT c.AccName, SUBSTR (a.VouNo, 3) VouNo,

                a.VouDate, a.TranType, b.ChequeNo,

                b.ChequeDate,

                DECODE (BalType, 'Debit', Amount, 0) Debit,

                DECODE (BalType, 'Credit', Amount, 0) Credit,

                d.DocNo, d.DocDate, a.Remark

           FROM TranDetails a,

                ChequeDetails b,

                AccMaster c,

                TranDocDetails d

          WHERE c.AccCode <> :p_BankAccCode

            AND a.VouDate BETWEEN :st_date AND :end_date

            AND a.CompanyNo = c.CompanyNo

            AND a.CompanyNo = b.CompanyNo(+)

            AND a.VouNo = b.VouNo(+)

            AND a.VouDate = b.VouDate(+)

            AND a.nu_serial_no = b.nu_serial_no(+)

            AND a.nu_serial_no = d.nu_serial_no(+)

            AND a.AccCode = c.AccCode

            AND a.TranType IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')

            AND NVL (a.SecFlag, 'N') = 'Y'

            AND d.VouNo(+) = a.VouNo

            AND d.VouDate(+) = a.VouDate

            AND c.CompanyNo = :p_CompanyNo

            AND d.CompanyNo(+) = :p_CompanyNo

            AND    a.CompanyNo

                || a.VouNo

                || TO_CHAR (a.VouDate, 'dd-mm-yyyy') IN (

                   SELECT    d.CompanyNo

                          || d.VouNo

                          || TO_CHAR (d.VouDate, 'dd-mm-yyyy')

                     FROM TranDetails d

                    WHERE d.AccCode = :p_BankAccCode

                      AND NVL (d.SecFlag, 'N') = 'Y'

                      AND d.CompanyNo = :p_CompanyNo)

       ORDER BY 3, 2

 

 

Thanks in advance.

Nayan

 

 

[fitzj...@cox.net]

Comments embedded.

On Dec 5, 11:25 pm, "Nayan Parmar" <parmarna...@gmail.com> wrote:
> Hi All,
>
> I am using Oracle 9i.
>

Now tell us WHICH release of 9i you're using. That would be all four
numbers reported by SQL*Plus when you connect.

> I have some reports developed in Reports 6i which run very slow.
>

My dog doesn't run very fast, either, but he's getting old.

> When I run the query in TOAD, it's taking too much time for displaying
> result.

Define 'too much time for displaying result'.

>
> Can anybody suggest me how to tune this query ?
>

No, as you haven't provided enough information, such as the full
Oracle release you're using, descriptions of any indexes created on
these tables, whether or not you have statistics computed on these
objects or the explain plan for this query.

Provide at least the information I mentioned above if you want anyone
to assist you further with this 'problem'.


David Fitzjarrell

[Charles Hooper]

As David points out, the Oracle version number (for example 9.2.0.4)
is important, as it determines what types of transformations Oracle
may use to help improve the query performance. First, a slight change
in the formatting to make it easier for me to read (you will need to
fix the DECODE statements):
SELECT DISTINCT
AM.ACCNAME,
SUBSTR (TD.VOUNO, 3) VOUNO,
TD.VOUDATE,
TD.TRANTYPE,
CD.CHEQUENO,
CD.CHEQUEDATE,
DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
TDD.DOCNO,
TDD.DOCDATE,
TD.REMARK
FROM
TRANDETAILS TD,
CHEQUEDETAILS CD,
ACCMASTER AM,
TRANDOCDETAILS TDD
WHERE
AM.ACCCODE <> :P_BANKACCCODE
AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
AND TD.COMPANYNO = AM.COMPANYNO
AND TD.COMPANYNO = CD.COMPANYNO(+)
AND TD.VOUNO = CD.VOUNO(+)
AND TD.VOUDATE = CD.VOUDATE(+)
AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
AND TD.ACCCODE = AM.ACCCODE
AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
AND NVL (TD.SECFLAG, 'N') = 'Y'
AND TDD.VOUNO(+) = TD.VOUNO
AND TDD.VOUDATE(+) = TD.VOUDATE
AND AM.COMPANYNO = :P_COMPANYNO
AND TDD.COMPANYNO(+) = :P_COMPANYNO
AND TD.COMPANYNO || TD.VOUNO || TO_CHAR (TD.VOUDATE, 'DD-MM-YYYY')
IN
(SELECT
TD2.COMPANYNO || TD2.VOUNO || TO_CHAR (TD2.VOUDATE, 'DD-MM-
YYYY')
FROM
TRANDETAILS TD2
WHERE
TD2.ACCCODE = :P_BANKACCCODE
AND NVL (TD2.SECFLAG, 'N') = 'Y'
AND TD2.COMPANYNO = :P_COMPANYNO)
ORDER BY
3,
2;

In the above, you are concatenating three columns into a single value,
and then trying to determine if the same concatenated value exists in
a table. A DBMS_XPLAN would probably show many, many full table scans
of the TRANSDETAILS table.

If we just list the three columns, and use TRUNC rather than TO_CHAR,
we may be able to take advantage of an index on those columns, and
avoid the CPU overhead of a data type conversion of a DATE column to a
character data type. You could use an EXISTS syntax instead, which
might be more efficient (not shown):
SELECT DISTINCT
AM.ACCNAME,
SUBSTR (TD.VOUNO, 3) VOUNO,
TD.VOUDATE,
TD.TRANTYPE,
CD.CHEQUENO,
CD.CHEQUEDATE,
DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
TDD.DOCNO,
TDD.DOCDATE,
TD.REMARK
FROM
TRANDETAILS TD,
CHEQUEDETAILS CD,
ACCMASTER AM,
TRANDOCDETAILS TDD
WHERE
AM.ACCCODE <> :P_BANKACCCODE
AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
AND TD.COMPANYNO = AM.COMPANYNO
AND TD.COMPANYNO = CD.COMPANYNO(+)
AND TD.VOUNO = CD.VOUNO(+)
AND TD.VOUDATE = CD.VOUDATE(+)
AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
AND TD.ACCCODE = AM.ACCCODE
AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
AND NVL (TD.SECFLAG, 'N') = 'Y'
AND TDD.VOUNO(+) = TD.VOUNO
AND TDD.VOUDATE(+) = TD.VOUDATE
AND AM.COMPANYNO = :P_COMPANYNO
AND TDD.COMPANYNO(+) = :P_COMPANYNO
AND (TD.COMPANYNO,TD.VOUNO,TRUNC(TD.VOUDATE)) IN
(SELECT
TD2.COMPANYNO,
TD2.VOUNO,
TRUNC(TD2.VOUDATE)
FROM
TRANDETAILS TD2
WHERE
TD2.ACCCODE = :P_BANKACCCODE
AND NVL (TD2.SECFLAG, 'N') = 'Y'
AND TD2.COMPANYNO = :P_COMPANYNO)
ORDER BY
3,
2;

Let's transform the subquery into an inline view, which will sometimes
help improve performance (usually a very noticeable on Oracle 8i).
Such a transformation may be performed by Oracle automatically:
SELECT DISTINCT
AM.ACCNAME,
SUBSTR (TD.VOUNO, 3) VOUNO,
TD.VOUDATE,
TD.TRANTYPE,
CD.CHEQUENO,
CD.CHEQUEDATE,
DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
TDD.DOCNO,
TDD.DOCDATE,
TD.REMARK
FROM
TRANDETAILS TD,
CHEQUEDETAILS CD,
ACCMASTER AM,
TRANDOCDETAILS TDD,
(SELECT DISTINCT
TD2.COMPANYNO,
TD2.VOUNO,
TRUNC(TD2.VOUDATE) VOUDATE
FROM
TRANDETAILS TD2
WHERE
TD2.ACCCODE = :P_BANKACCCODE
AND NVL (TD2.SECFLAG, 'N') = 'Y'
AND TD2.COMPANYNO = :P_COMPANYNO) TD2
WHERE
AM.ACCCODE <> :P_BANKACCCODE
AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
AND TD.COMPANYNO = AM.COMPANYNO
AND TD.COMPANYNO = CD.COMPANYNO(+)
AND TD.VOUNO = CD.VOUNO(+)
AND TD.VOUDATE = CD.VOUDATE(+)
AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
AND TD.ACCCODE = AM.ACCCODE
AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
AND NVL (TD.SECFLAG, 'N') = 'Y'
AND TDD.VOUNO(+) = TD.VOUNO
AND TDD.VOUDATE(+) = TD.VOUDATE
AND AM.COMPANYNO = :P_COMPANYNO
AND TDD.COMPANYNO(+) = :P_COMPANYNO
AND TD.COMPANYNO=TD2.COMPANYNO
AND TD.VOUNO=TD2.VOUNO
AND TRUNC(TD.VOUDATE)=TD2.VOUDATE
ORDER BY
3,
2;

Is it really necessary to reference the TRANDETAILS table twice? Will
the following work?:
SELECT DISTINCT
AM.ACCNAME,
SUBSTR (TD.VOUNO, 3) VOUNO,
TD.VOUDATE,
TD.TRANTYPE,
CD.CHEQUENO,
CD.CHEQUEDATE,
DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
TDD.DOCNO,
TDD.DOCDATE,
TD.REMARK
FROM
TRANDETAILS TD,
CHEQUEDETAILS CD,
ACCMASTER AM,
TRANDOCDETAILS TDD
WHERE
AM.ACCCODE <> :P_BANKACCCODE
AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
AND TD.COMPANYNO = AM.COMPANYNO
AND TD.COMPANYNO = CD.COMPANYNO(+)
AND TD.VOUNO = CD.VOUNO(+)
AND TD.VOUDATE = CD.VOUDATE(+)
AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
AND TD.ACCCODE = AM.ACCCODE
AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
AND NVL (TD.SECFLAG, 'N') = 'Y'
AND TDD.VOUNO(+) = TD.VOUNO
AND TDD.VOUDATE(+) = TD.VOUDATE
AND AM.COMPANYNO = :P_COMPANYNO
AND TDD.COMPANYNO(+) = :P_COMPANYNO
AND TD.ACCCODE = :P_BANKACCCODE
AND NVL (TD.SECFLAG, 'N') = 'Y'
AND TD.COMPANYNO = :P_COMPANYNO
ORDER BY
3,
2;

Why do you need to use the DISTINCT clause - is that a sign that you
do not have sufficient joins between the tables? It might be helpful
to post a DBMS_XPLAN for the query.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

[Nikhil Chavan]

Better to do index on "AccCode", "TransType" etc.

---

Sent from Yahoo! - a smarter inbox.

[Peter Teoh]

Sorry if my comments is not correct, or cannot be applied currently,
just based on my intuitive feeling:

> Nayan

Approximately, if there are n rows on average for all the tables, and
if there are 3 AND clause, then nxnxn rows have to be evaluated, minus
all the different optimization features like indexes etc. From
above, we can see there are so many AND clause, and so many of them
are from the same table. Ideally, there should be ONE AND CLAUSE for
each PAIR of table, assuming fully normalized scenario.

If given a chance, I will redesign the table again, normalizing it
further (meaning REMOVING MORE COLUMNS from each tables, so as to
decrease the interdependencies). More tables may be created in
between.

Just my personal opinions :-).

[fitzj...@cox.net]

> ___________________________________________________________
> Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
> now.http://uk.answers.yahoo.com/- Hide quoted text -
>
> - Show quoted text -

No one in this group has any knowledge of what indexes exist on these
tables, so to suggest to the OP that he 'build index X on ...' is, in
my mind, baseless 'advice'. You're guessing, and it shows.

When, and if, the OP decides to post the information I requested
(explain plan, table structures, indexes built) THEN such suggestions
can be made.


David Fitzjarrell

[Peter Teoh]

Relooking at the query again, I thought something is illogical, or
buggy - PLEASE ENLIGHTEN ME :-):

On Dec 6, 1:25 pm, "Nayan Parmar" <parmarna...@gmail.com> wrote:

First, look at the following (+) outer joins - is it correctly
written?

> AND a.VouNo = b.VouNo(+)
>
> AND a.VouDate = b.VouDate(+)
>

This means that in b, VouNo and VouDate may be NULL, but comparison
will still return TRUE.
Therefore, looking at the select statement:

> SELECT DISTINCT c.AccName, SUBSTR (a.VouNo, 3) VouNo,
>
> a.VouDate, a.TranType, b.ChequeNo,
>
> b.ChequeDate,
>

the CheckDetails's b.ChequeDate is in output,irregardless whether all
the conditions for b exists or not (noticed that b's column is always
outer join (+) with other tables' column?). The b.CheckqueDate in
SELECT statement don't quite make sense for me, if all the matching
for b's column does not exists, but still will be delivered to the
output by virtue of the outerjoin.