Oracle Usage

Article Summary

Share feedback

Thanks for sharing your feedback!

Oracle provides various usage collection mechanisms for application Entry Level Objects.

With every code extraction uploaded to Panaya, Panaya carries out the following -

Extracts the existing usage history from each different usage mechanism
Aggregates the existing usage information and thus presents a usage map for the organization

Panaya recommends collecting the usage information for an appropriate period (At least 4 to 6 months) before any Retirement or Planning activity.

Forms Usage

Usage mechanism	Oracle provides a form audit mechanism in which all end-user access information is stored in Oracle Applications’ tables. By default, only minimal audit data is saved. There are Standard reports and purge programs that have easy access to this audit data. The system profile option “Sign-On: Audit Level” controls the level of end user access auditing. The valid settings are None, User, Responsibility, and Form. This profile option should always be set to “Form” for the highest auditing level.
Tables storing the usage (Examples)	APPLSYS.FND_LOGINS, APPLSYS.FND_LOGIN_RESPONSIBILITIES APPLSYS.FND_LOGIN_RESP_FORMS
Average purge period	3 days to 4 weeks Governed by scheduling of Concurrent Program ‘Purge Signon Audit data’
Panaya maximum aggregation period	18 months

Usage mechanism	All concurrent requests are recorded in the FND_CONCURRENT_REQUESTS table. There is no configuration required. This usage information will include all concurrent types, including : Reports Stored procedures Hosts SQL Scripts SQL Loaders
Tables storing the usage (examples)	FND_CONCURRENT_REQUESTS
Average purge period	2 - 8 weeks Governed by the scheduling of Concurrent Program ‘Purge Concurrent Request and/or Manager Data’
Panaya maximum aggregation period	18 months

Good to Know!
Panaya classifies scheduled concurrent programs as Frequently Used to raise attention and avoid failure in future execution

Usage mechanism	All Discoverer reports are recorded in the EUL_QPP_STATS table. The Users Collect Statistics privilege needs to be given to all users, enabling the Discoverer to collect statistics on the queries run by users.
Tables storing the usage (examples)	EUL_QPP_STATS for Disco 4 EUL5_QPP_STATS for Disco 5
Average purge period	In most cases usage information is not purged
Panaya maximum aggregation period	18 months

Usage mechanism	All alerts usage information are recorded in the ALR_ALERTS table. There is no configuration required.
Tables storing the usage (examples)	ALR_ALERT_CHECKS ALR_ALERTS
Average purge period	In most cases usage information is not purged
Panaya maximum aggregation period	18 months

Usage mechanism	All workflows usage information are recorded in the WF_ITEMS table. There is no configuration required.
Tables storing the usage (examples)	WF_ITEMS
Average purge period	In most cases usage information is not purged Governed by scheduling of Concurrent Program ‘Purge Workflow Data’
Panaya maximum aggregation period	730 Days (2 years)

Usage mechanism	Oracle provides a framework audit mechanism in which all end user access information is stored in Oracle Applications’ tables (Page Access Tracking). By default, no audit data is saved. To activate the tracking, follow the instructions from the Oracle Support Website: Page Access Tracking in Oracle Applications 11i [ID 278881.1] Page Access Tracking in Oracle Applications Release 12 [ID 402116.1]
Table storing the usage	jtf_pf_ses_activity
Average purge period	3 days to 4 weeks Governed by the scheduling of Concurrent Program ‘Purge Signon Audit data’
Panaya maximum aggregation period	730 Days (2 years)

Database Components and Profile Options

As standard, the Oracle DB does not provide any usage collection mechanism for Database objects nor Profile Options.
Database objects include Packages, Procedures, Functions, Views, and more.
Panaya’s analysis builds a grid to store all code dependencies, which inherits the usage information from the application entry level objects (e.g. Forms, Reports) to the related DB objects or Profile Options (Based on the FND_PROFILE.VALUE).
For example: When a customized report calls a DB View, the report’s usage will be inherited to that View.

In this article