stubborn query
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: stubborn query

  1. #1
    Join Date
    Apr 2001
    Posts
    112

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    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.
    this space intentionally left blank

  3. #3
    Join Date
    Apr 2001
    Posts
    112
    WF_PMTAUX should be the subsidy table.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    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 space intentionally left blank

  5. #5
    Join Date
    Apr 2001
    Posts
    112
    This is a 817 database , the inner join syntax does not work.Any ideas.

    Thanks
    Pete

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    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;
    this space intentionally left blank

  7. #7
    Join Date
    Apr 2001
    Posts
    112
    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

  8. #8
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    Have all of the tables and indexes been analyzed?

  9. #9
    Join Date
    Jul 2000
    Posts
    521
    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

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ===
    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
  •  



Click Here to Expand Forum to Full Width