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

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)

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

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

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

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

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)

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.06ms6.24ms

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

Avg throughput:117MB/s137.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.654.92ms

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

Avg throughput:163MB/s171.2MB/s

0-byte IOPS=177203

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