Logging to Session Manager

In order to be able to use Session Manager, you need to log in using your NICE credentials.

The integration with CERN SSO will be implemented, which will eliminate the necessity of additional logging.

Logging out from Session Manager

Each page of Session Manager displays in right upper corner a link "Logout from Session Manager". You can use it in whatever moment you need. This will terminate the database connection, if you have an active one, as well as will terminate the connection to Session Manager showing logout page.

Session Manager timeouts

Because of the fact, that you log in twice to be able to use Session Manager (the Tool + a database), there are also two timeouts defined. The one for you Session Manager Tool is defined for 2 hours and the database timeout is set to 45 minutes.

Each time your database session expires, you will need to use "Database connection" page and retype authentication data required to be connected to the database again.

In case of Session Manager timeout, you will need to log in to Session Manager (your NICE credentials) and afterwards, log in to a database.

Connecting a database

After having successfully authenticated as a NICE user, you are redirected to the database login page. There are two ways of authentication:

• using credentials of a database account, which you are interested in
• using e-groups related set of permissions

Using Oracle credentials

In case of Oracle account credentials, specify the database user, whose sessions you are interested in, give its password and choose the appropriate database from the databases list. You will be able to manage sessions connected to the database schema you have authenticated against and also READER and WRITER schema associated with it (if exist). The READER and WRITER, which are taken account by Session Manager have usernames build out of the owner account with _W, _R or _WRITER, _READER suffixes (e.g. SAMPLEUSER_W or SAMPLEUSER_WRITER).

Using e-groups

In case of e-groups used, you are identified based on your NICE username and e-groups you are on. The "DB list" is populated with the databases to which you have permissions. The list is empty, untill you click "Refresh my e-groups data". Using this type of authentication, you will be able to manage sessions connected to multiple database schema at a time (all database schema associated with e-groups you are a member of). You can learn more about e-groups administration in Session Manager, reading this document.

Managing "My Sessions"

After successful database login, you are redirected to the "My Sessions" view.

• In case of database user authentication: You can see here database sessions established to the database user, you have used to log in to the database.
• In case of e-groups authentication: You can see here database sessions established to the database user having the same name as your NICE username.

You can click SID number of a session you would like to see the details of. You will be redirected to the "Session details" view.

You can also kill one or more sessions. Mark it (them), and then push "Kill marked sessions" button.

You can user "Search" field to limit the list of sessions displayed. You can specify there a string (case insensitive) you are interested in. The string will be searched in all fields visible on the report and the report will show only sessions containing your string (can be numeric or text value).

Managing "All Sessions"

"All sessions" view shows all sessions to which you have privileges.

• In case of database user authentication: you have privileges to see details and kill sessions connected to the database to the user, whose credentials you used to log in, but also sessions of READER and WRITER related to this database user (_W, _R and _WRITER, _READER). If you are authenticated against a database user granted with DBA priviledge, you can manage almost all database sessions (see list of exceptions).
• In case of e-groups authentication: you can see (and manage) sessions established against all database schema specified in e-groups you belong to. If one of your e-groups specifies "*" for the given database, you can manage almost all database sessions (see list of exceptions).

Viewing "Database Sessions"

"Database sessions" view shows a summary of all database sessions. This is read only view, so you cannot choose a session from it to see its details or to kill it.

Viewing session details

The "Session datails" view allows you to check more about a session you are interested in.

The information of the session is divided in 3 different areas:

• • General information: Shows the basic, mostely static information about the current session.
  • Dynamic information: shows the information related to the currently performed operation and the state of the session.

• • • The following two rows will contain values, if the last SQL statement processed by the session has not been flushed out from the database memory yet:
      • "Last SQL_ID": SQL_ID of the last processed statement. It is a hyperlink, which leads you to a new page: "SQL details", where you will be able to find detailed information about this SQL statement.
      • "Check Execution Plan": this link will display (in a popup window) the execution plan view used to run the statement.

• • Last SQL Statement text: Only an ACTIVE session fills up the area with the SQL text. It will be the statement which is currently performed by the session. It also can be the last statement this session performed (under the condition, that it was not removed from the database memory yet).

Kill this session: You can terminate the current session using this button.

