DBOptimizer 1.5

From DB Optimizer
Jump to: navigation, search

Concepts and New Features in DB Optimizer 1.5

DB Optimizer is bundled with Power SQL. I see it as a 3 part application

  1. Editing  (writing and debugging)

A rich SQL IDE with code completion, real-time error checking, code formatting, and sophisticated object validation tools helps streamline coding tasks.

  1. Profiling

Profiling can be run to monitor, analyze and identify performance issues on a sql statement, a packaged procedure or the database as a whole

  1. Tuning


  Edit profile tune.PNG

DB Optimizer can be used both as a tool for the

  1. Developer

The developer can write code, run it with the profiler to see verify the load the code puts on the system and if it the code needs top be optimized, it can be sent to the tuner.

  1. DBA

A DBA can evaluate performance of  a database quickly and easily with profiling.  Profiling with tell the DBA immediately what the load is on the system and if there are any bottlenecks. If there are bottlenecks on the system, profiler will identify whether it is an issue with the system configuration, the application or the SQL. If it is a problem with SQL that is inefficient, then the SQL can be sent to tuner for optimization. For optimization of SQL using hints, the DBA can  implement the hints with out touch the database by using stored outlines (on Oracle, Sybase and SQL Server coming in v2)


Editor

Profiler

width=729,height=618

 Profiler has a new look in v1.5.  The page is now divided into 3 sections showing information from the marco view down to the micro view. The idea is to see the macro view, the system level, on the "Load" chart at the top, then  in the middle, a course view of the top activity on the database in the "Top" section and finally at the bottom a fine grain view in the "Detail" section.

  Load tops details.PNG  

  1. The Load Chart tells us how much load is on the system and if there are any bottlenecks.
  2. The Overview section next tells us where the load is coming from, specifically what are the top SQL statements, top events that the database spends time in and who are the top sessions. By looking at the top players in each of these sections we can quickly see if there are any issues on the database.
    1. SQL: The database load is created by SQL being run on the database, so generally if there is high load on the databse we look at top SQL and tune them.
    2. Events: Tuning sometimes need to be done at the application or database configuration level and this can be seen by looking at the top events. If the top events are locks then the application logic needs to be looked at. If the top events are related to the database configuration then the database setup should be investigated. See XXX for more info.
    3. Sessions: sometimes a particular session might be having issues and the top Sessions section identifies sessions that are very active or bottlenecked
  3. The Detail section last shows detailed information on any item clicked on the overview section such a  SQL statement, Event or Session.

Load Chart

Load chart.PNG

The load graph is the same with the addition of "Max CPU" line for Oracle.

TOP X

The Top section gives a quick idea of where the load on the database is coming from. Normal IO and CPU load come from SQL statements, database configuration issues show up in the Events section, and any high activity users show up in the Sessions.

The overview tab summerizes all three areas, or one area can be looked at in more detail on the corresponding tab : SQL, Event or Session.

Overview

Tops.PNG

The overview tab of the overview section shows a quick glance of the major areas on the database SQL, Events, and Sessions. The overview tab shows at a glance if there are any outliers that could represent issues on the database. If  more detailed information is needed then one can click on the tabs for each of the three sections or if an outlier is found then it can be clicked on to see detailed information on that one item in the details section.

Top SQL

Top sql.PNG

The SQL tab shows more detailed information than the overview section. If an interesting SQL is found it can either be clicked on to show detailed information in the details section or it can be right clicked on to either show the explain plan or send it to SQL tuner tool in DB Optimizer.

Top Event

Top event.PNG

 

Top Session

Top session.PNG

 

Details

When clicking on any item in the "TOP X" section, details will be displayed in the details section for the selected time window

SQL     SQL Text      - text for the SQL statement     SQL Details   - details such as parsing information and execution statistics     Events         \- events the SQL statement had encountered     Sessions       - Sessions that have executed the statement     Children Details  - (Oracle) if the SQL statement has multiple children, details on the child cursors

Event     SQL            - SQL statements that have encounted this event     Session       - Sessions that have encountered this event     Raw Data    - the actual values sampled by the profiler for this event

Session     Session Details \- details such as username, when logged on etc     SQL                - SQL this session has executed     Event             - Events this session has encountered

SQL

   * SQL Text

Details sql text.PNG



   * SQL Details

Sql details new.PNG   

 * Events

Details sql events.PNG

 

   * Session

Details sql sessions.PNG 


   * Children Details

Details sql children.PNG

Event

    * SQL

Details event sql.PNG     * Session

Details event session.PNG     * Raw Data Details event raw.PNG Session

    * Session Details Details session details.PNG     * SQL Details session sql.PNG     * Events

Details session events.PNG

 

Tuner