Search

OakieTags

Who's online

There are currently 0 users and 42 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

Star Transformation – 2

After publishing my note about emulating star transformations when there was something blocking the optimizer’s ability to do them automatically I ended up having an interesting email conversation with Mark Farnham about variations on the theme, concluding with a completely different (and possibly more intuitive) SQL statement to achieve the same aim.

You will recall that I started off with the following statement:

select
	ord.*
from
	products	prd,
	customers	cst,
	orders		ord
where
	prd.grp = 50
and	cst.grp = 50
and	ord.id_prd = prd.id
and	ord.id_cst = cst.id
;

Although this specific example is so simple that the optimizer could turn it into a star transformation automatically, I used it to demonstrate a “minimum work” version of the statement as follows (with the execution plan from 11.1.0.6):

select
	ord.*
from
	(
	select
		/*+
			leading(prd ord)
			use_nl(ord)
			no_merge
		*/
		ord.rowid 	prid
	from
		products	prd,
		orders		ord
		where
		prd.grp = 50
	and	ord.id_prd = prd.id
		)	prid,
	(
	select
		/*+
			leading(cst ord)
			use_nl(ord)
			no_merge
		*/
		ord.rowid 	crid
	from
		customers	cst,
		orders		ord
	where
		cst.grp = 50
	and	ord.id_cst = cst.id
	)	crid,
	orders	ord
where
	prid.prid = crid.crid
and	ord.rowid = prid.prid
;

--------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    11 |  1650 |   570 |
|   1 |  NESTED LOOPS               |            |    11 |  1650 |   570 |
|*  2 |   HASH JOIN                 |            |    11 |   264 |   559 |
|   3 |    VIEW                     |            |  3361 | 40332 |   277 |
|   4 |     NESTED LOOPS            |            |  3361 | 87386 |   277 |
|*  5 |      TABLE ACCESS FULL      | CUSTOMERS  |    98 |   882 |    81 |
|*  6 |      INDEX RANGE SCAN       | ORD_CST_FK |    34 |   578 |     2 |
|   7 |    VIEW                     |            |  3390 | 40680 |   281 |
|   8 |     NESTED LOOPS            |            |  3390 | 88140 |   281 |
|*  9 |      TABLE ACCESS FULL      | PRODUCTS   |   100 |   900 |    81 |
|* 10 |      INDEX RANGE SCAN       | ORD_PRD_FK |    34 |   578 |     2 |
|  11 |   TABLE ACCESS BY USER ROWID| ORDERS     |     1 |   126 |     1 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("PRID"."PRID"="CRID"."CRID")
   5 - filter("CST"."GRP"=50)
   6 - access("ORD"."ID_CST"="CST"."ID")
   9 - filter("PRD"."GRP"=50)
  10 - access("ORD"."ID_PRD"="PRD"."ID")

Go back to the original SQL statement, though. The select list contains columns from just the orders table, and there’s a fairly well-known suggestion (or possibly guideline) that “tables that are not in the select list should not appear in the from clause, they should appear in subqueries in the where clause”. It’s not difficult to come up with examples where this strategy is a bad idea – but it’s often worth thinking through the consequences of trying to apply it. (Of course, you often find that after you’ve rewritten your SQL to match the strategy the optimizer transforms it back into the join that you had been avoiding.)

Since the products and customers tables don’t appear in the select list, can I find a way of rewriting my statement with subqueries ? The answer is yes. Here’s the SQL, with the execution plan I got.

select
	ord.*
from
	orders ord
where
	ord.rowid in (
		select
			/*+
				no_use_hash_aggregation
			*/
			prid.prid
		from
			(
			select	/*+ no_merge */
				ord.rowid 	prid
			from
				products	prd,
				orders		ord
				where
				prd.grp = 50
			and	ord.id_prd = prd.id
			)	prid,
			(
			select	/*+ no_merge */
				ord.rowid 	crid
			from
				customers	cst,
				orders		ord
			where
				cst.grp = 50
			and	ord.id_cst = cst.id
			)	crid
		where
			prid.prid = crid.crid
	)
