Oracle Administration and Monitoring Tools for Windows

1
Oracle Administration and Monitoring Tools for
Windows

• Administering and Monitoring Oracle with Windows
  Tools

2
Objectives

• At the end of this module the student will
  understand the following tasks and concepts.
• Oracle GUI Administration Tools
• Oracle Services Configuration
• Event Viewer
• Task Manager
• Oracle Administration Assistant for Windows
• Windows Performance Monitor
• Oracle Counters for Windows Performance Monitor
• Oracle Command Line Administration Tools
• Using SQLPlus for Administration
• The ORADIM utility
• Creating password files with orapwd
• The ORADEBUG Utility
• STATSPACK
• Automatic Workload Repository Reports (10g)
• Oracle Web Tools
• Managing Oracle with Oracle 10g Grid Control

3
Managing Oracle Services

• Oracle Services can be accessed by Control Panel
  -gt Administrative Tools -gt Services
• You can start, stop, pause and restart an Oracle
  Service.
• Services can also be enabled or disabled.
• It is also possible to run a custom program upon
  a Service failure event.

4
Viewing Oracle Events

• Oracle Events can be viewed through the
  Application Event Viewer.
• The Event Viewer can be accessed by Control Panel
  -gt Administrative Tools -gt Event Viewer
• Choose Application from the Log menu.
• Oracle 10g problems and other significant events
  are recorded here.
• A drill-down description of each event is
  available by double-clicking an individual event.

5
The Task Manager Tool

• The Task Manager Tool displays
• High-level summary of the concurrent applications
  and processes running
• Information about process state
• Graphical summary of CPU usage
• Graphical summary of memory usage
• Information about OS users
• Task Manager can be used to change the priority
  of a process.
• The Task Manager can be launched two ways
• Hit control-alt-escape
• Right-click on the task bar at the bottom of the
  screen
• The Performance tab contains useful information
  that directly impacts Oracle performance

6
The Task Manager Tool Oracle Performance

• CPU Usage, as well as Memory and Page File Usage
  can be monitored with the Performance tab
• Both CPU Usage and Memory and Page File Usage are
  critical for Oracle performance
• CPU Usage should be consistently high, but not
  maxed out
• You should not consistently see higher memory
  usage than the physical RAM
• Neither should you see high Page File Usage or a
  steadily climbing rate of Page File Usage

7
The System Monitor

• Launching Perfmon
• Start ? Programs ? Administrative Tools ?
  Performance

8
System Performance Monitor

• Performance Objects
• Counters
• Explain Text

9
Performance Logs and Alerts

• Right click on Counter Logs, Trace Logs, or
  alerts to set up
• Alerts require you to set limits for counter
  values
• Creating a performance log file with System
  Monitor
• Comma-separated format or tab-separated format
• Use meaningful collection intervals (gt 1 minute)

10
Administration Assistant for Windows

• Startup from Start -gt Oracle -Orahome92 -gt
  Configuration and Migration Tools -gt
  Administration Assistant for Windows
• Maneuver to the Database object
• Right click to login
• You can now startup and shutdown Oracle, as well
  as several other administrative functions.
• Right click and choose Process Information
• This gives you a way to kill Oracle threads, if
  necessary.

11
Oracle Counters for Windows Performance Monitor

• To initially configure the Oracle Counters for
  Windows Performance Monitor, open a command line
  window
• Enter the following
• OPerfCfg U system P password D SID
• The registry entries for the Performance Monitor
  are now set.
• Note Oracle Counters for Performance Monitor is
  sensitive to Oracle Net settings, which must be
  correct

12
Oracle Counters for Windows Performance Monitor

• Oracle Counters for Windows Performance Manager
  is available as a stand-alone application, or as
  add-in performance counters to Perfmon
• Startup from Start -gt Oracle -Orahome92 -gt
  Configuration and Migration Tools -gt Oracle
  Counters for Windows Performance Monitor
• By default, two Oracle counters for Sorts are
  displayed.

13
Oracle Counters for Windows Performance Monitor

• To display alternate counters, right-click in the
  window and choose Properties
• Click on the Performance Object menu. A list of
  Oracle counters is displayed.
