Friday, February 20, 2015

ASP.NET Response Headers and Unnecessary Information Disclosure

I've been listening to Troy Hunt's Pluralsight course entitled "Hack Yourself First: How to go on the Cyber-Offensive" and it has been pretty interesting.  One of the topics he covers is how ASP.NET actually discloses quite a bit of information on every request view HTTP headers.  What do I mean?  Pop open the developer tools on your browser, make a request to an ASP.NET website, and more than likely, you are going to see something like this:


This is a capture from a test app I had on my local IIS instance, but notice what is circled in red.  By looking at the response headers, you now know exactly what version of IIS the site is running, the exact version of the .NET Framework and the exact version of MVC.

Just so you don't think this is just an artifact of hitting the localhost address or something I conjured up, go to http://msdn.microsoft.com/ and use your developer tools to inspect the response headers.  This is what I get.



By default, for any ASP.NET site, all of this information is made available.  So what is the problem with that?  Well, a lot actually.  Now a potential attacker knows exactly what version of software you are running.  And they can use this information to understand what vulnerabilities have been reported against that version of software and use this information to refine their attack vectors.

How do you do this?  You can use a CVE (Common Vulnerabilities and Exposures) database, of which are freely available on the web.  For example, there is one at https://cve.mitre.org/cve/index.html, and a Google search will review numerous others.  These databases are used by security professionals to share information about vulnerabilities and measure risk exposure.  Unfortunately, they can also be used by the bad guys to understand what vulnerabilities exist in a product and plan an attack against that product.

So for an example, an attacker learns from your response headers that you are running IIS version 7.0.  Now they can go look up all the vulnerabilities in version 7.0 and craft an attack around each one.  If you have been negligent about patching, you will more than likely be exploited in such an attack.

But wait, it gets worse.  Mr. Hunt points out in his course that there are search engines out there that don't index documents and web pages, but rather devices connected to the Internet.  Once such example is http://www.shodanhq.com/.  Using such a search engine, someone can easily easily find a list of sites that are running a particular version of software.

So lets say that there is a known exploit in a particular version of ASP.NET.  What an attacker can do is get a list of all of the websites running that vulnerable version and then script an attack against all of those sites.  Sure, some of those sites may have been patched or otherwise mitigated the threat so the attack does not work.  But some of those sites will be vulnerable.  The more recent the exploit, the more sites that will be vulnerable because there will have been less time for patches to be applied or the threat to be mitigated otherwise.

All of this goes back to these headers, which making publicly available information that there is no legitimate use for.  What version of IIS, ASP.NET or MVC you are running makes zero difference to the browser.  These headers are purely informational.  The problem is that this information can be used against you.  So the best course of action is to turn these headers off.  Just like in the TV shows, if you are given the right to remain silent, then remain silent.

Turning Off Unnecessary Response Headers

I will first of all say that I really, really wish Microsoft would turn these headers off by default in future versions of IIS and ASP.NET.  That is what secure by default means.  Out of the box, we don't do things that could compromise security.  But that may never happen, so in the meantime, we have to resort to other means.

There are two blog posts I want to point out tat also contain information on this, and they are worth a read as well.
Both Mr. Hunt and Mr. Mitchell take the approach of disabling The X-AspNet-Version header in the config and the X-AspNetMvc-Version via setting a property in code.  I'm taking a different approach here and I am going to remove the X-AspNet-Version, X-AspNetMvc-Version and Server headers all in an HttpModule.

The reason why I am going to remove all three headers in a module is because I think it is useful to build a module that can applied at the machine level in IIS rather than individually to applications.  My reason for this is that it is too easy for someone to forget to turn some of these headers off when creating a new application, and then we are right back to the unnecessary information disclosure problem.  I want something I can set up when I build an IIS machine, and then I know it is being applied to all applications on the server.  This is just a different approach I am taking.  Ultimately, you have to decide what works best for you and your environment.

So how do we develop an IIS Module to do this?  It is actually crazy simple.

