Dimension Navigation
The History mode tree Instance View drilldown is a unique feature that facilitates iterative (up to three levels) access to any of the key dimensions associated with Oracle databases activity, based on the OLAP multidimensional model. Domain nodes offer a hierarchical view of all types of Oracle activity characteristics.
The Tree node dimension display determines what subset of activity is displayed. Iterative drilldown into domains of interest facilitates increasingly refined focus and diagnosis until the core cause of the performance problem is identified. The problem can then be resolved with appropriate Quest tools.
The root node to which the display opens is the entire instance activity. By expanding the tree, you change the application focus to that of the selected dimension into which you have drilled. The tree-pattern iterative drilldown is a two-node process where the first node is the dimension type selection and the second is the dimension value.
For example, from the overall instance activity view, we
seek to identify the most active DB User. Expanding the instance view
node reveals the list of available dimensions. Clicking on the DB User () node
displays the top most active (in accordance with the selected resource)
database users. Clicking on the first user will focus the entire window
on this user's activity. We can now identify the most demanding SQL statement
that this specific user has executed by expanding the user node and then
clicking on the SQL statement
dimension node. This will display the topmost active SQL statements executed
by this user. In this manner, you can iteratively drilldown into any Oracle
dimension of interest for a complete understanding of the causes of its
behavior.
Performance Analysis highlights application behavior, usage patterns and service levels, as well as revealing performance problems and helping users to drill down to the problem root causes quickly and efficiently.
Among the more practical monitoring and administrative scenarios where Performance Analysis dimensions act as the key insight are the following:
Find the topmost SQLs executed by a specific user.
Find the most offensive users executing a specific program. For those of interest, identify the SQLs causing the resource usage.
Within your Oracle Application, find the most active ORAAPPS users running a specific form.
To alter the display or order of Dimensions on the tree node, see Performance Analysis Configuration.
Default Oracle Dimensions:
Instance (for RAC configurations) - SID identifying the cluster member instance.
SQL Statements - The executed SQL commands.
Note: Right-clicking tree-node or pane-listed SQL statements enables activation of Explain Plan History to compare any two forms of execution plan used historically by Oracle for executing a given SQL statement.
Programs - Name of the programs connecting to Oracle and executing the SQL statements.
Note: If Oracle fails to report a valid program name and the program is running on the database host, the program will be named according to the running program executable.
OS Users - Operating system users running the client program.
DB Users - Oracle users used by the program to connect to the Oracle instance.
Client Machines - The machines on which the client executable (connected to Oracle) is running.
Actions - Optional value in the V$SESSION.ACTION column during SQL execution.
Modules - Optional value in the V$SESSION.MODULE column during SQL execution.
Clients Info - Optional value in the V$SESSION.CLIENT_INFO column during SQL execution.
Command Types - Executed SQL command type (for example INSERT, SELECT, and so on).
Sessions - Identifying the executing session (available for User Defined Collections only).
Note: When data received from Oracle contains empty values, the "<EMPTY>" designation will appear in the dimension tree of the history dimension navigation. (For example Oracle background processes which have no specific database user will appear as empty in the user node.) Drilling down within this domain will reveal its identifiable components.
Note: Metrics can be added or deleted from the right pane listing by enabling the grids customization and then right-click in the pane listing and selecting Select Metric... from the displayed shortcut menu.