OraFAQ Forum: Performance Tuning » db file sequential read causing Performance Issues

Home » RDBMS Server » Performance Tuning » db file sequential read causing Performance Issues (Oracle 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

db file sequential read causing Performance Issues [message #659102]

Fri, 06 January 2017 03:24

subbaiyan
Messages: 5
Registered: February 2008
Location: india

Junior Member

Hello

We upgraded our reporting system recently from Oracle 11.2 to 12c. Since the upgrade the DB FILE SEQUENTIAL READS have started causing performance issues with wait clause of user I/O. We have not changed anything in the queries. For example below is extract from AWR.

Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
db file sequential read 4,674,473 5235.2 1.12 48.3 User I/O

I have tried changing the parameters which could cause DB FILE SEQUENTIAL READ waits, but in vain. Can someone help?

Report message to a moderator

Re: db file sequential read causing Performance Issues [message #659103 is a reply to message #659102]

Fri, 06 January 2017 03:31

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Quote:

I have tried changing the parameters which could cause DB FILE SEQUENTIAL READ waits, but in vain.

What parameters were they?

If you post the AWR report, perhaps someone can help.

--update: and of course an AWR report from before the upgrade.

[Updated on: Fri, 06 January 2017 03:32]

Report message to a moderator

Re: db file sequential read causing Performance Issues [message #659104 is a reply to message #659103]

Fri, 06 January 2017 03:42

subbaiyan
Messages: 5
Registered: February 2008
Location: india

Junior Member

AWR attached. Please rename it to .HTML

Attachment: awrrpt_1_250_255.txt
(Size: 983.09KB, Downloaded 2898 times)

Report message to a moderator

Re: db file sequential read causing Performance Issues [message #659105 is a reply to message #659104]

Fri, 06 January 2017 03:57

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Your db file sequential read waits are responsible for only 4% of DB Time. You could reduce them to zero and probably no-one would notice.

There is only one user SQL that is doing any significant IO, this one that took 134 seconds to execute:
SELECT * FROM HK_RT22_STD_DE_V POS WHERE LOT_TYPE_FK = :B2 AND VALID_ON = :B1
if (and only if) that SQL is a problem, you could try to tune it.

Are your users actually reporting any problems with performance? If so, what?

Report message to a moderator

Re: db file sequential read causing Performance Issues [message #659106 is a reply to message #659105]

Fri, 06 January 2017 04:07

subbaiyan
Messages: 5
Registered: February 2008
Location: india

Junior Member

Thanks Watson. Appreciate your quick response. The process (SQL ID: 51wd4tyq7s5qa) which inserts data into table (SQL ID:by48aw2vas7pf) takes about 5 hours to complete.
The same process used to take less than an hour in 11g.

Report message to a moderator

Re: db file sequential read causing Performance Issues [message #659108 is a reply to message #659106]

Fri, 06 January 2017 04:10

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Well, I am sure you have noticed that statement spends virtually no time on IO.

But really, man, do think a bit. At the very least, You need to get the AWR SQL report for that statement from before and after the upgrade.

Report message to a moderator

Re: db file sequential read causing Performance Issues [message #659109 is a reply to message #659108]

Fri, 06 January 2017 04:13

subbaiyan
Messages: 5
Registered: February 2008
Location: india

Junior Member

I do not have the pre image of the system, would have done first thing if I had the same. I understand the insert does not show any time spent in AWR, however the process takes about 5 hours for inserting 1 million records.
Anyway, thanks for your inputs

Report message to a moderator

Re: db file sequential read causing Performance Issues [message #659111 is a reply to message #659109]

Fri, 06 January 2017 04:21

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

But the AWR shows it took 900 seconds to load the million records so means either there's an error somewhere OR more likely there's a slowdown in whatever is driving the ~~slow-by-slow~~ row by row insert.

Feels a lot like you're looking at the wrong thing here. The JOB may now take 5 hours, but the insert certainly shows no signs of this.

Report message to a moderator

Re: db file sequential read causing Performance Issues [message #659112 is a reply to message #659109]

Fri, 06 January 2017 04:24

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

You don't need any pre-image. Your AWR snapshots from before upgrade will still be there (assuming you are still within your retention period).

Following upgrade to 12, a common fix (but by no means a solution) to performance issues is to disable the adaptive features with

alter system set optimizer_adaptive_features=false;

this is done by default in release 12.2. T

Report message to a moderator

Re: db file sequential read causing Performance Issues [message #659113 is a reply to message #659111]

Fri, 06 January 2017 04:25

subbaiyan
Messages: 5
Registered: February 2008
Location: india

Junior Member

Thanks! But from the AWR I cannot figure out from AWR what's taking time. Any other pointers on how I go forward?

Report message to a moderator

Re: db file sequential read causing Performance Issues [message #659114 is a reply to message #659113]

Fri, 06 January 2017 04:30

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

You could use dbms_profiler and dbms_hprof. And (if you have the packs licenced) monitor the procedure as an operation.

Report message to a moderator

Re: db file sequential read causing Performance Issues [message #659124 is a reply to message #659114]

Fri, 06 January 2017 07:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) ddl for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Previous Topic:	Union all tables with View and using the Index
Next Topic:	slow while inserting

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 17:53:42 CST 2025