MGA EagleEye Version 2.11

1
MGA EagleEyeVersion 2.11

• Getting Started

2
Introduction toMGA EagleEye

• MGA EagleEye has been developed to provide system
  performance knowledge and advice to both the
  business and the technical staff.
• The MGA performance tuning strategy enables
  different teams within an organisation to
  collaborate and effect change that enables the
  system to perform to firms expectations.
• MGA performance tuning methodology is based on
  three strategic management principles
• You can manage what you measure.
• Paretos 80-20 80 of the solution takes 20
  of the effort and
• Solve the cause not the effect.

3
Step 1 The Monitor you manage what you
measure

• How is your database performing? How was your
  database performing yesterday, or last month?
  When we make changes what effect does this have
  on system performance?
• MGA EagleEye provides a way to quantify
  performance over time to allow the business, the
  staff and the IT department to collaborate on
  providing optimal system performance.
• To set up the measures, use the monitor menu and
  follow the quick step guide it could take as
  little as 5 minutes, depending on the complexity
  and number of SQL statements.

4
Install Monitoring Objects

• Ensure that a full install of MGA EagleEye has
  been completed.
• If you need to complete this step, choose
  Install EagleEye Monitoring Objects from the
  Admin Menu.
• Note 1 MGA recommends that you follow standard
  change control procedures on all systems. The
  process creates some tables and installs a
  package into the database.
• Note 2 You will be required to -
• Assign space to install the tables and a package
  and
• Have temporary access to an admin level account
  to create the monitoring user (MGA typically use
  the name EagleEye as the monitoring user).

• To set up the monitor and start measuring use the
  monitor menu and follow the quick step guide it
  could take as little as 5 minutes.

5
Complete - Install Monitoring Objects

• Follow the instructions on the screen to complete
  a full install
• There is opportunity to view the error log.
• The installed tables collect data to assist in
  performance tuning. There is an option to purge
  this should it be necessary.

6
What to measure?

• Find one or more SQL statements from a standard
  user that are consistent with normal operations
  and complete in around, eg 5 seconds
• Simple Example Select count() from
  VERYLARGETABLE where rownum lt 500000 The
  inclusion of rownum ensures the SQL statement
  runs consistently over time, since it is likely
  that the VERYLARGETABLE grows over time. You may
  wish to use the worksheet first to perfect a more
  inclusive and relevant SQL statement.
• Tip You may enter several scripts and have them
  grouped together in a suite. Some of our clients
  have built scripts that monitor functional areas,
  eg Accounts Payable, Account Receivable, Time
  Entry or Payroll. The SQL chosen is typical of
  the functions that a user performs in there area
  of expertise. Each Suite is named after the
  functional area and has one or several scripts
  assigned to it.

7
Add a SQL statement to the monitor - the Suite

• Use Monitoring Menu -gt Performance -gt Management
  -gt Edit Suites/Scripts...
• First you need to create a Suite with a time
  threshold. This concept is very important as MGA
  EagleEye captures SQL statements that are running
  when one of your scripts exceeds the suites
  threshold.

Tip If your script(s) under normal operating
conditions runs for around 10 seconds, MGA would
typically set the threshold to 30 seconds. Over
time, once accurate performance measurement
information is available the threshold maybe
modified.
Tip If you have multiple suites you can them off
individually using the Active button. This
allows you to create many suites to monitor
specific business functions without having to run
them all the time.
8
Add the SQL statement tothe monitor - the Scripts

• You may now add the scripts. The Edit Script
  window allows you to run the scripts and
  determine how long they take to complete, using
  the Run Script
• Tip Try to add the scripts when the system is
  not under load to determine the script times.
• Tip The script editor button takes you to the
  worksheet to allow you to use the full functional
  editor.

9
Turn the monitor on

• At this stage each suite you have enter with the
  one or more scripts are not being run on the
  database.
• Tip You can come back to this option and turn
  some or all monitoring off quickly.

10
Click on Start Monitor

• Tip In this example I used 1 Minute between
  Runs, this enabled the monitoring graph to be
  populated with data quickly. MGA advise clients
  to set the monitor to run every 10 or 15 minutes.
• Tip This information is best distributed to
  business owners and function experts to assist
  with solving performance problems.

11
Access the Monitor Chart

• Tip This information is best distributed to
  business owners and function experts to assist
  with solving performance problems.

• very shortly, you will have a graph over time of
  your database performance. This will depend on
  the monitor frequency - see last slide.
• There is a refresh button to update the graph.

12
The Monitor Graph
13
About the Monitor Graph

• You now have a measure of performance that you
  can manage too with data that can be analysed.
  Clients are increasingly using the threshold
  functionality as part of their overall service
  level process.
• Note 1 The red threshold line - you can click on
  the graph when it goes above this line to view
  what SQL statements were running at this point in
  time.
• Note 2 Note the number of users.
• Note 3 It is now possible to also graph the
  result of the SQL statement as well as the time.

14
Step 2 The 80-20 Rule Pareto's Principle The
80-20 Rule

• Pareto's rule states that a small number of
  causes are responsible for a large percentage of
  the effect, in a ratio of about 2080. Expressed
  in a management context, 20 of a person's effort
  generates 80 of the person's results. The
  corollary to this is that 20 of one's results
  absorb 80 of one's resources or efforts. For the
  effective use of resources, the manager's
  challenge is to distinguish the right 20 from
  the trivial many.

15
MGA Tuning Methodology

• MGAs successful performance tuning methodology
  ties in well with this rule in two important
  ways
