This discussion is archived
9 Replies Latest reply: Feb 2, 2012 7:34 AM by Justin Cave RSS

SQL tuning which uses View

914404 Newbie
Currently Being Moderated
I have used the below sql on a View .I will enter SEI_ACCT_NUMBER to get the respective ACCT_TRAN_ID.

It is taking longer time to run the query .ACCOUNT_TYPE_CD does not contain indexes.I do not have access rights to create indexes .

select
A.ACCT_TRAN_ID,
B.account_num as SEI_ACCT_NUMBER
from PACSAPP.TRAN_DETAIL_REPORT_VIEW A
INNER JOIN PACSAPP.TRAN_DETAIL_REPORT_VIEW B
          on A.REQUEST_ID = B.REQUEST_ID
          and A.ACCOUNT_TYPE_CD IN ('CHECKING','GL','SAVINGS')
          and B.ACCOUNT_TYPE_CD = 'TRUST'

The counts are below

select count(*) from PACSAPP.TRAN_DETAIL_REPORT_VIEW

2909725


select count(*) from PACSAPP.TRAN_DETAIL_REPORT_VIEW B
WHERE B.ACCOUNT_TYPE_CD = 'TRUST'

835354

select count(*) from PACSAPP.TRAN_DETAIL_REPORT_VIEW B
WHERE B.ACCOUNT_TYPE_CD = 'CHECKING'

320640


select count(*) from PACSAPP.TRAN_DETAIL_REPORT_VIEW B
WHERE B.ACCOUNT_TYPE_CD = 'GL'

16723

select count(*) from PACSAPP.TRAN_DETAIL_REPORT_VIEW B
WHERE B.ACCOUNT_TYPE_CD = 'SAVINGS'

530