;

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |   138 |   591   (1)| 00:00:08 |
|   1 |  NESTED LOOPS               |            |     1 |   138 |   591   (1)| 00:00:08 |
|   2 |   VIEW                      | VW_NSO_1   |    11 |   132 |   589   (1)| 00:00:08 |
|   3 |    SORT UNIQUE              |            |     1 |   264 |            |          |
|*  4 |     HASH JOIN               |            |    11 |   264 |   589   (1)| 00:00:08 |
|   5 |      VIEW                   |            |  3314 | 39768 |   294   (1)| 00:00:04 |
|   6 |       NESTED LOOPS          |            |  3314 | 86164 |   294   (1)| 00:00:04 |
|*  7 |        TABLE ACCESS FULL    | PRODUCTS   |   100 |   900 |    94   (2)| 00:00:02 |
|*  8 |        INDEX RANGE SCAN     | ORD_PRD_FK |    33 |   561 |     2   (0)| 00:00:01 |
|   9 |      VIEW                   |            |  3314 | 39768 |   294   (1)| 00:00:04 |
|  10 |       NESTED LOOPS          |            |  3314 | 86164 |   294   (1)| 00:00:04 |
|* 11 |        TABLE ACCESS FULL    | CUSTOMERS  |   100 |   900 |    94   (2)| 00:00:02 |
|* 12 |        INDEX RANGE SCAN     | ORD_CST_FK |    33 |   561 |     2   (0)| 00:00:01 |
|  13 |   TABLE ACCESS BY USER ROWID| ORDERS     |     1 |   126 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("PRID"."PRID"="CRID"."CRID")
   7 - filter("PRD"."GRP"=50)
   8 - access("ORD"."ID_PRD"="PRD"."ID")
  11 - filter("CST"."GRP"=50)
  12 - access("ORD"."ID_CST"="CST"."ID")

You’ll notice that this plan is remarkably similar to the plan I got from the joins with inline views – although the new plan has one extra “sort unique” operation at line 3. The optimizer has transformed my query by unnesting the subqueries and turning them into inline views – adding in a “distinct” operation because that’s what happens when you turn an “IN subquery” into a join when there isn’t a suitable uniqueness constraint on the join column.

There are two reasons for adopting this subquery approach. There’s the (highly subjective) argument that the code is a little easier to understand in this form, especially if you then want to join the orders table onwards to other tables. There’s also an objective argument relating to the “sort unique”. Note that I included the hint “no_use_hash_aggregation” to stop Oracle from using a “hash unique” operation at this point. By forcing Oracle to sort for uniqueness I know that the rowids will appear in (at least) an “extent-based” order rather than a completely random order. It’s possible that walking the fact table in physical order will be a little more efficient than accessing it in a completely randomised order. The former might find multiple rows in a block or benefit from some form of O/S or SAN read-ahead; the latter is more likely to turn one row into one random physical read.

One final thought – I said that you might want to take this type of approach for queries where the optimizer can’t do a star transformation automatically. There’s one very important case where this is always true – when you’re running Standard Edition, which doesn’t support bitmap indexes. So if you’re running SE and want to do star transformations – you now know how.

Footnote: I haven’t spent much time testing the limits of variations of this code although I did spend 30 minutes extending the example to use three dimension tables. If you think that this strategy might solve a production problem, think about using the sample code to create and test a better model of your production requirement before rewriting the production code – and if you go to production, check very carefully that the resulting code does produce the same results.

No Linux servers for Oracle Support…

I was just mailed a bug update and it included this text (spelling mistakes theirs, not mine).

Note customer is on Linux but could not find an available
11.2 Linux database to test on.  Reprocided problem on Solaris
confirming that there is some generic problem here.

Really?

And here’s me thinking that firing up a VM with any version of Linux & Oracle was quick and easy. Perhaps their VMs are running on Amazon, hence the lack of available systems. :)

Cheers

Tim…




Envisioning NFS performance

What happens with I/O requests over NFS and more specifically with Oracle? How does NFS affect performance and what things can be done to improve performance?

I hardly consider myself and expert on the subject, but I have yet to find a good clear  targeted description of NFS and especially NFS with Oracle on the net. My lack of knowledge could be a good thing and bad thing. A bad thing because I don’t have all the answers but a good thing because I’ll talk more to the average guy and  make less assumptions. At least that’s the goal.

This blog is intended as the start of a  number of blogs entries on NFS.

What happens at the TCP layer when I request with dd an 8K chunk of data off an NFS mounted file system?

Here is one example:

I do a

dd if=/dev/zero of=foo bs=8k count=1

where my output file is on an NFS mount, I see the TCP send and receives from NFS server to client as:

(the code is in dtrace and runs on the server side, see tcp.d for the code)

There is a lot of activity in this simple request for 8K. What is all the communication? Frankly at this point, I’m not sure. I haven’t looked at the contents of the packets but I’d guess some of it has to do with getting file attributes. Maybe we’ll go into those details in future postings.

For now what I’m interested in is throughput and latency and for the latency, figuring out where the time is being spent.

I most interested in latency. I’m interested in what happens to a query’s response time when it reads I/O off of NFS as opposed to the same disks without NFS. Most NFS blogs seem to address throughput.

Before we jump into the actually network stack and OS operations latencies, let’s look at the physics of  the data transfer.

If we are on a 1Ge we can do about 122MB/s, thus