• Use the Add and Remove buttons to modify the
  display.
• For example, to monitor database I/O, add the
  following counters
• physreads/sec
• physwrites/sec

14
Managing Oracle with SQL Plus

• Startup SQL Plus from Start -gt Oracle -Orahome92
  -gt Application Development -gt SQL Plus
• Log in as the SYSTEM user.
• You can then use
• connect / as sysdba
• to login with full privileges (on the console)
• You can now startup and shutdown Oracle, as well
  as any other administrative function.

15
The ORADIM Utility

• The ORADIM utility is a command line tool that
  can be used to manually create, delete or modify
  databases
• To get help, enter
• oradim -? -H -HELP
• To create an instance called PROD, for example,
  you could enter
• C\gt oradim -NEW -SID prod -INTPWD mypassword1
  -STARTMODE auto -PFILE C\oracle\admin\prod\pfile\
  init.ora
• To start an instance called PROD, for example,
  you could enter
• C\gt oradim -STARTUP -SID prod -STARTTYPE inst
  -PFILE C\oracle\admin\prod\pfile\init.ora

16
The ORADIM Utility

• To stop an instance called PROD, for example, you
  could enter
• C\gt oradim -SHUTDOWN -SID prod -SHUTTYPE srvc,
  inst
• To change an instance name from prod to lynx, set
  a new instance password mycat123, and specify a
  new initialization parameter file, for example,
  you could enter
• C\gt oradim -EDIT -SID prod -NEWSID lynx -INTPWD
  mycat123 -STARTMODE a PFILE C\oracle\admin\lynx\
  pfile\init.ora
• To delete an instance called prod, for example,
  you might enter
• C\gt oradim -DELETE -SID prod

17
Creating an Oracle Password File

• To establish a secure local logon user and
  password, you need to create a password file
• It can also be used for remote access to the
  database for selected users
• To start, open a command line session and
• cd C\oracle\ora92\dbs
• Enter the following
• orapwd fileORACLE_SIDpwd passwordyour
  password entries5 (the maximum number of open
  admin connections)
• A password file is now created

18
The Oracle ORADEBUG Utility

• The ORADEBUG utility is a debugging tool that
  sends debug commands through SQLPlus to an
  Oracle process.
• To start the ORADEBUG utility
• Start SQLPlus from the command promptC\gt
  sqlplus / NOLOG
• Connect to Oracle9i database as SYSDBASQLgt
  CONNECT / AS SYSDBA
• Enter the following at the SQLPlus promptSQLgt
  ORADEBUG ORADEBUG
• The utility runs and prompts you for parameters.

19
The Oracle ORADEBUG Utility

• To obtain a list of ORADEBUG parameters, enter
  the following
• SQLgt ORADEBUG HELP
• Output from most debug commands is written to
  trace files in the BACKGROUND_DUMP_DEST and
  USER_DUMP_DEST directories.
• To find the location of your trace file, enter
  the following at the SQLPlus prompt
• SQLgt ORADEBUG TRACEFILE_NAME
• If output is more than one line, then the result
  is sent to a trace file.
• If a debug command produces only one line of
  output, then the output is relayed directly to
  SQLPlus.

20
Statspack

• Standard Performance Package
• Similar to original BSTAT/ESTAT
• Install
• _at_?\rdbms\admin\spcreate
• Run
• SQLgt CONNECT perfstat/perfstat
• SQLgt EXECUTE statspack.snap
• Create the report with
• _at_spreport
• Tell it which snapshots to use.
• A report is created.
• Report contains a wealth of diagnostic
  information and predictive tools

21
Statspack Output Summary

• Statspack is divided into several sections
  including
• Summary information
• RAC statistics (if applicable)
• Wait events
• Top resource consumers
• Instance activity
• Cache statistics
• Rollback and Undo statistics
• SGA and Shared Pool statistics
• Initialization parameters

22
Automatic Workload Repository Features

• Statspack on Steroids (10g only)
• Collects stats every 60 minutes
• Kept for a week then purged
• A collection of performance stats
• A new background process MMON
• Resides in new sysaux tablespace

