Oracle SQL Tuning Tutorial -Tuning Long Full Table Scans

Long Full Table Scans. A Free Tutorial.

Number Eight in the  “Performance tuning Guide, Top Ten Mistakes Found in Oracle Systems” Long Full Table Scans is described as follows:

“Long full table scans for high-volume or interactive online operations could indicate poor transaction design, missing indexes, or poor SQL optimization. Long table scans, by nature, are I/O intensive and unscalable.”

Actually, there are many cases where the full table scan is your friend. But whether they are good or bad for the performance of the SQL that invokes them, there may be implications (for better or for worse) for performance of other statements. This is particularly true in 11.2.x, where direct reads are possible for serial scans, and indirect reads are possible for parallel scans. Do you trust the optimizer? The change in behaviour in recent releases may need some investigation, and revisiting older code.

As always with a SkillBuilders’ tutorial, we shall illustrate the issues and solutions with live demonstrations using release 11g (11.2.0.3).

Audience:  Operating System Administrators, Storage Administrators, Oracle Administrators and Management responsible for Storage and / or Oracle Databases.

Presenter: John Watson, Oracle Certified Master .  <<<<< Take class with John, March 27

This free tutorial is segmented into several separate lessons:

  1. Introduction (0:46)
  2. Have You Collected System Statistics? (6:33)
  3. Full Table Scans, Indexes (6:26)
  4. Direct (selfish) Reads and Indirect Reads (13:24) (click on video below)
  5. When is Full Table Scan Direct? (2:44)

 

Date: Mar 28, 2013


NOTE: Some corporate firewalls will not allow videos hosted by YouTube.

Transcript

Direct (selfish) Reads and Indirect Reads

>>John:  Working at whether you’re getting full table scans and whether you should get full table scans is only the first part of the problem. There’s a major additional factor to consider. If a scan does occur, is it direct or indirect?

 

I’m sure we all know what indirect read is. Your service session process copies blocks and data file into the buffer cache, data is then transferred from buffer cache into session memory.

 

[pause]

 

Direct read, on the other hand, copies blocks from disk directly into PGA. You bypass the buffer cache completely.

 

Now this can be absolutely fantastic. You avoid all the overhead of reading and writing the buffer cache. Think of free buffer weight wait events and other related wait events on the buffer cache. All those wait events fall away completely. You avoid that buffer cache overhead.

 

[pause]

 

Furthermore, there may be no impacts on other sessions because you’re not hammering the buffer cache. You’re the users and may be very pleased that your reports aren’t killing the system in the same way.

 

So it can be fantastic but there are two major providers. A direct read from disk into PGA will be significantly faster than via the cache if you scan the table only once. But if you scan the table multiple times you think of those horrible data warehouse queries that self-joins with many tables, you’re going to be scanning that table multiple times. Each one of those scans has got to come from disk because there’s no way to cache it into PGA.

 

[pause]

 

Very closely related to problem, what if other sessions also want to scan the table? If you have many users running the same code that all going to be doing those direct reads, whereas had it gone via the cache one person would read the data into memory, take a hit for the team as it were and everybody else will then get the benefits because the data will be in buffer cache rather than waiting.

 

[pause]

 

There is also a more subtle issue that’s not so well documented. Direct reads force a segment checkpoint. This means that you’ll select statements that does a full table scan and Oracle decides to do it direct, causes a vast amount of disk writing. Why is that? Obvious when you think about it. Your session might have copied the block of the table into memory and updated it. Committed or not, it doesn’t really matter. But the block’s been changed, it’s dirty. If I then scan the table directly from disk, I’m going to get out of date versions of the data.

 

[pause]

 

So before any direct read operation, the segment must be check pointed. So your session will hang while the database writer copies all dirty buffers of that segment to disk. This maybe bad single instance, it may be really bad in a cluster.

 

If you’re working in a RAC environment, you run a query on node of your four node RAC. All four nodes have to checkpoint that segment and that’s going to be a huge amount of traffic on the interconnect and as well as a lot of I/O on the I/O subsystem. The wait event for the segment checkpoint by the way is the enq: KO fast object checkpoints.

 

[pause]

 

This business at bottom never used to be a problem. But with 11.2.02 and later, it’s become a matter of critical importance to many DBAs and I did say 11.2.02. There are major differences here between 11.2.01 and 11.2.02. There are changes wherein some ways slotted in perhaps about some people realizing.

 

[pause]

 

So what’s going on here? Well, I’ll just set auto trace off. I created a table earlier called T1 and all it is is a copy of the old objects table so it’ll be about 70,000 rows, it’s a reasonable size table of about 70,000 rows. The other bit of preparation I’ve done is I’ve set one parameter and that parameter is ñ

 

[pause]

 

Rather, I’ll set it now.

 

[pause]

 

I shall set the parameter alter session set_serial_direct_read=never. I’ll go through this parameter in great detail but what I’m doing at the moment is disabling the ability for what we call serial direct reads.

 

By default ñ no, not by default. What this means is that any serial full table scan will always be via the buffer cache. But we’ll see how that can change with 11.2.02 or you can change this manually otherwise.

 

[pause]

 

Let me get some statistics running.

 

[pause]

 

And see what happens when I query that table. So I’ll select count (*) from T1.

 

[pause]

 

So there’s no index on thing. The only way to execute that table to execute that query is to do a full table scan, which is taking even longer than I expected.

 

[pause]

 