122 MB/s
122 KB/ms
12 KB per  0.1ms  (ie 100us)

12 us  ( 0.012ms) to transfer a 1500 byte network packet (ie MTU or maximum transfer unit)

a 1500 byte transfer has IP framing and only transfers 1448 bytes of actual data

so an 8K block from Oracle will take 5.7 packets which rounds off to 6 packets

Each packet takes 12us, so 6 packets for 8K takes 76us (interesting  to note this part of the transfer goes down to 7.6us on 10Ge – if all worked perfectly )

Now a well tuned 8K transfer takes about 350us (from testing, more on this later) , so where is the other ~ 274 us come from?

Well if I look at the above diagram, the total transfer time takes 4776 us ( or 4.7ms) from start to finish, but this transfer does a lot of set up.

The actual 8K transfer (5 x 1448 byte packets plus the 1088 byte packet ) takes 780 us or about twice as long as optimal.

Where is the time being spent?

WordPress 3.1.2 Released…

WordPress 3.1.2 has been released. Happy upgrading…

Cheers

Tim…




Fast & Furious 5: Rio Heist…

It was only as I started to write this post about Fast & Furious 5: Rio Heist that I realised I’ve not seen the 4th film yet. I’m sure it’s not much different to all the others. This one has most of the old gang back together to drive around really fast and do lots of impossible stuff. It’s a nice bit of mindless escapism, just before getting back into my diesel Renault Clio to drive home at a sedate pace… :)

Cheers

Tim…




Perhaps I’m not making myself clear… Virtual Insanity…

What is it with virtualization? It’s like people remove their brain as soon as they hear the word. I get a lot of questions about virtual RAC installations and the vast majority of problems are due to people trying to run them on a host that can’t cut it.

  • When I explain you need 3-4G RAM for each VM node in a VirtualBox RAC installation, would you consider it sensible to try and build a 3 node virtual RAC on a host with 8G RAM? Do the maths. If you are attempting a virtual 11.2 installation with less than 3G RAM per node and you have problems, don’t come asking me because I will be out.
  • Do you really expect disk speed to be good when you put 8 virtual disks on to the system disk on the host machine? Do you think virtual disks magically run independently of the hosts disk subsystem? Putting all the virtual disks on one physical disk will work, but it will be so darn slow you will probably be leaving installations running overnight. Believe me, I’ve done it. If you want any sort of speed, spread the virtual disks around on separate physical disks.
  • Yes, you really are going to need all those IP addresses. Deal with it.
  • Use DNS or accept the fact there will be SCAN configuration errors during the installation. It’s one or the other baby!
  • Have you really done what my article suggests? Normally, after about a week of going back and forward I get a message saying something like, “Oh I fixed it. I didn’t bother doing step X before and once I did it it worked fine.” Don’t waste my time and yours. Follow the steps, or do it your own way and don’t blame me if it doesn’t work.
  • Use the same software I list in the installation. Don’t just assume that if it works fine with OL 5.x it’s going to work unchanged on OL6, SUSE x.x or Ubuntu (Lethargic Leaper) etc. If you want to try with different distributions that’s fine, but I’m probably not going to be able to help you because if there isn’t an installation guide for it on my site, it probably means I’ve not done it.
  • If it is the first Oracle installation you’ve ever done, don’t start by trying to install a virtual RAC. Learn to walk before you try running…

Cheers

Tim…




New Blog Round-Up

I've spotted a few useful posts lately and a few friends have started blogging so I thought I'd draw people's attention to them.

Neil Chandler is a UKOUG regular as well as a central and well-loved member of the informal London Oracle drinking massive (well, that's what I'll say about him in public anyway ;-)). He's an Oracle DBA and a contractor like myself so hopefully he can add a few interesting technical tales and gotchas as he encounters them. He's only just started blogging but hopefully if I draw attention to his blog, it will kick-start him past that critical start-up phase.

I'm researching SQL Plan Management for a client at the moment and whilst trawling the net for any useful resources, Maxym Kharchenko's Intermediate SQL kept cropping up. He has a number of posts about SPM that delve into the nuts and bolts and have proved really useful to me. It looks like there's other good stuff on there too.

