PL/SQL for Concurrent Users

  • sahoong

    SSCrazy

    Points: 2543

    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.

    Sahoong

     

  • crow1969

    SSCrazy

    Points: 2970

    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.

  • Nice Marmot

    SSCrazy

    Points: 2256

    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. https://docs.oracle.com/database/121/TGDBA/gather_stats.htm#GUID-B7D1B185-4942-4934-9B21-A9010F82A4FF

    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 https://docs.oracle.com/database/121/REFRN/GUID-28E2DC75-E157-4C0A-94AB-117C205789B9.htm#REFRN30223 - 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.

    HTH

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

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