Update taking time because of db file sequential read.

Printable View

10-07-2005, 03:22 AM
paragp1981

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
10-07-2005, 07:14 AM
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.
10-07-2005, 08:43 AM
stmontgo

Quote:

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?
10-07-2005, 08:49 AM
slimdave

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).
10-07-2005, 09:34 AM
paragp1981

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.
10-07-2005, 10:18 AM
tamilselvan

From your trace p1=90

Do you know the object stored in the data file 90?

Tamil
10-07-2005, 10:51 AM
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
10-07-2005, 11:05 AM
slimdave

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.
10-07-2005, 11:21 AM
tamilselvan

Quote:

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
10-07-2005, 12:57 PM
paragp1981

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

Quote:

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
10-07-2005, 01:17 PM
paragp1981

still i think what slimdave is saying is true ub query is getin executed 52128446 times so i have asked developer to change login probabily going for cursor or some thing with bulk collect
10-07-2005, 01:48 PM
tamilselvan

Quote:

Originally Posted by paragp1981

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 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')

Then,

To speed up the subquery create an index on
sncode, call_type, xfile_ind, rtx_end_date, r_p_customer_id , r_p_contract_id, rated_volume.

Also, post the view definition.

Tamil