• The effort spent on tuning SQL statements has the
  greatest reward on system performance and it is
  directly measurable by technical and business
  users or SQL tuning is the 20 that implemented
  well, gives an 80 result and
• Tune the worst performing statements (eg the
  worst 20) to gain the greatest performance
  improvements (eg an 80 improvement).
• MGA EagleEye has many effective options that
  enable you to find the worst performing SQL
  statements to tune.

16
Useful tuning functions

• Top Sessions - the most active sessions at this
  point in time.
• Long Running SQL - SQL statements
• Active SQL - at this point in time what SQL
  statements are running.
• Slow SQL - What we the worst running SQL
  statements today. Eg find statements run today
  that have taken longer than 20 seconds to
  complete.
• IO Usage on Tablespaces/Datafiles
• IO Usage on Object/Events
• Note There are many other functions that can
  quickly assist.

17
Step 3 solve the cause

• Recall of set up so far
• Now you have the monitor turned on that is giving
  you an historical and accurate measure of system
  performance.
• You have found the statements that are performing
  poorly.
• You are ready to solve the performance problems.
  The following are scenarios that MGA has
  encountered and may well match your problem.

18
Viewing the Monitoring Graph

• View the monitoring graph.
• Tip You can monitor multiple databases at the
  same time - with the Multi Chart View
• The graph has a red line across it indicating the
  acceptable response time threshold. Each time
  that standard SQL runs, the response time is
  recorded and is plotted in the graph. The Y-axis
  shows the response time in seconds and the X-axis
  shows the time that the SQL scripts were run.
• You can view historical data as required.

19
Drilling Down to the Cause of the Problem

• When the response exceeds the threshold, you can
  click on the line on the graph to drill down to
  determine the cause of the problem. The drill
  down shows you the SQL running, who was running
  it and the object waits.

20
SQL Running When Problem Occurred

• MGA EagleEye provides a list of the SQL
  statements that were running when the response
  time threshold was exceeded.
• MGA EagleEye also provides the SQL statistics,
  the execution plan and the users that were
  running the SQL statement. The statistics
  provided include the runtime in seconds as well
  as the number of times the SQL statement has been
  executed.
• You can also produce a report of all SQL
  statements and their related information.
• Tip A nice feature of the product is the ability
  to double click on the table or index in the
  explain plan and to obtain details on the indexes
  that exist on the table, and how and when the
  table was analysed.

21
Waits Occurring When Problem Occurred

• You can then return to the previous screen and
  observe details on where the object and session
  waits were occurring.
• Select the Session Waits tab. Knowing where the
  waits are occurring assisted us in identifying
  which objects could be moved to alternate
  tablespaces to avoid disk I/O bottlenecks.
• It is also useful information on the cause of the
  waits, particularly in environments such as
  Oracle parallel server, or for applications that
  are experiencing locking issues.

22
SQL Scripts and Their Drill Downs

• The EagleEye monitor screen allowed us to drill
  down at the site to determine the actual SQL
  script(s) that was experiencing the problem.
• In the example shown, one script had its run time
  jump almost 10 times. The drill down highlighted
  that the DBA had re-built a table and had
  forgotten to re-apply an index.

23
Have a glance at the Top Sessions

• To see if a small number of sessions are
  dominating the resource usage- check out the SQL
  they are running.
• If one particular session is running an untuned
  transaction, turn on EagleEye Trace

24
Chaining and Fragmentation

• Check out the degree of chaining and
  fragmentation in the database. This needs to be
  repaired

25
Specific ERP Options

• Run through the specific tuning options - these
  were observed by MGA consultants, mainly on
  PeopleSoft databases.
• Are the INIT.ora parameter settings suitable for
  ERP.
• Are ERP Tables/Indexes analyzed
• Tip MGA recommend you reanalyze the
  Tables/Indexes that need reanalyzing.
• Are the ERP table and index statistics accurate.
• List all tables that are heavily deleted from
  that will require special attention such as
  regular truncates, index re-builds and correct
  setting of statistics.

26
Tune the SQL

• Perform generic SQL tuning for all statements
  that have run for gt 10 seconds and have been run
  gt 10 times
• Perform generic SQL tuning for all statements
  that have exceeded 200 second response time
  (typically batch and report SQL)
• Identify all statements that utilize WHERE
  EXISTS, which forces a FULL TABLE SCAN
• Identify App Engine SQL that could be made more
  efficient if many accounts/departments/projects/pr
  oducts were performed in a single SQL statement
  rather that one item at a time
• If necessary, turn on Batch Tracing to capture
  SQL statements overnight that exceed a response
  time, typically 60 seconds. This process is used
  to capture the badly tuned SQL from overnight
  data loads and other batch and report processing

27
What Next

• If your site is NOT strong in the Oracle tuning
  and/or tuning, MGA offers a wide range of
  services that may assist. Some sites prefer a
  remote tuning service. Running various
  diagnostic reports from within EagleEye and
  e-mailing them to MGA achieve this. MGA makes
  suggestions on how to repair the reports from its
  various US and Australian offices. The other
  option is to have skilled MGA tuning expert come
  to your site. Typically, the onsite work will
  comprise not only repairing your performance
  problems at your site, but also training your
  internal staff to repair their own performance
  problems in the future, if such a need should
  arise.
• For further information on MGA access our web
  site http//www.mga-it.com or via e-mail
  info_at_mga-it.com

28
The Beginning

• Now you have the tool and the methodology to
  ensure your systems are running optimally.