RE: Taking advantage of SSD Capabilities when Smart Flash Cache isn't an option?

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 29 May 2013 16:24:39 -0400
Message-ID: <035b01ce5caa$8c820510$a5860f30$_at_rsiz.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net Sent: Wednesday, May 29, 2013 12:52 PM
To: kevin.jernigan_at_oracle.com
Cc: oracle-l_at_freelists.org
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_at_oracle.com] Sent: Wednesday, May 29, 2013 11:46 AM
To: Taylor Christopher - Nashville
Cc: oracle-l_at_freelists.org
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_at_oracle.com

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 29 2013 - 22:24:39 CEST