23
Automatic Workload Repository Reports

• Two reports provided by Oracle
• awrrpt.sql
• awrrpti.sql
• Similar to Statspack report
• Reports can be generated with scripts or through
  a GUI interface
• Optional HTML or plain text formats

24
Administering Oracle and Monitoring Performance
with Oracle 10g Grid Control

• Oracle Grid Control is the enhanced version of
  OEM that is installed with Oracle 10g
• Requires a central Management Repository and
  Agents on each database server
• Enhancements for managing and deploying RAC nodes
  and application grid nodes
• Significant improvements in automatic performance
  monitoring and analysis
• Central interface for administering and
  monitoring Oracle 10g
• Perform all Administration tasks
• Contains a variety of Advisors for improving
  performance

25
Oracle Grid Control Performance Page

• The database Performance page gives a quick look
  at performance trends
• Host CPU
• Average Active Sessions
• Instance Disk I/O
• Instance Throughput

26
AWR Reports

• To create an AWR report
• On the Database Administration page, select the
  Automatic Workload Repository link under
  Statistics Management. The Automatic Workload
  Repository page appears.
• Under Manage Snapshots and Preserved Snapshot
  Sets, click Snapshots link. The Snapshots page
  appears.
• Under Select Beginning Snapshot, select the start
  point for the range of snapshots that will be
  included in the report.
• From the Actions pull-down menu, select View
  Report and click Go. The View Report page
  appears.
• Under Select Ending Snapshot, select the end
  point for the range of snapshots and click OK.
  The Processing View Report page appears while
  the report is being generated.
• Once completed, the Snapshot Details page appears
  and the report will be displayed.
• To save the report as an HTML file, click Save to
  File.

27
AWR Reports
28
Using the Segment Space Advisor

• The Oracle 10g Segment Advisor helps you
  determine whether an object has space available
  for reclamation.
• Available in 10g OEM or Grid Control
• The Segment Advisor can generate advice at three
  levels
• Object level - advice is generated for the entire
  object, such as a table. Advice does not cascade
  to dependent objects.
• Segment level, - advice is generated for a single
  segment, such as unpartitioned table, a partition
  or subpartition of a partitioned table, or an
  index or LOB column.
• Tablespace level - advice is generated for every
  segment in the tablespace.

29
Using the Segment Space Advisor
30
Using the Segment Space Advisor
31
SQL Tuning Advisor

• 10g Oracle Enterprise Manager (DB Console, Grid
  Control) offers the SQL Tuning Advisor as a SQL
  statement tuning tool.
• Accessed from Advisor Central
• Select one or more SQL statements, or generate an
  SQL Tuning Set
• View automatically generated Recommendations
• View Explain Plan output

32
SQL Tuning Advisor Recommendations
33
SQL Tuning Advisor Explain Plan
34
Using the SQLAccess Advisor

• The SQLAccess Advisor provides an alternative to
  manually determining which indexes are required.
• The SQLAccess advisor may be invoked from Advisor
  Central in OEM or run with the procedures in the
  DBMS_ADVISOR package.
• For a chosen schema, the SQLAccess Advisor either
  recommends using a workload or it can generate a
  hypothetical workload.
• Given a workload, the SQLAccess Advisor generates
  a set of recommendations from which you can
  select indexes to be implemented.
• SQLAccess Advisor generates an implementation
  script that can be executed manually or through
  OEM.

35
Using the SQLAccess Advisor

• The SQLAccess Advisor provides an alternative to
  manually determining which indexes are required.
• The SQLAccess advisor may be invoked from Advisor
  Central in OEM or run with the procedures in the
  DBMS_ADVISOR package.
• For a chosen schema, the SQLAccess Advisor either
  recommends using a workload or it can generate a
  hypothetical workload.
• Given a workload, the SQLAccess Advisor generates
  a set of recommendations from which you can
  select indexes to be implemented.
• SQLAccess Advisor generates an implementation
  script that can be executed manually or through
  OEM.

36
Using the SQLAccess Advisor
37
Automatic Database Diagnostic Monitor

• ADDM considers the following to make performance
  tuning recommendations
