Generational Realizations

It is nearly the end of 2018 and I'm just now publishing my first blog post.  In short - it has again been too long.  I'm mulling over various options but want to start spending more time on my blog next year to be able to capture what is transpiring in my life right now.   Not only to have a historical record for myself but also because I do believe at some point it could be helpful for others who are at the same point in their life undergoing a similar conversion from being hands-on day to day to a more strategic managerial role.

So let's start this post by a revelation - I'm old.  Each day I look in the mirror and see someone looking back that still has the same feeling I did twenty years ago when I couldn't wait to get into the office and lead an effort to code and build the next great "thing".    Yet while two decades ago I'd cap the day off by racing out of the office to go to happy hour or play a pickup game of basketball now  if I'm lucky to get out of the office with daylight as I'm racing home to spend more time with my family and perhaps get into bed a bit early that evening.

However this epiphany that I'm old is really more than coming to grips with the fact that my daily routine has changed.   What I've also accepted as of late is that I now manage an organization with a collection of overachievers that whether I like it or not are a generation younger than I am.  They see and do things differently.   I often catch myself and make a mental note that I should not be performing their role and when I did it was a different time. 

As an example, when I started my professional career Mosaic was in it's infancy stages - there really was not an Internet - and certainly no-one had a concept of IoT (well maybe some did but the technology certainly wasn't anywhere close to being able to suppor the idea).  As an aside, to provide some perspective, shortly after graduating from college my father's IT company actually stood up one of the first web servers released by Marc Andreessen.  It was a different time.

But I digress.  One thing I have to constantly remind myself is that my engineers don't have a concept of what things were like before the Internet.  They simply grew up in a world with Internet Explorer (or pick another browser) on every PC they touched.   While at times I still find comfort holding a textbook in my hands - most of them I'm sure can't stand the thought of it.  This isn't right or wrong it's just that their perception and frame of reference is different from mine.

Understanding these generational differences resulted in yet another a revelation the other day with my children.  I think everyone is aware at this point that with Big Data and the Internet it is now it incredibly easy for information about me personally to be accumulated, stored and reported on.   Everything we do in our daily lives has the chance of being captured and stored somewhere.

In it's infancy stages one might say this collection of data started with browsers and cookies but with the explosion of IoT this tracking has taking a completely different turn.  Everything is tracked now and it is to the point that unless one is OK with unplugging completely you simply have to subscribe and agree to have this information gathered and reported on.  Alexa, "turn on my lights" or "play some Elton John" - somewhere one has to believe those actions are being tracked.

I've in fact been able to see this as part of what I do everyday.  We are seeing databases out there that track cell phone locations using Bluetooth signatures.  As I'm driving home I know my Bluetooth signature from my phone where I am streaming a sports radio feed from Washington D.C. is allowing my location to be tracked.  While there wasn't a direct link to me in that database I'm smart enough to know that there are other databases in he cloud that do track that relationship.

Walk this forward to my elementary age twin girls.  They are growing up in a world where they will never know a reality where their locations are not being tracked.  To me that idea is horrifying.  But when I take a step back I also see how those who are younger in my organization are already not as concerned about their privacy as I am or others are from my generation.

And of course, I just need to constantly remind myself "it's a generational thing" and then try to determine how I can adapt and fit in ... or not.

Global vs Local Indexes in Oracle

I recently came across a case where I needed a fundamental lesson in how partitioning really works in Oracle.  The part of my brain that tries to think as a computer scientist couldn't reconcile how a certain execution plan in our production Oracle instance was being returned.

We have a multi-terabyte table (20+ TB actually) that we recently re-partitioned with a range partition on a date/time (e.g., CREATED_DATE) but also with a sub-hash partition on a particular unique device identifier (e.g., DEVICE_ID).

When I asked Oracle's cost based optimizer (CBO) to return a plan it came back with an index that was built with the columns in the order with the device identifier preceding the creation date.   From an indexing perspective this makes sense.  I want to start with the specificity of a numeric ID first but then focus in on a particular range of data for that device (e.g., search for all data for device X but do so for only a certain range of data between dates Y and Z).

