No question at this time
DBA Top 10
1 M. Cadot 22600
2 A. Kavsek 15200
3 M. Hidayathullah ... 11000
4 B. Vroman 8100
5 P. Wisse 6000
6 T. Boles 5200
7 J. Schnackenberg 3300
8 G. Lambregts 2200
8 P. Knibbs 2200
8 K. Pagadala 2200
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48017
Total active users1850
Act. users last 24h5
Act. users last hour0
Registered user hits last week339
Registered user hits last month1183
Go up

change parameter filesystemio_options from ASYNCH to SETALL and query goes from 8min to 18hours
Next thread: version mismatch while taking Rman backup
Prev thread: Creating 12c AUDIT PROFILES from standard audit setups

Message Score Author Date
Hi people, Complex query (plan) runs in about ...... Benny Derous Feb 17, 2017, 14:16
Hi, This is just hardware dependant. Regards...... Philip Wisse Feb 17, 2017, 14:31
Benny I don' t have a crystal ball but it would...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Guy Lambregts Feb 17, 2017, 17:22
Hi, Guy, long time not see...I'm happy you're s...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Ales Kavsek Feb 18, 2017, 12:51
Ales Thanks, we both agree Yes I am still in ...... Guy Lambregts Feb 18, 2017, 17:58

Follow up by mail Click here


Subject: change parameter filesystemio_options from ASYNCH to SETALL and query goes from 8min to 18hours
Author: Benny Derous, Belgium
Date: Feb 17, 2017, 14:16, 183 days ago
Os info: AIX 7.1
Oracle info: EE 12.1.0.2
Message: Hi people,

Complex query (plan) runs in about 8 minutes. I notice that the IO wait times are not impressive and look for cause/solution. I come up with parameter filesystemio_options which is set on ASYNCH. I switch it to SETALL to start using DIRECT IO but the response time goes through the roof, it needs now 18 hours !!

What happened here?

I have AWR and AWR SQL Reports and it looks like the avg IO wait time has indeed significantly been reduced (which is a good thing). And overall query runtime stats are improved. Only CPU time and elapse time have increased dramatically. Here are some numbers:

I can see that the avg IO wait has dramatically reduced from 12ms to 1.85ms – that’s must be because of the change setting of filesystemio_options.
What’s also improved: the runtime stats of the query have been significantly reduced
- Buffer gets from 855K to 663K
- Disk reads from 1694K to 1279K
- User IO wait time from 198K to 78K

I do see a change in query plan however. The query plan has changed slightly...would that be because of the parameter change? Please note that the query is running on a isolated system with hardly any activity and queries only 'old' data (which is never changed).
I am uploading relevant AWR reports.

Thanks a lot.
Kind regards,
Benny Derous
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: change parameter filesystemio_options from ASYNCH to SETALL and query goes from 8min to 18hours
Author: Philip Wisse, Netherlands
Date: Feb 17, 2017, 14:31, 183 days ago
Message: Hi,

This is just hardware dependant.

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: change parameter filesystemio_options from ASYNCH to SETALL and query goes from 8min to 18hours
Author: Guy Lambregts, Belgium
Date: Feb 17, 2017, 17:22, 183 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Benny

I don' t have a crystal ball but it would surprise me that such a huge a deviation is caused by the filesystemio_options change you made.

I would rather concentrate on the change in explain plan you described. It may look minor but it can be huge.

Regards
Guy

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: change parameter filesystemio_options from ASYNCH to SETALL and query goes from 8min to 18hours
Author: Ales Kavsek, Slovenia
Date: Feb 18, 2017, 12:51, 182 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

Guy, long time not see...I'm happy you're still in business :)...I agree with you, change in plan is the place to investigate.
My theory is that the "old" good plan was gone during database restart...and 12c "adaptive query plans" feature will eventually, after several SQL runs, perhaps change the execution plan to the better one (unfortunately, nothing is guaranteed when we talk about CBO "artificial intelligence").
Another thing that I would check is system statistics (SELECT * FROM SYS.AUX_STATS$), do they reflect the improved I/O due to the direct I/O kicked in...

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: change parameter filesystemio_options from ASYNCH to SETALL and query goes from 8min to 18hours
Author: Guy Lambregts, Belgium
Date: Feb 18, 2017, 17:58, 182 days ago
Message: Ales

Thanks, we both agree
Yes I am still in business, Oracle DBA is all what I can, but I admit to have lost energy.

Kind Regards
Guy
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here