But, with apologies to Neil and Maxym, I saved the best for last even thought it's absolutely nothing to do with Oracle. Over the past couple of years, I've been fortunate to work with some very smart, young and occasionally handsome people but that's more true of some than others ;-) One that it's been a pleasure to meet but is going to be lost to me all too soon is Gustav Andersson who, as well as being a top-notch Java guy (confirmed by other Java guys) has stuffed my head full of Agile/Scrum nonsense and been a good boss in recent times. (There's a lesson in there for managers everywhere - old guys can work perfectly well with much younger managers as long as they respect them!) But there's more to Gustav than that - a lot more - and, in retrospect, I realise I shouldn't have been surprised that he's about to embark on a magnificent venture. He will be blogging about it at The Modern Nomad and as soon as I read the first couple of posts I was as jealous of him as usual. You'll see what I mean when you read it. It's personal and far from light but it's so refreshing to read something completely different and nothing to do with Oracle.

The posts are currently just slightly in the past while he plays catch-up and he was reluctant for too much early publicity. As usual, there's plenty for me to jealous of there - a better template, better grammar, more substantial thoughts expressed more clearly and all from a Swede teaching me to write English. Actually, damn that guy. I'm glad he's on his way so he can stop the rest of us looking bad ;-) His blog is going to be great.

Good travels, man ...

The Final Adventures of Roger Wilco?

Regular readers might recall the previous adventures of Roger Wilco, the somewhat superior raccoon. (There are several links there, best read in order.)

Well last week I suspect we saw the final chapter in Roger's round-the-world trip which has included Birmingham, Malov, San Francisco, Malov, Birmingham, Cirencester and many points in between.  Through the magnificent efforts of some good Oak Table friends, he arrived with James Morle in London at the UKOUG Exadata event to complete the final leg. (I also finally took delivery of my new OT T-Shirt, which is very cool ...)

We all adjourned to a bar with outside tables and a wide selection of good beers so that Roger could say farewell to some of his new friends.

Left-Right - Roger Wilco, Paul Logan of Pythian, Dan Norris, James Morle, David Kurtz, Tanel Poder and the back of Peter Brink's head.

He almost didn't make it back though as he went missing again and I knew that I would be in deep trouble when I got home. When I saw the evil glint to Lisa Dobson's smile, I realised something fishy had been going on. He'd been kidnapped!

Once untangled, I was able to finally delivery him home to Edinburgh. His fur looks a bit bedraggled but he has lost none of his sense of superiority. In fact, he's much worse because he claims to have been places and seen things that a cuddly toy can never forget and the others are never likely to see. To say the atmosphere at home is a little frosty is an understatement!

I only hope that in time they bring him down to earth with a sharp bang and we can all get back to normal again. If not, I worry he might decide to go off on a new set of adventures.

As long as he doesn't find himself in this sort of situation.

Delayed Block Cleanout / ORA-01555

Here is a link to a collection of scripts that can be used for some entertainment (well, it probably depends on your personal definition of "entertainment"...) regarding "Delayed Block Cleanout". These scripts are meant to be used in a playground environment - do not attempt to get them close to anything important as they might have some undesirable side-effects. Please read the comments in the file header description before attempting to run them.

The scripts allow to gain some insights into the different variants of delayed block cleanout and how to force an ORA-01555 error caused by it. The scripts among others allow to demonstrate that blocks generated via direct-path inserts are not "clean" and subject to a special kind of delayed block cleanout and therefore - although unlikely - can still cause an ORA-01555 error due to delayed block cleanout. This has been discussed recently here.

It is amazing how much effort has Oracle put into minimizing the overheads of storing the lock information in the block and still offering fast commits:

- There are fast cleanouts for small transactions that leave blocks in a "clean enough" state (which means that only the commit SCN of the ITL slot is updated but everything else is left behind for others to clean up) but do not generate redo although they modify a block. The block will be dirty and written (possibly again) by DBWR

- There are "immediate" (as part of consistent / current mode gets) and "deferred" (piggy-back as part of subsequent block modifications) delayed block cleanouts for blocks left behind by larger transactions that will tidy up the block including any lock bytes and ITL slot information. These will generate redo and dirty blocks.

- There is a special treatment of blocks that are generated by direct-path inserts that do not need to be cleaned up as blocks modified by conventional DML but still miss the "commit SCN" information from the ITL slots. Only a single block will be cleaned out (small amount of redo and block dirtied) and the "commit SCN" obtained will be "cached" in the session for further blocks accessed by this session

For further reading regarding the topic I recommend these links as starting points:

- "Clean it up" by Jonathan Lewis
- "Impact of Direct Reads on Delayed Block Cleanouts" by Christian Antognini

Some final food for thought regarding delayed block cleanout:

- Direct path reads performed by parallel execution slaves
- Adaptive serial direct reads introduced in Oracle 11g
- Readonly tablespaces
- Index blocks modified by DML

YouTube Playlist…or “What someone does during Easter…”

Currently on (DBA) standby duty for some mayor Dutch sites (eh databases). Was a bit bored so created a YouTube playlist while listening to some of my favorite bands/musicians…

“Stuff I Like” (YouTube playlist) – or how sad can life be during Easter…Pretty good, I guess, after hearing this.

;-)