-
stubborn query
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
-
I would start by rewriting the query as such.
Code:
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.
-
WF_PMTAUX should be the subsidy table.
-
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?
-
This is a 817 database , the inner join syntax does not work.Any ideas.
Thanks
Pete
-
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.
Code:
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;
-
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
-
Have all of the tables and indexes been analyzed?
-
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...
svk
-
===
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|