Use “Refresh session info” button in case, when “Last SQL ID” and “Check Execution Plan” in Dynamic information table does not contain data and the session is ACTIVE.

Session's locks

The view "Session locks" presents a report of all locks your session is waiting for as well as all locks hold by your session.

Session's open cursors

The view "Open cursors" presents a report of all cursors, which are kept open by this session.

Click SQl_ID value for a statement you are interested in and you will be redirected to the "SQL details" view.

Viewing Blocking Locks

The view “Blocking locks” shows the blocking locks existing in the database. It displays a report in a treelike form, where “a parent” is a session, which keeps a lock and “children” are sessions, which are waiting for blocking session to release a lock. You can see also on the report, what objects are blocked.

The report shows:

• Who is blocking the object.
• Who is waiting for the object.
• The owner and the name of the blocked object.
• The time during each session is blocking or waiting for the object.
• The instance in which session is open.

Click in Bloker SID or Waiting SID of any session you are interested in, to be redirected to “Session Details” view. You can only see the details of a session if you have privileges to see it.

Viewing a list of buffered SQL statements

A tab "SQLs" shows the view presenting a list of SQL statements cached in a buffer of the database. It is possible to see a report of statements cached on one database instance at a time. In order to specify the instance, use the dropdown list from Search pane:

It is possible to view some performance data about each statement (e.g. CPU and DB time consumed by the statement, logical and physical reads, number of executions, the statement's text, etc.). The list is taken from a database instance cache.

The statements are flushed out from the cache depending on the database activity and the size of the cache itself, so on heavily used databases, you will find relatively "young" statements only.

Click SQL ID of any statement you are interested in, to be redirected to "SQL details" view.

Viewing details of an SQL statement

The view "SQL Details" shows the details of an SQL statement. The Oracle database is running always so called "child cursor". A child cursor is a cursor created based on an SQL statement, which runs in a specific context. One SQL statement has always at least one child cursor, but it can have a multiple ones (e.g. different execution plans). That's why the "SQL Details" shows at the top the list of child cursors, with some statistics about each child. You need to choose the child cursor of your interest to see the details of it.

The information of each child cursor is divided in 3 different areas:

• Execution Plan: Shows the execution plan of the SQL statement. The only condition is that the information about the child cursor you want to check has not been yet flushed from the database cache.
• General SQL statement Information: Shows the basic, mostly static information about the current SQL.
• SQL statement averages (per execution): Shows the averages of the last execution of the SQL.

Clicking on the values it opens other window where it shows a chart with the historical averages and its executions plans. With the chart the user can check if the execution plan changes and if it affects the performance data.

Example of a chart of logical reads per execution in KB:

The value of the execution plan is not meaningful (this is the hash value calculated over the text), it is used only to illustrate the changes of the execution plan and help checking if these changes affected the data.

It is possible to export all historical performance data to CSV using the appropriate button. This is preferred to export the data and process it locally (e.g. using Excel), because it lowers down the database load needed to get the required data each time, when a plot is generated.

Create the chart or export the data is a heavy task for the database, therefore, the user only can access this functionality each 5 minutes only (for same SQL at the same database).

Checking execution plan of a statement

It is possible to consult the execution plan of an SQL statement. You can run this functionality from a context of "Session details" (last SQL statement processed) or from "SQL details". The only condition is that the information about the child cursor you want to check has not been yet flushed from the database cache.

For security reasons, the columns "PREDICATE" and "FILTER" are shown only for users, who are privileged to see full SQL text of a statement.

Running Explain Plan for a statement

You can run the Explain Plan command against the SQL statement from a context of "Session details" or "SQL Details".

This functionality is under development currently.

Usernames exception list

The following database user's sessions are not displayed by by Session Manager: SYS, DBSNMP, SM_USER

Known bugs and limitations

"Return to application" link after session times out

The link "Return to application" after the database connection times out tries to redirect you to the previous page you used. In most cases it will happen that this page checks also if the session is not timed out. Since the session is timed out, the page with error and with the link "Return to application" reappears. In order to overcome this problem, one needs to log in again to session manager using http://cern.ch/session-manager link.

-- PrzemyslawRadowiecki - 08-May-2012