At the same time our re-partitioning strategy was driven by a desire to implement an Information Life-cycle Management (ILM) Architecture where we could query and move our older data from faster to slower disk storage.  This is why we chose to implement range partitioning first on the creation date thereby bucketizing data by year/month and then for added performance we created a sub-hash partition (i.e., additional buckets) on the device identifier.

At the end of the day my confusion was with how the CBO would pick an index that was not constructed in such a way that it aligned with our partitioning strategy.  Below is how in my mind I pictured our B-tree index being laid out with respect to leaf nodes and their traversal to retrieve data for the example I provided earlier (i.e., for Device X search for data between dates Y and Z).

Again from an indexing perspective this makes sense.  I'm starting with the most specific column using an equality in my predicate (i.e., DEVICE_ID = 103) and then performing a range scan for CREATED_DATE for a particular device identifier.   What didn't make sense to me was how Oracle reconciled this against my partitioning strategy where my first level of "buckets" were by CREATED_DATE on Year/Month.

At that point one of our contractors was able to connect the dots for me explaining the difference between Global and Local indexes.  Take first Global indexes.  They are built essentially irrespective of the underlying partitioning strategy.  They work well if you do not have the capability to include in your predicate or underlying index a qualifier that would allow you to take advantage of partition elimination (i.e., in our case CREATED_DATE).

