Oracle Tips and Tricks

GoldenGate capture with compressed tables will be available soon!

2012-01-30T12:49:00.000-08:00

I've been told "Golden Gate will support compressed tables...soon" for literally years now by multiple people at Oracle. My first SR on this was opened on March 29th, 2010, nearly 2 years ago! That SR's resolution was to add me to bugs:

bugdb - 9416239 which tracks SUPPORT of OLTP TABLE COMPRESSION
bugdb - 9428399 which tracks exadata V2 HCC compression
bugdb – 9426065 - SUPPORT ORACLE COMPRESSED TABLES

Mining compressed tables is a necessary requirement for many large databases that use CDC...especially databases in Exadata, where you're all but expected to make use of Hybrid Columnar Ccompression (HCC). Think of all the benefits of compression in your database:

1. Compressed data means smaller datafiles, which means faster restore times, improving your RTO.

2. Your 8k block actually stores more than 8k of data which means you need to read/write fewer blocks. Since the blocks are stored compressed in memory, it also means you increase the amount of data in the same size of your db cache. As the blocks move from memory to disk, it improves your potential IOPS capacity. Ok, not really increasing IOPS capacity, but increasing the amount of data you can move per IO, which has a similar effect.

3. Compression means less storage requirements which in turn mean less storage costs.

In a previous post, I mentioned how, when I first started looking at Golden Gate with Exadata, I found that not only were compressed tables not captured, if GG came across a logged change of a compressed table in the archivelogs or redo, it would abend...even if it was a table that was excluded from capture. We submitted a prio 1 SR and Oracle created a patch for this-so we were able to begin using GG with Exadata...but all tables that used compression were excluded from GG capture because of this limitation-put another way, we were forced to not compress tables that needed to be captured. Those were all the biggest, most compressible tables. This forced my client to use much more storage in their Exadata cells than they anticipated, and today they're preparing to buy additional storage cells, partially because of this limitation.

Imagine the fun they had explaining to their management they need hundreds of thousands of dollars to purchase additional storage from Oracle, ultimately due to an Oracle bug. :) Come to think of it...no wonder it took Oracle 2 years to fix this! :)

Not to get too off track, but for a truly high-performance database, you should really test how advanced compression affects your system performance. Your milage will vary, but based on my testing, I would expect it to improve your performance. See http://otipstricks.blogspot.com/2011/02/exadata-index-dropping-and-compression.html. There are other huge performance improvements 11.2.0.3 offers for Exadata, especially for OLTP environments.

Performance aside, IMHO you should begin to prepare for the upgrade to 11.2.0.3 PB 3 to take advantage of this new GG feature/bug fix, so you're ready to go when the new version of GG is released.

I have no special knowledge from a friend at Oracle this time...I'm gleaning this from a statement in a PDF from Douglas Reid, Oracle GoldenGate product mgmt. GG 11.2.1 will have tighter integration with XStream Out API (Capture), which means GG will be using a call to a procedure already in the Oracle kernel. That internal call will be what handles OLTP and HCC compression, which to this point hasn't been possible. Soooo...based on the schedule in that PDF of the approx March/April release of GG 11.2.1, there must be, prior to that time, a database change to allow that. Since we can't do it in 11.2.0.3 PB2 and its going to happen in the next several weeks...it must be coming in PB3. The release schedule "remains at the discretion of Oracle"...but short of mind reading, this is the best we've got.

So, 11.2.0.3PB3 will be out w/in the next few weeks. Based on the previous release cycles of patch bundles, it will be sooner than that. I would guess sometime this week Exadata 11.2.0.3 PB 3 will be released (it requires Exadata Storage Server 11.2.2.4.) Check Metalink note 888828.1 for updates...by the time you read this, you'll likely see 11.2.0.3 PB3 listed in that note...if not, check back in a few days. (Add that note to your MOS favourites by clicking the little star on it. Its the best source to find what's currently GA.)

To sum it up, when you use GG 11.2.1 with Exadata 11.2.0.3 PB3, you'll be able to FINALLY mine compressed tables in Goldengate.
I could be wrong, but my impression is that a lot of people at different companies are using Goldengate with Exadata. "Extreme Goldengate Performance on Exadata" is one of my most popular posts. Given the cost per GB of storage in Exadata, compression could save you a *huge* amount of money. Once GG 11.2.1 is released, the only reason I can think of NOT to compress everything you can is that your access patterns don't work well with it...assuming the budget that was big enough to buy Exadata is also big enough to license compression. ;)

After ~2 years of waiting for these 3 bugs to be fixed, the wait is finally over.

Yet another plug for a great book

2012-01-04T14:40:00.000-08:00

If you know anything about Oracle databases, you've likely heard of Jonathan Lewis and the great work he's done in the past on Oracle internals (why things do what they do...what's going on under the hood of the database)...especially his work on the cost-based optimizer. I know he's inspired me to be much better than I would have been, because after reading his books, I was humbled by my relative ignorance, and became desperate to improve. He's literally an alien of extraordinary ability....

We exist in a field filled with many geniuses. What sets him apart from all but very few is his ability to bring people to his depth of understanding...his ability to explain complicated things is unmatched...but come to think of it...many of the "greats" have this ability. Carry Millsap, Tanel Poder, Richard Niemic, Kerry Osborne...not to mention many others. Hmm...maybe that's the difference between obscurity and notoriety...

I bring this up because I've been reading one of his newest books, Oracle Core: Essential Internals for DBAs and Developers. His approach to explaining things in the book is interesting...he's recognized the circular manor of Oracle internals...you have to have a clue about everything before you can get a little depth about something in particular. You have to have a little depth about everything before you can get deep into anything. This is how he explains things. By the time he gets into real depth, he's already brought you to a place where its just a small step, rather than a leap. This makes his book interesting to people who have used Oracle for years and newbies too.

I'll add it to my book list on the right....

Databases on Flash done Inexpensively

2011-12-22T13:45:00.000-08:00

If you're looking at putting your database (SAP, Hana, Sql Server, MySQL, Oracle...whatever) on flash, you should *really* take a look at FusionIO. FusionIO is how Facebook is able to drive its MySQL databases so fast. The ioDrive card is a single point of failure for your storage, so you need some way to protect it. Oracle ASM's normal redundancy is effectively software mirroring...and its very simple to use and set up. If you have the budget, you can get truly extreme performance by using ASM's normal redundancy to mirror two ioDrives, but this method of protection would effectively double the cost of your FusionIO purchase.

If you need more performance than you currently have, and you can't afford the cost of the storage it would take to put your entire database on flash, there's a different way to get it done, while still protecting the storage from a single point of hardware failure.

In my previous post about databases on flash, I talked about "option #4", which is-set up ASM as if its an extended cluster (when its not) and set it to use the fast, relatively expensive (per GB) FusionIO ioDrive storage as the preferred read failgroup. The other failgroup is your existing SAN storage. There are advantages of doing this instead of just getting faster storage on your SAN. You're more HA than before, because you're protected from a storage failure from either the SAN or from the ioDrive. If the SAN storage is unavailable, the database will stay up and running, tracking all the changes that are made since the SAN failed. Depending on how long the outage lasts, and how you configured the diskgroup, when the SAN comes back, it'll apply those changes and after a while the SAN storage will again be in sync with the ioDrive. If the ioDrive goes down, the database will continue to run completely on SAN storage until the ioDrive is back online...at which point, things can be synced up again.

I wanted to quantify how this performs in a little more detail with a few tests. Using the uc460 from the previous DB on Flash tests, I added an HBA, set up some mirrored SSD's on a SAN with 2 partitions, configured an ioDrive with 2 partitions and then I created 3 databases...one purely on the SSD's, one purely on FusionIO, and one that uses normal redundancy with the preferred reads coming from FusionIO. All the databases are set up exactly the same, with only 256M of db_cache each.

Here is atop reporting IO while Swingbench is running on a database that's completely on the SSDs (sdb is the SSD presented from the SAN).

Test 1:

This is the same idea, but this time no SSD...with purely the ioDrive (fioa).

Test 2:

To set the preferred read fail group, you simply need to do this:

alter system set asm_preferred_read_failure_groups = 'diskgroup_name.failgroup_name';

This is the combination of the two, letting ASM distribute the write load in normal redundancy, with reads coming from the FusionIO card.

Test 3:

...as you can see:

1. All reads (yes, except for those 4 stray IO's) are coming from fioa, and the writes are distributed pretty much equally (both in IO's and MB/s) between fioa and sdb.

2. Atop is showing that even though all reads are coming from fio and its doing the same amount of writes as the SSDs on the SAN, its still easily able to keep up with the workload...its being throttled by the slower sdb storage. One more time I have to point out...the ioDrive is sooo fast. Incidentally, this speed is from the slowest, cheapest ioDrive made...the other models are much faster.

3. The Swingbench workload test is forcing the exact ratio of reads/writes will always happen. The potential for more than the 200% read performance shown above exists. What you would see if you logged in to the database while the test is running is that reads are lightning fast, and writes are 30% faster than they've ever been before on the legacy storage. In this configuration the legacy storage is only required to do writes and no reads, so its able to do the writes faster than before (60,596 vs 46,466 IO's and 57.19 vs 43.92 MB/s). All this performance boost (200%+ reads, 130% writes), and we now have an HA storage solution...at half the cost of moving the database completely to flash.

In the real world, your legacy storage wouldn't be a mirrored SSD on a SAN, it would likely be much faster FC storage in a raid array. This ioDrive could be a failgroup to san storage 3 times faster than the SSD before you'd get diminishing returns...at which point, you could just add a 2nd ioDrive. Still, I think the approach and the results will be the same. Unless you have a legacy SAN solution that's faster than a few of these can do together, there are definite performance gains to be had.

Flash with Databases-Part 3

2011-12-19T13:01:00.000-08:00

In my previous posts on this topic, I talked about flash technology of SSD's and compared it to the performance of a FusionIO ioDrive card. I also talked about how, in order to justify the cost of high performance storage, especially FusionIO technology, you have to transform the storage discussion from "How many GB of storage do you need?" to "Besides how much storage, what are your IOPS requirements?" I'm telling you now...you will get resistance from storage administrators. Try to point out the extreme case...that you wouldn't run the enterprise database on slow sata disks...so by bringing up FusionIO, you're just talking about a different, faster storage tier...specialized for databases. They'll point out that the storage isn't 100% busy now...so getting faster storage won't help. This is a fallacy...well, I should say...this might be a fallacy. Usually % busy is talking about either average throughput/max throughput or percent of time waiting on storage. Response time (which is key to designing a fast database) is often hidden from those reports. This means your storage is often your bottleneck...you just aren't measuring the right things...so you can't see it in your reports. When he shows you your slow database is only 50% busy on storage and your AWR reports are complaining about your storage performance...What is the bottleneck then? If your CPU utilization is low, you don't have excessive waits on locks and your AWR report is complaining about storage...I'll bet its your storage.

So...how do you design for necessary IOPS? This is actually a more difficult question that you might think. What I did a few months ago for a client to consolidate 22 databases into one was...add up each of the requirements of the 22 databases (found by dba_hist_iostat_filetype), joining by the time of their snaps, and then I could find the requirements of the peak times at the storage level. This was interesting because the peaks and lulls for the performance requirements didn't coincide when we would have thought.

For a single database, its much easier. I'm talking about OLTP databases here...for DSS/warehouse databases...look at throughput, not IOPS. As it relates to configuring for FusionIO, this query below will get you started. DBA_IOSTAT has the number of physical IO's by file type and increments since the last bounce. It will also reset when you bounce your database, so you have to filter out negative results. What I did below is take the number of IO's between two snaps in sequence, then found the number of seconds in the duration of that snap, and divided to get the IOPS. If you'll looking at a DSS database, you can look at the other columns in that view to find throughput per second.

select * from (

with snaps as (

select snap_id, sum(small_read_reqs+large_read_reqs) reads, sum(small_write_reqs+large_write_reqs) writes

from DBA_HIST_IOSTAT_FILETYPE HIOF

group by snap_id

my_snaps as

(select snap_id, instance_number, begin_interval_time, end_interval_time,

extract(second from (end_interval_time-begin_interval_time))+

(extract(minute from (end_interval_time-begin_interval_time))*60)+

(extract(hour from (end_interval_time-begin_interval_time))*60*60) seconds

from dba_hist_snapshot)

select s1.snap_id snap_1, s2.snap_id snap_2, to_char(ms.begin_interval_time,'MM/DD/YYYY') sample_day, sum(s2.reads-s1.reads) reads, sum(s2.writes-s1.writes) writes,

trunc(sum(s2.reads-s1.reads)/sum(seconds)) rps, trunc(sum(s2.writes-s1.writes)/sum(seconds)) wps

from snaps s1, snaps s2, my_snaps ms

where s1.snap_id=ms.snap_id

and s1.snap_id=(s2.snap_id-1)

and (s2.reads-s1.reads)>1

group by s2.snap_id, to_char(ms.begin_interval_time,'MM/DD/YYYY'), s1.snap_id

order by 7 desc

) where rownum<11;

The output will look something like this:

SNAP_1	SNAP_2	SAMPLE_DAY	READS	WRITES	RPS	WPS
			664318	6492660	184	1801
			521170	6356511	144	1763
			2242398	1930744	1984	1708
			1836943	5757317	509	1597
			68419	9689	11201	1586
			7373341	2418357	4794	1572
			631855	5669473	175	1571
			5218554	5272317	1446	1461
			1059836	4884479	293	1354
			5422603	4726101	1504	1310
			631855	5669473	175	1571
			5218554	5272317	1446	1461
			1059836	4884479	293	1354
			5422603	4726101	1504	1310

For configuring your database to use flash from SSD/FusionIO storage, there are 4 ways to do it that come to mind (there might be more...if you know of a better way than these, let me know)...each with their own pros and cons. The problem we're trying to deal with here is that an SSD or FusionIO is a single point of failure:

1. Use Oracle's DB Cache Flash. This can be thought of as a level 2 extension of the db_cache. As a block ages out of the db_cache in RAM, it goes into the db flash cache.

Pros: Easy to use, designed for flash. Since it moves "hot" data at the (usually) 8k block level, its *much* more efficient than most SANs that move hot data at a page level. Depending on the SAN...this can be anything from a few megs to 256MB+. Usually the SAN technology moves the data on some sort of schedule...ie: daily, it will look to see what 42MB sections were hot, and move them to flash. If your activity is consistent, that's fine, if not, you probably won't be helped by this. So, obviously moving hot blocks in real time is much more efficient.

Cons: There's a "warm up" time for this strategy...the db_flash_cache is cleared when there's a db bounce, and blocks need to come back to the cache over time. Also, you have to use OEL for this feature to work. From what I've been told by smart people at Oracle, this isn't a technical requirement, its built in to the database logic. Its a marketing ploy to encourage people to migrate to OEL. I'm the biggest cheerleader I know for companies to at least take a look at Oracle Enterprise Linux...it has a lot of merits that mostly come down to support cost savings. If companies choose to go with OEL, it should be because its the best choice for them...not because Oracle forced them to do it by disabling features in their database if they don't. This is the same argument I made for enabling HCC on non-Sun storage. What happens if the FusionIO card or SSD you're using for db_flash_cache crashes? I know the data isn't primarily stored there...its also on your normal storage, so your data isn't in jeopardy...but will it crash your database? If so, you need 2 FusionIO cards to mirror the db flash cache, which doubles your FusionIO storage cost.

2. Use Dataguard. You can set up a 2nd server and have the redo applied in real-time to it, so if there's a failure on the local FusionIO storage, you just fail over to the new server. Both servers would have a single FusionIO card.

Pros: Provides redundancy for all non-logical problems, including server hardware.

Cons: This doubles your server hardware costs, FusionIO storage cost and might more than double your Oracle licensing fees.

3. Use Oracle's ASM to mirror the storage across two FusionIO cards with normal redundancy.

Pros: Easy to do

Cons: Doubles $ per GB costs of storage

4. Use Oracle ASM's extended clustering option and set up FusionIO as the preferred read failgroup, and use cheaper SAN storage for the 2nd failgroup in normal redundacy. This is a topic for a whole new post.

Pros: Provided redundancy between FusionIO and existing legacy storage, but send no read IO's to that legacy storage, making it able to keep up with write IO's much faster. Usually, reads are over 50% of OLTP workload...sometimes 3-4X the writes. You can use the query above to find out if that's true with your workload. This means, when paired with FusionIO, your existing storage will perform many X faster than it is today because its only getting a fraction of the workload. Its also HA: if there's a failure in your legacy storage or your FusionIO storage, the database keeps running while you correct the issue. 11gR2 has a new feature to increase the sync speed of fail groups after the problem is corrected...it tracks changes being made while one of the failgroups is down. When it comes back, it only has to apply those changes, rather than copying all the data. Your reads (and therefore, the majority of your database workload) are as fast as can be provided by FusionIO (very, very fast).

Cons: Writes don't perform as well as a pure FusionIO solution, but writes will perform faster than your existing storage, because they won't get the read workload.

When you consider the cost/benefits...#4 is an excellent solution. #3 is better, if you have the budget. If you're doing RAC, I think #1 is your only option, if you want to use FusionIO. Remember if you doing RAC, that the db flash cache in #1 is local to the node. Normal L1 db_cache will go across the interconnect to other nodes, but L2 db_flash_cache does not.

For comparison's sake, the output above from the query is from a fairly fast database with 40+ cores on a fast EMC Clariion CX-4 with 15k FC storage. This is NOT to say that's the performance limit of the CX-4, its only to say that this particular database could perform as well on FusionIO. Here's the numbers from swingbench on a single Fusion IO with the UC460 server I used in my previous post:

SNAP_ID	SAMPLE_DAY	SNAP_ID2	READS	WRITES	RPS	WPS
21	12/15/2011	20	8297280	10464273	3843	4846

...this means FusionIO is a viable alternative to a large, expensive SAN storage for this particular OLTP database, if you can deal with its "local storage" limitations. This db could perform 2.5X or faster on FusionIO...the size of it would make me want to get 2 cards...which would give us many times the potential IOPS it can get today for the SAN, for just a few thousand dollars.

All this so far was about OLTP...let's talk for a second about DSS databases. I've seen 1900MB/sec throughput on a Clariion CX-4 that was short stroked with many 15k FC drives on an IBM P5 595...I'm sure there are better results out there, but from a database, that's the best I've seen on a CX-4. It would likely take 2 FusionIO cards like I tested to match that throughput (based on specs), but there are other, bigger, badder FusionIO cards that could blow that performance away with speeds up to 6.7GB/sec/card, while still getting the microsecond response times. Assuming you have several PCI-E slots in your large server, you could use many of these things together. Unless you have a monstrous server, with this storage CPU is the new bottleneck, not storage.

Summary: Ignoring products like Kaminario K2, FusionIO has a niche-it can't work for all workloads and environments because it has to be locally installed in a server's PCI-E slot. There are a lot of ways to use it with databases...make sure you recognize the fact that its a single point of failure and protect your data. For OLTP databases that are a few TB, I can't imagine anything faster. FusionIO ioDrive has become the defacto standard to run SAP's HANA database, and more and more Sql Server databases are using it to break performance thresholds too. List price for the entry level card I tested (384GB/MLC) is around $7k, but the cost for the larger cards isn't linear...the 2.4TB cards are cheaper per GB than the small card I tested. The FusionIO Octal cards are 10.24TB in size. If you have a few thousand to spend, you should check it out. For that matter, if you have a few thousand laying around...take a look at their stock, FIO. Its a small company with a great product that's taking huge market share. I've been told they're currently the flash market leader, after only 1 year. I wouldn't be surprised if they get bought out by one of the storage giants (Dell/EMC) soon.

<*UPDATE*>
I've just heard that FusionIO has added a former HP hotshot to its board...I guess we now know who's going to buy them out. :)

Also, I came across this interesting blog talking about FusionIO with MySql.
<*/UPDATE*>

I realize this is starting to sound like a sales pitch for FusionIO...I have no ties to them at all...but as a geek, I get excited when I see technology that can boost database performance to this degree. People are afraid of flash technology because very early products didn't have the longevity or stability that was offered by enterprise class FC disks. This has hugely improved over the years. The FusionIO cards are expected to last ~7 years, and have a 5 year warranty.

