MGA EagleEye 3.01

Download User Guide (contained in Zip file 433 kb)

NOTE: After downloading the zip file, unzip the contents to a folder before opening the html page in a browser as this will ensure that all the images will display correctly.

MGA EagleEye HTTP Replay User Guide 3.01

View in Microsoft Word format (1,843 kb)

View in PDF format (1,635 kb)

Download in Microsoft Word format (contained in Zip file 1,748 kb)

Download in PDF format (contained in Zip file 1,105 kb)

MGA EagleEye User Guide Quick Links:

ACTIVE buttons
i) Top Sessions
SQL*Worksheet
ii) Active I/O
iii) Active SQL Button

SUPPORTING INFORMATION buttons
i) Supporting Information - SQL Worksheet Button
ii) The Diagnostics Button
a) Slow SQL
b) Shared Pool Tuning
c) Table and Index Statistics
d) I/O Information with Fragmentation and Chaining
e) Sort Tuning
iii) The Suite Maintenance Button
Suite Maintenance screens
iv) The Robustness Checks
v) Locking Problems

SUMMARY

MGA EagleEye User Guide

MGA Eagle Eye, a product that was once used exclusively in house by one of the worlds leading Oracle tuning Companies, is now available to all Oracle sites.

As well as being one of the most attractive products on the marketplace to use, Eagle Eye has truly awesome power to assist you to tune your Oracle site and keep it tuned.

ACTIVE buttons

The "Active" buttons include (i) the button to view the sessions on your database that are currently using the most CPU, and Disk I/O resource (ii) the button to view the tablespaces and datafiles that are being most heavily used as well as the objects in the databases that are experiencing the most I/O waits and (iii) all SQL running right now along with its explain plan and other details.

     (i)          (ii)        (iii)

i) Top Sessions

Your users are complaining about performance and you need an express way to find out who is causing the problem and what SQL are they running. The Top Sessions screen display lists the users in descending order of resource usage. Eagle Eye provides you with an option to right hand mouse click on the troublesome session to turn Oracle SQL*Trace on.

The top sessions screen also has a bar chart to show you the Session Id that is utilising the most disk I/O and CPU on your database server. Perhaps there is a number of users hammering your database simultaneously. The graph will show you this at a glance. You have the option to remove the bar chart and list all user sessions, not just the highest resource users. You are also provided with the ability to specify your refresh interval as well as refresh manually if you desire.

The bottom portion of the screen shows the SQL statement that is running for the user session selected. If you leave the cursor on the user session, the SQL statement will change each time the screen refreshes.

SQL*Worksheet

A button is provided to allows you to view the explain plan details through the Eagle Eye SQL*Worksheet.

The SQL worksheet allows you view the explain plan. It also allows you change the statement and do a "what if" to get the new explain plan.

The SQL Worksheet also allows you to enter additional statements, for example, you may wish to count how many rows are in the RESULTS table or perhaps see the effect of placing a hint in a copy of the statement, but still preserve the original SQL.

ii) Active I/O Button

Pressing the active I/O button allows you to view I/O information by tablespace or datafile. It also provides you with information on the objects that are currently experiencing waits.

      (i)        (ii)          (iii)

If one of your tablespaces continually appears as the one experiencing the largest number of I/Os, there may be potential to place tables and indexes within the tablespace into an alternate tablespace. This is particularly true if the tablespace is on a disk(s) that is(are) experiencing waits from the operating system perspective.

The I/O examination can be further refined by determining which database objects the waits are occurring on. If the waits are on two objects that reside within the same tablespace, they are obvious candidates for relocation. Eagle Eye provides this information as another option on the Active I/O button.

iii) Active SQL Button

Pressing the active SQL button allows you to view all SQL currently running on your database. It is sometimes difficult to view this information effectively from the Top Sessions screen.

      (i)        (ii)         (iii)

The screen produces an Explain Plan in a similar format to the SQL*Worksheet. All Eagle Eye Explain Plan screens allow you to print a nicely formatted report that lists the SQL statement, along with the explain plan and other relevant details such as the statistics on the objects being referenced in the SQL and the indexes on the tables. You can also save the information into the clipboard, which allows you to copy and paste the information into your tuning report.

