Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.

Saturday, January 31, 2009

Scuba Diving New York City

A few months ago I wrote a blog about global warming and some possible mitigating actions we could all take (here: http://mikerault.blogspot.com/2008_04_01_archive.html) some might have been a bit off the wall, but were meant to make people think. Of course now many leading scientists are saying there is nothing we can do to radically affect global warming trends for the next 1000 years (http://www.abc.net.au/news/stories/2009/01/27/2475687.htm). The article pretty much echoed the sentiments I expressed in an earlier blog of mine in which I stated that while we as a species may have added 1 or 2 percent to the overall picture we are on a warming and CO2 curve that is following a natural cycle that seems to occur about every 100,000 years or so according to studies of ice cores from the Vostok site in Antarctica (http://www.daviesand.com/Choices/Precautionary_Planning/New_Data/). If there was a direct correlation between temperature and CO2 concentration then we would all be par boiled since we are at a level almost 100 PPM above highest historical levels and so the temperature should be 5-10 degrees above what it currently is, when in fact, we are actually cooler by up to 2 degrees than we should be according to the historical data.

Perhaps my most wild eyed suggestion was to place a solar shield between the Earth and the sun to reduce the amount of solar energy reaching the Earth. Oddly the most wild eyed suggestion seems to be the only one that would make a difference at all. Unless we find a way to reduce the amount of solar energy reaching the Earth’s surface we can expect global temperatures to increase steadily until there are no ice caps, Arctic or Antarctica.

The net effect of the melting of the melting of the Arctic ice cap would be negligible since it is in effect floating so the change in sea levels would be near zero, however, the polar bears would have a few issues. The biggest problem would be the Antarctic ice sheet which is resting on the continent of Antarctica. As it melts and adds to the water levels the weight compressing the Antarctic land mass decreases causing rebound. Between the added water level and the displacement from rebound we are talking over 100 feet of additional water levels around the world. Say good bye to New York, London, Hong Kong, Tokyo, almost all of Florida, heck, most of our seaports. You think Bangladesh has problems with flooding now, just wait.

We need to start planning and doing something now. By reducing the amount of sunlight we receive by 10% we could nip this issue in the bud before 42nd street is considered an advanced level scuba dive. This would be done by orbiting a single large sunscreen or multiple smaller sunscreens in the L1 Lagrange point. Maybe by making these sunscreens actually into thermionic generators or simple solar cell arrays we could also provide large amounts of energy which could be sent back to Earth via microwave beams for use as a green energy source(http://gltrs.grc.nasa.gov/reports/2004/TM-2004-212743.pdf). To block 10 percent of the energy of the sun that reaches the Earth sounds a bit crazy but it could be done.

Even by spreading large clouds of metallic debris (how about all those aluminum cans we see lining the roadways?) or using a few large asteroids pushed into place using low thrust ion drives (http://www.grc.nasa.gov/WWW/ion/) .

It is time to look out for all of us and put aside petty (in the scheme of global warming scale disaster) differences and pull together to really do something to fix this problem. Driving a fuel efficient car and turning your thermostat down in the winter and up in the summer may give you warm and fuzzy (or cold and fuzzy depending on the season) feeling but it won’t amount to a hill of beans when it comes to helping fix global warming.

Wednesday, January 07, 2009

Database Entomology in Oracle11g Land

For the past several months I have been working with Oracle11g, to be exact, doing TPC-H runs, tuning exercises and putting it through its paces. My platform is a 4-node, 32 CPU 64 bit Dell cluster connected to both a solid state disk (SSD) storage array set and standard 15K hard drive (HD) JBOD arrays. On this test rack I created a 300 gigabyte TPC-H test database, well, actually 600 gigabytes with dual identical TPC-H setups in the same database one on SSD and the other on HD.

As a pre-test I had created a small TPC-H (not more than 30 gigabytes) and on a single-server rig I could run the complete 22 query TPC-H set of queries in parallel query. Of course I wasn’t using high levels of partitioning and parallel query simultaneously with the small data set.
I knew it would be interesting when I got to query 9 on the 300 gigabyte dataset and on query 9 I got an ORA-00600:

ORA-00600: internal error code, arguments [kxfrGraDistNum3],[65535],[4]

When I ran an 8 stream randomized order query run I also periodically received:

ORA-12801: error signaled in parallel query server P009, instance dpe-d70:TMSTPCH3 (3)
ORA-00600: internal error code, arguments: [qks3tStrStats4], [], [], [], [], [], [], []

On query 18 once in a while but not every run. Due to not being a full customer (only having a partner CSI number) I was unable to report these as possible bugs. I did completely check out OTN and Metalink as well as Google and no one else seems to be having these issues, of course how many folk are running Oracle11g or with RAC, cross instance parallel query and heavy partitioning and sub-partitioning?

I had a quick look at the Oracle11g release notes and saw a load of bug fixes and hoped mine were covered, even though a text search didn’t show up the ORA-00600 arguments I received. So I bit the Oracle bullet and performed an upgrade.

Well, actually 2 sets of upgrades. First I upgraded my home, 32-bit (this is important later) servers and other than the usual documentation gotchas and needing to set the database as exclusive, start it, stop it and then reset it as a cluster before the dbua program would run properly, I was successful and now have an Oracle11g instance running on my home RAC setup. A quick run against the 30 GB database showing no really stellar improvements against my test setup using JBOD arrays for TPC-H and it successfully ran Query 9 against the non-partitioned, smaller 30 gigabyte data set.

Feeling pleased with the success I immediately set out the next morning to update my large test environment, my 64 bit cluster. The CRS update went smoothly, and other than some space issues (you may want to add a datafile to your SYSTEM tablespace) and some package problems (for some reason DBMS_SQLTUNE and DBMS_ADVISOR where missing) the database upgrade went fine, right up to the point of starting the instances under It seems there is just a small bug with the use of the new MEMORY_TARGET parameter and release…you can’t go above 3 gigabytes! This is why I said that the upgrade on 32 bits was important to remember, in 32 bit systems you will rarely get above a 3 gigabyte SGA size once you allow for user logins, process space and operating system memory needs. However, one of the major reasons for going to 64 bit is to have SGA sizes in Oracle greater than 4 gigabytes. Now, if you go back to using the SGA_MAX_SIZE and SGA_TARGET or the full manual specifications such as SHARED_POOL_SIZE and DB_CACHE_SIZE you can get above the 3 gigabyte setting.

Another annoying thing with 11g and the MEMORY_MAX_SIZE setting is that you cannot exceed MEMORY_MAX_SIZE with the sum of your SGA settings plus PGA_AGGREGATE_TARGET. Now for those of you with small sort sizes this isn’t really a problem and you probably won’t have any issues. However, with a TPC-H you need a large sort size so you need a large PGA_AGGREGATE_TARGET but, you quickly get into trouble with the limits on MEMORY_MAX_SIZE and large PGA_AGGREGATE_TARGETS. With my 16 gigabytes of memory per server I was only able to allocate about 8 gigabytes to Oracle (actually about 7.5 in and 3 in anything larger and I would get errors. So needless to say, I turned off the total memory management and did it the old fashioned way.

Finally, I had my instances up, about a 7 gigabyte SGA with 5 gigabytes of DB_CACHE_SIZE and 5.5 gigabytes of PGA_AGGREGATE_TARGET. Oh, did I mention, your SHARED_POOL_SIZE must be at least 600 megabytes for Oracle11g If it isn’t you will get 4030 errors on startup, I ended up with 750 megabytes worth.

So after 8 hours of upgrade time for my main set of instances I was finally ready to run a TPC-H. Guess what, with the upgrade in place, more cache and bigger sort areas, I seem to be getting worse performance than with the sub-optimal query resolving, bug-ridden version. Looks like they fed the bugs instead of killed them. Oh well, back to the tuning bench.