-
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.
-
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
-
Originally Posted by LKBrwn_DBA
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
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|