reads....

they are all about reads...

in cases of large bursts of redo - you could overwhelm a cache - but in general I would tend to agree with you.

I would like solid state disk for my laptop though - that would rock :) lower power consumption (longer battery) and possibly faster windows

as a desktop OS :) yes.

so I can run a linux virtual machine and drive powerpoint at the same time.

laughing out loud.

Sure, it'll make IO go faster, but so what?

Did you know that if you disable logging (just have oracle do everything BUT write the redo), the approximate speedup is near......

zero.

most of our time is spent doing other things. IO can be a contributor, but it is just that.

And SSD does NOT make you "infinitely fast" as far as IO goes. It just makes you faster. It might turn a one minute wait into a 45 second wait, but it will *not* make the wait go away.

unfortunately SSD doesn't really write much faster - if any faster at all - then spinning magnetic disks - especially for the type of writes we do to redo (sequential writes - magnetic spinning disks are great at that).

SSD might actually write slower than magnetic disk as they have to take great care to not write and rewrite the same areas all of the time. The first time you write to an SSD - fast. Over time however - they have to re-initialize areas, make sure they are not using the same areas over and over.

so, I would suggest a pretty intense benchmark (that makes sure you fill up the SSD many times over - write it a second time) to see if it would even be worth it.

they would benefit most

in single block IO's

when your cache cannot hold all of the data you need (you'd want them in the cache as that will be many times faster then ssd)

when you have the bandwidth to actually transfer the data from the ssd to your buffer cache - if you don't, don't even bother making IO's go faster - you need the bandwidth to move the data from disk to ram.




From experience the big performance problems I have encountered are
inefficient application design leading to massive logical IO which can be very
hard to correct in a performance tuning mode.

if you have that, ssd won't do anything for you except perhaps make that worse :)

You were hardly the first - Times Ten has been around for a while.

we have columnar storage already - an implementation that permits incremental loads in a very efficient fashion - not sure why you think we need a columnar engine.

Exalytic is geared towards analytics - analysis - as the name implies.

http://www.oracle.com/us/solutions/ent-performance-bi/business-intelligence/exalytics-bi-machine/features/index.html

(and incorporates columnar compression as well)

therefore it lightening fast.

I always laugh when I hear things like that.


"we do X, therefore we are lightening fast"

which seems to imply that in order to be lightening fast - you have to do X. Which of course is a falsehood.

The processing of data is done in the storage cell AND the database instance. And furthermore, if the data is done solely in the database instance - the database instance works with our compression units - and works at a columnar level, only going to rows of data when it needs to (just like any other columnar data store).

So, can SAP HANA push predicates and processing down to the disk - we do and therefore it is lightening fast. :)

you need to be on solaris or Oracle enterprise linux.

are you?


1) it'll use async io to write to both at the same time basically.

2) it'll look in the SGA and if the block is there - fine, you are done. If not, is the pointer to the block on the SSD there - if it is, do an IO against the SSD to get it. If not, do the physical IO

3) we keep a pointer in the SGA to the block on SSD, there is nothing on disk regarding it, it is a pointer in the SGA (so if you are using this, you'll need to allocate a bit more SGA to accommodate this extra information)

4) no, in memory parallel query requires real memory for the SGA. Adding SSD for a 'flash cache' does not increase the SGA size (in fact, it effectively makes it a bit smaller for the pointers).

I don't know what this 80% number is or means, sounds made up to me.

http://docs.oracle.com/docs/cd/E11882_01/license.112/e10594/editions.htm#sthref73

The license Guide contains this information.

I want to hope, that SSD can speed up FULL-TABLE-SCANS dramatically. Can you confirm this hypothesis?


extremely doubtful. spinning magnetic disks are good at doing 1mb IO's - the bottleneck almost always (as in always practically speaking) is not the ability of the disk to retrieve data, but rather the inability to transfer that data from the disk to the server (that is, the SAN or network or whatever is wholly insufficient). Given enough disks (spindles), sufficient IO bandwidth (network, hba's, whatever) - IO doesn't have to be a problem.

In the real world performance day I've been doing, we demonstrate how - in a properly set up system - reading from disk during a massive full scan to reading from plash cache (faster even then SSD) after it's been filled up - doesn't really equate to very much.

Where SSD/flash come into play - lots of single block IO's. Big full scans - no so much.


The most unpleasant thing in Oracle optimizer, in my experience, - is its unwillingness to use indices to join two big tables, and using hash joins instead.


wow, do you have a lot to learn - really. I'm not kidding. I am TOTALLY not kidding.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6749454952894#6760861174154

SSD ain't going to solve your problems. Architecting the right hardware will get you a bit of the way there - but you have a way to go.

I love full scans.
I love hash joins.

Indexes scare me to death when you start talking about 100,000 - 1,000,000 - or more rows being processed. totally to death

Well, I confess I know almost nothing about server hardware, because I am modest database developer.


get to know it, else a modest developer you'll remain...


So, I know, that sometimes we should love indexed access, and sometimes full table scans. Of course, I was speaking about queries which are supposed to return tiny amounts of rows, and need to see only few rows from big tables to make result set.

It is not that it needs to return tiny amounts of rows - it is that it is to PROCESS a tiny amount of rows.


select count(*) from one_billion_row_table where column_with_five_distinct_values = 'x';

that will count 20% of the rows in the table. It returns a TINY number of rows - namely 1. Should it use an index?



produce for us a concrete example that we can talk through.


