OraFAQ Forum: Performance Tuning » Incosistent Wait Event

Home » RDBMS Server » Performance Tuning » Incosistent Wait Event (12.1.0.2.0)

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

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

Report message to a moderator

Re: Incosistent Wait Event [message #669022 is a reply to message #669021]

Thu, 29 March 2018 03:40

John Watson
Messages: 8976
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.

Report message to a moderator

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:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: Incosistent Wait Event [message #669024 is a reply to message #669022]

Thu, 29 March 2018 04:20

nciteamo
Messages: 27
Registered: October 2014

Junior Member

John 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?

Report message to a moderator

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:20

John 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: 8976
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.

Report message to a moderator

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

Previous Topic:	optimzer dynmic smpling
Next Topic:	Direct path load insert take much longer than query inside it

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 01:17:00 CDT 2025