PL/SQL for Concurrent Users

  • Hello,

    I am a SQL Server person but I have been working with PostgreSQL in recent years in addition to SQL Server. I am in early stage of migrating an Oracle server to PostgreSQL. I am collecting some statistics on it as part of the discovery process prior to migration. I have some codes for the tasks below but they are not working with Oracle 12c which is the version that I am working with. Has anyone got working codes for:

    1. Max concurrent user per minute
    2. Volume of data outbound per day from DB – AVG week day and MAX week day
    3. List of client machines connecting direct o the DB alongside user names


    Many thanks in advance.



  • For item 1, you can try the v$license view to see what the high water mark is for sessions.  The users may not be actually running anything with those sessions, but they are connected.

    For item 3, you would likely need to parse the listener.log file.  This file can get pretty big, depending on how often you cycle out the file.

  • Oracle has a performance reporting tool called the Automated Workload Repository (AWR). It collects all kinds of stats, including file and object I/O. The tool needs Enterprise Edition plus a Diagnostic Pack licence. If your source system meets those criteria, the database will likely have AWR data in it already.

    Otherwise, you can install the older version of AWR, called Statspack, for free. Both work a similar way, with hourly snapshots that you can use for reports.

    You can write your own code using e.g. V$SESSION - the MACHINE column is usually the application server or end-users's PC - but if you can leverage Oracle's tools you'll get more information.


Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply