Update taking time because of db file sequential read.
One of my users is running following query to update.
HTML Code:
update BM_ANUSALES_OCT2005 bm set totalmou=
(select sum(rated_volume)/60 from tnpostpaid.rtx_jul_aug
where r_p_contract_id=bm.co_id and
r_p_customer_id=bm.customer_id and
rtx_ent_date>=bm.act_date and
rtx_ent_date<=bm.end_date and
sncode=1 and call_type=1 and xfile_ind='H')
where act_date < '01-Aug-2005'
he said its running very slow so i traced his session and found that trace file contains ony foll nothing else...
and it looks like session is waiting for indefinite time...
what need s to be done. From asktom i got to know that it is bcoz of sinle block read index scans but i am not sure what to do.
Also, how many rows are you updating here? Might it be possible to run the update "indirectly" by creating a new temporary data set based on a join of BM_ANUSALES_OCT2005 and tnpostpaid.rtx_jul_aug, then either replacing the data in the target table or updating it through an in-line view that joins it with the temporary table? Or maybe you can even update against an in-line view between BM_ANUSALES_OCT2005 and tnpostpaid.rtx_jul_aug right now (although it looks tricky).
yes actually developer was using that to_date. While trying to see explain plan i removed it. sorry for that.
I have asked user to change the logic .
My thinking is since he is using sub-query it might be the cause. I have asked him to make use of cursor. I have aksed him to test with bulk collect.. Do you think that will help.
The way that this update will be processed is that the subquery will be executed for every individual row that needs to be updated ... so you are potentially executing that subquery (and accessing the same index blocks) 100,000 times if you have that many rows to update.
Hence my suggestions to try and join the two tables directly together to get the result calculated more efficiently ... it could be that there is a more effective join strategy than the nested loops you are currently probably seeing.
Yes file is part of tablespace which contains all rtx partition tables for JUL,AUG,SEP,OCT. and rtx_jul_aug is view for these base tables..
Rgds
Parag
I suspect the cause may be in the view definition.
Do one thing:
First create a temp table and measure run time:
Code:
create table temp_rtx NOLOGGING
as
select r_p_contract_id, r_p_customer_id,
rtx_ent_date
sum(rated_volume)/60
from tnpostpaid.rtx_jul_aug
where sncode=1 and call_type=1 and xfile_ind='H'
group by r_p_contract_id, r_p_customer_id, rtx_ent_date
And also check how many rows in the temp file.
Well size of these each tables is around 15-16 GB. So i cant really create
these temp table that will require huge space .
but if you run
select count(1) from tnpostpaid.rtx_jul_aug
where sncode=1 and call_type=1 and xfile_ind='H'
group by r_p_contract_id, r_p_customer_id, rtx_ent_date
it gives
Reply With Quote
Originally Posted by marist89
Bookmarks