A nice feature on the Explain Plan is the ability to double click on the object involved. Eagle Eye produces a screen that lists all table columns, indexes and optimizer statistics on the table clicked on.

The screen provides you with a powerful arsenal to conquer the troublesome SQL statements without having to go from one tuning product to the next.

SUPPORTING INFORMATION buttons

The Supporting Information buttons provide you with a toolkit of functionality that allows you to run your SQL statements, test the robustness of the database, check for locking problems, and obtain a variety of tuning information to observe what SQL statements have exceeded designated runtimes. It also provides information on how effectively your database is set up from a performance perspective.

     (i)         (ii)        (iii)        (iv)        (v)

i) Supporting Information - SQL Worksheet Button

The SQL Worksheet button allows you to key in any SQL statement required to perform further research to resolve your performance problems. Some users like to use the worksheet in preference to SQL*Plus for a large variety of tasks.

The bottom part of the screen provides you with two sets of output. The first is the results from the query that you have entered and the second is the Explain Plan. As with the other Explain Plan outputs, you have the ability to click on the object being utilized and obtain all sorts of information, including the indexes on the table as well as the optimizer statistics.

You also have the ability to save all of your work into a file, print it, or place it into the clipboard, for transfer into the a word or other document.

ii) The Diagnostics Button

This button contains a variety of tuning information which can be accessed via a Sub menu provided when you press the button. Let's discuss each option.

a) Slow SQL

This button shows you all SQL that has exceeded a response time that you specify. The information is gathered from the Oracle's shared pool area in memory. You can select all SQL that exceeds a response time as well as further refining the selection to just obtain the SQL that contain certain characteristics, for example, a table name.

The Slow SQL display allows you to view the offending SQL statement, as well as the estimated response times and the number of times that the SQL has been run. As with other SQL displays, you can also view the explain plan details.

The reporting feature on the slow SQL screen allows you to print all or selected "slow SQL" into a nicely formatted output. The report contains the runtime statistics including response time as well as the indexes on the relevant tables, optimizer statistics and explain plan details.

b) Shared Pool Tuning

The shared pool option allows you to view the number of times stored packages, procedures, functions and triggers have been loaded into memory. If the various objects have been re-loaded multiple times, they are a candidate for pinning into memory using the dbms_shared_pool.keep procedure. You can also view the objects currently pinned in memory as well as seeing the size of the various objects to assist you with sizing the SHARED_POOL_RESERVED_MIN_ALLOC and the SHARED_POOL_RESERVED_SIZE parameters. The parameters are used to set up an area of the shared pool, which is especially set aside for large packages. Setting these parameters allows you to separate large code from small code and avoids greatly reduces fragmentation problems in memory.

c) Table and Index Statistics

This option shows you which tables and indexes have been analyzed and which ones haven't. Not analyzing correctly can cause incredible performance problems. The screen shows us a summary count of analyzed versus not analyzed by user. It also gives you the ability to drill down for the selected user and see the various optimizer statistics on a per table/index basis.

d) I/O Information with Fragmentation and Chaining

This option shows you the number of chained rows read since the database instance started as well as the individual objects that chaining has occurred on. Chaining often occurs on small tables, which can be easily re-built to remove the chaining problem. If you decide to re-build the table, you may decide to increase the PCTFREE on the table to allow more room for rows to expand in each block when the rows are updated.

Excessive fragmentation in your database can have a negative impact on your database performance. Having small extents all over the place can make object management of your database a nightmare. We recommend that you re-build your tables/indexes that are relatively small, say less than 200 Megabytes, and have a large number of extents. Don't forget to re-size your tables/indexes as part of the re-build.

e) Sort Tuning

Sorts take place in memory with each user being assigned their own memory allocation. Each user is assigned the amount of memory specified in the parameter SORT_AREA_RETAINED_SIZE by default. If the user sort requires more memory, it can be added for the user up to the SORT_AREA_SIZE parameter. If even more memory is required, the sort data is written to disk. Typical settings for the parameters are 65536 for SORT_AREA_RETAINED_SIZE and 1024000 for SORT_AREA_SIZE.

