Install And Configure DTrace Scripts
DTrace is a dynamic analysis tool for Sun Microsystems operating systems. It was first introduced in the Solaris 10 operating system, and can be used ONLY on Sun platforms from Solaris 10 onwards.
Spotlight and DTrace
Spotlight on Oracle uses DTrace to capture details of the interaction between Oracle and the operating system.
When Oracle sessions use CPU time, most of that elapsed time is spent executing Oracle code. However, Oracle sometimes uses system calls to perform functions such as to:
- Allocate or deallocate private (PGA) memory.
- Synchronize activities with other processes through semaphores or other IPC mechanisms.
- Allocate shared memory (SGA).
- Perform I/O operations on datafiles, redo logs, raw devices, and so on.
- Capture CPU or elapsed timing to support the time model and wait interfaces.
Spotlight uses DTrace to investigate these system calls to calculate where that elapsed time is being spent.
To install and configure DTrace scripts
To use DTrace within Spotlight, you need to install and configure a number of DTrace scripts on the server. Once installed, use the DTrace drilldown to view the DTrace analysis. Spotlight on Oracle Drilldowns
- Locate in the Spotlight installation folder: Plug-ins\SoO\SoO_DTrace_scripts.tar
- Unpack SoO_DTrace_scripts.tar to a directory on the database server.
- Ensure the unpacked files have execute permission.
- For the account that will be used to run operating system commands from Spotlight:
- Edit .profile to define a QUEST_SOO_HOME variable that points to the directory on the database server where the unpacked files are.
-
Provide DTrace authority. Add this line to /etc/user_attr:
<<username>>::::defaultpriv=basic,dtrace_proc,dtrace_kernel
For more information on DTrace and its uses:
Tune Your Oracle Database
The following is a summary of steps that you can undertake to tune Oracle by using Spotlight on Oracle.
Spotlight on Oracle supports an approach to Oracle performance tuning that could be described as "tuning by bottleneck". Spotlight on Oracle alerts you if any component of the Oracle architecture is forming a bottleneck. Additionally, Spotlight on Oracle provides tools that allow you to identify and rectify any inefficiency in your database’s configuration.
In general, you tune your Oracle instance by reiteratively identifying bottlenecks, contention, and critical resources, and by using the advice in this section to remove the bottleneck or contention or improve the performance of the resource.
Notes:
- Spotlight on Oracle’s approach to Oracle instance-tuning is not the only valid approach. In particular, it emphasizes post-implementation tuning of Oracle applications, and remedial action to correct fundamental physical configuration deficiencies in an Oracle installation.
- Optimal database performance occurs when optimization is built into the application design and development process, and when the database’s physical design is specifically tailored to support an accurately estimated workload.
Predictive Diagnostics
Collect and analyze performance metrics in order to predict the future behavior of the Oracle database.
To open Predictive Diagnostics
- Select the Spotlight on Oracle connection (or Spotlight on Oracle RAC connection) in the Spotlight Browser.
-
Click Predictive Diagnostics.
The page contains several sections —Configuration, Find Impending Problems, Performance Modeling, and What To Do Next — and a set of images that indicate a basic workflow through the predictive process.
Notes:
- Predictive Diagnostics uses a proprietary set of algorithms to process the data.
- You do NOT need to have Spotlight running continuously in order to collect the data.
- Use the Oracle User Wizard to create a new Spotlight user (or modify an existing user) that employs Predictive Diagnostics.
| Diagnostic Category | Description |
|---|---|
| SQL Performance |
Collect and analyze performance metrics for SQL statements as they are executed on the database instance. When enough performance data is collected, Spotlight may be able to predict the future performance for individual SQL statements on that database. These predictions can be used to identify SQL statements whose performance may not scale adequately as data volumes and SQL execution rates increase. Spotlight will refer to these SQL statements as degrading SQL. |
| Impending Bottlenecks - Timed Statistics |
Note: Available for Oracle 10.2 and later. Collect and analyze waiting events as they occur on the database. When enough performance data is collected, Spotlight may be able to predict potential bottlenecks in the system as they affect database throughput and response time. |
| Performance Modeling |
Note: Available for Oracle 10.2 and later. Collect and analyze statistics on the performance of the database during periods of high activity. When enough performance data is collected, Spotlight may be able to:
|
Spotlight on Oracle Tools: Explain Plan, SQL Optimizer, Space Manager
| Tool | Description |
|---|---|
| Tools | Explain Plan | The Explain Plan allows you to determine the execution plan that Oracle applies to a particular SQL statement. Spotlight on Oracle allows you to view graphical representations of the execution plan for SQL statements being executed by a user, or for those identified by the SQL & Application Workload drilldown. |
| Tools | SQL Optimizer (SQL Tuning) | SQL Optimizer (if installed) provides context-sensitive tuning advice for SQL statements based on the Oracle execution plan and the database structure. You must have DBA privileges for the database you are diagnosing. |
| Tools | Space Manager |
Space Manager (if installed) provides a comprehensive solution for space management and reorganization. Space Manager offers preventive maintenance, problem detection and resolution, and capacity planning across any number of databases. |