DPA - Count of sessions per user and remote machine on an instance ?

Within the DPA UI, you can filter out by a value in one dimension (e.g., DB Users) and the other tabs will only display entries related to that filtered dimension (e.g., sessions, queries, programs).

Steps:

1. You can only do the filtering at the hour level, so you must first drill down into an hourly interval (i.e., the bar graphs become horizontal)
2. Go to the DB Users tab
3. Click on the link for which user you are interested in (e.g., test7)
   • Note: the breadcrumbs at the top of the page will add test7 to indicate it is filtered by that and the "DB Users" tab disappears
   • 
4. Go to the Machines tab and click on the link to the machine in question
   • Note: the selected machine is added to the breadcrumb trail and the “Machines” tab disappears
5. Go to the Sessions tab and view the list of sessions (now filtered by user and machine)

Filtering for a specific SQL statement is slightly different.

1. On the SQL tab, if you click the URL it takes you to the Query Performance Analyzer page.
2. Instead, select the query you want from the “Filter on this Query:” pulldown list in the upper right
   • Note:
     • The breadcrumbs now indicate that SQL statement chosen
     • The SQL tab disappears
     • A “SQL Data” tab appears that gives details about that SQL statement
     • All the other dimension tabs (e.g., Programs, Machines, …) are now filtered to only show the information relative to that SQL statement
   • 

If the above approach via the UI won't work and you'd like to query the information directly. I'd search the DPA forum for "Custom" and "Sessions" or other keywords to see other custom metrics, reports, and alerts in the forum.

Some examples that you could use to piece together a query:

Custom Report - Top SQL by Database User

Custom Report - Top SQL for a Machine

Custom Report - Top SQL by O/S User

Custom Report - Find SQLs by Session ID (SID)

In General, as a starter for you, DPA has a sort of two-star schema for detail data and one for Sum data. The detailed data is where these are polling from with the CONSW_DBID table in the center. Where DBID is a number in the table COND;

CONO_DBID has the O/S User for Oracle servers and the Database name for SQL server instances. CONM_DBID has the Machine and CONU_DBID the database user.

you may need to do some joins to get them all in one report, but these should show your DBA's the structure.

Thanks for the information.  I'll look into it.

Thanks for the information.  I'll look into it.