-
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...
HTML Code:
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 19270, image: oracle@cbemis (TNS V1-V3)
*** 2005-10-07 11:00:39.230
*** SESSION ID:(38.46742) 2005-10-07 11:00:39.173
WAIT #1: nam='db file sequential read' ela= 22 p1=90 p2=110268 p3=1
WAIT #1: nam='db file sequential read' ela= 26 p1=90 p2=71954 p3=1
WAIT #1: nam='db file sequential read' ela= 157 p1=90 p2=71955 p3=1
WAIT #1: nam='db file sequential read' ela= 13 p1=90 p2=71956 p3=1
WAIT #1: nam='db file sequential read' ela= 14 p1=90 p2=71957 p3=1
WAIT #1: nam='db file sequential read' ela= 13 p1=90 p2=71958 p3=1
WAIT #1: nam='db file sequential read' ela= 13 p1=90 p2=71959 p3=1
WAIT #1: nam='db file sequential read' ela= 14 p1=90 p2=71960 p3=1
WAIT #1: nam='db file sequential read' ela= 13 p1=90 p2=71961 p3=1
WAIT #1: nam='db file sequential read' ela= 12 p1=90 p2=71962 p3=1
WAIT #1: nam='db file sequential read' ela= 17 p1=90 p2=110269 p3=1
WAIT #1: nam='db file sequential read' ela= 18 p1=90 p2=71963 p3=1
WAIT #1: nam='db file sequential read' ela= 139 p1=90 p2=71964 p3=1
WAIT #1: nam='db file sequential read' ela= 12 p1=90 p2=71965 p3=1
WAIT #1: nam='db file sequential read' ela= 13 p1=90 p2=71966 p3=1
WAIT #1: nam='db file sequential read' ela= 18 p1=90 p2=71967 p3=1
WAIT #1: nam='db file sequential read' ela= 14 p1=90 p2=71968 p3=1
WAIT #1: nam='db file sequential read' ela= 13 p1=90 p2=71969 p3=1
WAIT #1: nam='db file sequential read' ela= 12 p1=90 p2=71970 p3=1
WAIT #1: nam='db file sequential read' ela= 17 p1=90 p2=110270 p3=1
WAIT #1: nam='db file sequential read' ela= 15 p1=90 p2=71971 p3=1
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.
Oracle 9.2.0.6.0
HP-UX-11i
Any thoughts....
Rgds
Parag
-
"db file sequential read"s are definitely single block reads due to index usage. In about 80% of the cases, it means the wrong index is being used.
Jeff Hunter
-
Originally Posted by marist89
"db file sequential read"s are definitely single block reads due to index usage. In about 80% of the cases, it means the wrong index is being used.
or rather an appropriately selective enough index has not been created?
I'm stmontgo and I approve of this message
-
This leaps out at me:
Code:
act_date < '01-Aug-2005'
I think you ought to be using
Code:
act_date < to_date('01-Aug-2005','DD-Mon-YYYY')
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.
-
From your trace p1=90
Do you know the object stored in the data file 90?
Tamil
-
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
-
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.
-
Originally Posted by paragp1981
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.
Tamil
-
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
52128446
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
|