DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Tuning a query

  1. #1
    Join Date
    Oct 2008
    Posts
    69

    Tuning a query

    Hi There, i have done an explain plan on a query that is taking very long since the last upgrade of the application/database + 10g upgrade.
    I don't know if indexes have changed, bought in product, i can't change any indexes or create any or will void support.
    I have never tried to do query tuning before and just wanted to know if these costs are very high? Query is taking forever to run (currently been over an hour and still running) whereas used to take 1-2 minutes (or so i am told)

    Code:
    | Id  | Operation| Name                    | Rows  | Bytes | Cost (%CPU)|
    
    --------------------------------------------------------------------------------
    
    
    |   0 | SELECT STATEMENT|                  |  2951 |   397K| 135K (16)|
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
    
    |   1 |  HASH UNIQUE |                     |  2951 |   397K| 135K (16)|
    
    |   2 |   NESTED LOOPS ANTI |              |  2951 |   397K| 135K (16)|
    
    |   3 |    HASH JOIN |                     |  2951 |   337K| 126K (17)|
    
    
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    |   4 |     INDEX FAST FULL SCAN | AGS_PK  |  1365 | 30030 | 482   (7)|
    
    |   5 |     HASH JOIN   |                  | 11763 |  1091K| 126K (17)|
    
    |   6 |      NESTED LOOPS|                 |  3295 |   254K| 125K (17)|
    
    |   7 |       HASH JOIN   |                |  3295 |   138K| 122K (17)|
    
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
    |   8 |        TABLE ACCESS FULL | HB_DETAILS | 10792 |   231K| 1087  (11)|
    
    
    |   9 |        TABLE ACCESS BY INDEX ROWID| TRANSACTIONS | 47964 |   983K| 120K (17)|
    
    
    |  10 |         INDEX RANGE SCAN  | TRA_TRT_FK_I     |  4796K|       | 15150  (16)|
    
    
    |  11 |       TABLE ACCESS BY INDEX ROWID | REVENUE_ACCOUNTS |   1 |    36 |   1   (0)|
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
    
    |  12 |        INDEX UNIQUE SCAN   | RAC_PK           |     1 |       |   0   (0)|
    
    
    |  13 |      TABLE ACCESS FULL   | ADMIN_PROPERTIES |   173K|  2703K| 992  (12)|
    
    
    |  14 |    TABLE ACCESS BY INDEX ROWID  | TRANSACTIONS     |     1 |    21 |   3   (0)|
    
    
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    |  15 |     INDEX RANGE SCAN   | TRA_TRT_FK_I     |    15 |       |   2   (0)|
    
    
    --------------------------------------------------------------------------------

    query
    Code:
    select distinct t1.tra_rac_accno rac_accno, 
    r1.rac_pay_ref pay_ref, 
    to_char(r1.rac_last_aba_balance, '9900.00') last_balance, 
    r1.rac_report_pro_refno pro_refno,
    to_char(r1.rac_last_aba_date, 'DD-MON-YY') bal_date
    from transactions t1, hb_details h1, revenue_accounts r1
    where  t1.tra_trt_code = 'HBS'
    and not exists (select * from transactions t2
                     where t1.tra_rac_accno = t2.tra_rac_accno
                      and t2.tra_trt_code = 'HBS'
                      and t1.tra_cr = t2.tra_cr
                      and t2.tra_date < t1.tra_date)
    and trunc(t1.tra_date) = '&tra_date'
    and sysdate between h1.hde_award_from_date and h1.hde_award_to_date
    and h1.hde_rac_accno = t1.tra_rac_accno
    and r1.rac_accno = h1.hde_rac_accno
    and r1.rac_hrv_ate_code = 'REN'
    and exists (select null from prop_groupings
                         where pgr_pro_refno = r1.rac_report_pro_refno
                         and pgr_aun_code = '&aun_unit_code')
    any help?
    I have run dbms_stats.gather_schema_stats(ownname =>'PEEPS',estimate_percent => NULL
    ,method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',cascade => TRUE); to see if makes difference but seems not.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Talking Stats anyone?

    Try these stats:
    Code:
    EXEC DBMS_STATS.GATHER_SCHEMA_STATS('PEEP',20,TRUE,'FOR ALL COLUMNS SIZE AUTO',4,'ALL',TRUE);
    If this does not work, you may need some indexes on tables:

    TABLE ACCESS FULL | HB_DETAILS
    and
    TABLE ACCESS FULL | ADMIN_PROPERTIES
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Oct 2008
    Posts
    69
    Quote Originally Posted by LKBrwn_DBA View Post
    Try these stats:
    Code:
    EXEC DBMS_STATS.GATHER_SCHEMA_STATS('PEEP',20,TRUE,'FOR ALL COLUMNS SIZE AUTO',4,'ALL',TRUE);
    If this does not work, you may need some indexes on tables:

    TABLE ACCESS FULL | HB_DETAILS
    and
    TABLE ACCESS FULL | ADMIN_PROPERTIES
    Thanks for Reply, can you tell me what the difference is between the stats i ran and the stats you are suggesting.

    EDIT: Currently running your stats but will be Monday until i come back in and discover if any change

  4. #4
    Join Date
    Oct 2008
    Posts
    69
    actually those stats executed quickly but the query is still taking an age.
    I thought about indexes for the 2 full table scans but looking at the query plan the cost seems much heavier elsewhere
    such as
    Code:
    TABLE ACCESS BY INDEX ROWID| TRANSACTIONS | 47964 |   983K| 120K (17)|
    If i am reading the cost right but i presume 120K=120,000 while the full table scans are just around 1000 cost.
    Would indexes for those full table scans really make much difference?

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool V$sql_plan?

    Ok, seems to be this query:
    Code:
    -- Etc --
    and not exists (select * from transactions t2
                     where t1.tra_rac_accno = t2.tra_rac_accno
                      and t2.tra_trt_code = 'HBS'
                      and t1.tra_cr = t2.tra_cr
                      and t2.tra_date < t1.tra_date)
    -- Etc --
    1) Try replacing the '*' with 'NULL'?
    2) Check out the indexes on the columns
    3) Check the V$SQL_PLAN table to view the exact execution plan (which may or may not be different).

    PS: Did you use @?/rdbms/admin/utlxpls.sql to get the plan?
    If so, you should have a section called 'Predicate Information' after the plan which details the exact access path and filter it's using.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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