Flash with Databases - Part 1

Flash with Databases - Part 2

Flash with Databases - Part 3
Flash with Databases Inexpensively

Flash with Databases-Part 2

2011-12-19T10:43:00.000-08:00

In my previous post on Flash with databases, I talked about the upcoming FusionIO tests.

Here's the hardware configuration overview:

Server1 : Dell 610 2X4 (8 cores)

Server2 : Cisco UC460 4X8 (32 threads, 16 cores)

SSD : 2 mirrored SSD disks

HBAs : 2-4GB/s

DB_CACHE : 256MB
FusionIO : IODrive

First, let's look at the SSD to establish a baseline. The configuration is 2 SSD disks, mirrored over 2, 4GB HBA's. The tests were done multiple times, so these results are averages. For all the Swingbench tests after the first, I reduced the db_cache to just 256MB to reduce the IO to be nearly all physical. I want to stress the storage, not the cache. A good storage test must have the storage be the bottleneck. It might be interesting to compare these results to the Hitachi USP-V tests from last year. The testing was very similar, but the results were opposites of each other, due to the extreme response time differences.

ORION			Swingbench on 11gR2				topas/atop
IOPS	MBPS	Latency	Max TPM	Max TPS	Avg TPS	Avg Resp	Disk Busy %	CPU%
8303	100.98	0.52	164393	3489	2197	62	100	1.61
			54674	1100	872	156	100	1.1

IMHO, these are very nice results...this storage would support a very fast database. Obviously, more SSD's would mean more throughput (if we have enough HBA's.)

The SSD tests above were done on a Dell 610, dual-socket, 8 core server. As you can see from the green Disk Busy and CPU columns, atop was reporting 100% disk utilization and 1.1 core utilized (110% cpu used.)

When I first started the tests using FusionIO, I could see a huge speed difference right off. Here are the Orion results:

ORION
IOPS	MBPS	Latency
33439	750.5	0.08

Compared to the SSD, that's 4X the IOPS, 7.5X the throughput and 6.5X faster response time! The response time is measured in milliseconds...so what Orion is saying is that the response time is .08 milliseconds...which is 80 MICRO seconds. My baseline is very fast SSD disks...to give you perspective...it wouldn't be unusual to see normal FC storage on a SAN show 10-15 ms latency.

Above, I mention that in order to test storage, the storage has to be the bottleneck in the test...but I had a problem when I did the Swingbench test. No matter what I did, I couldn't cause the storage to be the bottleneck after I moved the indexes, data and redo to flash. At first I thought there was something wrong and the FusionIO driver was eating up the CPU...but I didn't see the problem in the Orion tests on FusionIO, so that didn't make sense.

Swingbench on 11gR2				Topas/Atop
MaxTPM	Max TPS	Avg TPS	Avg Resp	Disk Busy %	CPU%	Notes
20726	478	261	597	100/6	6.64	Indexes on Flash;First time using the FusionIO-CPU is very high
189132	3522	2897	47	100/63	4.92	Indexes and Data on Flash
162165	2868	2638	42	97/100	8	Indexes, Data and Redo on Flash;all CPU cores are completely pegged

The problem wasn't the driver...the problem was I was able to generate more load than I had ever done before, because the bottleneck had moved from storage to CPU...even with my 256MB db_cache!

Notice too that atop was reporting FusionIO was 100% utilized...strange things happen to monitoring when CPU is pegged. For the Disk Busy% field, the first number is the SAN storage, the second number is % busy reported by atop for the FusionIO card. This 100% busy is accurate from a certain perspective. The FusionIO card driver uses the CPU from the server to access the storage, and it couldn't get any cpu cycles to the driver to access the storage. It appears to atop that its waiting for the storage, and so the storage was reported as 100% busy. That isn't to say the FusionIO card was maxed out...just that you couldn't access the data any faster...which isn't exactly the same thing in this case. The CPU bottleneck created what appeared to be a storage bottleneck because FusionIO uses the server CPU cycles, and there weren't any available. I didn't investigate further, but I would suspect a tweaking of the nice CPU priority settings for the driver's process would allow the FusionIO to perform better and report more accurately. At any rate, the Dell 610 with 2X4 (2 sockets, 4 cores each) couldn't push the FusionIO card to its limits with Swingbench because it didn't have the CPU cycles needed to make storage the bottleneck.

To deal with this CPU limitation, Cisco was kind enough to let us borrow a UC460, which has the awesome UCS technology and 4 sockets with 8 core processors each. I only had 2 sockets populated, which more than doubled my compute power, giving me 16 Nahalem-EX cores and 32 threads of power (a huge upgrade from 8 Nahalem-EP cores). I installed the Fusion IO card and retested. With everything in the database on FusionIO with my extremely low db_cache to force physical IO instead of logical IO, it took 12.37 Nahalem-EX threads to push the FusionIO card to 100 utilization. When I first did the test with SSD on the Dell, using a normal db_cache, I was only able to get 167k TPM. Here, I did 170k. This means I was able to get more speed with purely physical IO's than I was able to get from physical+logical IO's on the SSD's.

Swingbench on 11gR2				topas/atop		Notes
Max TPM	Max TPS	Avg TPS	Avg Resp	Disk Busy %	CPU%
170036	123438	2749	48	0/100	12.37	Everything (including undo) on flash

This made me wonder...if I didn't hold back the db cache, what could the Cisco UC460 do with FusionIO? In a normal db configuration, how would it perform? The answer:

Swingbench on 11gR2				topas/atop		Notes
Max TPM	Max TPS	Avg TPS	Avg Resp	Disk Busy %	CPU%
440489	7599	6888	8	0/60	31.41	Everything on flash, with normal memory settings

It took almost 32 threads, and now I'm once again almost 100% CPU utilized. At this point with 32 threads on a high-performance Cisco UC460, the FusionIO is only 60% utilized. This is the fastest server I have to test with...there's never an 8X10 laying around when you need one.... :) That's ok, I have enough information to extrapolate. If 32 threads can drive this thing to 60% utilization...I can calculate this:

Swingbench on 11gR2				topas/atop		Notes
Max TPM	Max TPS	Avg TPS	Avg Resp	Disk Busy %	CPU
734148	12665	11480		0/100	53	Extrapolating (if we had the CPU to make FIO the bottleneck)

Unless some new bottleneck is introduced, a database on FusionIO, (with the 53 Nahalem-EX threads to drive it), would be the fastest database I've ever seen, according to Swingbench testing. 734k TPM....I'm not talking IOPS...I'm saying achieved transactions, with each one having many IO's. To put things in perspective, that's 13.6X faster than the SSD. There are 6 PCI-E slots on this server...so we could easily still see the bottleneck move to CPU, even with 4 sockets and 64 threads.

To be fair though, the SSD disk has a lot of things outside of the control of the manufacturer...it has to go though SAN hardware and HBA's. The FusionIO has direct access with the PCI-E bus...which isn't really a bus, if you get into the details of PCI-E. That's one of the reasons why, as the FusionIO card gets more and more busy, the response time continues to stay low. In my testing, the worst response time I saw was .13 milliseconds (there's a . before the 13...that's 130 microseconds...not quite RAM speed, but closer to RAM response time than spindle response time.)...when the UC460 was completely pegged (which alone, is a difficult thing to accomplish.)

Summary:
FusionIO is ridiculously fast and from an IOPS/$ perspective, extremely cheap. They captured a huge percentage of the flash market in the first year of their public offering, and its easy to see why. In the techy world we scratch and claw for any technology that can bring a few percentage of performance improvements to our databases. Its rare we see something that so completely transforms the environment...where we can shift bottlenecks and get 10X+ performance. Think about what this means...today you likely have CPU monitoring on your servers to make sure a process doesn't spin and take up CPU needlessly...that's going to have to go out the window or become more intelligent...because the CPU will be expected to be the bottleneck. We'll need to use Oracle Resource Manager more than ever.

Downside: FusionIO is local storage (for now.) Although their cost per IOP is low, their cost relative to cheaper storage on SAN is very high from a GB/$ standpoint, which is the traditional way storage is approached from storage administrators. Its takes a strong database engineer to convince management to approach the issue from an IOPS requirement rather than a GB requirement. Its a paradigm shift that needs to be made to reach ultimate performance goals. Kaminario has addressed the local storage requirement issue well, from what I've heard. Hopefully I'll take a look at that within a few months.

So...flash technology and especially FusionIO can be a game changer...but how can you configure it to be useful while most efficiently using your storage budget for databases? I'll look at that next...

Flash with Databases - Part 1
Flash with Databases - Part 2
Flash with Databases - Part 3
Flash with Databases Inexpensively

Flash with Databases-Part 1

2011-12-16T11:00:00.000-08:00

For those (like me) that just want to see the results, go <* HERE *>

I've been asked to review some flash storage from different vendors from a database perspective. Storage Administrators have run their battery of tests and at this point narrowed down the field to a famous enterprise storage manufacturer's SSD's on SAN storage and FusionIO (320GB first generation). The difference between these technologies is pretty vast...starting with their interface. Placing SSD drives in a SAN solution is transparent to the server...it could be any other storage (FC, SATA) connecting to the server from the SAN...it just gets presented as a lun. There are a lot of advantages to that approach with ease of administration and scalability. FusionIO uses PCI-E cards connected locally to the server. There are obvious pros and cons to this approach too. If a card dies, you have to go to that server and pull the card...also, there are a limited number of PCI-E slots in servers...some of the most popular, dense servers, like Cisco's B230M2 have no PCI-E slots...so they aren't compatible with FusionIO. The sales guys from FusionIO tell me they're working closely with Cisco to begin selling storage with mezzanine interface, to eliminate the PCI-E slot requirement.

For FusionIO, the speed of a direct PCI-E interface is tremendous, and it really shows up in the response time...which, to a database is hugely important...but being limited to a single server for this storage, IMHO is a limitation that's difficult for a storage admin to accept. The SAN-based approach taken by the SSD manufacturer allows much more simple, traditional central management of the storage through an HBA to your server. Likely, if you went with this solution it would be the same as your current FC storage, only faster. No major changes needed for monitoring, administration, etc. These advantages of the SSD solution can't be quantified, but they should at least be mentioned. FusionIO's response is that their cards are expected to last through ~7 years of typical use (depending on how much storage you reserve, which is configurable) so although administration and monitoring can be handled with SNMP traps and hot-swappable interfaces, its likely unnecessary because your server will be life cycled out long before your need to replace your FusionIO card. That being said, even the most reliable hardware has failures, so your configuration needs to compensate with redundancy.

The plan is to test the storage with Orion and Swingbench, moving different items in the database (data, indexes, redo logs, temp, undo) onto flash to find the best way to use flash and to see the performance benefit. Much of this testing has already been done by others, but there were some things I saw missing in the flash tests on databases I've seen:

1. The affect of undo on flash

2. The resource consumption of FusionIO, which uses a driver to simulate disk storage. This must introduce overhead, but I've never seen it measured.

3. As a flash device fills, it slows down. To be precise, its write performance slows down. Performance can also degrade after extended periods of high IO. How will this affect a database?

The tests will be similar to the tests done in previous posts to compare storage on/behind the Hitachi USP-V. Part 1, Part 2, Part 3, Part 4.