I think the reason that is because I haven’t committed the transaction in another session. My apologies for that.

 

Let me just commit to my insert of the rows and do it again. That’s better. There are 76,000 rows and note there 1149 consistent gets zero physical reads. Zero physical reads, why? Because the first run of the query despite the old results, because I haven’t committed the insert of the rows, scanned the entire table into database buffer cache. That was indirect reads. I run it again.

 

[pause]

 

Zero disk I/O. Run it again.

 

[pause]

 

Zero disk I/O. And I can run the same query in any number of sessions. I’m going to be reading those blocks from the cache.

 

[pause]

 

Now, what if I change the behavior of my session? I’ll set that to ALWAYS.

 

[pause]

 

I do need to alter to making these changes to make sure it’s fantastical flush the buffer cache and we also flush the shared pool to force a re-pass of the statement.

 

[pause]

 

Now run the statement.

 

[pause]

 

That was 1395 physical reads, 1302 consistent gets. But that’s not unreasonable. We have to read the entire table because I’ll flush the buffer cache.

 

Let’s run the query again.

 

[pause]

 

And again.

 

[pause]

 

My figures are stabilizing. Every scan of that table is coming from disk. So every logical read requires a physical read.

 

This is the proof that we’re getting direct I/O. My server session process is reading those blocks from the segment directly into my PGA. And because there’s no caching capability there, it means we scan the table every time and also that we can’t really prove it with simple examples like this. We are check pointing the segments making database writer to go mad every time as well.

 

[pause]

 

That’s two possibilities from that parameter. The two possibilities being, serial direct read never, meaning all full table scans will go via the cache. While serial direct read always, meaning all full table scans will go direct.

 

But what’s the actual default? The default from 11.2.02 onwards, the default is auto.

 

[pause]

 

This is significant change. 11.2.01 and earlier, the legitimate values were true or false and the default was false. So you never got serial direct reads unless you deliberately did set that parameter to true.

 

[pause]

 

But for 11.2.02, by default, it’s on auto. So with that on auto, what’s Uncle Oracle going to do? We’ll, it will flush the buffer cache, flush the shared pool, and try running that query again.

 

[pause]

 

Right. First run the query, it came from disk. Fair enough. Second run of the query, from disk. Third run of the query, from disk.

 

Here we see a huge change in behavior from 11.2.01 to 11.2.02, that by default we are now getting direct reads for serial full table scan. That can come as a real shock to many applications as you go upgrade through the releases.

 

[pause]

 

The other point to consider at this point is parallel reads because there’s a similar effect with parallel. Parallel processing up to and including 11.1, parallel reads will always direct. This would sometimes cause major problems. Their programmers would put parallel hints in their code, not being aware that that was going to force segment check points across every node in the cluster if it’s a clustered system. But parallel reads will always direct.

 

[pause]

 

However, from 11.2 and later, Oracle has a choice. If we look at the relevant parameters,

 

[pause]

 

From 11.2 onwards, we have a new parameter, parallel degree policy. Defaulting to manual. As ever, with a wonderful new feature, the default is to disable it. But what that means is parallel query is manually controlled and parallel query will always be direct I/O, which may be good, it may be bad.

 

[pause]

 

If we change this to the 11.2 new feature, I’ll set it to auto. There is another version in between but I’ll set it to auto. I’m also going to set one more parameter which I wouldn’t normally set in a production system, but to get my little naughty system here working I need to reduce the parallel_min_time_threshold just one second. The default is 10 seconds, I need to bring that right down.

 

[pause]

 

What happens if I run a parallel query? Well I’ll override the automatic mechanism and do it like this, parallel(4). I’ll run that. But I do need to set auto trace.” I need to get the execution plan out and then run it.

 

This is reverting to the pre-11.2 technique. We see the degree of parallelism is 4 because of overheads the hints and if I haven’t put the hints in, there what happens? Right. Without the hints in there it’s done a full table scan and it’s computed the degree of parallelism is 1 because it reckons the table isn’t big enough.

 

[pause]

 

That scan, as already proved that full table scan will have been direct if I display the statistics that would’ve been a direct read from disc into PGA. I’m going to lie to the optimizer at this point and tell Oracle the table is a bit bigger.

 

[pause]

 

Let me just copy the queries I have already prepared. What I’m going to do is tell Oracle that scott.t1 in fact has 10 million rows and consist of a million blocks. So all I’m doing is just fiddling the statistics.

 

[pause]

 

What’s going to happen now? Let us see.

 

We’ll flush the buffer cache and we’ll flush the shared pool as I revert to make sure we have at least something resembling a level playing field. I’ll set my autot (auto trace) to show the plan and the execution to the sixth and then to see what happens when I run that query.

 

Select count(*) from t1, no hint, no nothing.

 

[pause]

 

Right. Uncle Oracle has decided to use parallel degree 4. Cast your mind back to what we talked about earlier with system statistics. It’s done that arithmetic. Because I’ve gathered proper system statistics on this machine, Oracle knows the maximum throughput of the disks. It knows the maximum throughput per parallel execution server. So Oracle has decided to use 5.

 

But then the more interesting thing – is this direct or indirect? Let’s run that query again.

 

[pause]

 

There you see it. It’s indirect. That has gone via cache and this is in 11.2. Fantastic enhancement provided we tune your system to take advantage of it. That from 11.2, parallel scans can now be indirect. So no matter who runs that query is going to go via the cache.

 

 

Copyright SkillBuilders.com 2017

×
Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!

 

×