• CPU bottlenecks
• Undersized Memory Structures
• I/O capacity issues
• High load SQL statements
• High load PL/SQL execution and compilation
• High load Java usage
• RAC specific issues
• Global cache hot blocks and objects
• Iinterconnect latency issues

38
Automatic Database Diagnostic Monitor (cont.)

• ADDM considers the following to make
  recommendations
• Sub-optimal use of Oracle by the application
• Poor connection management
• Excessive parsing
• Application level lock contention
• Database configuration issues
• Incorrect sizing of log files
• Archiving issues, excessive checkpoints
• Sub-optimal parameter settings
• Concurrency issues - Are there buffer busy
  problems?
• Hot objects and top SQL for various problem areas

39
ADDM Recommended Solutions

• Hardware changes
• Adding CPUs
• Changing the I/O subsystem configuration
• Database configuration
• Changing initialization parameter settings
• Schema changes
• Hash partitioning a table or index
• Using automatic segment-space management (ASSM)
• Application changes
• Using the cache option for sequences
• Using bind variables
• Using other advisors
• Running the SQL Tuning Advisor on high load SQL
• Running the Segment Advisor on hot objects

40
Viewing Current ADDM Findings

• You can view ADDM findings based on the latest
  analysis period within OEM
• Go to the Database Home page
• View the Diagnostic Summary to see a list of ADDM
  findings
• Click on a finding link to see details

41
ADDM Findings in OEM
42
Running ADDM Manually

• From the Database Home page
• Under Related Links, click the Advisor Central
  link
• On the ADDM page, under Advisors, click the ADDM
  link
• Select Run ADDM to analyze past instance
  performance
• Choose the Period Start Time (choose starting
  snapshot)
• Choose the Period End Time (default is last
  snapshot)
• Click OK to start analysis

43
Running ADDM in OEM
44
Scheduling Jobs in Oracle 10g

• Oracle 10g contains a new Scheduler for
  scheduling and tracking jobs.
• One way to access the Scheduler is through the
  Grid Control Job Activity page. From this page
  you can
• Search for existing job runs and job executions
• Restrict the search by name, owner, status,
  scheduled start, job type, target type, and
  target name.
• Create a job
• View, edit, create like, suspend, resume, stop,
  and delete a run
• View, edit, create like, suspend, resume, retry,
  stop, and delete an execution

45
RAC Cluster Cache Coherency

• Heavy concurrent read and write activity on
  shared data in a cluster can cause performance
  problems.
• The Cluster Cache Coherency page enables you to
• View cache coherency metrics for the entire
  cluster database
• Identify processing trends
• Optimize performance for your Real Application
  Clusters environment

46
Cluster Interconnects

• The Cluster Interconnects page enables you to
  view the current state of interfaces on hosts
• You can use this page to
• Monitor the interconnect interfaces
• Determine configuration issues
• Identify transfer rate-related issues, such as
  excess traffic.
• This page helps determine the load added by
  individual instances and databases on the
  interconnect.
• Sometimes, you can immediately identify
  interconnect delays due to applications outside
  the Oracle Database.

47
Conclusions

• Oracle GUI Administration Tools
• Oracle Services Configuration
• Event Viewer
• Oracle Administration Assistant for Windows
• Windows Performance Monitor
• Oracle for Windows Performance Monitor
• Oracle Command Line Administration Tools
• Using SQLPlus for Administration
• The oradim utility
• Creating password files with orapwd
• The ORADEBUG Utility
• Statspack
• AWR
• Oracle Web Tools
• Managing Oracle with Oracle 10g Grid Control

48
Review

• How could you customize an Oracle Service?
• What command is used to create an Oracle password
  file?
• How must you log in to SQLPlus in order to stop
  and start the database?
• Where can you go if you need to kill Oracle
  threads?
• What Advisors can you launch from Oracle Grid
  Control to help monitor performance?

49
Summary

• In this training module the following tasks and
  concepts were covered
• Managing Oracle Services on Windows
• Creating a password file with orapwd
• Managing a database with SQLPlus
• Managing a database with Administration Assistant
  for NT
• Managing and Monitoring a database with Oracle
  Enterprise Manager