OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Stubborn Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Stubborn Query [message #122110]

Thu, 02 June 2005 21:46

tansdot
Messages: 2
Registered: June 2005

Junior Member

Hi
I have been trying to speed up the following query but havent been successfull.
I ran the timings on the following query using the first_rows hint and without it. The elapsed time was same ,. The query did not bring back the results till more than an

hour. We have a requirement that the query should response within 1 minutes or a timeout will happen.Im my case First_rows hint is not working. I've tried using all the

possible indexes to avoid the full table scan. When I do that the cost goes up and it takes more time to run. Please help
The query is as follows:

SELECT TO_CHAR(payment.SettlementDt, 'MM/DD/YYYY') AS PmtDate,
SUM(DECODE(payment.ProvId, 1, 1, 0)) as ACHCount,
SUM(DECODE(payment.ProvId,1, PmtAmt, 0)) as ACHSubTot, SUM(DECODE(payment.ProvId, 1, 0, 1)) as CCCount,
SUM(DECODE(payment.ProvId,1, 0, PmtAmt)) as CCSubTot,
COUNT(payment.PmtAmt) AS Count,SUM(payment.PmtAmt) as Total
FROM users, payment,subsidy, account
WHERE users.Masterbillerid = 3 and
payment.LfcyclCd='SN' and
payment.PmtId=WF_PmtAux.PmtId
AND payment.Acctid = account.Acctid
AND users.Billerid = account.Billerid
AND TRUNC(subsidy.SettlementDt) BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY') AND TO_DATE('03/03/2004','MM/DD/YYYY')
GROUP BY subsidy.SettlementDt

Table # of Rows
payment 10,000,000
account 1,000,000
users 108
subsidy 10,000,000

cost:96045

12 SELECT STATEMENT
11 SORT
10 HASH JOIN
1 USERS TABLE ACCESS
9 MERGE JOIN
6 SORT
5 NESTED LOOPS
2 Subsidy TABLE ACCESS
4 Payment TABLE ACCESS
3 XPKpayment INDEX -- on pmtid
8 SORT
7 Account TABLE ACCESS

Description Cost Est.Rows Ret
1 This plan step retrieves all rows from table USERS 1 3
2. This plan step retrieves all rows from table WF_PMTAUX. 21,762 24,831
3 This plan step retrieves a single ROWID from the B*-tree index XPKpayment. 1 2,498,909
4 This plan step retrieves rows from table Payment through ROWID(s) returned by an index. 2 2,498,909
5 Nested Loops 71,424 24,831
6 This plan step Sort-join operation. 72,137 24,831
7 This plan step retrieves all rows from table account. 2,880 1,037,774
8 This plan step accepts a row set (its only child) and sorts it in preparation for a merge-join 23,890 1,037,774
9 Merge Join 96,027 5,968
10 Hash Join 96,037 484
11 Sort GROUP BY clause 96,045 478
12 This plan step designates this statement as a SELECT statement.

To show the indexes on the tables that can be used plan With the rule hint:

13 SELECT STATEMENT
12 SORT
11 NESTED LOOPS
8 NESTED LOOPS
5 NESTED LOOPS
2 Payment TABLE ACCESS
1 XIF115BCPMT INDEX --on lfcyclcd column
4 Account TABLE ACCESS
3 ACCT1 INDEX --on account column
7 Subsidy TABLE ACCESS
6 XPKWF_subsidy INDEX -- on pmtid
10 Users TABLE ACCESS [BY INDEX ROWID]
9 XIF241users INDEX [RANGE SCAN -- ON MASTERBILLERID column

Have I hit a wall. Please help.

Report message to a moderator

Re: Stubborn Query [message #122114 is a reply to message #122110]

Thu, 02 June 2005 23:07

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

A shot in the dark here, but is there an index on subsidy.settlementdt? Is performance improved if you change the predicate involving the BETWEEN to the following?

 AND subsidy.settlementdt BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY')
                              AND TO_DATE('03/03/2004 23:59:59','MM/DD/YYYY HH24:MI:SS')

Report message to a moderator

Re: Stubborn Query [message #122553 is a reply to message #122114]

Tue, 07 June 2005 04:43

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

HI

Hv u got the statistics for these tables alredy collected & are they upto date?

use the following ordeing of tables in where claues:
users, account, payment,subsidy
Also place the filters after join conditions.

Then check & provide the plan generated.

Regds
Girish

Report message to a moderator

Re: Stubborn Query [message #122557 is a reply to message #122110]

Tue, 07 June 2005 04:57

nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait

Member

Can you provide us some info on these tables? something like heirarchy and relation b/w them?

Report message to a moderator

Re: Stubborn Query [message #122575 is a reply to message #122110]

Tue, 07 June 2005 06:35

nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait

Member

Try
set autot on

SELECT TO_CHAR (payment.settlementdt, 'MM/DD/YYYY') AS pmtdate,
       SUM (DECODE (payment.provid, 1, 1, 0)) AS achcount,
       SUM (DECODE (payment.provid, 1, pmtamt, 0)) AS achsubtot,
       SUM (DECODE (payment.provid, 1, 0, 1)) AS cccount,
       SUM (DECODE (payment.provid, 1, 0, pmtamt)) AS ccsubtot,
       COUNT (payment.pmtamt) AS COUNT, SUM (payment.pmtamt) AS total
  FROM payment, subsidy, ACCOUNT, users
 WHERE payment.acctid = ACCOUNT.acctid
   AND users.billerid = ACCOUNT.billerid
   AND users.masterbillerid = 3
   AND payment.lfcyclcd = 'SN'
   --AND payment.pmtid = wf_pmtaux.pmtid
   AND EXISTS (
          SELECT 1
            FROM subsidy
           WHERE subsidy.settlementdt BETWEEN TO_DATE ('03/01/2004',
                                                       'MM/DD/YYYY'
                                                      )
                                          AND TO_DATE ('03/03/2004',
                                                       'MM/DD/YYYY'
                                                      ))

Let me know what you up come with

Cheers,
NK

Report message to a moderator

Previous Topic:	How to read the execution plan
Next Topic:	URGENT:-Performance Tuning.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 09 05:56:10 CST 2025