1:    public class RemoveServerHeadersModule : IHttpModule  
2:    {  
3:        
4:      public void Dispose()  
5:      {  
6:    
7:      }  
8:    
9:      public void Init(HttpApplication context)  
10:      {  
11:        context.PreSendRequestHeaders += context_PreSendRequestHeaders;  
12:      }  
13:    
14:    
15:      void context_PreSendRequestHeaders(object sender, EventArgs e)  
16:      {  
17:        var headers = HttpContext.Current.Response.Headers;  
18:    
19:        headers.Remove("Server");  
20:        headers.Remove("X-AspNet-Version");  
21:        headers.Remove("X-AspNetMvc-Version");  
22:      }  
23:    }  

You just write a class that implements the IHttpModule interface.  And then, the event you want to act upon is the PreSendRequestHeaders event.  Then, it is just a matter of removing the unnecessary headers as you see.

One thing that you will notice is that this code does not remove the "X-Powered-By" header.  That is because this response header is set in IIS after ASP.NET processing is completed.  If you want to remove this header, you have to do so in IIS Manager.  How to do so is covered in both of the blog posts mentioned above, so I won't repeat how to do it here.

So now we have to add this module to an application.  You do so with the following config element (this assumes an assembly name of WebSecurity -- adjust for whatever you call your assembly or locate the module).

1:   <system.webServer>  
2:    <modules runAllManagedModulesForAllRequests="true">  
3:     <add name="RemoveServerHeaders" type="WebSecurity.RemoveServerHeadersModule, WebSecurity" />  
4:    </modules>  
5:   </system.webServer>  

All of this works just fine.  We could include the class above in one of our applications, include the config element in our Web.config file and we are off and running.

