script taking time

[Roshan]

SunOS dw172 5.10 Generic_147440-04 sun4u sparc SUNW,SPARC-Enterprise
DB 9.2.0.6.0

Hi Erman,

I am executing a script which is taking time to run. I have done a healthcheck at DB level. I have added more redo log groups since I was getting checkpoint error.

Please see attached script.
ins_mobiledata.sql

Regards,

Roshan

[ErmanArslansOracleBlog]

This may take time.. These are select-inserts..
The time that it takes depends on your servers performance (mostly I/O) and the data size..
Try to see the waits of the sessions and tune your env accordingly..
But again, this may take time..

So there is nothing more that I can say about it.

[Roshan]

Thanks for advise.

I have checked the db session wait for database link. Do you think the queries in the script below will force a full table scan causing waiting time to increase?

where substr(cdrfilename, 1,11) between '3gs00698947' and '3gs00700180'

 

where substr(cdrfilename, 1,15) between '3g3glf_00124907' and '3g3glf_00125968'

dbsessionWait.PNG

[Roshan]

sarPROD.png

The IOWAIT is high on production server. Should I reboot the server?

[ErmanArslansOracleBlog]

Looking at the dbsessionWait.PNG , I can say that, that session is waiting for data from the db link.
This means, db link is involved.. This means network is involved..

Looking at the sarPROD.png, you have significant IO wait and also CPU wait. But IO wait is obvious..

You don't need to think about whether a query will force a full table scan or not.. You just need to check the explain plan of the query and it will tell you..

[Roshan]

Hi,

I have generated an explain plan for a few queries.

scriptsexplain.docx

Do you think some tunings need to be done on target tables, like indexing?

[ErmanArslansOracleBlog]

It seems yes..
Your queries do partition range all.. It means all the partitions are accessed. (like a full tablescan)

[Roshan]

Can you please guide me on which columns to set the index?

select * from mobile_data@newarchicom --where time >= to_date('01/09/2012 00:00:00' , 'DD/MM/YYYY HH24:MI:SS') --and time <= to_date('30/09/2012 23
:59:59' , 'DD/MM/YYYY HH24:MI:SS') where substr(cdrfilename, 1,15) between '3g3glf_00124907' and '3g3glf_00125968' and postpre = 'roa');

For example in the fist query?

[ErmanArslansOracleBlog]

Try creating indexes on the columns that are used in the where condition.
But, always remember, having indexes on tables also slows down the DML performance.
So, you should be working on this with your developers, who knows the tables, data , queries and the application.