Could you please help in improving the performance of this
  • 1. Re: SQL tuning which uses View
    Justin Cave Oracle ACE
    Currently Being Moderated
    What does "longer time" mean? Longer than what?

    What is the query plan? Is there an index on the ACCOUNT_NUM column in the underlying table that is queried from the PACSAPP.TRAN_DETAIL_REPORT_VIEW view?

    Justin
  • 2. Re: SQL tuning which uses View
    914404 Newbie
    Currently Being Moderated
    Thank you so much for your reply.I do not have rights to create plan table .I am using the Pl/sql developer.I ran explain plan and did not get the result

    We are using this query to get the SEI Account numbers .This is one block (cloumn)of our business objects report(reporting tool).The report should run in 2 minutes.Addition of this sql to my report is taking 10 minutes .Sorry for the confusion.

    I saw the primery key(PK) only on acct_tran_ID.I could not found on account_num

    FK on
    account_id,
    acct_tran_status_id,
    acct_tran_type_id,
    batch_id,
    payment_type_id,
    request_id,
    created_from_tran_id

    Edited by: user2556087 on Jan 31, 2012 1:48 PM
  • 3. Re: SQL tuning which uses View
    rp0428 Guru
    Currently Being Moderated
    Did you post the correct code?

    You are only quering one table and joining it to itself:
    from PACSAPP.TRAN_DETAIL_REPORT_VIEW A 
    INNER JOIN PACSAPP.TRAN_DETAIL_REPORT_VIEW B
    Same table 'PACSAPP.TRAN_DETAIL_REPORT_VIEW =' two aliases

    Since the conditions:
    on A.REQUEST_ID = B.REQUEST_ID
    and A.ACCOUNT_TYPE_CD IN ('CHECKING','GL','SAVINGS')
    and B.ACCOUNT_TYPE_CD = 'TRUST'
    are on the same table the ACCOUNT_TYPE_CD value can never be TRUST and CHECKING/GL/SAVINGS at the same time.
  • 4. Re: SQL tuning which uses View
    914404 Newbie
    Currently Being Moderated
    Yes ! I have to enter the SEI accounts and the result should be the respective acct tran id's.If we enter the trust account data ,it should give the respective checking account,savings account ,GL account info.It is based on a transaction table .So I aliased them .

    For a given request id we will get 4 different transactions.The initial transaction type code would be trust and the result could be any of the other type codes.

    Edited by: user2556087 on Jan 31, 2012 1:53 PM

    Example:
    select
    A.request_id,
    A.ACCT_TRAN_ID,
    B.account_num as SEI_ACCT_NUMBER,
    A.account_type_cd,
    B.account_type_cd
    from PACSAPP.TRAN_DETAIL_REPORT_VIEW A
    INNER JOIN PACSAPP.TRAN_DETAIL_REPORT_VIEW B
              on A.REQUEST_ID = B.REQUEST_ID
              and A.ACCOUNT_TYPE_CD IN ('CHECKING','GL','SAVINGS')
              and B.ACCOUNT_TYPE_CD = 'TRUST'
    and A.REQUEST_ID = '499488'


    REQUEST_ID................ACCT_TRAN_ID.......... SEI_ACCT_NUMBER......ACCOUNT_TYPE_CD..........ACCOUNT_TYPE_CD
    499488................................ 2250968................ 23781802................ CHECKING ................ TRUST

    Edited by: user2556087 on Jan 31, 2012 1:58 PM
  • 5. Re: SQL tuning which uses View
    Justin Cave Oracle ACE
    Currently Being Moderated
    Are you saying that it takes ten minutes to return the single-row result in the query you just posted? Or are you querying the view many times passing in different account numbers?

    Unfortunately, if we can't see a query plan, it's going to be nearly impossible for us to suggest any way to tune the query. We could guess at what the query plan might be and suggest things that might change the plan, but that's a terribly inefficient approach. Realistically, you likely need to fight whatever political battles you need to fight in order to be allowed the tools to do your job (starting with the ability to generate a query plan).

    Justin
  • 6. Re: SQL tuning which uses View
    rp0428 Guru
    Currently Being Moderated
    >
    I have to enter the SEI accounts
    >
    Doesn't look like you are entering the SEI_ACCOUNT_NUMBER (account_num) but are entering the request_id.

    No index on ACCOUNT_TYPE_ID but is there an index on REQUEST_ID?

    Since your join condition is:
    on A.REQUEST_ID = B.REQUEST_ID
    and A.ACCOUNT_TYPE_CD IN ('CHECKING','GL','SAVINGS')
    and B.ACCOUNT_TYPE_CD = 'TRUST'
    An index on REQUEST_ID, ACCOUNT_TYPE_CD will let Oracle use the index to locate all of the rows needed for any given REQUEST_ID.
    You can test this by creating the index in a DEV environment and checking the explain plan for the query.

    A separate index on ACCOUNT_TYPE_CD alone won't be as useful since Oracle would then have to use two indexes to find the rows.

    -----
    re Justin's comment
    -----
    Of course Justin is correct. The place to start is to examine what plan is being used now.

    Edited by: rp0428 on Jan 31, 2012 11:27 AM
  • 7. Re: SQL tuning which uses View
    914404 Newbie
    Currently Being Moderated
    Thanks for your suggestions and your valuable time ! I agree with Justin .I need to get the required things to do the tuning.I wil be back with the optimization i did for this one.

    I am entering the sei account numbers only.talk to you soon

    I am very new to this forum.You guys are awesome
  • 8. Re: SQL tuning which uses View
    914404 Newbie
    Currently Being Moderated
    I got the plan .DBA saying that it has a cartesian.How can we improve this?Could you Please suggest

    Plan     
    SELECT STATEMENT ALL_ROWS Cost: 29,967 Bytes: 86,655,388 Cardinality: 646,682      
          27 HASH JOIN RIGHT OUTER Cost: 29,967 Bytes: 86,655,388 Cardinality: 646,682      
                1 INDEX FULL SCAN INDEX (UNIQUE) PACSAPP.ACCT_TRAN_TYPE_PK Cost: 1 Bytes: 8 Cardinality: 2      
                26 HASH JOIN RIGHT OUTER Cost: 29,937 Bytes: 84,068,660 Cardinality: 646,682      
                      2 INDEX FULL SCAN INDEX (UNIQUE) PACSAPP.ACCT_TRAN_STATUS_PK Cost: 1 Bytes: 44 Cardinality: 11      
                      25 HASH JOIN RIGHT OUTER Cost: 29,907 Bytes: 81,481,932 Cardinality: 646,682      
                            3 INDEX FULL SCAN INDEX (UNIQUE) PACSAPP.PAYMENT_TYPE_PK Cost: 1 Bytes: 15 Cardinality: 5      
                            24 HASH JOIN RIGHT OUTER Cost: 29,877 Bytes: 79,541,886 Cardinality: 646,682      
                                  4 INDEX FAST FULL SCAN INDEX (UNIQUE) PACSAPP.PK_ACH_TRAN Cost: 312 Bytes: 3,515,106 Cardinality: 585,851      
                                  23 NESTED LOOPS OUTER Cost: 25,007 Bytes: 75,661,794 Cardinality: 646,682      
                                        21 HASH JOIN RIGHT OUTER Cost: 24,466 Bytes: 71,781,702 Cardinality: 646,682      
                                              5 INDEX FAST FULL SCAN INDEX (UNIQUE) PACSAPP.PK_TRUST_TRAN Cost: 183 Bytes: 2,074,164 Cardinality: 345,694      
                                              20 HASH JOIN Cost: 20,315 Bytes: 67,901,610 Cardinality: 646,682      
                                                    18 HASH JOIN RIGHT OUTER Cost: 10,368 Bytes: 17,412,120 Cardinality: 241,835      
                                                          6 INDEX FULL SCAN INDEX (UNIQUE) PACSAPP.ACCT_TRAN_TYPE_PK Cost: 1 Bytes: 8 Cardinality: 2      
                                                          17 HASH JOIN RIGHT OUTER Cost: 10,356 Bytes: 16,444,780 Cardinality: 241,835      
                                                                7 INDEX FULL SCAN INDEX (UNIQUE) PACSAPP.ACCT_TRAN_STATUS_PK Cost: 1 Bytes: 44 Cardinality: 11      
                                                                16 HASH JOIN RIGHT OUTER Cost: 10,344 Bytes: 15,477,440 Cardinality: 241,835      
                                                                      8 INDEX FULL SCAN INDEX (UNIQUE) PACSAPP.PAYMENT_TYPE_PK Cost: 1 Bytes: 15 Cardinality: 5      
                                                                      15 HASH JOIN RIGHT OUTER Cost: 10,332 Bytes: 14,751,935 Cardinality: 241,835      
                                                                            9 INDEX FAST FULL SCAN INDEX (UNIQUE) PACSAPP.PK_ACH_TRAN Cost: 312 Bytes: 3,515,106 Cardinality: 585,851      
                                                                            14 NESTED LOOPS OUTER Cost: 8,688 Bytes: 13,300,925 Cardinality: 241,835      
                                                                                  12 HASH JOIN RIGHT OUTER Cost: 8,490 Bytes: 11,587,128 Cardinality: 236,472      
                                                                                        10 INDEX FAST FULL SCAN INDEX (UNIQUE) PACSAPP.PK_TRUST_TRAN Cost: 183 Bytes: 2,074,164 Cardinality: 345,694      
                                                                                        11 TABLE ACCESS FULL TABLE PACSAPP.TRAN Cost: 7,349 Bytes: 10,168,296 Cardinality: 236,472      
                                                                                  13 INDEX UNIQUE SCAN INDEX (UNIQUE) PACSAPP.PK_WIRE_TRAN Cost: 0 Bytes: 6 Cardinality: 1      
                                                    19 TABLE ACCESS FULL TABLE PACSAPP.TRAN Cost: 7,387 Bytes: 23,410,761 Cardinality: 709,417      
                                        22 INDEX UNIQUE SCAN INDEX (UNIQUE) PACSAPP.PK_WIRE_TRAN Cost: 0 Bytes: 6 Cardinality: 1      

    Edited by: user2556087 on Feb 2, 2012 8:59 AM

    Edited by: user2556087 on Feb 2, 2012 9:00 AM
  • 9. Re: SQL tuning which uses View
    Justin Cave Oracle ACE
    Currently Being Moderated
    1) If you take a look at the FAQ on HOW TO: Post a SQL statement tuning request - template posting, it'll show you how to generate a plan that is going to be more readable for the folks in the forum.

    2) The plan you posted does not appear to have a Cartesian product in it. Are you certain you're posting the same plan the DBA is talking about?

    3) Exactly what query did you execute in order to get this plan?

    4) How many rows did the query actually return? The optimizer is estimating that it would return ~650,000 rows.

    Justin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points