As depicted below, each leaf node in the index can have references to any of the underlying table partitions.  This also means that especially on range scans (e.g., let's say I only knew DEVICE_ID) you might end up touching every partition to retrieve your result set.   Again can be slightly more efficient if one doesn't have a predicate that aligns with your partitioning strategy.

Now take the case of Local indexes.  In this case Oracle is a bit smarter.  It takes the index, breaks it down and manages it using the underlying partitions.  In my mind I pictured this much like the figure below where the prior index is split and managed independently.  In my example, all rows with January CREATED_DATEs would be separated from rows with February values.

I can also now look at the EXPLAIN PLAN output for my query and see that it is actually using partition elimination.  Notice in the case below it used a PARTITION RANGE ITERATOR because the predicate in my query included a CREATED_DATE that went across multiple partitions.

      
-----------------------------------------------
| Id  | Operation                             |
-----------------------------------------------
|   0 |  SELECT STATEMENT                     |
|   1 |   SORT AGGREGATE                      |
|*  2 |    FILTER                             |
|   3 |     PARTITION RANGE ITERATOR          |
|*  4 |      TABLE ACCESS BY LOCAL INDEX ROWID|
|*  5 |       INDEX RANGE SCAN                |
-----------------------------------------------    
 

When adjusting my predicate to specify a range within a particular partition the plan changed indicating that partition elimination further reduced my results to a single partition.

      
-----------------------------------------------
| Id  | Operation                             |
-----------------------------------------------
|   0 |  SELECT STATEMENT                     |
|   1 |   SORT AGGREGATE                      |
|*  2 |    FILTER                             |
|   3 |     PARTITION RANGE SINGLE            |
|*  4 |      TABLE ACCESS BY LOCAL INDEX ROWID|
|*  5 |       INDEX RANGE SCAN                |
-----------------------------------------------    
 

In a subsequent post I'll go into greater detail on simple points for reading the output of  EXPLAIN PLAN traces.   Going through this particular indexing exercise forced me to relearn what previously second nature to me.  Specifically how one can use output from EXPLAIN PLAN to further tune your queries.

Transitioning to ANSI Compliant SQL

For years my guys have been a proponent of ANSI compliant SQL.    Today I decided to take the plunge and force myself to abandon the only SQL I have ever known and that was firmly grounded in an Oracle world (some might say grounded in cement shoes).

I made this decision not only because it is something that works across platforms but also as I been reminded over and over again by those I manage that at the end ANSI compliant SQL is simpler to understand and maintain.

To help others that might follow in my footsteps, I wanted to document my learning process by posting here.  I thought I'd start with something very simple - illustrate how joins differ between a more traditional Oracle SQL query and a purely ANSI one.

Typical Inner Joins

Let's start with a very simple query that uses a traditional join.  In a Oracle world, the syntax for this uses the WHERE clause to join the two tables.

Take the example below where I want to join an employee's profile record to the agency that he or she belongs.  Both tables are referenced in my FROM clause and in my WHERE clause I include a hard join that allows me to bring back the name of the agency to which the employee belongs.

One might also consider the case where for some reason the agency referenced didn't exist or there wasn't an agency specified in the employee's profile record.  In this case the query would not bring back a row.  That can be solved with an outer join - but more in that in a minute.

Below is the query as I used to write it for Oracle:

   select first_name
        , last_name
        , agency_name
   from employee_profile ep   
      , agency a
   where ep.agency_id = a.agency_id

To convert this to a ANSI compliant query my FROM clause becomes much more prominent as I use that to perform my join.

   select first_name     
        , last_name     
        , agency_name
   from employee_profile ep
          inner join agency a
            on ep.agency_id = a.agency_id

In this particular case as illustrated above my WHERE clause disappears.   That was one of the first things that jumped out at me as I could immediately see how my query is simplified.

Outer Joins

It is pretty safe to say that outer joins are an area where Oracle has their own "unusual" syntax that they introduced to handle this type of join.  Oracle uses a plus sign surrounded by parenthesis (+) to indicate that side of the join is "optional" when attempting to link the two tables.

Let's build on the prior inner join example and if the selected employee's agency has been awarded a certification we want to be able to display the type of certificate.  In a strictly Oracle world, I'd add the certificate table to my FROM clause and then add an outer join to my WHERE clause to indicate I want to pull back the certificate name only if there is a certification specified for the agency.

   select first_name
        , last_name
        , agency_name
   from employee_profile ep   
      , agency a
      , certificate c
   where ep.agency_id = a.agency_id
    and a.certificate_id = c.certificate_id (+)

Now I want to convert this to use a left outer join on the certificate table to ensure I only pull back certificate information if one has been specified for the agency.  So in essence I want to pull back data regardless of whether there is a matching certificate or not.

   select first_name     
        , last_name     
        , agency_name
   from employee_profile ep
          inner join agency a
            on ep.agency_id = a.agency_id
          left outer join certificate c
            on a.certificate_id = c.certificate_id

Looking at the SQL as written above it again is striking how simple the resultant query becomes.  I still do not have a WHERE clause and have performed all of the appropriate joins in the FROM clause.  The WHERE clause at this point strictly becomes a way to add additional filter criteria.  That being said, one could also add additional filter criteria within the FROM clause when building your joins.

For example, let's say that we only want to show the certificate if it is one that is part of a federal compliance program.  We would simply add that filter criteria to the outer join we have already provided.  Any records matching the outer join on the certificate ID simply wouldn't be displayed unless they are marked for federal compliance (i.e., and c.federal_compliance = 'Y').

   select first_name     
        , last_name     
        , agency_name
   from employee_profile ep
          inner join agency a
            on ep.agency_id = a.agency_id
          left outer join certificate c
            on a.certificate_id = c.certificate_id
           and c.federal_compliance = 'Y'

Reviewing the resulting SQL it is again easy to see how simplified the query becomes when you make it ANSI compliant.  I still do not have a WHERE clause having performed all of the appropriate joining in the FROM clause to include my additional filter criteria with ensuring the certificate is part of a federal compliance program.

Cartesian Joins

I almost didn't include this example but it also illustrates a simplification.  Let's say I have two tables - one that tracks day in year (e.g., 1 - 365) and a table to track hours (i.e., 1-24).  If I want to combine these two tables to fall all permutations, previously I would have written a query as follows:

   select day_id, hour_id
   from days, hours

Now that query becomes:

   select day_id, hour_id
   from days 
   cross join hours

Although not much simpler to write it does read in a much more straightforward fashion.  In general that probably is the most significant thing I learned when converting over to using ANSI compliant SQL - that what you produce is much easier for others to read.   This is especially more true as the size of your query grows and the number of joins you are specifying becomes larger and larger.

So that is it for the time being.  

A Long Time Away

It has been quite a while since my last post.  Although I had ever good intention of sitting down to write I haven't and quite a bit has happened during that time.  Mile high I've been continuing on a path to find out what success today equates to in my current role as an executive.

I'll be posting details on where my journey has taken me as of late including attending some executive education programs at UC Berkeley but before that I thought it would be worthwhile to share a couple of thoughts and observations I've had recently.

I have no clue what you are saying: So some of my engineers I'm realizing have a difficult time explaining complex topics to those that do not have an engineering or computer science background.  It's fascinating to watch as from their perspective what they are saying makes total sense to them but now that I'm observing more I am able to see the blank looks and stares on people's faces as my engineers dive into gory details that the other party has no hope of understanding.  These same dazed and confused individuals are also at the same time terrified to ask questions further compounding the problem.

It's all about solving problems: I came to the realization early on in my career that of my strengths was the ability to find innovative and creative solutions to technical problems.  The same part of me that never liked saying "no" also translated to software development and architecture where there was a place deep down that could not stand (actually detested) saying "no - it can't be done".
I'm finding that same mentality is what is driving me and making me successful in my new role.  As an example I have the same stubbornness when faced with a hard capital budgetary constraint that could kill a project.  It's my responsibility to find a way to succeed even when presented with something that to others would be a showstopping roadblock.   I've also found that usually there is a solution out there that although it might not be a path people want to follow it can be done in most cases.

Less detail in electronic communication: where as in prior days detailed e-mails helped describe more complex technical topics in a way that others could understand I'm finding now that less details is better.  Perhaps for now my blog will be my outlet for spewing details - I have to say I've found this consolidation of electronic communication undertaking extremely difficult - certainly another blog post once I have some learning behind me.

Employees and Work-life balance: this is another topic that has come up and continues to be something I deal with throughout my organization.  More on that in another post.   It's an interesting debate whether this is a timeless phenomenon or something that is more generational.   Right now I'm of the opinion that it's the former.   At the end of the day life is about choices - give and take.  I find those that are successful and I enjoy working with have found the right balance but at the same time do put an emphasis on their professions - the challenge is conveying this to those who may not have the same philosophy.

That's it for the time being.  My goal for the next twelve months is to pen at least once of these posts per month.   And who knows, perhaps some may even dive into a technical problem I have solved.

Where Have All The Lightening Bugs Gone?

A friend of mine politely reminded me recently that it has been quite a while since my last post and that was time for me to sit down and blog.  The past couple of months in particular have been a rough stretch but I'm hopeful a corner is being turned and I'll be able to find more time for my blog and other hands-on type activities.  The origins of this blog entry however have nothing to do with my professional life and yet in some strange ways maybe they do (more on that in a bit).

This past Sunday I took the opportunity to get away from work and enjoy some time with my family.  While my wife and mother-in-law spent the day shopping I decided instead to hang out with the kids in a local park.  It was one of those parks where fountains shot out of the ground at odd times allowing the kids to playfully run through and get themselves completely soaked.  Watching my girls play, I was struck by how much joy could come from the simplest of devices.  No money involved - not a shiny new game for the X-Box, or a trip to a movie, or an amusement park - something as simple as running through a fountain in a public park was sheer joy for them.

The afternoon concluded with my kids joining in with others in a game of tag I remember from my childhood.  A tree at the center of the park served as "home base" and while the other kids scattered a few would remain behind and close their eyes, count to ten, and then try to tag the other kids who were frantically scrambling back to the tree.  Again, kids laughing and playing with no money spent on equipment or organized activities.  My girls just enjoyed being able to play with other kids in the neighborhood in a simple game of tag.

A few weeks back my wife made a comment that had me think back to how life as a child everything seemed much simpler.  "Where have all the lightening bugs gone?" she asked.  I said I don't know but do remember them from my childhood.  Sunday's park experience had me thinking about this again where I was able to observe the joy my girls had with the simplest of activities.  My mind drifted to my original days in computer programming and what drove me to want to do more?  I remember the joy in creating my own adventure game in Basic a.l.a the Scott Adams ilk where whatever my imagination could come up with I could program.

Spin this forward to today.  What drives me now as an executive who oversees software development for my company.  While I'm certainly not as hands on as I once was, my joy for finding creative solutions to problems and programming a different and better reality for my customers is  as real as it was when I was a young kid learning to program in Basic.

Communication is the Key

While I have kept my hands dirty over the past six months I also know that so much more of my success is based on my ability to effectively lead and manage others.  Communication is an important component of that success.  I have noticed though that since becoming an executive the interaction I have with my team members has changed.  The frequency and level of detail when communicating with those in my organization is not what it has been in the past.  This extends to both the interaction I have with my leadership team as well as others in my organization.

Let's start with the latter where today I just do not have the same type of day-to-day conversation with everyone that is part of my team.  I realize now that as an executive I am no longer considered "one of them" or part of the rank and file and in the end probably should not be - well at least not completely.  Some level of distance is healthy - it provides my managers the ability and confidence to effectively lead.  They should be the ones developing a close relationship with their employees and not me.

As an example, although I always maintain an open door policy with anyone in my organization I am cautious when approached directly by an individual that is bypassing their manager.  I listen to what the employee has to say but in almost all cases defer to their manager trusting in their ability to resolve the issue.  To be completely honest one of the driving reasons I do communicate more with those that do not directly report to me these days is time.  I realize what a valuable commodity time is for me personally and I now use it judiciously.

As far what level of communication I have with my managers I have put quite a of thought into this as of late.  Looking back at the successful managers from my past what made them great was their ability to cultivate a strong relationship with those that worked for them.  Knowing what makes their employees tick - what motivates them and makes them happy.  The best managers always seemed to make me feel comfortable going to them with issues - those inside and and some cases outside of the office.  This only helped build a stronger bond and made me more productive.

In a recent discussion w/ our CEO he brought up another great point I hadn't considered and caused me to completely rethink how I interact with those I manage.  Responsibility he believes must also be on the employee to initiate communication.  I can't always be the one that makes sure this happens.  This fosters a healthy amount of independence with certain tasks.  Our discussion led to my realization that if I'm fulfilling my new role correctly, I ultimately become more of an arbitrator of decisions.  My team should already have considered alternatives and made a decision before coming to me.  I can then simply weigh in when competing viewpoints are raised.

Oracle Misdirection: Lies, Filthy Dirty Lies!

So I learned today that Oracle with it's EXPLAIN PLAN and AUTOTRACE features sometimes will "lie" about how a particular query is executing.

For a problematic query against a view in one of our databases, I pulled the query into Aqua Data Studio (cross platform database development/admin tool that I use)  and ran an EXPLAIN PLAN on it. What truly puzzled me was that the query appeared to be using the correct index but never seemed to return in a reasonable amount of time.

After speaking with our Senior Oracle DBA, he informed me that the RDBMS will often decide at execution to use a different plan than what EXPLAIN PLAN or AUTOTRACE says it is going to use.  Some of this decision depends on the whether or not you use bind variables or at times the data itself that you are binding in your predicates.

To troubleshoot, I needed to look for the SQL_ID for the query in question.  To make sure I pulled the EXACT query that was causing the problem, I actually looked at a join between the v$sql and v$sql_bind_capture views focusing in on a CustomerID for a query that I knew I had just run:

SELECT /*+ PARALLEL */
       v.executions,
       c.VALUE_STRING as CustomerID,
       v.sql_fulltext,
       v.sql_id,
       v.rows_processed, v.parsing_schema_name,
       v.last_active_time, v.last_load_time, v.first_load_time,
       v.disk_reads, v.buffer_gets, v.cpu_time, ROUND(v.physical_read_bytes/(1024*1024)) as read_meg
FROM  v$sql v, v$sql_bind_capture c
WHERE
v.sql_fulltext LIKE '%CustomerSummary%' AND
v.sql_id = c.SQL_ID
AND c.name like '%CustomerId%'
AND c.VALUE_STRING like '%2005681%'
AND v.PARSING_SCHEMA_NAME = 'MO_COUNTIES'
and v.last_active_time >= sysdate - 1
ORDER BY physical_read_bytes DESC;

This then gave me a SQL_ID that I could plug into the following that provided a table pinpointing the EXACT plan used at execution:

select * from table(dbms_xplan.display_cursor('3qub1pyswv17b','',''));  

Digging through this plan I discovered that an index I thought should have been used was not.  In addition to asking our DBA to gather statistics, I also for the time being modified the view such that it now has a hint driving it to the correct index.