Home » RDBMS Server » Performance Tuning » db file sequential read causing Performance Issues (Oracle 12c)
db file sequential read causing Performance Issues [message #659102] Fri, 06 January 2017 03:24 Go to next message
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?
Re: db file sequential read causing Performance Issues [message #659103 is a reply to message #659102] Fri, 06 January 2017 03:31 Go to previous messageGo to next message
John Watson
Messages: 7148
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 Go to previous messageGo to next message
subbaiyan
Messages: 5
Registered: February 2008
Location: india
Junior Member

AWR attached. Please rename it to .HTML
Re: db file sequential read causing Performance Issues [message #659105 is a reply to message #659104] Fri, 06 January 2017 03:57 Go to previous messageGo to next message
John Watson
Messages: 7148
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?

Re: db file sequential read causing Performance Issues [message #659106 is a reply to message #659105] Fri, 06 January 2017 04:07 Go to previous messageGo to next message
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.
Re: db file sequential read causing Performance Issues [message #659108 is a reply to message #659106] Fri, 06 January 2017 04:10 Go to previous messageGo to next message
John Watson
Messages: 7148
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.
Re: db file sequential read causing Performance Issues [message #659109 is a reply to message #659108] Fri, 06 January 2017 04:13 Go to previous messageGo to next message
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
Re: db file sequential read causing Performance Issues [message #659111 is a reply to message #659109] Fri, 06 January 2017 04:21 Go to previous messageGo to next message
Roachcoach
Messages: 1498
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.
Re: db file sequential read causing Performance Issues [message #659112 is a reply to message #659109] Fri, 06 January 2017 04:24 Go to previous messageGo to next message
John Watson
Messages: 7148
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
Re: db file sequential read causing Performance Issues [message #659113 is a reply to message #659111] Fri, 06 January 2017 04:25 Go to previous messageGo to next message
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?
Re: db file sequential read causing Performance Issues [message #659114 is a reply to message #659113] Fri, 06 January 2017 04:30 Go to previous messageGo to next message
John Watson
Messages: 7148
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.
Re: db file sequential read causing Performance Issues [message #659124 is a reply to message #659114] Fri, 06 January 2017 07:47 Go to previous message
BlackSwan
Messages: 25715
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
Previous Topic: Full table scan
Next Topic: slow while inserting
Goto Forum:
  


Current Time: Sat Nov 18 04:21:35 CST 2017

Total time taken to generate the page: 0.01360 seconds