stubborn query

[hrishy]

Originally posted by 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

Hi

Your code should be something like

where subsidy.settlementdt
between 
to_date('03/01/2004','MM/DD/YYYY')
and 
to_date('03/04/2004','MM/DD/YYYY')-1/24/60/60

instead of

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

and is there a index on SettlementDt has it been analyzed ?

regards
Hrishy

[tansdot]

I managed to get rid of the trunc and have created partition on the subsidy on the settlementdt column.PerfoRmance does increase but the issue is that the execution plan and the response time changes with input values for the settlementdt in the between clause
To give you an idea of the data in subsidy table:
SELECT COUNT(1) , TO_CHAR(SETTLEMENTDT,'YYYY') FROM subsidy WHERE TO_CHAR(SETTLEMENTDT,'YYYY') IN ('2000','2001','2002','2004','2005','2006','2007','2008','2009','2010','2003')


COUNT(1) TO_C
--------- ----
174 2000
8737 2001
53928 2002
5354892 2004
2375605 2005
240 2006
53 2007
35 2008
13 2009
22 2010
2178495 2003

Index exists on the settlementdt and the unique key (pmtid,settlementdt)
oN pAYMENT

SELECT /*+ FIRST_ROWS index(pAYMENT XPKPAYMENT ) */ 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=SUBSIDY.PmtId
AND payment.Acctid = account.Acctid
AND users.Billerid = account.Billerid
AND subsidy.SettlementDt BETWEEN
TO_DATE('03/01/2004','MM/DD/YYYY') AND TO_DATE('03/15/2005', || ' 23:59:59', 'mm/dd/yyyy hh24:mi:ss')
GROUP BY subsidy.SettlementDt



The execution plan for 03/01/04 and 03/04/04 returns 4 rows. Elapsed time 38
sec. The non-optimized version took 6 minutes.

Elapsed: 00:00:37.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=105277 Car
d=10 Bytes=840)

1 0 SORT* (GROUP BY) (Cost=105277 Card=10 Bytes=840) :Q349005
2 1 SORT* (GROUP BY) (Cost=105277 Card=10 Bytes=840) :Q349004
3 2 HASH JOIN* (Cost=105259 Card=992 Bytes=83328) :Q349004
4 3 TABLE ACCESS* (FULL) OF 'USERS' (Cost=1 Card= :Q349001
3 Bytes=15)

5 3 HASH JOIN* (Cost=105257 Card=12240 Bytes=966960) :Q349003
6 5 NESTED LOOPS* (Cost=102209 Card=50929 Bytes=300481 :Q349002
1)

7 6 INDEX* (FAST FULL SCAN) OF 'TEST3_SETTLE_PMTAUX' :Q349002
(UNIQUE) (Cost=351 Card=50929 Bytes=1273225)

8 6 TABLE ACCESS* (BY INDEX ROWID) OF 'PAYMENT' (Cost=
:Q349002
2 Card=2498909 Bytes=84962906)

9 8 INDEX* (UNIQUE SCAN) OF 'XPKPAYMENT' (UNIQUE) (C
:Q349002
ost=1 Card=2498909)

10 5 TABLE ACCESS* (FULL) OF 'ACCOUNT' (Cost=2880 Card=1 :Q349000
037774 Bytes=20755480)



1 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0,SUM(SYS_OP_CSR(A1
.C1,0)),COUNT(SYS_OP_CSR(A1.C1,1)),S

2 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C1 C0,SYS_OP_MSR(SUM
(A1.C4),COUNT(A1.C4),SUM(DECODE(A1.C

3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_FROM_SERIAL
5 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
A2.C1 C0,A1.C1 C1,A1.C2 C2,A1.C3 C3

6 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "XPKPAYMENT") */ A2."ACCTID" C0,A

7 PARALLEL_COMBINED_WITH_PARENT
8 PARALLEL_COMBINED_WITH_PARENT
9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_FROM_SERIAL


Statistics
----------------------------------------------------------
15617 recursive calls
2066 db block gets
378656 consistent gets
111356 physical reads
144128 redo size
528 bytes sent via SQL*Net to client
996 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
59 sorts (memory)
2 sorts (disk)
3 rows processed


The execution plan for 03/04/04 and 03/15/05 returns 275 rows.Elapsed time 15
minutes( not under the one minute limit).

There's a lot of data in the monthly partitions for the year 2004 and 2005 as
compared to the data for 03/01/04 and 03/04/04.

275 rows selected.

Elapsed: 00:15:20.09

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2083968 Ca
rd=331 Bytes=27804)

1 0 SORT* (GROUP BY) (Cost=2083968 Card=331 Bytes=27804) :Q352005
2 1 SORT* (GROUP BY) (Cost=2083968 Card=331 Bytes=27804) :Q352004
3 2 HASH JOIN* (Cost=2083690 Card=20215 Bytes=1698060) :Q352004
4 3 TABLE ACCESS* (FULL) OF 'USERS' (Cost=1 Card= :Q352001
3 Bytes=15)

5 3 HASH JOIN* (Cost=2083688 Card=249313 Bytes=19695727) :Q352003
6 5 TABLE ACCESS* (FULL) OF 'ACCOUNT' (Cost=2880 Card=1 :Q352000
037774 Bytes=20755480)

7 5 NESTED LOOPS* (Cost=2075666 Card=1037345 Bytes=612 :Q352002
03355)

8 7 PARTITION RANGE* (ITERATOR) :Q352002
9 8 INDEX* (FAST FULL SCAN) OF 'TEST3_SETTLE_PMTAU :Q352002
X' (UNIQUE) (Cost=976 Card=1037345 Bytes=25933625)

10 7 TABLE ACCESS* (BY INDEX ROWID) OF 'PAYMENT' (Cost=
:Q352002
2 Card=2498909 Bytes=84962906)

11 10 INDEX* (UNIQUE SCAN) OF 'XPKPAYMENT' (UNIQUE) (C
:Q352002
ost=1 Card=2498909)



1 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0,SUM(SYS_OP_CSR(A1
.C1,0)),COUNT(SYS_OP_CSR(A1.C1,1)),S

2 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C2 C0,SYS_OP_MSR(SUM
(A1.C5),COUNT(A1.C5),SUM(DECODE(A1.C

3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_FROM_SERIAL
5 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) SW
AP_JOIN_INPUTS(A2) */ A2.C1 C0,A1.C6

6 PARALLEL_FROM_SERIAL
7 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "XPKBCPMT") */ A2."ACCTID" C0,A

8 PARALLEL_COMBINED_WITH_PARENT
9 PARALLEL_COMBINED_WITH_PARENT
10 PARALLEL_COMBINED_WITH_PARENT
11 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
11463 recursive calls
2429 db block gets
17304285 consistent gets
4994667 physical reads
219620 redo size
17219 bytes sent via SQL*Net to client
2238 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
28 sorts (memory)
2 sorts (disk)
275 rows processed


Any ideas why would the plan be changing for diff input values (settlementdt) and thus slowing the query?

Thanks

[davey23uk]

post it properly using the [ code ] and [ / code ] tags (remove the spaces) it is unreadable otherwise

[abhaysk]

108 posts still dunoo how to post??