About Oracle Database Solution Tools

The Oracle Database Solution Tools are preconfigured diagnostic tool actions that are associated with and accessible to a particular Oracle Database Solution template deployment. These tools are deployed into the same group as the monitors that are deployed by the Oracle Database Solution template. The tools are listed in the monitor detail table and identified with the name Solution Tool in the Status field of the table.

Although the Solution tools are listed in the monitor table, they are not monitor instances. They do not run automatically, display a status based on action results, or trigger alerts. They are preconfigured actions that make use of a SiteScope Diagnostic Tool to check certain statistics from the Oracle database that may indicate a performance problem.The following describes tools deployed as part of the Oracle Database Solution:

Tool Name	Description and Usage Guidelines
Oracle Solution Tool: Top Ten SQL Statements in Logical IOs Per Row	This tool performs a query which is designed to locate the most resource-intensive SQL statements being executed in the database. The `V$SQL` table is queried for the ten SQL statements which are performing the most logical IOs per row are displayed in a table. The statement IDs of these ten statements are displayed in a table, along with some additional resource-usage data for each statement. This additional data includes: Physical IO Blocks : the number of disk reads performed on behalf of the statement. Logical IOs : the number of buffer gets performed on behalf of the statement. Rows Processed : the number of rows processed when executing the statement. Logical IOs Per Row : the number of buffer gets performed per row that was processed when executing the statement. Runs : the number of executions of the statement. Logical IOs Per Run : the number of buffer gets per statement execution. Note: Tha action performed can have a significant impact on database resources and should not be executed frequently.
Oracle Solution Tool: No. of Sessions Waiting Per Event	This tool can be used in troubleshooting stuck sessions. When several sessions become unresponsive, this tool can determine whether the stuck sessions are all waiting on the same event. The tool action displays a table containing the number of sessions waiting on specific events.
Oracle Solution Tool: Shared Server Process Contention (Common Queue Average Wait Time)	This tool calculates the average wait time of the shared server message queue (the Common Queue as recorded in `V$QUEUE`). A high average wait time may indicate contention between shared server processes.
Oracle Solution Tool: Tablespaces With Less Than 2 Extents Available	This tool can be used to locate tablespaces which do not have enough adjacent free space to throw more than two new extents. Important: This tool will not be useful on all Oracle configurations. For example, the query used by this tool (see below) would not work correctly on a database that uses dictionary-managed tablespaces (DMTs). Even for databases that used locally-managed tablespaces (LMTs), the query may not apply, depending on the segment space management scheme used for a specific tablespace. Tablespace management is a very complex subject. If there is any doubt as to the applicability of this tool to your specific Oracle installation, the best course of action is to consult your local DBA and ask them about the usefulness of the query used by this tool with respect to the target database configuration. The following query is executed by this tool: `SELECT owner, s.tablespace_name, segment_name, s.bytes, next_extent, MAX(f.bytes) largest FROM dba_segments s, dba_free_space f WHERE s.tablespace_name = f.tablespace_name(+) GROUP BY owner,s.tablespace_name,segment_name, s.bytes,next_extent HAVING next_extent*2>max(f.bytes)`

You use the following steps to run or execute the Oracle Database Solution tools.

To run an Oracle Database Solution tool:

Click the group name for the group where the Oracle Solution monitors are deployed. The group detail page opens.
Find the Solution Tool for the action that you want to execute. See the Name column for the Solution Tool for a description of the action performed by that tool.
Click the Tools link to the right of the tool Name to execute the action. The Database Connection Test page opens. From this page, the tool may be run as many times as necessary by clicking the Connect and Execute Query button.
Note: Some Solution Tools may create significant overhead on the database depending on the query. Executing the tools in quick succession is therefore not advised.
The upper portion of the Database Connection Test page displays the database connection parameters used for the test. The results of the tool query are found in a table near the bottom of the page. Review the results based on the Description and Usage Guidelines for that tool.