Oracle provides you with the ability to set up a tablespace on disk that is structured especially to provide high speed sort performance. You achieve this using the command "ALTER TABLESPACE tname TEMP;" It is useful if you set up your default initial and next extents in the tablespace to be a multiple of the SORT_AREA_SIZE.

It is also important that your users are assigned to your designated temporary tablespace. In the EagleEye display above, there are 14 users that are sorting in the SYSTEM tablespace which can be disastrous from both a performance and stability perspective. EagleEye also provides information on the appropriateness of the SORT_AREA_RETAINED_SIZE and SORT_AREA_SIZE. The product also provides information on how appropriate the default INITIAL and NEXT extent settings are on the temporary tablespaces used for sorting.

iii) The Suite Maintenance Button

One of the most exciting features of the MGA Eagle Eye product is your ability to run a suite that contains a set of standard queries that run against your schema and track their response times. If the response time is above a threshold of an acceptable response time, Eagle Eye gathers more information on who is logged on, what SQL they are running, as well as extensive information on the objects that were being waited for when the response time was unacceptable.

As mentioned, the Monitor screen shows you the response times compared to the acceptable response time. The red line in the graph is the acceptable response time threshold that you have set. The line graph shows the response time of the standard queries that you have set up. When the response time goes above the red line (acceptable response time), you can click on the graph to drill down to obtain more information on the cause of the jump in response times.

The drill down contains the SQL statement along with the explain plan and other relevant details. Often one of the SQL statements listed has caused the performance spike and needs to be tuned.

It also provides details on the objects that waits are occurring. You may have a chronic disk I/O and may have to split objects waited for onto different disks.

There is also a drill down provided that allows you to observe the individual response times of each of your statements. Perhaps an index has been mistakenly dropped of tables that you are accessing.

Suite Maintenance screens

To set up your suites and their associated SQL scripts, use the Suite Maintenance screens.

If you press the + button, you can enter the suite name as well as the acceptable runtime for the suite.

When the script is entered, you can select the script on the tree to enter one or more SQL scripts which you want to run within the suite. You will be requested to enter the name of the script as well as provide the SQL for the script. Typically the script must return the same number of rows and process the same amount of data each time it runs. It must resemble the type of SQL that your end users would typically run. Your applications most heavily used tables and indexes should be accessed.

When you enter the script, you will be given the opportunity to run it to observe the response time (see response time on bottom bar). The combined runtimes of all of your scripts will produce the information required to determine the threshold of acceptable response times. The industry standard for the acceptable runtimes is between 3 and 4 times the minimum combined runtime. If the minimum combined runtimes of all of your scripts was 20 seconds, the typical threshold of acceptable response time would be 60 seconds.

The robustness of your database is often confused with poor performance by end users. If they are sitting there waiting for a Query or transaction to return a result and it crashes with an error such as "Unable to allocate and Extent", in the users eyes the response is poor because the result has not been achieved.

iv) The Robustness Checks

The Eagle Eye robustness checks all objects that will crash if they are required to throw an extent as well as those sitting on the maximum number of extents. You will need to either adjust the NEXT extent size to fit in your largest free extent to fix the objects that are unable to throw an extent. We suggest that you set all of your object to MAXENTENTS UNLIMITED to avoid the blowing MAXEXTENTS problem.

Information is also provided on you rollback segment current sizing against the Optimal setting. Ideally you should be able to expand a rollback segment to be size of your largest table. Rollbacks are a common cause of robustness problems. Thankfully Oracle9i overcomes the problem by managing your rollback for you automatically.

v) Locking Problems

Locking problems can also be viewed as poor performance by the end user. A user will sometimes go to lunch with an uncommitted transaction sitting on their screen. And other users need the committed transaction to continue with their work.

SUMMARY

Eagle Eye is a product that has been developed to help sites quickly identify where the performance problems are occurring and the cause of the problems. The product is simple to use and focussed on quick results. When used with the MGA authored books "Oracle Performance Tuning" and "Oracle SQL Tuning" the tool provides a powerful arsenal to get those troublesome performance problems under control.

The product will shortly have a knowledgebase option for Peoplesoft Tuning as well as generic SQL tuning using the explain plan. The new options will not only identify the problems but also provide advice on how to fix them. The advice is from a professional company that works in the field day in day out tuning for its living.

Enjoy MGA EagleEye!