| Incosistent Wait Event [message #669021] |
Thu, 29 March 2018 03:22  |
 |
nciteamo
Messages: 27
Registered: October 2014
|
Junior Member |
|
|
Hy Guys
I Have a query that runs fast(30 second), when i see the event in v$session, most of the time is direct path read and just little db file sequencial read. After three hours i run the same query, it takes 10 minute and i see the event is db file sequencial read and db file parallel read in most of the time. Then i flush shared pool and the query runs fast again.
Why does it happen incosistent?
how to make it consistent?
Thank You
|
|
|
|
| Re: Incosistent Wait Event [message #669022 is a reply to message #669021] |
Thu, 29 March 2018 03:40  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
As I understand it:
The choice between direct read and indirect read is not a costed decision. It is made at run time by the SQL execution engine, not by the optimizer during the parse. Various people have attempted to reverse engineer the algorithm, I'm not going to quote them because I don't know if they are correct. What is documented is that the decision includes factors such as the size of buffer cache, size of table, and the proportion of the table that is already cached, and the "temperature" of the table (which is how often and in what way it is accessed). The release of the database is critical, 12.2.0.1 will not be the same as 12.2.0.2.
If you want direct path read, then set db_big_table_cache_percent_target will certainly help. Or you set _serial_direct_read=always|never to force the behaviour you want (the default is auto) if you are feeling brave.
|
|
|
|
| Re: Incosistent Wait Event [message #669023 is a reply to message #669022] |
Thu, 29 March 2018 04:03 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
This trace will let you see its decision process:
alter session set events 'trace[nsmtio]';
<QUERY>
alter session set events 'trace[nsmtio] off';
The other thing to consider is that UNDO reads are always db file sequential reads, so even if you're reading an object which is being read directly, if the DB needs to read undo, it will drop to sequential reads and if there a good number of undo reads needed, you'll probably find your performance tanks. You should be able to check this by examining what objects are being read at the time.
|
|
|
|
|
|
| Re: Incosistent Wait Event [message #669025 is a reply to message #669024] |
Thu, 29 March 2018 04:23 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
nciteamo wrote on Thu, 29 March 2018 10:20John Watson wrote on Thu, 29 March 2018 15:40
if you are feeling brave.
what do you mean "feeling brave"? is any impact if i force the behaviour to direct read?
You'll quickly become intimately familiar with your SAN admins mad face if it is a high throughput query.
[Updated on: Thu, 29 March 2018 04:23] Report message to a moderator |
|
|
|
| Re: Incosistent Wait Event [message #669026 is a reply to message #669023] |
Thu, 29 March 2018 04:27 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
I hadn't thought of undo. But I have thought of another related factor. A direct read will force a checkpoint of the segment, so if there are a lot of dirty buffers in cache, that checkpoint is going to be bad. This too might push Oracle towards the indirect path.
|
|
|
|
| Re: Incosistent Wait Event [message #669028 is a reply to message #669026] |
Thu, 29 March 2018 05:06 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
I also forgot the most obvious one.
It's changing plan. Non direct full scan should be db file scattered read. OP isn't seeing that.
Edit: I also seem to remember a long time ago reading something about enq ko - fast object checkpoint being a "dirtier" checkpoint but for the life of me I can't find the article I'd seen.
[Updated on: Thu, 29 March 2018 05:07] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
