1. Designing with Calculation Manager for Oracle Enterprise Performance Management Cloud
  2. Administering Essbase Servers, Applications, and Databases for Planning Applications
  3. Using Query Tracking on Aggregate Storage Databases

Using Query Tracking on Aggregate Storage Databases

Use query data to select the most appropriate set of aggregate views to materialize for a database.

You enable query tracking to capture data about the cost of each query that is performed against the database. The cost of a query is an estimate of the average retrieval time required to retrieve values from the view. For the first view (selected by default), the estimation is the average of all possible queries. For views for which query tracking is used, the estimation is the average of the tracked queries. Therefore, a view may, under different conditions, display different estimates. To compute a percentage that evaluates the benefit of using a particular view, divide the query cost value for the view into the query cost value for using views that contain only level 0 values.

Once enabled, query tracking continues until one of the following happens:

  • Query tracking is disabled for the database, as described in this topic.

  • The application is shut down. If the application is shut down, query tracking does not resume automatically when the application is restarted.

  • Additional aggregate views are materialized for the database. Because query tracking data becomes invalid when additional views are materialized, materializing any new aggregate views resets the query tracking.

Query tracking, which is stored only in memory, includes queries from Oracle Hyperion Web Analysis, the grid API, report scripts, Java APIs, and so forth.

Note:

Query tracking can be used only on aggregate storage databases.

To enable or disable query tracking:

  1. In System View, click Database Properties icon.
  2. In Enterprise View, right-click the plan type, then select Query Tracking, and then select and one of these options:

    • Enable, to enable query tracking

    • Disable, to disable query tracking

      When you enable query tracking, the database records query information; when you disable query tracking, the database stops recording query information and clears query data from memory.

  3. Click OK to confirm that you want to enable or disable query tracking.