I trust your sentence, Tom, that SSD will not save us, but it (or enhancement to existing disk system) could help.


Not much. On a well balanced system where you have the ability to actually transfer data (that is the brick wall you are going to hit - the ability to TRANSFER the data - you can probably already READ the data faster than you can a) TRANSFER IT, b) PROCESS IT - this is why you as a modest developer needs to become "unmodest" - else you'll be chasing rabbits down holes forever) and process the table - an SSD isn't going to do much for your full scans.

On a system that already cannot transfer the data fast enough (insufficient bandwidth) and even if it could could not process it fast enough (insufficient CPU) - SSD will do zero for you.


The "global" solution is to understand that we "architect" systems - well, at least we are *supposed* to. We don't just say "it must be good - we don't need to understand it - we'll just use whatever is there and assume it was well architected"

Optimizer hints and optimizer related parameters are useful, but we want "global" solution.


I disagree, my first step in "tuning" a system is to basically remove both and get to the root cause.

The root causes will either be:


a) incorrect cardinality estimates due to missing, non-representative statistics or SQL written in a way such that no one could correctly guess the estimated rows. We would use tools such as sql profiles, extended statistics, dynamic sampling to conquer that. If we get the right cardinalities - we get the right plan

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4043225300346897444

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4043033500346640019

b) inefficient sql written by people that don't know all of sql well enough to code something efficiently. For example the developer that does 15 self outer joins to PIVOT data in an "EAV" (entity attribute value) model. Two problems with that - problem 1 is the model itself, problem 2 is you do not need 15 outer joins to pivot - you can use either a "max(decode())" technique in a single pass OR the PIVOT command in 11g. there are tons of other examples of really 'poorly' written sql.

c) the hardware as configured just isn't going to do it - no matter what. and fixing "one thing" (magical SSD for example) isn't going to fix it - you need balanced resources from the disk on up to the server itself

we use the SSD purely as a *cache*, not for persistent storage when using the flash CACHE. That is what it does by design.

You have 10's maybe low 100's of gigabytes of SGA

You have 100's of gigabytes of SSD

You have 1000's (or more) gigabytes of Magnetic spinning disk <<<=== persistent



As for:


3) Are you saying that pointer of SSD disk reside in SGA, it means that if you
reach block with pointers you would need only one IO to get data.

Yes, that is pretty much what I typed. But not the "you would need only one IO to get data", you always need only one IO to read a given block. The flash cache allows that IO to be against the SSD instead of magnetic disk.



Why you recommend Solaris 11?. I think Oracle is moving away from Linux.
Solaris is too good.

where did I recommend an OS ?

We are not moving away from linux at all - what gives you that impression?

SSD's - the cheap ones we are talking about here, something you would buy at best buy - exhibit:

a) excellent random read (no seek time)
b) average large, sequential read (your transfer rates come into play)
c) average to less than average write performance (you have to be careful where you write to)
d) typically higher failure rates (although this is getting much better)
e) significantly higher cost


So, if you have some SSD, and wanted to put it to use, using it as a backing store - a cache - makes sense.


And the flash cache is a new feature of 11.2 - it has not been backported.

You get access to patches and upgrades with a license - yes.


http://docs.oracle.com/docs/cd/E11882_01/server.112/e25494/memory005.htm#ADMIN13391

you get upgrades (and patches). Sorry, I don't know how to say it more clearly?

You would upgrade 11.1 to 11.2. As long as you are licensed, you have upgrades. You get the base set of features - not any new options or features of a higher edition.

Meaning, if you have 11.1 enterprise edition with partitioning - you can upgrade to 11.2, you'll have every 11.2 enterprise edition feature (see http://docs.oracle.com/docs/cd/E11882_01/license.112/e10594/toc.htm for what are features of each edition) and still have access to the partitioning option - but not any new options that you don't license

If they are in a SAN it'll depend on how they are cabled to the server - doesn't matter how fast the IO is if you cannot transfer it from point A to point B.

And again- single block IO - they'll be really good. Having it in the buffer cache - better, but that would be a good second.

I don't know anything about this particular companies product - so best if I do not comment on the 'best buy' comparison one way or the other :)

with lots of cables

that are dedicated to you

with sufficient switches/hbas whatever so as to not be a single concentration point.


For example, if you have a GigE network - you can transfer (on the very very very best of days) about 100mb/sec. That is nothing. So you get a couple of those - but they all go into a single card. Or they hit a single switch. Or a single anything.


You need to make sure that you have put together the hardware in your environment to ensure you can move the data from the disk to the server - without a choke point.

blocks are scattered in the buffer cache - so blocks that are logically next to each other on disk are not stored next to each other in the buffer cache.

We get blocks one at a time from the cache.

Please utilize support.

I don't think the *answer* has changed, in that, as with *any* technology advancement, you should always test the claimed benefits against some real world analysis that is applicable to your particular business case.

Having said that, it is not really the ssd tech that has changed, more our way of accessing data. 10 years ago, vendors slammed flash into disk arrays, but you still accessed them as if they were standard disks, typically over a network of some sort, and often it was the network and/or controllers that were the dominant component of response time.

So a lot of people got "burnt" by flash, ie, it didn't match the hype.

With the advent of PCI attached disks, with near memory-speed style access, we are starting to see the true benefits of flash tech.

But our answer remains the same - if you don't have an I/O problem, throwing a chunk of into money into flash is just money wasted.

If you *do* have an I/O problem, then an investigation/benchmark on flash tech would be wise before spending that cash.