stubborn query

[tansdot]

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 [GROUP BY]
10 HASH JOIN
1 USERS TABLE ACCESS [FULL]
9 MERGE JOIN
6 SORT [JOIN]
5 NESTED LOOPS
2 Subsidy TABLE ACCESS [FULL]
4 Payment TABLE ACCESS [BY INDEX ROWID]
3 XPKpayment INDEX [UNIQUE SCAN] -- on pmtid
8 SORT [JOIN]
7 Account TABLE ACCESS [FULL]

Description Cost Est.Rows Ret Est. KBytes Returned
1 This plan step retrieves all rows from table USERS 1 3 0.015
2 This plan step retrieves all rows from table WF_PMTAUX. 21,762 24,831 581.977
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 82,971.588
5 Nested Loops 71,424 24,831 1,406.443
6 This plan step Sort-join operation. 72,137 24,831 1,406.443
7 This plan step retrieves all rows from table account. 2,880 1,037,774 20,269.023
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 20,269.023
9 Merge Join. 96,027 5,968 454.594
10 Hash Join 96,037 484 39.23
11 Sort GROUP BY clause. 96,045 478 38.744
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( BTW there are a lot more indexes on these tables)

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


Please help.

Thanks

[gandolf989]

I would start by rewriting the query as such.

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

Then I would try to run it, fix my coding errors, and when it runs generate a new explain plan and see how long it takes to run. I am curious about how WF_PmtAux fits in. It must either be a table name or an alias for a table name, but you only use it once, never declaring where it comes from.

Then I would start looking at adding indexes. Would it be faster if you added a non-unique index for Masterbillerid on the users table? What about an index for Acctid on the account table? Is that the primary key for that table or an unindexed foreign key? What about a non-unique index for SettlementDt on the subsidy table? If you start adding indexes, you may want to do some stress testing to see if there is an adverse effect on the database for each index added.

[tansdot]

WF_PMTAUX should be the subsidy table.

[gandolf989]

Originally posted by tansdot
WF_PMTAUX should be the subsidy table.

I did figure that out, but you did not alias the subsidy table as WF_PMTAUX. What is the explain plan for my version of the query?

[tansdot]

This is a 817 database , the inner join syntax does not work.Any ideas.

Thanks
Pete

[gandolf989]

Originally posted by tansdot
This is a 817 database , the inner join syntax does not work.Any ideas.

Thanks
Pete

This is the 8.1.7 version.

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

[tansdot]

N0 gain still:
Here's the fixed sql and execution plan:
SELECT PmtDate,
SUM(ACHCount) AS ACHCount, SUM(ACHSubTot) AS ACHSubTot,
SUM(CCCount) AS CCCount, SUM(CCSubTot) AS CCSubTot,
COUNT(payment.PmtAmt) AS Count, SUM(payment.PmtAmt) AS Total
FROM
( SELECT PmtId, Acctid,pmtamt,
DECODE(payment.ProvId, 1, 1, 0) AS ACHCount,
DECODE(payment.ProvId, 1, PmtAmt, 0) AS ACHSubTot,
DECODE(payment.ProvId, 1, 0, 1) AS CCCount,
DECODE(payment.ProvId, 1, 0, PmtAmt) AS CCSubTot
FROM payment
WHERE LfcyclCd = 'SN' ) payment,
( SELECT Acctid,billerid
FROM account ) account,
( SELECT PmtId,to_char(settlementdt, 'MM/DD/YYYY') AS pmtdate
FROM subsidy
WHERE TRUNC(SettlementDt) BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY')
AND TO_DATE('03/03/2004','MM/DD/YYYY')
) subsidy,
( SELECT Billerid
FROM users
WHERE Masterbillerid = 3 ) users
WHERE payment.Acctid = account.Acctid
AND payment.PmtId = subsidy.PmtId
AND account.Billerid = users.Billerid
GROUP BY pmtdate;


12 SELECT STATEMENT
11 SORT [GROUP BY]
10 HASH JOIN
1 users TABLE ACCESS [FULL]
9 MERGE JOIN
6 SORT [JOIN]
5 NESTED LOOPS
2 subsidy TABLE ACCESS [FULL]
4 payment TABLE ACCESS [BY INDEX ROWID]
3 XPKpmT INDEX [UNIQUE SCAN] --on pmtid column
8 SORT [JOIN]
7 ACCounT TABLE ACCESS [FULL]


Thanks a lot for your help

[dbtoo]

Have all of the tables and indexes been analyzed?

[svk]

Looks like you are dealing with data volumes that are on high side. You will need to have perfect stats all the time so that the CBO chooses good plan all the time...can be quite a task.

If not already done : Try setting optimizer_index_cost_adj parameter to anywhere between 10 and 25. Its 100 by default. You can test the performance of the query by setting this parameter as session level.

Otherwise, can you post what are the indexes on each of the tables involved in this query ? That TRUNC on a date column in SUBSIDY table may be a trouble...

[tamilselvan]

===
AND TRUNC(subsidy.SettlementDt) BETWEEN TO_DATE('03/01/2004','MM/DD/YYYY') AND TO_DATE('03/03/2004','MM/DD/YYYY')
===

Remove the trunc function.
Change the code to
AND subsidy.SettlementDt
BETWEEN
TO_DATE('03/01/2004 00:00:00','MM/DD/YYYY HH24:MI:SS')
AND TO_DATE('03/03/2004 23:59:59','MM/DD/YYYY HH24:MI:SS')

Do you have an index on Settlementdt column? If not, create it.
And add a hint like /*+ LEADING(subsidy) */

Post the explain plan.

Tamil