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.

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.

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

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.

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.

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...

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.

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...

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.

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.

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.

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.

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.

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.

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...

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:

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.

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

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.

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!

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.

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.”

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.

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.

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?

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."

Weathervanes indeed

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

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.

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.

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.

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.

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.

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!

Aloha

October 1997 was the last time I took more than a week off for vacation. At that time, I was somewhere south of Summerlands watching Ferry Penguins come ashore at dusk. Anybody (except HJR) know where I was?

Anyway, the moons have aligned and I am taking off to a remote spot in the South Pacific for two weeks and a little R&R. No cell phone, no laptop. I'm not even sure about TV. Since I won't have connectivity for a while, I've invited a few people to be "Guest Bloggers" to fill in while I am away. I know the readers will get a new perspective, and I hope the guest bloggers have a little fun.

Joey Vayo
Actually, this is all Joey's idea. He came up with the idea of "guest blogger" a little while ago and I thought it was a good one. Of course, he was the first one I asked. He's been a regular on DBASupport.com for years and always gives helpful advice.

Steve Prior
Steve's brainchild is http://www.geekster.com. Although he is a developer by trade (boo, hiss), I think he is a tinkerer by heart. A usual conversation with Steve might start out with "I am looking for ODBC drivers for a PROLOG engine that I am building into my smart-home system to automatically flush a toilet..."

Brian Byrd
Another DBASupport.com regular, Brian is a master at making SQL more efficient both from a coding perspective and from the perspective of the database. While Brian and I don't always agree, I certainly respect his knowledge and ability to think about problems using a different approach.

Jeffrey M. Hunter
OK everybody, there is more than one Jeff Hunter in the Oracle world. I usually differentiate us by saying he's the guy with the RAC Paper and I'm the guy with the blog. Someday we're going to run off and create our own consulting company, but until then, I'm sure Jeffrey will check-in with some very exciting information.

David Pittuck
Last, but not least, we have the world renowned DaPi. David likes to be know as the "Old Cranky Philosopher", but is quite enlightened in the world of IT. (He’s also proud of the fact he has a NerdScore of 93). You can check him out at http://www.pittuck.com.

A big thanks to my guest bloggers. I hope you guys have as much fun blogging as I do.

Regularly scheduled programming resumes around September 18th. Take it away guys...

Fun Blogs

I really enjoy peeking into other people's lives for a brief second. I've been reading a couple blogs lately that are very well written and entertaining. If you like reading the likes of Heather B. Armstrong, LeahPeah, or WaiterRant, I think you'll enjoy New York Hack and Everything is Wrong With Me.

9.2.0.8 Patchset

Has anybody heard when the 9.2.0.8 patchset for Solaris and/or Linux will be available? All I see on Metalink is 2006Q3, which we're about half way through...

Compute or Estimate

A question came by me the other day that I had to do a little head-scratching on.

"How can you tell if your table was analyzed with COMPUTE or ESTIMATE?"

Good question. In my enviornment, I know it was COMPUTE because that's how I do my stats. But how would I know otherwise? There's a column called sample_size in dba_tables that indicates how many rows were used for the last sample. Ah, just a simple calculation.

Lets see how it works out. First, create a decent sized table:

SQL> create table big_table as select * from dba_objects;

Table created.

SQL> insert into big_table select * from big_table;

42396 rows created.

SQL> /

84792 rows created.

SQL> /

169584 rows created.

SQL> /

339168 rows created.

SQL> /

678336 rows created.

SQL> commit;

Commit complete.

Of course, we don't have any stats yet, but lets just check it anyway.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                                NO                                     

Sounds good. Lets analyze our table using dbms_stats and see what we get:

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE');

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, 
     sample_size, num_rows, 
     decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
    /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES     1356672    1356672        100  

Cool, that's what we expect. Now let's analyze and estimate with 1% of the rows:

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE', estimate_percent=>1);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES       13371    1337100          1  

That definitely is what I was expecting.

I think I remember that if you estimate more the 25%, Oracle will just COMPUTE. Lets see what happens:

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE', estimate_percent=>33);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES      447748    1356812 33.0000029  

Here I'm on 9.2.0.5. Previous to 9.2, this would have done 100% of the rows, but in 9.2, it will estimate all the way up to 99%. (Why you would estimate 99% vs. 100%, I have no clue, but it can be done).

Let's see what the block_sampling does:

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE', 
     estimate_percent=>10, block_sample=>true);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES      132743    1327430         10  

Eh, nothing new there.

Lastly, let's see what dbms_stats.auto_sample_size does.

SQL> exec dbms_stats.gather_table_stats('JH','BIG_TABLE',
     estimate_percent=>dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed, global_stats, sample_size, 
     num_rows, decode(num_rows, 0, 1, (sample_size/num_rows)*100) est_pct
     from dba_tables
     where table_name = 'BIG_TABLE'
     /

TABLE_NAME                     LAST_ANAL GLO SAMPLE_SIZE   NUM_ROWS    EST_PCT  
------------------------------ --------- --- ----------- ---------- ----------  
BIG_TABLE                      14-AUG-06 YES     1356672    1356672        100  

That's interesting, 9.2 thinks my best method is to compute. I try this on several 9.2 dbs and different sized tables and it picks 100% every time. So I go to the friendly Metalink and find a couple bugs in 9.2 that indicate auto_sample_size doesn't exactly work in 9.2.

Not sure where I'll use this in the future, but it was an interesting experiment anyway.