Friday, December 12, 2014

Useful Websites for Web Developers

Many times a blog posts functions as a place where you can write something down so you will be able to find the information later, and that is what this blog post is.  There are a number of useful websites that I have bookmarked or sometimes not, but I want to keep this list all in one place.  If you are a web developer, you probably already know about most or all of these sites.  But if you don't, you might want to check them out as well.


Browserscope (http://www.browserscope.org/)

This site allows you to test the capabilities of your browser as well as see the capabilities for other browsers people have tested.  This does test browser capability, but also gets a little more down into the details than other sites on this list.  For example, it you are wondering how many concurrent network connections a particular browser supports, then this is the site to go to.

HTML5 Test (https://html5test.com/)

More and more browsers support HTML 5 these days, but there are still important differences out there.  This site will not only test your browser, but compare different browsers to each other so you know what capabilities are and are not supported.

Can I Use (http://caniuse.com/)

Another site about what features are available in what browsers.  In this site, you select the feature from the main page and it then takes you to a page showing you what browser versions support that feature.

Modern IE (https://www.modern.ie/en-us)

This site by Microsoft allows you to scan a page to see if you are using any old or deprecated features that you should be uplifting.  What is even better is they have an interface to Browserstack which will take screenshots of your website right from a webpage.


Google PageSpeed Insights (https://developers.google.com/speed/pagespeed/insights/)

Web performance is a big deal, and PageSpeed Insights quickly scans a target website for front end best practices.  You will get a score for both mobile and desktop page speed as well as mobile usability.  In addition, you will get specific recommendations about how the performance of your site can be improved, along with links to Google's documentation explaining exactly what needs to be done.

The online version of PageSpeed Insights can only target publicly available web sites.  You can also check out the Chrome Extension.  One of the nice things about the extension is that for things like optimized images, the extension will provide you with an already optimized version.


Web Page Test (http://www.webpagetest.org/)

This site allows you to run a performance test against your website from locations around the world.  This is very useful to get some real data about what your performance might look like for someone across the country or across the world.  You get back detailed reports including waterfall charts and a breakdown of the type of data that was loaded.

Alexa (http://www.alexa.com/)

Alexa provides site ranking and demographic data about who is viewing a site.  This is useful for comparing your site to competitors in your categories.  The data provided has an SEO bent.  There is a pay version, but still a good amount of interesting data available for free.


Built With (http://builtwith.com/)

Ever wonder what technologies a web site was using.  Built With scans the site and delivers you a nice report on everything ranging from the web server to JavaScript and CSS libraries used.  Could you get all of this information yourself by inspecting HTTP headers and HTML?  Sure.  But why do that when this site can do it for you.

Sunday, September 7, 2014

Sample External Tables

When creating external tables in Oracle, it is much easier to start with a sample definition and then just modify it to fit your specific needs. So that is what this post is all about, is providing some sample definitions that all of us can use the next time we need to come up with an external table.

With all of these, you will need to adjust your DEFAULT DIRECTORY definition in the external table definition if you just want to try out my files.


Comma Separated Values

Probably the most popular flat file format. The sample file contains data that has a header row and all of the values are quoted.

The sample file is states.csv
CREATE TABLE import_states_csv
(
    abbreviation        VARCHAR2(50),
    state_name          VARCHAR2(30),
    date_of_statehood   DATE,
    capital_city        VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_import
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        FIELDS TERMINATED BY ',' ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        REJECT ROWS WITH ALL NULL 
        FIELDS
        ( 
          abbreviation,
           state_name,
           date_of_statehood DATE "YYYY-MM-DD",
           capital_city
        )
    )
    LOCATION ('states.csv') 
); 


Tab Separated Values

This type of file is not seen as often these days, but you still may run into this from time to time.  Notice how in the external table definition the field separator character is specified as hex value 9 (which is the tab character).

The sample data file is states.tsv


CREATE TABLE import_states_tsv
(
    abbreviation        VARCHAR2(50),
    state_name          VARCHAR2(30),
    date_of_statehood   DATE,
    capital_city        VARCHAR2(30)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_import
    ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        FIELDS TERMINATED BY X'9' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        REJECT ROWS WITH ALL NULL 
        FIELDS
        ( 
           abbreviation,
           state_name,
           date_of_statehood DATE "YYYY-MM-DD",
           capital_city
        )
    )
    LOCATION ('states.tsv') 
); 

Fixed Width Data File

You can download a list of all of the US banks that participate in ACH transactions from the US Federal Reserve at this link - http://www.fededirectory.frb.org/download.cfm.

What is useful about this is that you can put this list in a database table, and then if oyu have customers enter an ACH number on your website, you can validate that the routing number they gave you is an actual bank that exists.  Yes, you can calculate a checksum on a routing number, but this is one step better.

So how would we import this file into Oracle using an external table.  Simple, the definition is below.  
What is also interesting about this is that we can use the NULLIF operator.  Banks that have underwent mergers have a new routing number assigned, but for most banks this is "000000000", so we can use the NULLIF operator to translate this value to NULL in our external table.  The same is true with zip code.


 CREATE TABLE fedwire_ach_file_import
(
    routing_number           VARCHAR2(9),
    last_name                VARCHAR2(1),
    servicing_frb_number     VARCHAR2(9),
    record_type_code         VARCHAR2(1),
    change_date              DATE,
    new_routing_number       VARCHAR2(9),
    customer_name            VARCHAR2(36),
    address                  VARCHAR2(36),
    city                     VARCHAR2(20),
    state_code               VARCHAR2(2),
    zip_code                 VARCHAR2(5),
    zip_code_extension       VARCHAR2(4),
    telephone                VARCHAR2(10),
    institution_status_code  VARCHAR2(1),
    data_view_code           VARCHAR2(1)
)
ORGANIZATION EXTERNAL
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_import
    ACCESS PARAMETERS
    (
      RECORDS FIXED 157 
      LOGFILE data_import:'fedwire_import.log'
      BADFILE data_import:'fedwire_import.bad'   
      FIELDS    
   (
          routing_number           CHAR(9),
          last_name                CHAR(1),
          servicing_frb_number     CHAR(9),
          record_type_code         CHAR(1),
          change_date              CHAR(6) DATE_FORMAT DATE MASK "MMDDYY",
          new_routing_number       CHAR(9) NULLIF new_routing_number = '000000000',
          customer_name            CHAR(36),
          address                  CHAR(36),
          city                     CHAR(20),
          state_code               CHAR(2),
          zip_code                 CHAR(5),
          zip_code_extension       CHAR(4) NULLIF zip_code_extension = '0000',
          telephone                CHAR(10),
          institution_status_code  CHAR(1),
          data_view_code           CHAR(1),   
          filler                   CHAR(5)
      ) 
 )
  LOCATION ('FedACHdir.txt')
)
REJECT LIMIT UNLIMITED;  

Sunday, August 24, 2014

Oracle 12c Data Dictionary Views

Oracle contains a wealth of information in its data dictionary views. I often find it is quicker and easier to query these views directly rather than going through a GUI in a tool like SQL developer. It is also possible to develop your own queries against these views to track conditions you care about.

 The data dictionary views are well documented by Oracle. Still though, with there being so many of them, I think it would be useful to have a quick reference to the most commonly used views. So that is what this post is all about. I've created easy to remember bit.ly links to each view, but feel free to bookmark this page as a condensed table of contents.

View Name Documentation Link
ALL_OBJECTS http://bit.ly/Ora12cAllObjects
ALL_TABLES http://bit.ly/Ora12cAllTables
ALL_TAB_COLS http://bit.ly/Ora12cAllTabCols
ALL_CONSTRAINTS http://bit.ly/Ora12cAllConstraints
ALL_INDEXES http://bit.ly/Ora12cAllindexes
ALL_IND_COLUMNS http://bit.ly/Ora12cAllIndColumns

Monday, July 7, 2014

SQL Server Summary Sheet for Tonight's CR-INETA Meeting

I'll be speaking at the Cedar Rapids .NET users group tonight.  Attached below is a summary sheet that contains all of the information that I will show, so there is no need to try to write everything down during the presentation.  Feel free to download this PDF, make copies or hand it out to your coworkers.  Its designed to be a quick reference of useful tips when working in a SQL Server environment.


Sunday, June 29, 2014

Using Pluralsight to Help Your Team Learn and Grow

This last week, Pluralsight published my first course title Oracle Performance Tuning for Developers.  But I am not just a Pluralsight author.  I use Pluralsight for my when I need to learn something and as the goto resource for when someone on my team needs to learn something.

I have managed an application development team for 3 years now.  One of my core beliefs is that as someone's leader, it is my job to help them grow and learn.  Learning is not confined to just technology skills, but at its core, application development is an engineering discipline, and there is just no way to escape the fact that you need good technology skills.

Often times you will see people who are 10 years into their career, but the work they do is that of a junior level software engineer.  They don't know about breaking code into different layers, using design patterns or how to distribute responsibilities between different components.  Sadly, too many people are 10 years into their career but don't have 10 years of experience.  They have just repeated their first year 10 times.

I do firmly believe that every professional needs to take responsibility for their own development.  No one can learn for you.  You have to make an investment in yourself if you want anyone else too.  At the same time, too many IT leaders are just worried about meeting project deadlines or insuring the next release doesn't blow up to devote the time to making sure their people get the skills development they needs.  This is unfortunate and only contributes to people gaining another year of tenure but not of experience.  Oh yes, we fill out an individual development plan every year in January.  But then it is stuffed in a drawer not to be seen again until the following January.

Sound familiar.  Unfortunately this is all too common.

A Better Approach

A few years ago, if you wanted training, you would find a class at a learning provider in your area and sign up.  This involved being out of the office for about a week and the cost was generally somewhere between $3000-$5000.  While this is still the best way to learn some things, there are several shortcomings to this approach:

  • You are taking a course on someone else's schedule.  Too bad you need to know how to write a WCF service next week.  That course isn't offered again for another three months.
  • The cost.  Sadly, most organizations budget very little per employee for training, and certainly not $3000+.  So the result is often times only a handful of employees get to go, or maybe none at all.
  • The throughput.  Because of time away from work and the cost, you are effectively limited to taking one course a year.  The problem is that the way software development has moved over the last 10-15 years, you need much more than that.
I've always been a fan of the Pluralsight model.  Long before I auditioned to become an author, I had my own Pluralsight subscription.  Even 3 years ago, when the catalog was much smaller, there was still more training than I could ever hope to watch.  The training is up to date on current subjects, and I can watch as much or as little as I need to in a given week.  All for $50 a month.

Making Pluralsight Work For Your Team

The purpose of this blog post isn't to be an advertisement for Pluralsight.  It is about how to make Pluralsight subscription work effectively for your team.  This is all about outcomes.  If all you do is buy subscriptions for your team and say "go at it", you are most likely not going to get the outcome that you want.

What I have done with my team is come up with a personalized development plan for each person, based on where their current skill level is at, where their interests lie and what our needs as a team are going forward.  I then break this down into the exact courses to watch and a schedule of when they are going to watch what modules.  Generally, everyone is assigned to complete two hours of training a week.  I tell people to block that time out on their calendar to make sure that they have time reserved to complete the training.

Here's a sample of what a plan might look like for someone who is new to C#.

Employee: Joe Learner
Course Module Time Complete By
Accelerated C# Fundamentals An Introduction to C# 53 minutes July 7, 2014
Accelerated C# Fundamentals Classes and Objects 49 minutes July 7, 2014
Accelerated C# Fundamentals C# Types 59 minutes July 14, 2014
Accelerated C# Fundamentals C# Events, Types and Methods 55 minutes July 14, 2014
Accelerated C# Fundamentals C# Flow Control and Exceptions 49 minutes July 21, 2014
Accelerated C# Fundamentals C# and the CLR 52 minutes July 21, 2014
C# Collections Fundamentals Introducing C# Collections 31 minutes July 28, 2014
Accelerated C# Fundamentals C# and Generics 46 minutes July 28, 2014

Part of the reason for doing this is to provide everyone with some structure.  Spelling things out like this, printing it out on a page and handing it to someone helps to clarify exactly what the expectations are.  And now this person knows exactly what they need to do.  This is much better than making a vague statement like "Check out some of the courses on C#.  I think there are some good ones out there."

Then, each week in everyone's weekly one on one, we devote about 30 minutes to discussing the training they completed the prior week.  Doing this every week has several effects.  
  • First, it puts front and center that personal development is a goal, for the team and each individual.  This constant reinforcement helps everyone to see personal development as a habit, not something that you just do occasionally.  This is essential.  We want to create an environment where we are in the mode of constantly sharpening our saw.  We want to make learning part of who we are, our identity.  Following up every week helps reinforce that notion
  • It allows each person to ask questions about things they didn't understand.  Don't get me wrong, there are lots of great courses, but nobody understands everything 100%  the first time.  Maybe there is a concept that just didn't resonate with that person while watching the video.  No problem, that is what I am here for, to help clarify those concepts and cement what has been taught.  I tell everyone take notes during the videos--just like when you were in college.  And write down what you don't understand.  Then we can go through it together to make sure that you are mastering the material, not just checking off courses you have been watched.
  • It allows me to ask some probing questions to see how well someone understands the material they just watched.  My purpose here is not to be cruel or show someone up, but to measure understanding and reinforce concepts I have found to be especially important.  Sometimes someone thinks they understand a concept, but in reality, they are a little fuzzy on it and don't want to admit it.  Sometimes they think they understand but maybe they don't really understand to the depth that they need to.  This is OK.  I want to identify those things so we can work through them now.  Maybe that will be scheduling some extra time with someone to go over the concepts again, but I would rather find out now than in 6 months when we are doing a code review.  This also gives me an insight into if we need to speed up or slow down for each person.
  • We have also started to incorporate some coding exercises for some core concepts that we want everyone to learn.  Much like an assignment in college, we give everyone a piece of code and have them put together a solution.  Then we'll talk through that solution in a future one on one.  Sometimes, people find things we a whole lot easier when the Pluralsight author did them in the webcast than when they try to do them on their own.  Again, this is OK.  It gives us a way to find out what the blocker is for someone and help get them over that hurdle.  Again, the outcome we are seeking is mastery of the material so they can apply it in their other projects.
This does take a lot of time, and when I say that, I am talking about my time.  I myself watch every webcast that one of my people watches so that I can effectively speak to what the author said and help clarify if need by.  It also means that I will spend some extra time outside of one on ones helping people in extra sessions when something doesn't make sense.  Is it worth it?  The answer is a resounding YES.  Again, one of the core requirements of my job as an IT leader is to develop my direct reports.  In many ways, this is my most important deliverable to the organization.  Leaders are supposed to be multipliers.  This is one of the ways I am a multiplier for my organization, by growing the next crop of software developers who have the skills we need.  I really couldn't think of anything that is would be more important for me to do most weeks.   

So what do my people think?  I think you are always afraid that when you push something as a leader, you will come across appearing heavy handed.  As it turns out, our people love it (I say our people because one of my fellow leaders is doing the same thing with his team).  People say that in years past, they had learning goals, but even when they completed them, they never felt like they really learned something.  Now, having someone to discuss what they learned with and doing it right after they viewed the material really reinforces those lessons.  Multiple people have told me that the constant follow up is the best part.  

As it turns out, it is also a exercise that builds trust within a team.  Sometimes, someone doesn't know how to do something, but they are afraid of admitting that to their manager because they will get yelled at or even worse.  But through this experience, one of the things your direct reports learn is that you really care about their development.  They see the time invested in them and they see that this is about making sure they are learning, so it becomes easier for them to talk about areas they don't feel comfortable.  Part of creating a learning culture is letting people open up about what they don't know so you can put a plan together to help them over that hurdle.  Working together to identify and resolve these items helps build trust in a team.

Putting a Plan Together for Your Team

Usually one of the hardest things to do is to get started, so if you are still with me at this point, hopefully you are motivated to get started with your team.  What I suggest is putting a plan together for each person that encompasses about 3-4 Pluralsight courses such that you end up with about 20 hours of video to watch.  This will end up taking about 3 months because some weeks people may only have time for one hour, people are out on vacation or a myriad of other factors.

What is important though is that you and each of your team members spend some time on learning each week and discuss it on a regular basis.  If you want, you could discuss every two weeks rather than weekly.  I wouldn't go a month, because that is too long and after all, part of what we are trying to do here is create a habit.  So commit to having everyone watch at least an hour of courses a week and follow up with them to see what they learned and how they are doing.

What if you are in a leadership position but you aren't technical?  In that case, have one of your senior developers or an architect review the material with each of your developers.  This may require reaching out of your group to find this person who can mentor, but it is as important to have that follow up with each person as it is to watch the courses in the first place.  Make sure you select someone who is a good mentor and can clearly explain things to keep the experience positive for each of your direct reports.  The most important thing is that your people have someone they can go to when they have questions.

I think what you will find is that people will embrace learning.  Teams often take on the personalities of their leaders, so if you as a leader put a priority on learning, team members will too.  Most people will be impressed at the investment that you are making in them to grow their skills.  And that is the outcome we are after.  Better developers who have better skills who can take on larger and more complex projects and produce better results.

That is what has worked for me.  If others have any other ideas, I would be interested in to hear about them in the comments section.

Saturday, May 17, 2014

Granting Developers Access to Dynamic Performance Views




CREATE ROLE perf_viewer;

GRANT SELECT ON V_$LOCK TO perf_viewer;
GRANT SELECT ON V_$LOCKED_OBJECT TO perf_viewer;
GRANT SELECT ON USER_OBJECT_USAGE TO perf_viewer;
GRANT SELECT ON V_$SESSION TO perf_viewer;
GRANT SELECT ON V_$SESSION_LONGOPS TO perf_viewer;
GRANT SELECT ON V_$SESSMETRIC TO perf_viewer;
GRANT SELECT ON V_$SESSTAT TO perf_viewer;
GRANT SELECT ON V_$SQL TO perf_viewer;
GRANT SELECT ON V_$SQLAREA TO perf_viewer;
GRANT SELECT ON V_$SQLSTATS TO perf_viewer;
GRANT SELECT ON V_$SQLTEXT TO perf_viewer;
GRANT SELECT ON V_$SQLTEXT_WITH_NEWLINES TO perf_viewer;
GRANT SELECT ON V_$SQL_PLAN TO perf_viewer;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS TO perf_viewer;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO perf_viewer;
GRANT SELECT ON V_$SQL_WORKAREA TO perf_viewer;
GRANT SELECT ON V_$STATNAME TO perf_viewer;
GRANT SELECT ON V_$SYSSTAT TO perf_viewer;
GRANT SELECT ON V_$TRANSACTION TO perf_viewer;

GRANT perf_viewer TO <<user name>>;



Monday, May 12, 2014

E-R Diagram for Sample University Database in My Pluralsight Course

I always like to have an E-R diagram handy whenever I am dealing with a database, and maybe some of you do as well.  If that is the case, there is the E-R diagram that I use in my Pluralsight course "Oracle Performance Tuning for Developers".  It should print on legal paper, so you shouldn't have too much trouble if you want a hard copy.

E-R Diagram for University Schema

Sunday, May 11, 2014

Finding Unused Indexes in Oracle

One area I think SQL Server really does a better job than Oracle is in the DMV's around indexes.  SQL Server makes it very easy to see what indexes it thinks are missing and also gives you the DMV dm_db_index_usage_stats which reports how often an index has been used and how often it has been updated.  Of course the point here is to find indexes that are not used and drop them and evaluate indexes that are only infrequently used and see if there is really a justification for those indexes to exist.

Oracle does make a lot more information available via AWR, but AWR requires you to have a diagnostics pack and tuning pack license.  So not everyone is going to have those.

We can use regular V$ views though to recover some of this functionality.  The following query uses the V$sql_plan table to find all the usages of an index, and then joins that information to the all_indexes view.  So we can see what indexes are getting used, but what statements and what indexes aren't getting used with this query.


 WITH index_usage AS
(
    SELECT pl.sql_id, pl.object_owner, pl.object_name, pl.operation,
            pl.options, count(1) as use_count
        FROM v$sql_plan pl
        WHERE pl.operation = 'INDEX'
        GROUP BY pl.sql_id, pl.object_owner, pl.object_name, pl.operation, pl.options
)
SELECT
        ix.table_owner, ix.table_name, ix.index_name, iu.operation,
        iu.options, ss.sql_text, ss.executions
    FROM all_indexes ix
    LEFT OUTER JOIN index_usage iu
             ON ix.owner = iu.object_owner
             AND ix.index_name = iu.object_name  
    LEFT OUTER JOIN v$sqlstats ss
        ON iu.sql_id = ss.sql_id
    WHERE ix.owner = '<<owner name>>'
    ORDER BY ix.table_name, ix.index_name;

Now this query is not perfect.  Since it is using v$sql_plan, you are limited to what statements are in the shared sql area right now.  But it does give you a start.  And you could always schedule this query to run on an hourly basis and dump those results to a table.  After a few days, you would have a pretty good idea of your index usage patterns.