Managing data caches and queries in Data Virtualization

Data Virtualization Admins can cache a query to save its data results and optimize query performance.

About this task

In Data Virtualization, you can virtualize remote data sources so that data can remain remote while it is accessed through a single framework. For example, you can virtualize tables across different remote databases, and you can run queries that can join these tables. To avoid query executions that are time-consuming, Data Virtualization Admins can create caches to improve performance by caching the result sets of your queries.

Important:

When pass-through authentication is enabled, caches (including user and automatically generated caches) cannot be created for data sources that use personal credentials. To enable cache creation, see TOGGLEPERSONALCREDENTIALSUSEINCACHE stored procedure in Data Virtualization.

For more information about pass-through authentications, see Adding data source connections to Data Virtualization to connect with personal credentials.

Managing caches

On the Cache management page, a Data Virtualization Admin can monitor storage and responsiveness of your data caches. To learn more about optimizing queries, consider the following elements from the Cache management page:
  • The Cache storage bar chart lets you view the total amount of cache storage that you allocated during provisioning. You can also monitor the amount of storage that is used by both active and inactive data caches. Active data caches are used in query optimization.
  • The Responsiveness bar chart lets you monitor how many queries ran in a specific execution time period. You can also compare the responsiveness of queries that use data caches with the queries that do not use data caches.
  • The Total hits column within Active data caches shows you the overall number of hits an active cache received.

Watch the following video to learn how to optimize queries using cache management in Data Virtualization.

Procedure

Refer to the following tasks to learn how to manage and customize your data caches:
Adding a cache entry
To add a cache entry, see Adding data caches.
Viewing details of a cache
To view the details of a cache, follow these steps.
  1. Go to Data > Data virtualization > Virtualization > Cache management.
  2. Go to the action menu of the cache and click View cache details.
Editing a data cache
To edit a data cache, follow these steps.
  1. Go to the action menu of an active cache and click Edit name.
    Note: You can only edit active caches.
  2. Enter a new name for your cache and click Apply.
Refreshing a cache
To edit the cache refresh schedule, follow these steps.
  1. Go to the action menu of an active cache and click Edit refresh rate.
    Note: You can only edit active caches.
  2. Set a refresh rate for the cache to prevent the cache from becoming stale in time.
    • None, manual only: To refresh a cache manually, go to the action menu of an active cache and click Refresh now.
    • Minutes: Select the interval in minutes to refresh your cache. For example, refresh every 15 minutes.
    • Hourly: Select time (minute) of the hour to refresh your cache. For example, refresh every hour at the 30 minutes past the hour.
    • Daily: Select time of the day to refresh your cache. For example, refresh every day at 12:00 AM.
    • Weekly: Select days of the week and time of the day to refresh your cache. For example, refresh every Monday, Wednesday, and Friday at 12:00 AM.
    • Monthly: Select weeks of the month, days of the week, and time of the day to refresh your cache. For example, refresh on the first day of the month at 12:00 AM.
  3. Click Apply.
Deactivating an active cache
To deactivate an active cache, go to the action menu of the cache and click Deactivate.
Deleting a data cache
To delete a data cache, go to the action menu of the cache and click Delete.
Re-creating a deleted data cache
To re-create a deleted data cache, follow these steps.
  1. Click Inactive data caches > Deleted caches.
  2. Go to the action menu of the cache you want to re-create, and then click Recreate cache.