Name: Kyle

Web Site: http://dboptimizer.com

Bio: Kyle Hailey worked on the complete redesign of the Oracle Enterprise Manager 10g performance pages. His contributions shifted the screens to be graphically oriented and wait interface centric and these successful designs have continued to be expanded upon in 11g OEM. Recently he worked at Embarcadero Technologies where he designed the first successful product the Embarcadero has written internally and released in the last 10 years called DB Optimizer. DB Optimizer is the first product in the industry to graphically describe the relationships of tables, views and subqueries of a SQL statement using Visual SQL Tuning (VST) diagrams. Currently Kyle works as a performance Architect at Delphix. He has long and distinguished career with Oracle having worked at Oracle in support, porting, benchmarking, and kernel development. He has also worked at Quest, Embarcadero as well as other companies in the industry on performance tuning and optimization of Oracle. He has designed tools to improve high end performance monitoring such as direct SGA attach and interactive graphic displays of performance data. He has speaks at Hotsos, NoCOUG, RMOUG, NYCOUG, Oracle World, DB Forum as well as teaches classes around the world on Oracle performance tuning.

Posts by khailey:

• Browser Size, Web Tools and the Fold line

  December 17th, 2009
  When i was working on OEM, one of my many pet peaves was the size of browser pages, wasted space and data below the “fold line”. Here is an image of Oracle’s Performance Page and the original mockup I did for the performance page (see the page before my designs verses after). In the mockup, I compress it so more data was above the fold, but unfortunately the size was bloated in the final version though more importantly my design ideas were mostly used verbatim.

  

  what is the fold line and how do you determine it? The fold line is the line at which data is off the default browser screen. I determined it in the above image based on my browser and screen at the time which was probably way bigger than mosts, you know, working at Oracle and having a nice laptop and all. Well now there is a better way to determine the fold line. Goolge has an application that will do it for you. Here is an example of Google’s application and my web page on DB Optimizer:
  
  Cool stuff! You can see percentage wise how much of the general view population sees of the web page above the fold line.
  Here is the application
  http://browsersize.googlelabs.com/

  1 Comment "

  Oracle Wait Events: os-thread-startup

  December 15th, 2009
  One goal I have over the next couple months is creating organized documentation on Oracle Wait events. One event, os-thread-startup, is not well documented and here is a good blog entry about one persons experience with it
  http://karlarao.wordpress.com/2009/04/06/os-thread-startup/

  1 Comment "

  Changing performance strategies for modern operations

  December 8th, 2009

  Three important factors to take into account in performance tuning of database operations:

  · Automation

  · Methodology

  · Collaboration

  Automation

  By automation I mean continuous performance monitoring across all the databases in the enterprise accessible from a single interface. Systems are too complex and DBAs are responsible for too many databases to efficiently monitor and maintain these systems without automation. Automation reduces costs and alarms notifications let DBAs quickly find and correct problematic SQL statements before they impact the bottom line.

  Methodology

  A standardized intelligent methodology that clearly prioritizes issues will simplifies and streamlines the processes of problem identification and solution, otherwise effort can be wasted on inconsequential issues.

  Collaboration

  Effective collaboration and healthy communication is essential to the efficiency of the enterprise. Development, QA and DBAs work together using Embarcadero’s products to easily share information (like profiling snapshots) for faster problem identification and assignment of work

  Changing Trends for Modern Operations

  I think most people would agree on the importance of automation and have either implemented automated systems or want to implement an automated system. On the other hand the areas of methodology and collaboration are often ignored. Without a methodology, DBAs can be struck with what my colleague Gaja wonderfully coined “compulsive tuning disorder” meaning they try to tune “nonexistent or irrelevant problems” as Christian Antognini put it in his book Troubleshooting Oracle Performance.

  Methodology

  Our methodology at Embarcadero with DB Optimizer is to first look at overall database performance with the system load chart which displays “Average Active Sessions” (AAS) as coined by my esteemed colleague John Beresniewicz) . If AAS goes above the max CPU line then there is a bottleneck. If there is a bottleneck, identify whether it is from

  1. Database configuration Issue

  2. Machine Sizing Issue

  3. SQL inefficiencies

  A database configuration issue is visible if the load in the load chart is coming from wait events that represent configuration issues such as redo log file switch or log file sizes.

  A machine sizing problem for example would be visible if the CPU spiked above the max CPU line, showing a bottleneck on CPU, and all the CPU usage was evenly distributed of all executing SQL, ie there is no resource intensive SQL. In this case the machine is undersized for the application.

  SQL inefficiencies would be visible, On the other hand, if there is a SQL statement that clearly more resource intensive than the average SQL on the system, then tune that SQL. In this final case of an inefficient SQL we would want to check

  1. Correct execution plan is chosen

  2. Indexes necessary exist

  3. Schema correctly defined such as unique constraints, FK/PK and not null constraints that help the optimizer

  Our tools, Performance Center and DB Optimizer provide this information quickly and easily.

  Collaboration

  A common toolset enables development, QA and DBAs to work together and share information more easily for faster problem identification and assignment of work

  Effective collaboration and healthy communication is essential to the efficiency of the enterprise. Too often teams such as Dev and DBAs use different tools and speak a different language. By using a common tool for performance tuning can Bridge the gap between developers and DBAs to quickly and clearly identify the root cause of the problem and who’s responsible

  With a common tool whose analysis can be save to a flat file, emailed it’s easy to share information.

  For example there might be a problem in the way the database is configured. If the configuration issue is found by a developer, then the developer can send that DB Optimizer session to the DBA and have it addressed. On the other hand, the DBA finds a problem with the query. The DBA can send that session to the developer responsible for that query. Finally, Q/A can run DB Optimizer and look for spikes in the load chart. If there is spike in load, then QA can mail the saved session to the DBA or developer.

  1 Comment "

  Taming the Performance Beast

  December 8th, 2009

  Presented with Forrester’s Noel Yuhanna. Noel presented on the trends that are effecting performance, why it is critical to maintain performance despite these trends, and he outlined some best practices for overcoming these challenges. I talked about real world steps to applying these best practices as well as a live demo of our products performance Center and DB Optimizer. The full presentation is available here:

  http://bit.ly/3eFtf9
  (NOTE: first fill out the form, then the presentation links for viewing and downloading will be emailed)
  Here is the quick snippet of the live demo:

  Embarcadero’s DB Optimizer and Performance Center from Kyle Hailey on Vimeo.

  No Comments "

  DB Optimizer 2.0 in the News!

  December 3rd, 2009

  Wow, I’m amazed and thrilled at all the press that DB Optimizer 2.0 is getting in the press. Check this out:

  SD Times
  Infoworld
  Codebeach
  DBTA
  Soft Dev Tools
  SQAZone
  Database Journal
  Information Management
  Database Journal
  Domain Trigger
  D-Zone
  ESJ
  Microsoft News

  No Comments "

  Advances in SQL Tuning Techniques at HOTSOS

  November 30th, 2009

  
  Late breaking news – I’ll be speaking at the 2010 HOTSOS conference!

  http://www.hotsos.com/sym10/sym_speakers_hailey.html

  Advances in SQL Tuning Techniques

  In the past few years some novel SQL tuning techniques have appeared such Oracle 10g’s SQL profiles, 11g’s SQL baselines , extended execution statistics, Tuning by Cardinality Feedback, Visual SQL Tuning, Oracle 11g’s real time SQL monitoring and plan stability. Find out what about these methods and how to best harness them easily and effectively on your system.

  Learning objectives:

  1) How to identify queries that are potentially suboptimal

  2) How to analyze the query

  3) What steps to take to come up with an optimal and stable plan

  HOTOS is my favorite Oracle conference both for the high caliber of the speakers as well as the participants. Here is the impressive speaker list for 2010:
  http://www.hotsos.com/sym10/sym_speakers.html
  I’ll also be teaching a 2 day Oracle performance and tuning seminar on the following dates and locations:
  Melbourne Feb 22-23
  Sydney Feb 25-26
  Singapore Mar 1-2
  Hong Kong Mar 4-5

  No Comments "

  Graphics vs Text : Picture superiority effect

  November 20th, 2009

  

  According to the picture superiority effect, concepts are much more likely to be remembered experientially if they are presented as pictures rather than as words.According to dual-coding theory by Allan Paivio (1971, 1986), memory exists either (or both) verbally or “imaginally”. Concrete concepts presented as pictures are encoded into both systems; however, abstract concepts are recorded only verbally.
  http://en.wikipedia.org/wiki/Picture_superiority_effect
  
  

  No Comments "

  Oracle’s Cardinality Hint, Profiles, Outlines and SQL Baselines

  November 19th, 2009
  This is more of a bookmark for a future blog post on using the Cardinality hint. This post by Greg Rahn give some example and I posted in the comments some more detailed examples:
  http://structureddata.org/2007/11/21/troubleshooting-bad-execution-plans/
  Here is another excellent post by Kerry Osborne on SQL Baselines:
  http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/
  which made me ask in the comments why one would want to use Outlines vs Profiles vs Baselines, in particular what advantages if any Profiles have.
  
  

  Interesting question. I had an online dialog with a guy name Randolf Geist a year ago or so where he convinced me to look closer at SQL Profiles. Up until that point I had only seen SQL Profiles that were created by the SQL Tuning Advisor and I was not very impressed. They tended to go sour after a little time because of the OPT_ESTIMATE hint, but I digress. The answer is yes, I think SQL Profiles have a couple of advantages over Outlines at this point.
  
  

  
  

  1. They have the ability to be used on multiple statements (ones that are the same except for literals) using the force matching signature bit.

  2. They are newer. I’m starting to worry about Outlines getting buggy due to lack of attention. Documentation says they are deprecated and although they continue to work in 11g, I am a bit leery of continuing to use them at this point. (the whole hint based mechanism is tricky enough as it is).

  11gR2 includes a procedure to migrate Outlines to Baselines by the way (DBMS_SPM.MIGRATE_STORED_OUTLINE). At any rate, I am not implementing Outlines at this point.

  No Comments "

  Explain Plan vs Real Plan on Oracle

  November 17th, 2009

  A good part of my presentation Friday at NoCOUG was on explain plan command being an estimate of the actual execution plan. Now on the Oracle-L forum list, I see the same question being asked “Under what conditions, could the plan I get out of ‘explain plan’ differ from what I get from running the sql statement?” and Tanel Poder giving a succinct clear list of reasons:

  main reasons:
  1) The optimizer statistics the EXPLAIN PLAN ends up using are different from the statistics the other session ended up using
  2) Explain plan does not use bind variable peeking thus will not optimize for current bind variable values
  3) Explain plan treats all bind variables as VARCHAR2, thus you ma have implicit datatype conversion happening during the plan execution, (meaning to_char,to_number functions are added around variables/columns) and this for example may make optimizer to ignore some indexes if you get unlucky.
  with the caveat:
  Little correction to point number 3 – of course explain plan doesn’t really execute the plan so the implicit datatype conversion you see is in the explained plan only, but if you actually execute the statement (with correct bind datatypes) then there’s no implicit datatype conversion. And that’s where the difference comes from…
  From the presentation Friday at NoCOUG:
  
  more examples on previous blog entry
  http://db-optimizer.blogspot.com/2009/09/explain-plans.html

  No Comments "

  Future of Cloud Computing and Relational Databases

  November 16th, 2009

  I was talking to someone from Amazon who indicated that Amazon is moving more and more processing away from Oracle into the middle tier and object oriented databases with the idea being to reduce usage of Oracle as well as to remove data processing delay times. The time it takes me as a consumer to put an order in can be reduced if Amazon just at takes all my information and says “purchase complete” and then process all the data and mails me later if anything goes wrong.

  Taking this idea farther, there are a lot of cases where processing doesn’t have to immediately be transactional. The transactional can be delayed in a lot of cases.
  One accutely relevant recent example has been the recent naked shares scandal which helped sink the US banks much faster and deeper. These naked shares are when brokerage houses sell shares of a company that they don’t really have. Normally a brokerage house would be transactional about buying and selling, but in this case the brokerage house can sell without actually owning the stock and buy the stock later. If the risk of this kind of inconsistency is smaller than the overhead a slower more expensive system insures consistency then why not go with the inconsistency?
  Of course in the case of the brokerage scandal the inconsistency might have been intentional and if so and if regulated would bring back a transactional system. There will always be the requirement for transactional systems guaranteeing the usage of relational databases.
  Interesting article on the subject (including quotes from me)
  http://www.sdtimes.com/content/article.aspx?ArticleID=33912
  

  No Comments "