FusionIO uses a driver on the server to access their storage to emulate a disk drive...similar to a RAM disk. This strategy must introduce some overhead. Texas Memory Systems considers that a selling point of their flash technology, it offloads the CPU overhead to a chip directly on their flash cards. Since Oracle licenses by core on the system (yes, that's overly simplified), that means you have to pay Oracle for the expensive cpu cycles used by FusionIO. This may not turn out to be significant...we'll see in testing. I may get a look at one of the TMS systems in a few weeks...it'll be an interesting comparison to the FusionIO card, since they're both PCI-E based. I'm also thinking about throwing Kaminario in the mix if that's possible. Kaminario doesn't have the name recognition of EMC, but they have an interesting product, designed for ulta low latency of flash. They use DRAM for their tier 1 storage and FusionIO for their Tier 2 storage. A rep from FusionIO told me their cards in Kaminario are nearly as fast as their cards plugged directly into a local PCI-E slot...pretty interesting.

Which is cheaper?

FusionIO and all single SSD drives I've seen are a single point of failure. For a database that's important enough to your company to justify the cost of ultra-fast storage, its likely important enough to require elimination of a hardware single point of failure. For an SSD on a SAN or NAS you already own, that might mean you just increased the cost per GB by 25%, (because you'll do 4+1 RAID5.) For FusionIO, since there's a limited number of PCI-E slots, your best option for a database is to do what Oracle did on Exaadata...use ASM's redundancy features...in this case, that means you probably mirror it with normal redundancy...which means you doubled the cost per GB. When you consider the cost of SSD vs FusionIO...you can't just look at your quoted price per GB, you have to consider configuration, which makes FusionIO more expensive at first glance. Another option is to use 2 servers and license a dataguard standby...but that increases your Oracle licensing and again, doubles the cost per GB of FusionIO storage. If you don't already have the infrastructure in place with extra capacity, you also have to consider the "other" costs on the SSD side...HBA's and SAN costs.

Usually...that's the traditional kind of price comparison that's done with storage. For database storage, what I highly recommend is that you base your requirements on IOPS per $ if its an OLTP database and throughput per $ if its a DSS database, not GB per $. When you're talking about a network share...$/GB might be the only consideration...but a database needs IOPS to perform well. You COULD run your enterprise database on cheap, large SATA disks you bought from Fry's or Microcenter, but when people see how it performs you'll be fired and replaced by somebody who isn't afraid to have the difficult conversations that justify SSD's for databases. :) You could buy a room full of these drives and maybe achieve the IOPS or throughput required for your database, but at that point, FusionIO or a SAN with SSD's would be cheaper...especially when you consider administration costs of maintaining and cooling that room.

I'm testing with the 320GB FusionIO Version 1 card. Here are the specs:

ioDrive Capacity	320GB
Nand Type	Multi Level Cell (MLC)
Read Bandwidth (64kB)	770 MB/s
Write Bandwidth (64kB)	790 MB/s
Read IOPS (512 Byte)	140,000
Write IOPS (512 Byte)	135,000
Mixed IOPS (75/25/r/w)	119,000
Access Latency (512 Byte)	26 μs

In contrast, here's the specs for the Version 2 of this card:

ioDrive2 Capacity	400GB	365GB
Nand Type	SLC	MLC
Read Bandwidth (1 MB)	1.4 GB/s	710 MB/s
Write Bandwidth (1 MB)	1.3 GB/s	560 MB/s
Read IOPS (512 B)	351,000	84,000
Write IOPS (512 B)	511,000	502,000
Read Access Latency	47µs	68µs
Write Access Latency	15µs	15µs

You might notice the difference in read throughput and IOPS on the V2 card dropped from the V1. This is because they changed the design of the card to be more dense moving from 30nm to ~22nm. MLC is noisy, so to ensure error-free data, they've implemented ECC, which serializes reads. Writes are still parallel. Latency has been lowered with the new design from 29 μs on the V1 to 15µs on v2 for writes...but read latency is much slower (but still insanely fast) at 68µs.

FIO expects the read speeds to become closer to their write speeds by driver updates in the near future. This is really cutting edge tech at this chip nm density, its only now starting to be available for non-Facebook customers of FusionIO. Facebook is a huge customer, and they get the good stuff first...

Anyway, since the V2 card isn't performing as well as it eventually will, and since the V1 card is faster, I opted for testing the V1 card.

I look forward to these tests...they should be interesting. I'll let you know what I find out.

Flash with Databases - Part 1
Flash with Databases - Part 2
Flash with Databases - Part 3
Flash with Databases Inexpensively

Index Compression Part 3 (Compressing the indexes online)

2011-10-19T14:09:00.000-07:00

My first post on index compression talked about what it is (and isn't). My 2nd post talked about alter index...validate structure, and offered a very fast way to get the number of columns to compress and estimated compression information into a persistent table. So now we know what we want to do, and which indexes we want to do it to...now what?

If you ran the procedure from Part 2, you now have a populated table with index owner, index name, the number of columns in the index you should compress (if any) and the expected compression ratio you'll achieve. The options before you are to:

1. Rebuild the indexes that will benefit from compression, compressing by the number of columns that were indicated from the table populated in Part 2...doing this offline, causing DML locking on the table you're building the index on.

2. Same as above, only doing it online, where you don't block dml.

3. Forget it...leave them the way they are because you don't think the space saved is worth the trouble of compressing them. This is a completely valid result of this analysis. If you have abundant storage or if the compression yields were low, don't bother.

Assuming you don't go with 3, and you're in a live database (not your testing database where you're nearly the only user) I would go with #2 above. Online index rebuilds are similar in some ways to online table redefs. What happens is that, instead of referencing your table for data to build the index, Oracle references the original index as the source, as of an SCN. No DDL operations are allowed. If there's DML on the table, that DML is entered into a journal table. When the first pass is complete, the journal table is accessed and the changes made during the online create are applied to the new index. If changes are made during this pass, they're again added to the journal table. Its possible that high DML activity will cause many passes, which is why Oracle recommends doing this during periods of low DML activity. Eventually, you'll have a very short pass where all the changes are made to the new index. At this point, the original index, the new index and the table are all in sync with each other. The next thing to happen is a nearly instant metadata update that makes the new index the active index, and the metadata for the original index is gone (for all practical purposes). This means DML is allowed on the table with the online index rebuild for nearly the entire time...there's just one extremely brief lock during the metadata switch-over. In most environments, this can be done without an outage...only extremely sensitive applications would have a problem with the lock during the metadata update. Again...test it, so when your management needs to be convinced, you'll have data to prove the outage time is insignificant.

Using the table from part 2 of this series, you can use something like this sql statement to dynamically generate your index rebuilds. It'll build them parallel 24, and then set them back to whatever parallel setting they had before. I'm filtering out indexes where compression will save less than 25MB...feel free to tweak it to fit your needs.

select 'alter index '||owner||'.'||index_name||' rebuild parallel 24 compress '||cmpr_count
||' online; -- will save '||trunc(saved)||'MB'||chr(13)||'alter index '||owner||'.'||index_name||' parallel '||
(select degree from dba_indexes d2 where d2.owner=d1.owner and d2.index_name=d1.index_name)||';'
   from
(
select owner, index_name, cmpr_count,
(select /*+ PARALLEL 24 */ sum(bytes/1024/1024)*(ici.cmpr_pctsave/100)
    from dba_segments where owner=ici.owner and segment_name=ici.index_name) saved
from jablac.idx_comp_info ici where cmpr_pctsave!=0
) d1 where saved>=25
order by owner, index_name;

I just met with some reps from FusionIO...its clear that as ultra-fast storage options come up, little space-saving techniques like this are even more valuable. NAND flash technology is becoming cheaper all the time...but it still comes at a premium, and putting indexes on FusionIO is a HUGE performance benefit to your database. The smaller you can make your indexes, the more you can store on flash, and the faster the FusionIO card performs. (Yes, as the SSD/Flash card fills, it slows.) That's an idea for a whole new series, for another day.

I hope all this information helps you when you find that storage is at a premium on your database. Index compression doesn't have the "Wow!" effect that HCC compression has, but with very little, if any, downside with modern processors, the technology is worth at least a consideration.

Just an addendum:

The stored proc doesn't work for local indexes...its a different syntax to analyze them, and the index partition info is stored in a different view (dba_ind_partitions). This is unfortunate because that's likely where your biggest indexes (and thus, biggest compression gains) will come from. Until I get around to adding that functionality, you can use this sql to generate a script to find the compression gains from those indexes:

select 'analyze index '||index_owner||'.'||index_name||' partition ('||partition_name||') validate structure;'||CHR(13)||
'delete from idx_comp_info where owner='''||index_owner||''' and index_name='''||index_name||''';'||chr(13)||
'insert into idx_comp_info select '''||index_owner||''','''||index_name||'.'||partition_name||''', opt_cmpr_count, opt_cmpr_pctsave, -1 from index_stats;'||chr(13)||
'commit;'
from dba_ind_partitions di where subpartition_count=0
and exists
(select 'X' from IDX_COMP_INFO_LOG2 cil
     where di.index_owner=cil.owner
       and di.index_name=cil.name)
union all
select 'analyze index '||index_owner||'.'||index_name||' subpartition ('||subpartition_name||') validate structure;'||CHR(13)||
'delete from idx_comp_info where owner='''||index_owner||''' and index_name='''||index_name||''';'||chr(13)||
'delete from idx_comp_info where owner='''||index_owner||''' and index_name='''||index_name||'.'||partition_name||''';'||chr(13)||
'insert into idx_comp_info select '''||index_owner||''','''||index_name||'.'||partition_name||''', opt_cmpr_count, opt_cmpr_pctsave, -1 from index_stats;'||chr(13)||
'commit;'
from dba_ind_subpartitions di
where exists
(select 'X' from IDX_COMP_INFO_LOG2 cil
     where di.index_owner=cil.owner
       and di.index_name=cil.name)
     order by 1,

Compressed Indexes (Part 1)
Compressed Indexes (Part 2)
Compressed Indexes (Part 3)

Index Compression Part 2 (Fast compression column count gathering)

2011-10-19T11:40:00.000-07:00

In my previous post, I talked about what Oracle index compression is, and what it isn't. It IS de-duplication, it isn't binary compression. It is something that may slightly increase CPU, depending on your workload, it isn't something that's likely to hurt your performance...it might even help it. It definitely saves space, and its worth considering.

Obviously, before you do anything like modifying indexes, that can affect performance, you need to test it in an environment where it won't matter if something negative or unexpected happens. The analyze index command will tell you the estimated compression ratio and a suggestion on the number of columns to compress. Unless you're on a very old DB version, don't use the analyze index...compute|estimate statistics...its deprecated, and isn't nearly as good as the more modern options available today. What we're doing is analyze index...validate structure command, which isn't deprecated.

So...you have many databases with thousands of indexes. How do you figure out which ones are candidates for compression? Earlier this week I spoke with a consultant who suggested my client take all the indexes that are over 100MB, run an analyze statement on them in non-prod, and any that have over 25% compression ratio...consider those your candidates. I know from looking at their storage requirements that many of their indexes are hundreds of GB, totalling 10's of TB. If they gave even a very slight positive compression ratio, the space savings could be substantial...so what I did was run the analyze on all the indexes. There are scripts all over "the internets" (that never gets old...thanks Mr Bush) to do this task one index at a time. It takes a really long time...we're talking about thousands of indexes and TB's of index storage, multiplied by many databases. From what I see in the syntax documentation, the parallel clause for the analyze statement is only available for the analyze table statement, not the analyze index/VS statement...I could be wrong about that, but I couldn't get it to work..

This was going to take an eternity to run "analyze index...validate structure" for all indexes, serialized. In addition, I needed to do this on many databases. After the AI...VS statement, the results are stored in a session-specific table, and they're erased when you analyze the next index...there's no persistent place where the results are stored after the analyze. Sooooo...I wrote a stored proc to do it...each analyze command is serial, but I can run multiple statements at a time (1 per session)...I chose 20. The results are then stored persistently in a table.

Something to be aware of...this procedure will do multiple "analyze index...validate storage" commands that will each cause a global, sharing DML lock, which will prevent dml on the table that has the index you're analyzing. Keep that in mind when you're doing this...don't do this in prod, unless you have an application outage. What I do is run my stored proc in a copy of the database (hopefully you have one for performance testing you can use, ideally a physical duplicate of the prod database. If you don't have one do a restore/recover or rman duplicate to a new instance.) For the analyze, it doesn't have to be performant storage...if you have an NFS server with storage, its easy to use that with dNFS for most databases. The thing is...you need to test after you compress the indexes. Really, you always need to test anything you do...so a physical copy with similar hardware to prod is preferred.

What you'll need:

(as sys) grant execute on sys.dbms_lock to [username];

grant dba to [username];

grant analyze any to [username];

You may need to modify the exclude list in Query1 to avoid analyzing indexes in your database that are out of project scope for whatever reason.

You may want to modify what v_Parallel is initialized to (currently 20) to match your system's CPU_Count (or something less)

What it does:

This stored procedure will create a list of all indexes you want to analyze and put the list in IDX_COMP_INFO. It will then recursively call itself, loading itself into the job_queue, v_Parallel (default 20) number of times, so that multiple indexes can be analyzed at once. As one analyze finishes it puts the results (how many columns you should compress, and the expected compression ratio) in IDX_COMPE_INFO. It'll load the next index and repeat until all the indexes have been analyzed. If there's an error while analyzing an index (or for any other reason) it'll put the error in IDX_COMP_INFO_LOG. You should expect errors like "no data found" in that table for indexes that are built on temp tables or empty tables...also indexes (or indexes with partitions) that are in an unusable state will give errors and put an entry into the log table.

IDX_COMP_INFO's last column will be -1 after this is executed for every row. The idea is that you can populate that column later with the new size or amount of space savings you'll have...its just nice to keep that info in the same table, IMHO. If there's an error for some reason while analyzing an index, IDX_COMP_INFO's CMPR_COUNT will be -1. This makes it easy to write sql statements later against this table, because you can say "where cmpr_count > 0" and only look at the compression candidates.

Lastly, there's dynamic sql being used without bind variables, so its vulnerable to sql injection to an evil person that has an execute grant on it...so after you're finished running this procedure on your cloned database, to be ultra safe, I would drop the procedure.

My next post will be about what to do with the information gained from the procedure below....

CREATE TABLE IDX_COMP_INFO
(
OWNER         VARCHAR2(30 BYTE)               NOT NULL,
INDEX_NAME    VARCHAR2(30 BYTE)          NOT NULL,
CMPR_COUNT    NUMBER,
CMPR_PCTSAVE NUMBER,
MB_SAVED      NUMBER
);

CREATE UNIQUE INDEX IDX_COMP_INFO_UNQ ON IDX_COMP_INFO
(OWNER, INDEX_NAME)
COMPRESS 1;

CREATE TABLE IDX_COMP_INFO_LOG
(
OWNER VARCHAR2(80 BYTE),
NAME   VARCHAR2(255 BYTE),
ERRM   VARCHAR2(2000 BYTE),
TRACK NUMBER
);

CREATE OR REPLACE PROCEDURE get_idx_comp_info
(
Ind_Owner_in varchar2 default 'X',
Ind_Name_in   varchar2 default 'X'
)
authid current_user
AS

type r_script is record (
    owner        varchar2(30),
    idx_name     varchar2(300));

type t_Idx is table of r_script index by binary_integer;
c_Idx t_Idx;

v_Query1       varchar2(32000);
v_Cmpr_Count   number;
v_Cmpr_Pctsave number;
v_Job          number;
v_Count1       number;
v_Count2       number;
v_Owner        varchar2(30);
v_Index_Name   varchar2(30);
v_Errm         varchar2(255);
v_Errtrack     number := 0;
v_MB           number;
v_MB_Saved     number;

-- *************************************
-- The line below may be adjusted for your platform. I would make it something less than CPU_Count on your db.
-- *************************************

v_Parallel number := 20;

BEGIN

/*
Author: Andy Black
For more info see: http://otipstricks.blogspot.com/2011/10/index-compression-part-2-how-to-do-it.html

USAGE: create the tables (source available from the link above) and grants needed, then just exec get_idx_comp_info;

Req: You'll need the init.ora parameter job_queue_processes!=0, and it needs to be at least the value of v_Parameter above.

This procedure will submit to the job queue a seperate job for each index, but only v_Parameter (default 20) at a time. (That way, if job_queue_processes is set to 100, you won't have 100 index rebuilds happening simultaneously.)

This was created to overcome the limitation of not being able to parallelize analyze index...validate structure commands, and no persistent way to trace index compression information found in index_stats (which is cleared out at the start of the next analyze in that session.) This is many X faster than a serialized script to do the same task.
*/

(as sys) grant execute on sys.dbms_lock to [username];
grant dba to [username];
grant analyze any to [username];
*/

if Ind_Owner_in='X' then
    execute immediate ('truncate table idx_comp_info_log');
    delete from idx_comp_info where cmpr_count=-1;

-- May need to modify the exclude list below
    v_Query1 := 'select owner, index_name from dba_indexes
                   where index_type=''NORMAL''
                     and owner not in (''SYS'',''SYSTEM'',''OUTLN'',''DBSNMP'',''WMSYS'',''XDB'',''CTXSYS'')
                     and compression=''DISABLED''
                     and temporary!=''Y''';

    execute immediate v_Query1 bulk collect into c_Idx;

    for i in 1..c_Idx.last loop
      begin
        insert into idx_comp_info values (c_Idx(i).owner, c_Idx(i).idx_name, v_Cmpr_Count, v_Cmpr_Pctsave,0);
      exception
        when DUP_VAL_ON_INDEX then
          -- This will allow you to skip the indexes that you've already done, if you want to re-run the procedure
          null;
      end;
      if mod(i,1000)=0 then
        commit;
      end if;
    end loop;
    commit;

    select count(0)
      into v_Count2
      from idx_comp_info
      where cmpr_count is null;

    while v_Count2!=0 loop

      execute immediate 'select count(0) from dba_jobs where what like ''%get_idx_comp%''' into v_Count1;

      if v_Count1<= v_Parallel then

        select owner, index_name
          into v_Owner, v_Index_Name
          from idx_comp_info
          where cmpr_count is null
            and rownum=1
          for update;

        update idx_comp_info
          set cmpr_count=-1
          where owner=v_Owner
            and index_name=v_Index_Name;

        commit;
        dbms_job.submit( job => v_Job,
               what => 'begin get_idx_comp_info('''||v_Owner||''','''||v_Index_Name||'''); end;',
               next_date =>sysdate-1);
        commit;

      end if;

      dbms_lock.sleep(.1);

      select count(0) into v_Count2
        from idx_comp_info
        where cmpr_count is null;

    end loop;

else
    v_Errtrack := 1;
    execute immediate ('analyze index '||Ind_Owner_in||'.'||Ind_Name_in||' validate structure');
    v_Errtrack := 2;
    select opt_cmpr_count, opt_cmpr_pctsave into v_Cmpr_Count, v_Cmpr_PctSave from index_stats;
    v_Errtrack := 3;
    update idx_comp_info set cmpr_count=v_Cmpr_Count, cmpr_pctsave=v_Cmpr_PctSave, mb_saved=-1 where owner=Ind_Owner_in and index_name=Ind_Name_in;
    v_Errtrack := 4;
    commit;
end if;

exception
when others then
    v_Errm := sqlerrm;
    insert into idx_comp_info_log values (Ind_Owner_in, Ind_Name_in, v_Errm, v_Errtrack);
END;
/

Compressed Indexes (Part 1)
Compressed Indexes (Part 2)
Compressed Indexes (Part 3)

Index Compression Part 1 (dispelling myths)

2011-10-18T15:25:00.000-07:00

I have a client that's having difficulty with a rapidly growing database that has a fixed amount of storage available in Exadata. Oracle has announced the ability to buy storage cells separately...which is great, but they aren't free, so its a matter of good stewardship to be as efficient with space consumption as possible. In an effort to reduce space consumption on this Exadata platform, I'm looking at different options that may help us. We're doing HCC/C4OLTP everywhere possible, but there's still a little more that we can do with index compression. When complete, it'll make your storage utilization shrink more than bernie madoff's bank account and it could make your database run faster than a guy married to kim kardashian.

Index compression has been around since 9i...years and years, but there are still a lot of misconceptions around it. I met some consultants from Oracle that are very knowledgeable about RAC, Exadata...you name it...but for whatever reason they were confused about index compression. Each of them reinforced the others incorrect ideas about it until they were all in a fortified group think on the topic. They thought index compression was like OLTP/HCC compression, where compression logic is applied. They argued that indexes are for quick lookups...why would you want the delay of decompression applied to them and all the additional CPU usage? First of all, index compression IS NOT like binary compression...like bzip2 or zlib compression. Its misleading to call it "index compression"...it should be called "index de-duplication." There is almost no CPU overhead in index decompression, because there is no decompression...its not compressed...its de-duplicated.

The other misconception I heard from those guys is that, a unique index wouldn't benefit from index compression because every row is unique, which means rows aren't duplicated, so there's no way to reduce the storage...you'll use more CPU for no gain.

If you have a single column (ie: a sequence number) as your PK, that's true. Often, unique indexes and primary keys are concatenated...so you have multiple columns that together, make something unique. For example, for a cars table, you have a PK for make, model, year, trim. 3 of the 4 columns can be compressed.

MAKE         TRIM           MODEL    YEAR
FORD          LIMITED   EXPLORER    1990
FORD          LIMITED   EXPLORER    2005
FORD          LIMITED     TEMPO          1991
FORD          SPORT         FUSION         2010

You can see all the first column is repeated, much of the 2nd column could be de-duplicated and much of the 3rd column could be de-duplicated. Together, all 4 columns are unique, but it could still benefit from index compression.

This has been gone over "on the internets", so I won't rehash (very much). Richard Foote has a nice post about it HERE.

(again, from Richard Foote's post)

Let’s say we currently have a nocompress Non-Unique index on a NAME column with the following entries:

0: Lady Gaga
: ROWID
1: Lady Gaga
: ROWID
2: Lady Gaga
: ROWID
3: Lady Gaga
: ROWID
4: Lady Gaga
: ROWID
5: David Jones
: ROWID
6: David Jones
: ROWID
7: David Jones
: ROWID
After the index is compressed, the leaf block entries would look logically something like this:
Prefix
0: Lady Gaga
1: David Jones
—

0:0
: ROWID
1: 0
: ROWID
2: 0
: ROWID
3: 0
: ROWID
4: 0
: ROWID
5: 1
: ROWID
6: 1
: ROWID
7: 1
: ROWID
…
Importantly, each distinct combination of compressed column values is now stored just the once within an individual index leaf block. In the example above, we previously stored “David Bowie” 5 times. In the compressed index leaf block, we now only store “David Bowie” once, with the prefix value now being referenced by each index entry instead.
The net result being we can now (hopefully) store many more index entries per leaf block meaning we don’t need as many leaf blocks in our index.

...and so, we need less blocks to store the index. Since there's nothing to decompress on read, there's not a huge amount of additional CPU usage to use the index to lookup the rowid.

For reads, there may be an extremely small amount of additional CPU to use the prefix, but from my testing that's more than made up for in a reduction in IO. For writes/updates, there may be more CPU used...index compression isn't always the thing to do, it depends on your access patterns. You must test. From my experience on Exadata, the CPU loads on databases are less than what they are on non-Exadata platforms due to cell offloading. This means you may have more free CPU than you anticipated. Since storage is expensive and you get a reduction in IO from compressed indexes (they're smaller...a full index scan and an index range scan will likely have fewer blocks to read in, because there are fewer blocks in the index, so less IO) the trade-off between a slight CPU increase and less IO is a positive one, or at least a wash.

As you can see from this OLTP workload simulating Swingbench test, there's almost no difference in performance between compressed and uncompressed indexes on the overall system...possibly a performance increase, but so small its in the noise...definitely not noticeable on this workload. These tests were run on a database in VMWare using dNFS to NetApp, with very low memory settings (~256MB db_cache) to emphasize physical IO performance.

Before Compressing the indexes

After compressing indexes

Soo...how do you know which indexes to compress, and how many columns are redundant? I'll save that for the next post.

Compressed Indexes (Part 1)
Compressed Indexes (Part 2)
Compressed Indexes (Part 3)

Bloggers meet and great at Julian's

2011-10-05T21:03:00.001-07:00

Oracleworld has been a blast...but one of the best times has been hanging with the Pythian guys at Jillian's tonight. In this pic, I'm laughing so hard I can't breath, next to Alex Gorbachev, Marc Fielding and Karen Angerer. The guy in front asked that I not reveal his name...he's in Pythian's private security business in anti-terrorism, specializing in micro-brewery anti-terrorism. He's very good at his job...no micro brews have been hit on his vigilant watch.

It was an honor spending time with these guys. The highlight was when Alex toasted me...saying, "Respect."

Oracle public cloud

2011-10-05T16:36:00.001-07:00

Wow...Larry sure threw Salesforce.com...which bases their whole company on Oracle under the bus! I wonder how Tom Kyte feels about the slams on Apex?

Oracle Social Network seems like an oxymoron...most techies I know aren't very social. This isn't Exafacebook, its a BI interface that looks very interesting and different. This session is a pretty hard slap at SAP. While showing the SAP UI, he said "It's like looking at the fins of an old Cadillac."

In contrast, the Oracle Social Network looks like a streamlined way for businesses to communicate by issue, rather than by group. The system recommends contacts for your project rather than the other way around.

New product announcements!

2011-10-03T09:16:00.001-07:00

1. Oracle Big Data Appliance
Hadoop map reduce
Oracle data loader for hadoop
Exalytics (see pic), Oracle's new nosql platform.

2. Oem release 12...I can see you can set up IORM in the new OEM UI...this is the new, more intuitive gui. Human factors have always been a problem for new users of OEM.

3. Exalogic isn't new, but integration with OVM 3.0 is new. This is a big step forward to the "Big Honkin' Cloud" Larry talked about last year. Oracle had a product called ASR that monitors Exadata environments and submits SR's to MOS today...it looks like that function has been added to OEM 12 now. If a hardware problem is detected, an SR is sent to Oracle, and a person is dispatched to replace the part immediately, automaticly.

4. Exalytics BI & EPM appliance. 20x faster relational OLAP performance, 80X faster multidimentional performance. Times Ten is used in the Exalytics server...think of it as an extension to your db cache, with the ability to offload plsql under some circumstances.

The demo is using an an analysis of all the cars sold in the world over the last 10 years...a huge amount of data, in memory.

X-2 4

2011-10-03T09:13:00.001-07:00

At Openworld, I'll likely have many short posts like this as I see interesting things, but don't have the time to go into depth on them.

Here's a pic of the front of the new X2-4 Exadata machine:

Openworld 10/3 Oracle Keynote

2011-10-03T09:03:00.001-07:00

I think its interesting that Oracle has allowed so much time to their partner/competitor EMC and VMWare in the Monday morning keynote. The value of EMC's big data strategies, Greenplum, VMWare and EMC Storage w/vfast are all being shown to Oracle's best customers.

They're talking about "Cloud meets big data"...and showing they can get 1 million iops with vfast storage in VMWare..like Exadata, while making available all the virtualization features we've become used to, like snap/clone.

Pared with Greenplum and hadoop, peyabytes are analyzed in seconds. It'll be interesting to hear Exahadoop's and Exalytics response. :)

Here's a pic of an Exalytics server:

Free at Last! (to post about EHCC on ZFS!)

2011-09-30T13:21:00.000-07:00

About a month ago, I made a post re:HCC working on ZFS...which I later had to remove, because it hadn't officially been announced yet, and the confidentiality was "implied." Finally, it has been officially announced to the world:

http://www.oracle.com/us/corporate/press/508020

When I originally posted about this, I was told that it was due to new functionality being put into the firmware of the Sun 7420 that would allow it...that HCC compression would be offloaded to the CPU of the head of the 7420. Kevin Closson correctly pointed out in a comment on my post that it wasn't a new feature in the 7420...there isn't HCC "offloading" like in a storage cell...its just that the code that prevents this from working is being removed if you're using storage from Oracle (Sun/Pillar Axiom). Kevin went on to say the feature of HCC on non-storage cell storage made it through beta testing...there was no technical reason to prevent the functionality.

He was clearly correct, because the official announcement states, "Hybrid Columnar Compression...is enabled in an update to Oracle Database 11g Release 2." ...no mention of a firmware update. That'll be the last time I listen to a non-technical source about a new technical feature. I'm just glad I didn't try to argue with Kevin. :)

About a year ago I was in an RFP with different vendors to provide a database solution to a client. The client went with Exadata...the primary reason was that HCC would reduce the amount of storage needed. Without HCC, much more storage would have to be purchased, and that cost more than offset the additional cost of licensing incurred by the Exadata platform. It was a huge selling point that the other storage vendors couldn't match. I hear rumors that may change in the future, however.... ;)

Anyway, I can see why Oracle would make that an Oracle-storage-only feature, I witnessed it making them a multi-million dollar deal. In a perfect world, Oracle would allow the feature to work on all storage platforms, and if people chose to go with the Pillar Axiom or Sun storage, it would be on the ample merits of their storage platforms alone. The customer would be allowed to choose what they believe to be the best storage solution to match their database, all other things (and compression features) being equal. So...making this an Oracl-only feature is good for Oracle, but possibly bad for the customer.

I wonder how different this "new" feature is than what we first saw in patch 8896202 (for 11.2.0.1 on OEL/RH). This is the patch to enable the compression advisor to estimate Exadata HCC compression ratios. It showed that HCC worked on any storage without Exadata. I first used that patch when it came out...about a year and a half ago, on a linux vm with vmdk storage. The thing is, when you do a trace...its not just an estimate...it literally takes your data and compresses it with HCC, and gives you the results. Soooo...I guess I'm just reinforcing the point that this isn't a new technical breakthrough, HCC on non-Exadata is a feature that's no longer prevented (as long as you buy the storage from Oracle).

It does move in the right direction for the customer though...7420 storage is much cheaper than a storage cell. I look forward to trying it out. Maybe I'll post some performance numbers of HCC on the 7420 vs a storage cell. Hmmmmm........

Openworld 2011 (How to fill out your sessions)

2011-09-30T08:51:00.000-07:00

I always put off signing up for my sessions...if you're the same way, and you're new to Openworld, maybe this will help you.

Sessions are often disappointing because...from the description you expect a "deep dive" into the technology, and they often end up being a marketing presentation, not at all technical. Last year I was at a session whose subject included the phrase "deep dive"...and it was still very light. I hate that...because there are always options for the session, and you don't know if you made the wrong choice until at least a few minutes in. At that point...it seems rude to get up and leave and try to find a better session...but do it anyway...this is your valuable time, don't waste it.

Another common pitfall to sessions that, from their description look like they'll be interesting, is when a non-technical manager gets up and speaks to the project, without knowing the technical details. These are usually a waste of time, but not always, because usually his techy underlings are lurking in the audience, and at the end of the session, during question/answer, he'll defer all the questions to the techies. At that point you start getting valuable information.

So, my strategy this year is to primarily pick the sessions based on the presenters. Any of these presentations will be excellent:CLICK HERE also HERE. After that, I populate the schedule with the database path, and then I go back and use search terms to find things like: "hadoop", "storage", "Exadata", "Backup", "Database Appliance", "RAC" etc. Then go back and prioritize the ones that have time conflicts.

When you try to sign up for a session that's at the same time as a different session and you get a time conflict, it asks you if you want to move the "other" session to "your interests"...say yes...because that way if you're in a session that tricked you into thinking it was going to be interesting...and it turns out to either be a high level marketing session or a manager talking about what his techy team accomplished (I hate those) then while you're sitting there you can pull out your Droid/IPhone and use the Openworld 2011 app to see what your alternate session that gave you a time conflict was. If it isn't filled up, you might be able to leave and go there...hopefully it'll be better.

I wish there was some kind of a rating scale for the sessions to give you guidance on how technical they are...but even if they had it, it would be subjective, and you'd still have the same problem. I think if you follow the guidelines above you'll be in reasonably good shape.

Rumors of Openworld 2011

2011-09-30T08:28:00.000-07:00

Its that time of year when everybody starts guessing re:What Larry will have to say for us that's new and exciting from Openworld. Here are some guesses:

1. 11.2.0.3 is already out for Linux from metalink/MOS...following the new pattern of 11.2.0.2 where its a complete download rather than a patchset. So...with that out of the way...I've been seeing patches "Backported from 12.1" for months now on MOS. Its a good bet Larry is going to drop RDBMS 12 on us.

2. We've all heard by now about Oracle's new database appliance. Its a stepping stone for smaller organizations to Exadata. A managed database/hardware solution with good performance. I plan to check it out at some point later this year...I'll let you know how that goes. Today I got an email from Oracle that mentions "Oracle Database Appliance 12c Private Database Cloud." So...this lends credence to #1 above, and...interestingly it'll be called...not 12i, not 12g...but 12c.

3. Without giving details this time (I was asked to remove the post talking about a change to HCC as it relates to storage), let me just say...there will be a big, positive change to HCC as it relates to storage.

4. This one is huge: Oracle's Hadoop appliance! I heard about this a few months ago, but I can finally talk about it. There have been tweets and blogs mentioning "Exa-Hadoop", and here's something official that confirms it:<>. I think this is an interesting play for Oracle...Hadoop's profitability in an appliance is clear...they make money on the hardware and on support. Since the product is Opensource, they'll help the entire Hadoop community with their development (Like OVM helps Xen and OEL and OCFS2 helps linux.) There's huge interest in the industry around Hadoop.

5. Exalogic v2! I've heard from a somewhat unreliable source (you know who you are) that Exalogic will now have better support for Cache Fusion...this will be a huge performance boost.

6. OEM 12 (or maybe 12c?)! I don't know if this will be announced, but the timing seems really good. See my previous post re:my friend who was encouraged by Oracle to remove his post re:the coming features of OEM 12c. :) There's some pretty cool stuff on the way...huge changes. I saw a mock up of the new UI about a year ago...its completely different and vastly improved.

I just read a funny post <> on this topic...Oracle really should come out with a product called "SexiaBIHadoop."

There are always surprises...can't wait to be there. :)

Page Missing?

2011-08-19T17:20:00.000-07:00

A close friend of mine recently blogged about an upcoming feature of an Oracle product...let's call it OEM. So, in his zeal to discuss the latest, greatest and the new technology in OEM, when a product manager sent him an email and asked about how he knew about the new features, my friend removed his post. Was that a wise move or did it display a lack of courage? Who's to say...maybe both? The real downside is that there was an interesting conversation between him and an Oak Table member I was enjoying. The lessons we should learn from this are, "Secret information isn't always declared so." The other lesson is..."He who has the most lawyers wins arguments before they begin."

Equi-sized Partition Cuts

2011-08-15T12:14:00.000-07:00

There are a lot of resources to show you how to make a partitioned table...syntax search results are plentiful...but its unusual to find a resource that tells you about the process to partition a table.

Some rows are wider than others, and a lot of times your partition key doesn't have gaps...so this may not apply to you. When partitioning my approach has been to:

1. Identify the access patterns
You can do this by talking to the application development teams and selecting from hist_sql_plan (see below). That will give you a good idea about some of the sql that's hitting the table in question. Is it direct-io reads or conventional dml? Are they simple queries, where response time is likely important, or are they more complex, taking several minutes to complete? Look at the operation column.

select * from dba_hist_sql_plan where sql_id in (select sql_id from DBA_HIST_SQL_PLAN where object_name='TABLE_NAME') order by sql_id, id;

2. From #1, what columns do you often see in the where clauses? This/these columns may be good partition key column candidates.

3. From #1, what's the best compression type you can use without negatively affecting application performance? (long runtime queries could likely have more aggressive compression on their tables, short queries will have to be C4OLTP or no compression)

4. If possible, use interval partitioning instead of range partitioning. There are a lot of things that will keep you from using interval partitioning (like domain indexes), but it'll save you time in management if you can use it.

5. There's a balance between adding lots of partitioning for performance reasons and having too many partitions to be easily managed. As a rule of thumb, I shoot for ~20, depending on table size, but never let a partition be smaller than 2% of the db_cache size (see earlier post re:smallness logic). This is hard with ASMM/AMM, since the db_cache size is dynamic...but even if you use ASMM and AMM you should set minimum sizes in the memory pools. Its a good practice to make the partitions as equally sized as possible...if that means equal number of rows or equally size segments, that's for you to decide...it depends on your situation. Also consider the partitioned tables commonly joined with this table...if your queries pull back many rows, partition-wise joins will make you want to use the same partitioning column if possible.

We're partitioning many, many tables and we need a process to do this as quickly as possible. Some of the tables are multi-terrabye, and it was taking me a really long time to come up with the values for the ranges. So, I changed my method and just created a query to do it. There might be a better way...but this is performing very well, even on multi-billion row tables this returns in around a minute. If you have a better method, I'd love to hear about it.

The idea here is that there are repeated values and/or gaps in the sequence column you want to make your partitioning key...so you can't just take the sequence number. The first partition has to be the row that's 5% in...regardless of what the value of the partitioned key is. This query will give you the value of the partitoning column key that's about 1/20th of the rows in the table...the 2nd will give you the one that's around 2/20th's...etc. Change the table_name to be your table, and seq_id to be your partition key.

select part, max(seq_id), max(rownumber) from (
select trunc(rownumber,2) part, tm2.* from (
select /*+ PARALLEL 96 */ seq_id, CUME_DIST() OVER (PARTITION BY 'X' ORDER BY seq_id) AS RowNumber
from table_name tn
) tm2
where trunc(rownumber,2) in (0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45,0.5,
0.55,0.6,0.65,0.7,0.75,0.8,0.85,0.9,0.95,1))
group by part
order by 1;

...when its done, it'll give you 3 columns. The first one is your "goal" (.6 is the one ~60% into the row count), the second is the partition key value at that point, the 3rd is how close to your goal you came (which depends on the data cardinality...how unique the column values are.) Use the 2nd column as the value for your partitioned table range. IE:

0.05    20      0.06
0.1      36      0.11
0.15    52      0.16
0.2      67      0.21
0.25    82     0.26
0.3      95    0.31
0.35    124    0.36
0.4      151    0.41
0.45    169    0.46
0.5      186    0.51
0.55    200    0.56
0.6      217    0.61
0.65    235    0.66
0.7      253    0.71
0.75    296    0.76
0.8      311    0.81
0.85    347    0.86
0.9      381    0.91
0.95    415    0.96
1         445    1

PARTITION BY RANGE (SEQ_ID)
(
PARTITION P_1 VALUES LESS THAN (20),
PARTITION P_2 VALUES LESS THAN (36),
PARTITION P_3 VALUES LESS THAN (52),
PARTITION P_4 VALUES LESS THAN (67),
PARTITION P_5 VALUES LESS THAN (82),
PARTITION P_6 VALUES LESS THAN (95),
PARTITION P_7 VALUES LESS THAN (124),
PARTITION P_8 VALUES LESS THAN (151),
PARTITION P_9 VALUES LESS THAN (169),
PARTITION P_10 VALUES LESS THAN (186),
PARTITION P_11 VALUES LESS THAN (200),
PARTITION P_12 VALUES LESS THAN (217),
PARTITION P_13 VALUES LESS THAN (235),
PARTITION P_14 VALUES LESS THAN (253),
PARTITION P_15 VALUES LESS THAN (296),
PARTITION P_16 VALUES LESS THAN (311),
PARTITION P_17 VALUES LESS THAN (347),
PARTITION P_18 VALUES LESS THAN (381),
PARTITION P_19 VALUES LESS THAN (415),
PARTITION P_20 VALUES LESS THAN (445),
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
)

I hope this helps you, or at least saves you some time determining your table partition key and the best range of values for that key.

Partitoning while migrating to Exadata-P2 (Exadata is inexpensive)

2011-08-15T09:46:00.000-07:00

Preliminary testing results are very promising. After a few days of activity the accelerated performance due to C4OLTP is working, and the majority of the data is seeing excellent compression (up to 55X+) for HCC c4archive-high.

One of the tables is ~2TB in size and contains a clob column. If you read the documentation, it tells you that HCC doesn't work on cLOBS. What they SHOULD say is it doesn't work on out-of-line clobs. If your clobs are small, they're stored in the table's segment and HCC does work on them. I was able to take this 2048GB table and compress it down to around 76GB. That's 3.7% of its original size, for a compression ratio of about 27X.

This is one of the concepts that's difficult for people considering buying Exadata to grasp...it costs a lot, but due to its compression abilities, it may be the least expensive thing out there for very large databases. Its hard to compare Exadata to a standard 11.2 database system because Hybrid Columnar Compression makes it an apples-oranges comparison.

Consider the cost of high-performance storage on an EMC or Hitachi array. Everybody has their own TCO for high performance storage...but lets say EMC gives you a good deal and it costs $15/GB. The storage savings on this table alone saved almost $30k. Multiply that out by the 28TB in a high performance machine and it saves $11.3 million. A different way to look at it is...in a world where you can compress everything with HCC-QH and get these compression results...the actual capacity of a 28TB high capacity Exadata machine is 756TB!

Now that Oracle is selling storage cells individually, Exadata is truly expandable...there really isn't a capacity limit until you can saturate IB and create a bottleneck...but since that's been optimized (only sending required blocks, columns and sometimes result sets to the RAC nodes), its difficult to image how much storage you can have before that's an issue.

Partitoning while migrating to Exadata (ILM and Compression)-P1

2011-08-15T09:31:00.000-07:00

In our frenzy to move data from around 22 databases into a single database in Exadata, at the same time we're doing some modifications to the larger tables- hundreds of them are 2GB+ in size. As you've read in the previous posts, there's concerns that the growth rate of these tables may exceed the storage capacity before the original 3 yr projections. To postpone this, we're trying to partition these tables for ILM (information lifecycle management) and performance during the migration.

The idea is that usually, the newest data is the most active and the older data is kept for archival purposes. The new data is usually manipulated with DML and the old data is usually used only for queries.

With Exadata's 8 table compression options, we have a lot of ways to shrink the data and make it fit in our storage capacity as long as possible. These all have trade-offs between compression density and CPU overhead. HCC compress for archive high is extremely good at compressing data...but your access is very slow and it uses lots of CPU...compress for OLTP is relatively bad at compression, but its extremely fast. Since the CPU for compression/decompression is often offloaded to the cores on the storage cells...the only real concern is how well does it compress, and how will it affect query and DML performance?

Experimentation and testing is in progress now, but our initial results show compress for OLTP actually improves performance on Exadata with our workload! C4OLTP usually gives between 2X and 4X compression.

My thought is, since DML on an HCC compressed table without direct IO moves that row into a C4OLTP block, and since there's no way to delineate the "old, Read-Only" data from the "new, often-modified" data in the tables, we compress some of the largest multi-TB tables with "HCC compress for query-high". The idea is that we take an initial performance hit, but after a while, internally all the old stable data will be stored with HCC QH, and the new volatile data will be C4OLTP. Its the best of both worlds...the volatile data will be faster than no compression due to C4OLTP and the older data will be extremely densely compressed. This should work because our access patterns show we rarely do bulk insert/update/deletes.

Eventually, (I'm thinking annually) we'll have to move the partitions to re-compress the data to HCC QH again and let the activity sort out the data between the 2 compression methods. As testing continues, I'll let you know how its going.

An Overlooked Database Performance Pitfall - Part 2 (smallness logic)

2011-08-12T11:18:00.000-07:00

To answer the question, "How can I make my large transaction faster in Oracle?" We first have to know when direct path reads are prevented and triggered...when will the help, and when will they hamper performance? Here are some situations that prevent them:

    * If the table uses advanced security features, like fine-grained access control

    * Queue tables don't use direct path reads

    * Tables that have BFILE or opaque or have an object type containing opaque

    * Tables that have encrypted columns

    * A table with LONG or LONG RAW column, where that column isn't the last one in the table

    * I've heard direct path reads are avoided when X% of the table's block are already in the db_cache. This may be due to smallness logic...if the table is 5% the size of your db cache, and 4% of it is already in the db cache, the read would only be 1% of the db cache. I haven't verified that, but that's my theory. This means its possible that one day you get direct path reads, the next day you don't.

    * When "smallness logic" (_small_table_threshold) comes into play (Metalink-ID 787373.1):

"If the number of blocks to be read is lower than or equal to the setting of the parameter Oracle will load the object via the buffer cache as this is more efficient than doing a direct read operation. However, the parameter does not need to be set explicitly as there is also a dependency function calculating the cut over threshold if the parameter is unset. This calculation is roughly 2% of the buffer cache size to be used as the cut over threshold. This means any object (table) smaller than 2% of the buffer cache will be read via the buffer cache and not using direct load."

Table smallness logic has a lot of ramifications. It doesn't just apply to tables, it applies to table partitions too, measuring segment size vs cache size.

Here are 2 situations I've seen where table smallness logic had an impact:

In an effort to simplify the decision path of the query, a DBA I know went on a mission to partition everything he could. He should have listened to Einstein who said, "Make everything as simple as possible, but not simpler." The DBA almost created a new type of index. Partitioning a table with hundreds of partitions on a medium-sized table (to get partition pruning) to the point where some partitions only hold a few rows...and this has been done on an Exadata system! He concluded that it was faster after he was able to do a sample workload much faster than before. Besides the obvious management pain, this makes the partitioned segments so small that you can't trigger direct-path reads, which then prevents the use of storage indexes, and all the other Exadata goodies. For performance reasons, if a query hits that table and filters on the partition key, that's likely not a big deal...its small so a conventional, non-direct path read will be faster anyway. For large queries, over partitioning removes the performance advantage of direct path reads. I think Einstein would say, "He made it too simple." He prevented the use of direct path reads, which meant his reads were no longer blocking his writes...so the end result of his tests were faster because there weren't waits...but he had no idea why it was faster.

I had the opportunity to work with Scott Gossett briefly for a consulting stint in Saint Louis. He's an author, creator of ADDM...also teaches the Exadata classes to Oracle consultants and one the most talented "explainers" I've ever met. There's a disconnect often between "techys" and "management" that's difficult to overcome. Scott has the ability to almost put the management in a trance with his style of explaining things. (Visualize a cobra in front of a guru playing a flute.) Anyway, he sized the nodes of a full OLTP Exadata machine with 16GB of SGA and 32GB of PGA (out of 96GB of ram.) The reason is, even if you have spare RAM, if you create the sga too big, you increase the db_cache size. This means fewer tables will be less than 2% of the cache size, and that means you reduce the percent of activity that'll be direct path. Its counter-intuitive, but less cache means it will run faster (depending on your workload and table layout, of course.)

Should you design your database to use direct path more often than conventional path? It depends on your data and your workload. For a data warehouse, its likely that direct path will almost always be the correct answer...but even in a data warehouse system, there's usually some non-DSS activity. Be aware of direct path IO...it can make things much faster (single, large queries) or it can be a pitfall and make things much slower (multiple transactions attempting to read and modify the same segment).

An Overlooked Database Performance Pitfall (Direct IO) - Part 1

2011-08-12T10:53:00.000-07:00

At this point, if you've heard of Oracle (other than from watching The Matrix or reading "The History of Ancient Greece") then you've likely heard of Exadata. If you've heard of Exadata, whatever your source...reading about it, hearing about it, whatever, there was a mention of how fast it is. If this was a power point slide instead of a post, I'd show a race car, because designing your database is a little like speeding in your car...people just don't always know when they're hitting the accelerator and when they're breaking.

In my previous post, Click Here, I talked about how Exadata's accelerator-direct path reads, allows for the use of many Exadata performance features. Normally, the IO path for Oracle will move the data blocks from storage to shared pool, and then work with it from there. There's a lot of overhead related to shared memory latches. For small amounts of data movement, there's no better way to do it. For large data transfers (large is relative to your segment) there's a better way. Direct-IO allows you to circumvent the overhead of shared buffer caching, and move the data directly from the storage (array/spindle, etc) to the your process memory (PGA).

"But what about dirty blocks? They're in memory, but they haven't been written to disk. If I'm reading directly from disk to my process, wouldn't I miss them?" Yes, you would...that's why at the start of any direct-io read there's a segment checkpoint that flushes dirty blocks to disk, followed by a shared lock to prevent the segment contents from changing during a direct-io read. So, writing (especially direct IO writes...they can't be dirty) will have to wait for the read to complete. Readers blocking writers...where have I heard that before?

This is why there isn't one correct method of disk access in Oracle...it depends on what you're doing. If you're moving large amounts of data direct-io moves it faster but you incur segment checkpoint waits before you can begin. If you're only moving a few blocks they copy slower with conventional IO because of the latch waits of shared memory, but they don't incur the overhead of the segment checkpoint at the beginning. The optimizer looks at how much you're copying and attempts to do the best path, based on the system settings and table statistics.

In previous posts I gave examples on how under certain OLTP conditions, direct path reads can also work like a break in your db-racecar, due to waits on segment checkpoints. As a DBA/Data Architect, how do you design your database to take advantage of the benefits? The optimizer will choose direct path reads when it thinks they're optimal, so you really need to know, what are the pitfalls that prevent them?

I'll list a few in my next post.

Be careful when you stop using a v11 Goldengate extract

2011-07-13T09:03:00.000-07:00

Just wanted to give a heads up on an issue that I caught, that would have taken down our Exadata environment. In the old version of Goldengate, you create extracts and you have .prm files that have their parameter information. When you no longer want to use that extract, you can be lazy and just leave it there not running, or you can delete the prm file and drop the extract.

When you do this...verify that the number of extracts you have running in GGSCI is equal to the number of entries you see in dba_capture. Make sure the old entries are gone. There's an enhancement in GG 11 that will prevent rman from deleting files that haven't been mined. What actually happens is rman checks min_required_capture_change# in v$database, which is populated every 6 hrs based on dba_capture. So, if you stop using an extract, but its entry is still in dba_capture, rman won't delete the archivelogs with the usual command:

backup archvielog all delete input;

Instead you'll get RMAN-08137 and you may see this in your backup log:

RMAN-08120: WARNING: archived log not deleted, not yet applied by standby

From note 1079953.1 you can force it to delete them:

delete noprompt force archivelog all completed before 'sysdate-10/1440';

...but in our situation we're using a standby database. The enhancement to not delete logs that you still need is great...I don't want them deleted before I can have GG mine them and data guard apply them. So...forcing the delete seems...rash to me.

I've been told be a consultant from Oracle (formerly a consultant from GG) that if you connect to the db and then delete the extract, it will also remove the streams/dba_capture entry from the database...if you don't connect to the db when you drop the entry, it'll stay in the db until you manually unregister it. Also, after you create the extract, when you start it, it verifies there's an entry in dba_capture for this process...if there isn't, it creates one. He said there's very little documentation on this...just a mention in the release notes. This change is a pretty big deal, given that if you aren't aware of it, and don't notice the errors in your backup logs, it could stop preprocessing in your database. What would be *much* better, is if...instead of just verifying your extract exists, that it did something like a sync, and made the list in dba_capture match the list in GGSCI. It would also be nice if they changed a "drop extract" command to require a db login to minimize the issue.

Regardless if you choose to go with the MOS work around or unregister the extra streams entries in dba_capture, be aware if you don't do something, your FRA or archivelog destination will eventually fill, which will halt your database.

FAST Domain (and normal unq, non-unq) index rebuilds on Exadata

2011-07-11T15:36:00.000-07:00

With any migration one of the key objectives is to minimize downtime. Even in a MAA migration where you're expecting to move the data and keep it in sync via some form of CDC and finish with a nearly instant "flip-over", you still want the time between the start of the data move and the start of the CDC to be minimized. The longer it takes, the more storage is needed to keep track of the DML changes. So, as we prepare for another round of database migrations to Exadata, one of my objectives has been to minimize the time it takes to get the data in a usable state on the Exadata target.

As part of the migration, to take advantage of the compression and performance features in Exadata, we've also made a lot of table partitioning changes. We have several 2TB+ tables that use context indexes (aka domain indexes). These are a little obscure, so if you don't know what these are...in essence, they bring many advanced search features to large text objects...key among those features is they make clobs searchable. They're completely unlike any other index...when you create a domain index, with a single statement you're creating many new objects...tables and indexes to support that index. Its very messy, so its common to want to put those "supporting" objects in a different schema.

As you can imagine, to rebuild these multi-terrabyte indexes can take an extremely long time, so its avoided whenever possible. Do to the migration's new partitioning schemes, it can't be avoided, and these indexes will need to be rebuilt. The last time it was attempted, one of the indexes took over a week to rebuild. The reason it took so long is the limitation Oracle has to parallelize the process. If you have a domain index with 8 partitions and you issue a rebuild command specifying parallel 80, in v$process for the first few seconds you'll see 80 processes spawn off...then you'll see all but 8 of them die. Oracle will only allow 1 process per partition during the domain index rebuild. This makes the rebuild process on large domain indexes extremely slow.

To get around this issue, Oracle created a built-in package that will submit multiple jobs (one job per partition), and allow each of those to be parallelized. Its called dbms_pclxutil.build_part_index. The first time I used this I was extremely pleased...in my experimentation instance I was able to recreate the index that had previously taken over a week in about 4 hours.

The problem was, in my sandbox I had created the table and the index in my schema, and I was the one issuing the rebuild so everything just worked. During a later test migration when I submitted the same procedure that worked before...it gave me errors. There are multiple limitations with Oracle's procedure...the big one is that the table owner, index owner and index re-builder all have to be the same user.

That makes no sense, especially for domain indexes, which, like I said, commonly are created in a separate schema. Is this a technical limitation? No. I can still do an "alter index rebuild partition" statement on an index not owned by me, and I can build a domain index on a table owned by somebody else. I checked around the internet and found a possible reason << HERE >>. Which is to say...there is no reason.

In the link I referenced, Charles Schultz asked, "For starters, the said index has to be partitioned and unusable. Not a biggie, but why?" I think the answer is...its not that its necessary, but if you aren't partitioned, why not just do a normal alter index rebuild statement? It wouldn't hurt to not have the limitation...I'm just saying a procedure wouldn't really help you. In all the situations I've needed to use this, it was a new index, and so creating it unusable was the fastest way...other than that I don't know what advantage the unusable limitation brings.

*update*
I changed the procedure below to work for both partitioned and non-partitioned indexes. Although it doesn't bring any performance benefits, with the ability to do both partitioned and non-partitioned, now you don't have to verify if an index is partitioned if you use this in a script.
*/update*

Like Charles, I find the limitations pointless and oppressive, so...since this is something that's going to need to happen over and over for multiple domain indexes and multiple migrations...I created my own package that does the same thing without the limitations. It will recursively call itself for each partition, submitting jobs to the job queue to perform the index rebuild.

I also added a few features...the big one is that you specify how many RAC nodes you want it to run on...and when the jobs are submitted, they're submitted round-robin to each node. For one of the indexes, without this package I was only able to go parallel 9 (because I had 9 partitions on the domain index.) With it, I went parallel 10 times 9 partitions...so I had 90 processes across 4 Exadata compute nodes rebuilding this index. This was on a 4 node high capacity Exadata machine and I was able to build an index that had previously taken over a week on an IBM P595 in only 4 hours using all 4 nodes.

There's nothing technically that specifies "domain index" in this...so I guess it could be used for other large indexes too, although I've only tested it in the scenario I described.

*UPDATE*
I've now tested it with many different types of indexes, both domain and local without any issues.
*/UPDATE*

See the usage info in the comment section in the package body under the "ind" procedure.

Disclaimer: I hope this helps you, but as always, the thoughts described here are my opinions...use at your own risk. Like most personal relationships, the good thing about this package is potentially the worst thing about it. It parallelizes and can consume all the resources on your RAC if you haven't already made limitations in your init.ora parameters for parallelism and job_queue_processes.

CREATE OR REPLACE package quickly_rebuild
authid current_user
as
PROCEDURE ind_part
(
Ind_Owner_in     varchar2,
Ind_Name_in      varchar2,
Ind_Part_Name_in varchar2,
Parallelism_in   number
);

PROCEDURE ind
(
Ind_Owner_in        varchar2,
Ind_Name_in         varchar2,
Parallelism_in      number,
Instance_Count_in   number default 1
);
v_Last_Node number; --persistent variable to track the last node that was assigned a job, so you can do many jobs spread across nodes equally.
end;
/
CREATE OR REPLACE package body quickly_rebuild
as
PROCEDURE ind_part
(
ind_owner_in IN varchar2,
ind_name_in IN varchar2,
ind_part_name_in IN varchar2,
parallelism_in IN number
) IS
cursor_name INTEGER;
ret INTEGER;
v_Ind_Owner varchar2(30) := replace(ind_owner_in,';',':');
v_Ind_Name varchar2(30) := replace(ind_name_in,';',':');
v_Ind_Part_Name varchar2(30) := replace(ind_part_name_in,';',':');
v_Parallelism number := parallelism_in;
BEGIN

cursor_name := DBMS_SQL.OPEN_CURSOR;
if ind_part_name_in='NOT_PARTITIONED' then
DBMS_SQL.PARSE(cursor_name, 'alter index '||ind_owner_in||'.'||ind_name_in||' rebuild parallel '||parallelism_in, DBMS_SQL.NATIVE);
else
DBMS_SQL.PARSE(cursor_name, 'alter index '||ind_owner_in||'.'||ind_name_in||' rebuild partition '||ind_part_name_in||' parallel '||parallelism_in, DBMS_SQL.NATIVE);
end if;
ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;

PROCEDURE ind
(
Ind_Owner_in varchar2,
Ind_Name_in varchar2,
Parallelism_in number,
Instance_Count_in number default 1
)
IS
type r_script is record (
line number,
text varchar2(1024));

type r_IP is record (
index_owner varchar2(30),
index_name varchar2(30),
partition_name varchar2(30)
);

type t_IP is table of r_IP index by binary_integer;
c_IP t_IP;
v_Job number;
v_Count number;

Ind_Not_Exist exception;
pragma exception_init(Ind_Not_Exist,-20001);

v_Statement1 varchar2(32767) := 'select count(0) from dba_ind_partitions where index_owner=:a and index_name=:b and status=''UNUSABLE''';
v_Statement2 varchar2(32767) := 'select index_owner, index_name, partition_name from dba_ind_partitions where index_owner=:a and index_name=:b and status=''UNUSABLE''';
v_Statement3 varchar2(32767) := 'select count(0) from dba_indexes where owner=:a and index_name=:b and status=''UNUSABLE''';

begin

/*
Author: Andy Black
For more info see: http://otipstricks.blogspot.com/2011/07/fast-domain-index-rebuild-on-exadata.html

USAGE: Create the index with the UNUSABLE clause and then issue:
exec quickly_rebuild.ind(index_owner, index_name, parallelism_per_partition, instance_count);

Req: The user submitting this work will need alter any index granted directly
You'll need the init.ora parameter job_queue_processes!=0
The index you're rebuilding must exist, must be partitioned and must be marked unusable
Don't forget to commit after executing this procedure

This procedure will submit to the job queue a seperate job for each partition of your index, and rebuild each one with the parallism you specified.
In RAC, when you specify instance_count_in, it will try to evenly distribute the job submissions across instances. So on a 5 node RAC an index with 10 partitions
and parallel 10 passed in will have 100 processes rebuilding it, 20 process, 2 partitions and 2 jobs running on each node.

This was created to overcome the limitation of a similar Oracle-supplied package that limits the index owner, the table owner and the index rebuilder to all be the same user.
This procedure doesn't have that limitation. I had a multi-TB domain index that took over a week to rebuild the normal way,
because the index rebuild parallelism is limited to the number of partitions of the domain index. I found this limitation unnecessary and impractical,
so I made this. Using this procedure it took about 4 hrs. There's nothing "domain index" specific about the rebuild statement,
so it will likely work for other indexes, although that hasn't been tested.

Any errors, such as ORA-29952 will be found in the alert log of the node that had the error.

*/

execute immediate v_Statement1 into v_Count using Ind_Owner_in, Ind_Name_in;
if v_Count=0 then
dbms_output.put_line('Not partitioned or error...');
execute immediate v_Statement3 into v_Count using Ind_Owner_in, Ind_Name_in;
if v_Count=0 then
raise_application_error(-20001, 'That index does not exist or is not marked UNUSABLE.',FALSE);
else -- not a partitioned index
quickly_rebuild.v_Last_Node := nvl(quickly_rebuild.v_Last_Node,0)+1;
dbms_output.put_line('Launching...');
dbms_job.submit( job => v_Job,
what => 'begin quickly_rebuild.ind_part('''||upper(Ind_Owner_in)||''','''||upper(Ind_Name_in)||''',''NOT_PARTITIONED'','||parallelism_in||'); end;',
next_date =>sysdate-1,
interval =>null,
no_parse => false,
instance => mod(quickly_rebuild.v_Last_Node,Instance_Count_in)+1,
force =>true);
dbms_output.put_line('Launched...');
end if;
else

execute immediate v_Statement2 bulk collect into c_IP using Ind_Owner_in, Ind_Name_in;
for i in 1..c_IP.last loop
quickly_rebuild.v_Last_Node := nvl(quickly_rebuild.v_Last_Node,0)+1;
dbms_job.submit( job => v_Job,
what => 'begin quickly_rebuild.ind_part('''||c_IP(i).index_owner||''','''||c_IP(i).index_name||''','''||c_IP(i).partition_name||''','||parallelism_in||'); end;',
next_date =>sysdate-1,
interval =>null,
no_parse => false,
instance => mod(quickly_rebuild.v_Last_Node,Instance_Count_in)+1,
force =>true);

end loop;
end if;
end;
end;
/

Extreme Goldengate performance on Exadata

2011-06-13T09:30:00.000-07:00

I had a birthday recently, and now I'm going to show my age. I remember once thinking, "What application could possibly demand the performance of an AT class computer?" I was in awe of how fast it was...how programs I had on my 12 Mhz XT ran so fast on my 20Mhz AT (with a turbo button) some of them were unusable. It wasn't long before that awe came and went. I think I may still have that machine buried somewhere in my basement. New technology and performance is always relative to the future, and its a fight today's very best technology can never win because no matter how unlikely it may seem today, there's always room for improvement.

As you may have read in my previous posts, I'm working on a project to move 22 databases (~30TB) times 4 environments from 3 IBM P5 595 frames to 4 Exadata frames. On the P5's, we used materialized views extensively to keep the inter-related data in sync within the multiple databases. As the databases became more and more busy, the mv logs grew and with their increase in size, the refresh performance dropped. We made multiple changes to have them refresh more frequently, but eventually we could see we were reaching the edge of the abilities of the technology on our hardware. At some point we began to have issues with performance where MV's weren't going to cut it any longer.

Something often forgotten or overlooked in the use of MV's is that there's huge overhead caused by MV logs. You don't just increase the overhead by refreshing the MV's...you increase the overhead with every DML statement on a table that has a MV log on it. Before I looked at it and quantified it, I assumed this was trivial, but its not. Jonathan Lewis did extensive research on that, showing that a single dml statement on a table with a MV log causes multiple DML statements in the background..and the overhead from "on commit" MV's makes them almost unusable in a high DML environment.

We felt like we reached the edge of the MV replication technology on our hardware...the next step forward in db replication was change data capture (CDC) technology. The concept with CDC is that every change made in a database (at least on the tables you care to run CDC on) is logged in redo and archived redo logs anyway for backup purposes. So, instead of adding overhead with triggers or MV's, you can just get the inserts, updates and deletes that happened from the logs you're already creating. All the changes are there...you just need to parse them and apply them to the remote database. The first step is to instantiate the remote table (copy as of an SCN via db link) then start CDC after the SCN you copied the data from. The next time you do an insert on the table on the source side, the change is put into a redo log. The CDC capture (or extract) process will see that and move that same insert to the table in the remote db. They're kept in sync in near real-time. (There's some lag in parse overhead, network speed, etc...)

I wasn't involved in the first pass at CDC here. About a year previously Oracle had sent some people to implement Streams...one of them a top notch Oak Table member, but they couldn't get it stable enough to depend on. This was an early implementation of "down" Streams in 10g (Asynchronous Autolog mode). We wanted to have the load of mining be on the target, not the source, which is a little atypical...maybe that's why we had so many issues? With the talent they sent us I can safely say it was the product, not the implementation that failed. Bug after bug was filed, after months and after huge expense the project was abandoned. Incidentally, I'm told 11g Streams (which is at end of life with no future features after 11.2.0.2) is much more stable today.

So...a year later the issue resurfaced and became a new "hot item." This time, I got a shot at it. I performed some proof of concept performance tests of multiple products working with vendors such as Informatica and Oracle Streams and at the time, an independent company called Golden Gate (which has since be bought out by Oracle). There was a very bad taste still in the mouth from the last time Streams was attempted...so there was no way that was going to be the chosen direction. Still, it was useful to include it in testing for comparison purposes.

When I perform these tests I usually have a list of qualities for the product prepared before testing begins (we call it a "Score Card"). After all, if a product is lightning fast, but isn't stable enough to rely on, it can't be a consideration. If its perfect in every way, but priced prohibitively, its unattainable. There's a balance of qualities that makes a "best" choice. We found Informatica was very robust and relatively easy to use. The bottleneck we found with Informatica was on the capture side. Informatica and Streams (and Shareplex) used Oracle Logminer for their CDC which was too slow to meet our requirements. We had planned to take a look at Shareplex, but decided not to when we saw it mined logs with the same method as Informatica and Streams.

After spending a few weeks with experts from Informatica, we finally had to say that it wasn't possible to meet the performance criteria we had set out, although they were close.

To be fair, our requirements are very difficult (and for a while, I thought unattainable), and I'm sure all the CDC technologies that use logminer would be more than sufficient for 99.99% of the requirements out there.

Before I go on...let me refer you to my previous post/rant of Golden Gate Director. Its the worst software product I've ever implemented, and my original motivation for blogging. It was so bad, I felt like I had to warn the world to avoid it. We just installed the latest, greatest version of Director...I really hope its been improved. The rest of this post I'm talking about GG CDC only....

In the CDC world, Golden Gate is different. I was told that the guy that originally designed Oracle Logminer did so more for auditing purposes and it wasn't originally designed for performance. Later, he was hired by Golden Gate and designed a new, proprietary miner...designed from the ground up for performance. Take that with a grain of salt...I heard this from GG sales guys.

My first concern was - using a 3rd party miner to mine Oracle's proprietary redo/archive logs would create a dependency in our system on a relationship between 2 competing companies. What happens to us if that relationship breaks down? Right around the time we went live with Goldengate, Oracle bought them- so that concern was removed. Thanks Larry. :)

As testing began on the P5's, GG was immediately much faster with our workload than logminer, and after a few days it was easy enough to use. In our non-RAC system we were able to mine around 55GB/hr of archivelogs, at which point the single CPU core it used was pegged. There was really no way to parallelize it to use more CPU. A GG consultant that moved to Oracle University taught me a trick to improve non-RAC GG performance when the CPU is the bottleneck (thanks Hitomi)...set it up like its a 1 node RAC. (ie, when setting up the extract, say "threads 1"). This will spawn 2 processes off, one to purely parse the log, one to hand off the work. This improved performance around 10% in our environment, to around 60GB/hr.

With our Exadata system, we'll be making around 1,300GB/day of archivelogs...so 60GB/hr isn't going to cut it. When I say 1,300GB/day, there are peeks and vallys of generation, and the app needs to stay as near real-time as possible. So...although 60GB/hr is fast enough on average...through out the day, we'd have times of lag that would be unacceptable to the performance of the applications.

As I mentioned, the CPU usage on the capture side was the bottleneck on the P5. Exadata's Nahalem EP not only has faster cores than the 5 yr old P595 cores, we're now in RAC, so we get to use more cores. When you set up the extract in GG for RAC, you have to specify the number of threads...each thread equating to the archivelog sequence from a specific node. In the full HP X2-2 Exadata machine I was testing on, that meant we had 8 threads and 8 nodes...so the bottleneck we had seen in the past not only had faster cores, we now could use 8 of them. This made me think...unless something else becomes the bottleneck first, we could see over 800% performance increase due to this parallelism. Ok, not really parallelism...but you know what I mean.

Before any tweaking on our first try, we were able to get about 400GB/hr. I've worked with a few people from Oracle/Goldengate, and Mike Bowen is one of the best. He has decades of experience in CDC and finds creative ways to overcome obstacles. Oracle sent him to us and together we were able to tweak the performance of Golden Gate on Exadata. We were only focused on the capture side. The 4 notable things he changed were:

1. Increase the size of the trail files. There's considerable overhead as trail files are switched (similar to redo log switching in the database), so increasing the size of these can improve performance by reducing the switch frequency..

2. Trail file storage destination was changed from local storage on the compute node to dbfs. This is something that should have been done per best practices anyway. To allow for failover in the event of a node failure, the trail files must be located on some form of shared storage. Having them on dbfs not only meets that requirement, but now they're on very fast spindles in the high performance storage nodes.

3. Switching from the Golden Gate v10 "TRANSLOGOPTIONS ASMUSER" method of ASM access to the new v11 DBLOGREADER method. As with new features, there were bugs found right away. This isn't what Oracle recommends, but I pull the latest/greatest GG version from Metalink patches, rather than eDelivery to avoid the bugs. GG patches aren't one-offs...they're the entire build...so the newest patch on Metalink includes every patch ever made and in theory, is the most stable. This, and everything else I'll ever say or type, is just my humble opinion...do what you think is best.

4. Increasing the read buffer size of the dblogreader to 4M.

TRANLOGOPTIONS BUFSIZE 4096000
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS DBLOGREADERBUFSIZE 4096000

Just a note if you're using Goldengate on Exadata. We encountered a bug that happens when your extract comes across HCC compressed objects in the log...even objects that are excluded, it causes the extract to abend. If you have any compression in Exadata at all (and of course you will), GG will abend. Oracle created a patch where that's corrected...if a table you aren't mining is compressed, that's no longer a problem. Hopefully that patch will be generally available on Metalink soon. For that matter, hopefully mining compressed tables will be possible soon.

After adding "TRANLOGOPTIONS DBLOGREADER" it created new entries in the dba_capture view (the view used by Streams), which is strange. The capture processes were disabled, but "start_time" was more recent than the start time of the GG capture process. Since there's a streams capture process created for GG now, this causes the RMAN-08137 errors when you try to delete archivelogs, even archivelogs that aren't needed for Goldengate (1079953.1). This is due to a delay in updating this view...so even though the log isn't needed, for a period of time, its still reported as needed. My opinion is, its great they brought this functionality to Golden Gate, to prevent you from removing archivelogs during a backup that GG hasn't read yet...but this needs to be up-to-date information! From this feature there's a new wait in the database:

Wait event "Streams miscellaneous event" in wait class "Other" was consuming significant database time.

Don't worry if you see this...it SHOULD BE an idle event, and its another bug. From Metalink:

The Streams miscellaneous event will be renamed to "Waiting for additional work from the logfile" to better describe the activity from Oracle release 11.2.0.2.x See detail in BugDB 12341046 for more information

After dealing with the new bugs we were able to read 700GB of mixed OLTP/DSS archivelogs in 46 minutes, to achieve just over 1000GB/hr (over 125GB/hr/node). After spending several weeks 2 yrs ago trying to scrounge for every last byte of CDC capture speed (I think I peeked below 40GB/hr on the old hardware with logminer), I find this near TB/hr speed ridiculous. Not only are we good to go for this year's peek performance requirements, we'll do it easily, in near real-time, with so much bandwidth to spare, its hard to imagine a day when this speed will ever be insufficient. Just like the "AT Class Computer", the awe of 1000GB/hr CDC will...I'm sure, be temporary. Golden Gate performance, although...not cheap, is pretty awesome...for now. :)

How much faster is Exadata High Capacity vs High Performance Storage? (short stroking)

2011-06-09T14:36:00.000-07:00

Compression is a powerful feature in Exadata, especially HCC compression...but in the real world you have time constraints on your migration project and it isn't necessarily possible to compress everything you'd like to compress immediately...you have to test and compare the performance impact on queries vs storage gains.There are so many options for compression in Exadata...basic compression (formerly bulk compression), compress for OLTP (formerly advanced compression), HCC compress for query (low, high) and HCC compress for archive (low, high)...not to mention index key compression, which I'll post about later. Which one you choose is dictated by your data access patterns. It takes time to figure out which is right for each table/partition.

This came up because my client has too much data to fit into a full high performance storage cell machine (over 28TB). We've dropped indexes where possible, which has had a hugely positive impact...but now we're finding many of them need to be added again for performance reasons. This was expected and planned...and we should be fine on capacity for the migration, but when this database hits the expected growth curve in a few years, we'll be in trouble.

One of the on-site Oracle consultants (who was very stressed about getting things to fit) suggested we move from our 80/20 data/fra High Performance storage machine to a High Capacity storage machine. He said, "Since most of your IO will be hitting the flash cache anyway, you should only see it be a few percentage slower." He said this w/o ever looking at our access patterns, so I dismissed it out of hand. Some other people on the project pointed out...companies don't buy Exadata because they want "good enough"...you buy Exadata because you want ultra performance. There were other "good enough" platforms that were much cheaper than Exadata. Knowing they have ~1:1 read/write ratio, I wanted to try to quantify the performance difference between the options. Everything is identical between an Exadata X2-2 high performance storage machine and an Exadata X2-2 high capacity machine...except for the spindles, so that's what I'll be focusing on.

Of course...all things being equal, High Capacity SAS storage cells are slower than High Performance SAS storage cells. The HP cells have 15000rpm 600GB SAS disks. HC cells have 7200rpm 2TB SAS disks (which are mechanically similar to the SATA disks from Exadata V1 machines...although the sales guys won't say it, Kevin Closson did: "...think in the same way you do about technology like FC-SATA (a SATA disk with FC attach and FC-SATA head electronics."

Things are not necessarily equal though because Exadata short strokes its storage. The idea of short stroking disks is that...the outside circumference of a spinning disk is moving faster than the inside...not in RPM's of course, but in the distance travelled by the head of the disk...so the throughput of the outside of the spindle is higher than the throughput of the inside, and if the head doesn't have as far to move, it will take less time for it to be where you want it to be.

Exadata takes advantage of short stroking and puts the most performant storage on the outside of the spindles for the data diskgroup, followed by the fra, and the most inside, slowest part of the spindle is used for dbfs. There's a new 11.2 feature that does something similar for local storage...but that's a topic for a different post. Once the diskgroup is created in ASM, there's a hash algorithm that distributes that data evenly around on the storage w/in that diskgroup...so short stroking in ASM can only happen when the diskgroup is created (barring a resize).

There's a standard data/fra ratio that Exadata uses normally...but the percentage of the storage vs FRA dedicated to data is variable in the Exadata setup script...we chose an 80/20 path. Obviously flash cache plays a huge role in IO for reads...but we're a little write heavy. Its possible that at some point as we short stroke the HC disks more and more, we'll approach or possibly excede the performance of the HP disks.

Here's the math for the SAS-2(6GB/s) drives:
2TB SAS Spec Sheet

2TB (High Capacity)
seek:8.9
rotational speed=7200
latency=7200r/m=120r/s=8ms/r
avg latency=8ms/2=4 (4.16ms from data sheet)
avg access time=13.06
Sustained Sequencial Read is 90MB/s(ID) and 144MB/s(OD)
Avg throughput:117MB/s

600GB SAS Spec Sheet
600GB (High Performance)
seek:3.65
rotational speed=15000
latency=15000r/m=250r/s=4ms/r
avg latency=4ms/2=2ms
avg access time=5.65
Sustained Sequential Read is 122MB/s(ID) and 204MB/s(OD)
Avg throughput:163MB/s

Latency is the time it takes for the spindle to spin around...sometimes you're closer and sometimes you're farther from the data you're going after...so worst case scenario, you're a full spin away...best case you're next to the data...so on average take latency/2 and that's what you can typically expect for avg latency. Avg access time is avg latency+seek time. This is how long you can expect the head to move per IO.

Again...we're doing an 80/20 configuration for data/fra...with data shortstroked on the outside edge we lower the capacity to 480GB (80% of 600GB), but our throughput on HP should be a little better than the spec at (204-122)*(1-.8)+122=138.4MBs (minimum inside). 204MBs (outside) to 138.4MBs(inside) so the avg throughput after short stroking is 171.2MB/s, increased from 163MB/s.

For the same storage (480GB) of data on the 2TB high capacity disks, we'll be using only the outer 23.4% (480GB/2048 GB) of the HC spindles. The spec throughput range is 90MB/s(inside) to144MB/s(outside), so (144-90)*(1-.234)+90=131MB/s (minimum inside). (144+131)/2 gives us an average of 137.5MB/s, increased from 117MB/s.

The same logic works for average seek times. If you only use the outer 50% of the spindle, you cut your seek time in half. In our case, we're using the outer 80% for HP and outer 23.4% for HC.

2TB SAS Spec Sheet

2TB (High Capacity Short Stroked)

seek:~~8.9(~~8.9ms*23.4%)=2.08ms

rotational speed=7200

latency=7200r/m=120r/s=8ms/r

avg latency=8ms/2=4 (4.16ms from data sheet)

avg access time=~~13.06ms~~6.24ms

Sustained Sequencial Read is ~~90MB/s(ID)~~ 131MB/s(ID)and 144MB/s(OD)

Avg throughput:~~117MB/s~~137.5MB/s

0-byte IOPS=77160

32k IOPS=~154

32k*160=5MB/s@137.5MB/s=1/27.5 seconds for transferring, rather than accessing the data, so the 32k IOPS would happen about 154 times/sec.

600GB SAS Spec Sheet

600GB (High Performance Short Stroked)

seek:~~3.65(~~3.65ms*80%)=2.92

rotational speed=15000

latency=15000r/m=250r/s=4ms/r

avg latency=4ms/2=2ms

avg access time=~~5.65~~4.92ms

Sustained Sequential Read is ~~122MB/s(ID)~~ 138.4MB/s(ID) and 204MB/s(OD)

Avg throughput:~~163MB/s~~171.2MB/s

0-byte IOPS=~~177~~203

32k IOPS=~195

32k*203=6.34MB/s@171.2 would take 1/27th of a second for transferring, rather than accessing the data, so that would lower the 32k IOPS to about 195.

Soo...for throughput 137.5/171.2 tells us the HC disks would be 80.3% as fast as the HP disks, not counting caching. For the all-important IOPS measurement, HC would be about 79% as fast as the HP disks. With the 1:1 read/write workload in this database and around a 90% hit ratio (a lot of activity goes to dbfs, otherwise the flash hit rate would be higher), we'd be looking at around 18.7% more performance from the HP disks.

18.7% might not seem like very much, but when you're paying for the state of the art, why would you take an 18.7% performance hit? At that point, alternative platforms become more attractive. Instead of going down to 4 Exadata machines filled with HC storage, we opted for 3 HP machines and 1 HC machine, with the option to add a 5th chassis filled with more HP storage cells, at an additional expense, of course.

Still...this was pretty close. Eventually as technology improves X3-2 (or 4 or 5)'s I bet we'll have a 3TB 10K HC spindle option. If so, they might come very close to outperforming today's HP spindles, after they're short stroked. Just for the heck of it...if Sun/Oracle does offer 3TB 10K's in the X3-2, this is a possibility of what we would see (still keeping with the 480GB/spindle for data use):

3TB (Future High Capacity Short Stroked)
seek:~~6.4~~(6.4ms*15.6%)=1ms
rotational speed=10000
latency=10000r/m=166.6r/s=6ms/r
avg latency=6ms/2=3
avg access time=~~9.4ms~~ 4ms
Sustained Sequencial Read is 125MB/s(ID) and 200MB/s(OD)
Avg throughput:162.5MB/s
0-byte IOPS=250
32k IOPS=~238

Exadata Backup and Recovery-2

2011-05-24T09:05:00.000-07:00

In a previous post, I gave a lot of details about our SUN backup hardware for our Oracle Exadata environment. Here's the breakdown:

Database : 8 Exadata compute nodes
DB Storage                      : 14 high performance storage cells
Backup Disk Storage      : single-head SUN 7420 ZFS server
Media Servers                  : 2 (X4270M2)
Admin Server                   : 1 (X4170M2)
Tape Storage                   : SL3000 Library (Base + Expansion)
Tape Slots                        : 838 (max 5965)
Tape Drives                      : 8 LTO-5 (via 8GB FC)
Tape Drive Bays              : 24 (max 56 LTO-5's)
Backup Software             : Oracle Secure Backup

The plan is to backup the database to the 7420 ZFS server using RMAN, then copy that backup to tape for offsite storage. The Recover Time Objective is 8 hrs. We'll have archivelogs stored in FRA and lev 0's and cumulative incremental level 1's on ZFS. If there's a failure within 1 week, we can go from either ZFS or Tape (I'm playing with the idea of both, but I'll save that for a seperate blog.) Here's the test plan:

There have been at least 3 white papers I'm aware of that discuss redo apply rates on Exadata, but really, this comes down to your activity. We have very nearly pure OLTP activity, which is relatively slow for redo apply, so we need to test with our workload.

Here are the test steps:

1. Create a flashback point using flashback database
2. Take a level 0 backup of an existing ~8TB database to ZFS
3. Generate 1300GB of archivelogs
4. flashback to first restore point
5. recover database

We were told, and the whitepapers from Oracle confirm that the apply of 1300GB or archivelogs should take about an hr on our full Exadata machine...but with our OLTP workload, the recovery took 4 hours (after tweaking it many ways)! That's half our RTO! Future tests will try to break up the 1300GB with level 2 cumulative backups, so hopefully we'll be able to cut the time down.

Having the recovery take 4 hrs, not 1, means the restore needs to complete in 4 hrs, not 7. To do these tests quickly, I'm breaking up the restore time, the incremental time and the recovery times in seperate tests and tweaking each individually before combining for the final result. After doing many tests, I discovered something interesting.

I already knew the ZFS head is our bottleneck at 1.12GB/sec. I was able to achieve that speed after turning on jumbo frames and going active/active on the infiniband network. I'm getting off track here, but this may interest some people:

1. Active/Passive, small MTU on 40GB Infiniband: 550MB/s
2. Active/Passive, Jumbo frame MTU                    : 660MB/s
3. Active/Active, Jumbo Frame MTU                      : 1200+MB/s

(Note: Don't take this to mean IB is pegged. One of the Oracle BUR whitepapers said they were able to achieve 2000MB/s via IB, so my bottleneck at this point is the single-head of our 7420. When we need more speed in 3 years, we'll go to a dual head.)

People generally equate compressed backups to "slow" backups. Its true that, channel for channel, compressed backups add CPU overhead that slows down the backups. On Exadata, running a backup across all nodes, we have 96 cores. We also have the ability to throttle the backups with ORM and Exadata's IORM, so the backup will have a lower priority of CPU and IO than other activity. This is better than using the rman RATE parameter IMHO, because if the resources are available, we can take it. We ran tests using rman "medium" (zlib) compression, opening a varying number of channels (32, 64, 128). We found opening 64 channels (8 per node) was the sweet spot in backup performance.

When I did this with zlib (in 11.2, its called medium) compression, I was able to get much faster backup results than I could do without compression. With no compression I was limited by the 1.12GB/sec 7420 ZFS head speed. I was getting a ~4.5X compression ratio, so in theory, I could get 4.5X1.12GB/sec (5.04GB/sec). In reality it was less than that, but its still much better that 1.12GB/sec. (results below)

My test used a representative 8TB db, but in a few months many more databases will be consolidated into this DB, bringing it to 20TB (compressed, in 5 years this will be upwards of a PB uncompressed), and I expect a 6TB cumulative incremental size. As I mentioned, given the 4hr redo log apply time, the restore and incremental apply needs to happen in 4 hours or less. I extrapolated the 26TB restore time based on the 8 TB db in the "Time(HR)@Rate" column. I think its also interesting how much CPU was consumed on the compute nodes during these transactions. Here's a subset of the test results:

Test	Iteration	duration	ch	comp	NW Config	Size GB	Comp Ratio	Time(HR) @Rate	Comp Mps	ZFS Mps	CPU
Exadata2ZFS	1	42.5	64	yes	AA	8TB	4.23	2.38	3186.71	753.36	14.75%
ZFS2Exadata (RESTORE)	1	69	64	yes	AA	7871	4.43	3.89	1947	439.5	3.00%
ZFS2Exadata (RESTORE)	2	65	128	yes	AA	7871	4.43	3.66	2066.64	466.51	3.40%
ZFS2Exadata (RESTORE)	3	90	32	yes	AA	7871	4.43	5.07	1492.57	336.92	3.00%
Exadata2ZFS	2	10	16	no	AA	727	1	6.1	1240.75	1240.75	-

These results give you an idea of what I mean. With a 1:1 compression ratio (bottom line), the uncompressed backup was limited by the ZFS head speed at 1240MB/s. Considering compression, I was able to achieve 3187MB/s for a backup and 2067MB/sec for a restore (with a single head 7420!). There's no way an uncompressed backup can compete with that speed. In addition, the compressed backup on ZFS means I'll have even more space to store backups and incrementals there.

Its almost dishonest to bring that up though, because if we weren't using compression in RMAN, we'd be using ZFS compression, achieving approx the same level of compression, so really, that advantage is a wash. I've seen tests where people have used zfs compression for their backups...the problem is, they're still hitting the bottleneck...so for compression's sake, that's great. For performance, that's very limiting compared to these speeds.

To recap so far, the redo apply rate allowed 1300GB to be applied in 4 hours, the extrapolated restore of 20TB data and 6TB cumulative incremental will happen in 3.66hrs, leaving .44 hrs (26.4 minutes) for the cumulative incremental apply time. When I tested that, if finished in exactly 20 minutes. This means the full restore and recovery will happen with 6.4 minutes to spare.

That's cutting it close...with level 2 incrementals, I'll be able to cut the 4 hours into a much smaller time period. If we only do 1 lev 2 incremental per day, that would bring us down to ~650GB of archivelogs we'd need to apply...and applying the level 2 is much faster than applying redo.

I broke up the restore, incremental apply and recovery pieces of the RTO so I could tweak each as I went along. When I did the entire thing it completed in slightly less time than the individual pieces did due to RMAN "spin-up" time. The RTO was met in the 8 hr time frame.

The hardware sales engineers from Sun *really* know their stuff. We asked them to use "actual" rather than theoretical speeds to design the HW for our system, and they hit it on the nail.

...but what if we have to restore and recover from tape? :) I'll blog about my tape performance tests soon.

Part 1:

http://otipstricks.blogspot.com/2011/03/exadata-backup-and-recovery-1.html

Part 2:

http://otipstricks.blogspot.com/2011/05/exadata-backup-and-recovery-2.html

using encrypted db links in 11g

2011-04-08T09:15:00.000-07:00

I made a bet the other day with somebody I work with. We were in need of some passwords to re-create database links during a migration from 11.1.0.7 on AIX to Exadata X2-2. He said it was no longer possible in 11g+, I said it was. In previous rdbms versions, there was a password field included in the dba_db_links view you could use along with the values keyword to re-create the database links without knowing the password. Since this field was removed from the view, he assumed it was no longer possible to use the values keyword when creating database links.

I was willing to make the bet because I knew we can still do exports/data pumps. When these are imported on the target side, they don't have the original password, so the encrypted password must still be stored somewhere, and there must be some statement that's still used.

I created a schema in 11g, created a db link and exported it. Looking at the mostly binary file, I see this:

CREATE DATABASE LINK  "EXADATA1.MYCOMPANY.COM" CONNECT TO "TESTER" IDENTIFIED BY VALUES  '054B7E837E4DDD8040336B8D6551171D7A179DC8D8E2148ABC' USING  'REMOTEDB.MYCOMPANY.COM';

In previous versions of Oracle, this password field was much shorter due to the weaker encryption used...today they've switched to SHA-1, combined with a salt field (stored in spare4 in sys.user$).

So...the syntax for the statement is still valid...but where is it getting the encrypted password that's no longer included in dba_db_links? I could have run a trace on the export, but I thought it would be faster to look at the source of the dba_db_links view and check the underlying tables. Here's the query for the view:

   SELECT   u.name,
            l.name,
            l.userid,
            l.HOST,
            l.ctime
     FROM   sys.link$ l, sys.user$ u
    WHERE   l.owner# = u.user#;

...and describing sys.link$:

Name                                      Null?    Type
----------------------------------------- --------
OWNER#                                    NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(128)
CTIME                                     NOT NULL DATE
HOST                                               VARCHAR2(2000)
USERID                                             VARCHAR2(30)
PASSWORD                                           VARCHAR2(30)
FLAG                                               NUMBER
AUTHUSR                                            VARCHAR2(30)
AUTHPWD                                            VARCHAR2(30)
PASSWORDX                                          RAW(128)
AUTHPWDX                                           RAW(128)

So, after I got the user_id from dba_users for the db_link owner, I selected from sys.link$ and was able to pull my encrypted password.

For the client I'm working with today, they have hundreds of apps and thousands of passwords. For security concerns, very few people are privy to this information...so re-creating db links can take a very long time due to coordination efforts. This process could be used in the future by dba's who don't need to know the passwords stored in the database links in order to migrate the db links. This will hugely improve their efficiency, but more importantly, it means I won the bet. :)

Why isn't the optimizer doing the right thing!?!?

2011-03-21T08:20:00.000-07:00

The purpose of the Oracle Cost Based Optimizer is to quantify the different possible ways to execute a query and compare those options in order to figure out what the best way is to run your query. For even a small query, the options are vast...its a very difficult job and IMHO, the CBO does a great job when its supplied with enough accurate information about the problem. That being said, there are times when its decisions are perplexing, even to people who feel they understand the CBO.

When you go to the optometrist and take a vision test, you run through multiple questions..."Which is clearer, left or right?" After all these test your vision's clarity can be quantified to represent how clearly you see. Even if your vision is perfect at "20/20", there are people who can see even more clearly than that, so vision is always relative. This is how I think most technical knowledge is. If somebody asks you, “Do you understand the CBO?”, you may think the answer is yes, and you may grasp the concepts very well...but *very* few people have 20/20 clarity on the topic. In vision like in complex topics like the CBO, understanding is relative.

To gain better vision into the CBO, the definitive reference is Jonathan Lewis. If you get a chance to hear him speak or read his books, do it. Wolfgang Breitling is another of my heroes. I had the opportunity to meet him at Hotsos a few years back...he's brilliant, and like Jonathan Lewis, he approaches being a DBA from the perspective of a mathematician.

There have been many blogs and books by brilliant authors (like Jonathan Lewis, see link to the right for his CBO book) to answer the question, “Why isn't Oracle choosing the right plan?”. I just want to point you to another source. After spending years reading several books and tons of blogs...I gained new levels of clarity on the topic when I read Wolfgang Breitling's white paper:

A Look Under the Hood of CBO The 10053 Event

The copy I got a few years ago was very old at that time...but the vast majority of it-if not the formulas, than the concepts-still apply today. In modern Oracle RDBMS versions with system stats, the rough time estimate cost in the CBO has switched from counting I/O's, to the amount of time needed to read a single block, so its a little more tightly coupled with the concept of time.

The idea behind the CBO is to quantify and compare multiple potential plans...so if its quantified in time or IO, it doesn't really matter...the result should be the same. Keep in mind, it does make it difficult to compare plan costs between older DBMS versions though.

When I get a chance, I'm planning to revisit the concepts of his 10053 analysis a bit on the Exadata platform to see what's changed...I'll post anything interesting I see.

Who has dba privs?

2011-03-17T15:41:00.000-07:00

A database auditor asked me for some quick help answering the question, "Who has DBA privs?" Somebody from the client's dba team did a simple:

select * from dba_role_privs where granted_role='DBA'

That's ok...but some people were still demonstrating DBA abilities who didn't show up on that list. If somebody is granted a role that's been granted dba...or a role that's been granted a role that's been granted dba (etc) this statement would miss them. This can go on infinitely deep, and it can be a way to hide dba privs from auditors if its buried deeper than the person searching for the priv is willing to check.
This is a hierarchical query problem. Traditionally, hierarchical queries are used to answer questions like, "Who is under the CIO?" Direct reports are easy...but a person who reports to a person who reports to the CIO is more difficult...the more layers, the more difficult it is.
The first time I had to do this was on a Sql Server ERP I was supporting...I had to write a query that went 6 levels deep...it was complex and ended up being about 2 pages long. To do on Sql Server you have to find the root node and then all the leaf nodes for all the levels of the tree. Quite the pain. About a year later we migrated that ERP to Oracle...the new statement in Oracle that was logically equivalent was just a few lines. This is because Oracle's SQL extensions, "CONNECT BY PRIOR" and "START WITH" make this relatively easy.
The request from the auditor was to find all the people who have sysdba, sysoper or dba privs, no matter how they got them. (note: The statement below will display people twice if they're granted sysdba/sysoper AND a deep dba role...the auditor was ok with that)

select username, 1 level_deep from V$PWFILE_USERS
union
select grantee, max(level_deep) from (
select distinct level level_deep, grantee, granted_role
from dba_role_privs
start with granted_role='DBA'
connect by prior grantee=granted_role
) where grantee in (select username from dba_users)
group by grantee
order by 1;

With variations of this statement, he was able to find how some people who weren't directly granted DBA were able to use DBA privs. He even found one guy who had a dba role that was granted 8 levels deep...(ie: he was granted a role that was granted a role (repeat 7 times) that was granted DBA!) That would never have shown up on older audit reports.
Hopefully this will help some of you tighten up your db security and aid in your hierarchical problems.

Exadata Backup and Recovery-1

2011-03-10T13:21:00.000-08:00

In theory, there's no difference between theory and practice, but in practice, there is. When theoretical maximums are used, life occurs, and the reality sets in. Seldom do companies even have an agreed upon RTO, and even more seldom are they proven out until a disaster occurs. An RTO that isn't proven is called a guess...and even an educated guess based on published theoretical maximums is a guess of what the best case scenario could be. My current client has quarterly requirements to meet the RTO, and if the recovery plan can't hack it, adjustments (even if that means additional hardware purchases) must be made. Designing a new backup infrastructure and recovery plan for this Exadata environment needs to not only be precise, it needs to be proven.

In my previous posts on virtualized storage (specificly the Hitachi USP-V), I talked about doing Oracle database backups via snaps on the virtualized storage. They make life much easier for the DBA, with interfaces directly into RMAN. Unfortunately, until Oracle buys or partners with a storage vendor (I'm hoping for Netapp) to bring that technology to Exadata, we're limited to traditional backup methods.

For the last several months, one of my focuses has been on meeting the recovery window set out by the business. At first the requirement was...worst case scenario, we have to do a full recovery, everything needs to be running in 4 hours after a disaster. This is an OLTP VLDB that generates about 1300GB of archivelogs/day. Our RTO is RECOVERY time objective, not restore time objective...so archivelog apply on Exadata is relavent to my issue.

Archivelog apply rates have primarily 2 big variables...the hardware speed and the type of DML. A database with a DSS workload will apply much faster than an OLTP database on the same hardware. Other factors affecting our apply rate are flashback database and Golden Gate, which requires supplemental logging. 1300GB archivelog generation rate is less than what its going to become...how much more is anyone's guess. Our on-site Oracle consultants are guessing 2X, but I think that's an over-estimate to play it safe.

To understand the size of the data that needs to be restored, we have more variables. The uncompressed size of this database is expected to be many petabytes (projections are in flux...but more than one, less than 5). We've been sold a single full rack exadata machine (in an 80% data, 20% fra configuration), with a contingency for more storage cells as needed for the next 3 years. After 3 years the growth rate accelerates and we'll have more Exadata machines to add to the mix then. Depending heavily on HCC and OLTP compression and based on growth projections, we're going to say in yr 1 we only have 20TB after index drops and compression.

This database is going to be a consolidation of 22 smaller databases. The plan is to have a full backup once a week and cumulative incrementals the other 6 days. Based on the current activity in those databases, We're going to say our incrementals will be at worst 6TB.

This brings up an Exadata rule-of-thumb...block change tracking (bct) hugely improves incremental backup speeds. Basicly, as a change to a block is made, a set of blocks is marked "changed" in a file. The next time you do an incremental, only the block sets in the BCT file are sent to backup. Storage Cells also have a similar functionality...in Exadata, if you aren't using BCT, the storage cells will find the blocks that have changed and return only changed sets of blocks. Neither method actually marks the individual blocks as changed...the blocks are grouped together and if one block is changed, several blocks are marked changed. Storage cells mark the changed blocks in smaller sets...so they're more efficient than BCT. According to "Expert Oracle Exadata" (link to the right) by Kerry Osborne, Randy Johnson and Tanel Põder, (I'm not sure which author said this, I have the alpha version of the eBook...which anybody with interest in Exadata should buy), using BCT is still faster if less than 20% of your blocks have changed...after that, its faster to offload the functionality to the storage cell. So, based on our 6TB/20TB per week changes...it would likely be best for us to do BCT early in the week and use the storage cells' incremental offloads later in the week. We'll have to test it.

So, worst case scenario, we need to restore a full that's 20TB+6TB of incrementals and then apply ~1.3TB of archivelogs in 4 hours. To complicate things, we can't do backups to the FRA because our database is too big. Using the 80/20 configuration, we'll only have ~9TB (20% of 45.5TB in the High Performance machines). We can always add more tape drives and media servers to make the process faster, my biggest concern is the redo apply rate, since its mostly a serialized process...its not scalable.

We're buying a ZFS storage server (Sun 7420) with connections via Infiniband for "to-disk" backups and a SL3000 tape library with 2 media servers and an admin server. We'll be utilizing a new 11g HA OEM setup on its own hardware to monitor the backup solution (and all the other Exadata goodies.) Although its nice to have 96TB waiting around to be used, its going to go fast as a backup destination. Using this as a backup destination limits our options...it takes away the best option...merged incremental backups.

Merged incremental backups allow you to take a backup once (and only once...ever), and then always apply incrementals to that backup to keep it up to date. Its the fastest way to do backups, and its Oracle recommended backup methodology. More importantly when the time comes to do a restore, for example a restore of a datafile, instead of restoring the file from tape or disk, you go into rman and say "switch file 5 to copy;" Whatever archivelogs are needed to recover that datafile are applied and without even physically moving the backed up file, your database is back to 100%. The backed up file is now the active file that your database is using. While its up then, you can go through a procedure to fix the file in the original destination...but your uptime is maximized. Obviously, when using Exadata, the features you depend on that normally come from the storage cells only work on things that are stored in the storage cells...so although "switching to copy" works, it isn't a practical option for Exadata when you aren't using the FRA.

Originally, the recovery window was 4 hrs, so Oracle presented us a solution with 2 heads for a ZFS server with many spindles, 4 media servers and 28 LTO5's (sadly, the T10kc's won't be out in time for our purchase.) After the business recovered from the sticker shock, the recovery window was increased to a more resonable 8 hrs. Oracle then presented us a scaled down (cheaper) solution with 8 LTO5's and a single-headed 7420 with 48 spindles.

The ZFS server will have 2 trays of 24 2TB disks...which I'm told will be the bottleneck. The single head on the 7420 can do 1.12GB/sec...so unless the disks are *really* slow (around 50MB/s), or the ZFS overhead is extremely high...I think the head will be the bottleneck...but we'll see in testing.

In this design there were a lot of theoretical maximums used. We've insisted the design be tested and benchmarked before we purchase it, so Oracle has been good enough to set up a similar set of hardware in their lab in Colorado to prove out the numbers we'll see for our recoveries. If Exadata backups interest you...I highly recommend you read the new white paper that was published in the last few weeks on the topic at http://www.oracle.com/technetwork/database/features/availability/maa-tech-wp-sundbm-backup-11202-183503.pdf.

Part 1:

http://otipstricks.blogspot.com/2011/03/exadata-backup-and-recovery-1.html

Part 2:

http://otipstricks.blogspot.com/2011/05/exadata-backup-and-recovery-2.html

Quick plug for a great book

2011-03-09T11:29:00.000-08:00

Its not out yet, but if you're interested in Exadata, check out Expert Oracle Exadata. You can download the alpha book NOW and eventually when its finished you'll get access to the full ebook.

http://apress.com/book/view/1430233923

I just signed up for the alpha program and d/l'd it...I can't wait to read it...yet I'm ashamed that I would be this excited over a book that's so geeky. I wonder if it comes in the original Klingon print?

Exadata-index dropping and compression

2011-02-21T12:51:00.000-08:00

As I said in the previous posts...there are a lot of variables in play as we move to Exadata...the two I'm most concerned about is the effect of overhead due to OLTP compression and the effect of dropping many of our indexes.

I've never read anything from Oracle that says, "If you have Exadata you don't need indexes." I've heard it implied though, in the context of storage reduction. Data Cells are licensed by the spindle, so if you need more storage, it gets expensive. When you bring this up, the sales guys will talk about how you'll get great compression rates with HCC (which is true) and you'll reduce space because you can drop indexes. To no fault of Oracle's, some people extrapolate that idea and think, "I can drop all indexes...or most indexes."

Before we look at dropping indexes, let's start out with a baseline. I've installed the SOE schema for Swingbench...I'm hitting the scan listener of a High Capacity half rack of Exadata using 500 sessions. Some swingbech settings I've modified:

Number of Users: 500
Min Delay between transactions: 0
Max Delay between transactions: 1
Logon Delay: 10ms

Almost all defaults are being used for the Exadata machine. There would be huge gains in performance if this was tweaked...but for our purposes, this setup is fine.

When I first ran this test, the best I could do was about 310K, which is blistering fast...but then I learned we had a hardware failure that was preventing the use of one of our storage cells. The failed equipment was replaced and I re-ran the test.

I was able to get just over 400K transactions per minute (in red above)...that's very fast. For a frame of reference, our legacy system test was on an LPAR (IBM's VM) with 16 CPU's on fast 15k disks using an EMC Clarion CX4...definetaly no slouch, but it was 5 years old, and it was only able to produce ~63K TPM.

OCS' plan is to compress all tables (that aren't being replicated by Golden Gate) with either OLTP or some form of HCC compression and to drop all indexes that aren't either PK/UK indexes or indexes supporting FK's. The patch for Golden Gate that allows it to mine compressed tables still hasn't come out, so that's why those tables are being skipped. We may use Stream on some OLTP-compressed tables, since the 11.2.0.2 version does have the ability to mine archivelogs of compressed tables. Let's try some OLTP compression on Exadata. I altered the tables to be "compress for OLTP."

select 'alter table SOE.'||table_name||' compress for OLTP;'||chr(13)||'alter table SOE.'||table_name||' move;' from dba_tables where owner='SOE' and partitioned='NO' union all select 'alter table SOE.'||table_name||' modify partition '||partition_name||' compress for OLTP;'||chr(13)||'alter table SOE.'||table_name||' move partition '||partition_name||';' from dba_tab_partitions where table_owner='SOE';

...and then I rebuilt all the indexes.

The new baseline test results are nearly 450K trans/min! This is the fastest Swingbench score I've ever seen, and this is only on a half rack of relatively slow 7,200 RPM high capacity SAS storage! The other 3 high performance machines just arrived today...I can't wait to see what they can do! Granted, I expect there was serious caching in the db_cache...but this still says a lot about the compute nodes, since decompression can be done on either the compute or storage nodes and all compression is done on the compute nodes.

Next, (as is planned by our Oracle architects), I mark "unusable" all the indexes that aren't PK/UK's or supporting FK's. I'm leaving the function-based index usable though.

I restart Swingbench and begin the "index reduced" run...this is what I see:

All 96 threads on all servers are pegged at 100% user (in green above)...almost no CPU is used by system or IO waits. My response time is through the roof reaching 1266ms (red line above), and Transactions/Min has plummeted to less than 20K.

I ran ADDM/AWR reports and checked out the high waits during the "reduced index" test. What's happening is that, without indexes on the columns used in where clauses, we're forcing full table scans, but only 2% of the time is being spent actually doing the FTS.

This is exactly why I wanted to run these test. All over the internet you can find results of tests where somebody executes a single query to encounter a storage index with timing...things act differently when there's contention in an OLTP environment.

The high wait was "enq: KO - fast object checkpoint". One of the features of 11g is that the optimizer can choose to use direct path reads even if you don't specify the append hint or make the transaction parallel...this is called a serial direct read. Direct path reads are necessary to use storage indexes and HCC compression...and basicly to take advantage of all the special sauce Exadata offers. To begin the direct path read, a segment checkpoint is needed. The idea is that in a direct path read, we're going to pull the data straight from disk (or flash card) into memory in the PGA, which is very fast...you avoid all the SGA overhead. If there are dirty blocks in the SGA, we'd get incorrect results...so the dirty blocks are flushed to disk before we start our direct read.

As Tanel Poder put it, "You see this event because of object level checkpoint which happens when you run direct path reads on a segment (like with serial direct read full table scan or parallel execution full segment scans).

Now the question is-how much of your session's response time is spent waiting for that event - and whether the winning in query speed due to direct read/parallel execution outweighs the time spent waiting for this checkpoint."

In a DW/DSS environment, this feature will accelerate performance. With fewer process to contend with, the chance of creating a long "fast object checkpoint" wait is reduced, and your IO transfer is faster, but we're implementing Exadata in an OLTP environment.

This isn't an Exadata-specific issue...its just the way Oracle works on a hot segment with direct path reads in 11g+. By dropping the indexes we're forcing the optimizer to do DW-style IO in an OLTP environment. We're handcuffing the Oracle optimizer and taking away its options. The answer for this as we migrate will be to replace the indexes that were removed...and basicly only drop indexes that aren't used very often.

The take-aways from this are:

1. Don't believe you can just "drop all indexes" or that Exadata doesn't need indexes...your workload, not the hardware dictates this. Exadata will work better than anything else out there if you don't have indexes and you're forced to do a FTS, but for an OLTP environment, indexes aren't optional.

2. OLTP compression on Exadata is extremely fast. OLTP compression performance has always been a tradeoff between the time saved in IO vs the extra time used consuming CPU. With the fast Nahalem EP's in the X2-2, compression actually improves performance.

Exadata feature performance overhead

2011-02-21T12:10:00.001-08:00

We're getting ready to migrate 88+ large non-RAC OLTP databases from an IBM P5 595 into a single RAC database running on 3 High Performance Exadata machines and 1 High Capacity machine. We're having Oracle Consulting Services do the actual architectural design and migration work and I'm here to keep them on their toes and make sure the recommendations work from a holistic perspective on the overall system. There are a lot of changes that will be made during this consolidating migration...even one variable can have a negative effect that could impact the entire system. Before I get into the variables I see as having the largest impact (and before I test them) let's talk about the hardware a bit.

Exadata is much more than a "database-in-a-box." Its a set of compute nodes (think RAC node servers) combined with ultra-fast infiniband (...and 10GB ethernet, and multiple 1GB ethernet) and storage nodes. Storage nodes are basicly "helper" servers with local storage. The database can send requests to the storage node (that it sees like an ASM disk), the node will be able to handle the request and just send the results (offloading the workload) or it'll handle it like a traditional ASM disk would handle it...returning the blocks back to the compute node. New concepts in storage nodes include storage indexes (dynamicly created indexes stored on flash storage that may/may not be used to retrieve your data), compression/encryption offloading (cpu in data cells decrypt and uncompress the data, if that's in your best interest. Sometimes Oracle will choose to send the compressed blocks to the compute node instead.) When doing incremental backups, the storage cells will only send the changed blocks to the compute nodes to accelerate performance. There's a misconception out there that RMAN backups can be completely offloaded to your data cells...this (mostly) isn't true...what Oracle means by that is that the CPU interrupts when accessing the spindles are handled by the data cell...all the rest of the work is handled by your compute node. From a db that's on a server with local storage, this is a big improvement...for a db that uses a SAN...its not really an improvement.

Storage indexes are a unique feature that allows us to drop indexes and perform full table scans very fast. Exadata will create storage indexes dynamicly, based on the first query to hit the tables in question. The first time you access the table, you perform a full table scan. The storage cells will then build a storage index on your table. The second time you query that table, the storage cell will have the option to be able to access the table via the storage index. There are a lot of great blogs re:storage indexes out there, so I won't rehash the topic again, except to say, in theory, you can do a full table scan and have your results returned almost as fast as if you had done an index range scan...and depending on the amount of data you're returning and how its layed out (clustering factor), maybe even faster.

Other great Exadata features include multiple types and levels of compression (HCC/OLTP) that are handled in some situations on the data cells themselves. The marketing guys at Oracle will say you can take your database "as-is" and drop it into Exadata. Some people will take it to the extreme and claim you don't need indexes at all...which is a misunderstanding of the technology. Oracle's Advanced Consulting Services is planning to drop all indexes except those supporting FK's and PK's and compress almost everything else via OLTP or HCC compression. They'll then compare performance with the legacy system (via RAT) and put indexes back where they're needed. What will be the effect on CPU/IO...and ultimately performance in an OLTP environment?

Again, lots of people on lots of blogs have discussed the features. What I haven't seen blogged about is the overhead of these activities. Yes, dropping indexes and having quick restores may be possible, what what happens on a macro level to the system? Poder and Claussen have talked about storage indexes and their effect on a query...and for a DSS system that's all that matters...but in an OTLP environment you'll have many statement happening simultaneously by hundreds of users. What's the overhead of storage indexes then? What's the overhead of OLTP compression on Exadata in an OLTP scenario?

Let's see if we can get a baseline of what the planned implementation of these features will do. I'll install Swingbench with a 10GB database and run a series of tests on a half rack (4 compute node, 7 data cell) high capacity Exadata machine. Swingbench is an excellent graphical tool that provides performance information and simulates OLTP-type load. Please don't think these tests are something you can deturmine Exadata performance against...there are a lot of limiting factors outside of Exadata in play here (like the network speed between swingbench and Exadata, etc...)...so...just measure these tests against each other and the variables I introduce.

GoldenGate Tips and Tricks

2010-12-10T10:43:00.001-08:00

GoldenGate was bought out by Oracle last year. Around that time, I was asked to take a look to see how it could be used as a Materialized View (aka Snapshot) replacement. As we took a closer look, we found more and more uses for this great product and the project sprawled. The more its around, the more uses we find for it. Its worth every penny...but like everything else...don't just install it...research, test and find its limitations before you depend on it.

Some of the details are possibly out of date on this post...I've put off writing about GoldenGate for reasons stated below. Forgive me if this seems harsh...I hold the highest respect for the people who work at Oracle and the work they do to bring excellent software to the marketplace. I've made my career off of them, I know many of them and...besides the occasional bug, I've never had a genuine negative experience with them. My complaints below relate to the now merged company Golden Gate, and really, only with Director (licensed by the Golden Gate mgmt pack)...Golden Gate (the product) itself is excellent. I've spoken with employees at Oracle who agree with my opinions, I even had to cover for an Oracle consultant who was threatened with being fired for telling the truth about Director.

Before I get into the details of why GG is great, let me tell you why it isn't (and then I'll tell you how to get past the problems.) The difficult part of it is monitoring and administration. When Oracle purchased GoldenGate, along with it came a product called Director. Director is a Web Logic based application that promises to allow people to make changes to GoldenGate configuration files and monitor their application. This is necessary unless you're ok with your app dba's and developers getting Unix-level access to your database servers. GoldenGate is wonderful, but Director is probably the biggest software disappointment I've ever seen. On one day I launched 13 SR's with bugs to Metalink...not little things...each of them deal breakers. Some of the issues I found:

1. Every few days Director crashes...you have to restart the WL instance to bring it back up.

2. The security layers (different users can do different things) they have no longer worked in the version we were testing...and I was told they hadn't worked in several previous releases. This means every Director user had full authority to do anything to any configuration on any database, and start and stop replication.

3. If more than 2 people try to use the web app at a time, their GUI configurations become in conflict (due to the shared db connection Director uses to its repository) and the first person in has all their icons moved on top of each other to the top left corner of the screen. We have some complex configurations with hundreds of icons...so this means you have to drag the icons back out to where they were one at a time...taking sometimes 20 minutes...and this happens often.

4. There's no logging of who does what...so I can go into your configuration and make changes and nobody will know. Its also very difficult to trace back causality of bugs and issues.

5. [UPDATE 10/6/2011-There is a huge security hole that has been around for many versions. I brought it up with an SR and I know it was brought to the attention of the GG product manager, but they chose to not correct the issue. At Openworld 2011 I talked to Mr Screvens, who seemed genuinely concerned about the issue. I think its finally going to be addressed, but in the meantime, there's an undocumented work around you need to use to protect yourself from the issue. I'll blog about that asap.]

6. Of the *many* SR's I created in total, very few of them were corrected because Oracle was coming out with a product to replace it called Monitor. I was told at the time it was due out in the fall, now I'm hearing early spring, although the beta is available now. My impression was that Oracle was picking and choosing what they were willing to fix, since they knew it was going away soon. I haven't had a chance to test out Monitor yet, so I can't say if these issues have been corrected...I hope they have been. GoldenGate is a truly great and useful product...it just needs some attention to details that it wasn’t getting under the watch of GoldenGate, the company. Oracle, in my opinion, is usually very good at meeting user needs and correcting security issues, so I hope they'll pay attention to these issues.

The reason I waited almost a year after notifying Oracle of these problems is because I consider publicizing security issues to be irresponsible. I notified Oracle and I put off writing about these issues to give them time to correct them...after a major release (11) and several minor releases, they've hopefully corrected them. If they haven't, people need to know how to protect themselves from vulnerabilities while setting up GoldenGate.

...enough about the bad. How can you get past these issues, what are the tips and tricks? I'll cover them in my next post.

Virtualized Storage (USP-V) for Oracle Databases-4. thin provisioning

2010-12-09T07:43:00.000-08:00

Let's say you need to build a database that's expected to be 7 TB within 2 years. When you create the database server, you tell the storage team or administrator you need 500GB times 20 luns, for example (10TB). You know you need less than that, but its better to be safe than come back for more space later. In a nutshell, thin provisioning is when you're presented with the luns you asked for, 10TB in our example...but physically, you only allocate storage as its needed from the storage. If you only use 2TB the first year...although your server thinks it has 10TB...you're physically only allocating 2TB leaving 8TB for allocation by other servers.

The concept of thin provisioning is motivated by the fact that every server in an enterprise has unused, but allocated space every time a lun is carved out. On local storage, this is just something you live with, but on expensive san storage, this adds up to a huge amount of space...an independent company recently did an audit and claimed upwards of 40% of our storage is allocated, but unused. This is millions of dollars of underutilized storage.

As a database guy, my instinct is to say there's no such thing as wasted storage...the empty space means I'm occupying more spindles giving me the potential for more database speed...but the fact is that empty space is unmanaged. We always need free space for growth, but by the time you sum up empty space in blocks, tablespaces and storage...you're talking about a huge amount of space. It would be better to occupy that space with a managed ILM approach...sharing rarely accessed data with highly accessed data, or packing it in on storage with enough speed to handle it. I don't love the concept of sharing storage with other systems, but if there's enough cache that its transparent to me...no harm, no foul.

There are a lot of details (as it relates to Oracle databases) around storage virtualization that haven't been documented, so I wanted to dive a little deeper into it...specifically around thin-provisioned ASM. In the previous post we looked at thin-provisioned overhead on performance...so that aside, how, and when, is space allocated at the storage layer? How do you become un-thin? What can we do to prevent this and when it happens, what do you do to correct this situation?

We know storage is allocated on the USP-V as its requested in 42MB pages. Once its allocated, it stays allocated...so if you create a 10GB file and then remove it...you're still allocating 10GB. From my tests on NTFS...an OS block is created, then deleted, then created in the next free space for it. This is great for a hard drive because it spreads out the wear. Most SSD's do this internally anyway...but for thin provisioning this is terrible. NTFS with any activity won't remain thin. Oracle ASM is the opposite...it uses a space allocation algorithm that dictates that as storage is written (and allocated), then deleted (but still allocated on storage), then written again, you'll be writing to the exact space that was already allocated in whatever increments you set for your AU size. The problem can still exist that you allocate a lot of space, then remove it (like for archivelogs)...in which case you're no longer thin. The cure for this is the ASRU utility developed by Oracle and 3PAR. Its a pearl script that will shrink all the ASM disks to their minimum size, write (extremely efficiently) 0's to the remaining free space in the diskgroup, then return the ASM disks to their previous size. When this is complete, the storage administrator can click the Zero Page Reclaim button (why isn't this scriptable, HDS?!) and reclaim as free all the space that's free to the storage pool. I had a meeting at Oracle Openworld with reps from Netapp about why this process is necessary...from the ASM instance we can recognize all the AU's that are occupied and their physical addresses...so we should then know all the space that's not being used. In addition, there's an algorithm for AU allocation in Oracle...not only do we know from the ASM instance what AU's are currently being used, from the algorith we can deturmine were the next 100 AU's will be placed. The ASRU utility and the administration associated with it shouldn't be necessary. The first storage vendor to recognize that and implement it wins the database storage market...it'll save companies millions of dollars on new storage.

That being said, its all we have today, so we tested it. My test diskgroup had 350GB...I had 100% allocated on storage but only 20% utilized by the database. The ASRU utility finished in 24 minutes, ZPR took 67 minutes and reclaimed 80% of the storage. To locate the ASRU utility was painful...all the links to it were dead that I found on the internet. I opened an SR and was given a link to an internal Oracle website (which obviously didn't work for me.) Eventually Oracle Support was able to supply it to me. If you need it, let me know.

We created a 10GB datafile to see how much space was allocated. Physically on storage, there are occasional very small writes, even in empty non-sparse tablespaces. The USP-V allocates at a minimum 42MB...so in the end our 10GB empty tablespace physically allocated 7770MB. Lesson learned here...create smaller datafiles with autoextend on.

Most people recognize that a rebalance happens when you add a lun to an ASM diskgroup...to me the exact process of how it moves the AU's to the new lun is unclear...it isn't a direct read and copy...there's a lot more activity than that. My test diskgroup was about 50% full of data and I added an equal amount of space to it. What you end up with is a diskgroup that's now 25% full (according to ASM), but 100% allocated at the storage level. You can correct this with ASRU, but that's a manual process (due to the gui-only mgmt interface HDS provides.) The work around to this is to grow the luns in the USP-V and then resize the ASM disks to their new larger size. This process is officially supported by Hitachi on NTFS only. We tried it with AIX, but after the luns grew (from the USP-V's perspective), the lun sizes never changed at the OS level, so there was nothing ASM could do. Our P5 is using VIO servers which act as a middle man between the luns presented by the USP-V and our LPAR...we suspect that might have been our issue, but due to time constraints we were unable to verify this. This is a problem for thin provisioning on AIX, it makes thin provisioning very difficult for databases using ASM...at least, keeping them thin after a rebalance. We wanted to test this on Suse Linux 11 running on a Cisco UCS, but we discovered SUSE isn't bootable from the USP-V...although a patch is expected within the month.

I remember the first time I explained the concept of thin provisioning to the database tech lead at a client site. Her reaction was a mix of confusion and horror...followed by, "What happens if we try to allocate space and its not really there?" ...that was our first test.

What happens when you call for space that the OS thinks is available, but it isn't due to thin-provisioning? I set up an 11.2.0.2 GI ASM diskgroup using a 10GB lun in a 5GB storage pool. I started adding data until eventually I was trying to allocate storage that didn't exist.

1. I was told by the storage team that at least 4 emails were sent to them to warn them of the situation.
2. The database froze, and I called the storage team and had them add some space to my storage pool...which they did, and the USP-V seemed content without an issue...unfortunately, the db didn't get the memo that the crisis had passed...it was still frozen.
3. I logged in to unix and I could do things like cd and ls...but anything else would give me "IO ERROR". I was working in an OS mount that wasn't in the same storage pool I had tested on...so this surprised me. Eventually the db gave the error "ORA-01114: IO error writing block to file 4..."
5. I decided to bounce the database...but I couldn't log in and create a new session to issue a shutdown command.
6. I killed PMON at the OS level...and it died...but all the other processes were still up!
7. We then bounced the LPAR...first a "soft" boot (which didn't work), then a "hard" boot. When the server came back...several luns were missing...and we later determined, completely corrupted.

Luckily I had scheduled this test right after the "merged incremental backup" test in the previous post...because I was forced to do a complete restore/recovery of the database. After more analysis, I was told the scci driver on the frame had locked up, which affected all the storage on our testing frame. Lesson learned...never, ever let your storage pool run out of storage.

Conclusion: Thin provisioning is in its infancy...only recently has the linux foundation implemented SCCI trim extentions to allow the OS to notify the storage server of the deletes...to my knowledge no distro's have implemented this yet, although I discussed it in a meeting with Oracle VP Wim Coekaerts to be added to their new kernel. Potentially ASM and databases could work very well with it but there's a trade-off between additional maintenance to keep things thin vs storage savings. If your database isn't growing, this might be an option for you...keeping in mind the overhead seen from the performance tests in the previous posts. Its possible that a different OS or even AIX not using a VIO server would have been more successful in these tests. This isn't the USP-V's fault since it works with NTFS...but our big databases are in *nix...so for practical purposes, it didn't work. The Storage Guy has some interesting points on this topic...read about them here.

In this series:
HDS USP-V Overview for Oracle DB
HDS USP-V Performance for Oracle DB
HDS USP-V Features for Oracle DB
HDS USP-V Thin Provisioning for Oracle DB

Virtualized Storage (USP-V) for Oracle Databases-3. backup/clone

2010-12-08T11:41:00.000-08:00

Now that the performance tests are out of the way (see previous post), we can take a look at the virtualization features found in the USP-V as they relate to databases. Incidentally, I'm told the USP-V and the new VSP are virtually (pun) identical in their usage. There are a few changes and differences, such as page level auto tiering is only found in the upcoming micro-code update of the VSP, and the VSP uses more energy efficient, more dense 2.5" drives.

Two features we need from the USP-V are faster backups and faster database refreshes.

Today, clones are accomplished through an RMAN duplicate procedure I put together. Its still limited by the 10GB network (which I used to think was a lot). The requirement is to refresh Prod to Test-take 22 databases, (about 30TB) and refresh them all, keeping them in sync with each other because they have interdependencies. Although you can do an RMAN duplicate to a point in time, there's no RMAN ACTIVE duplicate to a point in time...and restoring (or RMAN duplicating) 22 databases at once would be a huge strain on backup resources. What I came up with is an RMAN active duplicate to 22 standby databases that are each using flashback database. After the RMAN "active duplicate for standby", eventually all the 22 standby databases are current and shipping logs. You stop them, flash them back to the same moment in time, and start them up. Viola...RMAN active duplicate of an entire prod environment to a point in time. :) I should blog about the details around that someday....

These databases are growing quickly (growing by factors over the next few years)...we won't be able to get this done inside our maintenance window next year. Storage virtualization to the rescue....

I ran ~30 tests, I'll just give you the highlights and lessons learned here. There's a feature in NetBackup that will interface between the storage array and RMAN. When RMAN runs a "backup proxy" command, NetBackup passes the details to the storage array, and the storage array does the work. The net effect of this feature is that multi-petabyte databases can have a backup taken in a few seconds by creating a set of pointers to the original storage and tracking deltas after that. An Oracle DBA, who doesn't have access to the Hitachi command interface, can initiate that backup from the familiar settings of Oracle's RMAN. When it comes to restores, the process is basically reversed and your MTTR is reduced to the time it takes to apply archivelogs since your last backup. There's also a feature in the USP-V that allows the storage-level equivalent to the block change tracking feature dba's commonly use in Oracle...only in the USP-V, the smallest denominator is a 42MB page instead of an 8k db block. Since the deltas are tracked after the first backup, only a small percentage of your data (the data that's changed since the last backup) needs to be backed up. The 3rd option is to keep 2 lun sets in sync.

As multiple databases at this client's site are growing to multiple petabytes, this feature holds great promise. I wanted to compare the differences between the current 2tape backups to two alternatives...Oracle's recommended backup strategy which they call merged incremental backups and Hitachi's methods described above.

The database I tested with is around 12TB. Its backed up to 4 LTO-4 drives. I established a baseline by reviewing backup times and performing a baseline restore/recovery. This is a very busy OLTP database, and the backup times for it vary widely around 14hrs...I suspect that at times the drives are attempting to write faster than the database can feed them. Eventually the cache on the tape drives runs dry and they have to stop, rewind and begin writing again where they left off. Its ironic that backing up a busy database that's barely able to keep its head above water is sometimes faster with slower tape drives than it is with fast ones. Anyway, the restore baseline finished in 8.5 hrs, the recovery took 33.25 hrs. The full backup was taken about a week prior to my restore point, so many, many GB of archivelogs needed to be applied. After speaking with the application manager, the cost of 41.75 hrs of downtime would cost the business more than the purchase price of the Hitachi...so this feature alone could justify its purchase, all other things being equal.

The first lesson learned came when I tried to add 16, 1TB luns to my ASM diskgroup. Although the published limitation is 2GB/lun for ASM, the error, ORA-15099, reported that I was adding a lun larger than ASM could handle. Doing a:

./kfod di=all

...from the grid home, I was able to see that Oracle was reporting the luns to be 4TB in size, not 1TB. I verified the correct size with AIX's bootinfo, then I created an SR. Oracle identified it as bug 10072750 and they're creating a patch for it. Hopefully it'll be ready before you encounter this issue.

The work-around is to specify the disk sizes (less than 1TB) when you create the diskgroup and add the disks...so now I have 16, 1023MB disks in 2 diskgroups, data and fra.

There were complications that prevented the Backup team from applying the NetBackup feature that allows proxy copies to the media server due to a prior issue. It would have been easier for me, but for my purposes, I just need to be able to backup a database using virtualization features. So with a little coordination with the storage team, we were able to manually interface with the storage array and get this to work. Essentially the procedure is:

1. Establish a consistency group (this will mirror 2 sets of luns)
2. Place the database in backup mode
3. Take a snap (this takes a few seconds)
4. Take the database out of backup mode

Putting the db in backup mode would increase this database's archivelog generation...currently reaching 60GB/hr. Since we manually did this we were admittedly a bit clumsier and less efficient than we could have been. I'm told by HDS experts this process, when done in RMAN, places the database in backup mode for no more than a few seconds no matter how big your database is.

Since we were manually doing this we had some options. The USP-V can do "Shadow Images" (which is a storage-layer mirror), or snaps (which are copy on write, tracking deltas). We need to also do a cloning test so we went the Shadowcopy route. To have a point-in-time capability, we would have to do snaps instead.

For the restore, we had the mirror lun set presented to our 2nd lpar on the frame. The storage team split the consistency set in a few seconds and they were done. Once I set up the init.ora/oratab settings we had restored the database to a new LPAR for the restore and since its on a different LPAR, I'm calling it a refresh/clone too. The MTTR is now zero, because there's no recovery to speak of (the database was in a current, crash-consistent state.)

Lastly, I did a test of the RMAN merged incremental backup. The basic procedure of a MIB is:
1. You turn on block change tracking
2. Do an image backup of your database (probably to cheap sata storage)
3. Schedule a "merge"...a recovery of the copies of the datafiles you just made.

With block change tracking...this is a very inexpensive process...normally taking a few minutes on TB-sized, busy databases (depending on activity patterns, of course). In the case of a corrupted datafile, you just have to "switch" (an RMAN command) to the copy, and RMAN will recover that file to be in sync with the rest of the database and start using it. While that's happening, you can restore and recover the original, then switch back. In the event that the entire database is lost, you can "switch database to copy" and the same process takes place.

I've always been a fan of this backup method, and if it weren't for the Hitachi's features, this would still be the way to go. The restore time was eliminated...but they don't say Mean Time To Restore...its Mean Time To Recovery. With block change tracking turned on, the merge took only a few minutes. When it was time for the database switch, since I recommended the merges be scheduled hourly, only 1/2 hr (avg) of archivelogs would have to be applied...compared to the 7 days of archivelogs from the baseline, that's fast to apply. From simulated crash to open, the restore/recovery of the 12TB database took a little under 5 minutes. Like the USP-V features, this option for backup scales...but it also has the advantage of being free. :)

Conclusion:
The USP-V was able to complete a restore and a clone instantly...this solution is scalable up to many, many PB...so its up to the task of keeping us in the maintenance window indefinitely, and it can do it with the simple RMAN interface a DBA likes to use without giving them storage array access. A close 2nd option is a merged incremental backup strategy...but this wouldn't help us with clones/refreshes.

During step 1, the mirror set is in read-only mode. This gave me an idea on how to cost avoid some licensing for the Golden Gate implementation...I'll have to test that out a different day.

In this series:
HDS USP-V Overview for Oracle DB
HDS USP-V Performance for Oracle DB
HDS USP-V Features for Oracle DB
HDS USP-V Thin Provisioning for Oracle DB