My Oracle Support Banner

Enterprise Manager 13.4 Release Update 10 JET SQL Details and Session Details (Doc ID 2751569.1)

Last updated on FEBRUARY 25, 2021

Applies to:

Enterprise Manager for Oracle Database - Version 13.4.1.0.0 and later
Information in this document applies to any platform.

Details

 

Beginning with Enterprise Manager 13.4 release update 10 (RU10), the SQL Details and Session Details drill down screens from ASH Analytics have been updated to use the Oracle JET UI.





From Ash Analytics, you can drill down into SQL Details when you click on a SQL ID in the ‘Top SQL’ in the region in the lower left.
You can drill down into Session Details when you click on a User Session link in the ‘Top Sessions’ region in the lower right.

 

SQL Details - Summary



Note:

The new JET UI 'SQL Details' lacked a functional 'Tune SQL' button on release.
This will be implemented in a near future release update and is being tracked as an enhancement request in <Bug 32033507>.
An immediate alternative is a workaround to use the 'Tune SQL' button from the main 'ASH Analytics' screen.



When you click on a SQL ID from the ‘Top SQL’ region in ASH Analytics, you are taken to the JET SQL Details page.  


At the top of the page is the ‘Time Picker’ which allows you to indicate the time frame you are interested in investigating for the selected SQL. 

 

In the center of the page are five tabs with additional information about the SQL execution.  
These tabs are ‘Summary’, ‘ASH Analytics’, ‘Execution Statistics’, ‘SQL Monitoring’ and ‘SQL Text’.

 

The first tab and default view is the ‘Summary’ tab.   
The Summary tab contains three sections, ‘Overview’, ‘Source’, and ‘Plan’.

 

The ‘Overview’ and ‘Source’ sections have information about the execution environment for the specific SQL, such as the SQL text, version count and invalidations.  
It also has data about the Username, Program, Module and other details about the environment where the SQL has executed.

 

The bottom table on the ‘Summary’ tab lists the SQL execution plans, identified by the plan hash values, for the SQL statement in the selected time range along with execution statistics for that plan.  
The execution statistics include the ‘Execution Rate’, ‘Database Time’ and I/O data.

 

SQL Details - ASH Analytics

 

The second tab in ‘SQL Details’ is the ‘ASH Analytics’ tab.

 

The ‘ASH Analytics’ tab in SQL Details has the same functionality and data presentation as the main Performance Hub ‘ASH Analytics’ screen.   
The data is pre-filtered to the ASH data regarding the execution of the selected SQL, indicated by the ‘Applied Filters: SQL ID:  xxxxxxxxxxxx’ above the center ‘Average Active Sessions’ chart  (‘xxxxxxxxxxxx’ is replaced with the actual SQL ID being investigated).

 

SQL Details – Execution Statistics

 

The third tab in SQL Details is the ‘Execution Statistics’ tab.


 

The ‘Execution Statistics’ tab expands the ‘Plans’ section from the ‘Summary’ tab.  
It lists each Execution Plan per Plan Hash Value for the selected SQL and time range along with Execution statistics.   
In addition, for each Execution Plan it lists the Explain plan below.

 

SQL Details – SQL Monitoring

 

The fourth tab in ‘SQL Details’ is the ‘SQL Monitoring’ tab.


 

The ‘SQL Monitoring’ tab lists all of the monitored executions of the specific SQL for the selected time range.   
‘SQL Monitoring’ behaves the same as ‘SQL Monitoring’ from the Performance Hub, just filtered to the specific SQL selected in SQL Details.   
SQL Monitoring monitors expensive SQL, defined as SQL that executes in parallel or takes more than 5 seconds to complete.   
It does not monitor fast, short lived SQL.

 

SQL Details – SQL Text

 

The final tab in SQL Details is the ‘SQL Text’ tab.   


 

The ‘SQL Text’ tab lists the complete text of the selected SQL.

 

Session Details - Summary

 

When you click on a session id link in the ‘Top Sessions’ region in Performance Hub ‘ASH Analytics’, you’re taken to the ‘Session Details’ drilldown.


 

At the top of the page is the ‘Time Picker’ which allows you to indicate the time frame you are interested in investigating for the selected session. 

 

In the center of the page are four tabs with additional information about the SQL execution.  
These tabs are ‘Summary’, ‘ASH Analytics’, ‘Metrics’, and ‘SQL Monitor’.

 

The first tab and default view is the ‘Summary’ tab.   
The ‘Summary’ tab contains information about the Server where the session executed, the Client where the session was initiated and some information about the Activity of the session.

 

Session Details – ASH Analytics

 

The second tab in ‘Session Details’ is the ‘ASH Analytics’ tab.


 

The ‘ASH Analytics’ tab in ‘Session Details’ has the same functionality and data presentation as the main Performance Hub ‘ASH Analytics’ screen.   
The data is prefiltered to the ASH data relating to the selected Session, indicated by the ‘Applied Filters: User Session:  x:yyyy,zzzzzzzz’ above the center ‘Average Active Sessions’ chart   (‘x:yyyy,zzzzzzzz’ is replaced with the actual User Session ID being investigated).

 

Session Details – Metrics

 

The third tab in Session Details is the ‘Metrics’ tab.


 

The ‘Metrics’ tab gives information about resources used by the session for the selected time range in the time picker.  
It graphs ‘CPU Used’, ‘Memory’ resources, ‘I/O Throughput’ and ‘I/O Requests’.

 

Session Details – SQL Monitor

 

The fourth tab in ‘Session Details’ is ‘SQL Monitor’.


 

The ‘SQL Monitor’ tab lists all of the monitored executions of any SQL for the selected time range.   
‘SQL Monitor’ behaves the same as the Performance Hub ‘SQL Monitor’, just filtered to the specific Session  and time range selected in Session Details.
SQL Monitor monitors ‘expensive’ SQL, defined as SQL that executes in parallel or takes more than 5 seconds to complete.
It does not monitor fast, short lived SQL.

 

 

Actions

 

Contacts

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Details
 SQL Details - Summary
 SQL Details - ASH Analytics
 SQL Details – Execution Statistics
 SQL Details – SQL Monitoring
 SQL Details – SQL Text
 Session Details - Summary
 Session Details – ASH Analytics
 Session Details – Metrics
 Session Details – SQL Monitor
Actions
Contacts
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.