RE: Taking advantage of SSD Capabilities when Smart Flash Cache isn't an option?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Christopher.Taylor2@xxxxxxxxxxxx>, <kevin.jernigan@xxxxxxxxxx>
- Date: Wed, 29 May 2013 16:24:39 -0400
For modest sized clusters this is plausible if you control the applications. It
does not scale and requires some special attention on your part only because
temporary is bound to the user account instead of to the session.
IF temporary tablespace were bound to session (only defaulting to the temporary
tablespace bound to the user, analogous to the default tablespace being used
unless somewhere else is specified by the user), then you could create a login
trigger such that you would alter your session to use as temporary some
tablespace depending on your current instance's node location. Sadly this is
one of the Oracle VLDB enhancement requests that did not get enough votes to be
taken seriously by Oracle (some 58 of 63 vote winners were implemented by 7.1,
the rest by 8.1 if memory serves - no complaints there! The only ones that were
never implemented were supplanted by the revolution in Oracle's storage
partition implementation.)
Okay end of whining (if it is not obvious, this was one of my personal
requests. Two temps [so the merge runs could alternate sources and sinks on
sorting, for example got even less far.])
The workaround is to create multiple users for each schema such that the users
have all access (but use the schema user for all permanent creates to keep
things clean) to everything, one user definition per node. (That is the part
that is inconvenient to scale as it is all extra dictionary space. Of course
you only use one user per instance, so it is not a big deal to the shared pool.)
Then each user has a temporary assignment for the node on which it should run.
If the login "alter session temporary <temp designator>" trigger fails, it is
up to you to take care of it or the default temporary tablespace would be used.
And it would be up to you to make sure you used user1 to connect to an instance
on node1 so this is not compatible with some automatic failover/redirection to
a secondary working node/instance schemes.
Now if you have NFS mounting, then you can mount the SSD r/w on its local node
and read only on non-local nodes. In this manner you CAN use local SSD for
online redo logs, arch destinations, diagnostic out, what have you that only
needs to be writable locally but readable by, for example, alternate node
application of a redo log.
Would support have a fit? I don't know. You probably should not call them to
help you set up something like this, and if you do something that does not work
it is probably your problem, but there is nothing inherently unsupported: It is
read/write where it needs to be and anything that needs to read it can read it.
Good luck,
mwf
alter session default temporary TEMP1;
alter session secondary temporary TEMP2;
oh sweet if you want unchallenged and optimized i/o performance of some single
non-parallel task. Secondary would have to be 2N for parallel degree N with
subcomponents sorting.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: Wednesday, May 29, 2013 12:52 PM
To: kevin.jernigan@xxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Taking advantage of SSD Capabilities when Smart Flash Cache isn't
an option?
Ok, so it sounds like SSD have limitations on even being setup as shared
devices. I guess that makes sense as I think about it in more detail now.
Local HDs aren't easily sharable either so now I'm thinking my premise may be
in error to begin with.
The SSDs wouldn't be coming from an array. It would probably take an ugly hack
to get the servers to see the SSD devices on each host and have them
read/writeable from any host in the cluster. So, not really a good option
there then.
Chris
From: Kevin Jernigan [mailto:kevin.jernigan@xxxxxxxxxx]
Sent: Wednesday, May 29, 2013 11:46 AM
To: Taylor Christopher - Nashville
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Taking advantage of SSD Capabilities when Smart Flash Cache isn't
an option?
Ok, so if you're on 10g then Database Smart Flash Cache is out of the question
- it was introduced in 11gR2. If the SSDs are set up as drives in a storage
array, then they might be sharable - but we've seen several "all flash" arrays
from various storage vendors, none of which can be mounted as shared storage,
so you should check to be sure the SSDs are truly share-able, and that there
aren't sneaky performance bottlenecks hiding in the storage controllers etc...KJ
Kevin Jernigan
(650) 607-0392 (o)
Senior Director Product Management
(415) 710-8828 (m)
kevin.jernigan@xxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
