Concepts and New Features in DB Optimizer 1.5
DB Optimizer is bundled with Power SQL. I see it as a 3 part application
- 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.
Profiling can be run to monitor, analyze and identify performance issues on a sql statement, a packaged procedure or the database as a whole
DB Optimizer can be used both as a tool for the
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.
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)
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.
- The Load Chart tells us how much load is on the system and if there are any bottlenecks.
- 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.
- 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.
- 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.
- Sessions: sometimes a particular session might be having issues and the top Sessions section identifies sessions that are very active or bottlenecked
- The Detail section last shows detailed information on any item clicked on the overview section such a SQL statement, Event or Session.
The load graph is the same with the addition of "Max CPU" line for Oracle.
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.
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.
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.
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 Text
* SQL Details
* Children Details