As I alluded to earlier, I wanted to be able to apply this site wide.  So how do we do that?

  1. You need to be using an App Pool with the IIS Integrated Pipeline.  This is so that we can use an HttpModule.
  2. Create a class library project in Visual Studio that will contain the HttpModule written above.  Note that I had to create this as a .NET 3.5 module, because for my version of IIS (IIS 7.5), it only seems to see .NET 2.0/3.0/3.5 modules in the IIS configuration.  When I created the assembly as a 4.0 assembly, IIS would not pick it up.  Again, I've only tried this on IIS 7.5, so later versions may be different.
  3. Code the class above in the class library project you just created
  4. Configure the class library to be strongly named (right click on the project --> Properties --> Look in the Signing tab).  We need to do this because we will have to put the assembly in the GAC, which requires the assembly to be strongly named.
  5. Compile the project.  
  6. Get the release version of the DLL and add it to the GAC using the "gacutil -u YourAssemblyName.dll"
  7. Configure IIS to run the HttpModule for the entire site.  To do this, go into IIS Manager, click on the name of the server (item #1 in the diagram) and then double click on the Modules icon (item #2).  This will bring up all of the Http modules that are configured server wide, and you can add your module as a managed module from here.


If you are wondering, the configuration file that IIS uses for machine wide configuration is the ApplicationHost.config file, which is located in %WINDIR%\system32\inetsrv\config directory.  If you want to, you can also edit this file directly to add in the module.

What I found is that I needed to then stop and restart IIS for the module to take effect.  Of course, it is always good to test things out and make sure things are working as you expect them to.


Am I Safe Now

You have probably already thought of this, but there are other ways someone can figure out if you are running ASP.NET, the most obvious being the aspx extension applied to pages in Web Forms.  And that is true.  And if they know you are running ASP.NET, its a pretty safe guess that you are running on top of IIS.

But at least now someone does not have detailed information about exactly what version of software you are running.  And this is the point.  We don't want to give away any more information than we have to.  We want to make things a little harder for a potential attacker and not hand them information about our site on a silver platter.

Of course, I would always advocate that you make these changes in DEV first, then QA and finally move them to your production environment.  But take a little time in your next sprint to either build this module into your site or otherwise turn off these headers.  It takes just a few minutes, and it helps keeps private information that should have never been available anyway.



Sunday, February 15, 2015

Your User's Connection Speed Is Not as Fast as You Think

This last weekend, we took a quick getaway as a family to Wisconsin Dells.  The resort we stayed at offered free WiFi, which is always good.  When we checked in around 1:00 PM, the hotel was not very busy, but I did notice a steady stream of guests at the front desk throughout the day.

Around 5:00 PM, I was trying to use my phone to get online, and I noticed everything was painfully slow.  Web pages took 30 seconds or more to load.  The voice recognition built into Android would not work at all.  Trying to browse items in the Amazon app resulted in this.



It didn't take long to figure out what was happening,   My network connection speeds through WiFi were painfully slow.  Unusable slow.  I did manage to get a speed test app downloaded somehow and ran several tests just to quantify what I was seeing.  Here is what I got.





What you are seeing is correct.  The speed I was getting on WiFi was about 2 Mbps.  That reading of 3.17 Mbps was taken at 5:11 AM, when most everyone in the hotel should have been asleep.  Yes, the resort offered free WiFi, but the quality of that service was effectively unusable, at least during my visit.

This is not my only experience with free or even paid WiFi being so slow that it was effectively worthless.  A few weeks ago, I was at a retailer that offered free WiFi, but when i tried to stream a YouTube video (on web performance coincidentally), I would get abut 10 seconds of video followed by 30 seconds of buffering.  After a few minutes I gave up.  And last year while staying in Chicago, I paid for WiFi at a hotel that ended up being so slow that every web page took 30 seconds or more to load.

Why is this?  My guess would be that there are simply too many users for the size of the connection the business has.  If a hotel has 300 rooms, it would take a connection of 3 Gbps such that every room would have an equivalent of a 10 Mbps connection.  Of course you may have multiple people staying in a room, which means multiple devices.  And it isn't a stretch to say that a number of those people may be trying to stream video to those devices.  Combine this with other users who are streaming audio, users who need to VPN back to their company and general web usage, what we have is a demand for much more bandwidth than is available.

When we are in our workplaces developing web sites, we often have very fast internet connections, because a slow connection would be unproductive.  And at home, as technology professionals, we probably tend to pay for faster connections than the average consumer because we make heavy use of online services. Although even at home, we don't always get the speeds we expect.  My internet package is "up to 15 Mbps".  Focus on those words "up to".  Here is the speed I got when I tested last night at home:



A download speed of 9.43 Mbps is not bad, but it is still 33% below the rated speed of my package.  And I also performed this test when I knew that no one else in my household was performing any Internet activity like streaming Netflix or something like that.

The point is, you can't always assume that someone has an ultra fast internet connection that is able to download images, video and other content in a matter of a couple of seconds.  They may be on a WiFi connection which is shared by 10's or even 100's of users.  They may be viewing your page in the evening, when many other users are streaming content, saturating network connections of their ISP.  Or maybe they are viewing your site over a slow cellular connection.  Whatever the case, there is likely to be a significant number of your users that are accessing your site on a connection with suboptimal performance.

What Can We Do About It?

I wish tomorrow that Google Fiber would magically roll out across the entire United States, no wait, the entire world and fix this problem for us.  Since that is unlikely to happen, we have to take action as web developers to make out pages work better for these users with slow connections.  Those users are our customers too, and their experience matters like everyone else's.

In a blog post last week, I included this graph from the HTTP Archive that showed that the average size of a webpage was now approaching 2 MB.


If we leave out the time it takes the server to respond, DNS lookups, any network latency and the time to render a page, the act of simply downloading a 2 MB page on my (effective) 2 Mbps water park resort connection is 8 seconds.  A 3 MB page would be 12 seconds just for download time and 4 MB would be 16 seconds.  Again, those numbers are just the pure download time, not the total amount of time it takes to render a page.  Eight seconds is not a good user experience.  Sixteen seconds (or longer) and most user's will be giving up.

So we have to carefully pay attention to the size of our web pages.  Every modern browser contains built in developer tools with a network tab.  Open these tools (usually by hitting F12),  go to the network tab and reload the page.  And I suggest you disable caching or clear your cache before doing this, so you can get a feel for what a new user might be looking at needing to download to view each one of your pages.  Below is the output from Google Chrome when viewing the Pluralsight homepage.


There is a lot of good information here.  In the lower left hand corner, we have the total size of the page (792 KB in this case).  We can also click on the "Size" column header to sort the files that make up this page by size (you will actually need to click it twice to get a descending sort).  So we can easily identify the heaviest resources on the page.

We also have a series of buttons like 'All", "Documents", "Stylesheets", "Images" and so on in the top part of the panel.  Clicking on one of those will give display only that file type in the list.  And when we do that, the stats in the lower left hand corner will update to only show the summary for the selected file type.  So in short order, we can understand how large our page is and what files are driving that size.

To address issues with page size, we want to make sure our images are optimized and appropriately compressed and that our JavaScript and CSS is mnified.  At the same time though, we want to think about the content that is on our pages and understand if it really makes sense to be there.  In designing websites, designers often focus heavily on the aesthetics of the site.  For a usable site though, these aesthetics need to be balanced with usability and performance.  Maybe some of that content could go on a separate page that the user clicks through to access.

Finally, I would recommend that you use Web Page Test to simulate access to your page at various network speeds.  This is important, because unlike you, your users do not sit down the hall from the web server serving up your content.  The default choice of a 5 Mbps down/1 Mbps up cable modem is a good choice to start with.  As you can see though, you can test all different combinations and define your own custom combinations:


The idea is that you can simulate users from various different locations and connection speeds.  And as we have seen, there is a great amount of variability in what connection speeds a user may be experiencing at any given time.  With web page test, you can experience what they experience and design your page that even a user with a relatively slow connection speed will still have a reasonable experience in viewing your page.

Summary

Poor network connections are a fact of life that is unfortunately not going away any time soon.  As web developers, we have no control over the connection that a user finds themselves on.  yet that doesn't mean we can't be proactive about addressing the issue.  We have tools that can tell us how large our pages are. We can make sure that our resources like images, CSS and JavaScript are completely optimized so the user doesn't have to wait for unnecessary bytes to be downloaded.  And we can take advantage of caching, to make sure a user doesn't have to re-download the same resource over and over again on every page they visit.  

Slow connectivity will always be frustrating, but by keeping mind that it does exist, and probably exists more often than we would like to admit, we can design sites that at least give a user a reasonable experience when visiting our sites.  

Wednesday, February 11, 2015

Caching Static Resources in IIS

When a user requests a web page from your server, your web server does not just have to serve up the page itself.  It also has to serve all of the images, JavaScript, CSS and other files needed to render the page.  And this can add up to quite a few bytes having to travel over the wire.  According to this graph at the HTTP Archive site, the total size of the average web page is almost 2 MB.


Data from HTTP Archive (http://httparchive.org/trends.php)


One of the most tried and proven ways to improve performance in all of Computer Science is by caching, and this applies to web performance as well.  By locally caching static assets that change relatively infrequently, we can transfer many fewer bytes over the wire on subsequent page loads, because we already have many of the assets we need cached locally.

Two Use Cases

Consider the following two use cases:

A user going from page to page during a single session within your site.
Usually, there is some content on each page that is the same.  Think of a company or site logo in the header.  This same image is probably used on every page within your site  Rather than having the user's browser download this same image over and over again for each page they visit, we want them to download the image once (on the first page they visit) and then use the cached copy for all of the subsequent pages they visit.

A user visits your site and returns a few days later to view information again
Lets say you are running some sort of web store or site that has some product information pages.  A user might visit your site today and then come back in a few days to double check some of the information or do additional research on that product.  It is possible that some information in that time may change.  But most of that information is static.  Think of product photos.  These are updated relatively infrequently, so what we really want is the browser to download these on the first visit to the page and cache them.  This way, in a few days when the user returns, the browser will not need to re-download these files, but instead just load them from cache.

Benefits

One of the benefits is obvious.  When the browser can use a cached copy of a resource, it doesn't have to wait for that file to load.  This makes the page load faster.

A second benefit though is reduced load on your web server.  Think about it.  Why should your web server be extending cycles and using up network bandwidth serving out the same, unchanged file over and over again.  This is wasteful and ultimately lowers the total number of users you can support per server.

Many people are tempted to think that caching is only important for public facing web pages, but it can be just as important for internally facing web apps.  You may have branch offices that have smaller network pipes back to your main office.  And as just pointed out, caching can save resources on your web server, which ultimately saves your company money.

Implementing Caching in IIS

You can turn caching on from IIS Manager, but all this really does is write some configuration data to the web.config file.  So lets just look at what that data looks like.


<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <system.webServer>
    <staticContent>
      <clientCache cacheControlMode="UseMaxAge" cacheControlMaxAge="30.00:00:00" />
    </staticContent>
  </system.webServer>
</configuration>

The critical parameter is cacheControlMaxAge.  This tells IIS to set a header on the response for when the file should expire in the browser's cache.  The format here is (days).(hours):(minutes):(seconds).  So in the example above, we would be caching static content for 30 days.

If you put this configuration section your root web.config file of your site, it will apply to all static content in your website, so images, plain old html files, JavaScript CSS files and any other static files.  If you want to have a more focused policy, then you can create a web.config file in a subdirectory of your site and drop the above text into that file, and the policy will only apply to static items in that folder and its subfolders.  So for example, if you wanted to cache images for 30 days as shown above, but not other file types, you would put this file in a subdirectory called images on your site that contains all of your images for your site.  What this allows you to do is to effectively define separate caching policies per subfolder on your site (and thereby by type if you keep things nice and organized).

Why would you want to do this?  I'll talk about that in a future post as well as how the browser will actually confirm that a resource has not changed when it uses a file from its cache.

Useful Links

IIS clientCache Parameter
http://www.iis.net/configreference/system.webserver/staticcontent/clientcache

Google Documentation on HTTP Caching
https://developers.google.com/web/fundamentals/performance/optimizing-content-efficiency/http-caching#cache-control

Tuesday, February 10, 2015

Passwords

I ran across an article today in USA Today about a security consultant who had released a database of over 10 million usernames and passwords that have been collected from various hacks over the years.   In releasing this database, his point is that these lists are already widely available on the Internet to malicious users, and so this is not really causing things to get worse, but creates a database that security professionals can use to analyze their password databases to see how secure they are.

I will agree that password lists of this variety are widely available on the Internet.  Here's how it goes.

  1. A list of usernames and passwords is stolen from website A
  2. This list is bought and sold on the underground Internet.  Think of this as an eBay style marketplace for malicious characters
  3. Someone writes a script to use the stolen username/password list to attempt to login to website B.
  4. Since many users re-use the same username/password combination, the malicious user is able to login to site B under your identity.
  5. Usually, they don't just stop at one site, but try hundreds of sites.
What do they do?  Perhaps it is to get personal information to use for identity theft.  Perhaps it is to transfer spam other accounts with some sort of marketing message.  Who knows,  But it is something you would not do with your account, and that is a problem.

There are a couple of root causes here.  The first one is that securing a site with only a password is actually a pretty weak form of security.  That is why you see companies like Google, Yahoo! and Twitter moving to two factor authentication where ever they can.

Second, way too many of us use the same password on multiple sites.  Even if we have a strong password, all it takes is any one of those sites to be compromised, and our once strong password is on a list somewhere being bought and sold on the shady side of the Internet.  And now, all it takes is someone with a little bit of scripting knowledge, and most if not all of the accounts we own can be compromised.

Securing Your Accounts

You need to take action now.  Not tomorrow, not next week, not next month.  Today.  Preferably now.

Get a Password Manager  -- You need to have a different password for each account.  Period.  This way if one site gets hacked, then your data on that site is compromised and not every site.  But you will never remember all of these passwords.  So get a password manager that will securely store all of the new, strong passwords you are about to create.

I myself like KeesPass.  The main reason I like it is that it lives on a USB stick.  I do not want to store my passwords in the cloud somewhere, because anywhere in the cloud can be hacked.  Sure, my PC can be hacked too, but the reward of hacking my PC is just getting my passwords.  The reward of hacking a cloud based password manager is getting a whole lot of people's passwords.  And if anyone has forgotten, anything in the cloud can be hacked.  I can take a USB stick out of my PC and secure it when it is not in use.


Change the Password on Your Most Critical Accounts -- This would be all of your email accounts, so Google, Yahoo!, Hotmail and the like, and accounts like Facebook and Twitter.  On most sites, if your password is changed or a significant event occurs, they email you.  But if your email is compromised, then a hacker can better cover their tracks.  Eventually you want to change all of your accounts, but these are the priority accounts to change.  And again, use your new password manager to generate all new, random strong passwords twenty or more characters in length.  You want to make things as hard as possible for the bad guys.

Turn on Two Factor Authentication Where Available --- Google, Yahoo! and Twitter all have it.  Usually this means that if you want to connect a new device to one of these accounts or perform a password change, they are going to send an SMS message to your phone so you can verify that indeed it is you making the change.  If you get an SMS message that you did not initiate, you know something is going on and you can react to it.

This is not perfect.  If someone gets a hold of your phone, they probably also have access to your email.  But this is much better than just having a password.  Its another mechanism to authenticate that it is really you logging in to your account, hence the name two factor authentication.

What Next?

These are your most critical accounts, but if you are like me, you could have 100 or more accounts out there.  So come up with a list and prioritize these accounts to change to all new unique passwords.  Try to do five a day for the next week.  Its not fun, it takes time, but it is less time than it takes to clean up from someone using your account for activity you did not authorize.

The point is, we all have to start being much more proactive about security.  Bad guys aren't going away.  People selling password lists are not going away.  Companies will still store passwords and other data insecurely.  But we cannot be the victim.  We have to do everything in our power as consumers to make it harder for the bad guys.  And when it comes to companies that do not securely store our information, we need to vote with our dollars to take our business elsewhere.


Sunday, February 8, 2015

What Statements Have the Worst Performance in my Oracle Database?

So you know that your database access overall is sluggish.  But where do you start?  Maybe you know certain actions in your application are slow, and you can figure out what SQL those actions run, and that is a good place to start.  But But maybe overall, everything seems slower than it should.  Or maybe you are taking over a new application.  For whatever reason, one of the really useful things that Oracle can tell us is what statements are taking the most time to execute and the most resources.

Whenever a statement is run in Oracle, Oracle keeps detailed statistics on the execution of that statement.  Better yet, Oracle makes all of this diagnostic information available to us.  This is one of the great things about Oracle.  It is a highly instrumented piece of software that gives us lots of information on what it is doing with our SQL statements and why.  And if we know where to look, we can analyze this information to understand why our statement ran the way it did and to know what statements we need to focus on for tuning.

The main view with this information is V$SQLSTATS.  Here is the query I use to get information about the most intensive statements in the databases I work with.

 SELECT * FROM  
 (  
   SELECT sql_id, sql_text, executions,   
     elapsed_time, cpu_time, buffer_gets, disk_reads,  
     elapsed_time / executions AS avg_elapsed_time,  
     cpu_time / executions AS avg_cpu_time,  
     buffer_gets / executions as avg_buffer_gets,  
     disk_reads / executions as avg_disk_reads  
   FROM v$sqlstats  
   WHERE executions > 0  
   ORDER BY elapsed_time / executions DESC  
 )  
 WHERE rownum <= 25;  

Basically, we are querying the V$SQLSTATS dynamic performance view for the execution statistics on all of the statements currently stored in our Shared SQL Area and then ordering them by elapsed time in this case.  Finally, the outside query will limit us to the top 25 rows, though you could change this to any number you wanted to.

In the results, you want to look for statements that use a high amount of CPU or perform a large number of logical reads (buffer gets) per statement execution.  These are statements that are using up a lot of resources each time they run, and statements that use up a lot of resource run slowly.  I also like to look at the number of times a statement has been executed.  Sometimes this is very revealing that a statement is running many more times than I expect, revealing a problem in the application.

One thing you want to know about this statement is that is is only looking at statements in the Shared SQL Area in Oracle.  Statements are cached out of this area using a least recently used algorithm, so if you run this query at noon, you may not see an expensive statement that ran as part of your batch process at midnight.  So it is a good idea to run this query at multiple times throughout the day and over several days.  In doing this, right away patterns will emerge about what statements are the most inefficient.  You can then concentrate your efforts on tuning these statements.

When looking at these inefficient statements, one of the things you will want to know is what execution plan the statement was using.  This way, you can tell if it wasn't using an index, was using an index that wasn't optimized to the statement or doing something else that was expensive.  You can retrieve this data from Oracle as well by using the following query and feeding in the SQL ID from the query above.

-- Displaying an execution plan
SELECT plan_table_output 
    FROM
    table(dbms_xplan.display_cursor(‘<<sql id>>',
        null,'typical'));

With these two queries, you will easily be able to walk up to any Oracle database and identify your top performance tuning targets to get to work on.

Wednesday, February 4, 2015

What SQL Statements are Currently Running in my Oracle Database

We are often times faced with the situation where we need to know what statements are running right now in our Oracle database.  Maybe we are having some sort of issue, and applications are not responding or responding very slowly, and you need to get some information fast about what is happening in your system.  One of the first things I always take a look at is what is happening in the database.  This may just be from experience, but on (too) many occasions, I've found the issue to be a long running, inefficient query that is consuming way too many resources.  In any case, one of the things I always want to do is narrow down what tier of the system the problem might be in.  So by scanning through the results of this query, I can quickly determine if the problem is in Oracle or I need to look somewhere else.

So here is the query:

 SELECT  
     s.sid, s.username, s.osuser,   
     s.machine, s.process, s.program, s.module,   
     q.sql_text, q.optimizer_cost,   
     s.blocking_session, bs.username as blocking_user,   
     bs.machine as blocking_machine, bs.module as blocking_module,  
     bq.sql_text AS blocking_sql, s.event AS wait_event,  
     q.sql_fulltext  
   FROM v$session s  
   INNER JOIN v$sql q  
     ON s.sql_id = q.sql_id  
   LEFT OUTER JOIN v$session bs -- blocking sessions  
     ON s.blocking_session = bs.sid  
   LEFT OUTER JOIN v$sql bq -- blocking queries  
     ON bs.sql_id = bq.sql_id  
   WHERE s.type = 'USER';  


What do I look for when I run this query?  First, statements with a high optimizer cost.  The cost is usually high because the statement is expensive.  So I check those out first.  I also look to see if there is a blocking session for a statement.  I have seen cases where someone created a lock and then everyone piled up behind them bringing an entire environment to a halt (fortunately this was is DEV, but still).  And finally, I'll run this statement a couple of times, and see if there are any statements that are still the same on the second or even third running of the query.  Most statements that are run (in an OLTP environment anyway) execute in sub-second time, so if a statement is sticking around over 10, 15 or even 30 seconds, I know it is trouble.

What I also get from the statement is enough information to find out who is running the statement.  Session id, Oracle user, OS user and machine are all there.  I've seen cases where someone ran a statement in production but forgot a join condition or didn't realize they weren't using an index.  From the data in the above statement, I usually am able to track down who it is so I can give them a call and get them to cancel the statement.

I usually keep a cheat sheet around of about 10 statements like this that I commonly use to diagnose what is going on with an Oracle database, and this is always on of the more frequently used statements in that list.  So put this in your own cheat sheet or bookmark this page so that the next time someone asks "Does anyone know what is going on in the Oracle database right now?" you will be prepared to find the answers.