Thursday, December 21, 2006

The Optimizer, an update

My last post involving the optimizer was quite perplexing until Jonathan Lewis commented on it. Indeed, the check constraint was the problem in this particular case.

I was so frustrated, I went down the path of upgrading to the latest version since I knew Oracle wouldn't be submitting a bug on 9.2.0.5. I even changed platform just for kicks:


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.2.0 - Production
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

Alas, I still had the problem before Jonathan suggested the constraints.

SQL> select count(*) from activity_rtab where date_matched = '31-dec-4712';

COUNT(*)
----------
370319

Elapsed: 00:00:00.27
SQL> select count(*) from activity_rtab where date_matched = '31-dec-4712';

COUNT(*)
----------
370319

Elapsed: 00:00:00.27

If I had only chased down the filter predicate using the new dbms_xplan.display method, I might have had some more clues:

SQL> explain plan for select count(*) from activity_rtab where date_matched = '31-dec-4712';

Explained.

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------
Plan hash value: 3790338984

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 269 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| IX_ACTIVITY_DATE_MATCHED | 3801 | 30408 | 269 (3)| 00:00:04 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
2 - access("DATE_MATCHED"=TO_DATE('4712-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
filter(TRUNC(INTERNAL_FUNCTION("DATE_MATCHED"))=TO_DATE('4712-12-31 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))

16 rows selected.

I dropped the constraint and sure enough, the performance problem went away:

SQL> select count(*) from activity_rtab where date_matched = '31-dec-4712';

COUNT(*)
----------
370319

Elapsed: 00:00:00.06
SQL> explain plan for select count(*) from activity_rtab where date_matched = '31-dec-4712';

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3790338984

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 264 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| IX_ACTIVITY_DATE_MATCHED | 380K| 2969K| 264 (1)| 00:00:04 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
2 - access("DATE_MATCHED"=TO_DATE('4712-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

14 rows selected.

Now we have to decide; use the constraint to ensure data integrity or suffer the performance penalty for the sake of the data. A classic trade-off.

Friday, December 15, 2006

What is the optimizer doing?

Interesting problem here. I have a table in one of my Oracle 9.2.0.5 databases on Solaris called ACTIVITY_RTAB:

SQL> desc activity_rtab;
Name Null? Type
----------------- -------- --------------
ID NOT NULL NUMBER(19)
FLAG1 NOT NULL CHAR(1)
MONEY_AMOUNT NOT NULL MONEYAMOUNT_T
DATE1 NOT NULL DATE
DATE2 NOT NULL DATE
DATE3 NOT NULL DATE
DATE_MATCHED NOT NULL DATE
FLAG2 NOT NULL CHAR(1)
STR1 NOT NULL VARCHAR2(100)
FLAG3 NOT NULL CHAR(1)
ID2 NOT NULL NUMBER(19)
STR2 NOT NULL VARCHAR2(10)
ID3 NOT NULL NUMBER(19)
FLAG4 NOT NULL CHAR(1)
DATE4 NOT NULL DATE
DATE5 NOT NULL DATE
ID4 NOT NULL NUMBER(38)
DATE6 NOT NULL DATE

This table has two indicies; a Primary Key on ID, and a NON-UNIQUE index on DATE_MATCHED. The data in DATE_MATCHED is pretty skewed. I have about 10K rows per day in this table, except for the date '31-DEC-4712', which I have about 355K rows.

select
before_on,
avg(rows_per_day) avg_rows_per_day
from (
select
case
when date_matched < '31-dec-4712' then 'before'
else 'on'
end before_on,
trunc(date_matched) dm,
count(*) rows_per_day
from activity_rtab
group by
case
when date_matched < '31-dec-4712' then 'before'
else 'on'
end,
trunc(date_matched)
)
group by before_on
/

BEFORE AVG_ROWS_PER_DAY
------ -----------------
before 9413.75
on 355606.00

Queries using the index on DATE_MATCHED index are somewhat quick for any day other than '31-DEC-4712'. This behaviour doesn't really surprise me since I'll be fetching 355K rows basically a single block at a time.

select
count(*)
from activity_rtab
where date_matched = '31-DEC-4712';

COUNT(*)
----------
355606

Elapsed: 00:00:03.48

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IX_ACTIVITY_DATE_MATCHED' (NON-UNIQUE) (Cost=29 Card=99 Bytes=792)
Statistics
----------------------------------------------------------
56 recursive calls
0 db block gets
1031 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

I know what you're thinking, "OK, so what's your problem?". In order to do some maintenance, I did a CTAS and created a new table in the same tablespace. I put both indicies on the new table and ran a quick query.

select count(*) from jh$activity_rtab where date_matched = '31-DEC-4712';

COUNT(*)
----------
355606

Elapsed: 00:00:00.53

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'JH$DATE_MATCHED' (NON-UNIQUE) (Cost=29 Card=9867 Bytes=78936)

Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
949 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


Huh? Why is this returning in less than a second where the query on the real table takes 3? At first, I thought something must be wrong with the storage so I moved the table and rebuilt the indexes. Same thing.

Then I looked closer at the plan and noticed the query on JH$ACTIVITY_RTAB had "Card=9867" and the query against ACTIVITY_RTAB had "Card=99". Aha! A statistics problem. I calculated statistics on both tables using the following commands:

exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'activity_rtab',cascade=>true);


No dice. The query on JH$ACTIVITY_RTAB was still significantly faster than ACTIVITY_RTAB. Then I decided to take my own advice and ran a 10046 trace on both queries. The TKPROF confirmed my observations, but still offered no clues as to what the real problem was.

select count(*)
from
jh$activity_rtab where date_matched = '31-DEC-4712'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.01 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 6 0.92 0.91 0 2838 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.92 0.92 0 2838 0 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
355606 INDEX RANGE SCAN JH$DATE_MATCHED (object id 43980)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 6 0.00 0.00
********************************************************************************

select count(*)
from
ACTIVITY_RTAB where date_matched = '31-DEC-4712'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 6.05 6.22 0 2030 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 6.06 6.23 0 2030 0 2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 42

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
355606 INDEX RANGE SCAN IX_ACTIVITY_DATE_MATCHED (object id 37979)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 0.00 0.00
********************************************************************************

At this point, I had a hunch that it was an optimizer problem but couldn't convince myself. I couldn't get around the fact that both tables had nearly exact characteristics but the queries performed radically different. Time for a 10053 trace on both tables:

Table stats Table: JH$ACTIVITY_RTAB Alias: JH$ACTIVITY_RTAB
TOTAL :: CDN: 7538301 NBLKS: 121877 AVG_ROW_LEN: 109
-- Index stats
INDEX NAME: JH$DATE_MATCHED COL#: 9
TOTAL :: LVLS: 2 #LB: 19996 #DK: 764 LB/K: 26 DB/K: 253 CLUF: 194025
INDEX NAME: JH$PK COL#: 1
TOTAL :: LVLS: 2 #LB: 16754 #DK: 7538301 LB/K: 1 DB/K: 1 CLUF: 908531
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
Column: DATE_MATCH Col#: 9 Table: JH$ACTIVITY_RTAB Alias: JH$ACTIVITY_RTAB
NDV: 764 NULLS: 0 DENS: 1.3089e-03 LO: 2453031 HI: 3442447
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: JH$ACTIVITY_RTAB ORIG CDN: 7538301 ROUNDED CDN: 9867 CMPTD CDN: 9867
Access path: tsc Resc: 11723 Resp: 11723
Access path: index (iff)
Index: JH$DATE_MATCHED
TABLE: JH$ACTIVITY_RTAB
RSC_CPU: 0 RSC_IO: 1925
IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00
Access path: iff Resc: 1925 Resp: 1925
Access path: index (equal)
Index: JH$DATE_MATCHED
TABLE: JH$ACTIVITY_RTAB
RSC_CPU: 0 RSC_IO: 29
IX_SEL: 0.0000e+00 TB_SEL: 1.3089e-03
BEST_CST: 8.00 PATH: 4 Degree: 1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: JH$ACTIVITY_RTAB[JH$ACTIVITY_RTAB]#0
Best so far: TABLE#: 0 CST: 8 CDN: 9867 BYTES: 78936
Final - All Rows Plan:
JOIN ORDER: 1
CST: 8 CDN: 9867 RSC: 7 RSP: 7 BYTES: 78936
IO-RSC: 7 IO-RSP: 7 CPU-RSC: 1 CPU-RSP: 1
PLAN
Cost of plan: 8
Operation...........Object name.....Options.........Id...Pid..
SELECT STATEMENT 0
SORT AGGREGATE 1
INDEX JH$DATE_MATCHED RANGE SCAN 2 1
BASE STATISTICAL INFORMATION
***********************
Table stats Table: ACTIVITY_RTAB Alias: ACTIVITY_RTAB
TOTAL :: CDN: 7538301 NBLKS: 121875 AVG_ROW_LEN: 109
-- Index stats
INDEX NAME: ACTIVITY_PK COL#: 1
TOTAL :: LVLS: 2 #LB: 16748 #DK: 7538301 LB/K: 1 DB/K: 1 CLUF: 908561
INDEX NAME: IX_ACTIVITY_DATE_MATCHED COL#: 9
TOTAL :: LVLS: 2 #LB: 20112 #DK: 764 LB/K: 26 DB/K: 253 CLUF: 194021
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
Column: DATE_MATCH Col#: 9 Table: ACTIVITY_RTAB Alias: ACTIVITY_RTAB
NDV: 764 NULLS: 0 DENS: 1.3089e-03 LO: 2453031 HI: 3442447
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: ACTIVITY_RTAB ORIG CDN: 7538301 ROUNDED CDN: 99 CMPTD CDN: 99
Access path: tsc Resc: 11723 Resp: 11723
Access path: index (iff)
Index: IX_ACTIVITY_DATE_MATCHED
TABLE: ACTIVITY_RTAB
RSC_CPU: 0 RSC_IO: 1936
IX_SEL: 0.0000e+00 TB_SEL: 1.0000e+00
Access path: iff Resc: 1936 Resp: 1936
Access path: index (equal)
Index: IX_ACTIVITY_DATE_MATCHED
TABLE: ACTIVITY_RTAB
RSC_CPU: 0 RSC_IO: 29
IX_SEL: 0.0000e+00 TB_SEL: 1.3089e-03
BEST_CST: 8.00 PATH: 4 Degree: 1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: ACTIVITY_RTAB[ACTIVITY_RTAB]#0
Best so far: TABLE#: 0 CST: 8 CDN: 99 BYTES: 792
Final - All Rows Plan:
JOIN ORDER: 1
CST: 8 CDN: 99 RSC: 7 RSP: 7 BYTES: 792
IO-RSC: 7 IO-RSP: 7 CPU-RSC: 1 CPU-RSP: 1

The queries are basically the same except for that darn Cardinality (CDN). I pulled out Jonathan Lewis' latest book and went through the calculations but still don't see how the CDN of 99 is being computed. I played around with histograms and different bucket sizes, but always came back to the simple premise of "If performance is fine with JH$ACTIVITY_RTAB, why does it suck with ACTIVITY_RTAB"?

I just don't understand it. For kicks, I tried the query with a +RULE hint and it returned in .48 seconds.

SQL> select /*+ rule */ count(*) from ACTIVITY_RTAB where date_matched = '31-DEC-4712';

COUNT(*)
----------
355606

Elapsed: 00:00:00.48

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IX_ACTIVITY_DATE_MATCHED' (NON-UNIQUE)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1015 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

I'm really perplexed at this point. Sure, I have two workarounds; use the +RULE hint or drop the table and recreate it. I'm not a big fan of hinting since hinted queries seem to be the first queries that "go bad" when you upgrade. Dropping the table seems reasonable, but since I don't know how I got into this situation, I'm not sure how to detect it. Sort of like choosing between a root canal and a colonoscopy.

An update is here.

Thursday, December 14, 2006

NYOUG Winter

Just got back from the New York Oracle Users Group Winter meeting. Although I've only been involved with the NYOUG for about two years, I was saddened to see Dr. Paul Dorsey step down as President. He's been a driving force for getting top quality speakers to the quarterly meetings and really supports the user community. I'm sure Michael Olin will take the reigns from Dr. D and keep the top quality speakers coming.


World renowned Michael Abbey presented the keynote address. His presentation took a light hearted look on the soft skills needed to cope with the various personalities encountered in the IT arena. I had to chuckle about his characterization of a sysadmin; hair as messy as his office, the biggest screen in the whole shop, and an aire of secrecy surrounding this person. I kept picturing the sysadmin I had right out of college and his description was right on. Perhaps the best management advice I got out of this presentation was "delegate and oversee". Michael explained that one person can't do and know everything and you have to trust (but verify) the work your people do. I struggle daily with knowing when to hand things off to other people and when to handle them myself.


The second presentation of the day was for 11g PL/SQL features by Tom Kyte. There's a lot of new features coming for PL/SQL in 11g (expected really late next year) that will help optimize the code as well as make it easier to write code. It sounds like the most helpful feature in my environment will be that dependency tracking is now at the element level instead of the object level. For example, just adding a column to a table won't validate the packages that depend on it. If you drop a field, then sure, everything referencing it will need to be recompiled. There are also other optimizations like the result cache (just-in-time materialized views) and better native compilation, but it sounds like you shouldn't be expecting your applications to speed up more than 5-10%. One really nice feature is the ability to mark just a table as readonly instead of having to move it to a readonly tablespace. As always, Tom was the entertaining speaker keeping that kept the audience participation at a very high level.


All the presenters then got together just before lunch to have an "Ask the Experts" panel; or as I like to call it "Ask Tom Live". Tom is a top notch presenter and world-class information source, but I kind of felt bad for the other presenters who were basically left out.


After lunch, I stole some power to recharge the laptop and bumped into Dave Anderson from SkillBuilders who was also powering up. We swapped laptop stories and seems neither one of us are really happy with our current capacity. As Tim Allen says, "More Power!"


Come to find out, Dave was the next presenter with an introduction to Automatic Storage Management. ASM is a 10g specific piece of software that essentially takes the place of a volume manager if you have JBOD (just a bunch of disks). You present the raw devices for those disks to ASM as a diskgroup and Oracle stripes the data over the physical disks in the disk group. For those of you on servers with direct attached storage and limited budgets, I would think ASM would be a good option rather than chunking down money for VxVM and VxFS just to have some RAID. On the other hand, I'm not sure how practical it is if you're in a SAN environment where the striping and mirroring is handled at the OS level. Also, Dave mentioned that there was no performance penalty of ASM striping over a hardware stripe. However, since ASM is a piece of software, you're basically putting a software based RAID over a hardware based RAID robbing my host of CPU that may be neede for user processes. Even so, ASM is obviously here to stay and I'll be investigating the technology and it's use as I get closer to 10g.


All in all, a great education value for the membership you pay. IF you're in the Tri-State area, it makes sense to join even if you only get to the September conference. Lets just say there are some educational events coming in may where the COST will be BASED on your membership being OPTIMIZEd. (OK, so maybe not so subtle, Jonathan Lewis is coming in May).


Powered by Zoundry

Sunday, December 10, 2006

The Failure was a success



We periodically excersize various Disaster Recovery scenarios on our mission critical systems. The most recent test was scheduled for Saturday, and most things came off without a hitch. As always, we learned a couple things and have some work to do before the next test, but overall, it went pretty good. Checkout my setup for Saturday. Three screens on two computers logged into the Disaster Recovery site, and the laptop logged into my production environment fighting another fire. I love the thrill of a crisis.

Switched to Blogger Beta

Switched the blog to Blogger Beta. Let's see how much transfers over correctly.

Wednesday, November 29, 2006

Where am I deploying MySQL, revisited, revisited...

Seems like I'm doing a lot of revisting these days. Perhaps I'm just not making myself clear.

I have basically five types of systems in my organization; mission critical, 24x7, ancillary, QA, and Development. Below are some detail explanations of each type of system:

Mission Critical
A mission critical application is a piece of software that must be running in order for the business to continue to operate. Without the software, the business would cease to exist. An example of this is eBay's auction system or the software that executes orders for Ameritrade.

24x7
A 24x7 system is important to the business, but not essential for the business to run. If a 24x7 system goes down, customers get upset but business continues to operate. An example of this type of system would be Ameritrade's charts or eBay's feedback system.

Ancillary Systems
An ancillary system is a system that a limited number of users access. If an ancillary system is down your bean counters may complain because they can't crunch their numbers, but overall, there is no impact to the business. Usually an ancillary system is something used internally and not exposed to your customer. An example of this type of system might be eBay's customer data warehouse or Ameritrade's accounting system.

QA
A QA system is "almost production" in the sense that it carries a full load of production data, but the data may be "stale". Performance testing occurs on these types of systems as well as new software changes. The only people that are affected by QA outages are those running the tests.

Development
A development system is a work in progress. A handful of developers will be ticked off if development is down, but nobody really cares.

Mission Critical, 24x7, and Ancillary systems are production systems and are treated as such. Any production system is important to the business, just some are more important than others. QA systems are sometimes treated like production, sometimes not. Development systems are almost never treated like production.

In "Where am I deploying MySQL", I point out what types of systems where I am comfortable deploying MySQL. I have deployed several systems in development, QA, and production. My production apps are mostly of ancillary type, but I have recently deployed a 24x7 web based application. As we build the infrastructure around MySQL, there will be a natural progression towards more 24x7 apps. Those ancillary and 24x7 apps eventually evolve into mission critical systems as our experience becomes deeper and our confidence becomes stronger.

I'm an Oracle guy, no question about it. But I'm implementing MySQL. In fact, the vast majority of my new systems are being developed on MySQL and are going into production on MySQL.

From time to time we have to emerge from our comfort zones. We can either confront it as an opportunity to learn and progress or fear it and fail.

Tuesday, November 28, 2006

Why I still use pfiles, revisited...

Ivan Kartik has an informative guide on SPFILES and their use at http://ivan.kartik.sk/index.php?show_article=22. I hope he posts a followup explaining what advantages they have over PFILEs and some real-world scenarios. Perhaps he can persuade me to change my mind...

Monday, November 27, 2006

Where am I deploying MySQL, revisited...

The internet is a great thing. You can find almost anything and share your ideas with anybody that is willing to listen. Unfortunately, that means that anybody with an pugnacious personality and a keyboard also has a voice.

A couple weeks ago, somebody asked me where I deploy MySQL. I created this post about where I think MySQL's sweet spots are in my organization.

I'm no MySQL expert, I'll admit it. In fact, right at the top of the post I indicate as such:
In my opinion, I'd use MySQL for anything but the most mission critical applications. I'm not saying MySQL can't handle the most mission critical applications, but I'm not comfortable betting my business on MySQL at this point.
Sheeri Kritzer decides to put her own disparaging slant on my post and writes MySQL Is a Great Database, But Do Not Use It???? I posted a comment on the blog clarifying my position on a couple points, but wanted to expound on it a little more here.

First off, I didn't say nor did I imply not to use MySQL. I didn't say anything of the sort. I simply explained where I use MySQL and what kind of applications I use MySQL for. Nowhere in the post did I say MySQL is only good for XYZ type application in every organization.

She also goes on to pick apart this paragraph:
Last, but not least, MySQL is suited for data marts ( less than 1TB). Stuffing lots of historical data into denormalized relational tables is what "LOAD DATA LOCAL" is all about. These types of applications aren't needed 24x7 but require snappy response times when queried.

with
In the third sweet spot, you say MySQL is good at stuffing lots of data into denormalized tables. I’ve been using MySQL for quite a long time, and I missed the part where stuffing lots of data into normalized tables is slow. It’s fast for stuffing lots of data into tables, period.
Um, I guess if I stood on my head with one eye closed my paragraph says "MySQL sucks at stuffing lots of data into normalized tables."

Next, we get into a common misconception:
And MySQL isn’t “just as fast as the next guy”. It’s FASTER, otherwise why would people use it, because it’s much less featured than Oracle? This I’ll admit — however, you can code around features. No amount of code can make Oracle faster.

Where, oh where, do I start? I know I use MySQL because it's cheaper. I wouldn't make a blanket statement saying it's FASTER. Sometimes it's faster, sometimes not. All I have to do is come up with one example of where MySQL is slower and the blanket statement is false.

People that have been in this business a little more than a couple years know lots of examples where people think they can code around features. And no amount of code can make Oracle faster? If you treat it like a big black box, then you're probably right. But treat it like a sophisticated piece of software that has it's on intricacies, then yes, code matters.

Oh, and:
To Jeff Hunter’s comment I say: Incremental backups exist — that’s what binary logs are.
...has nothing to do with MySQL, it's a reply to the previous comment about what's missing in Oracle SE vs. EE.

And I purposely didn't make this an Oracle/MySQL comparison. Primarily because I can't say with authority how MySQL behaves. I could bring up the fact that you have to bring down the db just to add a datafile to the InnoDB tablespace, but I'm not 100% sure you can't do that online. I could also bring up the fact that you have to shutdown the database to change the size of your logfiles, but again, I'm not 100% sure that's the only way to do it.

Apparently MySQL is her gig and she's a zealot for the software. That's cool, more power to ya. But don't belittle me because that's not the only thing I use. And if you're going to criticize me, at least have the courtesy of alerting me to the fact that you have done so.

Tuesday, November 21, 2006

Selling out


Danasoft has a .jpg generator that echo's your IP address and does a IP lookup to figure out your ISP. You could also put up to ten sayings on your .jpg and they would randomly rotate on the graphic. Up until a couple days ago, I thought it was kinda cool, so I included it in my signature on several forums.

A couple days ago, I noticed that my signature had an advertisement in it. I thought that maybe my signature had been hacked and I was just going to create another one. But then I saw on the Danasoft website that you could create a .jpg without advertisements by clicking a certain link.

Granted, everytime my .jpg is displayed, I'm using some of their bandwidth. But the part that really miffs me is they dropped these advertisements in without notifying anybody. Needless to say, I ripped the graphic from all the forums in which I participate. That's 3000 less views they'll be getting every day...

Tuesday, November 14, 2006

Why I still use pfiles

I'm always up for investigating features of Oracle and deciding when and how they should be introduced into my environment. I recently got a chance to go back and look at spfiles again.

Server Parameter Files (spfiles) are binary files that contain your initialization paramters. The two major advantages to using spfiles over standard text parameter files are that the spfiles let your initialization parameters persist across instance restarts and they can be backed up by RMAN. How many people have changed a parameter on-the-fly only for it to be reset by the init.ora upon restart? All your init.ora problems could now be solved, just by using spfiles.

But I don't use them.

The first reason is maybe I don't want the instance to keep a value when it gets restarted. For example, maybe I changed my log_archive_dest_1 to a temporary location because my primary location started filling up. I don't want that to be my permanent location, just until I get a chance to backup my archived redo logs. Sure, I'll change it back when my backup changes, but if I forget, I haven't made an unintentional permanent change.

Also, I dig change control. Nothing goes to production until it's checked into my version control system. This includes init.ora files, tnsnames.ora files, listener.ora, scripts, and anything else. If you want to know what the db_cache_size was back in 2002, I can tell you. Sure, I could dump my spfile to a pfile when I make a change and put it into version control, but that goes against the process. Things go into version control before they go into production.

Along those same lines, version control allows me to back out a change that won't allow me to start the instance. For example, say I set the shared_pool_size to 400G on a box with 4G of RAM. When my instance doesn't start, I can check the old version of the init.ora out of my version control, replace it, and start the instance. If I were using an spfile I'd have to create a pfile that specified my spfile and then add an entry that reduced the shared_pool_size back down. And that's assuming I knew what the problem was. With version control, I just diff the two files and I know what the change was.

Another reason I like pfiles is I can include a "standard" pfile so all my instances run with similar parameters. For example, maybe I want to make sure all my instances run 4 job queue procesess. I just put the appropriate parameter in the my initStandard.ora, include the initStandard.ora in my init.ora file using ifile= and I'm good to go. I know that on the next restart my environments will be the same.

The last advantage I think standard pfiles give me is the ability to distribute production changes to my DR site. I have two init.ora files at my DR site; one that holds the parameters for recovery and one that holds the parameters for running during a failover. When I make a change to my primary init.ora, I push that change to both the primary site and the standby site (using automated methods). When I have to failover, I know the standby will operate just like my primary site did.

I also don't care about the backup. Whoa, I mean I don't care about backing up the initialization parameters because I already have them in my version control.

I know this goes against conventional wisdom in the Oracle world. I'm a firm believer in doing what makes sense for my particular environment regardless if "everybody" thinks its OK or not. When I start running RAC, maybe it will make more sense for this environment, but until then I'm still using pfiles.

Thursday, November 09, 2006

Keeping you in the loop

The email subscription form we introduced at Wilton Diaries a couple weeks ago has really taken off. In other words, we've got three subscribers, but the "cool" factor is way up there.

When you subscribe via email, you will get a message when the Blog gets updated. To subscribe, simply enter your email address and click the "Subscribe" button. A form will then pop-up asking you to verify that you are an acutal person and not an aggregator. You simply type the letters that show in the box and click the buttons you are instructed to. You will then be sent an email message with a URL that you must click on to finish your subscription. This last part is important as you don't want to go through all that work and not get the feed!

If that all sounds too complicated, feel free to continue checking the So What Co-operative every day.

Wednesday, November 08, 2006

Playing in the Sand

Just got back from a week in Kuwait upgrading our database down there from 9i to 10g and upgrading our ESRI from 9.0 to 9.1.

I must say upgrading Oracle9i to 10g on a Sun Solaris 10 OS is the most easiest and painless install I’ve ever done. I found out on our Dev box that trying to put 10g on Solaris 8 was like trying to put a square peg in a round hole. Luckily, I got our UNIX admin on board with upgrading all of our servers to Solaris 10 before I started my production upgrade festivities.

When I got down there we had one database serving up maps and vehicle tracking data. All the tracking data is OLTP oriented and the maps are nothing but a bunch of blobs. The user has the ability to see maps by themselves and vehicle information (text) by itself. The user also has the ability to see maps and the vehicle data at the same time.

The server has plenty of horsepower and space so I decided to break the database into two. I created another database and put the maps on it. I configured it for bulk stuff .One other thing I did was, we have a particular map that automatically loads up when a user first accesses the webpage, I threw that in to the keep pool. Performance is very nice. It’s so refreshing when you have a database configured correctly for the environment it supports.

Tuesday, November 07, 2006

Where am I deploying MySQL?

If cost were no object, I'd always deploy Oracle. I'm comfortable with Oracle technology and I think I have a pretty good idea how to implement and administer it.

In the world of corporate IT, however, budgets are king. Projects are measured by their Return on Investment (ROI) and the lower I can get that investment, the better return I can get for my investment. I have a real hard time spending $160K on an application that will occupy 40G of space.

In my opinion, I'd use MySQL for anything but the most mission critical applications. I'm not saying MySQL can't handle the most mission critical applications, but I'm not comfortable betting my business on MySQL at this point.

I think there are about three sweet spots for MySQL. The first is small to medium size OLTP databases (<100 GB) that are fronted by something like a java middle-tier. These applications typically control most of the business logic and authentication/authorization in the middle-tier (right or wrong) and use the database as a big storage bucket. These applications rely on the backend serving data as fast as it can and MySQL can serve data just as fast as the next guy.

Another area where MySQL excels in serving database driven content directly on the webserver. This type of application typically cranks out high numbers of queries and has very little updates to worry about.

Last, but not least, MySQL is suited for data marts ( < 1TB). Stuffing lots of historical data into denormalized relational tables is what "LOAD DATA LOCAL" is all about. These types of applications aren't needed 24x7 but require snappy response times when queried.

No, MySQL doesn't have some of the features that some of the big-box databases have. And it's got plenty of limitations. But when you want an 80% solution, I think it's the right choice. My company is sold on MySQL and as our confidence grows in the software, so will our installed base.

Monday, November 06, 2006

Quick and Dirty MySQL Backup

Until recently, the MySQL databases I work with contain data that can be retrieved from other sources. Most of the data is either batched in from flat files or another database. It would be inconvenient to reload a couple months worth of data, but since these databases are not mission critical, the business could operate without them for a couple days. Lately, we've been implementing some semi-critical systems that rely on a somewhat expedient recovery.

The requirements for the project were that the database must remain up during the backup and losing a day's worth of data was acceptable. All of my regular Oracle readers are cringing at the moment, but hey, that was the rules I was working with.

My first thought was to use mysqlhotcopy because it backed up the actual physical files. However, mysqlhotcopy only allows you to backup MyISAM tables and we extensively use InnoDB.

My next choice was mysqldump. mysqldump basically takes the entire database and dumps a text file containing DDL and DML that will re-create your database. Coming from an Oracle background, I knew there were shortcomings to dumping the entire database, but hopefully I could mitigate them.

The first hurdle was security. I specifically turn off unauthenticated root access on my databases, but I needed to be able to read all the tables to do a backup. I don't want to hard-code my root password or any password in a script as I don't have suicidal tendencies (diagnosed, anyway). So I created a user called backup that could only login from the server machine, but could login unauthenticated.

The next thing I had to figure out was how to get a consistent view of the data. I knew that my developers preferred InnoDB for it's Referential Integrity features and getting inconsistent data would be disasterous. Fortunately, one of the flags to mysql_dump is the --single-transaction which essentially takes a snapshot in time.

So I wrote a script around mysql_dump and --single-transaction and dumped my entire database to disk. Every now and again, I encountered an "Error 2013: Lost connection to MySQL server during query when dumping table `XYZ` at row: 12345". The row number changed each time, so I figured it had something to do with either activity in the database or memory. I could rerun the command and it usually finished the second or third time.

After the third day straight of my backup failing, I decided to research it a little more. mysql_dump has a flag called --quick which bypasses the cache and writes directly to disk. I put this flag in my backup script and the script started finishing more consistently.

The last hurdle was having enough space on disk to store my backups. Since the backup file is really a text file, I decided to pipe the output through gzip to reduce it's size.

Currently, my quick and dirty backup script is a wrapper around the following command:

mysqldump --all-databases --quick --single-transaction -u backup | gzip > mybackup.sql.gz

We're adopting MySQL at a blistering pace, so I'm sure I'll need to make changes in the future. For right now, though, it gets the job done.

Wednesday, November 01, 2006

Check this out

I usually employ a logon trigger for most of my Oracle databases so I can grab certain identifying information about the session. Then I save this information in another table for later analysis.

I have started testing 9iR2 on a 64-bit Linux box and have come across a certain peculiarity. v$session is defined as:

SQL> desc v$session
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(4)
SID NUMBER
...

I then create a table using the same type and try to insert a value:

SQL> create table jh1 (saddr raw(4));

Table created.

SQL> desc jh1
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(4)

SQL> insert into jh1 select saddr from v$session;
insert into jh1 select saddr from v$session
*
ERROR at line 1:
ORA-01401: inserted value too large for column

Hmmmf. So I do a CTAS:

SQL> drop table jh1;

Table dropped.

SQL> create table jh1 as select saddr from v$session;

Table created.

SQL> desc jh1
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)

...and look what size the column is!

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for Linux: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

Update: 2006/11/01 16:09:
From Support:
I checked my Windows (32bit) database and v$session.saddr is a RAW(4).

OK, that explains it.

Tuesday, October 31, 2006

The OS wars heat up

You may remember we talked about Oracle's Unbreakable Linux the other day.

Anybody try to get on Metalink yesterday? The Register is reporting on the poor response time yesterday. Maybe Linux will kill Oracle...

Here's an interesting take from Dave Dargo...

Sunday, October 29, 2006

Firefox 2.0


I downloaded Firefox 2.0 this weekend to see what it was all about. I thought a couple pages of my regular sites loaded slowly, but that could just be my internet connection. Once of the first features I noticed was it's ability to detect a fraudulent website.

I received an email from a suspected ebay spoof. Sometimes I just click on the links to see how close they are to the real website. To my surprise, this message came up from Firefox:

Thursday, October 26, 2006

Will Oracle Kill Linux?

That's right, not will Oracle Kill Red Hat, but will Oracle Kill Linux?

There seems to be some buzz that Oracle's Unbreakable Linux is positioning itself against Red Hat's Linux. Oracle will be offering support on a version of Linux that they have basically ripped off from Red Hat.

There's no doubt in my mind that Oracle won't kill Red Hat. Linux is used for more than just running Oracle software. I should switch my whole enterprise of umpteen hundred Red Hat computers so I can run 20 Oracle servers more efficiently? I don't think so. Can you see a SysAdmin calling in to Oracle for support and having to wait 6 days to talk to Sandeep in some far reaching corner of the globe? Can't see it myself. Besides, Oracle needs Red Hat to continue to develop the platform so they can rip it off again.

The big question in my mind is will Oracle kill Linux? To successfully deploy Unbreakable Linux, Oracle is going to have to snuggle up to the hardware vendors in order to get Unbreakable Linux pushed out on their hardware. I would imagine that is going to tick off some of the proprietary vendors.

Or better yet, will Linux kill Oracle? Will Oracle get so distracted from it's core business of infrastructure software that the products go downhill further?

Only time will answer these questions. Of course, I don't have a yacht and a billion dollars, so what do I know?

At this point, I'd just be happy with a filesystem that doesn't reboot my box every 5 days.

Tuesday, October 24, 2006

I can see you!


My apologies for not posting anything for awhile but I’ve had some personal issues that I needed to take care of . So….. enough of that I’m back now and hope to contribute on a regular basis.

Let me seeee… We left off with the wonderful world of Spatial and me ranting about ESRI. I want to continue with the Spatial stuff but I wanted to share with you guys a more intriguing (at least in my opinion) turn of events. Even though this is totally out of my job description, in my shop we have to do what it takes to get the job done even if that means doing *shutter* development work.

We recently had a complaint from one of our customers that in certain parts of a country that his equipment couldn’t send or receive a satellite signal due to terrain obstacles and he would like to be able to see on the webmap those areas that are “blacked out” so he can avoid them. Well, since we’re all about keeping the customer happy I was tasked to “Make it happen”.

The first step in the process is getting the elevation data of the desired location, once you have the elevation, you find out which satellite your equipment is using. Once you know the satellite, you find out what the Lat/Log and height (location and distance above the earth) of it is.

When we have all that information we start piecing the puzzle together. We have to create physical objects in the database like a shape file (our satellite) and a Raster image ( our map) and input all the data that we gathered up (elevation, lat/log, height) once all the values are given we use a tool to calculate the black out areas. The example above, I’ve used an observation tower as an example. The red areas are not visible from the observers view but the green areas are. Get it?


This website gives you in real time all satellite names, and locations using a nice little java thingy. http://science.nasa.gov/RealTime/JTrack/3D/JTrack3D.html

Thursday, October 19, 2006

Taking the plunge

Within the last six months, I've gotten my enterprise off Oracle 8i and on to 9iR2. We were really behind the 8 ball (no pun intended) since the support window for 8i was quickly running out. We forged ahead and were able to get everything to 9i. Along the way, we upgraded some development systems to 10gR1 and put out a non-critical system in 10gR2. However, something was missing. We hadn't taken advantage of a great deal of the 9i features that didn't come straight out of the box (Optimizer enhancements, PL/SQL fixes, bug fixes, etc).

I didn't want the upgrade to 10gR2 to go the same way. I don't want a hard-and-fast deadline for 10g and I want to be able to take advantage of some of the bells and whistles that come with 10g to ease my management burden. My educational jumping off point is to be certified in 10g.

I'm a firm believer in certification for enhancing the individual's self-worth. That doesn't mean every certified DBA is worth something to a company, nor does it mean somebody certified is worth more than someone who is not certified. It simply means that I view the certification process a valid educational opportunity for the individual to benchmark his knowlege against a standard.

Now, it's not the full-blown-from-the-top certification path that new DBAs are going on. I'm certified in 7, 8, and 8i, so I'll be looking to upgrade from 8i to 10g through 1Z0-045. I know, kind of a cop-out, but I think this will give me an opportunity to try out some of the new stuff before I need it. I bought the book last week, so now I have to start studying.

Don't be surprised if you see interesting (to me, anyway) 10g thingies on the blog in the next few months.

Monday, October 16, 2006

Steve Jobs is a true marketing genius

Newsweek:
Microsoft has announced its new iPod competitor, Zune. It says that this device is all about building communities. Are you worried?

Steve Jobs:
In a word, no. I've seen the demonstrations on the Internet about how you can find another person using a Zune and give them a song they can play three times. It takes forever. By the time you've gone through all that, the girl's got up and left! You're much better off to take one of your earbuds out and put it in her ear. Then you're connected with about two feet of headphone cable.

Me:
Steve Jobs is widely known in the business for his "reality distortion field", but sometimes you've just got to sit back and appreciate the art form he makes of it.

Who else could get asked about a feature a competing product is coming out with that his product doesn't have, and spin it so well. That feature isn't all it's cracked up to be, and besides you have a better shot at getting the girl without it.

There's marketing spin, but this is art.

Friday, October 13, 2006

Apex Rocks

Two days ago, I knew very little about Apex (HTMLDB). I knew how to set it up and install it on the database side, but as far as developing something with it, forget it. There are other people using HTMLDB at my company and they have been getting good results with it. Since I didn't know that much about it, I kind of let them have free reign over things.

I am still supporting some reports that I did as a favor for a user a couple years ago. I don't mind since it's a real simple process, but the report relies on me running a query and dumping the results to a CSV file so I can send it to the user.

It's that time a year where I have to go through this process again and this year I decided to hand over the power to the user. From what I heard from other developers, it was a simple tool, so I gave myslef an extra week. I knew I could bang out hte CSV file in about 2 hours if I had to, so I basically had the whole week to work on it.

I started playing with Apex in the morning and in about four hours I had a basic report. In another day, I added some calendar pickers to let them enter a date range and some other dodads to give the user flexibility in how they wanted to filter the report. The best part was the "Spread Sheet" link that automatically downloads the report to a CSV file. Jeff - exit stage left.

I deployed it and gave the user the link in just under two days.

Apex Rocks!

Wednesday, October 11, 2006

Passwords

One of the things about IT security that really irks me is passwords. As a user I need a password for system X and a different password for system Y. Not only that, but system X requires a password at least 6 characters long with at least one alpha character and one numeric character. System Y requires a password 8 characters long, with two numeric characters and I can never reuse the same password. To add insult to injury, System Y's password expires every 45 days and system X's password expires every 365 days. I just changed my password for system Y to something I know I'll never remember.

Now, I know what you are thinking: LDAP server. Centralize the authentication and authorization and you only need to supply a password once. That's all fine and dandy when I have control over the security, but not when system X is where I do my online banking and system Y is my brokerage account.

Things are changing in the financial world, and not for the better, IMHO. At some sites, I have to answer a personal question every time I login. Others, I have to choose a picture before I even get to enter my password. Others still, I need an RSA key along with another password. I think there should be a standard of authentication practices that your personal trading partners should have to adhere to. I've got so many passwords in my head, I can barely remember how to login to work. In the time I wrote this post, I've forgot system Y's password.

Wednesday, October 04, 2006

Truth and the DBA

I don’t tolerate lying at all.

I don’t even want you to spin the truth. Give me the whole truth and nothing but the truth and we’ll be fine. An article about conducting business in an ethical manner by Bud Bilanich at Trump University got my attention. It’s worth a good read.

When I first moved up the ranks from a team member to a team leader, I had somebody that worked for me that skated on the edge of the truth quite often.

“How’s project the upgrade project going?” I asked.

“Fine. I’m right on schedule”, she answered.

“Last time I did an upgrade, the JServ configuration gave me problems. How did that go with this upgrade?” I countered.

“No issues.” She replied.

OK, I guess they fixed that.

Two weeks before the big upgrade, I asked again if we were on schedule and she replied “Oh yeah, probably be done in a week.” So I sent a note to the users about the upgrade and how we’ll need people here to test on Sunday to make sure everything is fine. The users got their army ready for Sunday, upper management was notified since they had been breathing down our neck for getting this project done as well.

Monday before the conversion came and I ask for the new URL so I can look at the new software.

“Not quite done yet, definitely this afternoon.”

Hmm, something is sounding fishy here. I looked at the machine and the database wasn’t even up yet. I poked around some logs and saw that certain pieces were failing to come up for various reasons. Did a quick search on Metalink and saw a couple resolutions to the issues so I didn’t think they were too serious.

Tuesday and Wednesday I was out for training, but left explicit instructions that if progress wasn’t being made I was to be notified.

When I got back Thursday, I went to get a quick status.

“JServ doesn’t work, the Concurrent Managers keep dying, and Apache dies when you hit the login URL” was the reply.

Needless to say, the upgrade was cancelled. Upper management was steamed and since I was the project leader, it was my fault. That person no longer works for me.

Granted, it was my fault for not asking the right questions. However, if they had been truthful about their progress and struggles they would have garnered much more respect and I could portray an accurate picture of the progress to upper management. Their spin on the truth (or outright lies) caused my group to lose a lot of respect from the powers that be.

That’s one of the many reasons I manage the way I do today. As Tom Kyte puts it, “Trust, but verify.”

Monday, October 02, 2006

The world of Spatial part I.....

I want to apologize even before I begin because there’s so much information that I feel I need to share with others that my topics may jump around.

Notice how I didn’t say “The world of Oracle Spatial”? That’s because there is an alternative in the land of GIS (Geographic, Information, System), you don’t have to use Oracle’s spatial module. The big dog in GIS is ESRI and if you use ArcSDE (component of ESRI) it has its own way of doing spatial stuff. Before I get down and dirty with the technical aspects of running and maintaining a spatial database, I feel that it’s important that you (the reader) know upfront you do have a choice of how you can mange your spatial storage.

*Steps up on soapbox*

The ESRI company started out as bunch of engineers who wanted to develop geographic software. The thought was great but, engineers have this idiosyncrasy about delegating work, they think they can do it all and it reflects grossly in their product. The first thing you will become distinctly aware of is that Oracle is kind of an afterthought in the eyes of ESRI, SQL Server is all that is holy with ESRI and the reason for that is because most of it’s customers run SQL Server. Why anyone would want to run an enterprise system with terabytes of data on SQL Server is beyond me (yes, I am Oracle biased). The second thing is Bind variables, they are unheard of, if you have a spatial database and you run ESRI get use to thousands of literal statements plaguing your shared pool . I have done battle for two years with these people and they just don’t get it. There are days where I really want to hop on an airplane, fly to CA and cause bodily harm to the development staff. End of rant.

*Steps off soapbox*

As far as using Oracle spatial vs ESRI “spatial” there are pros and cons of each, it’s up to you to decide which is best for your environment and skill set. I look at using Oracle spatial as kind of like “more moving parts”. The less “stuff” I have to deal with in a database the better, especially if it’s big. I figure if I let ESRI handle ESRI there’s less that can go wrong. I hang out over on ESRI’s forums and the number of Oracle Spatial problems are limited but when there are, they’re usually pretty bad and the question(s) go unanswered. As far as performance gained with Oracle spatial, the jury is still out on that one with me because I’m waiting to see what ESRI’s new release of 9.2 is going to be like. Apparently, they’re going to finally take advantage of the SDO_GEORASTER parameter. Right now they’re only using the SDO_GEOMETRY parameter which handles shape files. It’s like they developed a product, packaged it up, shipped it out, and forgot to put the CD’s in the box. Yeah, if you do use Oracle spatial (now) you get rid of your F and S tables but what’s the sense if you can only use half of the modules ability? It boils down to if you want to use Oracle Spatial when ArcSDE 9.2 comes out you’re going to have to completely drop your rasters and bring them back in (at least that’s the way I see it). I can see it now, managers across the nation giving birth to small farm animals when they find out they’re going to have to drop terabytes of data to take full advantage of Oracle Spatial because of ESRI laziness.

The last thing I want to do is go over anyone’s head when I’m on a roll talking about this stuff so if I mention something that you don’t quite get or want me to elaborate on, please speak up and I’ll be more than happy to pull the reins in sit for a spell.

Friday, September 29, 2006

Job Opportunity

Just learned of a great DBA opportunity in the Jersey City area. Contact Evan Lerman from IJC Partners LLC at (212)626-6920. From Evan:

FINANCIAL EXPERIENCE A MUST ORACLE 9I AND 10G LOCATION JERSEY CITY PAYS UP TO 120K BASE

QUALIFICATIONS:

Minimum of three years experience working as a Database Administrator.

Familiarity with Oracle and Microsoft SQL Server with emphasis on
Oracle. Knowledge of relational database concepts and standards, best practices and procedures relating to database administration.

Experience in financial industry, insurance industry or law firm a plus. Must have excellent technical skills and knowledge of Unix and Windows operating systems. Strong interpersonal, analytical and troubleshooting skills with superior verbal/written skills are required.

JOB Description:
The Senior Database Administrator directs and controls the activities related to data planning and development, and the establishment of policies and procedures pertaining to its management, security, maintenance and utilization. Sets and monitors standards; ensures that database objects, program data access, procedures and facilities are used properly. Advises management on database concepts and functional capabilities. Position is also responsible for installation and ongoing maintenance of enterprise server operating systems and system management products, as well as coordination of installation and upgrades to enterprise servers. Position also provides on-going production system support and performs other duties, as assigned.

Duties & Skills
A. Business/Application Knowledge
  • Understands the company's general business functions, and has a conceptual understanding of each unit's activities.
  • Has general knowledge of assigned application systems.
  • Comprehends the relationships between business activities and application systems. Is able to determine impact of database changes to the application systems, and vice versa.

B. Technical/Programming Skills
  • Builds and maintains all Corporate database environments.
  • Builds and maintains test database environments.
  • Is responsible for recommending and planning the installation of new releases of database software.
  • Ensures the integrity of all physical database objects and established database procedures.
  • Creates storage groups, databases, tables and views, reviews SQL, develops and enforces database standards.
  • Ensures work is thoroughly tested and smoothly implemented.
  • Is responsible for database performance and capacity monitoring and tuning. Prepares regular capacity analyses for management review.
  • Assists in determining storage procedures for on- and off-site storage of historical data.
  • Assists in establishing backup, recovery and restart procedures.
  • Assesses the need for additional hardware or software to assist in monitoring or performance of database applications.
  • Communicates availability requirements for database accessibility.
  • Coordinates schedules and procedures for the implementation or discontinuance of relational database applications.
  • Provides technical support and basic training in the proper use of production databases to database users.
  • Assists in troubleshooting application problems where database management is an integral element.
  • Mentors junior staff on database management techniques.
  • Installs upgrades and fixes to server operating systems (UNIX, NT, etc)
  • Analyzes and recommends upgrades and/or new acquisitions of hardware to support new systems or growth of existing systems.
  • Coordinates vendor installation of hardware.
  • Installs and utilizes third party system management software to monitor overall server performance and capacity utilization.
  • Designs and implements backup schedules for critical databases.

C. Analysis Skills
  • Provides ongoing research and development activities to investigate new technologies and tools which might be used by Company personnel to more effectively and efficiently perform their jobs.
  • Performs functional evaluations of candidate products.
  • Prepares time and cost estimates for assigned projects.
  • Understands the Company System Lifecycle Methodology, and project development lifecycle.
  • Acts as methodology and process mentor for junior staff, as they prepare project deliverables.
  • Contributes to database design reviews.
  • Develops and maintains a security scheme for the database environments.
  • Assists in disaster recovery planning, testing and execution as needed.
  • Possesses strong understanding of the system deployment process and correlation with database administration responsibilities.
  • Coordinates and conducts database design reviews.
  • Possesses keen troubleshooting and creative problem solving skills.
  • Possesses the ability to translate user needs and projections into system hardware and/or software requirements.
D. Basic Skills
  • Adheres to Company standards and methodology.
  • Adheres to company confidentiality and security requirements.
  • Communicates effectively.
  • Consistently demonstrates a high level of integrity and professionalism.

Wednesday, September 27, 2006

The Co-operative

Things are changing in the home offices of "So What?". Some of my guest bloggers expressed an interest in continuing to blog about IT goings on and I thought "Why Not?". We're going to concentrate more on IT stuff on So What and I'll leave the personal stuff over at Wilton Diaries. I present to you, the So What Co-operative.

Oracle Spatial & Wildebeests

Before I make an effort to Blog about Oracle Spatial, Rasters, and Shape files is there anyone who reads this Blog that would benefit from me sharing my experiences with it?

Don’t get me wrong, I’m not saying my time is valuable and I have better things to do with it. I’m just trying to get a feel for what interests you (the reader). I’m sure there are those of you who would much rather discuss the migration habits of the West African Wildebeest during the dry season or how histograms for join predicates only work if you stick your tongue out in the right place. Sorry Dave, you know I have to mess with you :)

So Often I read peoples blogs and it looks like they threw up a paragraph or two of gibberish just to take up virtual space and to make it look like there’s activity. I refuse to succumb to that. You take the time out of your day/evening to come and pay this place a visit the least thing that the contributors can do is write decent content that will make your time spent here either enjoyable or knowledge gained. Nuff Said?

Tuesday, September 26, 2006

What do you do all day?

The conversation started out "What do you do all day?"

I was talking to a fellow IT worker and was trying to explain my job function. I often get this question from non-computer people and I just respond "computers", but this required a more in-depth answer.

I started off with "Basically, I make sure all those databases that the company uses stay up and functional."

"Ah....But what does that mean?"

So I start explaining my day.

My day typically starts with resolving any non-critical problems that happen overnight. I don't have to worry about the critical problems, because they have already been resolved by the person on duty (which is me every other week). I might create a new schema for a user in Europe. Or I might try and find out why userX tried to login yesterday over 300 times using the wrong password. This type of stuff usually lasts from 15 minutes to an hour. In my group, we each have about the same amount of this type of work in the morning. In addition, we'll respond to these type of quickie tasks throughout the day, recording each one.

Then I spend about 15 minutes catching up on how my people are doing with their projects. Sometimes more, sometimes less, but I generally like to get a feel of how things are going before I start doing my heavy duty work.

Most of my day is spend doing what I call "project work". Project Work are those tasks that can't be done in less than a day. A project might be as short as a day, or may be as long as 18 months. An example of a project might be as complex as upgrading Oracle Applications to 11.5.10 or might be as simple as setting up a connection manager for a particular sub-net. I typically schedule my work so I can work on two projects at the same time (ie. while Oracle Applications is applying patch XYZ, I write code for my monitoring software).

Occasionally, I'll have to respond to a critical situation. I have monitoring software running all the time and when it encounters something that it thinks I should know about, the software sends us a message. If a process is running over X minutes, I get a message indicating that maybe I should investigate more. If the software encounters a condition that could potentially stop business, I get notified right away using a text message. If my backups fail at 02:00, I get notified. If the log_archive_dest gets over 90% full, I get notified. On average, I get about three after-hours messages a week when I'm on duty.

I don't worry about backups, they're automated. I don't worry about my alert.log, it's being monitored. I don't worry about the database being up, it's monitored.

The other person usually wakes up from their coma at that point and says "Oh."

Monday, September 25, 2006

Weathervanes indeed

You may recall Steve's entry about weathervane theft in New England. At these prices, I can understand why.

Saturday, September 23, 2006

On top of the world

I’ve never climbed a mountain, but seeing a sunset at 14,000 feet is a breathtaking experience. We took the Mauna Kea (pronounced Mona Kaya) Sunset and Stargazing tour by Hawaii Forest and Trail one evening. They take you from your hotel (sea level, temperature 93) on a 90 minute ride to the summit of Mauna Kea (14,000 feet, 38 degrees) to watch the sunset. The view did not disappoint as an unobstructed sunset was one of the most incredible things I have ever seen.

Mauna Kea is home to some of the world’s most powerful observatories. The stargazing portion of the tour was a 90 minute talk on the constellations and how ancient Hawaiians used these stars to navigate. Then the guide pulled out an 8” telescope and let us see some of the stars up close. The view of Jupiter was striking and we saw details on the moon almost like it was coming from Google Maps.

I’ll let the pictures do the talking, but if you’re ever on the big island, you won’t be disappointed with this tour.
2006_09_05 076
2006_09_05 062
2006_09_05 070
2006_09_05 052
2006_09_05 049
2006_09_05 043

Tuesday, September 19, 2006

Choosing your platform based on TCA?

I giggled at "The Total Cost of Administration Champ: Microsoft SQL Server 2005 or Oracle Database 10g" over on ComputerWorld.com.

I've been in this business 17 years working in countless companies. I've implemented everything from cancer protocol databases to telecom billing systems and I can tell you management has never looked at the Annual TCA per database user as a deciding factor.

I can't believe people are actually paid to come up with these numbers. Lets see an apples to apples comparison. For example, put Oracle and MS SQL on the same hardware on Windows XP (yes, I'm going against my "Never Windoz" philosophy, but last I checked, MS SQL wasn't available on Linux). Then run ApplicationX against both platforms and measure number of customer complaints/hour for each system. Now measure how long it takes to solve those problems until customer is satisfied. That's a real number.

Monday, September 18, 2006

Airline Travel

Airline travel has changed a little bit since I last flew.  Granted, I don’t travel on business and fly maybe twice a year on average, so things could change without me taking notice.  Sure, we all know about taking liquids and gels on the plane is a big no-no now, but other changes abound as well.

First thing we noticed was there is a not-so-generous weight limit on baggage.  Our particular carrier had a 50 pound weight limit per checked bag, two bags per passenger.  Otherwise, they hit you with a $80 charge for “overweight” baggage.  We each packed one bag not wanting to lug our whole wardrobe to Hawaii.  No problem, we thought, until we stepped on the bathroom scale with the bags the day before we left.  One bag was OK at 46 pounds, but the bigger one weighed in at 58.  Problem was, the bag itself was 15 pounds (one of those super large 29” bags).  Valerie took out enough stuff to get it under 50 pounds, but that left the bag about 1/3 empty, which seemed like a waste.  Also, that gave us enough room to buy stuff and pack the bag again and go over the limit on the return trip. We made a mad dash to the store and purchased another 26” bag, stuffed all the stuff in and it tipped the scales at 48 pounds.  First hurdle passed.

We get to the gate to check in and there are kiosks instead of agents at the gate.  I’m no stranger to using the electronic check in when I only have carry on luggage.  But when you have bags to check how do they get on the plane?  Well, you wait.  You punch in all your information, put the bag on the scale and wait for an attendant to take you bag and put it on the belt.  Then the attendant goes to the next kiosk while you put your second bag on the scale and you wait again until he comes back around.  Sure, I understand they’re not paying someone to do that work, but now I have to wait longer.  And what about the 70ish woman ahead of me who had no clue as to what she needed to do?  She needed a ticket agent.  To me, that’s the sign of a company that uses automation to cut costs without regard to the customer’s time.

Anybody who knows me, knows I’m a big guy.  I get along in a normal seat, but it’s not the most comfortable experience for me.  Used to be that the exit row seats weren’t assigned until the gate agent saw you were capable to operate the emergency doors in an emergency.  I used to take advantage of that by getting to the airport two hours ahead of time and snagging an exit row seat about 80% of the time.  Got to the airport two hours early as usual, and requested exit row from the kiosk (see above).  “No problem”, the kiosk says, “I’ll just charge your credit card $41 per seat per leg of your trip.”  WTF?  Thanks, but no.  We board the plane and one seat out of 19 has a passenger who paid the extra $41.  Nice.  Luckily, we encountered one of the nicest flight attendants who saw I was “seat challenged” and “made” us change to an unoccupied exit row.  “I’ll get in trouble for this, but you’re going to die back there.” he said.  Nice to see a person who knows how they used to treat customers in a company that doesn’t care about the customer anymore.

Last, but not least, Airline food.  Does anybody really expect a decent meal on an airline?  Me neither.  In fact, I made conscious decision about 10 years ago that I’d rather have nothing than eat the half not-so-hot half ice-cold apple pancakes on a plane.   More and more airlines are going the way of Southwest in not even offering meals on their flights and opting instead for the obligatory snack of 6 pretzels.  Anyway, this airline has gone the same way and don’t provide meals for their economy passengers.  But you can purchase one onboard; for $5.  Personally, I’ve got no problems with this, but is it another way to extract $5 from the customer?  Perhaps.  Maybe when people pay for their food they will expect more quality.

Saturday, September 16, 2006

Thanks

Thanks to my guest bloggers the last couple of weeks. I hope they gave you something a little different to read and maybe encouraged them to start blogging on their own. A couple observations over vacation will follow and progress to the normal stuff after that.

Monday, September 11, 2006

The Ultimate job....

What if you were a gamer and an Oracle DBA, what would be the ultimate job for you? You know a job where you couldn't wait to get to every morning!

I think I found the Holy Grail of jobs (at least in my opinion) and it pains me to see that I have to let this slip through my fingers knowing that if I applied I could get it. I keep convincing myself "No, I don't want to move to California". Don't get me wrong, I really enjoy what I'm doing now but... it's Blizzard *droool*.

Gaming has always been a hobby of mine (when I can find the time). I just recently acquired an old P233 and I slapped a hundred megs of ram with Windoz95 on it so I could play all those old games like MecWarriorII. Here’s the kicker I had an extra Nvidia PIC 125meg video card sitting around and I went to their website to see if they had the drivers for Win95 low and behold they did! So you’ve got 100megs of RAM and 125meg video card with plenty CPU power. The games play great the only thing is you can’t get that high resolution like 1024 X 786 only 800 X 600.

Now for something completely different….

For those of you who are upgrading from 9i to 10g Oracle has this nifty little validation checker that runs a script against your server (Solaris) to ensure all the settings, patches, bla, bla are correct. Well as of Aug 25th it’s no longer valid. Last week I was prepping a server for an upgrade ran the script and everything came back all nice, then on Saturday I was doing the actual install, it’s running its own checks and bam! It tells me I’m all messed up and not to even try installing 10g. Ohhh I was soooo mad! Sure would have been nice to know Oracle de-supported it. Only good thing that came from it all was that I was able to get back home and spend the weekend gaming *wink* hee hee.

Friday, September 08, 2006

Why I can't call tech support anymore...

I got home from work today and discovered that my Internet connection was funky - I could get to Google (figures because they have nodes everywhere), but not to Yahoo or CNN. Traceroute showed that my packets were being routed all over the country, but not getting to their final destination. It was obviously a network peering problem and nothing on my end, so I figured what the heck, maybe SNET tech support might actually know about the outage so I called tech support.

Here's why I've gotta stop doing that...

First off I'm talking to a first line support center in India and tell him that there is obviously an outage somewhere (not in my house) because I can get to some sites and not others and traceroute is showing the packets being routed all around the country. He asks me what operating system I'm running. The machine I happened to be sitting at was XP which would be an acceptable answer. I mentioned that I've got multiple computers on the network, he asked how many and I hesitate to answer - the real answer is something like 17, but somehow I thought 10 would sound more reasonable.

He's asking me questions to see if my DNS is correct, I told him it wasn't DNS related and was a routing problem, but he quizzed me on a few IP address anyway. Then he asked me what brand router/firewall I was using and I knew he wouldn't be comfortable with the answer (a homebuilt box running Linux I built from source with 2 ethernet cards in it). He asked me what DNS server I was using (my own (built from source) which is configured to access the Internet root servers directly). I told him to look up my call history and notice that everytime I've EVER called them it ended up being an outage on their end. I could tell he wanted to ask me how many lights were on on my DSL modem.

Finally I decide to give up, their network support would figure out the problem eventually, but the first line guy was being persistent now and offering to conference in level 2 support. Then put me on hold for a while. I'm noticing that I can get to european sites fine, but not major US sites (CNN, Yahoo). Then the US sites slowly start working, so I hang up on the support call (still on hold).

Then the tech calls me right back and I tell him the situation seems to be resolving. He asked me if there was anything he could have done to make this a better tech support call - I said yes, you could have believed what I was telling you!

I know that most of the people they deal with are wondering how to retract the cupholder on the computer so they really can't believe what they're being told, but if the person they're talking to is discussing traceroute maybe they could step it up a notch...

How about a special phone number for people who have demonstrated that they know what they're talking about? That way the conversation could have been:

Me: "Hi super tech support, I've noticed that some major sites are offline, but my overall connectivity is there. My requests are being routed in crazy ways around the country so it looks like a peering problem."

Them: "Yup, looks like something that would affect lots of users, we'll check it out, thanks for letting us know about the outage".

Tuesday, September 05, 2006

The Joys of RFID

Welcome all to the “So What?”!! The Hunter family off to the South Pacific? *pffffftttttt* yea right he’s probably syncing up his Blackberry right now or sitting on the couch watching cartoons. Anyway……

I guess I should at least give a little background on myself before I dive into the Oracle silliness. Most of you know me as OracleDoc on the forums and I’m currently working for a Defense contractor who has a contract with the US Army in Germany. I’ve been here for over two years now and I don’t see an end in sight. In all actuality, I rather enjoy what I’m doing because it’s cutting edge and it keeps me on my toes, not to mention it’s in the land of beer and Autobahns!

I’ll give you a little sample of what we do here because I think it’s a great technology and it’s going to be more main stream with the commercial world here very soon (if it hasn’t already).

RFID (Radio Frequency Identification). Go watch this commercial then come back. Basically anything the Army slaps an RF tag on or any other device that has the ability to broadcast its location, my database stores its location. I can then regurgitate that information onto a webpage either textually or as an image on a satellite map. I’m sure you’ve all seen Google earth, so imagine you have a piece of equipment that you want to know where in the world it’s at, you plug in the identification number of the tag and voila`! there it is displayed neatly on a Sat image.

*side note; These things are great for tracking teenagers driving habits

Funny story….couple months ago I was looking for a particular truck that the Army uses solely in Kuwait for transporting food. I plugged in the criteria and said give me all the food carrying trucks that have reported within the last 24 hours and bring it to me on the Sat map. I was expecting to see all these little truck icons scattered throughout Kuwait but to my surprise, I see this one lonely truck icon smack dab in the middle of Beirut. I’m thinking “nawhhh it’s a glitch”…click ”refresh” but nope it was still there. So I plug in the id number of this truck and tell it to give me the history of all its positions. Good Lord…. this truck started out in Kuwait then worked its way up to Iraq, then on over to Syria, then finally stopping in Beirut. At this point my mind is throwing up red flags all over the place because there’s a remarks column next to the tag and it’s saying that the truck was destroyed by an IED (improvised explosive device) several months back. At this point I call in my boss and we start making phone calls.

As it turns out, the truck was stolen and the company that owned the truck instead of saying it was stolen just said it got blown up. You say stolen and the investigation and paper work start, you say blown up and you get reimbursed for the cost of the truck. Needless to say someone got their genitals whacked.

3 days later the little truck icon in Beirut disappeared. I love my job!

Sunday, September 03, 2006

Theft blows in the wind in New England

I read this story and had one of those "Only in New England" moments. People are climbing barns all over New England, stealing antique wind vanes while replacing them with cheap copies so hopefully the owner won't notice until they've been sold. The owners are so distraught that they wise the theives would still their SUV instead. Someone's going to end up with a pitchfork where the sun don't shine if a local catches the crook before the police do...